pgAdmin 4 Not connected to the server I've installed Postgresql 11 and pgAdmin4 (version 4.5) and while I'm able to create tables in a new database, if I attempt to SELECT or INSERT into those tables I always get a message 'Not connected to the server or the connection to the server has been closed.' in the "Messages" tab in pgAdmin 4.

If, however, I open the SQL Shell I can execute a SELECT on a table that I've attempted to insert into from pgAdmin and it shows that the rows have been inserted.

I have exactly the same problem with 4.5 & 4.6. Postgres 10 server on Ubuntu, PG Admin on Windows 7. I downgraded PG Admin to 4.3 and it works.


How does recovery work when server is shutdown before a checkpoint? So for any query that makes changes to data:

SQL Server pulls the page from disk into memory if it doesn't already exist in memory.
Lock the page/row and make the data changes.
Writes the changed to the log in memory.
Release the page/row lock.
Send acknowledgement to user

Then, whenever there is a checkpoint:

Write the log to disk
Write dirty page to disk

Suppose the server gets shutdown due to power failure just before a checkpoint. Then is the data from memory that wasn't burnt to disk lost? Or does SQL Server write the log to disk before sending acknowledgement to user?

In full durability, the default, the commit completes only when the data is physically written to the log file - it's only data pages that are later written to disk on checkpoint by the lazy writer.
There's a per-database setting called delayed durability that can override and allow the transaction to be committed but only written to the log file when the buffer is full, offering faster transactions but the risk of losing (as yet) unwritten committed transactions.
More info


How to remove a database server from Utility Explorer I use the Utility Explorer to monitor some of my databases but I forgot to remove one before retiring the server. Normally, you could just right click and select "Remove Managed Instance" but I can't do that since the server has been wiped from existence. Now it is stuck in my list without any data

I've recently had to drop a defunct instance from a UCP.
Here's how:

DECLARE @instance_id int;

SELECT @instance_id = mi.instance_id
FROM msdb.dbo.sysutility_ucp_managed_instances AS mi
WHERE mi.instance_name = 'defunctservrname\instancename';

EXEC msdb.dbo.sp_sysutility_ucp_remove_mi @instance_id;


db2 SQL0930N error on linux - not enough space to process this statement This is another newbie question on db2, related to space.
How to I start diagnosing and then solving what the problem is so that users can connect to this database?
This error is displayed when both connecting to an existing database as well as when creating a new database.

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/mylogin/sqllib
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

db2 => connect to sample
SQL0930N  There is not enough storage available to process the statement.
SQLSTATE=57011
db2 => !uname -a
Linux myhostname 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
db2 => create database newdb
SQL0930N  There is not enough storage available to process the statement.
SQLSTATE=57011
db2 =>

Check db2diag.log around the time of the error -- you will see what memory pool is exhausted or misconfigured.


How slow is slow in postgres? I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now working with big data (300-750gb) for the first time.

As a result, I have no concept of how long things should be taking. I think my database is performing very poorly, but having never worked on these scales I don't really know.

So here's my question: even basic queries are taking me at least 8 hours on a 237gb table. Vacuum takes ~6 hours. And a query pulling out distinct pairs of values:

CREATE TABLE UserPairs AS
SELECT DISTINCT a, group_a, sum(quantity) FROM cdr GROUP BY (a, group_a) HAVING type = 'DATA' AND group_a IS NOT NULL;

ran for 8 hours before I aborted.

An attempt to build 4 hash indices over main columns ran for 24 hours then crashed.

Hardware: 3 cores, 12 gb ram Windows 8 server VM. (I know, but I don't have control over my hardware. Long story).

So basically: within an order of magnitude, how long should I expect basic queries to take in postgres for tables of this size?

And if this seems way off, how do I get more precise benchmarks? I'm running pgbench now, but can't find resources on how to interpret the results. This listserv exchange suggests there aren't any repositories of results...

1.5 billion rows.
Settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb.
IO System: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16.

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc.

Then work towards meeting those targets. That is the "normal" performance you want. Now go about meeting those targets. The database may be your bottleneck, it may not be. To identify issues at the database side, try using a tool like pgbadger. That will tell you which queries are taking time.

By the way, 8 hours for a query is probably not acceptable under any circumstance. Try the tool pgtune and see if there is scope for optimizing parameters.


How to real time replicate a SQL Server DB without primary keys defined Is there a way to replicate a SQL Server database real time without primary keys defined?

I understand we can't use the SQL transactional replication because of lacking primary keys. Is there any 3rd party tool that can do that?

It is unlikely that any replication solution you can import would work without a PK (or at least a unique index) otherwise it can't tell the difference between an update and a delete+insert (amongst other problems) which could be quite a significant issue depending on what keys and triggers exist on or refer to the affected table(s).

If you can't add a PK or other unique index then you will need to try write your own synchronisation solution for the situation.

Is there any reason why the table(s) can't have a primary key?


Comparing multiple tables on two databases using dynamic PL/SQL The issue I'm having is I have a block of dynamic SQL that I can't get the results from.

Here's the SQL:

CREATE OR REPLACE PROCEDURE GGCHECKER

IS
    cursor c1 is
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM system.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50);

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1
    )';

    EXECUTE IMMEDIATE var2;

VAR5:='
SELECT '|| PK_CTABLE || '
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )';

    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;
    EXIT WHEN c1%NOTFOUND;

    END LOOP;
    CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;

end;
/

So because it has to go through a number of tables on a number of schemas on each database all the SQL for the comparison of the data has to be dynamic. I'm looking to get the results from the query in variable var5. The issue being that I also need the schema and table name for ID's that are outputted so the data is useful to me.

Ideal output would be something like this:

SCHEMA_NAME | TABLE_NAME | COL_ID

ODD         |     MIKE   | 1234567890

Thanks in advance!

I've found a solution but it's probably not the best way of doing it.

CREATE OR REPLACE PROCEDURE GGCHECKER

IS
    cursor c1 is
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM MODDIE.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50);

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

    var6                varchar2(4000);

    VAR7                VARCHAR2(4000);

    var8                VARCHAR2(4000);

    var9                VARCHAR2(4000);

    NUMROWS             NUMBER(20);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

-- Create query to populate comparison table

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1
    )';

    EXECUTE IMMEDIATE var2;

--Compare the two tables
VAR5:='CREATE TABLE System.TEMP_RESULTS AS (
SELECT DISTINCT '|| PK_CTABLE || '
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM System.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )
        )';
    execute immediate var5;

    var8:= 'SELECT COUNT(*) FROM system.TEMP_RESULTS';

    -- Find the number of results for the loop
    execute immediate var8 into NUMROWS;

    DBMS_OUTPUT.PUT_LINE('THE NUMBER OF DISCREPANCIES IN ' || CTABLE_OWNER || '.' || CTABLE_NAME || ' IS: '|| NUMROWS);

    -- For every result add a record to the results table
    IF NUMROWS > 0 THEN

        var6:='
        INSERT INTO system.RESULTS_TABLE
        (TABLE_OWNER, TABLE_NAME, COL_ID)
        VALUES
        ( ''' || CTABLE_OWNER || '''  , ''' || CTABLE_NAME || ''' , NULL)';

        FOR N IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE(var6);
            EXECUTE IMMEDIATE var6;
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('mid if');

        FOR M IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE('mid for2');
            var7:=' UPDATE system.RESULTS_TABLE SET COL_ID = (SELECT * FROM system.TEMP_RESULTS WHERE ROWNUM = ' || M  || ' ) WHERE ROWNUM = ' || M || ' ';
            DBMS_OUTPUT.PUT_LINE(var7);
            EXECUTE IMMEDIATE VAR7;
         END LOOP;

     END IF;

    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;

    var9:='DROP TABLE system.TEMP_RESULTS';
    EXECUTE IMMEDIATE VAR9;
     DBMS_OUTPUT.PUT_LINE('exit 1');
    EXIT WHEN c1%NOTFOUND;

    END LOOP;
        CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;
     DBMS_OUTPUT.PUT_LINE('exit 3');

end;
/


Keyword not supported: 'statslog_on' I am trying to connect to a SQL 2017 instance via SSMS 18.2 with the 'Additional Connection Parameters'
StatsLog_On=Yes;StatsLogFile=C:\Temp\TestODBC.txt
The documentation at Using Connection String Keywords with SQL Server Native Client is less than perfect, but it strongly implies that it should work.  I know from other sources that the keywords StatsLog_On & StatsLogFile need to be used together.
I get this error message when I try to connect

===================================
Cannot connect to sandbox.
===================================
Keyword not supported: 'statslog_on'. (System.Data)

Program Location:
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.SqlServerType.Microsoft.SqlServer.Management.UI.ConnectionDlg.IServerType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Note: "Sandbpx" is the name of the SQL instance
As a reality check using Server=sandbox;MultiSubnetFailover=True works fine

It looks like SSMS is using the .NET System.Data.SqlClient API.  As far as I can tell, the statslog_on connection property is not supported using this API.

I ran a simple program* that iterates over the key value pairs allowed by that API and came up with these keys that are permitted in connection strings for SqlClient connections:

