Can a plpgsql stored procedure monitor for a query and kill it? I want to write a function that monitors for a particular query becoming a blocker (i.e. it is both blocked by a query, and is blocking another query) and terminate it. Here is my current code, which I have amended to use left join for testing purposes so that it does not require the query to be blocked:

CREATE OR REPLACE FUNCTION monitor_sql_and_terminate_blocker(IN p_query text, OUT result text)
  RETURNS text AS
$BODY$
DECLARE
  monitor_sql text;
  rec record;
BEGIN

  monitor_sql = '
with blocker as
(
  select distinct
    waiting.pid  pid
  , other.pid    blocker
  from pg_stat_activity
  join pg_catalog.pg_locks  waiting
    on waiting.pid = pg_stat_activity.procpid
    and not waiting.granted
  join pg_catalog.pg_locks  other
    on ( ( other."database" = waiting."database"
           and other.relation = waiting.relation )
         or other.transactionid = waiting.transactionid )
    and other.pid <> waiting.pid
  where current_query not like ''%<IDLE>%''
)
, blockers as
(
  select pid, array_to_string(array_agg(blocker),'','') blocker_list
  from blocker
  group by pid
)
, blocking as
(
  select blocker, array_to_string(array_agg(pid),'','') blocking_list
  from blocker
  group by blocker
)
select
    procpid
from pg_stat_activity
left join blockers on blockers.pid = pg_stat_activity.procpid
left join blocking on blocking.blocker = pg_stat_activity.procpid
where current_query = ''' || p_query || '''
';

LOOP
  FOR rec IN EXECUTE monitor_sql
  LOOP
    RAISE NOTICE 'Terminating procpid %', rec.procpid;
    PERFORM pg_terminate_backend(rec.procpid);
  END LOOP;
  PERFORM pg_sleep(1);
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

It is invoked like this:

select monitor_sql_and_terminate_blocker('select * from very_large_table')

However, it just loops infinitely and never does anything. If I run the query manually, it finds the process and returns the procpid which I can then terminate manually.

This is because of transaction isolation, the function only sees the queries that were running when it started. If I run the monitor function while the query is running, it kills it successfully and then keeps trying to kill it again and again. What can I do to work around it?

My current solution is to move the loop out into a shell script that runs psql to invoke a version of this code that runs once and then exits.

[Originally, the question was tagged with PostgreSQL.  I have no idea about Greenplum, but hopefully someone will turn up to answer for that system, too.  The rest applies to PostgreSQL only (8.3 onwards, whereas Greenplum was forked from 8.2).]

There is a relevant snippet in the documentation:

  Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction.

That means that the output is frozen for the transaction that once queries pg_stat_activity.  Fortunately, a few sentences later, this is being said:

  Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). The next use of statistical information will cause a new snapshot to be fetched.

I've tried it in the below minimalistic example, while issuing different statements in an other session:

DO $$
DECLARE i text;
BEGIN
    LOOP
        PERFORM pg_stat_clear_snapshot(); -- makes the output up-to-date
        PERFORM pg_sleep(1);

        FOR i IN SELECT query FROM pg_stat_activity
        LOOP
            RAISE WARNING '%', i;
        END LOOP;
    END LOOP;
END;$$;

It finds newly issued queries and even new sessions, so I think this is what you need.


monitoring mongodb and mysql replications using nagios I have set replication for mongoDB and mySQL.

Now I have to manage them using nagios. I have to write some php-script that will read server status.

I am good with nagios setup.

Now I want to know if there is some plugin that may ease my work, otherwise how exactly do I get the server status from nagios.

One solution I thought is to read the log file of nagios and look for pattern to know the server status, is it feasible?

For MongoDB, this would be a good place to start:

https://github.com/mzupan/nagios-plugin-mongodb

It's python rather than PHP, but it's basically what you are looking for to get the data into Nagios.  Then for what to monitor and alert on, I've previously written up a quick summary of this plugin previously on the mongodb-user group:

https://groups.google.com/forum/#!topic/mongodb-user/bix36VvywXE/discussion


An efficient database design for recording complex contact/configurator form I know it's a quite generic question and probably opinionated. I don't know where to ask. Maybe you could point me to the right direction.

Currently I have a very big 'configurator', 10 steps long, online form, with 'substeps'. It is for client asking quotes for custom screwdrivers components applications (called 'auto-feed tightening modules'). Say about 50 fields/props per-submission/record.

WHen compiling the form, the user can:

Add as many 'screws' he wants (each screw described by some subfields)
Add as many 'components' he wants(each component described by some subfields) > and for each component, say what (above) screws it is related to

So when the form is submitted, we have a record composed like this:

a lot of 'shared' fields
some 'screw' fields group
some 'components' fields group
and screw and components must be somewhat associated (each component can have 1 or more screw)

All of this should be saved in a database.

Now, since:

we don't know how many screws and components will be added in a single submission
each screw and component has it's own set of fields (duplicated)

I cannot save each submission in one single row/record.

So, how to? Considering that then I'll need to show up the data somewhere else.

In the end, the real question: how should I design my db tables?

You have three questions here.

Storing multiple rows (of components) per form submission. This will happen in any process flow of a manufacturer. It is possible and you shall normalize by adding more rows per form (even for a component).

Secondly, each screw / component having its own set of (duplicate) fields can be handled simply by making those components refer to the existing data itself just by adding another sub-item id as required.

And for your main question of "how should I design my db tables", it is better if you could present a design and your expected bottle necks / issues / hurdles / etc. for which we shall suggest solutions.


Split column into 2 column oracle I have a data like this

AIN     ACCEPT_TYPE DATE1
91957753    A      04-NOV-14 12.50.31 PM
91957753    R      06-NOV-14 11.18.01 AM
919577      A      04-NOV-14 12.50.31 PM
919577      A      05-NOV-14 11.18.01 AM

And look for result like this

AIN     ACCEPT_TYPE_1   ACCEPT_TYPE_2   Date_1                  Date_2
91957753    A                 R             04-NOV-14 12.50.31 PM   06-NOV-14 11.18.01 AM
919577      A                 A             04-NOV-14 12.50.31 PM   05-NOV-14 11.18.01 AM

Is this possible.? Sorry i'm new to oracle .any help will be much helpfull.

It is quite simple:

select AIN,
       max(ACCEPT_TYPE) keep (dense_rank first order by date1) ACCEPT_TYPE_1,
       max(ACCEPT_TYPE) keep (dense_rank first order by date1 desc) ACCEPT_TYPE_2,
       min(DATE1) date_1,
       max(DATE1) date_2
  from my_table
 group by AIN


AWS RDS MYSQL How do I reduce the DB size? Possible Duplicate:
  How do I reduce the database size on AWS RDS MySQL?

I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much.

How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly wack it!

One idea (wholly un-researched, so there might be a better way, but this should work):

Create a new RDS instance that is smaller in size.
Dump the old one into the new one:

mysqldump -h <old-RDS> -u <user> -p<password> | mysql -h <new-RDS> -u <user> -p<password>

Whack the old one.

Of course, this assumes your total db size is smaller than 20GB, and that you're just looking to end up with a smaller instance. If you're trying to shrink the size of the database, in total, that's a different question.


Lost connection to server executing ALTER TABLE I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk space to run the query, because whenever I try to run it, another #sql… file will be created.

Query:

ALTER TABLE kt_ticket_message
    ADD COLUMN from_email varchar(255) NOT NULL COMMENT 'From email';

  ERROR 2013 (HY000): Lost connection to MySQL server during query

Table size is 5 GB, database size is 6 GB.

These are parameters in the config file:

wait_timeout=30
interactive_timeout=60
max_allowed_packet=1M

Can I increase the timeout parameters?

Change specific timeouts value to 6000 it's available in all new versions of MySQL Workbench.

  Open Edit → Preferences → SQL Editor → DBMS connection read time out
  (in seconds): 600

  Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.


Get SQL query in logon trigger I have a requirement for a logon trigger. I need to capture the SQL query that is coming to SQL Server from an Excel Pivot file.

Currently I am using the following code:

CREATE TRIGGER [MyLogonTrigger] ON ALL SERVER FOR LOGON
AS
BEGIN
    IF PROGRAM_NAME() <> 'Microsoft%'

    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @@spid

    DECLARE @SQLQuery varchar(MAX)
    set @SQLQuery = (select TEXT
    FROM sys.dm_exec_sql_text(@sqltext))

    INSERT INTO TestDatabase.dbo.LogonAudit
             (
                    ... audit columns ...
             )
       Select
                    SUSER_SNAME()
                    ,GETDATE()
                    ,@@SPID
                    ,PROGRAM_NAME()
                    ,ORIGINAL_DB_NAME()
                    ,HOST_NAME()
                    ,client_net_address
                    ,@SQLQuery
   FROM sys.dm_exec_connections
   WHERE session_id = @@SPID
END;

The problem I am facing is that @SQLQuery is the Create Trigger definition code every time, and not the actual query that is passed to SQL from the Excel file when the pivot is refreshed.

