pgpool-II doesn't execute my failover script I have been installed pgpool-II 3.3.4 from source. I have two pgpool nodes and I also used the watchdog configuration to get a pgpool services with High Availability. When I stop one, the other wake up and everything is fine.

Also I have two backends, one master and one slave, then I have configured the failover command. Here is my problem, when I stop the master, pgpool doesn't execute the failover.

There are my backends:

backend_hostname0 = '172.23.0.70'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.3/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '172.23.10.21'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.3/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

There is my failover command declaration:

failover_command = 'failover.sh %d %M %m'

Of course, I have been copy the failover to /sbin/ directory in pgpool nodes.
There is the failover.sh script code:

#!/bin/sh

FALLING_NODE=$1

# The new master
SLAVE1="172.23.10.21"

if test $FALLING_NODE -eq 0
then
ssh -T postgres@$SLAVE1 "touch /tmp/postgresql.trigger.5432"
ssh -T postgres@$SLAVE1 "while test ! -f /var/lib/postgresql/9.3/main/recovery.done; do sleep 1; done;"
ssh -T postgres@$SLAVE1 "/etc/init.d/postgresql restart"
/usr/local/bin/pcp_attach_node 10 localhost 9898 pgpool pgpool 1
fi

Now, when I run the failover.sh manually, it works, so why it doesn't run with pgpool?

I run pgpool with sudo user so:

Do I need to run pgpool with other user?
Do I need to create an specific user to pgpool configuration? It works manually with sudo!!!.
Do I have to change my failover script command?

I had issues with getting the script to run as well. But eventually figured it out.

A few high level steps to keep in mind.

In your failover script redirect results to a log file like:

ssh -T postgres@$SLAVE1 "touch /tmp/postgresql.trigger.5432" > /tmp/failover.log 2>&1

This might help you identify why it is failing.

However, here is a high level overview of how I managed to get the failover  script running:

install failover script on pgpool server
pgpool is running under root. So...
as user root run ssh-keygen -t rsa on the pgpool server and cat id_rsa.pub and >> to authorized_keys on the db servers

Doing so will allow the script to run without interactive authentication, which is what my problem was.


Why is log file divided into virtual log files in SQL Server? In Books Online it is mentioned that the transaction log file is divided into virtual log files internally with no fixed number of count. It says that virtual files are useful while file growth operation but I didn't get it completely. I am curious to know why exactly it needs virtual structure?

Thanks for your time

I believe that some other DBMSs requires several files, to provide for deletion of old log files as they are no longer needed. SQL Server doesn't need that thanks to the subdivision of one ldf file internally into several virtual log files. One can argue which concept one would prefer (several files, or one file with VLFs), but that decision was made for us about the mid ninties (the first version of SQL Server with the current storage architecture was released 1998).


Can I join multiple column in a table? I am working school homework question. I have an inpatient table, I have to determine the top 10 ICD code that have the highest frequency.  Please  output  each  code  with  its  frequency  (i.e.  occurrence)  in  the descending order.
My SQL code is:

select ICD9_DGNS_CD_1, ICD9_DGNS_CD_2, ICD9_DGNS_CD_3, ICD9_DGNS_CD_4,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,
ICD9_DGNS_CD_7,ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10, count(*) AS frequency
from seis735_db.ip
group by ICD9_DGNS_CD_1,ICD9_DGNS_CD_2,ICD9_DGNS_CD_3,ICD9_DGNS_CD_5,ICD9_DGNS_CD_6,ICD9_DGNS_CD_7,
ICD9_DGNS_CD_8,ICD9_DGNS_CD_9,ICD9_DGNS_CD_10
order by count(*) desc
LIMIT 10;

This outputs the top 10 in each ICD code diagnoses. I am trying to find just the top 10 from all the 10 columns? I am new to sql and I am having a hard time writing the query for this question.

Any help will be much appreciated. Thank you!

Following is one way to find the 10 most frequent values across all 10 columns - but only counting frequency for each value within its own column (explained further, later):

select AggregatedCounts.ICD_Value, AggregatedCounts.Source_Column, AggregatedCounts.frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value, 'CD_1' as Source_Column, count(1) as Frequency from seis735_db.ip group by ICD9_DGNS_CD_1
    union
    select ICD9_DGNS_CD_2, 'CD_2' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_2
    union
    select ICD9_DGNS_CD_3, 'CD_3' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_3
    union
    select ICD9_DGNS_CD_4, 'CD_4' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_4
    union
    select ICD9_DGNS_CD_5, 'CD_5' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_5
    union
    select ICD9_DGNS_CD_6, 'CD_6' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_6
    union
    select ICD9_DGNS_CD_7, 'CD_7' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_7
    union
    select ICD9_DGNS_CD_8, 'CD_8' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_8
    union
    select ICD9_DGNS_CD_9, 'CD_9' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_9
    union
    select ICD9_DGNS_CD_10, 'CD_10' as Source_Column, count(1) as frequency from seis735_db.ip group by ICD9_DGNS_CD_10
) as AggregatedCounts
order by frequency desc limit 10;

How this works:

The main part of this solution is that we have 10 separate SELECT statements.

The first selects all values from column 1 (ICD9_DGNS_CD_1), groups by these values and counts the frequency.

The next statement does the same for the second column, and so on.

By using the UNION operator, we merge together the results from all of these 10 SELECT statements - producing one big set of results. This counts the frequency of each unique value in each of the 10 columns and puts them all into a single long list.

The next part of the solution is that we SELECT from this result set. So the result of those 10 SELECT statements (with UNIONs) is named 'AggregatedCounts' - and we now select from these aggregated counts.

Note, in each of the 10 SELECT statements we are actually selecting 3 values: first the value in the column we are looking at, then a hard-coded string which tells you the name of the column we are looking at (like 'CD_1'), then finally the count. In the first SELECT statement we name these 3 columns (ie. we give each an alias): ICD_Value, Source_Column and Frequency.

Finally, in our outer select, we select these 3 column names. That outer select is ordered by Frequency, descending, and we limit the results to the top 10 rows - that is, the 10 rows with the highest value in the Frequency column - and we report what the value is, and from which column it originally came.

But note (!) If you have, say, the value 'aaa' in column 1, and 'aaa' in column 2 - they will not be counted together using this solution.

If we want to count values across all columns, then we use the following solution instead:

select AggregatedCounts.ICD_Value, count(1) as Frequency from
(
    select ICD9_DGNS_CD_1 as ICD_Value from seis735_db.ip
    union all
    select ICD9_DGNS_CD_2 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_3 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_4 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_5 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_6 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_7 from seis735_db.ip
    union all
    select ICD9_DGNS_CD_8 frequency from seis735_db.ip
    union all
    select ICD9_DGNS_CD_9 frequency from seis735_db.ip
    union all
    select ICD9_DGNS_CD_10 frequency from seis735_db.ip
) as AggregatedCounts
group by AggregatedCounts.ICD_Value
order by Frequency desc limit 10;

What have we changed here?

Firstly, the 10 selects are now just selecting all values - no group by, and no other values (like source column or counting).

Secondly, the UNION statements have been changed to UNION ALL - this is because by default, UNION will include only unique values in the result set. This time we want to do our counting in the outer SELECT, so we need all instances of all values in our AggregatedCounts list.

Finally, as just suggested, now our GROUP BY has moved to the outer SELECT statement - and we are doing our counting here - across all values from all columns.

You can view the first solution working on db-fiddle

And you can view the second solution working on db-fiddle - using exactly the same source data.

Note on these 2 fiddle solutions, in the first solution, the value '500' ranks above the value '401' (with frequencies 30 and 28) but in the second, '401' ranks above '500' (with frequencies 31 and 30). Examine the last 3 lines of inserting data - I have put the value '401' into the 5th column here - so '401' appears 3 times in column 5, and 28 times in column 4 = 31 times in total. This explains the difference in results, and the difference between the two SELECT statements.

Lastly, using UNION like this is making your database do a lot of work. If your table is large, this solution may take a long time to run and slow down your database. There may be other ways to solve this using procedures, but this is an easy to understand approach using SELECT statements.


What is the row overhead when using Page Compression? I have created a table with 650 Numeric(19,4) columns.
When I switch on Page Compression, by running

ALTER TABLE fct.MyTable REBUILD  WITH (DATA_COMPRESSION = PAGE);

I get

  Msg 1975, Level 16, State 1Index 'PK_Mytable' row length exceeds the maximum permissible length of '8060' bytes.

but 650 times 9 bytes is only 5850 bytes, which is quite far from the stated limit of 8060 bytes.

The server is running Windows 2012 r2 with SQL Server 2016 SP1 CU2

What is the row overhead when using Page Compression?

Here is some code to show what I mean:

/* test script to demo MSG 1975 */
DECLARE @sql NVARCHAR(max)='', @i INT =0
drop table if exists dbo.mytable;

SET @sql = 'Create table dbo.Mytable (MyTableID bigint not null
  identity(1,1) primary key clustered, '

WHILE @i < 593 BEGIN
    SET @sql += ' Column' + LTRIM(@i) + ' numeric(19,4) null, '
    SET @i +=1
END

SET @sql += ' LastColumn int) '
--SET @sql += ' with (DATA_COMPRESSION = ROW) '
SET @sql += ' with (DATA_COMPRESSION = PAGE) '

SELECT @sql
EXEC sys.sp_executesql @sql

SELECT top 10000 * FROM dbo.MyTable MT

Row compression also fails, but at a different row count.