Data Source
Failover Partner
AttachDbFilename
Initial Catalog
Integrated Security
Persist Security Info
User ID
Password
Enlist
Pooling
Min Pool Size
Max Pool Size
PoolBlockingPeriod
Asynchronous Processing
Connection Reset
MultipleActiveResultSets
Replication
Connect Timeout
Encrypt
TrustServerCertificate
Load Balance Timeout
Network Library
Packet Size
Type System Version
Authentication
Application Name
Current Language
Workstation ID
User Instance
Context Connection
Transaction Binding
ApplicationIntent
MultiSubnetFailover
TransparentNetworkIPResolution
ConnectRetryCount
ConnectRetryInterval
Column Encryption Setting
Enclave Attestation Url

This list was produced specifically using the .NET Framework 4.7.2.  The list could be different depending on the versions of things that are used by different versions of SSMS.

For instance, I have SSMS 18.4 installed. Attaching a debugger tells me that it's using the .NET Framework 4.8 version of System.Data.dll:

Based on that, I would expect any of these to be allowed in an SSMS connection string (give or take what might have changed between version 4.7 and 4.8 of the framework).  Of course, some of them might fail at runtime, might not be compatible with one another, or might depend on the type of database you're connecting to.

Some allowed aliases for those specific values are allowed, and documented here: SqlConnection.ConnectionString Property - Remarks

*here is the C# program I used to produce those values:

var builder = new SqlConnectionStringBuilder();
foreach (var key in builder.Keys)
{
    Console.WriteLine(key);
}
Console.ReadLine();


How to implement soft deletes? What is your method of implementing soft deletes in a database?

Ideally the solution would allow for

decent performance on large tables
take relationships into account
take unique keys into account
the user should be able to find and restore his deleted items.

Thanks!

There's a couple of ways I can think of to do it:

You could have a "Deleted" indicator, which is set to "Y" when a record is deleted. Very simple, but it leaves deleted and active data in the same tables, which could cause performance problems if there's a lot of activity. Restoring a record is as simple as changing the indicator.
You could have a table that mirrors the structure of the one you want to delete from, and when you delete a record from the main table, insert that record into the "deleted_data" table. This allows you to move deleted data into separate table to give the main table better potential performance, but searching for data that is in either table could make things more complicated, and keeping the data structures in sync  (when they change) is also a bit more work that has to be done. In this case, restoring the data would involve removing the record from the "deleted" table and inserting it back into the main table.

In both cases, you may want to find any child records and mark them as deleted as well. I suppose you could do this with triggers or with code.


Postgresql: Get pg_stat_statments.calls but per PID I am trying to collect information about a particular query, namely select 1 (or more globally, any select ?), and I need to know which application is issuing it the most. I know that I can get the total number of calls from pg_stat_statements, which is the total count, and I can also get which process is issuing it through pg_stat_activity, but I am not sure how I can combine this info to tell which process is issuing it the most. Any ideas?

I can't see a possibility for a join because the pg_stat_statments appears to coalesce all this data across all processes. If I can't use these tables, any others I can use before I can run some other form of profiling?

pg_stat_statement cannot produce this kind of information.

You'd have to set log_statement = all, then you get all these statements (and all others) in the log. Make sure that you have %p in your log_line_prefix.

Be careful: logging all statements can bring a busy server down. In PostgreSQL v12, you can set log_transaction_sample_rate to log only a certain sample of transactions, then you can estimate the total count.


Understanding `null` with Oracle Function's IF-Statement Given the following Oracle function:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX = 1) THEN RETURN 1;
                  ELSE RETURN 0;
    END IF;
  END;

Then, I called it from a Oracle SQL Developer's worksheet:

select nullFunction(3) from dual returns (null).

Next, I modified my function to:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX is NULL) THEN RETURN 0;
                      ELSE RETURN 1;
    END IF;
  END;

But, I received the same output when calling it with a value of 3 - null.

Why does it return NULL? I would've expected it to have returned 0 since varX, which I'm assuming to be equal to null, as part of the else.

If you pass any number except the number 1 then your select returns no data and raises a no_data_found exception.  Not sure what you are wanting to do but a simple solution would be to add an exception.

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
varX NUMBER;
BEGIN
select 1 into varX from dual where exists(select 1 from dual where a = 1);
 RETURN 1;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;

END;

Alternatively restructure the select from dual so that it cannot return a no_data_found.


Read-only copy of SQL Server database We have main database, through out day we insert/update this DB through main application(windows). We have another web application which is suppose to just display content from main DB but instead we have created new DB which we update through out day either manually or from code.

I want to get rid of this manual/code process of updating one DB to another DB. What are the options? Eliminate second DB and just read from main DB? Replication? Mirroring?

It has to be real time update.
Thanks,

You could implementation Replication if there is a subset of data that you are looking to read from.  Replication publishes articles to subscribers.  It's a great way for a subset of data that needs to be recent data.

If you are looking for a read-only copy of the whole database, though, and the data doesn't need realtime accuracy, you could create snapshots every morning with a job.  That way the OLAP can be done against the snapshot.  Or if you need the data to be a maximum of 4 hours old you can do that. (or whatever schedule you find the need to "refresh" the data)


Mysql Or MariaDb for high traffic server What server should I choose to install for a server that will host mostly shopping carts?

I'll have average 10-20K products in store and some tables with up to 1 million rows (cache tables for filtering).

Here is my opinion. As long as you are going to use innodb engine it doesn't matter which one are you in.
Provided Maria gives you the option of multiple writes to master-master architecture using Galera-cache. But as your TPS goes beyond a particular point, you might face issues in getting writes paused due to flow-control. Hence you may use innodb engine let there be 2Nodes using MariaDB using GaleraCache Cluster or simple Master&Slave. But have your applications writing to one node only either case.
For my.cnf you may use percona tool wizard to generate as per your requirement.

Generate data as per expected prod including right data using datatypes. To an expected 2Yr down the line. In your case lets say
1Million records for one year lets consider 2Million records to
2years. Ensure data is random not same for all records.

Lets say you have above data generated and sizing is placed for right indexes and data. Check the data dir size, I would recommend you
to have data size to be your innodb_buffer_pool_size if it is
affordable.

Memory - innodb_buffer_pool_size(as per data) + 30% of IBPS . I.E.
120GB + 36GB.
CPU - As per user parallel connections and type of queries involved.
But this can go as per assumption. If it is 156GB RAM lets start with
1core*8CPU, considering 100Connections Per Second.
Disk IO - NVMe,SSD which ever best fit as per your Infra.
Network - Set maximum network bandwidth for DB Servers.
Have your applications in-place to throttle MAX-TPS for read/write,
ensure this MAX-TPS value helps number of customers using at peak-hrs
projected by business team.

Initially you may do a perf test with MAX-TPS read/write with right indexes in place and queries not going more than 30ms.

Now make a note of latency on app if it 50ms or whatever and see if business and apps team are ok with it. If not try to tweak if problems
are at query end or app end. After all enough variables tweaking (you
need to drill DBA here) if things don't satisfy the latency increase
which ever is required MEM/CPU/DISKIO/Network in DB Server.


Do MySQL limitations apply to MariaDB as well on AWS RDS I am thinking of migrating MariaDB 10.3.13 to RDS, I want to do an analysis of what limitation can be caused by migrating to RDS, I could find no documentation by AWS for MariaDB for issues and imitations, however there is a MySQL doc for issues and limitations, I am wondering do the same limitations apply to MariaDB?

Short answer:  Probably yes.
Long answer:
I reviewed that link.  All three (MySQL, MariaDB, RDS) are derived from the same original code.  As far as I know, all three have, or at least had all of those bugs/issues/etc.  When one of the 3 fixes a bug, the others may eventually pick up the fix.
For innodb_buffer_pool_instances, MariaDB seems to be leading the change:  MariaDB-10.5.1 changelog says that innodb_buffer_pool_instances deprecated.
WHERE indexed_col1 = 'value1' AND indexed_col2 = 'value2' -- This is almost certainly fixed by using a composite index rather than depending on index merge.
The 10x for blobs was a workaround that was mentioned in 5.6(?).  It may be relaxed in some later version.
lower_case_table_names is a kludge to porting to both Windows and Unix.  It got worse with 8.0's Data Dictionary.  So, beware of switching OS's
As for the 16TB limit for an InnoDB table, I thought it was 32TB or 64TB.  It won't be long before someone finds out for sure.
Lower limits such as 2 TB, and long ago, 2 GB are probably dictated by the underlying Filesystem on the Operating System.
long_query_time allowed microseconds long ago, in 5.1.21, before MariaDB forked off.  I don't understand why AWS mentions it.
My list of limits:
http://mysql.rjweb.org/doc.php/limits


How can I view what NLS settings are active for other session? I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2.  What can I query to show the NLS settings for a different active session - all of the NLS_* views reference my current session.

There is no simple way to achieve this. You can either do it by tracing the session or by using a login trigger. Here's an example of how to do it using a DB login trigger.

Logging table:

create table nls_session_parameters_log
(
    inserted_date    date,
    sid              number,
    username         varchar2(128),
    parameter        varchar2(30),
    value            varchar2(40)
);

Public grant and synonym:

grant insert on nls_session_parameters_log to public;

create public synonym nls_session_parameters_log for sys.nls_session_parameters_log;

Database login trigger:

create or replace trigger nls_log_dbtrig after logon on database
begin
  if user in ('PHIL') then
    insert into nls_session_parameters_log
    select sysdate, (select sys_context('USERENV','SID') from dual), (select user from dual),parameter, value
    from nls_session_parameters;
  end if;

  exception
    when others then
      null;
end;
/

Test:

SQL> conn phil/phil
Connected.
SQL> select count(*) from nls_session_parameters_log;

  COUNT(*)
----------
        17

SQL>