I think this has something to do with the fact that the login and the actual select query execution happens on two different transactions (SPID's).

Any idea how I can get the actual select query which excel uses to retrieve the data in a login trigger?

As Brent Ozar noted in his answer, you are attempting to use a Logon trigger for a purpose for which it was not designed.  Logon triggers are typically used to either capture details about who is logging onto the server, or to deny particular combinations of users/machines etc from connecting.

Luckily, Extended Events offers a mechanism for capturing T-SQL in memory that, when configured correctly, can be fairly light on system resources.

I've used something like the following code in the past to capture T-SQL from a particular user or machine:

IF EXISTS
(
    SELECT 1
    FROM sys.server_event_sessions dxs
    WHERE dxs.name = 'queries'
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM sys.dm_xe_sessions dxs
        WHERE dxs.name = 'queries'
    )
    BEGIN
        ALTER EVENT SESSION queries
        ON SERVER
        STATE = STOP;
    END
    DROP EVENT SESSION queries
    ON SERVER;
END

CREATE EVENT SESSION queries ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6 /* track a specific database only */
        AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6
        AND sqlserver.client_hostname <> 'excluded_host_name'
)
, ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        package0.collect_system_time
        , package0.event_sequence /* SQL Server 2012+ */
        , sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name /* SQL Server 2012+ */
        , sqlserver.plan_handle
        , sqlserver.sql_text
        , sqlserver.username
        , sqlserver.request_id
        , sqlserver.session_id
    )
    WHERE sqlserver.username = N'some_user_name'
        AND sqlserver.database_id = 6
        AND sqlserver.client_hostname <> 'excluded_host_name'
        /* fluff errors below - for SQL Server 2008 R2, use "error" instead of "error_number" */
        AND error_number <> 5703 /* Changed language setting to %.*ls. */
        AND error_number <> 5701 /* Changed database context to '%.*ls'. */
        AND error_number <> 2528 /* DBCC execution completed. If DBCC printed error messages, contact
                            your system administrator. */
        AND error_number <> 7969 /* No active open transactions. */
        AND error_number <> 4035 /* Processed %I64d pages for database '%ls', file '%ls' on file %d. */
        AND error_number <> 18265/* Log was backed up. Database: %s, creation date(time): %s(%s),
                first LSN: %s, last LSN: %s, number of dump devices: %d, device information: (%s).
                This is an informational message only. No user action is required. */
        AND error_number <> 3014 /* %hs successfully processed %I64d pages in %d.%03d seconds (%d.%03d MB/sec). */
        AND error_number <> 14570/* (Job outcome) */
        AND error_number <> 8153 /* Warning: Null value is eliminated by an aggregate or other SET operation. */
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 1024
)
/* add or remove the below target as required. */
, ADD TARGET package0.asynchronous_file_target
(
    SET filename = 'C:\temp\queries_xe_target.xel'
        , max_file_size = 10        /* max size in MB */
        , max_rollover_files = 10
        , increment = 1             /* file growth increment in MB */
)
/* Don't start this Extended Events session automatically when the server starts */
WITH
(
    STARTUP_STATE = OFF                                 /* Extended Event Session will NOT be automatically
                                                            started at server startup */
    , TRACK_CAUSALITY = ON
    , MAX_MEMORY = 5MB                                  /* buffer size to use */
    , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS    /* ALLOW_MULTIPLE_EVENT_LOSS or NO_EVENT_LOSS */
    , MAX_DISPATCH_LATENCY = 15 SECONDS                 /* maximum number of seconds until buffer contents
                                                            are written to the target */
    , MEMORY_PARTITION_MODE = PER_NODE                  /* NONE, PER_NODE, PER_CPU */
);
GO

Use this to start the session as required:

IF EXISTS
(
    SELECT 1
    FROM sys.server_event_sessions dxs
    WHERE dxs.name = 'queries'
)
BEGIN
    ALTER EVENT SESSION queries ON SERVER STATE = START;
END

This shows the session target details:

SELECT SessionName = xe.name
    , TargetName = xet.target_name
    , EventData = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

Use this to see the results from the ring buffer:

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries'
    AND xet.target_name = 'ring_buffer';

IF OBJECT_ID('tempdb..#xmlResults') IS NOT NULL
DROP TABLE #xmlResults;

CREATE TABLE #xmlResults
(
    RowNum INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , xeXML XML NOT NULL
);