If you try creating your table without the clustered PK Constraint, and you'll get a slightly different error:

  Msg 1701, Level 16, State 1, Line 1 Creating or altering table
  'Mytable' failed because the minimum row size would be 8067, including
  1530 bytes of internal overhead. This exceeds the maximum allowable
  table row size of 8060 bytes.

In this error message, you can see that there is 1530 bytes of internal overhead for page compression.

Now, you can do the math:

8 bytes for bigint MyTableID
4 bytes for int LastColumn
9 bytes for each of the 593 numeric(19,4) columns (5337 bytes total)
1530 bytes of compression overhead

So, 8 + 4 + (593*9) + 1530 = 6879.
Wait a second....  That's still below 8060. What's up with that?!

The Page Compression algorithm actually stacks several compression algorithms together. The first step is to apply ROW compression. The overhead of row compression is not included in the 1530 bytes of overhead listed in that error message.

You can read more about how row compression works here on my blog and here in BOL. You'll note in the BOL article that it describes the numeric storage as "This storage is exactly same as the vardecimal storage format," but doesn't explain vardecimal. This post covers vardecimal a bit more--essentially, it adds 2 bytes of overhead per column to store the actual length (similar to what varchar does).

Row compression will require an additional 2 bytes for each of the 593 numeric columns, plus the bigint and int will require 1 byte of overhead each.

The row-compressed storage requirements would be:

8 bytes + 1 byte overhead for bigint MyTableID
4 bytes + 1 byte overhead for int LastColumn
9 bytes + 2 bytes overhead for each of the 593 numeric(19,4) columns
1188 bytes of ROW compression overhead

8 + 4 + (593*9) = 5349 bytes data

1 + 1 + (593*2) = 1188 bytes row compression overhead

6537 bytes total for row-compressed schema

Now that we have the row size for the row-compressed schema, we can revisit our math. The page-compressed row size will be the data size + row compression overhead + page compression overhead:

8 bytes for bigint MyTableID
4 bytes for int LastColumn
9 bytes for each of the 593 numeric(19,4) columns
1188 bytes of ROW compression overhead
1530 bytes of PAGE compression overhead

  5349 bytes data
+ 1188 bytes row compression overhead
+ 1530 bytes page compression overhead

8067 bytes total


what is difference between oracle instant client and oci? I've been reading upon the definitions of oracle instant client and oci but being new to oracle database having a bit of difficult time to understand what they are/for. I would appreciate someone can clarify the difference of two. Thank you!

Oracle Instant Client is a light-weight software which helps to run OCI, OBDBC, Pro*C, JDBC without installing full Oracle client.  In other hand the Oracle Call Interface (OCI) is an application programming interface (API) that allows applications written in C to interact with one or more Oracle Servers.
For details:
What is Oracle's instant client and how do I install it?
Introduction to Oracle Call Interface


Configuring SQL Replication subscriber to a publisher in a different DNS zone I have a server that I wish to set up a subscriber on, however the publisher lives on a different domain controller and is not in the primary DNS.

To access the server, a FQDN needs to be provided.
so publisherServer becomes publisherServer.something.local

when trying to add a subscription, I get the following error:

  SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name

How can I set up a subscription, when the server name publisherServer does not resolve from the server I wish to subscribe from.

I can set up the subscription if I add the server name and ip into the hosts file.

Not pretty, but it works.


Setting connection collation in master for slave replication I am trying to replicate a MySQL 8 server (master) to MySQL 5.7 (slave) instance. I know it is not officially supported, but the thing is we need to use a service that has only supports MySQL 5.7.

I know there are a few collations that are not available in MySQL5.7 which are present in MySQL8. For that, i converted all tables from utf8mb4_0900_ai_ci to utf8mb4_general_ci as our data values are supported by both, so I was able to do this without any consequences.

I have also set the following settings in my master's configuration:

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci; SET NAMES utf8mb4;'

which yields me the following: (I am not connected as superuser)

mysql80> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

Now I have narrowed it down to this that my collation_connection is the culprit because utf8mb4_0900_ai_ci is not part of MySQL57. Due to which I get the following error on slave:

Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file' on query. Default database: 'database'. Query: 'BEGIN'

Is there any way to force my master instance to use utf8mb4_general_ci instead of utf8mb4_0900_ai_ci?

Regards

Find all the Stored Routines; capture the ability to re-CREATE them.
Change the DEFAULT COLLATION for each DATABASE that had such routines.
DROP those routines.
Re-CREATE them.
SHOW CREATE PROCEDURE (etc) to verify that the collation is compatible with 5.7.


web interface port for oracle 11g I am running a fresh Oracle 11g installation created on lunix redhat 5, but I forget the web interface port, I have already tried this https:// localhost:5500/em/ but did'nt work.

any help will be appreciated.

The default port used for dbcontrol is 1158. You may have changed the port though, of course.

The emctl control commands also display the 'about' URL which includes the port number. For example:

$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://myhost.mydomain.com:1158/em/console/aboutApplication
...


How to compare strings as int in EAV model? I have implemented an EAV db pattern as part of a PoC in Postgres. This is the following ER illustration:

entity:
id, primary key
firstName, varchar
lastName, varchar
tenantId, int, b-tree indexed

attribute:
id, primary key
label, varchar
type, varchar, (enumerated as string from java enum , NUMERIC, TEXT, DATE)

attribute_value:
id, primary key
attribute_id, fk reference attribute primary key
entity_id, fk reference entity primary key
value, varchar

Now the problem I am facing is comparing values in the joined tables. For example:

  How do I check whether an attribute with label = 'marks12' has value < 100

Comparing the value::int does not solve the problem as there are other attribute such as gender that cannot be cast into int value.
How do I perform such value-centric conditions in the above EAV design model?

PS: I am open to other database designs which allow to map/store dynamic attributes in an RDBMS.

easiest is to ignore anything that does not look like an integer.

select e.* , av.value
FROM attribute_value AS av
JOIN attribute AS a ON av.attribute_id = a.id
JOIN entity AS e ON av.entity_id = e.id
WHERE
   a.label = 'marks12'
 AND
   CASE WHEN av.value ~ '[+-]?[0-9]{1,20}' THEN av.value::bigint END < 100;

this is much harder when value might be a date,


PostgreSQL visibility of records changes from other transaction inside the current one I have a table "tst" which has a "status" column using which we process records in that table.
Once the records are processed we set the status to 'Y';

    #\d tst
                 Table "core.tst"
     Column |  Type   | Modifiers
    --------+---------+-----------
     id     | integer | not null
     status | text    |
    Indexes:
            "amn2_pkey" PRIMARY KEY, btree (id)

Issue: At the start of the long(approx 20 mins) transaction I have 3 records in tst table as shown below

    #select * from tst;
     id | status
    ----+--------
        1 | N
        2 | N
        3 | N
    (3 rows)

But during the transaction I have couple of more records being inserted into the tst table, but I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

Session 1:

    DO $$
    BEGIN
        perform pg_sleep(20) ;
        UPDATE amn2 SET des = 'Y' ;
    END $$;

Session 2:

    INSERT INTO tst VALUES ( 4, 'N');

Post the Session 1, all the records were updated to status 'Y' including id 4.

Is this expected? Or some DB/table serializable set up missing?

I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a statement.

You can use SERIALIZABLE isolation to get that behaviour, but it has a performance impact and can lead to table bloat on fast-changing tables.

See the documentation on transaction isolation to understand what is going on.


Scalable DBaaS that can scale to hundreds of terabytes with easy backup and restore I've gone over many DBaaS solutions, including Xeround (limited to 50G, in memory), NuoDB (currently in beta 7), DrawnToScale (not out yet), MongoDB (in memory), RavenDB (for ASP.NET, but NoSQL) and many others, DynamoDB (costly for real-time web app and doesn't offer backup/restore).

I find everyone lacking in some ways. I search for a scalable and affordable Database as as service solution using NewSQL (RDMBS), but I will ocnsider NoSQL if there aren't any solutions for my needs.

Amazon DynamoDB seems like a good solution because I can scale to terabytes of data without worrying about disk space. However, DynamoDB costs a lot for high read / write applications and it doesn't offer easy backup/restore option in case of data corruption that is very important to me.

In short what I need:

1) DBaaS (can be self hosted too) the can easily scale to terabytes of data (ie. big data)

2) Capable of very high throughput for real-time application that does many read/writes (although I will use caching too to reduce overload)

3) Being able to query the data using SQL, a RDBMS DB. Although if a NewSQL solution isn't available, I will go with NoSQL

4) Easy backup and restore big amount of data in case of data corruption. I know that many solutions are safe against server failure, but I worry about data corruption and I want to be able to restore the data back if that happens - IMPORTANT!

5) I will be developing my web app / mobile app in ASP.NET, so I prefer a solution that have a .NET connector

6) If hosted, I prefer hosting my DB on Amazon AWs.

7) I don't want a self-managed solution and handle sharding and all the other complicated stuff

8) I want an affordable solution, not like Clustrix that costs thousands of dollars per node. I want a cost-effective solution where I can start small and pay more when my database grows (all idea behind cloud architecture, right?)

Right now the only solution that looks ok is RavenDB, NuoDB (still in beta). Solution like Xeround is amazing, and I am using it right now, but it's in-memory DB and limited for 50GB, so it's not good for a bog data hosting.

It seems that new solutions appear every month and it seems like there isn't any good solution out there (a new startup maybe?). I wanted to know what are my options. As a developer, I really want to focus on building big applications, but I can't start until I have a reliable DB solution that can handle very high traffic and can scale in size without me needing to build all the DB server architecture myself.