Obviously you'd be better off putting the table in an appropriate schema and granting on a per-user basis.

If you need help with another approach (session tracing), let me know.


Best read performance when every query is known, and the data doesn't change I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or so queries for the map rendering is static, except that the bounding box for each tile changes.

Is this a common situation? What's the best approach? For example:

Create an index exactly matching each query:

CREATE index roads_high_idx
 ON planet_osm_line
USING gist(way)
WHERE (highway IS NOT NULL OR railway IS NOT NULL)
  AND (tunnel IS NULL OR tunnel = 'no')
  AND (bridge IS NULL OR bridge = 'no');

Pre-load all the queries into other tables:

SELECT [columns]
INTO roads_high
FROM planet_osm_line;
CREATE index roads_high_idx
 ON planet_osm_line
WHERE (highway IS NOT NULL OR railway IS NOT NULL)
  AND (tunnel IS NULL OR tunnel = 'no')
  AND (bridge IS NULL OR bridge = 'no')
ORDER BY ...;

Some smarter approach, where Postgres figures this out implicitly?

Although all the queries are known, they're buried in an XML project file, so it's not quite trivial to extract them all.

Off the top of my head, here are a few things to do, in no particular order.

VACUUM FREEZE
ANALYZE
Add the indexes that you think are going to help.
Execute as many queries as you can.
Look at pg_stat_user_indexes to see which queries are being used.
Look at the EXPLAIN ANALYZE plan for each query.
Review https://wiki.postgresql.org/wiki/Slow_Query_Questions
Review https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Post follow-ups with server specs and questions to SO or the Postgresql mailing lists (most of the core developers answer questions in the pg lists).

It will likely be an iterative process, so don't expect to get it right in the first go.


Survey Database Structure I am building a database to feed into a PowerBI dashboard.
The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database.
The outcome i am after is the ability to provide an Average for each KRA.
The response table has a unique row for each survey response, with each question as a separate column, with a score for a value.
Each Question is assigned a KRA, so i have created the lookup table (Question_KRA).
can someone point me in the right direction in how i can structure the data in order to have a summary table which describes the KRA and the average?
I would like to avoid hard coding in a view, as i would like the ability to add in new questions, with relative ease.

You can use UNPIVOT to transpose the question columns from SurveyResults into rows and then average the KRA values from there. This db<>fiddle shows an example.

The first query shows the UNPIVOT and the second query shows a dynamically generated SELECT statement that would allow you to add additional question columns without needing to recode the SELECT statement.


found xmin X from before relfrozenxid Y during vacuum The vacuum of two pg_catalog tables runs into the following error and stops there.

  found xmin X from before relfrozenxid Y during vacuum

I could find a lot of discussions about this problem but solutions are hard to find. Is there any solution for this problem other then restoring the database from a backup?

postgresql 9.3.22 (I know it is old, the server is being phased out but needs to continue working for now).

You might be in luck.

The last but one point release for 9.3 has this change:

  Ensure that updates to the relfrozenxid and relminmxid values for "nailed" system catalogs are processed in a timely fashion (Andres Freund)

  Overoptimistic caching rules could prevent these updates from being seen by other sessions, leading to spurious errors and/or data corruption. The problem was significantly worse for shared catalogs, such as pg_authid, because the stale cache data could persist into new sessions as well as existing ones.

See the discussion on the -hackers list.


Adding a Group ID to a query where the sort by is not based on the Column your grouping by I am trying to add a groupID column to a query where the column the grouping is based on is sorted randomly

DECLARE @t TABLE
(
  ID INT primary key IDENTITY(1,1),
  ProductID INT,
  QuestionID INT
)

INSERT INTO @t (ProductID,QuestionID)
SELECT  ProductID
        ,QuestionID
FROM    dbo.Products p WITH(NOLOCK)
JOIN    dbo.Questions q  WITH(NOLOCK) on p.StudyID = q.StudyID
WHERE   p.StudyID = @StudyID
ORDER BY FIRST_VALUE(CRYPT_GEN_RANDOM(4)) OVER (PARTITION BY ProductID ORDER BY ProductID),
        ProductID,
        QuestionNumber

SELECT ID,ProductID,QuestionID
       , DENSE_RANK() OVER(ORDER BY ProductID) as ProductGroupID
FROM   @t t
ORDER BY ID

Every time the query is run there is a random sort off the ProductID Column.
I trying to get the GroupID to be 1,2,3 etc...  no matter what ProductID is first.

This is my goal
Example:

     ID  | ProductID   | GroupID   |  QuestionID
    -----|-------------|-----------|-------------
     1       22             1              2
     2       22             1              3
     3       22             1              5
     4       11             2              2
     5       11             2              3
     6       11             2              5
     7       44             3              2
     8       44             3              3
     9       44             3              5

If you re-query it should be:

     ID  | ProductID   | GroupID   |  QuestionID
     ----|-------------|-----------|-------------
     1       44             1              2
     2       44             1              3
     3       44             1              5
     4       11             2              2
     5       11             2              3
     6       11             2              5
     7       22             3              2
     8       22             3              3
     9       22             3              5

and again:

     ID  | ProductID   | GroupID   |  QuestionID
    -----|-------------|-----------|-------------
     1       11             1              2
     2       11             1              3
     3       11             1              5
     4       44             2              2
     5       44             2              3
     6       44             2              5
     7       22             3              2
     8       22             3              3
     9       22             3              5

The easiest way in my opinion would be to just add the randomly ordered sequential integer in the SELECT from Product before the join.

WITH Prod
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (ORDER BY CRYPT_GEN_RANDOM(4)) AS ProductGroupID
         FROM   dbo.Products
         WHERE  StudyID = @StudyID
         )
SELECT ProductID,
       QuestionID,
       ProductGroupID
FROM   Prod p
       JOIN dbo.Questions q
         ON p.StudyID = q.StudyID
ORDER  BY ProductGroupID,
          QuestionNumber


inet_client_addr in PostgreSQL don't return IP i trying use the function inet_client_addr() (PostgreSQL 9.2) in a trigger, but the return  is '::1/128'.
This is a problem with my script or with the server?

    CREATE OR REPLACE FUNCTION trg_aud_cms() RETURNS TRIGGER AS $aud_cms$