INSERT INTO #xmlResults (xeXML)
SELECT xm.s.query('.')
FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
OPTION (Optimize FOR (@xml = Null)); -- immensely improves performance in SQL Server 2008

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName =          xeXML.value('(event/@name)[1]','varchar(500)')
            , EventDateStamp =      xeXML.value('(event/@timestamp)[1]','datetime')
            , DatabaseID =          xeXML.value('(event/data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber =         xeXML.value('(event/data[(@name)[1] eq "error"]/value/text())[1]','varchar(255)')
            , ErrorSeverity =       xeXML.value('(event/data[(@name)[1] eq "severity"]/value/text())[1]','varchar(255)')
            , ErrorState =          xeXML.value('(event/data[(@name)[1] eq "state"]/value/text())[1]','varchar(255)')
            , ErrorMessage =        xeXML.value('(event/data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime =   xeXML.value('(event/action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName =       xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName =      xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle =          CONVERT(xml, xeXML.value('(event/action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText =             xeXML.value('(event/action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName =            xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM #xmlResults xm
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
WHERE t.UserName NOT IN (
      'user_1'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_2'  COLLATE SQL_Latin1_General_CP1_CI_AS
    , 'user_3'  COLLATE SQL_Latin1_General_CP1_CI_AS
    )
ORDER BY t.UserName
    , t.EventDateStamp;

SELECT *
FROM #xmlResults


switch TOP clause between percentage and rows by parameter For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows.

I have two ideas

Calling two different sub stored procedure based on passed param.

if @param ='percent'
begin
      exec sp_data_TOP_by_Percent
end
if @param ='perRow'
begin
    exec sp_data_TOP_by_PerRow
end

other idea is to make dynamic TSQL query something like this

declare @command nchar(max)

select @command = 'select top(10) '
                   + case @param
                              when 'percent' then percent
                              else ' '
                     end
                   + ' * '
                   + 'from table
                        order by 1';
exec sp_executesql @command

Is there third solution for something like this ?
What is better approach ?
First one avoiding dynamic TSQL but is harder to maintain code in two places.
I am using MSSQL2005 as databse.

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL.

An IF statement in one stored proc would be enough too


What is the relationship between ADO.NET and Npgsql? There is a sentence in the Npgsql official page:

  Npgsql is an open source ADO.NET Data Provider for PostgreSQL.

I know that I can access a Postgresql database using Npgsql with the .NET framework.

Is Npgsql a subset of ADO.NET or is it derived from ADO.NET?

From Microsoft's docs about ADO.NET

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers.

From Npgsql's docs

Npgsql is an open source ADO.NET Data Provider for PostgreSQL, it allows programs written in C#, Visual Basic, F# to access the PostgreSQL database server. It is implemented in 100% C# code, is free and is open source.
In addition, providers have been written for Entity Framework Core and for Entity Framework 6.x.

What's a data provider? I think the one of the best answers come from the Wikipedia article about ADO.Net Data Providers:

An ADO.NET data provider is a software component that interacts with a data source. ADO.NET data providers are analogous to ODBC drivers, JDBC drivers, and OLE DB providers.
ADO.NET providers can be created to access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, IBM DB2, Sybase ASE, and many others. They can also provide access to hierarchical data stores such as email systems.

So I wouldn't say that Npgsql is not a subset of ADO.Net. It is, to some extent, a piece of software containing a collection of classes that implement a certain number of standard interfaces; which will be the ones considered "essential to every data provider" and a few of the "optional ones". You should take a look as well to ADO.NET Architecture


Handling misspellings when searching across tsvector columns I have a projects table with a tsvector column. The column is populated and refreshed via a trigger function when some of the other columns on a given row change:

CREATE OR REPLACE FUNCTION refresh_project_search_vector() RETURNS trigger AS $$
    BEGIN
        NEW.project_search_vector :=
            setweight(to_tsvector('english', NEW.id::text), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.name,'')), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.description,'')), 'B');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

I perform searches using:

...
WHERE project_search_vector @@ plainto_tsquery(search_term)
...

This seems to work well.

However, I also want to account for the user misspelling something. For example, if they search for Californa, I want Postgres to account for that and include/match tsvectors that have "California". (I know about stemming, but it doesn't seem sufficient for many types of misspellings like this.)

So my question is, is there a way to use a similarity logic when using tsvectors for full-text search?

Apologies if I'm asking the wrong question - still new to full-text searching.

I don't think there is any built-in way to do this. I think it would usually be handled by spell checking the search phrase before it ever gets submitted to the database (or by spell checking each word in it after it had been submitted and found no results).

You can use pg_trgm to do fuzzy searching which will be likely to find misspellings, but it doesn't integrate with tsvectors the way you apparently want.  One way to use that would be to create two indexes, one FTS based on stemming, and the other for fuzzy searching based on trigrams.  Then you could do the query one way (the @@ operator over tsvector column), and if it got no results then redo it the less-preferred way (likely using the %>> operator over raw text column/expression).

If you do find something that integrates these the way you want automatically, please post an answer to your own question.


Pinging SQL Server Availability Group Listener sometimes fails I have a client with a Server Availability Group. Let's say the AG listener is named AG1 . The AG1 listener is set to static IP with two IP address. When I do a nslookup of AG1, the DNS entry has the same IP addresses. It's an active/passive AG with automatic failover and it hasn't failed over.

If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable). In other words, the "Ping AG1" command reply sometimes tries to ping the IP address of the Primary, and in that case it succeeds. Other times the "Ping AG1" command sometimes tries to ping the IP address of the secondary, and in that case it fails. It seems like it switches back and forth every 30 seconds or so.

Is this normal behavior? I would expect the primary to always respond , but perhaps ping isn't intended to work with an AG?

Update the AG group seems to be setup like the pics in this documentation https://docs.aws.amazon.com/quickstart/latest/sql/step3.html
where the AG listener's Network Mode is Static IP with two IP addresses

The AG1 listener is set to static IP with two IP address
  ...
  If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable)

Yes.  This is normal when the nodes are on different subnets.  The listener requires two separate IP addresses, only one of which is ever online.

Ping will resolve the DNS name, but will attempt to connect only to whichever IP is returned first.  The order address are returned varies based on the client's subnet, and through "DNS Round-Robin".

The current SQL Server client libraries are coded to understand that when a DNS name resolves to two IP addresses, it must try both, as only one will be online.


What are the default fixed server role members for SQL Server 2012 What are the default fixed server role members for newly created SQL Server 2012 instance using all default options.

As given by

EXEC sp_helpsrvrolemember

On a clean install, just do:

SELECT role_name = pr.name, member_name = m.name
FROM sys.server_role_members AS r
INNER JOIN sys.server_principals AS m
ON r.member_principal_id = m.principal_id
INNER JOIN sys.server_principals AS pr
ON r.role_principal_id = pr.principal_id;

Here's what I get:

sysadmin    sa
sysadmin    CANUCK\aaronbertrand -- Administrator account I added during setup
sysadmin    NT SERVICE\SQLWriter
sysadmin    NT SERVICE\Winmgmt
sysadmin    NT Service\MSSQL$InstanceName
sysadmin    NT SERVICE\SQLAgent$InstanceName


Why does SQL Server say it can't convert varchar to numeric? I have the following schema/data in MSSQL Server 2019 (SQLFiddle):

CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

sales has mixed data i.e VARCHAR and NUMERIC. The transaction filter takes care of JOINing correctly.

Why does the following SELECT fail (For some reason beyond my control, the generated query has N literals for strings.)?

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    AND sales.type = N'number'
WHERE
    products.idn in (1);

I don't see why casting NVARCHAR to NUMERIC is an issue:

SELECT CAST (N'1' as NUMERIC);

If I modify the query slightly it works:

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    AND sales.type = N 'number'
WHERE
    -- Selecting the same data from `sales`.
    sales.pid in (1);

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number'
WHERE
    products.idn in (1);

A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'

The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.


Is having relationships between separate databases bad practice? I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The relationships from instance to master are integer values that represent a primary key to tables in the master. The views and stored procedures in the instances are setup to load data from the master via these stored keys.

Obviously, there is no real referential integrity, but is that bad practice or should the data reside in read-only tables in the instance databases?

The only issue is that there isn't really a convenient way to enforce these relationships. You can use a boatload of triggers, or store the master table in each instance database and simply expose all of them as metadata to the master, using a view perhaps.

The biggest downside, I think, has to do with keeping the data accurate and synchronized, in at least three scenarios off the top of my head (all of which can still be an issue even if you use triggers):

improperly coordinated cross-database transactions - for example one transaction adds a new row to master, hands off the identity to another transaction, and then the first transaction rolls back.
recovery from a single database failure - a database crashes, you have to restore to a point in time, that may have been before other successful transactions in other databases had finished. This could mean if an instance database has to revert to an earlier time, it is missing rows that master expects; if master has to revert, it's possible that all of the instance databases will have values that don't exist in master.
backup consistency in general - if you need disaster recovery, you're going to have a hard time keeping all your full and log backups transactionally consistent to a point in time. File system snapshots don't help with this, and even Availability Groups don't guarantee cross-database transactional consistency for the databases in a single Availability Group. If you have a disaster and need to restore your databases to a new system, there's no way to be sure they will be transactionally consistent.

All that said, I have always been and always will be a fan of separating tenants out into their own databases, and I acknowledge there is risk associated with the central database - but it is necessary.

(As an aside, you should use names for your databases other than master / instance. master in SQL Server definitely has an explicit meaning, and even reading my own words above they could be ambiguous. Same for instance. In a previous life I ran a multi-tenant system and we called the central database Control and the tenant databases, well, Tenants.)


group several columns together in materialized view For statistical reasons, I need to group several ids in a single column of a materialized view.
This should form a list of ids from other tables with an info. Further processing will be done on this view. The goal is to save seconds of processing by first generating this view instead of querying all the tables (about 10) one by one.

But I don't understand if I can create a new column name with view or only select one in existing table.

There are two ways to specify the name of a view column:

Do it with a column list:

CREATE VIEW myview (colname1, colname2) AS
SELECT appa.appa_id,
       font.font_id
FROM appa JOIN font ON ...;

Do it with aliases in the defining query:

CREATE VIEW myview AS
SELECT appa.appa_id AS colname1,
       font.font_id AS colname2
FROM appa JOIN font ON ...;


Cassandra: Request did not complete within rpc_timeout i was working with Cassandra 1.2.4 probably, after restoring some key-space when i tried to query in a key-space it gave me Request did not complete within rpc_timeout

so i checked system.log & output.log under /var/log/cassandra path

i just find this exception:

  Exception in thread Thread[ReadStage:42,5,main] java.lang.RuntimeException: org.apache.cassandra.io.sstable.CorruptSSTableException: java.io.EOFException

what is the reason ? and how can i get rid of rpc_timeout

thanks in advance,

From the EOFException I take it that your problem is not the rpc_timeout but a corrupt data file. And it seams that Cassandra is not properly handling the exception and never returns the error to the piece of server code that "holds" your client connection and waits for data to send back to the client. Thus the timeout.

You could try and run nodetool repair <your_keyspace>. Maybe it is able to fix the corrupted data file. If not, try to drop the keyspace and restore it again.

To answer your question. The timeouts are set in the cassandra.yaml file in the config directory of your cassandra installation on each node.


Insert values from a record variable into a subclass table My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME schema. I want the output_table to have the same schema PLUS some extra computed columns.

Will I have to use a less elegant solution than the one linked? Or is there a solution where I can append columns onto the _tbl_in record before I insert it to the subclass _tbl_out?

Here is the code for the previous solution for reference:

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

  Call (important!):

SELECT gesio(NULL::t, 't1');

  t and t1 being the tables with identical schema.

I've realized my problem has a very simple solution. As long as we are sure about order of the schema and the extra columns are after all the original columns, my problem is simply solved by adding the values to the SELECT of the INSERT INTO

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record
   computed_column1 := 'some value';
   EXECUTE format('INSERT INTO %s SELECT $1.*, computed_column1', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 are tables with identical schemas, but t1 has added a varchar column. The name of the column doesn't matter here because we're only using the order of the columns as they appear in the schemas of the two tables.


I'm guessing you have binary as your default character set for the server and/or for the database. You probably want to change this to prevent the same thing happening next time you create a table.

You can check by running: SHOW VARIABLES LIKE 'character_set_server'; and SHOW CREATE DATABASE the_database_name;

If the database doesn't have a character set, then the server character set will be used.

You can change the database character set with a command like:

ALTER DATABASE the_database_name CHARACTER SET = 'utf8';

The server character set can be set with a command like:

SET character_set_server = 'utf8';

(Use SET GLOBAL ... for the setting to be made outside of the current session. Or you can set this in your config file so it survives a restart.)


limit keyword support in DB2 latest version I could see limit keyword is supported in latest version v10.5.500.107 5 of db2 by default without setting MYS compatibility vector. My installation was DB2 Express-C. Is there any announcement for that? I couldn't find any online document.

EDIT

After the answer of Ian I checked the value of compatibility vector after unsetting it as below also.

db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start

and found it was MYS. seems like it is default set to MYS. that's why it has limit keyword support.

There was no change that I am aware of in Fixpack 5 to the requirement that the registry variable  DB2_COMPATIBILITY_VECTOR must be set to MYS for the ... LIMIT x OFFSET y functionality to work.  (Unsetting it in FP5 still results in an error if you try to use LIMIT / OFFSET).

You may want to look to see if DB2_COMPATIBILITY_VECTOR is set in the global registry.

Notice that running db2set alone does not show DB2_COMPATIBILITY_VECTOR:

$ db2set
DB2COMM=TCPIP
DB2AUTOSTART=YES

While running db2set -all does, since it's set at the global level:

$ db2set -all
[i] DB2COMM=TCPIP
[i] DB2AUTOSTART=YES
[g] DB2_COMPATIBILITY_VECTOR=MYS
[g] DB2SYSTEM=host1
[g] DB2INSTDEF=db2inst1


Windows user automatically gets db_owner? All databases from a server have just been migrated to a new server. It looks as if some users were carried across and some weren't, I'm not really sure as I did not know the server very well.

One user who was on the old server has requested a new windows login on the new one. When I go to create this login, and map it to a particular database, the 'db_owner' role is already checked, whereas for other databases it is not? The user has only requested read only.

Obviously I can just uncheck this but how is it that this is the case? Were they an owner of the previous database and someone has cleared out the users and created them anew and SQL remembers this person was a user? Or have all new users automatically been set to have db_owner granted to them? Is there anything I can query to see more information on this?

Thanks

From Comments:

This issue could occur when there is no owner listed under properties --> files. Once you populate the owner with a valid login, this issue is gone and db_owner is not granted automatically.

Same can be done using sp_changedbowner.

Please mark it as accepted answer so this could help someone else facing this or similar issue on DBA.SE forum.


copy a sqlite3 table preserving custom _id I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)

I'd like to copy the table preserving the labelling 1,2,5,7, as opposed to 1,2,3,4 that is given by AUTOINCREMENT below.

What is the correct way to do it?

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (key_remote_jid)
    SELECT key_remote_jid
    FROM android.messages;

I can achieve that by

CREATE TABLE messages (_id INTEGER PRIMARY KEY, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;

but then such table is not included in sqlite_sequence.

Just copy the ID values over:

CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL);
ATTACH 'msgstoreF.db' AS android;
INSERT INTO messages (_id, key_remote_jid)
    SELECT _id, key_remote_jid
    FROM android.messages;

Please read the SQLite Autoincrement documentation. New ID values will not conflict with the manually inserted values. (And it is likely that you do not need to specifiy the AUTOINCREMENT keyword.)


How to select only one database in a policy in SQL Server? I am creating a new policy which shows sproc creation dates. I am based on Stored Procedure facet and appropriate condition is created. Now I want this policy to include only one database in an instance

When I select new policy from the dropdown menu, which is next to every keyword, it shows this

But my point is not to create a new condition for all databases.

My question is, is it possible to to run a policy only for one database in an instance, and if yes, then how?

Thanks for your time!

Policies are server-wide. But you can have a condition to only bother about the policy based on, for instance, the database name. And that is exactly what that condition that you were about to create would do. So keep creating that for the database name that is relevant for you and you have achieved what you want to achieve.


Should I rebuild index after truncate/large insert? I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)