I spent weeks trying to find a solution with no luck. A need a solution like Xeround but for big data. Who knows, maybe there is one up there that I missed. Please help me out.

Barry Morris here from NuoDB.  Answers below:

  1) DBaaS (can be self hosted too) the can easily scale to terabytes of
  data (ie. big data)

NuoDB is a downloadable product that runs anywhere (laptop, rack, public cloud).  We have not announced DBAAS at this point.  NuoDB uses Key/Value stores at the storage layer so supported DB sizes will be related to whatever the particular KV store can handle.

  2) Capable of very high throughput for real-time application that does
  many read/writes (although I will use caching too to reduce overload)

NuoDB is a general purpose database system designed for high-speed scaled-out transaction processing.  There is no need for a separate cache tier because the NuoDB in-memory tier performs a similar function.

  3) Being able to query the data using SQL, a RDBMS DB. Although if a
  NewSQL solution isn't available, I will go with NoSQL

Check. NuoDB is a SQL database.

  4) Easy backup and restore big amount of data in case of data
  corruption. I know that many solutions are safe against server
  failure, but I worry about data corruption and I want to be able to
  restore the data back if that happens - IMPORTANT!

NuoDB supports JDBC, ODBC and other standards APIs, so backups can be done at the SQL level. Other alternatives include simply taking one of your storage managers offline and putting it somewhere safe.

  5) I will be developing my web app / mobile app in ASP.NET, so I
  prefer a solution that have a .NET connector

NuoDB has an ODBC API and there is a community developed ASP.NET connector.

  6) If hosted, I prefer hosting my DB on Amazon AWs.

NuoDB supports Amazon AWS

  7) I don't want a self-managed solution and handle sharding and all
  the other complicated stuff

No sharding or partitioning (or caching, M/S replication etc) needed with NuoDB.

  8) I want an affordable solution, not like Clustrix that costs
  thousands of dollars per node. I want a cost-effective solution where
  I can start small and pay more when my database grows (all idea behind
  cloud architecture, right?)

NuoDB is free forever for a baseline system.


How to backup a MySQL database without slowing the application? I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is running our application response time goes through the roof. So every 8 hours, the application is slow and sometimes even produces errors.

Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?

A) Take the backup from a replicating slave. Zero performance loss.

B) Use LVM to take a consistent snapshot. Copy the database files at your leisure.

C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds.

As an aside, how are they taking a consistent backup with MyISAM tables without LOCKING the application for 8 hours straight? It doesn't seem possible.


Searching for a Data Dictionary for SQL or Oracle I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases.

By metadata map, I mean a definitive description of what objects make up database model and their relationships (i.e. a server has 0:n databases, a table has 1:n columns, a column has 0:n constraints).

Any versions of these DBMS platforms would be useful.

I've Community Wiki'd this

Oracle

Storage can hold multiple databases
Databases can be opened by multiple instances
Server can run run multiple instances
A server can only run a single instace for any given database
Data is stored in tablespaces
A tablespace may be made up of multiple data files
Tables/indexes/Materialized Views can be partitioned across multiple segments
A segment lives on a tablespace
A database object (table, index, stored program unit...) is owned by a user
A table may be be referenced (through a foreign key constraint) by another table, including a table owned by another user
A table may have at most one primary key constraint
A table may have zero or more unique constraints
A primary key or unique constraint is logically enforced by exactly one index (though you could have a multi-column unique constraint which is backed up by multiple unique indexes with the columns in a differing order)
An index may be unique or non-unique. A unique constraint may be enforced by a unique or non-unique index.
A table must have at least one column
Each column has a datatype (and optionally a length or precision/scale)
A trigger may relate to at most a single table. (Some triggers, such as SERVERERROR, don't relate to a table).


Implement a Parent-Level Count in Binary Search Tree Based on this question, have any way to implement a parent level count?

Find highest level of a hierarchical field: with vs without CTEs

Example table:

+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         0 |
|  2 |         1 |
|  3 |         1 |
|  4 |         2 |
|  5 |         2 |
|  6 |         3 |
|  7 |         3 |
|  8 |         5 |
|  9 |         5 |
| 10 |         7 |
+----+-----------+

Function:

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT, Count INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;

SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
SET count_limit = Count;

WHILE queue_length > 0 && count_limit > 0 DO
    SET front_id = FORMAT(queue,0);
    IF queue_length = 1 THEN
        SET queue = '';
    ELSE
        SET pos = LOCATE(',',queue) + 1;
        SET q = SUBSTR(queue,pos);
        SET queue = q;
    END IF;
    SET queue_length = queue_length - 1;

    SELECT IFNULL(qc,'') INTO queue_children
    FROM (SELECT GROUP_CONCAT(id) qc
    FROM pctable WHERE parent_id = front_id) A;

    IF LENGTH(queue_children) = 0 THEN
        IF LENGTH(queue) = 0 THEN
            SET queue_length = 0;
        END IF;
    ELSE
        IF LENGTH(rv) = 0 THEN
            SET rv = queue_children;
        ELSE
            SET rv = CONCAT(rv,',',queue_children);
        END IF;
        IF LENGTH(queue) = 0 THEN
            SET queue = queue_children;
        ELSE
            SET queue = CONCAT(queue,',',queue_children);
        END IF;
        SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
    END IF;
END WHILE;

RETURN rv;

END $$

I need implement a count on that function to limit iterate based on parameter passed on query.

SELECT id,parent_id, GetFamilyTree(1, 3) FROM pctable;

Detail: first param is id of desired family tree
        secund param is a level depth of descendants

Example result of example table:

+----+-----------+
| id | parent_id |
+----+-----------+
|   1|        0  |
|   2|        1  |
|   3|        1  |
|   4|        2  |
|   5|        2  |
|   6|        3  |
|   7|        3  |
+----+-----------+

It is possible?

In this case, the count not working as well...its counting every tree item, not every tree level.

If you look at the GetFamilyTree code, there is nothing monitoring the tree height or any particular level.
To compensate, I have a revised version of this code
DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT,LevelLimit INT)
RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
    DECLARE rv,q,queue,queue_children,front_rc,level_part VARCHAR(1024);
    DECLARE queue_length,front_id,front_ht,pos,curr_level INT;

    SET rv = '';
    SET queue = CONCAT(GivenID,':0');
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET front_rc = queue;
            SET queue = '';
            SET pos = LOCATE(':',front_rc);
        ELSE
            SET pos = LOCATE(',',queue);
            SET front_rc = LEFT(queue,pos - 1);
            SET q = SUBSTR(queue,pos + 1);
            SET queue = q;
            SET pos = LOCATE(':',front_rc);
        END IF;
        SET front_id = LEFT(front_rc,pos - 1);
        SET front_ht = SUBSTR(front_rc,pos + 1);
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(CONCAT(id,':',front_ht+1)) qc
        FROM pctable WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                IF front_ht < LevelLimit THEN
                    SET rv = queue_children;
                END IF;
            ELSE
                IF front_ht < LevelLimit THEN
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    #
    # Strip away level parts of the output
    #
    IF LENGTH(rv) > 0 THEN
        SET curr_level = 1;
        WHILE curr_level <= LevelLimit DO
            SET level_part = CONCAT(':',curr_level);
            SET rv = REPLACE(rv,level_part,'');
            SET curr_level = curr_level + 1;
        END WHILE;
    END IF;

    RETURN rv;
END $$

DELIMITER ;

This is how I changed it: For each id in the queue, I introduce a height.
Each member of the list will look like this:
id:height,id:height,id:height, ... ,id:height

I place a limit on when to concatenate by adding IF front_ht < LevelLimit THEN
When the list is compiled, I then taken the return value rv and remove all colons (:) and the number behind each colon. What's left is a comma-separated list of IDs within the height range set by LevelLimit. If you want the height to be included in the output, remove the Strip away level parts of the output code at the bottom of the Stored Procedure.
Give it a Try !!!
UPDATE 2014-11-29 16:05 EST
It is important to note that you need to make GROUP_CONCAT have enough space to collect a long list of info. Please run this in mysql:
SET GLOBAL group_concat_max_len = 1048576;
SET group_concat_max_len = 1048576;

Then, add this to my.cnf
[mysqld]
group_concat_max_len =1048576;


Postgresql sync databases I am having a postgres database on my portable and on a server. I would like to keep those two in sync as easy as possible. I know about slony but I am not able to figure out:

Does it work when the two servers are not always connected? - I may do changes on the db on my portable when it is not able to reach the other server.

Does it work both ways? Can I update the slave and get the updateds pushed to the master?

Does it sync changes in the db-structure? The database in question is a geographical database, and occasionally I import a new map as a new table, I am also often adding new view for data processing.

If slony is not useable in my case, are there any other usable tools? I've looked at dblink, but that does neither seem to be what I need.

What I know - Slony is Master/Slave replication solution with limited possibility copy structure's changes from master to slave. So this is not usable for your purposes. Probably dblink is not for you too - it is able to execute foreign SQL statement - nothing more.

you probably search tools like this or this


glogin.sql supress error when Instance Idle I have a pl/sql block in my glogin.sql file to show information from v$instance when I connect on the database server.  Is there a way to suppress the ORA-01034 we would naturally get when the instance is idle?  An idle instance reports that it is idle when you connect, so anything additional is not necessary and could be confusing.

This is what I use in my login.sql. Tom Kytes book Expert One on One has this script. No sign of ORA-01034 when instance is down. My prompt only shows idle>

set term off