BEGIN
    --
    -- Cria uma linha na tabela para refletir o estado anterior ao atual
    IF (TG_OP = 'DELETE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('D', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('U', USER, inet_client_addr(), now(), OLD.codcms, OLD.titlecms, OLD.contentcms, OLD.hashtagcms);
            RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO aud_cms(operation, user, ip, date, codcms, titlecms, contentcms, hashtagcms) VALUES ('I', USER, inet_client_addr(), now(), NEW.codcms, NEW.titlecms, NEW.contentcms, NEW.hashtagcms);
            RETURN NEW;
    END IF;
    RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$aud_cms$ LANGUAGE plpgsql;

CREATE TRIGGER aud_cms
AFTER INSERT OR UPDATE OR DELETE ON cms
FOR EACH ROW EXECUTE PROCEDURE trg_aud_cms();

You could abuse the application_name setting to pass the HTTP client IP from PHP to Postgres.

In PHP, once connected:

<? pg_query("SET application_name TO '$_SERVER[REMOTE_ADDR]'"); ?>

Or set it right at connection time with pg_connect which would normally support it in the conninfo string (see Connection Strings in the manual)

In your trigger, use   current_setting('application_name') to retrieve this information,
instead of inet_client_addr() which doesn't do what you want, as already answered.

There should also be a check in the trigger that this value is what you expect before storing it, since the trigger may be fired in any session, not just the ones initiated by PHP.


Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability? I'm new to MS SQL Server.

Colleagues working in IT security, have run an scan showing come DB users with null password.

Some of them are ( # included )

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##

I suspect they are not a security threat but, since they don't use Windows Authentication either, I don't know how to support that assumption.

What are these logins for ?

These logins are created from a certificate.  In fact, if you run the following query:

select
    name,
    type_desc
from sys.server_principals
where type = 'c';

You will see that they are of type CERTIFICATE_MAPPED_LOGIN.  They are used typically to sign code.  And you cannot use a certificate mapped login to connect with SQL Server.  Please see this BOL reference on CREATE LOGIN:

  Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server.


Remove duplicates groups I have a many-to-many relationship table (bridge table) (TicketId, CiiD) and I want to transform it into the following model : (IdBridge, Ciid),
actually my problem is how to remove the duplicates groups !
here is an exemple code for source table (TicketCis):
TicketCis
CREATE TABLE TicketCIs
(
      TicketId varchar(5)
      ,CIId varchar(5)
);

INSERT INTO TicketCis (TicketId, CiiD)
VALUES
('A','AA'),
('A','AB'),
('B','AA'),
('C','AC'),
('C','AD'),
('D','AA'),
('E','AC'),
('E','AD'),
('F','AF');

so I proceed to grouping by TicketId with adding ID_GROUP with this query:
SELECT
DENSE_RANK() OVER(ORDER BY TicketId) AS ID_GROUP,
     CIId
FROM TicketCIs

The output I'm getting:
╔══════════╦═══════╗
║ ID_GROUP ║ CIId  ║
╠══════════╬═══════╣
║    1     ║  AA   ║
║    1     ║  AB   ║
║    2     ║  AA   ║
║    3     ║  AC   ║
║    3     ║  AD   ║
║    4     ║  AA   ║
║    5     ║  AC   ║
║    5     ║  AD   ║
║    7     ║  AF   ║
╚══════════╩═══════╝

The output I'd like to get:
╔══════════╦═══════╗
║ ID_GROUP ║ CIId  ║
╠══════════╬═══════╣
║    1     ║  AA   ║
║    1     ║  AB   ║
║    2     ║  AA   ║
║    3     ║  AC   ║
║    3     ║  AD   ║
║    6     ║  AF   ║
╚══════════╩═══════╝

In the output (what I'm looking for) as you can see, the duplicate group_ids (4,5) are removed.
I hope it's clearer now :)
Thank you.

You can use the COUNT() OVER window function and PARTITION by your ID_GROUP field (in a series of CTEs) to filter on in the final query.
WITH CTE_Group AS
(
    SELECT
        DENSE_RANK() OVER(ORDER BY TicketId) AS ID_GROUP,
        CIId
    FROM TicketCIs
),
CTE_Group_Distinct AS
(
    SELECT
        ID_Group,
        CIId,
        COUNT(CIId) OVER (PARTITION BY ID_GROUP) AS DupeCount
    FROM CTE_Group
)

SELECT ID_GROUP, CIId
FROM CTE_Group_Distinct
WHERE DupeCount = 1


Transactional replication creating a fixup index that's sleeping, but blocking other processes I've just migrated my ERP database server from SQL Server 2000 to SQL Server 2008 R2 (both Enterprise Edition) and that migration went fairly smoothly.

However, now that I'm trying to recreate replication from the ERP to the Reporting environment, I have two publications that are trying to create a series of MSFixupNonUniqueIndex_{{datetimestamp}} nonunique indexes. The processes creating these indexes are all sleeping, but they are blocking other SQL processes that end-users are running.

What is causing this fixup index creation and can I avoid it?

Using sp_whoisactive there is no wait_info for the index creation task.
I did not drop the replicated database in the Reporting environment.
The "Reporting environment" is also SQL Server 2008 R2 Enterprise.
This is 2008 R2 to 2008 R2 replication, now.
sysprocesses shows two procs blocked by the spid attempting to create the index. The spid itself has a waittype of 0x0000 and a lastwaittype of MISCELLANEOUS.
sys.locks shows there are 777 locks: 7 Intent, 761 Exclusive, 6 Shared, 2 Schema Mod, 1 Schema Stability. Resources are: 1 ALLOCATION_UNIT, 9 OBJECT, 3 DATABASE, 2 METADATA, 677 PAGE, 11 KEY, 1 HOBT, 73 EXTENT.
select * from sysprocesses where open_tran > 0 does return the spid with one open transaction
DBCC OPENTRAN has no open transactions.
MAXDOP is unconfigured

If replication is automatically creating indexes, then you probably can't avoid it.  The only way to do so might be to create a matching index on the subscriber.

Try running the merge agent from the command line with a more verbose logging level and see what information it returns. It might be bumping against something which isn't being returned as an error.


Database Design Concept - Are Multiple Relationships A Bad Thing? I have a question about Relational Database design. I'm just learning the basics of databases, so bare with me, in that I'm not sure of the technical terms for some of the concepts I'm trying to express here.

In short:

In the database structure in the image below, there is no replicated data in different tables, so far as I can tell, this design is 3NF compliant, however maybe I have misunderstood one or more of those concepts.

I'm unsure as to whether or not having multiple relationships in the design creates possible issues with referential integrity. A picture says a thousand words at this point:
http://img580.imageshack.us/img580/3757/t9ku.png

If you look at the Service table in the middle, it has a relationship directly to Customer. It also has a relationship to Site, which in turn has a relationship to Customer.
Is this okay, or should I remove some foreign keys so that the only 'path' from Service to Customer, is via the Site table?

In long:

I'm working on a practice example revolving around data services. I've created my 'structure', that is to say the tables themselves and the key's in them (both primary and foreign) in order to see the relationships. What has struck me is that it is possible to define a few of my tables to have more than one relationship to other tables, such that there is more than one 'path' between tables.

Does this create possible issues of referential integrity?
(i.e. You can get from one table to another, with or without going 'through' other tables, the extra relationship appears to allow some tables to be 'bypassed')

Is this a bad thing in a relational database?
Should any given table only have a single relationship to another table such that the 'path' through the database is 'linear' rather than having choices in the 'path'?
Or, assuming this is in fact 3NF compliant, is being able to define more relationships a good thing?
(i.e. it might make query design easier in some instances because the direct relationships allow you to avoid having to join lots of intermediate tables...)

In your model, every Service has one Site and every Site has one Customer.
Now you have to ask yourself: Is there a use case where the CustomerID associated with the service can be different from the customerID associated with its site?

If a service can have a service-customer and a site-customer, then this design would be correct. If not, you have stored redundant information in your database by including the CustomerID column in the Service table and redundancies should be avoided in good database design.


how to change a user password while checking the current password in oracle? In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command:

alter user user_name identified by new_password;

My problem is that I don't know how to check whether the current password which the user has been entered in the form is correct or not ( as the passwords are encrypted in oracle)

How should I do this?

11g and higher accept this syntax:
ALTER USER username IDENTIFIED BY new_password REPLACE old_password;


Slowly Changing Dimensions Type 4 SCD type 4 keeps the foreign keys to the original dimension and the mini-dimension in the fact table.
Why is the foreign key to the mini-dim not stored in the original dim?

Link to an article explaining SCD types: http://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/

Usually, we use SCD-type-4 when a dimension(SCD Type 2) grows rapidly due to the frequently changing of its attributes. These frequently changing attributes will be removed from the main dimension and added in to a new one known as Mini-Dimension.

==> Mini Dimension do not store the historical attributes, but the fact table preserved the history of dimension attribute assignment

A good example is illustrated here : link-SCD4


Where clause returns null, but need to return 0 Need an SQL Statement for the following:

Table a
  partID
  partNumber

Table b
  partID
  purchased

SELECT a.partNumber
FROM a
WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0

If the WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0 returns Null, I'll get no record to display.   Would like it to return 0 so I can show the a.partNumer having a 0 purchased quantity.

This is an MS Access database running on IIS 6.0 using ASP/VBScript

I think you need a LEFT JOIN or an inline subquery. The FROM b is also missing.

For the NULL to be converted to 0, you need the equivalent of COALESCE() or ISNULL(), which is the Nz() function in Access:

SELECT a.partNumber,
       Nz( (SELECT SUM(b.purchased)
            FROM b
            WHERE b.partID = a.partID
           ), 0) AS sum_purchased
FROM a ;

Assuming that table's primary (or unique) key is partID, you can have the same results with a LEFT JOIN:

SELECT a.partNumber,
       Nz(SUM(b.purchased), 0) AS sum_purchased
FROM a
  LEFT JOIN b
    ON b.partID = a.partID
GROUP BY a.partID, a.partNumber ;


Auditing/Logging Help I'm working on a request to start logging some additional data for PCI compliance.  I need to log who, when, and what changed on a set of tables.  I set up SQL auditing which would work fine except for the fact that the values of what is being changed is coming through as @1, @2 etc instead of the actual data.  What are my other options for logging the needed information?  CDC does not track who made the change.

This is currently running on SQL Server 2012 but a possible upgrade to 2014 is possible if that would provide a better solution.

Thanks,
Tim

I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to potentially outpace your actual data. Might want a separate database on isolated storage for that.

Auditing, CDC and Change Tracking would be great if they were combined, but they're not. As you've discovered, auditing shows parameter placeholders, so it tells you who changed it but not what they changed, and the other two tell you what has changed, but not who changed it or when.

I've asked for the ability to include username and date/time in CDC data, but it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

I also asked for date/time information to be included in Change Tracking, but similarly it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/319049/change-tracking-provide-datetime-column

I wasn't aware of the auditing limitation. If I had known about it, I would have asked for it, too. And it would have been declined like the others.


BigQuery - load data with changing schema I have situation when an external system is providing data to BigQuery instance as JSON files.
They are rows, that have been updated in that external system, I need this data in BQ for later processing.
It is not a full snapshot of data, only changed rows.
I need to get this data from the external system, periodically join with data I already have in BQ and export for another system.
The problem occurs in a situation, when source external system changes they database schema - it's NoSQL database, they can add some columns. They can do it in every moment without notifying us about it. And then we are expected, to export data with new column already in export.
Any ideas on how to handle this situation? I was wondering about creating a new table with every load and then use a wildcard query, but it will not work when tables that are wildcarded have different columns structure.

EDIT to make question clearer.
I am loading JSON input files into BigQuery. There can be added new columns to the JSON structure at any time.
I need to load all incoming data and then have the possibility to query all of it as with the latest schema I received in the last JSON file. So like dynamically "add" missing columns to old data.

I'm reading your question as "how to detect schema changes in in-coming data and automatically change out-going data to match?"

Obviously, you're going to need to examine the meta-data of the in-coming records. Since its JSON this should be easily handled by whatever technology you use to accept these records. Then you'll need a local store holding the meta data your application is already aware of. As JSON is a tree structure your local meta data store will model a tree. As records with new schema are detected this local store can be updated.

You will need a second local store to hold the structure of your output document and a third to map the input fields to output fields.

The next three parts cannot be automated. If there are transformations from input to output a human will have to write them. Where in the output document structure the new values should appear will also have to be hand-written, as will the mapping from input to output.

My feeling is this will be horribly inefficient. It may be possible, depending on your technology, to use reflection to alter the run-time definition of your output class to accommodate new columns. Good luck.


Vacation Approvals Multiple requests into one request? Not 100% sure if this makes the most sense to try and do in SQL.

What I am trying to achieve, is drive Vacation Approvals through SharePoint Lists using Flow.

Currently I am able to get the data from our scheduling system with the requests as individual rows, however I need to present the request back as a single item.

For Example:

The reason why I need it like this is because when I use Flow to populate the sharepoint list, it seems to be 1:1 mapping from field to field per row.

Not opposed to creating additional tables or request ID system to join all these together.

Thanks in advance,

Craig

For 2017 you can use STRING_AGG.

DBFIDDLE

create table  #myTable
([Name] varchar(64),
 DateOfRequest date,
 ReqLine int,
 FromDate date,
 EndDate date,
 SchedCode char(2))

insert into #myTable
values
('BSMITH','20190911',1,'20190912','20190912','V8'),
('BSMITH','20190911',2,'20190914','20190915','V8')

select
  [Name],
  string_agg(DateOfRequest,char(13)) as DateOfRequest,
  string_agg(ReqLine,char(13)) as ReqLine,
  string_agg(FromDate,char(13)) as FromDate,
  string_agg(EndDate,char(13)) as EndDate,
  string_agg(SchedCode,char(13)) as SchedCode
from #myTable
group by [Name]

This won't make your DateOfRequest unique, but if you know it will be the same for each Name then you can just select it, and add it to the group by

select
    [Name],
    DateOfRequest,
    string_agg(ReqLine,char(13)) as ReqLine,
    string_agg(FromDate,char(13)) as FromDate,
    string_agg(EndDate,char(13)) as EndDate,
    string_agg(SchedCode,char(13)) as SchedCode
  from #myTable
  group by [Name], DateOfRequest


How to initialize a PostGIS database in PostgreSQL single-user mode? I'm using PostgreSQL 9.2.15 with PostGIS 2.0.7 through the standard packages available on CentOS 7 with EPEL.

I have a server script to automatically setup a PostGIS database, by first executing initdb, then using postgres --single to create a new database with PostGIS extension. This works roughly like this:

initdb -D ...

postgres --single -D ... postgres <<'EOF'
    CREATE DATABASE mydb;
EOF

postgres --single -D ... mydb <<'EOF'
    CREATE EXTENSION postgis;
EOF

However, the last command produces the following strange error message:

ERROR:  type reclassarg[] does not exist
STATEMENT:  CREATE EXTENSION postgis;

If I instead run the database server and execute CREATE EXTENSION postgis via sql, everything works fine:

postgres -D ... &

# Wait until service it up and running

psql mydb <<'EOF'
    CREATE EXTENSION postgis;
EOF

But that is not suitable for a database initialization script.

So how can I initialize a PostGIS database in PostgreSQL single-user mode?

Update: There is  a thread in the pgsql-bugs mailinglist on that topic, but that doesn't answer my question. It just explains that it is not planned to improve the single-user mode of PostgreSQL. So there will always be missing PgSQL language features to execute the current PostGIS extension creation script. However, most other PostgreSQL extensions can be installed in single-user mode just fine. So maybe there is a way to reformulate the PostGIS creation script so it achieves the same thing without requiring those language features, i.e. to make it "more portable".

(This question originally appeared on StackExchange / GIS: https://gis.stackexchange.com/q/214449/82644)

Someone previously ask a very similar question on the lists. Anyway, as told in the thread, reclassarg is a composite type.

  RhodiumToad: Guest78493: in single-user, doing CREATE TYPE ... AS ...;
  doesn't create the array type

  RhodiumToad: Guest78493: it creates only the non-array one, but I have no idea yet why

  RhodiumToad: Guest78493: so for now, any extension with a CREATE TYPE AS won't load correctly in single-user

  RhodiumToad: Guest78493: arguably if it doesn't work it should error rather than quietly getting it wrong, so I think you're justified in calling it a postgres bug

Further, from Tom Lane himself!

    [ Can't create postgis extension in single-user mode ]

  Why in the world would you think that's a good thing to do?

  Single-user mode is a barely-documented disaster recovery aid.
  It's not meant for routine activity.  There are a whole lot
  of behaviors you want that are turned off in single-user mode.

  The specific reason why this doesn't work is this bit in
  heap_create_with_catalog:

/*
 * Decide whether to create an array type over the relation's rowtype. We
 * do not create any array types for system catalogs (ie, those made
 * during initdb). We do not create them where the use of a relation as
 * such is an implementation detail: toast tables, sequences and indexes.
 */
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
                          relkind == RELKIND_VIEW ||
                          relkind == RELKIND_MATVIEW ||
                          relkind == RELKIND_FOREIGN_TABLE ||
                          relkind == RELKIND_COMPOSITE_TYPE))
    new_array_oid = AssignTypeArrayOid();

  We could possibly develop some other mechanism for detecting whether
  we're within the initdb sequence, but I can't get very excited about
  treating this as a bug.  Single-user mode hasn't been considered a
  standard user environment since maybe the early 90s.

If it was you, please update us in the future and self-answer your questions when you get word from the horse's mouth.

UPDATE.

  So maybe there is a way to reformulate the PostGIS creation script so it achieves the same thing without requiring those language features, i.e. to make it "more portable".

No, there is not. ST_Reclass requires it. The method you're using is not supported. Initializing a database is currently viewed as a different thing than CREATE EXTENSION on one. I don't think PostgreSQL intends -single to be used in that way, and certainly PostGIS doesn't intent to rewrite working code so it functions in PostgreSQL's unsupported modes, with outdated single-user contexts.


LOB_DATA, slow table scans, and some I/O questions I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete numbers, let's say the table has a million rows and is ~15 GB in size.

What I noticed is that this table is really slow to select data from if I want to select all the columns. When I do

SELECT TOP 1000 * FROM TABLE

it takes around 20-25 seconds to read the data from disk - even though I don't impose any ordering on the result.
I run the query with the cold cache (i.e. after DBCC DROPCLEANBUFFERS). Here's IO statistics results:

  Scan count 1, logical reads 364, physical reads 24, read-ahead reads
  7191, lob logical reads 7924, lob physical reads 1690, lob read-ahead
  reads 3968.

It grabs ~15 MB of data. Execution plan shows Clustered Index Scan as I'd expect.

There's no IO going on on the disk besides my queries; I've also checked that clustered index fragmentation is close to 0%. This is a consumer-grade SATA drive, however I'd still think SQL Server would be able to scan the table faster than ~100-150 MB/min.

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).

I guess my question is - am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively when there's a lot of LOB_DATA pages in the table?

Even more broadly - is it considered reasonable to have such a table structure/data pattern? Recommendations for using Filestream usually state much bigger field sizes, so I don't really wanna go that route. I've not really found any good info about this particular scenario.

I've been thinking towards XML compression, but it needs to be done on the client or with SQLCLR and would require quite some work to implement in the system.

I tried the compression, and since XMLs are highly redundant, I can (in a c# app) compress XML from 20KB to ~2.5KB and store it in VARBINARY column, preventing usage of LOB data pages. This speeds SELECTs 20x times in my tests.

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA).

Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or maybe several ;-) might argue that duh, the XML column implies that there will indeed be XML data, it is not guaranteed that the XML data will need to be stored off row: unless the row is pretty much already filled outside of their being any XML data, small documents (up to 8000 bytes) might fit in-row and never go to a LOB_DATA page.

  am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively when there's a lot of LOB_DATA pages in the table?

Scanning refers to looking at all of the rows. Of course, when a data page is read, all of the in-row data is read, even if you selected a subset of the columns. The difference with LOB data is that if you don't select that column, then the off-row data won't be read. Hence it is not really fair to draw a conclusion about how efficiently SQL Server can scan this Clustered Index since you didn't exactly test that (or you tested half of it). You selected all columns, which includes the XML column, and as you mentioned, that is where most of the data is located.

So we already know that the SELECT TOP 1000 * test wasn't merely reading a series of 8k data pages, all in a row, but instead jumping to other locations per each row. The exact structure of that LOB data can vary based on how large it is. Based on research shown here ( What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc? ), there are two types of off-row LOB allocations:

Inline Root -- for data between 8001 and 40,000 (really 42,000) bytes, space permitting, there will be 1 to 5 pointers (24 - 72 bytes) IN ROW that point directly to the LOB page(s).
TEXT_TREE -- for data over 42,000 bytes, or if the 1 to 5 pointers can't fit in-row, then there will be just a 24 byte pointer to the starting page of a list of pointers to the LOB pages (i.e. the "text_tree" page).

One of these two situations is occurring each time you retrieve LOB data that is over 8000 bytes or just didn't fit in-row. I posted a test script on PasteBin.com ( T-SQL script to test LOB allocations and reads ) that shows the 3 types of LOB allocations (based on the size of the data) as well as the effect each of those has on logical and physical reads. In your case, if the XML data really is less than 42,000 bytes per row, then none of it (or very little of it) should be in the least-efficient TEXT_TREE structure.

If you wanted to test how quickly SQL Server can scan that Clustered Index, do the SELECT TOP 1000 but specify one or more columns not including that XML column. How does that affect your results? It should be quite a bit faster.

  is it considered reasonable to have such a table structure/data pattern?

Given that we have an incomplete description of the the actual table structure and data pattern, any answer might not be optimal depending on what those missing details are. With that in mind, I would say that there is nothing obviously unreasonable about your table structure or data pattern.

  I can (in a c# app) compress XML from 20KB to ~2.5KB and store it in VARBINARY column, preventing usage of LOB data pages. This speeds SELECTs 20x times in my tests.

That made selecting all columns, or even just the XML data (now in VARBINARY) faster, but it actually hurts queries that don't select the "XML" data. Assuming you have about 50 bytes in the other columns and have a FILLFACTOR of 100, then:

No Compression: 15k of XML data should require 2 LOB_DATA pages, which then requires 2 pointers for the Inline Root. The first pointer is 24 bytes and the second is 12, for a total of 36 bytes stored in-row for the XML data. The total row size is 86 bytes, and you can fit about 93 of those rows onto a 8060 byte data page. Hence, 1 million rows requires 10,753 data pages.
Custom Compression: 2.5k of VARBINARY data will fit in-row. The total row size is 2610 (2.5 * 1024 = 2560) bytes, and you can fit only 3 of those rows onto a 8060 byte data page. Hence, 1 million rows requires 333,334 data pages.

Ergo, implementing custom compression results in a 30x increase in data pages for the Clustered Index. Meaning, all queries using a Clustered Index scan now have about 322,500 more data pages to read. Please see detailed section below for additional ramifications of doing this type of compression.

I would caution against doing any refactoring based on the performance of SELECT TOP 1000 *. That is not likely to be a query that the application will even issue, and should not be used as the sole basis for potentially needless optimization(s).

For more detailed info and more tests to try, please see the section below.

This Question cannot be given a definitive answer, but we can at least make some progress and suggest additional research to help move us closer to figuring out the exact issue (ideally based on evidence).

What we know:

Table has approximately 1 million rows
Table size is approximately 15 GB
Table contains one XML column and several other columns of types: INT, BIGINT, UNIQUEIDENTIFIER, "etc"
XML column "size" is, on average approximately 15k
After running DBCC DROPCLEANBUFFERS, it takes 20 - 25 seconds for the following query to complete: SELECT TOP 1000 * FROM TABLE
The Clustered Index is being scanned
Fragmentation on the Clustered Index is close to 0%

What we think we know:

No other disk activity outside of these queries. Are you sure? Even if there are no other user queries, are there background operations taking place? Are there processes external to SQL Server running on the same machine that could be taking up some of the IO? There might not be, but it isn't clear based solely on the info provided.
15 MB of XML data is being returned. What is this number based on? An estimation derived from the 1000 rows times the average of 15k of XML data per row? Or a programmatic aggregation of what was received for that query? If it is just an estimation, I wouldn't rely upon it since the distribution of the XML data might not be even in the way that is implied by a simple average.
XML Compression might help. How exactly would you do the compression in .NET? Via the GZipStream or DeflateStream classes? This is not a zero-cost option. It will certainly compress some of the data by a large percentage, but it will also require more CPU as you will need an additional process to compress / decompress the data each time. This plan would also completely remove your ability to:

query the XML data via the .nodes, .value, .query, and .modify XML functions.
index the XML data.

Please keep in mind (since you mentioned that XML is "highly redundant") that the XML datatype is already optimized in that it stores the element and attribute names in a dictionary, assigning an integer index ID to each item, and then using that integer ID throughout the document (hence it does not repeat the full name per each usage, nor does it repeat it again as a closing tag for elements). The actual data also has extraneous white space removed. This is why extracted XML documents don't retain their original structure and why empty elements extract as <element /> even if they went in as <element></element>. So any gains from compressing via GZip (or anything else) will only be found by compressing the element and/or attribute values, which is a much smaller surface area that could be improved than most would expect, and most likely not worth the loss of capabilities as noted directly above.

Please also keep in mind that compressing the XML data and storing the VARBINARY(MAX) result won't eliminate the LOB access, it will just reduce it. Depending on the size of the rest of the data on the row, the compressed value might fit in-row, or it might still require LOB pages.

That information, while helpful, is not nearly enough. There are a lot of factors that influence query performance, so we need a much more detailed picture of what is going on.

What we don't know, but need to:

Why does the performance of SELECT * matter? Is this a pattern that you use in code. If so, why?
What is the performance of selecting only the XML column? What are the statistics and timing if you do just: SELECT TOP 1000 XmlColumn FROM TABLE; ?
How much of the 20 - 25 seconds it takes to return these 1000 rows is related to network factors (getting the data across the wire), and how much is related to client factors (rendering that approximately 15 MB plus the rest of the non-XML data into the grid in SSMS, or possibly saving to disk)?

Factoring out these two aspects of the operation can sometimes be done by simply not returning the data. Now, one might think to select into a Temporary Table or Table Variable, but this would just introduce a few new variables (i.e. disk I/O for tempdb, Transaction Log writes, possible auto-growth of tempdb data and/or log file, need space in the Buffer Pool, etc). All of those new factors can actually increase the query time. Instead, I typically store the columns into variables (of the appropriate datatype; not SQL_VARIANT) that get overwritten with each new row (i.e. SELECT @Column1 = tab.Column1,...).

HOWEVER, as was pointed out by @PaulWhite in this DBA.StackExchange Q & A, Logical reads different when accessing the same LOB data, with additional research of my own posted on PasteBin ( T-SQL script to test various scenarios for LOB reads ), LOBs are not accessed consistently between SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn, SELECT @XmlVariable = XmlColumn.query(N'/'), and SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn). So our options are a little more limited here, but here is what can be done:

Rule out network issues by executing the query on the server running SQL Server, either in SSMS or SQLCMD.EXE.
Rule out client issues in SSMS by going to Query Options -> Results -> Grid and checking the option for "Discard results after execution". Please note that this option will prevent ALL output, including messages, but can still be useful to rule out the time it takes SSMS to allocate the memory per each row and then draw it in the grid.
Alternatively, you could execute the query via SQLCMD.EXE and direct the output to go to nowhere via: -o NUL:.

Is there a Wait Type associated with this query? If yes, what is that Wait Type?
What is the actual data size for the XML columns being returned? The average size of that column across the entire table doesn't really matter if the "TOP 1000" rows contain a disproportionately large portion of the total XML data. If you want to know about the TOP 1000 rows, then look at those rows. Please run the following:

SELECT TOP 1000 tab.*,
       SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],
       AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
       STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