Basic outline is very simple:

Truncate Tables
Insert 1.75M rows in 'batches' of around 75,000 per time.

I am wondering if I should explicitly re-build the indexes at any time in this process? e.g.

Truncate Tables
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90) [or something similar]
Insert 1.75M rows

or perhaps

ALTER INDEX ALL ON xxx DISABLE
Truncate Tables
Insert 1.75M rows
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90) [or something similar]

Any assistance appreciated...not a DBA - a Dev who knows DB's pretty well is more accurate!

As with most questions of this type, it depends. It's unlikely that you're going to be inserting the data in the "correct" order for all of the indexes involved, which means that all of those indexes are likely to encounter a lot of page splitting during the insert process. So let's assume that you're inserting in clustered index order. You could disable all non-clustered indexes, truncate, do your insert, and then rebuild all of your non-clustered indexes. Of course, trying both approaches will tell you the truth of which is faster regardless of the theory behind it. :)


Timeout for Always On Synchronous Commit Mode For SQL Server Enterprise's Always On availability groups, how can you configure the timeout for Synchronous Commit mode?  Is there a maximum?  Can it work, for example, between continents?
Thanks

how can you configure the timeout for Synchronous Commit mode?

You can do it using SSMS, TSQL and powershell. Microsoft has documented this. Please see Modify the session-timeout period for an availability group replica. For example TSQL would look like
ALTER AVAILABILITY GROUP AccountsAG
   MODIFY REPLICA ON 'INSTANCE09' WITH (SESSION_TIMEOUT = 15);

Just note that the timeout value you put is the value which AG will take into consideration making the replica Async in functionality after primary looses connection to secondary replica. Means it will start committing data on primary without waiting for commit on secondary. BUT this does not means that log file will not grow on primary, it will, and primary will store all the log records so that when the secondary comes online it can send records to secondary and sync. If your secondary will be down for long time and you worry about logs being accumulated take the secondary out from the AG and this will allow committing of transaction logs on the primary.

Can it work, for example, between continents

Yes it will, but the timeout value you have to find out which best suits your environment. I have seen such configuration where value was 25 seconds due to network latency


Best way to add Foreign Key to massive table? Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small).

arrival and departure each have 2 ForeignKeys: one to airport, one to airplane.

Currently, to figure out which planes are at an airport at a certain time, I have to do a massive join between arrival, departure, and airport. So, I'd like to add a (possibly NULL) ForeignKey to the departure table that points to the arrival of the same airplane that's departing.

Given that these tables are so big, it seems like a bad idea to do this all as a single transaction. So, what's the best way to go about it?

First of all, depending not just on the number of rows but also on the total size of your table (based on the average row size) , it may not be excessive to create a new Foreign Key. Especially if scheduled properly.

However, it may well be too heavy a change.  In that case, you could consider creating an Intersection Table like:

CREATE TABLE FlightPair
(
     departurekey INT REFERENCES arrival NULL,
     arrivalkey INT REFERENCES departure NULL
)

This would allow you to incrementally batch the insert of rows into the intersection table a bit at a time, thus keeping the update transactions quite small.  Perhaps insert one day at a time, 10,000 rows at a time, or some other batching method.  Each batch should be a transaction that begins before the insert/update and commits when finished.

That means that the updating of the batches of your choice would be many small transactions instead of one big one.  That would limit the resources needed and, in many cases, would run faster that one big update.

Perhaps having a date included would help to filter the results more effectively.  NULL keys are probably needed for the periods that are incomplete.

What indexes do you currently have?  SQL performance issues often depend on having the indexes properly defined. The columns in the intersection table likewise need to have indexes, as do the departure and arrival tables.


Normalized Table Design 3NF I am building a table that will store 'task records' i.e. who approved a task for a group and period.

I have normalized the table but I am not sure if I have gone beyond 3NF, and if this is overkill?

I have created 6 related tables each represented by a FK in the TaskRecord table. The Model.Period table just contains a date column...

CREATE TABLE [Model].[Period]
(
   [PeriodID] [int] NOT NULL IDENTITY(1,1),
   [Period] [date] NOT NULL
   CONSTRAINT [PK_Period_PeriodID] PRIMARY KEY CLUSTERED([PeriodID] ASC)
);

Is this really necessary or should I store the date value in the TaskRecord table?

Here is the TaskRecord tbl...

CREATE TABLE [Model].[TaskRecord]
(
    [TaskRecordID] [int] NOT NULL IDENTITY(1,1),
    [GroupID] [int] NOT NULL,
    [PeriodID] [int] NOT NULL,
    [TaskID] [int] NOT NULL,
    [StatusFlagID] [int] NOT NULL,
    [ApprovalLevelID] [int] NULL,
    [ApprovedByID] [int] NULL,
    [ApprovedDateTime] [datetimeoffset](0) NULL,
    CONSTRAINT [PK_TaskRecord_TaskRecordID] PRIMARY KEY CLUSTERED  ([TaskRecordID] ASC),
    CONSTRAINT [FK_TaskRecord_GroupID] FOREIGN KEY(GroupID) REFERENCES [Model].[Group]([GroupID]),
    CONSTRAINT [FK_TaskRecord_PeriodID] FOREIGN KEY(PeriodID) REFERENCES [Model].[Period]([PeriodID]),
    CONSTRAINT [FK_TaskRecord_TaskID] FOREIGN KEY(TaskID) REFERENCES [Model].[Task]([TaskID]),
    CONSTRAINT [FX_TaskRecord_StatusFlagID] FOREIGN KEY([StatusFlagID]) REFERENCES [Model].[StatusFlag]([StatusFlagID]),
    CONSTRAINT [FK_TaskRecord_ApprovalLevelID] FOREIGN KEY(ApprovalLevelID) REFERENCES [Model].[ApprovalLevel]([ApprovalLevelID]),
    CONSTRAINT [FK_TaskRecord_ApprovedByID] FOREIGN KEY(ApprovedByID) REFERENCES [Model].[UserDetail]([UserDetailID])
);

I think you have gone too far for a transactional system, however if you are designing a data warehouse, your approach is correct.

If you are designing and OLTP system, I would recommend that you replace "PeriodID" in your "TaskRecord" table with "CreateDate" or something similar, so it implies what you are actually storing. You have lost the definition of what you are storing by having a column named "PeriodID" in your TaskRecord table in addition to potentially losing accuracy (as it is a date, rather than a datetime).

If you are designing and OLAP system however your approach is correct - your Model.Period table is a Dimension table and your Model.TaskRecord table is your fact table (though it appears to be lacking measures). I don't believe you are designing an OLAP system however so will not mention more.

In respect to normal forms, I would suggest you have decreased from 3NF rather than gone beyond. This is because you have introduced a dependency on insertions into the Model.Period table in order to maintain the ability to insert into your Model.TaskRecord table. A clear goal of normalisation according to Codd is "To free the collection of relations from undesirable insertion, update and deletion dependencies". Essentially date/time has no dependency if used in a field which stores a date/time value. However you have introduced a dependency on a suitable date record exiting in the Period table by using your original design.