-- a lot of stuff

REM here is how I set my signature prompt in sqlplus to
REM username@database> I use the NEW_VALUE concept to format
REM a nice prompt string that defaults to IDLE (useful for those
REM of you that use sqlplus to start up their databases - the
REM prompt will default to idle> if your database isn't started)
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name, 1, decode(dot, 0, length(global_name), dot-1)) global_name
from (select global_name, instr(global_name, '.') dot
from global_name);
set sqlprompt '&gname> '

-- a lot of more stuff

set term on


Oracle Database: View schema without access to data In Oracle Database 10g, I need to create a user with these permissions:

user can access schema to be able to read table names and columns
user can not select (get) any data from any table

Is this possible or is there a way for a workaround?

I'd create a procedure returning all tables/columns and grant access to that procedure:

  create or replace package pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor);

  end;

  create or replace package body pkg_ddl_read as

    procedure get_table_cols(v_cur in out sys_refcursor) is
    begin
      open v_cur for
        select owner, table_name, column_name
        from dba_tab_cols;
    end;

  end;

  grant execute on pkg_ddl to <myuser>;

Depending on what privileges the creating user (not the user you want to give access to) has and whether you want to allow the reading user to get the DDL for every table in the database or only for a certain subset, you might want to

use all_tab_cols / user_tab_cols instead of dba_tab_cols
add a where clause to the procedure


Calculate row value based on previous and actual row values Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)

I want to calculate the balance following these rules:

The first row balance (chronologically) = debit - credit and for the rest of the rows

current row balance = chronologically previous row balance + current row debit - current row credit

As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.

Thank you very much for your help.

Assuming that stmnt_date has a UNIQUE constraint, this would be fairly easy with window/analytic functions:

SELECT
    s.stmnt_date, s.debit, s.credit,
    SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
        AS balance
FROM
    statements AS s
ORDER BY
    stmnt_date ;

Unfortunately, MySQL does not (yet) have implemented analytic functions. You can solve the problem either with strict SQL, by self-joining the table (which should be rather inefficient although working 100%) or by using a specific  MySQL feature, variables (which would be quite efficient but you'd have to test it when upgrading mysql, to be sure that the results are still correct and not mangled by some optimization improvement):

SELECT
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0.0) AS dummy
  CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ;

With your data, it will result in:

+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)


Can't create a table with the same name I deleted before I started to use SQL Server recently and I think that its simple to resolve

I did:

1 - Create table table_nome

2 - Drop table table_nome

3 - I'm trying to create a table with the same name, and stay with error in the table_name, written: "There is already an object named 'table_nome' in the database."

How can I fix it and create a table?

if have some errors in the writing, I'm sorry, I'm from brazil and dont know English very well -

I'm using SQL server 2012.

you can do something like this, it will create and drop a table called t1, schema dbo

use tempdb
go

--------------------------------------------------------------------------------
-- creates the table
--------------------------------------------------------------------------------

if exists (
              select *
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          )
          begin

              drop table dbo.t1
               print 'table dropped'
          end
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end
go

--------------------------------------------------------------------------------
-- drops the table
--------------------------------------------------------------------------------

if exists (
              select *
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          )
          begin

              drop table dbo.t1
               print 'table dropped'
          end
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end
go


Export mutliple query output into one excel sheet using Redshift and SQL Developer I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to achieve this.
Thanks

If you want to export the data from Redshift to excel you can use some software like Talend Open Integration or export directly to CSV with the UNLOAD functionality that Redshift has.
https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html


SQL Server - How to know which tables are used and which are not? I have a database that has 225 tables in it

What is the best way to find out / log which tables are used, and which are not ?

I would like to see something similar to below:

Table 1 - select 2222 times, insert 0 times, update 3 times, delete 0 times
Table 2 - select 1 times, insert 50 times, update 0 times, delete 5 times

Or in a below format:

Table 1 - select 2222
Table 1 - update 0
Table 2 - select 1
Table 2 - insert 50
Table 2 - delete 5

SELECT t.name AS [TableName],
       fi.page_count AS [Pages],
       fi.record_count AS [Rows],
       CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes],
       CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent],
       SUM(iop.leaf_insert_count) AS [Inserts],
       SUM(iop.leaf_delete_count) AS [Deletes],
       SUM(iop.leaf_update_count) AS [Updates],
       SUM(iop.row_lock_count) AS [RowLocks],
       SUM(iop.page_lock_count) AS [PageLocks]
  FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
  JOIN sys.indexes AS i ON iop.index_id = i.index_id AND
                           iop.object_id = i.object_id
  JOIN sys.tables AS t ON i.object_id = t.object_id AND
                          i.type_desc IN ('CLUSTERED', 'HEAP')
  JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND
                                                                                fi.index_id=CAST(i.index_id AS int)
  GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
  ORDER BY [RowLocks] desc

Remarks:
sys.dm_db_index_operational_stats

  (How the Counters in the Metadata Cache Are Reset

  The data returned by sys.dm_db_index_operational_stats exists only as
  long as the metadata cache object that represents the heap or index is
  available. This data is neither persistent nor transactionally
  consistent. This means you cannot use these counters to determine
  whether an index has been used or not, or when the index was last
  used. For information about this, see sys.dm_db_index_usage_stats.

  The values for each column are set to zero whenever the metadata for
  the heap or index is brought into the metadata cache and statistics
  are accumulated until the cache object is removed from the metadata
  cache. Therefore, an active heap or index will likely always have its
  metadata in the cache, and the cumulative counts may reflect activity
  since the instance of SQL Server was last started. The metadata for a
  less active heap or index will move in and out of the cache as it is
  used. As a result, it may or may not have values available. Dropping
  an index will cause the corresponding statistics to be removed from
  memory and no longer be reported by the function. Other DDL operations
  against the index may cause the value of the statistics to be reset to
  zero.)


First time running a replication on SQL Server 2008. "The process could not connect to subscriber 'SERVER2'" I've set up Replication between two remote servers, Using Peer to Peer & Transactional updates. The subscription is all set, but the actual replication doesn't take place.

When viewing the Synchronization Status of the subscription, i get an error, saying "The process could not connect to Subscriber 'SERVER2\DBINSTANCE'."

Clicking on start, this is the error message I get:

TITLE: View Synchronization Status

The agent could not be started.

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=CantStartAgent&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQLServerAgent Error: Request to run job SERVER\DBINSTANCE-DBNAME-DBPUBLICATION-SERVER2\DBNAME-3 (from User sa) refused because the job is already running from a request by Start Sequence 0.
Changed database context to 'DBNAME'. (Microsoft SQL Server, Error: 22022)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

Viewing the Agent History, trying to get more specific into the error, i get the following Error:

Error messages:
The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: help/MSSQL_REPL20011
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: help/15517
The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: help/MSSQL_REPL22037

I have looked each and every one of the errors online, went through all this kind of explanations and changes, and nothing worked.
Many thanks to anyone who can pour some light on the issue.

This is likely a permissions issue.

Verify that your replication agent accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.


The pg_wal directory on a standby server does not get auto-purged I have a classic master-slaver PG (version 10) architecture. Despite the fact the parameter wal_keep_segments is set to 200, the pg_wal directory on the standby server is not purged and keeps filling in. Do you have any ideas ? For information, I don't have this issue on the primary server.

Both primary and standby have the same configuration.

On the master:

select * from pg_stat_replication ;
-[ RECORD 1 ]----+----------------------------------------------
pid              | 5399
usesysid         | 16387
usename          | replication
application_name | walreceiver
client_addr      | XXXXXXXXXXXX
client_hostname  | XXXXXXXXXXXX
client_port      | 56780
backend_start    | 2018-11-05 10:18:50.280663+00
backend_xmin     |
state            | streaming
sent_lsn         | 71/E3000000
write_lsn        | 71/E3000000
flush_lsn        | 71/E3000000
replay_lsn       | 71/E3000000
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+----------------------------------------------
pid              | 10175
usesysid         | 16389
usename          | barman_replication
application_name | barman_receive_wal
client_addr      | XXXXXXXXXXXX
client_hostname  | XXXXXXXXXXXX
client_port      | 42572
backend_start    | 2018-11-12 03:09:03.715933+00
backend_xmin     |
state            | streaming
sent_lsn         | 71/E3000000
write_lsn        | 71/E3000000
flush_lsn        | 71/E3000000
replay_lsn       |
write_lag        | 00:00:02.516016
flush_lag        | 00:00:02.516016
replay_lag       | 06:28:11.482478
sync_priority    | 0
sync_state       | async

On the standby:

select * from pg_replication_slots ;
-[ RECORD 1 ]-------+------------
slot_name           | barman
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | f
active_pid          |
xmin                |
catalog_xmin        |
restart_lsn         | 16/9F000000
confirmed_flush_lsn |

The replication slot should not exist on the standby, only on the master.  Unless you are using cascading replication, which you don't seem to be.

If you have a replication slot on the standby but no one is connecting to it to read from and advance it, that explains the retention.

See https://www.postgresql.org/docs/9.6/continuous-archiving.html#BACKUP-BASE-BACKUP:

  It is often a good idea to also omit from the backup the files within
  the cluster's pg_replslot/ directory, so that replication slots that
  exist on the master do not become part of the backup. Otherwise, the
  subsequent use of the backup to create a standby may result in
  indefinite retention of WAL files on the standby


Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)? PostgreSQL supports "Event Triggers" which is defined as,

  To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

SQL Server "DDL Triggers",

  DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

Oracle simply calls them "Triggers". Is there any standardized functionality that provides this functionality?