FROM   SchemaName.TableName tab;

The exact table schema. Please provide the full CREATE TABLE statement, including all indexes.
Query plan? Is that something that you can post? That info probably won't change anything, but it is better to know that it won't than to guess that it won't and be wrong ;-)
Is there physical / external fragmentation on the data file? While this might not be a large factor here, since you are using "consumer-grade SATA" and not SSD or even Super-Expensive SATA, the effect of sub-optimally ordered sectors will be more noticeable, especially as the number of those sectors that needs to be read increases.
What are the exact results of the following query:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),
                          OBJECT_ID(N'dbo.SchemaName.TableName'), 1, 0, N'LIMITED');

UPDATE

It occurred to me that I should try to reproduce this scenario to see if I experience similar behavior. So, I created a table with several columns (similar to the vague description in the Question), and then populated it with 1 million rows, and the XML column has approximately 15k of data per row (see code below).

What I found is that doing a SELECT TOP 1000 * FROM TABLE completed in 8 seconds the first time, and 2 - 4 seconds each time thereafter (yes, executing DBCC DROPCLEANBUFFERS before each run of the SELECT * query). And my several-year-old laptop is not fast: SQL Server 2012 SP2 Developer Edition, 64 bit, 6 GB RAM, dual 2.5 Ghz Core i5, and a 5400 RPM SATA drive. I am also running SSMS 2014, SQL Server Express 2014, Chrome, and several other things.