Accessing Table Data ONLY From View I have a table '[Employee]' , and I want to allow access  to certain people only through [View] , using a grant.I am trying first to do it with a single user [User]  and then do it for a collection of users through a role.  I know I can use :

GRANT SELECT ON OBJECT : : [VIEW] TO [User] ;

And I can also create a role [Role] , and use:

GRANT SELECT ON OBJECT : : [VIEW] TO [Role]

My question is whether this permission somehow implicitly excludes all others,
i.e., do the two grants above prevent any of the [Users][Roles] from having any other type of access to '[Employee]' ( Read, Select, Update ), or do I have to expressly prohibit said users, roles from having additional access?

I assume this has to see with membership of [User] and the people in [Role] in other logins or existing user types which may have been granted said permissions either explicitly or as part of role membership? Thanks.

Unless you grant SELECT to 'public' role, you must explicitly grant access to each user, group or database role.

For simplicity, you can create a new database role, (i.e. my_users), then grant select to this role and finally add new users to the role.


tuning huge delete operation on sql server table I am performing a delete operation on very large sql server table based on query as discussed below.

delete db.st_table_1
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

stg_table and stg_table_1 doesn't have indexes on service_date.

both of these tables are loaded with million rows of data and delete operation is taking lot of time. Requesting your suggestion to improve the performance of this query.

I referred to strategy described in below question but couldn't understand how to implement it.

How to delete large amount of data in sql server without data loss?

requesting your kind suggestion on this.

Update:

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

how about if go with above logic to delete the millions of records. any advantages and disadvantages with that.

Testing based on your comments

Tested on SQL Server 2014 SP3

  stg_table and stg_table_1 doesn't have indexes on service_date.

  both of these tables are loaded with million rows of data and delete
  operation is taking lot of time.

DDL

CREATE TABLE dbo.st_table_1( stg_table_1_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                             service_date datetime2,
                            val  int)
CREATE TABLE dbo.stg_table (stg_table_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                            service_date datetime2,
                            val  int)

PK's + Clustered indexes on identity fields.

DML

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val)
SELECT -- 1M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.st_table_1 WITH(TABLOCK)
(
service_date,val)
SELECT -- 2.5M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(2500000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val)
SELECT -- 4M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(4000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

2.5M rows in dbo.st_table_1 and 5M rows in dbo.stg_table
(Almost) all of these 2.5M rows will be deleted by the query Which is a more than 10 times less than yours.

Running your query

The actual execution plan for your base delete statement

As expected dbo.stg_table is accessed twice to get the max & min values with a stream aggregate.
The cpu time & elapsed / execution time:

  CPU time = 4906 ms,  elapsed time = 4919 ms.

A missing index hint is added to the execution plan:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[st_table_1] ([service_date])
INCLUDE ([stg_table_1_ID])

However, when we add the index, an extra sort appears to delete the rows from this newly added index:

The plan

And the cpu time / elapsed time increases:

   CPU time = 11156 ms,  elapsed time = 11332 ms.

YMMV, but from my example, based on your comments about data, it did not improve the query.

Creating an index on  [dbo].[stg_table]

CREATE NONCLUSTERED INDEX IX_service_date
ON [dbo].[stg_table] ([service_date]);

As a result the MAX() and MIN() can leverage the newly created index to return only one row instead of a full clustered index scan:

With the execution time improved:

 SQL Server Execution Times:
   CPU time = 2609 ms,  elapsed time = 4028 ms.

And the execution plan

But this is only based on indexing & my own example.
Proceed at your own risk.

Extra Notes

You should look into splitting that delete into separate batches so it won't fill up the log file & not having one big block of failed / succeeded delete .

You could also consider using (TABLOCK) so the entire table is locked from the very beginning.

SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

Update: SELECT INTO + sp_rename

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

  how about if go with above logic to delete the millions of records.
  any advantages and disadvantages with that.

Apart from performance, sp_rename needs a Sch-M lock to complete, meaning that it has to wait for all other sessions to release their locks on the table before it can be modified. Any indexes/constraints on the original table will be gone and you will have to recreate them.

When I run the query on my own data:

select * into dbo.temp_stg_table_1
from dbo.st_table_1
where service_date not between( select min(service_date) from dbo.stg_table)
                             and (select max(service_date) from dbo.stg_table);

This does not represent your data, keep that in mind.

It is reading all rows to return 0 which is not optimal.

With a high execution time:

 SQL Server Execution Times:
   CPU time = 27717 ms,  elapsed time = 10657 ms.

But this is not really meaningful without more information regarding your data.
A query plan would be needed to give out more correct advice.


SQL query on SUM function plus JOINS Sorry if this has been covered before but I couldnt find an example to my issue

Situation: Say I have a table of sales and want to display them

Step 1)

SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW());

Now I want to sum sales or 'amount
Step 2)

SELECT SUM(amount) as 'Total Amount'
FROM
(SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW()
);

Is it possible to have that 'total amount' as a new row in my first table? Ie join the list of payment details with a total amount figure at the end of the list?

As I understand payment_id is unique (maybe primary). If so:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW())
GROUP BY payment_id WITH ROLLUP;

If it is not unique, or if ONLY_FULL_GROUP_BY enabled:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01'
AND payment_date <= NOW())
GROUP BY payment_id, payment_date WITH ROLLUP
HAVING payment_date IS NOT NULL or payment_id IS NULL;

Last query will give wrong result if payment_date can be NULL.