The SQL Spec currently only says this about Triggers,

  4.6.5.5 Triggers

  A trigger is an object associated with a single base table or a single viewed table. A trigger specifies a subject table, trigger event, a trigger action time, and one or more triggered actions.

  A trigger event specifies what action on the subject table shall cause the triggered actions. A trigger event is either INSERT, DELETE, or UPDATE.
  A trigger action time specifies whether the triggered action is to taken BEFORE, INSTEAD OF, or AFTER the trigger event.
  A triggered action is either an <SQL procedure statement> or BEGIN ATOMIC, followed by one or more <SQL procedure statement>s terminated with <semicolon>s, followed by END.

Without any further mention, I believe the spec does not currently address this functionality at all.

Formatting slightly modified from the spec. I turned it into a list. Spec used "Information technology — Database languages — SQL —
Part 1: Framework (SQL/Framework)"


Unable to remove permission for mysql.user I am trying to remove permission from mysql user to not be able to delete specific database. But if i run this command:

revoke all privileges on test.* from 'demo'@'localhost';

or this

revoke drop on test.* from 'demo'@'localhost';

I get this error

  ERROR 1141 (42000): There is no such grant defined for user 'demo' on host 'localhost'

test is the name of the databse.

The permissions for demo user are this:

mysql> show grants for demo@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for demo@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'demo'@'localhost' IDENTIFIED BY PASSWORD '*C142FB215B6E05B7C134B1A653AD4B455157FD79' |

Also, when i login as user demo i am able to delete the database test.

Why i am getting this error when i try to remove the permissions from user demo?

There is a big reason for this. There are four levels of grants in MySQL

Global (stored in mysql.user)
Database (stored in mysql.db)
Table (stored in mysql.tables_priv)
Column (stored in mysql.columns_priv)

When you ran

show grants for demo@'localhost';

mysqld read the grants and saw demo@'localhost' in mysql.user

When you ran

revoke all privileges on test.* from 'demo'@'localhost';

mysqld looked for the grants, not in mysql.user, but mysql.db.

You cannot yank grants on individual database from a user with global grants.


What indexing to be used over string columns(text type) with string length around 3000 characters I am using open source columnar dbms (postgresql+Cstore).

I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to 3000 characters per value). A linear look up will take it too long to give results. My queries vary from simple '=' to like, contain and wildcards.

I am unable to conclude on what indexing should I use on string columns. I have hash table, trie structure, B+ tree index in mind.

The problem with hash table is collisions and even if use perfect hashing, there are chances of returning false positives. Moreover, hash index cannot solve like, contains and wild card queries. Apart from that, my data have duplicate values in columns like company name etc., and building perfect hash is getting difficult over values with duplicates in them.

Trie and B+ tree: Trie seems useful for all the queries, but as I mentioned, my strings are of huge length. Can trie be scaled to that extent? I have the same question regarding B+ Tree also.

So, to sum up, I have lengthy string columns and I need speed in query execution. Which indexing should I use, or is there any other method to speed up searches, apart from what I've mentioned?

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods?

PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a non-standard block size.

PostgreSQL has hash indexes, but they are discouraged because they are not crash-safe.  And as you say, it would only support '=' operators.

You might be able to use the pg_trgm extension which creates a type of GIN index which can support LIKE queries.  It should be able to accommodate your long strings, but it might not be very effective with them.  But with some C programming you could perhaps customize it to suit your usage.


How to connect to a local server in Oracle SQL Developer like SQL*Plus with no TNSlistener? How can I use SQL Developer to connect to a local Oracle 11g server?  I didn't enable TNS Listener, but SQL*Plus works well.

@update1:

I copied all *.jars from $ORACLE_HOME/app/product/11.2.0/dbhome_2/jdbc/lib/ to jdbc directory in SQLDeveloper

And I get error: no ocijdbc11 in java.library.path

So I traced down sqldeveloper.sh , and add some parameters to it:

However , another error discovered:

Certain wired string kept changing each time i connect

@update2:

If only I add

export CLASSPATH=$CLASSPATH:/Application/Binaries/oracle_11g/app/product/11.2.0/dbhome_2/jdbc/lib

I'll get the following error:

In SQL Developer, use the Local/Bequeath connection type:


Is this a good strategy for importing a large amount of data and decomposing as an ETL? I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of other tables, including inserts that just rely on the values I just inserted into the first tables.

I can do my inserts as an A, B, C process, where I insert into the first table, then insert into some table S where exists in A + T (being some other table that has preloaded "configuration data"), then inserting into Z where exists in B + U, etc.

Should I be trying to batch those inserts with a cursor (I know, stone the traitor) or should I just run the raw insert into scripts and let the server die a thousand heat deaths? Basically I'm worried about starving the server or causing it to collapse from too many index or something else with inserts.

Should I stage out the inserts as:

Insert into one set of tables
Insert into secondary tables based on the first tables
Insert into tertiary tables, etc.

OR should I insert into all the tales where the data is needed but do it via cursor, in a "for loop" style pattern of 100k rows at a time.

FWIW, this is a behavior I saw from the DBAs at my last job, so I figure that's "what I'm supposed to do" (the batch process via cursors) but maybe I don't understand enough about what they were doing (they were also live-loading into systems that already had data, and were loading new data afterwards).

Also bear in mind that I'm normally a C# dev, but I've got the most TSQL experience here and I'm trying to make the best process I can for raw-loading this data as opposed to our "current" method that is mostly webservice fetches and NHibernate save-commits.

Things I think are important to the question:

There will be no other load on this server when I do this, I will have complete and total control of the box, and I'm the only user interested here (this is the initial data load before anyone else can do anything with the box)
There are indexes on all the tables, where appropriate, and there are FKs and other predicates
The entire db system is in use for slews of other clients right now, so I know the indexes are needed for operation, but if I should do something to disable those and re-enable them to make the inserts etc faster, I'm looking for those sorts of optimization hacks.
I'm waiting on sample data but I expect the maximum records in a given table to be about a million rows, fairly wide, and that I'm going to insert into those other tables at most a million rows for the ancillary tables that can come secondary or tertiary.
The hardware is "average".

If you are confident in the integrity of the data being imported, it may be a good idea to disable all the constraints to your database before beginning your inserts and then re-enabling them after the fact.

See this helpful stack overflow answer from awhile back: Can foreign key constraints be temporarily disabled using T-SQL?

This will save you the head ache of having to worry about layering the inserts in order to respect the existing constraints of the database you are loading into.

In terms of the actual inserts themselves, I'd be on the side of not using cursors.  Not only is the process slow but they take up a large amount off memory and create db locks.  If you are cursor-ing through a very large amount of rows you also run the risk of very quickly escalating the size of the database logs.  If the server is only an average one then, space may eventually be a concern.  Try to consider a more set based approach when doing the additional inserts needed for your process.

example, if you can do this:

insert into t1 (col1)
SELECT col1 FROM t2

instead of this:

...
insert into t1 (col1) values ('foo');
insert into t1 (col1) values ('bar');
insert into t1 (col1 values
...


Update a column for every row of a large table to a unique value in a set range We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier.
These products are displayed by our e-commerce site, and because of the nature of the business, products are always ordered by the product 'last updated' date. This is a column on the products table called LastUpdated.
This gives suppliers whose products were updated last an unfair advantage.
To get around this problem, we shuffle the products by setting the LastUpdated datetime column to a random datetime value over a 4 hour period. The problem is that this process takes about 15 hours. We must also ensure that the suppliers are spread out evenly, so suppliers' products are not bunched together when queried by the website.
We can't change the way the website queries the data (always by LastUpdated DESC), because of the way other parts of the business work, so we can't randomise when querying the data.
We are looking for a faster way to update the LastUpdated column, for every table row, to a unique datetime covering a 4 hour window, that also prevents supplier products from being bunched together when ordering by LastUpdated.
Example

Note: Some suppliers have a different number of products, so any bunching of supplier records must be as random as possible towards the bottom of the queried results set.
To clarify, we have a very slow query using cursors to do this product shuffling, but we feel there is a better way to do this, that does not take 15 hours.
Update: Vladimir's answer is excellent, as is the comment from Shoeless, but what about part of the question ensuring suppliers products are not bunched together when randomising?
Update: Some people have questioned why the LastUpdated order by is so important. Suppliers pay our website to display these products (approx. 10,000 products per subscription), but they can also pay for a limited number of these products to remain at the top of the results for a user's search criteria. If multiple suppliers want this top spot, then we have to rotate the order of these at the top of the results. We do this base on a queue system, based on an updated date. There could be 30 suppliers all paying for the same top spot for the chosen criteria.

Four hours is only 14,400 seconds and you have 3M rows.
If we generate random timestamp with 1 second precision within 4 hour interval we'll get approximately 3,000,000 / 14,400 ~= 208 rows for each second.
To reduce the number of rows that have exactly the same timestamp we should use fractions of the second.
If you use datetime type, its precision is about 3 milliseconds, which should be good enough.

So, for each row of the table I'll generate a random timestamp within a 4 hour interval with a millisecond precision,
which will be rounded to the precision of the datetime type.

I will use CRYPT_GEN_RANDOM function for it.

For example,

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

This generates 4 random bytes as varbinary. We have to explicitly cast them to int first. Then result is transformed into a float number between 0 and 1.
This can be miltiplied by the size of the window in milliseconds and added to the timestamp that defines the start of the window.

Sample data

DECLARE @T TABLE
(ID int, Supplier nvarchar(50), ProductCode char(5), LastUpdated datetime);

INSERT INTO @T (ID, Supplier, ProductCode, LastUpdated) VALUES
(1, 'Acme',  '00001', '2016-03-23 06:00:00'),
(2, 'Acme',  '00002', '2016-03-23 06:00:00'),
(3, 'XYZ',   '00023', '2016-03-23 05:30:00'),
(4, 'XYZ',   '00055', '2016-03-23 05:30:00'),
(5, 'Q & B', '00453', '2016-03-23 04:15:00'),
(6, 'Q & B', '00045', '2016-03-23 04:15:00');

SELECT * FROM @T ORDER BY ID;

+----+----------+-------------+-------------------------+
| ID | Supplier | ProductCode |       LastUpdated       |
+----+----------+-------------+-------------------------+
|  1 | Acme     |       00001 | 2016-03-23 06:00:00.000 |
|  2 | Acme     |       00002 | 2016-03-23 06:00:00.000 |
|  3 | XYZ      |       00023 | 2016-03-23 05:30:00.000 |
|  4 | XYZ      |       00055 | 2016-03-23 05:30:00.000 |
|  5 | Q & B    |       00453 | 2016-03-23 04:15:00.000 |
|  6 | Q & B    |       00045 | 2016-03-23 04:15:00.000 |
+----+----------+-------------+-------------------------+

Query

DECLARE @VarWindowStart datetime = '2016-03-23 06:00:00';
DECLARE @VarWindowSizeMilliseconds int = 4 * 3600 * 1000;

UPDATE @T
SET LastUpdated =
    DATEADD(millisecond,
    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * @VarWindowSizeMilliseconds,
    @VarWindowStart);

SELECT *
FROM @T
ORDER BY LastUpdated;

Result

+----+----------+-------------+-------------------------+
| ID | Supplier | ProductCode |       LastUpdated       |
+----+----------+-------------+-------------------------+
|  2 | Acme     |       00002 | 2016-03-23 06:02:57.260 |
|  6 | Q & B    |       00045 | 2016-03-23 06:48:07.203 |
|  3 | XYZ      |       00023 | 2016-03-23 06:52:38.813 |
|  1 | Acme     |       00001 | 2016-03-23 07:20:16.750 |
|  4 | XYZ      |       00055 | 2016-03-23 07:23:21.140 |
|  5 | Q & B    |       00453 | 2016-03-23 08:59:53.303 |
+----+----------+-------------+-------------------------+

I'm pretty sure it will take less than 15 hours to process 3M rows using this query.

To clarify, this method doesn't guarantee that generated values in LastUpdated will be unique. There is a pretty good chance that for 3M rows there will be few duplicates and I'm too lazy now to calculate the probability, but I think that for your purposes the result will be good enough.

You can use datetime2(7) with a microsecond precision, which would substantially reduce the probability of collisions. There still will be a chance of collision with this method. If this is not acceptable, you should use some other method.

Update

I will try to guess what you mean by

  We must also ensure that the suppliers are spread out evenly, so
  suppliers' products are not bunched together

The query above generates random number for each row independently. As a result, if your table has, for example, supplier A with 100 rows and supplier B with 10 rows, you would get ~10 rows from A, then 1 row from B, then another ~10 rows from A, then 1 row from B, etc (with various random fluctuations, of course).

This result will look like rows from A are clustered together at the top (and at the bottom, which is not so important).

If you want to guarantee that when table has N suppliers, the first N rows are all from N different suppliers, the next N rows are again from N different suppliers, etc., then below is one way to achieve it.

In the example with suppliers A and B you may want to have at first 2 random rows from A & B, then another 2 random rows from A & B, and so on for 10 pairs of rows, then the rest 90 rows from A in random order. This result will look thoroughly shuffled at the top between suppliers, while it will have only supplier A at the bottom.

I'll extend your sample data slightly.

DECLARE @T TABLE
(ID int, Supplier nvarchar(50), ProductCode char(5), LastUpdated datetime);

INSERT INTO @T (ID, Supplier, ProductCode, LastUpdated) VALUES
(1, 'Acme',  '00001', '2016-03-23 06:00:00'),
(2, 'Acme',  '00002', '2016-03-23 06:00:00'),
(3, 'XYZ',   '00023', '2016-03-23 05:30:00'),
(4, 'XYZ',   '00055', '2016-03-23 05:30:00'),
(5, 'Q & B', '00453', '2016-03-23 04:15:00'),
(6, 'Q & B', '00045', '2016-03-23 04:15:00'),
(10, 'ABC',   '00010', '2016-03-23 06:30:00'),
(11, 'ABC',   '00011', '2016-03-23 06:30:00'),
(12, 'ABC',   '00012', '2016-03-23 06:30:00'),
(13, 'ABC',   '00013', '2016-03-23 06:30:00'),
(20, 'QWE',   '00020', '2016-03-23 02:30:00'),
(21, 'QWE',   '00021', '2016-03-23 02:30:00'),
(22, 'QWE',   '00022', '2016-03-23 02:30:00'),
(23, 'QWE',   '00023', '2016-03-23 02:30:00'),
(24, 'QWE',   '00024', '2016-03-23 02:30:00'),
(25, 'QWE',   '00025', '2016-03-23 02:30:00'),
(26, 'QWE',   '00026', '2016-03-23 02:30:00');

Query

WITH
CTE_Rnd
AS
(
    SELECT
        ID
        ,Supplier
        ,ProductCode
        ,LastUpdated
        ,CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5 AS RandomNo
    FROM @T
)
SELECT
    ID
    ,Supplier
    ,ProductCode AS Code
    ,LastUpdated
    ,RandomNo
    ,ROW_NUMBER() OVER (PARTITION BY Supplier ORDER BY RandomNo) AS RowNo
FROM CTE_Rnd
ORDER BY RowNo, RandomNo
;

Here we assign a random number for each row and partition all rows by supplier, so that we get one random row from each supplier first, then another random row from each supplier again, and so on.

Result

+----+----------+-------+-------------------------+----------------+-------+
| ID | Supplier | Code  |       LastUpdated       |    RandomNo    | RowNo |
+----+----------+-------+-------------------------+----------------+-------+
|  5 | Q & B    | 00453 | 2016-03-23 04:15:00.000 | 0.027009220917 |     1 |
| 10 | ABC      | 00010 | 2016-03-23 06:30:00.000 | 0.091210213162 |     1 |
| 24 | QWE      | 00024 | 2016-03-23 02:30:00.000 | 0.128781275971 |     1 |
|  3 | XYZ      | 00023 | 2016-03-23 05:30:00.000 | 0.248359622609 |     1 |
|  1 | Acme     | 00001 | 2016-03-23 06:00:00.000 | 0.347667260759 |     1 |
| 13 | ABC      | 00013 | 2016-03-23 06:30:00.000 | 0.154523770012 |     2 |
| 20 | QWE      | 00020 | 2016-03-23 02:30:00.000 | 0.211252812229 |     2 |
|  2 | Acme     | 00002 | 2016-03-23 06:00:00.000 | 0.580557575235 |     2 |
|  6 | Q & B    | 00045 | 2016-03-23 04:15:00.000 | 0.921966472273 |     2 |
|  4 | XYZ      | 00055 | 2016-03-23 05:30:00.000 | 0.952229986538 |     2 |
| 11 | ABC      | 00011 | 2016-03-23 06:30:00.000 | 0.425760054944 |     3 |
| 23 | QWE      | 00023 | 2016-03-23 02:30:00.000 | 0.455146492682 |     3 |
| 21 | QWE      | 00021 | 2016-03-23 02:30:00.000 | 0.485142913877 |     4 |
| 12 | ABC      | 00012 | 2016-03-23 06:30:00.000 | 0.554860722984 |     4 |
| 25 | QWE      | 00025 | 2016-03-23 02:30:00.000 | 0.668510247526 |     5 |
| 22 | QWE      | 00022 | 2016-03-23 02:30:00.000 | 0.815049941724 |     6 |
| 26 | QWE      | 00026 | 2016-03-23 02:30:00.000 | 0.818780491668 |     7 |
+----+----------+-------+-------------------------+----------------+-------+

As you can see top 5 rows are all from different suppliers in random order. Next 5 rows are again from different suppliers in random order. Then we have only supliers ABC and QWE left, and finally only supplier QWE.

I will leave it to you to figure out how to combine two numbers from RowNo and RandomNo columns into one datetime value in LastUpdated. RowNo could become number of minutes and RandomNo could become seconds plus milliseconds.


Seems like you are using an integer column flag where a series of BIT columns or - even better - an intersection table should be used.

The index on (flag) is effectively useless for the condition where (flag & 0x40) != 0. Even if it was used, the whole index would have to be scanned first, then all the values computed (with & 0x40) and compared to 0. After that, the matching rows would have to be fetched from the table, resulting in multiple, even millions of seeks. The optimizer is clever enough to do a simple - and fast but still rather slow - whole table scan.

You could improve the efficiency somewhat with a "covering" index on (flag, email). That index would be used but only with a full index scan - you probably can't avoid that with the current design.

Other suggestions, some involving altering the table:

If you can split the flags to multiple BIT columns, the query could be simplified and the possibility of using indexes would arise. Still, you might end up needing multiple (composite) indexes, one for each BIT flag.
If the possible combinations of flags is low (as the cardinality = 18 suggests), you could use that info in your favour by changing the condition to:

where flag in (64, 65, 66, 68, 72, 80, ..., 192, 193, ...)
-- the list of all possible flag values that have the proper bit set

This might use the (flag, email) index slightly better than before, especially if this list is small, still scanning a small or large part of the index but not the whole index.
A much better design would be to use an intersection (many-to-many) table to store which users have some attribute, instead of using flags.


How can I generate all trailing substrings following a delimeter? Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.

For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, I suppose), I want to generate an array like:

{a.b.c.d.e, b.c.d.e, c.d.e, d.e, e}

The intended usage is as a trigger to fill a column for easier querying of domain name parts (i.e. find all q.x.t.com for query t.com) whenever another column is written to.

It seems like an awkward way to solve this (and it very well may be), but now I'm curious how a function like this could be written in (Postgres') SQL.

These are email domain names so it's hard to say what the maximum possible number of elements is, but certainly the vast majority would be < 5.


Database structure for object with dynamic attributes I am developing a proof of concept for a personalized email sending web application (like MailChimp, but with premade dynamic templates). The general structure is that I have my templates (different email templates that the user can choose from), the email object that the user is creating (from/to/subject), and his list of recipients with different fields (firstname/lastname/email address/etc). The web application will be multi-tenant so we need to have a flexible structure. My problem is the following:
Depending on the template the user chooses, the recipients will need more or less properties (home address/insurance type/promo code/gender/etc). One template can require only the basic recipient information and another one could require 50 different attributes. I will have a table Templates, a table PersonalizedFields with the TemplateID in it, so I can know which fields are required for my recipient datasource.
I am hesitating for the structure of the recipient object/table. At first I thought about E-A-V, but after reading about it, I am not so sure it is the best structure. I will be using Entity framework to interact with the database and one list of recipients can have 50k record with 0 to 50 (fringe cases) additional properties. The structure would be like this:

Another structure I am considering is putting the additional properties in a XML column, which will be convenient because the email composition tool I am planning on using takes XML but I will still need to process it:

Or maybe a third structure I am not aware of?
The original data will be read from an XLS(X)/CSV file and then written to the database, and later on read from the database to be formatted in XML strings.
What would be the best data structure?
The technology used for the proof of concept and the future project will be C#, ASP.NET MVC for the web application, ASP.NET MVC API for the service that will do the heavy lifting of the processing, and SQL Server 2008 for the database. Entity Framework will be used to interact with it.

It looks like you want to fill out the email templates using only the unique data for each recipient.  If this is true then I would recommend option 3.  I would have a recipient table that is not directly linked to the email, but is instead linked to the email via a join table.  I would also put a FK on the recipient table to the email sender.  Then I would basically follow your EAV model.

This way your "recipient" table is more like a contact table for the sender.  The EAV model allows you to easily select the custom data for the templates, gives you the ability to painlessly extend the data points, and gives you the opportunity to allow your users to create their own templates using the fields they want.  The senders also win because they can reuse the 50+ data points for future emails because we decoupled the recipient table from the email.