Based on the response time of my system, I will repeat that we need more info (i.e. specifics about the table and data, results of the suggested tests, etc) in order to help narrow down the cause of the 20 - 25 second response time that you are seeing.

SET ANSI_NULLS, NOCOUNT ON;
GO

IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
    PRINT N'Dropping table...';
    DROP TABLE dbo.XmlReadTest;
END;

PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest
(
    ID INT NOT NULL IDENTITY(1, 1),
    Col2 BIGINT,
    Col3 UNIQUEIDENTIFIER,
    Col4 DATETIME,
    Col5 XML,
    CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO

DECLARE @MaxSets INT = 1000,
        @CurrentSet INT = 1;

WHILE (@CurrentSet <= @MaxSets)
BEGIN
    RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',
              10, 1, @CurrentSet) WITH NOWAIT;
    INSERT INTO dbo.XmlReadTest (Col2, Col3, Col4, Col5)
        SELECT  TOP 1000
                CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)),
                NEWID(),
                GETDATE(),
                N'<test>'
                  + REPLICATE(CONVERT(NVARCHAR(MAX), CRYPT_GEN_RANDOM(1), 2), 3750)
                  + N'</test>'
        FROM        [master].[sys].all_columns sac1;

    IF ((@CurrentSet % 100) = 0)
    BEGIN
        RAISERROR(N'Executing CHECKPOINT ...', 10, 1) WITH NOWAIT;
        CHECKPOINT;
    END;

    SET @CurrentSet += 1;
END;

--

SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows

-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;

--

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,       physical reads 1,     read-ahead reads 4436,
              lob logical reads 5676, lob physical reads 1, lob read-ahead reads 3967.

 SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 8329 ms.
*/

And, because we want to factor out the time taken to read the non-LOB pages, I ran the following query to select all but the XML column (one of the tests I suggested above). This returns in 1.5 seconds fairly consistently.

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET STATISTICS IO, TIME ON;
SELECT TOP 1000 ID, Col2, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;