mysql - large table - index performance and query issue I have simplified a much larger table below:
CREATE TABLE `core` (
  `id` int NOT NULL,
  `loc_country` enum('United States','Colombia','United Kingdom',
       'Australia','India','Germany','Canada','Korea','Netherlands',
       '200 more')  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `loc_city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `job` enum('a','b','c','d') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
          ROW_FORMAT=COMPRESSED

explain format=json
    SELECT id FROM core
        WHERE id!=518601449
          AND loc_country='Mongolia'
          AND id < 518601449
          AND job='a'
        LIMIT 151\G

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14002.99"
    },
    "table": {
      "table_name": "core",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "loc_country_2"
      ],
      "key": "loc_country_2",
      "used_key_parts": [
        "loc_country",
        "job",
        "loc_city",
        "id"
      ],
      "key_length": "34",
      "rows_examined_per_scan": 45657,
      "rows_produced_per_join": 45657,
      "filtered": "100.00",
      "using_index_for_skip_scan": true,
      "cost_info": {
        "read_cost": "9437.29",
        "eval_cost": "4565.70",
        "prefix_cost": "14002.99",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "id",
        "loc_country",
        "job"
      ],
      "attached_condition": "((`api`.`core`.`job` = 'a') and (`api`.`core`.`loc_country` = 'Mongolia') and (`api`.`core`.`id` <> 518601449) and (`api`.`core`.`id` < 518601449))"
    }
  }
}

The query took 14 seconds to run, I need it to be done in 0.01 seconds
The biggest problem seems to be to use id < XXX and order by id, I thought that this should be "free" to use given that the id is primary key.
I need the id < and sort because I need to get a different portion out of the database with each query, if I'd not use it I would receive the same data with each query for each country+job
I can not parition the table as I have dozens of such queries using different columns, it's just one example.
I believe the compression makes a big impact, it might be responsible for most issues I have though I don't have the storage on my NVME disks to run without compression.
Would it help to add the primary key to the indexes I have? At the end ? I fear it would waste a lot of storage space.
Any ideas ?

ASPECT #1: Indexing
There is no need to add the PRIMARY KEY value id to the index. Why ?
According to the MySQL Documentation of Clustered and Secondary Indexes, Paragraphs 1,2 under the Subheading How Secondary Indexes Relate to the Clustered Index say the following:

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

Thus, the primary key is automatically added to a secondary index.
The proof of this is in the XML output
  "used_key_parts": [
    "loc_country",
    "job",
    "loc_city",
    "id"
  ],

and
  "used_columns": [
    "id",
    "loc_country",
    "job"
  ],

ASPECT #2: Table Compression
Compression might be greatly contributing to the slowness. After all, InnoDB must decompress data coming from that table for it to reside in the InnoDB Buffer Pool. In addition, the InnoDB Buffer Pool holds both compressed and uncompressed data and index pages. How so ???
According to the MySQL Documentation on How Compression Works for InnoDB Tables
, Paragraphs 1,2 under the Subheading Compression and the InnoDB Buffer Pool say the following:

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes (or a smaller size if innodb_page_size is set). To access the data in a page, MySQL reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.
To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, MySQL can evict from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page might be written to disk, to free space for other data. Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

SUGGESTION #1
Since the Buffer Pool holds both compressed and uncompressed pages, maybe increasing the InnoDB Buffer Pool size might slightly improve things. How ?
InnoDB has to evict unused pages from the Buffer Pool. A larger buffer pool reduces the number of times the Buffer Pool hasd to be cleared to make waves for new pages.
SUGGESTION #2
Perhaps changing the size of compressed pages might slightly improve things. This would require reloading the table with the new compression size. Please see my old post innodb_file_format Barracuda on changing the KEY_BLOCK_SIZE would be needed.
UPDATE 2020-12-12 19:52
Here is a crazy trick to try
Instead of your query
SELECT id FROM core
WHERE id!=518601449
AND loc_country='Mongolia'
AND id < 518601449 AND job='a'  LIMIT 151\G

Try refactoring as follows
SELECT id FROM (SELECT id FROM core WHERE loc_country='Mongolia' AND job='a') A
WHERE id < 518601449 LIMIT 151\G

This will force the loc_country_2 index to gather ids in the subqueries first. Then, all IDs < 518601449 are dismissed. Finally, the LIMIT 151 is imposed.
I do not guarantee the result will be better. It could be worse.
You won't know until you try it out and see or at least look at the EXPLAIN plan !!!


create mongodb user to only read in replica set (not in master) We have a Mongodb with ReplicaSet and a readonly user. This user can read from both Master and Replica Set. Some of the developers might accidentally run heavy queries from Master with this readonly user. Can be restricted to readonly in ReplicaSet instance and not in Master?

User authorizations are same automatically at primary and secondary nodes, so answer is no.


This is really basic SQL, anurag:

SELECT column1, MAX(column2)
FROM my_table
GROUP BY column1;

Suggest you read up on some more basics.


Monitoring the Postgres query parser As I understand it, when you send a query to Postgres like:

SELECT id FROM table1 WHERE name = $1;

Postgres will create a query plan.  The plan will be cached for the same query in the same session.  But if you create a function:

CREATE OR REPLACE FUNCTION get_table1 (parname text) RETURNS bigint
LANGUAGE plpgsql AS
$$BEGIN
  RETURN (SELECT id FROM table1 where name = parname);
END$$;

The query plan will be cached for all future sessions.

I'd like to verify this theory by checking how often the query analyzer is invoked.  Is there a way to inspect the number of times Postgres parses a query?

If possible, I'd like to monitor things like #parses per second and the min/max/avg parse duration.

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions.

Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning.

It's important to note that there have been substantial changes since Postgres 9.1, where generic plans were more generally saved and reused for statements inside PL/pgSQL. This has become a lot smarter with pg 9.2.

The manual (bold emphasis mine):

  Prepared statements only last for the duration of the current database session.

That's not saying anything about query plans, yet:

  When the PREPARE statement is executed, the specified statement is
  parsed, analyzed, and rewritten. When an EXECUTE command is
  subsequently issued, the prepared statement is planned and executed.

And further down:

  If a prepared statement is executed enough times, the server may
  eventually decide to save and re-use a generic plan rather than
  re-planning each time. This will occur immediately if the prepared
  statement has no parameters; otherwise it occurs only if the generic
  plan appears to be not much more expensive than a plan that depends on
  specific parameter values. Typically, a generic plan will be selected
  only if the query's performance is estimated to be fairly insensitive
  to the specific parameter values supplied.

How to tell whether a generic plan is used?

  To examine the query plan PostgreSQL is using for a prepared
  statement, use EXPLAIN. If a generic plan is in use, it will contain
  parameter symbols $n, while a custom plan will have the current actual
  parameter values substituted into it.

A saved plan is always a generic plan, but not vice versa.
Where are prepared statements saved?

  You can see all prepared statements available in the session by
  querying the pg_prepared_statements system view.

Query plans for SQL statements in PL/pgSQL

Those are not normally visible in EXPLAIN output. You can install the additional module auto-explain to write those plans to the log, too. Details here:

Postgres query plan of a UDF invocation written in pgpsql

For details about Plan Caching in PL/pgSQL functions, best read the manual directly.


Error "The publication '[Name]' does not exist." running sp_article_validation DECLARE @p SYSNAME, @a SYSNAME
SET @p = N'[NAME]'
SET @a = N'[ARTICLE]'

EXEC sp_article_validation @p,@a

I'm using the exact name of the publication provided by the table MSpublications (even using NAME or QUOTENAME(N'Name') generates the same error), yet it's saying it doesn't exist.  Note that it doesn't work with any other publications either.  Yet this is what sp_article_validation (Transact-SQL) says.

SELECT publication
FROM MSpublications
ORDER BY publication

Ensure you're on the 'publication' DB so USE [publicationDBName] and then execute the SP.

Otherwise, try various combinations such as the below for the NAME variable you're passing to the SP as an argument to the SP in case it's related to it not liking the preceding 'N which indicates that the subsequent string is in Unicode, thereby passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

SET @p = NAME
SET @p = [NAME]


Why doesn't this invalid subquery raise a syntax error? Is there any explanation so as to why SSMS did not raise any compilation error for the below delete query with a subquery that is invalid on its own?

Below is the sequence of steps to reproduce the behavior:

CREATE TABLE [dbo].[delete_test]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) NOT NULL
) ON [PRIMARY];

Seed data:

insert dbo.delete_test(name) values(N'a'),(N'b'),(N'c'),(N'd'),(N'e');

Create a backup table:

select id, name
  into dbo.delete_test_backup
  from dbo.delete_test where id > 2;

Below statement should ideally not execute and, even if it executes, should only delete rows with id values that have been backed up :

delete dbo.delete_test where id in (
    select id delete_test_backup
);

The message shows that 5 rows were deleted, but I expected only 3 rows to be deleted:

(5 row(s) affected)

Attempt to execute the sub-query as a standalone fails :

select id delete_test_backup ;

  Invalid column name 'id'

Below one executes as expected :

select id from dbo.delete_test_backup ;

  Output :

id
--
3
4
5

This is the same as:

(
  SELECT id AS delete_test_backup
)

In other words, you have merely assigned a column alias to id, and there is no reference at all to a table called delete_test_backup.

According to the ANSI standard, what SQL Server is supposed to do in this case - when it has a subquery and does not find id at that scope - is traverse to the outer scope(s) until it finds one. If it does, it assumes that's the one you meant.

See:

Best practices : Properly referencing columns
KB #298674
Connect #735178
Connect #624370
Connect #392492
Connect #362016
Connect #265772
Connect #772612

  * Microsoft decided to eradicate all of Connect instead of archiving the content, and also disappeared the knowledge base article for some reason.

To avoid the problem, always use table aliases, which allow you to be 100% explicit about which table a column comes from (not naming every key column id helps, too). Of course you can leave these prefixes out accidentally, just like you left out the FROM, but the following syntax would have failed as you expect, and most other omissions would have left you with a parsing error rather than accidental execution:

(
  SELECT x.id FROM delete_test_backup AS x
)


How to verify that a PostgreSQL base + WAL backup has been restored correctly Coworkers were trying to extract a PostgreSQL database copy from a backup made at a hot standby in version 9.1, but it wasn't reliable - we would run it daily, but usually it would end up with various errors while running queries against the copy.

Sadly I hadn't been able to find a definitive answer as to why on the web, and it took a kind soul in the PostgreSQL IRC channel to set me straight - taking a backup like that from a standby isn't supported out-of-the-box in that version.

So for the benefit of others who may run into the same problem and try to google it, I'm going to write up our notes in an answer below.

The answer will contain two sections - first, what's acceptable to see in the logs after the restore, and second a few examples of what is not. The first section should be fairly deterministic, while the second one is basically a random assortment of whatever happened to us that indicated we had a problem.

Acceptable log output

at the start:

2015-07-23 06:51:24 UTC LOG: database system was interrupted; last known up at 2015-07-23 02:10:42 UTC

It's important to see that the restoring PostgreSQL knows when it was last up. I think that's so because that means it's starting from a checkpoint.

xlog min recovery request ... is past current point

Right at the beginning, a few of these can happen:

2015-07-23 06:51:30 UTC WARNING:  xlog min recovery request 1027/B0A28D98 is past current point 1027/2BE36DA8
2015-07-23 06:51:30 UTC CONTEXT:  writing block 0 of relation base/117264/9551898_vm
       xlog redo insert: rel 1663/117264/8310261; tid 68622/40

But according to http://www.postgresql.org/message-id/CAB7nPqTd43hqpuC+M8fo+xkqHv1WtFe_16NUttu1pHcBtZhZmw@mail.gmail.com that is harmless

FATAL: the database system is starting up

Any number of these can happen:

2015-07-23 06:51:24 UTC FATAL:  the database system is starting up

This should actually be harmless because they were in our case the result of automated SELECT 1 ping-like queries that scripts run to check that PostgreSQL is ready.

unexpected pageaddr ... in log file ..., segment ..., offset ...

At the end, there's this:

2015-07-23 06:52:21 UTC LOG:  restored log file "0000000100001027000000B2" from archive
2015-07-23 06:52:21 UTC LOG:  consistent recovery state reached at 1027/B2F8F2F8
sh: 1: cannot open ../../../wal_backup/0000000100001027000000B3: No such file
2015-07-23 06:52:21 UTC LOG:  unexpected pageaddr 1027/AA000000 in log file 4135, segment 179, offset 0
2015-07-23 06:52:21 UTC LOG:  redo done at 1027/B2F8F2F8
2015-07-23 06:52:21 UTC LOG:  last completed transaction was at log time 2015-07-23 02:17:33.842307+00

But according to http://www.postgresql.org/message-id/CAGrpgQ-BbXUNErrAtToYhRyUef9_GdUQz1T3CXbpTMLTnuKANQ@mail.gmail.com that's also harmless

Note that there may be more of the WAL restorations after that point:

2015-07-23 06:52:21 UTC LOG:  restored log file "0000000100001027000000B2" from archive

That would merely mean that you supplied more WAL files via recovery.conf than strictly necessary.

00000002.history: No such file

At the very end of the WAL unroll process there's this:

sh: 1: cannot open ../../../wal_backup/00000002.history: No such file
2015-07-23 06:52:21 UTC LOG:  selected new timeline ID: 2
sh: 1: cannot open ../../../wal_backup/00000001.history: No such file
2015-07-23 06:52:21 UTC LOG:  archive recovery complete

This is apparently/hopefully irrelevant, because that's where the restored database (clone) starts a new life (timeline).

Unacceptable log output

at the start:

2015-07-20 12:38:31 UTC LOG: database system was interrupted while in recovery at log time 2015-07-20 01:41:22 UTC

This is critical - it means that the backup process did not start at the right time - after a pg_start_backup(...) checkpoint - rather that the database was working normally and was at some random point, which means that this restore is more akin to restoring a crashed database.

missing chunk in pg_toast...

This indicates that the restore wasn't right. As a quick fix, we tried the recipe from http://postgresql.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-td5682176.html

mydb=# vacuum analyze mytable; -- trigger the error to see the problem toast
ERROR:  missing chunk number 0 for toast value 13044178 in pg_toast_2619
mydb=# reindex table pg_toast.pg_toast_2619;
REINDEX

This could sometimes get the table back in a working state, but it would also sometimes not have that effect. After that we poked at it some more, and thought we found it's just pg_statistic which is disposable:

mydb=# reindex table pg_statistic;
ERROR:  could not create unique index "pg_statistic_relid_att_inh_index"
DETAIL:  Key (starelid, staattnum, stainherit)=(884792, 34, f) is duplicated.
mydb=# delete from pg_statistic;
DELETE 188540
mydb=# reindex table pg_statistic;
REINDEX
mydb=# vacuum analyze mytable;
VACUUM

right sibling's left-link doesn't match

CREATE TABLE "myschema"."mytable" ( ... )
ERROR: right sibling's left-link doesn't match: block 27 links to 21379 instead of expected 21393 in index "pg_depend_reference_index"

We tried to quickly bypass this by doing:

mydb=# set zero_damaged_pages=on;
SET
mydb=# reindex table pg_depend;
REINDEX
mydb=# set zero_damaged_pages=off;
SET

could not read block in file ...

2015-05-12 13:32:53 UTC ERROR:  could not read block 76408 in file "pg_tblspc/4606764/PG_9.1_201105231/117264/4614269": read only 0 of 8192 bytes

This was obviously a bummer. We couldn't quickly hack our way around this:

mydb=# select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind from pg_class cl join pg_namespace nsp on cl.relnamespace = nsp.oid where relfilenode = 4614269;
 relfilenode | schema_name | relname | relkind
-------------+-------------+---------+---------
     4614269 | myschema    | mytable | r
(1 row)

mydb=# select pg_relation_filepath('myschema.mytable');
               pg_relation_filepath
---------------------------------------------------
 pg_tblspc/4606764/PG_9.1_201105231/117264/4614269
(1 row)

% sudo ls -lah /var/lib/postgresql/9.1/main/pg_tblspc/4606764/PG_9.1_201105231/117264/4614269
-rw------- 1 postgres postgres 597M May 11 19:22 /var/lib/postgresql/9.1/main/pg_tblspc/4606764/PG_9.1_201105231/117264/4614269

That was a good indicator that too much data was getting "lost".

duplicate key value violates unique constraint "pg_type_typname_nsp_index"

This was another indicator that the restore was broken:

CREATE TABLE "myschema"."mytable" ( ... )
ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(mytable_mycolumn_seq, 3780903) already exists.

The quick hack for this was to move the sequence position:

SELECT setval('mytable_id_seq', (SELECT MAX(id) FROM mytable));


Is the inverse of Armstrong's Axioms valid as well? I have been looking into Armstrong's axioms a little bit. In a homework[1] exercise I was asked to prove A→G is in F-closure. I managed to get it at this point:

AB → GB

Being at this point, can I simply say that A → G as it seems pretty straightforward or do I have to go through certain steps as well. As Armstrong's axiom states:

if X → Y then XZ → YZ

Would the inverse be true as well ?

if XZ → YZ then X → Y  <-- ?

[1] I don't think the content of the homework does relate in here.

No, that is not true.

Armstrong's axioms are a sound and complete  axiomatization of the logical implication for functional dependencies.

Here is a relation, where the functional dependency XZ → YZ holds:

X  Y  Z
--------
x1 y1 z1
x2 y1 z1
x1 y2 z2
x2 y2 z2

But the functional dependency X → Y does not hold, as the tuples x1 y1 z1 and x1 y2 z2 show. Because of the soundness of Armstrong's axioms, which means that only valid functional dependencies can be derived, the "inverse" is not true. So we have constructed a counterexample.

If this "inverse" law would hold, then we can show that X → Y for arbitrary attribute sets X and Y:

Y → Y      (reflexivity)
XY → XY    (augmentation)
XY → Y     (reflexivity)
XY → YY    (idempodency of union-operator: Y=YY)
X → Y      ("inverse")


SQL Server process suspended while executing simple SQL (one row deletes and selects) We have a problem on MS SQL Server. Processes get suspended. Looks like it is caused by simple SQL queries like Delete from x where id = @x, or select * from table where id = @x. Several similar parallel queries are being executed like delete one row. Process stays suspended. Any suggestions how to solve this problem?

In server reports no Locks or Blocks are reported. Process is suspending itself. There are no concurrent users or processes (it looks to me)

Even though you're deleting one row, do you know the code that is being executed?  Could still be locking the tables you are deleting from.  Triggers that contain other triggers are notorious for this.

Cursors go row by row but they will put locks as well I believe so it might mean that tweaking the code is in order so that it's more set-based deletes and not row-based.


CREATE USER fails when I'm trying to replicate the SQL required to create a user with privileges, if I use HeidiSQL GUI interface to create a USER and set the privileges it works, then I delete the user from the User manager in HeidiSQL and copy SQL from echoed response area at the bottom of HeidiSQL:
CREATE USER 'simon'@'localhost' IDENTIFIED BY 'password';

Just the above, nothing more, but it fails with a dialog containing:
SQL Error (1396): Operation CREATE USER failed for 'simon'@'localhost'

You may need to perform a FLUSH PRIVILEGES; before trying to create an account that was recently removed. This is the result of a very old bug in MySQL server. By flushing the privileges the system will properly “forget” that simon@localhost existed.


Does MySQL limit the number of users (not connections) I have always believed that you can create as many users as you like. I have been creating a user per app with limited database/table access for only what that app requires.

Recently I was told there is a limit. While I have never heard of one, or reached one, I still wanted to ask the community.

Note: I am not referring to max connections, but the number of users.

User accounts are again stored in tables in terms of rows and columns and also its respected permissions under mysql database wherein it has primary key as host and username. So, I don't think so you have any limitations. But you have something called max_connections that has to be set to a value, say it has 100000 as max that you can set. Then keeping each user connection in mind then we can go upto 99999 users connected to it parallely.

My question to you is.

Why do you need all different users to be connected to the mysql connection pool?
It is highly impossible to make a note of users permissions and its passwords.
If you need those many users for application purpose, you may try doing it in regular innodb tables and have list of users under one group and tag them to a role. Just grant multiple roles to the users who needs the access.


Convert Ruby regex to Postgres regex, for selecting invalid email addresses I've done some internet trawling, and some reading of the Postgres regex docs, but I'm at the stage where I think asking a question here is the best thing to do.

I have the following Ruby regex:

/\A[^@\s]+@([^@\s]+\.)+[^@\W]+\z/

Which I am trying to convert into a Postgres regex.
So far I have come up with:

'\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z'

As I found this in one article online:

  Thus, + is a metacharacter in PostgreSQL, whereas \+ is a plain “plus” character.

and this in another:

  The backslash is used to escape characters in PostgreSQL strings. So a regular expression like \w that contains a backslash becomes \\w when written as a literal string in a PostgreSQL statement. To match a single literal backslash, you’ll need the regex \\ which becomes \\\\ in PostgreSQL.

The total query I'm running is this:

SELECT email
FROM users
WHERE email !~ '\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z'

And I'm expecting it to return zero results, bit it looks like it is selecting all email addresses.

I'm expecting no results because I've used the operator !~ (I've also tried !~*), which I think returns non-matches, and because I'm pretty sure that all the email addresses in my database do match the Ruby regex I pasted at the top of this question. (This in turn is because we use Devise in a Rails app to validate all user emails, with the default email validation settings)

So I've come to the conclusion that I've not converted my regex format/syntax correctly for Postgres, but also that I don't know what else I'm missing. Please can you help me understand whether this is possible, and if so, what else I need to change.

Once I have successfully translated that regex, I have a second Ruby regex which I also want to translate into Postgres, which is this one:

/\A[a-zA-Z0-9.!\#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*\z/

This is because I want to find all the user emails that don't have a valid email address according to the spec here: https://html.spec.whatwg.org/multipage/input.html#valid-e-mail-address

Thanks so much in advance

You should debug regexp by first just selecting the raw text make sure it is perceived at the textual level the way you want.

select '\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z';

                 ?column?
------------------------------------------
\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z

So the doubled backslashes did not get condensed to single, the way you probably thought they would. Note that this is with the default (on) setting of standard_conforming_strings setting.

So regexp parser is going to see a backslashed backslash, which will match a literal backslash character, followed by a literal 'A', which will match a literal 'A'.  Which is obviously not what you want.

You can remove the double backslashes.  Then you have to worry about whether all the notations you use are supported in PostgreSQL. \W is illegal in PostgreSQL within brackets, so at least that one is not supported.


PostgreSQL Replication on a daily basis With PostgreSQL, I want to replicate a subset of all tables from Database A to B, but only once a day. Since I do not know what to search for: is there a technical term for such a scenario? Does e.g. Slony-I support this?

You can achieve this by stopping the replication, which is the slon daemon. By stopping the daemon, sync event get accumulated on sl_log tables on master. When slon get started again, this accumulated logs will get synced to slave.

Hope it helps.


How to remove data redundancy in product table if multiple suppliers are there for same product I am designing a database where I have different products and suppliers.
For example, I have a product, "mobile phone", say an Apple 5S. This will be sold through my website from different suppliers like Mapple, Mango.
I can not store the data for every single supplier as it will cause data redundancy.  I have been considering the following columns for the products table:

ProductID
SupplierID
ProductName
Value
SKUID
VendorName
and many more...

We can see the Product name is repeated every time which caused data redundancy.
My main concern is how to avoid data redundancy? How to design the Product table?
Added from comments:

There will be a many to many relationship between Products and Suppliers.
A Vendor and a Supplier are the same. It is a small organisation like shop owner who are listed to us.
There will be number of different shop owners and they all may have the same Product. It will increase everyday. Suppose we have 1000 shop owners (Vendors) with us, and each has 100 Products which are the same, then there will be 1000*100 rows for data which is not required.
There is correction in table Vendor and Supplier are the same. About individual or person. They will never be Supplier.

Business rules
Based on (a) some deliberations we had via comments and (b) the content of the question, we have defined the following characteristics of your business environment:

A Product is primarily identified by its Id
A Product is alternately identified by its Name
A Product is alternately identified by its SKU
A Product is offered at a Price
Supplier and Vendor are different names you use to refer to the same particular entity type
An Organization can become a Supplier
An Organization is primarily identified by its Id
An Organization is alternately identified by its Name
A Person cannot become a Supplier
A Product can be offered by zero-one-or-many Organizations playing the Role of Suppliers
An organization, playing the Role of Supplier, supplies one-to-many Products

So, among other points, there is a many-to-many (M:N) association or relationship involving the entity types called Supplier and Product. This is a very basic —but, at the same time, quite powerful when modeled accurately— construct usually employed to shape some components of a database conceptual model.
IDEF1X model
Consequently, I created an expository IDEF1X1 diagram that consolidates the previously formulated business rules in a single graphical device, which is shown in Figure 1:

As you can see, ProductSupplier is the associative entity that portrays the M:N association that connects the Product and Organization.
Each ProductSupplier is uniquely identified by the combination of values of two distinct properties or attributes, i.e.,

SupplierId (a role name I have assigned to Organization.OrganizationId in order to express the meaning that such attribute holds when it migrates2 to the ProductSupplier entity type) and

ProductId,

so they are depicted as a composite PRIMARY KEY. These properties are FOREIGN KEY references to Organization and Product, respectively.
With this layout, I have arranged the properties that pertain to a each entity type in their appropriate place:

The properties that arise regarding the association between Product and Organization (let us say, the property that denotes the point in time when a given Organization started suppliying a certain Product) are included exclusively in the associative entity type.

The properties that concern only to the Organization entity type are placed in the corresponding box, an so are the properties that belong to the Product entity type.

Expository logical SQL-DDL structure
I wrote the DDL statements that follow based on the conceptual definitions discussed above, so that you can get a form a better idea about how to design this part of your database:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on the exact business context characteristics.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Organization (
    OrganizationId  INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Product (
    ProductId       INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    SKU             CHAR(30) NOT NULL,
    Price           INT      NOT NULL, -- Retains an amount in Cents, but there are other options.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Product_PK      PRIMARY KEY (ProductId),
    CONSTRAINT Product_AK1     UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT Product_AK2     UNIQUE      (SKU),  -- ALTERNATE KEY.
    CONSTRAINT PriceIsValid_CK CHECK       (Price > 0)
);

CREATE TABLE ProductSupplier ( -- Represents the conceptual M:N association.
    SupplierId      INT      NOT NULL,
    ProductId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT ProductSupplier_PK               PRIMARY KEY (SupplierId, ProductId), -- Composite PRIMARY KEY.
    CONSTRAINT ProductSupplierToOrganization_FK FOREIGN KEY (SupplierId)
        REFERENCES Organization (OrganizationId),
    CONSTRAINT ProductSupplierToProduct_FK      FOREIGN KEY (ProductId)
        REFERENCES Product (ProductId)
);

In this logical structure, as demonstrated:

each table denotes an individual entity type;
each column stands for an property of the respective entity type;
a specific data type is fixed for each column in order to ensure that all the values it contains belong to a particular set (that you will have to adapt to adapt to your organization needs), be it INT, DATETIME, CHAR, etc.; and
multiple constraints are set up (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined in the conceptual model.

All these factors help to prevent ambiguities that would have come about if multiple entity types are represented by way of a single table.
Derivable information
Successively, you can derive data from the three different base tables —e.g., by dint of JOINs— in order to take advantage of the logical structure and produce new tables that include information like shown in the pictures you included in the question.
Redundancy
In a database that is designed following the principles of the relational model, duplication of values contained in columns is not only acceptable but expected, while duplicate rows are strictly forbidden.
As for the logical structure expounded above, all the tables are constrained declaratively so as to prevent the INSERTion of duplicate rows, therefore the avoidance of this kind of harmful redundancy is well addressed.
Normalization
Relational normalization —a subject worth mentioning since you added the normalization tag— is a logical-level procedure whose purposes are

to decompose via first normal form the table columns whose types accept non-atomic values so that data manipulation and constriction are much easier to cope with by the data sublanguage of use (e.g., SQL), and

to get rid of undesirable dependencies among the columns of a table by virtue of the successive normal forms to avoid update anomalies that introduce other kinds of dangerous redundancies.

Of course, one has to take into account the meaning carried by the table(s) and column(s) at issue.
I suggest that you take the time to test the tables I present in the previous logical structure so that you can determine if it meets each normal form.

Endotes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


Design for file based with many events I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into /date/guid.cvs. The sync part is done but now I need to take the synced files and dump them into a database for a reporting system. This all gets stored on a client's computer so I need a file database for ease of use. I've been using sqlite.

Now for my question. Each row in the csv file is an event with data defined:

Timestamp, vehicle length (single), controller state (int), turn type (int), queue length (int), other ints defining the event

I was also planning to include the guid from the csv filename. The problem I see is that this single table will have many, many rows. Just on a small amount of data (couple days) I already have over 100k rows. This will need to handle potentially years worth of events.

Is there a good way to reduce the row amount or is it not going to be an issue? Am I missing some way to design the table(s) so they don't grow so large? I saw something about partitioning but I'm not sure sqlite supports that. Is there another file database that does? I'm using c# 4 as the front end for the client.

Looking at your table structure means that while the actual row is small (many int values) there will be millions of rows.

Yes you can use a database of your choice, MySQL, Oracle, SQL Server, PostGres for the raw data from the cameras.

You can add partitioning by day which will create a partition for each day, and you may want to consider archiving data on an annual basis into a separate table for each year to make the table sizes manageable.

I would recommend that you setup another set of summary tables for reporting which can be based on the partitions, and this can be done on a daily basis (per partition) and automated.

The actual design of the summary tables depends on the reports you need to run, but it will be necessary so that your queries do not run off the base data table which will be huger.


PostgreSQL Common Table Expressions vs a temporary table? The PostgreSQL documentation on WITH shows the following example:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

It also notes:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries.

I see that WITH can be used for other things, like recursive evaluation. But in the example above, is there any important difference between using WITH and creating temporary tables?

There are a few subtle differences, but nothing drastic:

You can add indexes on a temp table;
Temp tables exist for the life of the session (or, if ON COMMIT DROP, transaction), wheras WITH is always scoped strictly to the query;
If a query invokes a function/procedure, it can see the temp table, but it can not see any WITH table-expressions;
A temp table generates VACUUM work on the system catalogs that WITH doesn't, it needs an extra round trip to create/fill it, and it requires extra work in the server's cache management, so it's slightly less efficient.

Overall, you should prefer WITH to temp tables unless you know you will benefit from creating an index.

However, the other option, a subquery in the FROM clause, has a very different set of advantages. It can be inlined, in particular, and qualifiers can be pulled up / pushed down. I wrote about this in a recent blog article.