Some tables not found after moving MySQL database from Linux to Windows I had a MySQL database which I was working with from Linux. Some of the schema/table names were in mixed case. I then needed to work with the same database on Windows, but because of the enormous size, doing mysqldump simply did not work (I know it's usually the best option, but I spent many days trying to make it work, to no avail). So, I copied the data files to an external drive and then set the Windows machine to look there when starting the MySQL server.

This worked great, except that I hadn't realized the mixed casing would be a problem on Windows. I can see the schemas/tables in the MySQL Workbench Navigator, but cannot perform any operations on them. If I try, I get error code 1146: Table 'x' doesn't exist.

The original database I was using on Linux, unfortunately, has been corrupted since then, so I am unable to go back and change the case of the schemas/tables there. I tried having the Linux machine read the copied data with the goal of changing the mixed case names, but it is no longer able to read the copied data since it's been modified on the Windows machine.

Is there some way I can enforce case sensitivity on Windows? Or is there some way I can change the cases of the schemas and table names without using MySQL (since it thinks those tables don't exist)? Any other solutions? I really need the data in the mixed case schemas/tables but currently see no way of getting it out.

You can force case sensitivity with lower_case_table_names

Add this to your my.ini

[mysqld]
lower_case_table_names=0

Since lower_case_table_names is not dynamic, you must then restart mysql as Administrator with

C:\> net stop mysql
C:\> net start mysql

The MySQL Documentation says the following

  You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On OS X, the default value is 2.

Please read my 2.5 year old post Is it okay to have different lower_case_table_names value on master and slave? for more clarification.

Please backup the data folder before doing any of this

You might have more issues if the tablename mentioned in constraints are case sensitive. You will have to post that as a separate question because it requires a deeper dive.


Running SSIS jobs sequentially in T-SQL We are loading ETL data in two parts, first with an SSIS package and then stored procedure.

In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 Stored procedure to run only After completion of Part1 SSIS?

Is there a way to conduct this in T-SQL? Should we use delay function and check every few minutes with SSIS dmv SSISDB.catalog.executions if package is complete? Is there another way?

Background:
We can place sproc in a control flow arrow after SSIS. However, our company does not want to conduct this. We are more of a script based company. Additionally trying to refrain from SQL agent jobs. Currently QA department needs a way to run items, without creating multiple jobs. We created jobs for parent level packages, however do not want to create unit jobs for our 500+ SSIS and sprocs.

--Part 1
EXEC [SSISDB].[catalog].[create_execution]
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

select @execution_id

--Part 2
execute Customer_StageTable_Part2_Insert

https://www.sqlshack.com/execute-deployed-package-ssis-catalog-various-options/

I think that approach will just complicate your task.

What I'm thinking is a validation in a table, when your package starts insert something and then when it's completed insert something else, and then your "part2" when executed will look to the table if it's over execute this other wise wait for 5 minutes and execute this same check again.


Should I query from a snapshot or use a backup for reporting purposes I have a very large select query for reporting purposes that can take up to 1-2 hours to run.

There are a few issues with this query.

It takes up too much system resources and this server can't handle the load, the DB is also shared on the same server with 30 other dbs.
The query is very large and read/write performance suffers, sometimes the tables are locked so I can't update.

The database is used in an application and for reporting purposes. We have another server in a different location where we can host the DB.

The application runs fine as is on this server, the issue comes in with the reporting. My solution is to

solution 1:

Create the same DB on the other server for reporting purposes.
Run timed jobs to to place a backup file in a shared location
Back up the DB from the back up file that is stored in the shared location.

One of the DBA's is telling me this is too much work unless

solution 2:

We move the application DB to the other server.
Create the copy DB and host both DBs on the same server.
Run a timed job for back ups
And then Back up the reporting DB.

I fail to see how this is a too work. I do DB back ups all the time but I've never done one from a file in a shared location. But it seems like it's the same process to me.

If solution 2 is in fact a more hassle free solution, why is that so?

Solution 2 is very confusing as written and your question seems to start with query performance and moves into backup best practices. In solution 2, you still have your app DB and your new reporting DB on the same server. This means you'll still have the same resource contention (memory, CPU). You won't have the same blocking issues since you did create a reporting copy of your. This has nothing to do with the backups though.

Solution 1 is very common. You can even keep the data more "up to date" with log shipping. There's lots of information on this site about that.

Regarding your backups, some general advice would be

Run your backups from your App DB (at least). This is the data you really care about.
Back it up to wherever you want. Ideally not the same drive your data files are on. Corruption on these drives could mean corruption on your data files, and backups, leaving you dead in the water for recovery
Schedule your backups. You shouldn't be doing this manually. The frequency of your backups should be based on your RPO and RTO.


Arbitrarily ordering records in a table A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of relationships, so a relational database seems to make sense.

The common solution that I have seen is to add an integer column order:

CREATE TABLE AS your_table (id, title, sort_order)
AS VALUES
  (0, 'Lorem ipsum',   3),
  (1, 'Dolor sit',     2),
  (2, 'Amet, consect', 0),
  (3, 'Elit fusce',    1);

Then, we can sort the rows by order to get them in the proper order.

However, this seems clumsy:

If I want to move record 0 to the start, I have to reorder every record
If I want to insert a new record in the middle, I have to reorder every record after it
If I want to remove a record, I have to reorder every record after it

It's easy to imagine a situations like:

Two records have the same order
There are gaps in the order between records

These could happen fairly easily for a number of reasons.

This is the approach that applications like Joomla take:

You could argue that the interface here is bad, and that instead of humans directly editing numbers, they should use arrows or drag-and-drop—and you'd probably be right. But behind the scenes, the same thing is happening.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

Is there a better way to store order in a table?

If I want to move record 0 to the start, I have to reorder every record

No, there's a simpler way.
update your_table
set order = -1
where id = 0;

If I want to insert a new record in the middle, I have to reorder every record after it

That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update a value to, say, 2.5. But varchar(n) works, too.  (Think 'a', 'b', 'c'; then think 'ba', 'bb', 'bc'.)

If I want to remove a record, I have to reorder every record after it

No, there's a simpler way. Just delete the row. The remaining rows will still sort correctly.

It's easy to imagine a situations like:
Two records have the same order

A unique constraint can prevent that.

There are gaps in the order between records

Gaps have no effect on how a dbms sorts values in a column.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

You don't stop until you have to. The dbms has no problem sorting values that have 2, 7, or 15 places after the decimal point.
I think your real problem is that you'd like to see values in sorted order as integers. You can do that.
create table your_table (
  id int primary key,
  title varchar(13),
  sort_order float
);

insert into your_table values
(0, 'Lorem ipsum', 2.0),
(1, 'Dolor sit', 1.5),
(2, 'Amet, consect', 0.0),
(3, 'Elit fusce', 1.0);

-- This windowing function will "transform" the floats into sorted integers.
select id, title,
       row_number() over (order by sort_order)
from your_table


Create users identified by the same password as another user in MariaDB 10.0.X How do I create users with the same password as another user in MariaDB 10.x?

I do not know the password of the existing user , so i want to use same hashed password as existing, also do need to replica of the user with different host , meaning same grants.

CREATE USER has number of forms. You can provide the plain password as well as already hashed value stored in the engine:

authentication_option:
  IDENTIFIED BY 'string'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_plugin
| IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} 'authentication_string'


Why doesn’t this recursive CTE with a parameter use an index when it does with a literal? I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the CTE just to that value, giving a query plan with low actual rows counts, et cetera:

However, if I pass the value as a parameter, it seems to realize (spool) the CTE and then filter it after the fact:

I could be reading the plans wrong. I haven’t noticed a performance issue, but I am worried that realization of the CTE could cause issues with larger data sets, especially in a busier system. Also, I normally compound this traversal on itself: I traverse up to ancestors and back down to descendants (to ensure that I gather all related nodes). Due to how my data is, each set of “related” nodes is rather small, so realization of the CTE doesn’t make sense. And when SQL Server seems to realize the CTE, it is giving me some quite large numbers in its “actual” counts.

Is there a way to get the parameterized version of the query to act like the literal version? I want to put the CTE in a reusable view.

Query with literal:

CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

Query with parameter:

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

Setup code:

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case you are interested in the details.

SQL Server rewrites tail-recursive common table expressions (CTEs) as iteration. Everything from the Lazy Index Spool down is the runtime implementation of the iterative translation. I wrote a detailed account of how this section of an execution plan works in answer to Using EXCEPT in a recursive common table expression.

You want to specify a predicate (filter) outside the CTE and have the query optimizer push this filter down inside the recursion (rewritten as iteration) and have it applied to the anchor member. This will mean the recursion starts with only those records that match ParentId = @Id.

This is quite a reasonable expectation, whether a literal value, variable, or parameter is used; however, the optimizer can only do things for which rules have been written. Rules specify how a logical query tree is modified to achieve a particular transformation. They include logic to make sure the end result is safe - i.e. it returns exactly the same data as the original query specification in all possible cases.

The rule responsible for pushing predicates on a recursive CTE is called SelOnIterator - a relational selection (= predicate) on an iterator implementing recursion. More precisely, this rule can copy a selection down to the anchor part of recursive iteration:

Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

This rule can be disabled with the undocumented hint OPTION(QUERYRULEOFF SelOnIterator). When this is used, the optimizer can no longer push predicates with a literal value down to the anchor of a recursive CTE. You don't want that, but it illustrates the point.

Originally, this rule was limited to working on predicates with literal values only. It could also be made to work with variables or parameters by specifying OPTION (RECOMPILE), since that hint enables the Parameter Embedding Optimization, whereby the runtime literal value of the variable (or parameter) is used when compiling the plan. The plan is not cached, so the downside of this is a fresh compilation on each execution.

At some point, the SelOnIterator rule was improved to also work with variables and parameters. To avoid unexpected plan changes, this was protected under the 4199 trace flag, database compatibility level, and query optimizer hotfix compatibility level. This is quite a normal pattern for optimizer improvements, which are not always documented. Improvements are normally good for most people, but there is always a chance that any change will introduce a regression for someone.

  I want to put the CTE in a reusable view

You could use an inline table-valued function instead of a view. Provide the value you want to push down as a parameter, and place the predicate in the recursive anchor member.

If you prefer, enabling trace flag 4199 globally is also an option. There are many optimizer changes covered by this flag, so you would need to carefully test your workload with it enabled, and be prepared to handle regressions.


SQL problem with checking next and previous rows I have a list and the returned table looks like this. I took the preview of only one car but there are many more.

What I need to do now is check that the current KM value is larger then the previous and smaller then the next. If this is not the case I need to make a field called Trustworthy and should fill it with either 1 or 0 (true/ false).

The result that I have so far is this:

validKMstand and validkmstand2 are how I calculate it. It did not work in one list so that is why I separated it.

In both of my tries my code does not work.

15756 should be 0 in validkmstand2 as it is larger than the next value.
15757 should be 0 in validKMstand as it is smaller than the previous value

Here is the code that I have so far.

 FullList as (
SELECT
    *
FROM
eMK_Mileage as Mileage
)

, ValidChecked1 as (
SELECT
        UL1.*,
        CASE WHEN EXISTS(
            SELECT TOP(1)UL2.*
            FROM FullList AS UL2
            WHERE
                UL2.FK_CarID = UL1.FK_CarID AND
                UL1.KM_Date > UL2.KM_Date AND
                UL1.KM > UL2.KM
            ORDER BY UL2.KM_Date DESC
        )
        THEN 1
        ELSE 0
        END AS validkmstand
FROM FullList as UL1
)

, ValidChecked2 as (
SELECT
        List1.*,
        (CASE   WHEN List1.KM > ulprev.KM
                THEN 1
                ELSE 0
                END
        ) AS validkmstand2
FROM ValidChecked1 as List1 outer apply
    (SELECT TOP(1)UL3.*
            FROM ValidChecked1 AS UL3
            WHERE
                UL3.FK_CarID = List1.FK_CarID AND
                UL3.KM_Date <= List1.KM_Date AND
                List1.KM > UL3.KM
            ORDER BY UL3.KM_Date DESC) ulprev
)

SELECT * FROM ValidChecked2 order by FK_CarID, KM_Date

How about a rewrite of it?

;with eMK_Mileage
AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL
SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL
SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL
SELECT 15758,'8E79F' , 72055 , '20140822')
,FullList
AS (
 SELECT  * FROM eMK_Mileage as Mileage
)
-- FINAL SELECT
SELECT
*
,CASE WHEN FL.KM < ISNULL(P.KM_PREV,FL.KM) THEN 0 ELSE 1 END AS validKMstand
,CASE WHEN FL.KM > ISNULL(N.KM_NEXT,FL.KM) THEN 0 ELSE 1 END AS validKMstand2
FROM
FullList AS FL
OUTER APPLY
--PREVIOUS ROW
(SELECT TOP 1
        KM AS KM_PREV
    FROM FullList AS PREV
    WHERE
        FL.FK_CarID = PREV.FK_CarID
        AND FL.KM_Date>=PREV.KM_Date
        AND FL.ID>PREV.ID
    ORDER BY
        KM_Date DESC, ID DESC
)AS P
--NEXT ROW
OUTER APPLY
(SELECT TOP 1
        KM AS KM_NEXT
    FROM FullList AS NXT
    WHERE
        NXT.FK_CarID = FL.FK_CarID
        AND NXT.KM_Date>=FL.KM_Date
        AND NXT.ID > FL.ID
    ORDER BY
        KM_Date ASC, ID ASC
)AS N
ORDER BY ID,KM_Date

the output, for my sample is this:

ID  FK_CarID    KM  KM_Date KM_PREV KM_NEXT validKMstand    validKMstand2
15755   8E79F   69646   20140716    NULL    70393   1   1
15756   8E79F   70393   20140726    69646   58258   1   0
15757   8E79F   58258   20140822    70393   72055   0   1
15758   8E79F   72055   20140822    58258   NULL    1   1


Version control for database objects The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be responsible for db changes only. So we need a source safe for database objects, something like version control for standard code, but more related to database, that can synchronize database and scripts. What is the best one. Reliable, Cheap, Functional - choose the two ones :)

Red Gate SQL Server Source Control
Visual Studio Database Projects
Developer discipline


Getting the contents of a json file as a variable in plpgsql? I have a function based this post by Sean Huber in order to get the content of a file:

CREATE FUNCTION file_read(file text)     RETURNS void AS $$
DECLARE
  content text;
  tmp text;
BEGIN
  file := quote_literal(file);
  tmp := 'tmp_table';

  EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)';
  EXECUTE 'COPY ' || tmp || ' FROM ' || file;
  EXECUTE 'SELECT content FROM ' || tmp INTO content;
    **Do some more stuff here**
  EXECUTE 'DROP TABLE ' || tmp;

END;   $$ LANGUAGE plpgsql VOLATILE;

I'm not really happy with this as it is doing so much more work than necessary. I'd prefer not to create/drop relations cause all I really want to do is run Postgres' JSON functions against the content of some .json file. Does anyone know of a better way to do this without using psql?

If the file contains a valid JSON literal, you could read it in with pg_read_file() and assign to a json variable directly:

CREATE OR REPLACE FUNCTION file_read(file text)
  RETURNS void AS
$func$
DECLARE
   content json := pg_read_file(file, 0, 10000000);  -- arbitrary max. 10 MB
BEGIN
   -- do some more stuff here
END
$func$  LANGUAGE plpgsql;

But that requires superuser privileges for reasons explained in the manual where I linked.

You could make that a SECURITY DEFINER function, owned by a superuser, but be very careful who to grant the EXECUTE privilege then. Example:

Creating UUID extension as the non super privileged user


Concept of Schema in PostgreSQL I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?

Can it affect me in future if I decide to not think about them right now and worry about them later?

Explanation with an example will be nice.

Schemas in postgres are used mostly for namespacing and sometimes for security.

Namespacing because two objects can have the same name in different schemas, and are then referenced by schema.object notation - especially useful in conjunction with search_path (many contrib modules do this, eg xml2). Security because you can now do grant ... on all tables in schema.