/*
Scan count 1, logical reads 21,    physical reads 1,     read-ahead reads 4436,
              lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1666 ms.
*/

Conclusion (for the moment)
Based on my attempt to recreate your scenario, I don't think we can point to either the SATA drive or non-sequential I/O as the main cause of the 20 - 25 seconds, especially because we still don't know how fast the query returns when not including the XML column. And I was not able to reproduce the large number of Logical Reads (non-LOB) that you are showing, but I have a feeling that I need to add more data to each row in light of that and the statement of:

  ~90% of table pages are LOB_DATA

My table has 1 million rows, each having just over 15k of XML data, and sys.dm_db_index_physical_stats shows that there are 2 million LOB_DATA pages. The remaining 10% would then be 222k IN_ROW data pages, yet I only have 11,630 of those. So once again, we need more info regarding the actual table schema and actual data.


SQL Server (2014) Failover Cluster Performance with many databases We are considering implementing SQL failover clustering and I have a question about how SQL Failover clustering works with regards to performance.  Specifically, we would have about 1000 dbs mounted on said cluster, and my concerns are:

Performance during a failover: Will there be an outage during the failover operation?
How long can I expect the failover to take?

I'm not sure how it's implemented, but I'm just imagining the analogy of shutting down a SQL server service on one machine, and once complete starting up on another machine.  With all of these dbs, just recycling the SQL service takes about 10 minutes.  Should I expect the same from the failover?

1.Performance during a failover: Will there be an outage during the failover operation?

Yes there would be some outage during cluster failover.. During failover SQL Server is stopped on current node and failedover/moved to other node. After this SQL Server is brought online on the node to which it is failed over. Sometime this is so quick that application users might not notice. But the fact that SQL Server is restarted means you should always keep in mind that there might be some outage

  How long can I expect the failover to take?

There are lot of factors that influence how long it would take for failover cluster to come online on other node. The factors are (but not limited to)

Edition of SQL Server. Fast recovery which is enterprise edition feature could bring database online after second phase(redo) of recovery
Amount of transaction log SQL Server has to rollforward and rollback to bring database online in consistent state. Because database would go through crash recovery this would always be the one influencing the time most. If database takes lot of time to recover due to large amount of uncommitted transactions gettign rolled back it would definitely increase your time.
The hardware strength and network capabilities supporting the cluster. Some of this is documented here.


MySQL Best approach for recording last update timestamp for every column and every row This question is about possible approaches for time stamping every column of every row in a table.
I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each row refers to an individual entity and each column corresponds to piece of data about that entity. The columns will be updated whenever new information becomes available, and I need to record when that happens.
For example, think in terms of a contacts table:
CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `address` VARCHAR(100),
    `phone_number` VARCHAR(100),
    `email_address` VARCHAR(100),
    `updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

If the phone number is updated this month, we might be inclined to believe that the `email_address` was updated as well, even though it may be severely outdated.
I can think of three approaches:
1. Create an additional timestamp column for each column:
CREATE TABLE `contacts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100),
    `name_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` VARCHAR(100),
    `address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` VARCHAR(100),
    `phone_number_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` VARCHAR(100),
    `email_address_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

2. Or, I could created a secondary table that contains the same information:
CREATE TABLE `contacts_updated` (
    `id` INT,
    `name` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `phone_number` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `email_address` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_contacts_updated_contacts_id`
        FOREIGN KEY (id)
        REFERENCES contacts (id));

In which case, is there an easy way to duplicate a table and automatically change the column types to timestamps? And use triggers to update the secondary table?
3. Or, I could create an Entity-Attribute-Value table:
CREATE TABLE `contacts_updated` (
    `id` INT,
    `column_name` VARCHAR(15),
    `updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

...but this seems like it would be more difficult to query the information.
Are there other options that would be better? And I also assume that the answer will be different for a small dataset vs. a large one, so I'd love to know your opinion on both cases.

COMMENTARY
I love this question because I answered another question like it 7 years ago

June 30, 2013 : Get the Time of Last update of a column

You actually wrote the SQL I had suggested. I am impressed.
YOUR ACTUAL QUESTION
IMHO I would go with Suggestion #3 because its implementation is as normalized as you are going to get. The retrieval of the deltas for any column would consume the least amount of memory in the InnoDB Buffer Pool, memory within the DB Connection, and the smallest footprint on disk.
The other suggestions would have so much redundant data that retrieving the delta info would most likely push your working dataset out of the InnoDB Buffer Pool frequently.
CAPTAIN'S LOG : SUPPLEMENTAL
Surprisingly, there is an alternative.
MariaDB has implemented all the above suggestions into a table metadata framework called a Temporal Table. What you are going to implement falls under the category "System-Versioned", which tracks the Change History of all table rows. The best part about "System-Versioned Temporal Tables" is that you do not need to explicitly create and track timestamps. That's done for you.
The only drawbacks I can think of are the following:

Small Learning Curve in Understanding MariaDB's SQL Dialect for Temporal Tables.
The timestamps are for the row not the column. This means a column may change or not from one iteration of a row's point-in-time to the next. You may need write some fancy SQL GROUP BY or DISTINCT aggregation to go through each row and stare at the column you wish to track or audit.


Problems loading pgAudit library on Postgres 12.2 I'm trying to add the pgAudit extension to register the events of my database. I downloaded pgAudit and installed via git.
git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit
git checkout REL_11_STABLE
gmake install USE_PGXS=1

And added 'pgaudit' to the shared_preload_libraries. However, when I try to start the server, it throws the following error:
FATAL: could not load library "/usr/local/lib/postgresql/pgaudit.so": /usr/local/lib/postgresql/pgaudit.so: Undefined symbol "IsSystemNamespace".

I've googled for a while but cannot find what's wrong. I've checked that the library is really there and uninstalled/reinstalled it. I'm currently working with FreeBSD-12.0 RELEASE and PostgreSQL 12.12.
Thanks in advance for your time.

This commit renamed IsSystemNamespace to IsCatalogNamespace in PostgreSQL v12, so you must have built the extension with PostgreSQL v11 or older by mistake.
Check the result of
which pg_config

to see what was first on your PATH when you built the extension.
Be explicit with
make USE_PGXS=1 PG_CONFIG=/path/to/pg12/bin/pg_config


SQL Server Execute Permission Denied I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password.

The problem is that some (Windows Domain) users get an error saying execute permissions were denied on the stored procedure, whilst other users do not get any such error. However, both use the same app which uses the same login and password. The app does not use Trusted Connection or Integrated Security at all, only login and password from an ini file that stores encrypted credentials.

I'm at a loss. I have run SQL Server Profiler whilst the program is being used to confirm the user executing the procedure is as expected, I have tried setting permissions up for Windows Domain users in the Database... I have no idea why it would work for some and not for others when none of it was dependent on windows user permissions. Not only that, but it was working fine for everyone last week.

Is there anywhere I can look, Or tests I can do, that might help me diagnose the problem?

EDIT: Unfortunately I don't have access to the source code.

I found the issue through screenshare with a user. The ODBC DSNs on some client machines were set up to use Windows Authentication, and the ODBC implementation in the applications was rather simplistic, so when running the application it was using our Windows logins. It worked for me since I am sysadmin on the database server.

The ODBC DSN setup has been changed to use SQL Authentication with a username and password, and now it works.


Why isn't the transaction log getting truncated when taking a full backup? In full recovery model, if we take a full backup, the log file is not truncated. It is done when we take a transaction log backup.

Any particular reason of why it is done this way?

Being in the full recovery model means you want the option of restoring to any point in time. That means having a full set of transaction log backups after any database backup.

Imagine you have a db backup from midnight yesterday and log backups from 6, 12 and 18:00. Now you take another db backup. If the log is truncated, you can't restore to 11pm (which would require the previous db backup and all the log backups since).


Retrieving only the last row of a table, in the absence of a primary key I have a table that has 3 columns:

Column 1: contains XML data (that contains a timestamp value)
Column 2: contains the file name(with the path to it) corresponding to  XML data record (as to which file that XML data is stored in)
Column 3: contains the offset of the XML data within that file

For instance, different file names can be

C:\Extended Events\ReadWrite_0_130863592475800000
C:\Extended Events\ReadWrite_0_130863685030030000
C:\Extended Events\ReadWrite_0_130864300903760000

That is, the names assigned to the files are in incremental order.

Further each file name has different offset values in it, corresponding to the XML data value, returned above.

For instance,

C:\Extended Events\ReadWrite_0_130863592475800000 can have offset values 24576, 34816, 62976 (in an increasing order too)

Then, C:\Extended Events\ReadWrite_0_130864300903760000 can have offset values for itself independent of the above offset values.

I want to retrieve the file name and offset value corresponding to the last record returned i.e. if a total of 9 rows have been returned, I need the file name and offset value corresponding only to the 9th row.
I also want it to be optimal.

Using the MAX function isn't helping my case. I tried max(file_name) and it simply opened the Documents folder.

If you want the row with the highest offset value for each file, then:

;WITH x AS (SELECT [Column 1], [Column 2], [Column 3],
  rn = ROW_NUMBER() OVER (PARTITION BY [Column 2] ORDER BY [Column 3] DESC
  FROM dbo.source_table
)
SELECT [Column 1], [Column 2], [Column 3]
  FROM x
  WHERE rn = 1
  ORDER BY [Column 2];

If yu just want the row with the highest offset across all files, you don't want MAX(), but you can use TOP (1):

SELECT TOP (1) [Column 1], [Column 2], [Column 3]
  FROM dbo.source_table
  ORDER BY [Column 3] DESC;


Deleting Duplicates - mariaDB I have a mariaDB database containing details about user devices (sort of an 'allowed device' list for VPN access). Back when the project started, there was no restriction on what devices were allowed so, everyone has 2 devices registered to their name. I've been tasked with removing the older device from the database.
This is a simplified database schema:

UserID|RegisterDate|UniqueID
12345|12-08-2020|ABC123
12345|11-05-2020|XYZ789
23455|20-08-2020|QWE345

The requirement is that I have to delete the older device, in case the user has two devices. So in this example, deviceID XYZ789 belonging to User 12345 would need to be deleted.
What I have so far is this:
select min(RegisterDate), UserID from DeviceDetails  where RegisterDate in (select RegisterDate from DeviceDetails) group by UserID having count(*) > 1;
I understand this is very convoluted, and there is definitely room for improvement, as it takes around 3 minutes to run on around 14k rows.
The next step would be to get these rows exported into a log, and then deleted.
I'm not really a DB person, but its been tasked to me as we inherited this product from the previous vendor and it just works so far.
Database details:
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
CentOS 1810
Out of date, yes, but that decision is taken above my pay-grade.

This works quite fast and 14 k isn't that much data
You should add a unique constraint on UserID so that you will never have to run it again

CREATE TABLE DeviceDetails   (
  `UserID` INTEGER,
  `RegisterDate` varchar(10),
  `UniqueID` VARCHAR(6)
);

INSERT INTO DeviceDetails
  (`UserID`, `RegisterDate`, `UniqueID`)
VALUES
  ('12345', '12-08-2020', 'ABC123'),
  ('12345', '11-05-2020', 'XYZ789'),
  ('23455', '20-08-2020', 'QWE345');

delete d from
DeviceDetails d
inner join (
    select max(`RegisterDate`) as maxdate, `UserID` from DeviceDetails
    group by `UserID`
    having count(*) > 1
) u on u.`UserID` = d.`UserID` and u.maxdate != d.`RegisterDate`;

SELECT * FROM DeviceDetails;

UserID | RegisterDate | UniqueID
-----: | :----------- | :-------
 12345 | 12-08-2020   | ABC123
 23455 | 20-08-2020   | QWE345

db<>fiddle here


set variable result in case statement to be used again in the same select statement Is it possible to create, inside a single select statement, like this:

SELECT
   name,
   CASE
      WHEN name = 'a' THEN 'a'
      ELSE 'b'
   END tag,
   CASE
      WHEN tag = 'a' THEN 'a2'
      ELSE tag = 'b2'
   END tag2
FROM table_name

My reason is there is a previous column on the single statement where I want to use its case statement result on my other column.

I want to know if it can be done using variables like:

SELECT
   name,
   CASE
      WHEN name = 'a' THEN 'a'
      ELSE 'b'
   END @result = tag,
   CASE  @result
      WHEN 'a' THEN 'a2'
      ELSE 'b2'
   END tag2
FROM table_name

I know the second code is incorrect but that is the logic I need. Thanks guys!

You need to either repeat the expression, or derive it in a separate scope, e.g.:

SELECT name, tag, CASE
   WHEN tag = 'a' THEN 'a2'
   ELSE 'b2' END AS tag2
FROM
(
   SELECT  name, CASE
        WHEN name = 'a' THEN 'a'
        ELSE 'b' END AS tag
   FROM dbo.table_name
) AS derived;


How to drop materialized view logs if underlying log table ($MLOG_...) is dropped? In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says it's because those tables were manually dropped.
Is there a way of either forcing the logs to be dropped in the absence of those tables or else recreate those tables?

Version Information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0  Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Ok since there are no responses here.

I don't know if the problem was to do with the underlying tables or something else, we bounced the instance anyway. I also recreated the views using a SQL script (just create one for another table and use it as a template) and it seemed to work ok.


best design for my data storage I am looking for advice on storing rows of data.

What I have a bunch of users (id), and data about their activities.   Each user can perform a type of activity (type_id).  And then about 20 columns of integer data related to the type of activity.

This next bit is slightly wrong.  Originally I said: I currently fetch the data and store it once per hour, so I have a unique index over 3 columns (id, type_id, timestamp) + the 20 integers.

But actually I currently fetch and store the data every 15 mins, but only keep the last record for each hour.  I have been doing this with "on duplicate key update"

The data for type_id might not change for hours, days, weeks or even never again.  Some users have 3 or 4 types, others 20 or more types of activities.  In a typical hour I am adding 1000's of rows of data, most of which are duplicates (eg when sleeping none of the users data changes but I still store a row for each type_id for each hour, each row contains the 20 identical integers)

I need to look at this data and generate graphs and other reports so I am storing all the data even though it is mostly duplicates but I think I am looking for a way to do the reverse of "insert or on duplicate key update" where I only insert a new row if the integer data is different for each type_id for each user id.

If I could do that, can I then use SQL to fill in the blanks when I select data for a time period? Buy that I mean if users A type B has not changed since January I don't want to store rows for them in February and March, but still be able to select and get data for those months. In other words select the last row before a given timestamp?

Are there better ways to store and retrieve this data?

You could expand your activities table so that instead of a single timestamp column, it includes two timestamps, ts_from and ts_to, that indicate the time period for which the row's data is applicable. As long as no data changes, you do not insert any new rows into your table. When data does change, you insert the new data into a new row and update the old row's ts_to column.

Then you can create a calendar table that contains all timestamps of interest to you (in your case that table would contain a row for every day and every hour in the period of time that is of interest to you).

Finally, you get the result by cross-joining the two tables and keeping only those records whose date (from calendar table) falls between ts_from and ts_to.

For example (my example uses only dates and not datetimes, but you can change that easily):

create table calendar (
    d date
);

create table time_data (
    d_from date,
    d_to date,
    val1 int(10),
    val2 int(10)
);

insert into time_data values('2015-01-01', '2015-01-15', 10, 15);
insert into time_data values('2015-01-15', '2015-02-21', 7, 18);
insert into time_data values('2015-02-21', '2015-03-04', 1, 55);
insert into time_data values('2015-03-04', '2015-04-21', 3, 22);
insert into time_data values('2015-04-21', null, 9, 99);

I filled the calendar table with all dates from 2015-01-01 to now and did the simple query

select d, val1, val2
from calendar, time_data
where d >= d_from and d < ifnull(d_to, curdate())
order by d

The resultset contains an entry for every date in the range (2015-01-01, 2015-05-01) and val1, val2 from last entry from time_data before that date.

For a nice procedure to fill the calendar table, see this article.

However, since a cross join is an expensive operation, and you have large amounts of data, thoroughly test this (or any other) solution before applying it to your production system.


Issues with Log Shipping in SQL Server 2019 We're in the process of migrating a customer from a SQL Server 2012 environment to 2019.

I've been trying to get the log shipping up and running as it was in the old environment but I keep coming up against errors.

The first error I get is in the restore job on the secondary server:

2020-02-25 16:04:04.77  Skipped log backup file. Secondary DB: 'DB_Name', File: 'E:\LogShipping\DB_Name_20200225154501.trn'
2020-02-25 16:04:04.77  Error: Could not log history/error message. Microsoft.SqlServer.Management.LogShipping)
2020-02-25 16:04:04.77  Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data)
2020-02-25 16:04:04.77  Error: Object must implement IConvertible.(mscorlib)

If you google that, it leads you to a page suggesting the fix is to install cumulative update 2:  https://support.microsoft.com/sq-al/help/4537869/fix-log-shipping-agent-is-not-able-to-log-history-and-error-informatio

I tried installing that but immediately started noticing an issue where when you open Job Activity Monitor, the list of jobs does not load and you get an error message including the text "Failed to retrieve data for this request".  You also cannot do things like edit Log Shipping schedules for servers affected.  Nothing will work until you restart SQL Server Agent on the affected server.  If you check the job history, you can see that no scheduled tasks run during the time that SQL Server Agent is stalled.  SQL Server Agent doesn't appear to crash completely which is a shame in a way, because it should re-start itself in that case.

Searching for this error leads you to a dba.stackexchange.com page describing the same issue with the only resolution being uninstalling CU2.

I tried uninstalling CU2 but am immediately back to the original error message.

This leads me to believe that Log Shipping is broken in SQL Server 2019.

Is anyone actually using it successfully?

Did you come across either of these issues and manage to resolve them?

Should I be looking at mirroring or replication instead in 2019, or is Log Shipping still an acceptable warm-standby solution?

I had the exact same issue. I installed Cumulative Update 3 for SQL Server 2019
and it went away


How to Move TempDB Files to a Different Drive or Folder? How do I move my TempDB Data or Log File(s) from wherever they are now to a different Drive or Folder?

Moving the TempDB files is a 2-step process:

Tell SQL where you want your new TempDB files to go to
Restart the SQL Server service for the change to take effect

To tell SQL where to create the new TempDB files, you can use:

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size / 128 AS [Size(MB)] --Number of 8KB pages / 128 = MB
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

This will generate the T-SQL statements you need to run to move the files to the new drive:\folder you want. (click image to make larger)

When you have ran your moving statements, you can run the above query again, to check that the Current Location column is now showing your new drive:\folder.

Once you're happy with your changes, restart the SQL Server service.