texts: Same product name with different price in different states

texts:
Consider the price of products in the table differs in each state. If the price varies in district I also have to mention it. How can I do this for more than 5 states?

product Name   Price  Wholeseller Price Retailer price   state     district
-------------|------|------------------|---------------|-----------|------
fan          |   6  |   12         |     18            | delhi      name1
wheels       |   10 |   17         |     27            | delhi      name1
biscuits     |   5  |   10         |     23            | delhi      name1
biscuits     |   6  |   11         |     24            | delhi      name2
fan          |   7  |   13         |     20            | karnataka  ...
wheels       |   9  |   16         |     26            | karnataka  ...
biscuits     |   5  |   12         |     25            | karnataka  ...

Image of table here.

Here I'm repeating the product for each state. Is it a correct method? Should I make a separate table for states & districts or is there a need to normalize?


texts: Compare Two Rows and Find the Difference Between Them Which is In The Second Column?

texts:
I have a table that has the following data:

ID      Name       LastName
1      William     Johns
2      John        Adam
4      William     Smith
3      Mark        Stephan

I want to Find all those records that their Names are the same but there Last Names are different. so the result will be something like this:

ID      Name       LasName
1       William     Johns
2       William     Smith

What would be the MS. Access Query or method to find all these records?


texts: User Defined Function (UDF) in master database

texts:
I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database so that my function can be accessed easily.

Is there any problem in writing the user defined function in master db?

When surfing the Internet some suggested converting the function to a system function. (Using “EXEC sp_ms_marksystemobject  'fn_db_name' ”).

Is there a necessity to convert the function created in master db to system function? If yes why?


texts: Is it possible to copy a dev. database schema over a prod. one, but keep the prod. database' data?

texts:
I'm using MySQL 5.6.  I have a production database and a development database.

As I'm developing the application, I sometimes need to add new columns/tables to the database.  Is it possible, when I'm ready to update the production database, to take the dev. schema and "lay it over" the production schema, but let the production server keep its data?

Currently, I'm just using mysqldump and dumping out the whole database, and then copying it over the top of the other.  It's working at the moment because the app isn't entirely in production mode, yet, but it's not gonna work forever.


texts: I have a DSN that uses Integrated Security. I'm trying to setup a linked server that uses that DSN but I'm getting the error below. I have successfully tested my connection using another tool so I know it works.


texts:
SELECT gen_random_uuid()

produces output

ERROR:  function gen_random_uuid() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I ran CREATE EXTENSION pgcrypto; on selected database and SELECT gen_random_bytes(1) works perfectly (gen_random_bytes doesn't work on other databases where pgcrypto extension was not manually created).

% psql --version
psql (PostgreSQL) 9.5.3

Ubuntu version is 16.04.


texts: How and when to use sys_refcursor in oracle

texts: Can some one give me a small explanation about how and when should someone use sys_refcursor ?


texts: Movement of Standby database from one location to another location

texts:
Looking for help on below mentioned process:

We are planning to move the standby database from Location A to Location B. I need your help to move this database using lift and drop method, by shutting down the database moving from one location to another and after movement bring up the server and sync with the primary database.

Need your help to identify the steps required / or to be followed for performing this activity. Mainly I am looking for details on backup and archive.

We are planning to shut down the Location "A" office and to want to reuse the existing server by physically moving the server to location "B". We have time and resource constraints for having a second standby.

More information on the primary and standby structure:

Currently the Primary is located in location "C" and DBA taking only archive logs in the server path. No backups are taken from the primary database.
2.The standby which we are planning to move to Location "B" in this server we are taking the latest FULL (netbackup) and placing in the same server.

In this current scenario primary is always relying on the standby for backup files.


texts: Nested with - Incorrect syntax near the keyword 'WITH'

texts:
With inside with

This is ok

    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs

http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question

But, as soon as I wrap it with a second with,

with PS as (
        WITH CS AS (
          SELECT      'ab' as a
        )
        SELECT * from cs
        ) select * from PS

I get this error,

Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'.  If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

With inside select

The following is ok

select ab.a from (
  select a from (SELECT 'ab' as a) b
            ) ab

This is also runs

  with cs as (SELECT 'ab' as a)
  select a from cs

But, wrapping expression with another select, as this one proposes,

  select a from (
    with cs as (SELECT 'ab' as a)
    select a from cs
  ) ab

fails again with the same error.

I do it in data.stackexchange and they report that they use MSSQL


texts: MySQL - Master to master replication without downtime in Live production

texts:
I want to setup 2 node master-master replication. Im aware of the point of failiures in this replication. Its a huge database around 1.5TB.
Heavy OLTP is going on. But its mandatory to implement right now.

I have 2 servers.

 1. Master1
 2. Master2

Initially what I did is,

Master1:

Make changes my.cnf for replication.
Take mysqldump with --master-data=2 (For getting exact binlog
position)

Master2:

Restore dump to Master2.
Make changes on my.cnf.
set replication to Master1 (Change master='Master1').
Start Slave.

  Now Master1 is replicating to Master2.

Then I need to replicate the Master2 to Master1.
Just take the binlog position and set replication on Master1 will work, but the problem is binog file and position is changing frequently.
So before execute the Change master command on Master1 the binog get changed.

How can I achive this without downtime.


texts: Automatically generate database creation script with default values for SIZE and FILEGROWTH

texts:
By using a mix of several PoSH scripts I managed to get a working script that generates, copies and remotely executes a script to create the basic structure for a given database with all its objects, logins, etc. But is using for SIZE and FILEGROWTH the values stated on the production environment (say 1GB and 512MB). The goal is to create the database on a dev environment for developers to play with it. So an initial default size and filegrowth will be more than enough. For SQL2016 those values are 8MB and 64MB respectively as stated here by MSSQL Tiger Team.

Is there a way to generate the script with those values instead of the ones configured for the database? Or should I search and replace the values on the script?


texts: Reducing Memory Pressure When Performing Parallel Staging Table Loads

texts:
I'm attempting to migrate several very large tables in my data warehouse to new partition scheme (the older partition scheme was based on a RANGE LEFT function, that wasn't regularly split, and the most recent data is top-heavy in the last filegroup). The tables are sufficiently large (5 billion rows, 400 GB data space), that I'm leery to try a clustered index rebuild for fear of insufficient logging space.

I'm attempting to load the new table by running several INSERT...(WITH TABLOCK) SELECT...WHERE <check constraint predicate> (OPTION MAXDOP 4) statements into identical staging tables, one per filegroup in the scheme which I'll later switch into the replacement table. I coordinate execution of this faux bulk load by individual SSA jobs to take advantage of read-ahead/merry-go-round.

All works pretty well on the relatively small tables will all jobs running simultaneously. However, on the first large table, most of the SSA jobs have RESOURCE_SEMAPHORE waits while the job with the first partition is chugging along.

If my server has 768 GB RAM, 24 cores (hyperthreaded) and it's working on a 400 GB table, should I adjust my MAXDOP downward to free up more grants or enable resource governor to reduce the maximum grant to get more SSA jobs to kick in and work in parallel?

I initially tried the BULK_LOGGED recovery model, but went to SIMPLE. Transaction size is still huge; I generated over 1 TB of log copying 15 GB (must be due to the page splits if the data set wasn't sorted, I'm using page compression, and I was running other than MAXDOP 1).

I'm working on a FTDW rig (local SSD array running about 5 GB/s throughput, PureStorage array peaking at 11 GB/s) so hopefully disk latency won't be an issue. I'm writing the SSA jobs with SMO.

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
    Oct 28 2016 18:17:30
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit)
      on Windows Server 2012 R2 Standard 6.3  (Build 9600: )


texts: Deleting all rows from INNER JOINed 3 different tables

texts:
I have a query. I would like to delete all selected rows in these 3 different tables
Since I have a lot of INNER joins, I couldn't figure it out.
My goal is to delete everything for these seller_ids.

SELECT *
FROM orders a
 INNER JOIN order_items b ON a.order_id = b.order_id
 INNER JOIN order_item_histories c ON c.order_item_id = b.order_item_id
WHERE a.seller_id IN (1, 3)

Version Postgres 10.3

I tried this but I couldn't succeed.

DELETE
FROM
   USING orders
   USING order_items,
   USING order_item_histories
WHERE orders.order_id = order_items.order_id AND order_items.order_item_id = order_item_histories.order_item_id
AND  orders.seller_id IN (1, 3)


texts:
I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  1   | 2015-01-23
EXAMPLE     | 20142015    |    1     |  2   | 2015-06-20
DEMO        | 20142015    |    1     |  1   | 2015-01-23
DEMO        | 20142015    |    1     |  2   | 2015-06-20

What I've done in my test case was:

SELECT TOP 1 * FROM School_Terms
WHERE Term_End >= '2015-01-20'

The problem is this only returns one row and not the first valid row for each school.

My expected results for the term end date '2015-01-20' are:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  1   | 2015-01-23
DEMO        | 20142015    |    1     |  1   | 2015-01-23

and if the Term End was '2015-01-30' I would expect:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  2   | 2015-06-20
DEMO        | 20142015    |    1     |  2   | 2015-06-20


texts: How to Fix log_reuse_wait_desc=LOG_BACKUP? LOG Backup not possible

texts:
I checked sys.databases to find out which databases need LOG backups. It surprised me that I had quite a few where log_reuse_wait_desc=LOG_BACKUP meaning, the JOB I have set up to run LOG backups on these databases isn't actually creating LOG backup files.

After doing some searching, it had been suggested to change database modes from FULL to SIMPLE, then back to FULL to reset things. After doing this I am still unable to get a LOG backup to successfully run.

I am using SQL Server 2005, and use the ola hallengren method of backup found here http://ola.hallengren.com/sql-server-backup.html.

Thank you


texts:
I'm trying to decode html containing html entities.
I've tried dbms_xmlgen.convert and utl_i18n.unescape_reference but the results are less than satisfactory.

SET ESCAPE ON;
SELECT
   'dbms_xmlgen.convert' AS method,
   dbms_xmlgen.convert('\&hearts;', 1) AS hearts,
   dbms_xmlgen.convert('\&amp;',    1) AS amp_ent,
   dbms_xmlgen.convert('\&#0038;',  1) AS amp_dec,
   dbms_xmlgen.convert('\&#x0026;', 1) AS amp_hex,
   dbms_xmlgen.convert('\&#28608;\&#20809;', 1) AS chinese_laser
FROM dual
UNION ALL
SELECT
   'utl_i18n.unescape_reference',
   utl_i18n.unescape_reference('\&hearts;'),
   utl_i18n.unescape_reference('\&amp;'),
   utl_i18n.unescape_reference('\&#0038;'),
   utl_i18n.unescape_reference('\&#x0026;'),
   utl_i18n.unescape_reference('\&#28608;\&#20809;')
FROM dual;

The results I get are:

METHOD                        HEARTS      AMP_ENT     AMP_DEC     AMP_HEX     CHINESE_LASER
----------------------------------------------------------------------------------------------
dbms_xmlgen.convert           &hearts;    &           &#0038;     &#x0026;    &#28608;&#20809;
utl_i18n.unescape_reference   ¿           &           &           &           ¿¿

My actual problem involves Chinese characters, processed by a Java program to create PDF reports. I have no easy access to the Java code, but I do have control over the query used by the program.

An example of Chinese characters that I use for testing is 激光, which Google Translate tells me means 'laser' and which I receive encoded as &#28608;&#20809;. These aren't decoded properly, as can be seen in the example above.

I realise that in the second row, the inverted question marks seem to indicate that the entities were converted, but can't be displayed properly. However, is this Oracle itself doing this, or the client (I tried in both SQL+ and Toad)? When I plug utl_i18n.unescape_reference into the query used by the Java program, it works for entities like &#177; (±), but again, not for the Chinese characters.

How can I have all entities properly decoded?

Should I use another function? (These were recommended somewhere on the Internet).
Should I change some settings? (Relevant settings shown below).

Relevant information

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL*Plus: Release 10.1.0.5.0

NLS_LANGUAGE                           AMERICAN
NLS_TERRITORY                          AMERICA
NLS_CHARACTERSET                       WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                 AL16UTF16


texts:
I am using AWS RDS postgres instance version 10.3.

Completly stuck with the problem.

I have a table like:

create table social_message(
  social_profile_id int8 not null,
  body text,
  subject text,
  conference_id int8 not null,
  created_at timestamp not null default now(),
  sent_at timestamp default now(),
  contact_ids bigint[] not null default '{}'
);

Problem is with equality check with contact_ids column.

SQL query like this shows problem:

 select
   contact_ids,
   '{23673159}'::int8[],
   contact_ids = '{23673159}'::int8[],
   contact_ids @> '{23673159}'::int8[] and contact_ids <@ '{23673159}'::int8[]
 from social_message

Which returns (skipping not interested rows):

contact_ids |int8       |?column? |?column? |
------------|-----------|---------|---------|
{}          |{23673159} |false    |false    |
{23673161}  |{23673159} |false    |false    |
{23673159}  |{23673159} |false    |true     |

Question is how it is possible that = operator does not work? I mean that if A @> B and A <@ B is true, this should be A = B - but it is not!

I am using doing inserts with scala slick. It is doing such prepared statements:

insert into "social_message" ("social_profile_id","contact_ids","body","subject","conference_id","created_at","sent_at")  values (?,?,?,?,?,?,?)

Sample data for whole table:

social_profile_id |body   |subject |conference_id |created_at          |sent_at             |contact_ids |
------------------|-------|--------|--------------|--------------------|--------------------|------------|
42717             |sample |        |3313          |2018-08-14 10:45:18 |2018-08-14 10:45:18 |{}          |
42718             |sample |        |3313          |2018-08-14 10:52:48 |2018-08-14 10:52:48 |{}          |
42718             |sample |        |3313          |2018-08-14 11:07:08 |2018-08-14 11:07:08 |{}          |
42719             |sample |        |3313          |2018-08-14 11:09:06 |                    |{23673161}  |
42719             |sample |        |3313          |2018-08-14 11:09:29 |                    |{23673159}  |
42718             |sample |        |3313          |2018-08-14 12:06:32 |2018-08-14 12:06:32 |{}          |
42721             |sample |        |3313          |2018-08-14 12:15:36 |                    |{23673170}  |
42721             |sample |        |3313          |2018-08-14 12:16:29 |                    |{23673169}  |
42718             |sample |        |3313          |2018-08-14 12:43:13 |2018-08-14 12:43:13 |{}          |
42722             |sample |        |3313          |2018-08-14 12:45:56 |                    |{23673176}  |
42722             |sample |        |3313          |2018-08-14 12:47:24 |                    |{23673175}  |
42723             |sample |        |3313          |2018-08-14 13:26:28 |                    |{23673178}  |
42720             |sample |        |3313          |2018-08-14 11:11:35 |2018-08-14 17:29:29 |{23673165}  |
42724             |sample |        |3313          |2018-08-14 15:09:12 |2018-08-14 15:09:12 |{}          |
42725             |sample |        |3313          |2018-08-14 15:14:41 |2018-08-14 15:14:44 |{23673182}  |
42726             |sample |        |3313          |2018-08-14 15:17:17 |2018-08-14 15:17:18 |{23673185}  |
42728             |sample |        |3313          |2018-08-14 15:19:19 |2018-08-14 15:19:20 |{23673188}  |

I localized problem to jdbc driver. When i do manual insert with psql select works like a charm. But when insert happened within scala code it does not selected.

It is still not clear how it is possible to do.

Thank you


texts: Handling exceptions from inside a function

texts:
I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update statement may throw an error (updating creates a duplicate index on the table), then I need to take a different approach, meaning I need to delete instead of update.

The function, so far, is this:

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS $BODY$
DECLARE found_group bigint;
BEGIN
    SELECT group_id INTO found_group
      FROM inv_items_group_members
     WHERE item_id = NEW.item_id;
     IF found_group IS NOT NULL THEN
        UPDATE public.prod_profiles_steps_items
           SET item_id = found_group
         WHERE item_id = NEW.item_id;

        -- TODO : on error for the last update, this should get executed:
        -- DELETE FROM public.prod_profiles_steps_items
        -- WHERE item_id = NEW.item_id;

    END IF;

    RETURN NEW;
END; $BODY$

How can this be done? All I read everywhere are RAISE. Which is irrelevant here.


texts:
I'm creating a script to automaticly copy from multiple access databases into sql server databases.

My script currently creates a new sql database and creates all of the tables.  It then trys to insert the data from an access database (using linked servers) table into the newly created sql database table.

The problem is not all of the databases have all of the tables.  In these cases it is ok for the sql server table to remain empty.  However, the insert throws an error because the table does not exist in the linked server.

Is there any way to tell sql server to ignore the table not found error so that it will continue on to the next insert statment?


texts: What happens if Always On High Availability nodes have different connection default languages?

texts:
I have been working on databases in a Always On High Availability group which - for reasons that I do not understand - have different default languages for an application's login on the primary and secondary nodes.

The particular situation I have at the moment is this:

The language that the application expects is British English. It does not specify the language that it uses when it connects; it uses whatever the default language is for the login. The documentation for the application says that the default language should be British English, presumably because it does not specify a language on connecting.
The application connects to the listener, not to either of the nodes directly.
The application will often save dates as strings (e.g UPDATE FooTable SET BarColumn = '01\02\2020' WHERE IdColumn = 42). The data will be saved in a date or datetime column.
The default language of the login on the database listener is British English.
The default language of the login on the primary node is British English.
The default language of the login on the secondary node is American English

I am no DBA, and certainly no expert in Always On High Availability. What dangers might there be/have been in using this configuration? Does it matter if the languages on the primary and secondary nodes are swapped?


texts: Using conditions to return specific rows

texts:
From the following table i am trying to retrieve specific rows based on a criteria

+----+--------------+------+
| ID | UPDATED_DATE | TYPE |
+----+--------------+------+
|  1 | 01/13        | P    |
|  1 | 02/13        | P    |
|  1 | 03/13        | P    |
|  2 | 01/14        | P    |
|  2 | 02/14        | W    |
|  2 | 03/14        | P    |
|  2 | 04/14        | W    |
|  2 | 05/14        | P    |
|  2 | 06/14        | P    |
+----+--------------+------+

Expected rows:
+----+--------------+------+
| ID | UPDATED_DATE | TYPe |
+----+--------------+------+
|  1 | 01/13        | P    |
|  2 | 05/14        | P    |
+----+--------------+------+

Unique constraint on ID and UPDATED_DATE

Criteria:

  If type "W" exists in any of the rows for an ID then retrieve the next occurrence of 'P' after 'W'. If not retrieve the first occurrence of 'P'. If there are two 'W' for each ID then get the 'P' after the latest 'W'. I dont want to use window function as it is not supported in HSQL for unit testing.

I was successfully able to get the required row if i pass ID. But i want to run this for the entire table and get the necessary rows.

SELECT
CASE
    WHEN (
        SELECT
            MAX(UPDATED_DATE)
        FROM
            TABLE_NAME
        WHERE
            ID = '1'
            AND TYPE = 'W'
    ) IS NULL THEN
        (
            SELECT
                MIN(UPDATED_DATE)
            FROM
                TABLE_NAME
            WHERE
                ID = '1'
        )
    ELSE
        (
            SELECT
                MIN(UPDATED_DATE)
            FROM
                TABLE_NAME
            WHERE
                ID = '1'
                AND UPDATED_DATE > (
                    SELECT
                        MAX(UPDATED_DATE)
                    FROM
                        TABLE_NAME
                    WHERE
                        ID = '1'
                        AND TYPE = 'W'
                )
        )
END
FROM
   dual;


texts: SQL Server auto restore only log where not restored before

texts:
i want to make an auto restore script for the transaction log only
i have found the script to restore log in directory which have named with dbname_timestamp.trn

the problem is this script will restore all the log files in there directory no matter which log files have restored before or not. and it will get failed in the output screen in sql server management studio.

DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.trn'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
   PRINT (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile
END

the question is, what script to make the restore is only restore the log file only the log where not restored yet before.

for example:
in directory, there are 4 log files, log_1 , log_2, log_3, and log_4. i have restored log_1 and log_2, what script to make restore only log_3 and log_4, because my script will restore the all of log files in there directory.

btw, This is my script (modified from Greg's Scripts)

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @standbydir NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
-- DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'yogalogmanual'
SET @backupPath = 'K:\shared240\logmanual\'
SET @standbydir = 'K:\shared240\logmanual\yogarollback_01.bak'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
   AND backupFile LIKE @dbName + '%'

--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = N'''
--       + @backupPath + @lastFullBackup + ''' WITH FILE = 1,  STANDBY = N''' + @standbydir + ''''
--EXEC (@cmd)

-- to check backup log which not restored yet

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.trn'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
   PRINT (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

Thank You,

Yoga


texts: Cannot do a restore using PgAdmin4 after a new install of PostgreSQL - pg_restore.exe file not found

texts:
I am new to PostgreSQL. I installed the software. Almost everything works. Then I tried to follow a tutorial for dvdrental. All instructions for using from command line worked. Then there is instruction for creating a DB using pgAmdin 4, which also worked. The next instruction is to do a restore. This fails. The error message is 'C:\Program Files\PostgreSQL\13\pgAdmin 4\runtime\pg_restore.exe' file not found. Please correct the Binary Path in the Preferences dialog.
The message is correct. pg_restore.exe is not in the runtime folder, the runtime folder does not exist. There is a bin folder and pg_restore.exe is that direction.
I tried various things such as changing "EDB Advanced Server Binary Path", I also created folder runtime and copied everything that was in bin folder to runtime, but it still will not work. This looks like an installation problem. Same message seemed to have appeared for years, but none of those solutions have worked for me. I would appreciate any help.


texts: What are points to consider when replacing the SQL Server service account?

texts:
In What are the implications of changing the SQL Server Service Logon Account? it mentions that when replacing the SQL Server service account, the service will need to be restarted.

But there is more to it than that. According to Configure Windows Service Accounts and Permissions, the change of logon account should be done through the SQL Server Configuration Manager.

  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

  For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.
  To change Reporting Services options, use the Reporting Services Configuration Tool.

My current environment has some 15 servers between production and development and they all use the same domain account to run the SQL Server services and a different domain account for the SQL Server Agent.

I want to replace this with 15 different pairs of active directory accounts (SQL Server service and Agent).

Questions

What can possibly go wrong? Any gotchas?
Can any part of this process be automated?


texts: What is the best practices for storing sales tax rates?

texts:
In Canada we have a federal general services tax (GST) of 5%. Some provinces have a provincial sales tax (PST). Some provinces have a hybridized sales tax (HST) instead of a separate (GST) and (PST). Some provinces have a tax on Insurance (RST). Extended Warranty on vehicles is taxed GST but only one province taxes it for PST. One province (BC) varies the PST on passenger vehicles where the rate is 7% for < $55,000, 8% for >= $55000 AND < $56000, 9% for >= $56000 AND < $57000 and 10% for >= $57000. There is a definition of what

In the states I read that you don't have a Federal Sales Tax but you do have separate state, county and city sales taxes. Looking at Wikipedia there are many details to the taxes in the states. https://en.wikipedia.org/wiki/Sales_taxes_in_the_United_States

I will likely need to support taxes in other countries as well at some point.

What is the best practice for storing all these tax rates so that applications can use them effectively in real time to compute tax amounts?

What is the best practice for storing the tax amounts for different transactions?


texts:
In MS SQL Server, if I'm connected to two servers (for example dev and production) that both have a DB called MyDb which server is targeted when I run a  script like

use MyDb

SELECT Description
FROM MyDescriptions
WHERE Description LIKE '%something%'

I'm using SSMS. I'm not asking about how to get the information but rather what the consequence will be.


texts: How can I modify this trigger with loops and concat?

texts:
I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name I want it to record:

  old.lastname was changed to new.last_name

If its the first name or gender or DOB or marital or SSN that were updated it should do the same.

How can I achieve this? Can I use the concat function and loops?

CREATE TRIGGER emplog_update AFTER UPDATE ON emp
FOR EACH ROW
INSERT INTO emplog
VALUES (
    NEW.id
    ,NEW.lastname
    ,NEW.firstname
    ,NEW.gender
    ,NEW.dob
    ,NEW.marital
    ,NEW.SSN
    ,'U'
    ,NULL
    ,USER()
    ,('this is where the description will go')
    );


texts: Can't set up mirroring for a specific database

texts:
We have several databases. All of them are mirrored. Until now, we never had issues with this.

One database had some problems, and now when we try to enable mirror again, it just stops. Message in the SQL Log is Database mirroring has been terminated for database 'databasename'.

What we do:

  - Create full backup of the primary database
  - Create backup of the transaction log, in the same backup file
  - Restore that backup on the secondary location, with the norecovery option
  - Set partner on mirror, then primary.

The primary gets status 'Principal, synchronizing' for a moment, but then it just stops and terminates the mirroring. Placing the mirror back in restoring state and the principal just as a database with no mirror.

What can we do to investigate what is going wrong here? I think the endpoints are fine, because the other databases have no problem at all.

Thanks for any advices.


texts: We are currently trying to implement a two node AG where node 1 has two AG's. The first AG is primary to node 1 and secondary to node 2, the second AG is primary to node 2 and secondary to node 1. I was told that this is the set up we will be using because we wanted to make use of all resources, for example node 1 will be responsible for 3 databases and node 2 will have 2 databases, I guess this is a pseudo active/active set up. I personally don't know the use case for this set up, but it does seem a little bit over the top. Any pro's, con's or resource help would be helpful.


texts:
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, etc.

Now the doubt I have is what are the scenarios where I should use the shrink option in the database.

I have never seen a scenario it was stated as the useful one. Is shrinking always evil?


texts:
I run PostgreSQL-9.2.4

Is it possible to call 2 functions from a trigger?

Let's say I have two functions for two different tables to be executed if following trigger fires:

Trigger:

CREATE TRIGGER start ON system_status FOR EACH ROW
WHEN ((new.event = start_task))
EXECUTE PROCEDURE ...()

Function 1:
(When task starts => remove any previously assigned next task for this system)

CREATE FUNCTION void_next_task() RETURNS trigger AS $$

BEGIN
  DELETE FROM tasks_status ts
  WHERE ts.system = NEW.system
  AND ts.event = 'next_task';
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

Function 2:
(If inserted combination of task and system already presented in the table => mark any earlier records with this combination as deleted)

CREATE FUNCTION void_dup_task() RETURNS trigger AS $$

BEGIN
  UPDATE system_status ss
  SET deleted = 'TRUE'
  WHERE ss.system = NEW.system
  AND ss.task = NEW.task
  AND ss.deleted IS FALSE;
  RETURN NEW;
  END;

$$

LANGUAGE plpgsql

So I ended up with following ways to resolve it:

To have a trigger which calls two functions;
To have a function which performs update on one table and delete on another one;
To have two exactly same triggers and two different functions;

Before I will go ahead and implement solution 3 could you advice me if solution 1 or 2 are possible at all?


texts: Indexes with High Lock waits - how to fix

texts:
I've been running dbo.sp_BlitzIndex and have 4 somewhat similar indexes on the main table in my database.  Each one has a high number of waits and escalation attempts.  I'm not sure where these are coming from. I don't have missing indexes. I don't see searches on this table taking a long time so not sure if these are coming from selects or inserts.  Need some pointers on where to look next.

CORE.tblCase.idx_tblCase_bClosed_nUserID_PrimaryAgent (2):
Row lock waits: 1; total duration: 0 seconds; avg duration: 0 seconds;
Page lock waits: 85; total duration: 92 minutes; avg duration: 1 minutes;
Lock escalation attempts: 100,965;
Actual escalations: 1. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nActionID_Last_nWorkflowID (27):
Row lock waits: 3; total duration: 26 seconds; avg duration: 8 seconds;
Page lock waits: 15; total duration: 10 minutes; avg duration: 42 seconds;
Lock escalation attempts: 31,908;
Actual Escalations: 0. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nWorkflowID_nActionID_Last (28):
Page lock waits: 112; total duration: 85 minutes; avg duration: 46 seconds;
Lock escalation attempts: 31,748;
Actual Escalations: 0. NC indexes on table: 1
CORE.tblCase.idx_tblCase_bClosed_nCaseID_INC (35):
Row lock waits: 2; total duration: 13 minutes; avg duration: 6 minutes;
Page lock waits: 307; total duration: 399 minutes; avg duration: 1 minutes;
Lock escalation attempts: 43,090;
Actual Escalations: 0. NC indexes on table: 1


texts: How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?

texts:
This is a beginner question:

A user can belong to many groups, and a group can contain many users.

Let's say the two tables look something like this:

user_id
user_name
user_email

group_id
group_title
group_description

Where and how do I store all of the users which belong to a group and all the groups a user belongs to?

I can't imagine the groups table having a users field which contains comma separated user IDs being an acceptable solution. I think I get the basic concept of relational databases, but I'm not sure of the implementation. I get the one-to-one relationship, but I'm struggling with one-to-many. Is what I'm dealing with in this example two separate one-to-many relationships?


texts:
I have a table like this:

CREATE TABLE Updates
(
    UpdateId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ObjectId INT NOT NULL
)

Essentially tracking updates to objects with an increasing ID.

The consumer of this table will select a chunk of 100 distinct object IDs, ordered by UpdateId and starting from a specific UpdateId. Essentially, keeping track of where it left off and then querying for any updates.

I've found this to be an interesting optimization problem because I've only been able to generate a maximally optimal query plan by writing queries that happen to do what I want due to indexes, but do not guarantee what I want:

SELECT DISTINCT TOP 100 ObjectId
FROM Updates
WHERE UpdateId > @fromUpdateId

Where @fromUpdateId is a stored procedure parameter.

With a plan of:

SELECT <- TOP <- Hash match (flow distinct, 100 rows touched) <- Index seek

Due to the seek on the UpdateId index being used, the results are already nice and ordered from lowest to highest update ID like I want. And this generates a flow distinct plan, which is what I want. But the ordering obviously isn't guaranteed behavior, so I don't want to use it.

This trick also results in the same query plan (though with a redundant TOP):

WITH ids AS
(
    SELECT ObjectId
    FROM Updates
    WHERE UpdateId > @fromUpdateId
    ORDER BY UpdateId OFFSET 0 ROWS
)
SELECT DISTINCT TOP 100 ObjectId FROM ids

Though, I'm not sure (and suspect not) if this truly guarantees ordering.

One query I hoped SQL Server would be smart enough to simplify was this, but it ends up generating a very bad query plan:

SELECT TOP 100 ObjectId
FROM Updates
WHERE UpdateId > @fromUpdateId
GROUP BY ObjectId
ORDER BY MIN(UpdateId)

With a plan of:

SELECT <- Top N Sort <- Hash Match aggregate (50,000+ rows touched) <- Index Seek

I'm trying to find a way to generate an optimal plan with an index seek on UpdateId and a flow distinct to remove duplicate ObjectIds. Any ideas?

Sample data if you want it. Objects will rarely have more than one update, and should almost never have more than one within a set of 100 rows, which is why I'm after a flow distinct, unless there's something better I don't know of? However,  there is no guarantee that a single ObjectId won't have more than 100 rows in the table. The table has over 1,000,000 rows and is expected to grow rapidly.

Assume the user of this has another way to find the appropriate next @fromUpdateId. No need to return it in this query.


texts: Inserting into a table a row represented by JSON

texts:
I've been laboring away trying to create a procedure which can data JSONB (or JSON, doesn't really matter), a table, and convert the JSON to a record and insert that record into the table. This is (roughly) the closest I've come to getting a functional prototype:

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, table_type anyelement) RETURNS VOID
    SECURITY INVOKER
    LANGUAGE plpgsql
AS $$
    DECLARE
        record RECORD;
        record_type RECORD;
    BEGIN
        EXECUTE format('SELECT x.* FROM (SELECT a.* FROM CAST(NULL as %s) a) x', pg_typeof(table_type))
            INTO record_type;
        record := jsonb_populate_record(record_type, data);
        INSERT INTO users SELECT (record).* RETURNING id;
    END;
$$;

So let's say that I do the following:

SELECT fn_alter_entity('{"a": "b"}'::JSONB, users);

I receive the following error:

ERROR: record type has not been registered
SQL state: 42809
Context: SQL statement "INSERT INTO users SELECT (record).* RETURNING id"
PL/pgSQL function fn_alter_entity(jsonb,anyelement) line 9 at SQL statement

Now, obviously the reason I'm receiving that error is that record_type is not actually a real record type. Now, normally we would just do something like jsonb_populate_record(data, NULL::users) or jsonb_populate_record(data, NULL::events) or the like. But what if I want that record type to be dynamic? Is there any possibility? I feel like I'm on the verge of nailing this down, but just can't quite get over the hump. All I want to do is be able to transform JSON into a record.


texts: Is there any need to persist a (precise) computed search column that I've indexed?

texts:
I have a number of columns that store data that may contain some extra characters for formatting/grouping. Think white space and dashes in data like phone numbers and license plate numbers.

When querying, we can't be sure if and where these dashes are present, so we need to strip them from the column. Doing so while querying isn't great for performance, so I want to create a computed column for the stripped column and put an index on that.

-- trimming the length down to 50 characters
-- which is the length of the original field
ALTER TABLE foo
ADD search_license_plate AS (LEFT(REPLACE(license_plate, '-', ''), 50));

CREATE INDEX ix_foo_search_license_plate ON foo(search_license_plate);

These computed columns are always precise, so they don't need to be persisted because of a lack of precision.

The data will typically only be entered once and will rarely be altered. Never in high volumes, so I don't care much about any performance impact of persisting the column.
Also, the computed column will never be returned from a query; it will only be used in conditions on the query, typically

SELECT
   foo.id,
   foo.license_plate
FROM foo
WHERE foo.search_license_plate LIKE '%bar%';

... although I can't guarantee that there will be no SELECT * done on these tables.

Is there any need to persist these columns?


texts: SYS.DBMS_INTERNAL_LOGSTDBY causes error on dump import

texts:
I'm having problems importing a dump into an Oracle XE Database.
The import worked for several other people, so I can assume that the dump is not corrupt.

In ran the following command:

  C:\Users\franzm>impdp abcac@xe DIRECTORY=exp_dir
  DUMPFILE=expdp_abc_20140122010001.dmp schemas=abc LOGFILE=log.log

exp_dir was set before to the folder with the dump file

I'm getting the following error:

  ORA-31626: job does not exist ORA-04063: package body
  "SYS.DBMS_INTERNAL_LOGSTDBY" has errors ORA-06508: PL/SQL: could not
  find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
  ORA-06512: at "SYS.KUPV$FT", line 991 ORA-04063: package body
  "SYS.DBMS_LOGREP_UTIL" has errors ORA-06508: PL/SQL: could not find
  program unit being called: "SYS.DBMS_LOGREP_UTIL"

I searched the net and found some topics in the oracle board, but those didn't helped me.
Anyone has a hint?


texts: Filesystem copying inactive DBs on active MySQL server

texts:
My question is related to this question and answer:
Copying datadir directory enough for backing up a database with InnoDB tables?

I want to use a filesystem copy on a whole DB without shutting down the whole server. The documentation references using FLUSH TABLES ... FOR EXPORT and then UNLOCK TABLES to be able to safely do this.

https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-for-export-with-list

But what happens if I use those queries to target a whole DB like FLUSH TABLES example_db.* FOR EXPORT then filesystem copy the whole example_db folder then run UNLOCK TABLES example_db.*

Is that safe? Or just possibly safe because new tables in the DB could theoretically be created after the existing tables are locked which would then potentially not be flushed when the filesystem directory is copied? Is there something extra I have to do here to lock the whole database?


texts: Splitting read/write heavy huge table for performance?

texts:
We have a gigantic table of products that have many prices that are updated by different microservices (say 20 different sources updating 80 columns on the same rows). We also have a website with say 40-100 concurrent users, and the pricing data is refreshed every 5 seconds and generally not cached as it is time sensitive.

Every single source of price is a new column and as you can imagine there are currently occasional deadlocks.

Would splitting them into multiple tables work well enough (e.g. split meta data and prices, or split meta data and prices that each microservice updates)? Would we get even worse performance with all the joins we have to do while reading?

Are there other options other than splitting tables? We only have indexes on the lookup fields (generally an ID and one other piece such as quantity for bundles). Or should we just suck it up and go into a larger instance?


texts: Subtract column value from date in days or months

texts:
I want to subtract the value in column lag_days from current_date.
Usually I would do current_date - interval '1 day'
I'm getting syntax error on the following example to subtract by days:
dkarchive=> select current_date - interval lag_days day from example_tbl;
ERROR:  syntax error at or near "day"
LINE 1: select current_date - interval lag_days day from example_tb...


texts: Postgres binary replication: Standby takes a long time to start

texts:
I am using the Postgres binary replication tutorial, and have rsynced all the data files from my master to standby

rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/

Afterwards, I'm starting my standby first, and see this in the logs:
FATAL:  could not connect to the primary server: could not connect to server: Connection refused

According to the tutorial, this should be fine as I haven't started the master yet.

However when I try to connect to the standby, I get this error msg even after waiting for a few mins:

psql: FATAL:  the database system is starting up

My database is 600GB big, is it normal for the standby database to start after I'm done initially copying all the data files over? Or is something seriously wrong? How long does it normally take?


texts: Delete all highscores from a certain country

texts:
I have two tables:

tbl_players
tbl_highscores

I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by highscore. I can gather the information with JOIN like this:

SELECT scorePoints, playerAlias, playerEmail, playerCountry
    FROM tbl_highscores JOIN tbl_players ON tbl_highscores.playerId = tbl_players.playerId
    WHERE playerCountry = 'lt'

But how do I delete all highscores that are saved by a user from a certain language?


texts: Why Does the Transaction Log Keep Growing or Run Out of Space?

texts:
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:

In SQL Server -

What are some reasons the transaction log grows so large?
Why is my log file so big?
What are some ways to prevent this problem from occurring?
What do I do when I get myself on track with the underlying cause and want to put
my transaction log file to a healthy size?


texts:
I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C".  On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both servers:

TraceFlag   Status  Global  Session
1204        1       1      0
1222        1       1      0
3605        1       1      0

On A the trace flags work as expected, when a deadlock occurs, we get both the 1204 and 1222 deadlock reports in the error logs.  However, on C, only the 1204 report shows up, we never get the 1222 report.

For the life of me I cannot see any reason for this difference.  I have both googled this extensively, and read (and re-read) the MS doc on these trace flags, and I cannot find any reports of behavior like this, nor any hints as to what might cause it.  The only thing that comes close is the occasional claim that neither trace flag was working, but these all turned out to be cases were they had typos in the enabling commands. I know that this is not the case here because I have used DBCC TRACESTATUS to confirm it.

So any insights into what might be causing only trace flag 1222 to not work and/or how to fix it would be greatly appreciated.

Well, here's an interesting development.  Whenever I generate a deadlock myself (using this code: https://stackoverflow.com/questions/7813321/how-to-deliberately-cause-a-deadlock), then I get both trace reports in the error logs.  It's only the "natural" deadlocks that occur every couple of days from the applications that seem to only trigger one of the deadlock reports.  Not sure if this helps, is there any reason to believe that trace 1222 would not report on all of the same deadlock conditions that 1204 would?


texts: cent os 7 alwayson primary is in resolving state and not going to secondary

texts:
I have configured AlwaysOn in external mode between two CentOS 7 VMs.
When manual failover happens, primary goes to resolving state. It never turns to secondary and secondary is always in secondary state

-- All nodes (linux01 and linux02)

sudo pcs property set stonith-enabled=false
sudo pcs property set no-quorum-policy=ignore
sudo pcs property set cluster-recheck-interval=2min

-- resource configuration

sudo pcs resource create sqlag ocf:mssql:ag ag_name=haag master notify=true meta failure-timeout=30s
sudo pcs resource create vip_ip ocf:heartbeat:IPaddr2 ip=192.168.52.62 cidr_netmask=24 op monitor interval=30s

sudo pcs constraint colocation add vip_ip  sqlag-master INFINITY with-rsc-role=Master

sudo pcs constraint order promote sqlag-master then start vip_ip

sudo pcs cluster start --all

-- linux01 is primary move to linux02

sudo pcs resource move sqlag-master linux02 --master

linux01 in resolving; linux02 in secondary. Hangs; no response in SSMS.


texts: Postgresql function taking long time on newly restored database

texts:
I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute.

Now I have restored a new PostgreSQL 9.0 dump using following command:

pg_restore -hx.x.x.x -p5432 -d xxx -Ux -Fc -v </path/pgbackup.backup

but on this newly restored database it takes 2 minutes to execute same function.

Please give me possible way to sort out this problem.


texts: MySQL Insert from Select and schema alter Busy Production without downtime

texts:
Strategy for migration of Huge MySQL (Percona 5.7) Table (InnoDB) in production.
The aim is to have minimum downtime (offline schema change would take a week or more...)

old has a 100 plus columns
we are planning to delete 90 columns and move (adjusted) data into new

THE PLAN

1) create new

2) transfer data from old to new

3) Alter old

ad 2)

Old Table (c1 .. c150):

Id  | c1    |c2     |c3 |
-------------------------
1   |A      |null   |F  |
2   |B      |Z      |H  |

New table:

Id  | OldId |colName    |value  |
---------------------------------
1   |1      |c1         |A      |
2   |1      |c3         |F      |
3   |2      |c1         |B      |
4   |2      |c2         |Z      |
5   |2      |c3         |H      |

to transfer data

INSERT INTO new(OldId,colName,value) SELECT Id, 'c1', c1 FROM old WHERE c1 IS NOT NULL;
INSERT INTO new(OldId,colName,value) SELECT Id, 'c2', c2 FROM old WHERE c2 IS NOT NULL;
.
.
.
INSERT INTO new(OldId,colName,value) SELECT Id, 'c150', c150 FROM old WHERE c150 IS NOT NULL;

How to execute statements similar to the ones listed above, with least impact on the production system (I know that Insert with Select will Lock both tables...), and make sure that new data inserted into old is transferred to new before switchover?

ad 3) for schema change I am planning to use
GH-OST or pt-online-schema-change
most likely GH-OST as it does not create triggers, and can be easily paused/recovered.


texts:
I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT.

Now, when entering data into one such row, I get

  mysql_error() = Row size too large (> 8126). Changing some columns to
  TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may
  help. In current row format, BLOB prefix of 768 bytes is stored
  inline.

I am thinking of changing the table type to MyISAM.
Its on a shared server, but can setup a private MySQL instance.

From here :

  InnoDB implements transactions, foreign keys and relationship
  constraints, MyISAM does not.

I can still use JOINS etc on MyISAM, right ? I'm not implementing any other complex stuff on these tables.


texts: How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

texts:
I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it.

I need to create a table where the elements were previously selected from another table and they are all unique.

Here's my try:

CREATE TABLE new_name AS (

   SELECT DISTINCT(table.field1, table.field2), table.field1, table.field2,
   FROM ...
   WHERE ...

);

If I run this statement, PostgreSQL says:

  ERROR: column "row" has pseudo-type record

How could I solve this problem?


texts:
A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however:

  'CONCAT' is not a recognized built-in function name.

I understand that CONCAT() is a new built-in function introduced in SQL Server 2012, which is all fine and well, however I have been asked to roll back my change to make this 2008R2 compatible under the guise of "the user executing the query may not have Transact-SQL permissions to execute." So I am just proving my point that the client most likely has a different version of SQL Server installed in DEV than they do in PROD.

I can't find any information about specifically denying SELECT/EXECUTE permissions for built-in scalar valued functions, but is it possible, and if so does the user still receive the same error text?


texts: Group and order output into a single row

texts:
I want to display each person's contact info and their commissions in a single row as opposed to multiple rows.

Commission Table

ID          COMMISSION  DATE
N12545233    774         4/1/15
N12545233    361         5/1/14
N12545233    685         6/1/15
N14521746   3384        10/1/14
N14521746   1188        11/1/14
N14521746   1256        12/1/14
N15981070   1188         1/1/15
N15981070    616         2/1/15
N15981070    936         3/1/15

Sales_Staff Table

ID          LASTNAME    FIRSTNAME   STREET          CITY        STATE   ZIP
N12545233   Jones       Mark        123 Main St     Brooklyn    NY      11377
N14521746   Greene      Phil        345 Broadway    Queens      NY      11237
N15981070   Stahl       Barbara     567 Lexington   New York    NY      10018

Desired Output

ID         LASTNAME FIRSTNAME   STREET        CITY      STATE   ZIP     COMMISSION  DATE      COMMISSION    DATE           COMMISSION   DATE
N12545233  Jones    Mark        123 Main St   Brooklyn  NY      11377   774          4/1/15    361           5/1/14          685         6/1/15
N14521746  Greene   Phil        345 Broadway  Queens    NY      11237   3384        10/1/14   1188          11/1/14         1256        12/1/14
N15981070  Stahl    Barbara     567 Lexington New York  NY      10018   1188         1/1/15    616           2/1/15          936         3/1/15


texts:
I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the print statement errors in the error log.

However, I'm also getting sql errors for 2 other users that say "Severity 020 occurred, Logon failed for login 'randomuser' due to trigger execution." which don't come from that server/application combination and the received error message isn't what I've used in the trigger.

Any ideas what could be causing this?

I know that this isn't ideal and there are ways to bypass the checks but the use case is to prevent ssrs data sources coming from a dmz server being created that aren't using a predetermined restricted user account. The only ones that can create the data sources are internal employees with elevated permissions.

CREATE TRIGGER [LogonTrigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    BEGIN TRY

        DECLARE @host NVARCHAR(128) = ISNULL(HOST_NAME(), ''),
                @user NVARCHAR(128) = ISNULL(ORIGINAL_LOGIN(), ''),
                @app NVARCHAR(128) = ISNULL(APP_NAME(), '')

        IF (   @host = 'myhost'
               AND @user <> 'acceptableuser')
            BEGIN
                PRINT 'LogonTrigger Deny User:' + @user + ' Host:'
                      + @host + ' App: ' + @app
                ROLLBACK;
            END
    END TRY
    BEGIN CATCH
        PRINT 'LogonTrigger User:' + @user + ' ErrorNumber: '
              + CAST(ERROR_NUMBER() AS VARCHAR) + ' ErrorMessage: '
              + ERROR_MESSAGE()

    END CATCH
END


texts:
By mistake one of my colleague in the office has restored .mdf and .ldf files of another database to production with existing database name. So existing database was overwritten with new backup.

Is there any chance to recover that db back?

xyz database - .mdf & .ldf - restored as abc database (but already existing in production)


texts:
I've got a big table (~9M rows) and want to group the rows on a field containing the year. So far that's pretty easy:

// greatly simplified:
SELECT count(*), year FROM dataset GROUP BY year ORDER BY 2;

We defined some irregular time periods spanning multiple years:

<1945, 1946-1964, 1965-1974, 1975-1991, 1992-2005 and >2005

I've got no clue on how to group these results in the group by clause. I could make subquery's for every time period.

SELECT
  ( SELECT count(*) FROM dataset WHERE year <= 1945 AND ...... ) AS pre1945,
  ( ....) AS period2,
  ....
FROM dataset

But that feels not right and I'm wondering if it was possible to let Postgresql do it. Especially because the query is a strong simplification of the real query: it has multiple conditions, amongst them a ST_within clause spanning four tables. So choosing the subquery-approach results in a bloated query.

Is there a better way to create this result?


texts: Release unused space from SQL Server database

texts:
We have deleted a large part of the data from a two-digit terabyte sized database, and would now like to release some of the space back to the operating system. The database is currently about 80 % unused space. It will never reach its former size again (the load has been transferred elsewhere).

We have tried running SHRINKDATABASE on a copy of the database on a separate server (non-production). Running for 2 days (on our hardware) it was able to release the space.

I'm assuming it will take much longer on the database in production, as this is heavily used (mostly for querying now though, less so inserts).

Only hours of downtime (at a time) is acceptable on the production system. Some performance degrade over extended periods would be acceptable too.

Any ideas on how to release this amount of unused space, without having excessive downtime, or introduce severe performance degrade during the process?


texts: Slow select Queries on Log shipping standby server

texts:
We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB.

To avoid users from reporting a LS set up was done for above database in different DC where the LS restore job runs once nightly.

on Primay- update stats and update stats AYNC are both set to True and hence reflected the same on secondary. I am not sure why someone kept both enabled and is that how it should be?

Now on secondary, i see lots of blocking occurring when a SELECT query runs from users results in select statman statement for auto update stats for that read only database.

  I am not able to get why there would be one when auto update async is
  enabled?

  Also, will the update stats done on primary (once a week) be restored during
  LS restore? if not, whats the better way to run update stats on
  secondary database involved in LS standby mode, may be to run daily for better select performance out there?

Please advise


texts:
So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single user mode, but that gave us some trouble, sometimes we would lose the connection in the middle of the update.
So we setup a test environment to replicate the behavior.
We are using Microsoft SQL server 2017, with the AdventureWorks2017 database to replicate the issue.
On the database we have turned off Auto close and Auto Update Statistics Asynchronously
If we then have two connections to the server, using the master database.
Tell one of them to run this script
USE MASTER
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('AdventureWorks2017')
EXEC(@kill);

USE AdventureWorks2017
GO

DECLARE @cnt INT = 0;
WHILE @cnt < 10000
BEGIN
  SELECT TOP 1000 * from Person.Person;
   SET @cnt = @cnt + 1;
end;

And then on the other repeatedly run
SELECT TOP 1000 * FROM AdventureWorks2017.Person.Person;
GO;

At some point the first script stops working, and complains with an error

Database 'AdventureWorks2017' is already open and can only have one user at a time.

But to our understanding, this should not happen cause it still has the connection.
Note this doesn't happen all the time. But it's still fairly consistent.
Is there anything that we are missing, or can this be an issue with the SQL server?


texts: How can I improve this query in PostgreSQL?

texts:
I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as:

SELECT COUNT(*) AS count_all, listings.id AS listings_id
FROM "listings" LEFT OUTER JOIN houses ON houses.id = listings.house_id
WHERE "listings"."subscription_id" = 123
  AND (exists (select 'x' FROM apps
               WHERE apps.listing_id = listings.id
                 AND apps.subscription_id = listings.subscription_id LIMIT 1))
                 AND "listings"."listing_status" = 'Active'
GROUP BY listings.id

How can I write this query in PostgreSQL where it will return in a reasonable amount of time?

My explain plan is the following. I admit I really don't know how to read it very well. But it looks bad:

HashAggregate  (cost=2579302.75..2579319.31 rows=1656 width=8)
  ->  Bitmap Heap Scan on listings  (cost=304056.70..2579294.47 rows=1656 width=8)
        Recheck Cond: ((subscription_id = 162) AND (listing_status = 'Active'::citext))
        Filter: (SubPlan 1)
        ->  BitmapAnd  (cost=304056.70..304056.70 rows=3313 width=0)
              ->  Bitmap Index Scan on listings_subscription_item_idx  (cost=0.00..570.84 rows=5637 width=0)
                    Index Cond: (subscription_id = 162)
              ->  Bitmap Index Scan on index_listings_on_listing_status  (cost=0.00..303484.78 rows=3936030 width=0)
                    Index Cond: (listing_status = 'Active'::citext)
        SubPlan 1
          ->  Limit  (cost=0.57..683.01 rows=1 width=0)
                ->  Index Scan using apps_listing_idx on apps  (cost=0.57..42312.00 rows=62 width=0)
                      Index Cond: (listing_id = listings.id)
                      Filter: (subscription_id = listings.subscription_id)

This query works fantastically on a laptop with the same database, but dies in production and takes over a minute to return. Does anything jump out at anybody for things I could rapidly improve?

The database has autovacuum turned on.


texts: Migrating from MariaDB to Mysql - Duplicate Constraint Name

texts:
I am attempting to migrate from MariaDB to MySQL by doing a mysqldump from MariaDB and then restoring it to MySQL (mysql:latest docker container).

I am getting the following error when importing into MySQL:

ERROR 3822 (HY000) at line 172: Duplicate check constraint name 'CONSTRAINT_1'.

If I look at the mysqldump file I can see why this is happening. All boolean columns in my database have a constraints that looks something like:

CONSTRAINT `CONSTRAINT_1` CHECK (`bool_col_1` in (0,1))
CONSTRAINT `CONSTRAINT_2` CHECK (`bool_col_2` in (0,1))
CONSTRAINT `CONSTRAINT_3` CHECK (`bool_col_3` in (0,1))

These constraints were not explicitly created by me but implicitly by Flask-SQLAlchemy (I think).

Notice how the constraint names are incremented starting with CONSTRAINT_1. Well the problem is that each table starts incrementing its constraint names starting with CONSTRAINT_1. Thus the error I am seeing gets thrown when trying to create the second table. According to the MySQL docs, duplicate constraint names are not allowed. Apparently MariaDB allows them.

Is there a way to rename these constraints systematically or an alternative way to migrate the data?

The quantity of tables prohibits manually changing the name of each constraint.

Note: This fiddle tests duplicate constraint names and executes without error on MySQL. However if I run the same commands on a fresh MySQL container, it fails with the duplicate constraint.


texts: SQL Server : KEY lock prevents FK on nearby row, but not SELECT

texts:
I have the following table structure (simplified, pseudocode):

TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)
TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id)
    ... -- no other user-related fields
)

I have the following situation in application code:

-- Given a User with Id 5 that already exists
BeginTransaction
1. INSERT INTO dbo.Users (...) VALUES (...) -- Creates user with Id 6
    NotInTransaction.Start
    2. INSERT dbo.Transfers (CreatedByUserId, ...)  VALUES (5, ...)
    NotInTransaction.End
...
CommitTransaction

In this specific case (when existing user and newly created user have nearby Ids), I get a timeout from step 2.

Now the interesting part is that I paused the application on step 2, and tried the following things outside the (paused) transaction:

SELECT * FROM dbo.Users WHERE UserId = @UserId
works (not locked) for all values of @UserId except 6, which makes total sense
INSERT dbo.Transfers (CreatedByUserId, ...)  VALUES (@UserId, ...)
works for 1,2,3,4, locked for 6 and 5

I did a quick look on sys.dm_tran_locks (which I am not a master of) with some hobt joins, and when INSERT is locked, it shows

ObjectName | resource_type | resource_description | resource_associated_entity_id | request_mode | request_status
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | X            | GRANT
Users      | KEY           | (b9b173bbe8d5)       | 72057594047299584             | S            | WAIT

Now, the question is: why can I select user 5 from outside the transaction, but not insert into a table that references it through FK?


texts:
I am deploying pl/sql code using several sql files that are called with @@file.
If a package got a compilation error the script continues to the end.

Is there a way to stop on every compilation error?

I tried WHENEVER SQLERROR EXIT SQL.SQLCODE but the script still continues.


texts: How do I check three conditions on a select with one query?

texts:
I have a table containing email text for welcoming new users. There is a default email, one that may be client_id specific and one that may be user_id specific. I can do three queries to find user, then if not found. client, then if not found, default. But I'd prefer to have one query that selects the one that follows that priority list of user, client and default.

The other option is to do one query that may return up to three rows and then just select the appropriate row. But that is messy...

SELECT * FROM emails
WHERE primary_key=?
  AND ((user_id=?)
       OR (user_id IS NULL AND client_id=?)
       OR (user_id IS NULL AND client_id IS NULL))

This may return up to three rows. Is there a way to just return one row?


texts: Generate Schema and Data to SQL script and Run it on another DB programmatically

texts:
I can manually generate a schema with data via
Table Name > Tasks > Generate Scripts...

But it is possible to create a schema+data single sql file backup of a database and run it on another database programmatically ?

Maybe some SQL Script which will be scheduled by Job or by another approach, i dont know.

Thank you


texts:
I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving.   On this server, there are three databases: databaseA, databaseB and databaseC.  Someone deletes a table at 12 pm on databaseB and I want to restore databaseB to just before the time at which the table was deleted.  The time is now 3 pm.

How can I just restore databaseB up to 12 pm without losing three hours worth of data in the other two databases?

postgresql.conf

wal_level = archive
archive_mode = on
archive_command = 'test ! -f /opt/pgsql/logs/%f && cp %p /opt/pgsql/logs/%f'
max_wal_senders = 100

recovery.conf

restore_command = 'cp /opt/pgsql/logs/%f %p'
recovery_target_time = '2012-06-29 11:59:59 CEST'

I ran my base backup at 9 am

pg_basebackup -h 127.0.0.1 -D /opt/pgsql/backup

The PostgreSQL service was shutdown at 3 pm.


texts: How can I obtain a list of our applications' SQL statements that do not make use of indexes?

texts:
We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement when they are run thousands of times.
In some cases, we found statements doing table scans where they should have done index seeks if the datatype parameter had been correct.
We take a profile of some millions of rows over 10 minutes and save as a table for easier analysis.
Is there a way to search for statements that aren't using indexes. Is searching the showplan XML the best method? If so, what should we search for?


texts: Mysql_upgrade fails when running from Xampp

texts:
Recently I had to reinstall Xampp and I added some older databases to MySQL after which all seemed to run just fine. Now, a few months later, I got an error while using IsNumeric in a query, and was told by the MySQL error message to run mysql_upgrade -u root -p, so I did.
Unfortunately, this doesn't seem to work, as I run into the issue that the upgrade tells me that some tables already exist (innodb_table_stats, innodb_index_stats, transaction_registry and gtid_slave_pos). After some Googling I found several posts telling me just to delete those files and run the upgrade again, but this fails with the exact same message.
Any idea how I can get this upgrade to finish, or what is going on (wrong) here? Here is the actual output from the upgrade:
> C:\xampp\mysql\bin>mysql_upgrade --force -u root -p
Enter password: ****
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user_info                                    OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1050 (42S01) at line 682: Table '`mysql`.`innodb_table_stats`' already exists
ERROR 1050 (42S01) at line 686: Table '`mysql`.`innodb_index_stats`' already exists
ERROR 1050 (42S01) at line 690: Table '`mysql`.`transaction_registry`' already exists
ERROR 1050 (42S01) at line 762: Table '`mysql`.`gtid_slave_pos`' already exists
FATAL ERROR: Upgrade failed

Thanks for any tips!
Edit: I found a message on another forum telling me to log in to the database to repair all these tables (using "command repair table x" as root), but it tells me the tables do not exist. The same goes for an attempt to drop these tables, again I get the message that they do not exist.


texts: Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave?

texts:
According to the docs, "MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.6 to a slave running MySQL 5.7". In theory, this also means that a 5.6 master can replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave. I'm assuming that the intermediate 5.7 server will write to its bin-logs in the 5.7 format, which will allow its 8.0 slave to replicate from it. Is that the case?

I'm doing this because I have a 5.6 instance that I want to upgrade to the latest mysql 8 with minimal downtime. I'd rather not upgrade incrementally, in order to only create a single short swapping downtime (the time it takes to turn of all writes on the master and promote the slave, and route traffic to it.)


texts: Accessing computed column in a nested query

texts:
I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/  by Mike Hillyer.

In the article Hillyer uses the following SQL to return the depth of each node:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

I would like to return a list of all nodes that have a depth of, say, 2.  My attempts have been around using a sub-query but I have been unable so far to figure out where to add the check for "depth = 2".  This is what I have so far:

select * from nested_category nc
  where exists
      (select node.category_id, (COUNT(parent.name) - 1) AS depth
       from
          nested_category AS node,
          nested_category AS parent
        where
          node.lft between parent.lft and parent.rgt AND
          nc.category_id = category_id
        group by
          node.category_id, node.lft
       ) as ???

How can this be done?


texts:
I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

For example (pseudocode - doesn't work, wrapped for readability):

SELECT ARRAY['2017-01-01'::DATE, '2017-02-03'::DATE] &&
    daterange('2017-01-01', '2017-01-31', '[]');

What this example is trying to show (although it doesn't work), is whether there's an overlap between values between my array of dates and the daterange.

Ideally I could do it with sets, and work out if there's a union between the two, but it doesn't seem possible to cast daterange objects to anything.

I did manage to get this working, but I can't work out how to subsequently reduce the rows:

SELECT daterange('2017-01-01', '2017-01-31', '[]') @>
    unnest(ARRAY ['2017-01-01' :: DATE, '2016-07-30' :: DATE]);

This returns me multiple rows:

?column?
----------
 t
 f
(2 rows)

Does anyone have any pointers for this? Any help is appreciated.


texts:
I have a few tables (6 to be exact) and I want to make a trigger that will delete a row from a table based on a value from the main table.

To elaborate,

Let's say I have a main table called "main" and there is a column there called "table_short" and that row contains either "S", "P", "A", "Z" or "K".
"S" points to the "Stanovi" table, "K" points to the "Kuce" table and so on, meaning the data in the "table_short" in the main table is associated with another table. In the main table there is also a "id_t" column, that points to a specific row inside the table associated in the "table_short" column.

Now, I want to make a trigger statement that would, upon deleting a row in the main table, delete the row number in the table associated in "table_short", with the id specified in "id_t".

How could I do this?
Hopefully someone understood what I mean.

I tried this but it gives me an error when trying to make a trigger.

BEGIN
 CASE old.sifra
  WHEN "S" THEN SET @table="stanovi";
  WHEN "K" THEN SET @table="kuce";
  WHEN "A" THEN SET @table="apratmani";
  WHEN "P" THEN SET @table="poslovne_zgrade";
  WHEN "Z" THEN SET @table="zemljista";
 END CASE;
 DELETE FROM @table WHERE id = old.id_t;
END


texts: Estimated row size and estimated data size is incorrect

texts:
I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns

As a simple example, if I dump the table into a temp table, the actual plan shows a whopping 42GB estimated and actual data size. See image below.

I suspect this is due to the two varchar(4000) columns which is causing the estimated row size to be larger than they are. However, I really don't think 42GB are being transfered - not sure how that would be possible.. I don't know if I'm seeing performance issues because of this but the query plan looks scary because of these wrong estimates. Why is the query plan so wrong on the estimated and actual table scans?


texts:
I'm trying to import a dump file with the command below:

impdp system/password dumpfile=mw0sd1.dmp keep_master=Y

But it returns following error:

ORA-39002: invalid operation
ORA-39059: dump file set is incomplete
ORA-39246: cannot locate master table within provided dump files

I've been checked logfile and didn't found any error for master table.

Export: Release 11.2.0.3.0 - Production on Fri Mar 2 01:31:27 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_02":  userid="/******** AS SYSDBA" directory=BKPSQL1 dumpfile=BKP_MW0SD1_PONTUAL_2392329.dmp LOGFILE=BKP_MW0SD1_PONTUAL_2392329.log SCHEMAS=MW0SD1

Master Table:

Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
      /home/oracle/orabkp/BKPORA/ORAINST1/SQL1/BKP_MW0SD1_PONTUAL_2392329.dmp
    Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 03:46:24

I'll appreciate so much If anyone could help me to solve this question ASAP. Already exported this database 2 times for make sure that is not an export problem.

Thanks.


texts:
How to tell whether mysql was built with libedit or readline?

I'm trying to configure the mysql client tool to use Ctrl-R for searching command history but not sure which input library my installation is using.


texts: SQL Server: Database integration and synchronization

texts:
I have n SQL Server databases with the same data schema.

On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n databases.

A possible solution is to apply the ETL operations (Extract, transform and load) using SSIS (SQL Server Integration Services) and BIDS (Business Intelligence Development Studio).

With the application of the ETL operations I obtain a database (warehouse) that can be used from my web application. See the picture below for a basic sketch.

The problem is that the web application not only access the warehouse for read, but also for write and update information.

If an entity is updating into the warehouse, the same update need to be applied into the original source.

NB. the information into the source and warehouse can be updating frequently.

Can replication be a good approach to sync the update into warehouse to the right source or there is another tools that I can use?

Maybe there is another strategy instead of ETL and warehouse that I can consider?


texts: 70-432 : SQL Server 2008 Implementation and Maintenance

texts: Has anybody taken this certification exam?  If so, how many years of experience do you have as a SQL Server DBA and how hard did you find the exam?


texts: Compress backup taken from two different environments take different time to restore

texts:
I have two backup files one from Normal SQL Server and one from SQL Server from Azure VM.

The restore of these backup files is totally different from each other. Though the database backups are performed at the same state, meaning the Normal Servers backup is being first restored on Azure VM1 took 20 Minutes. and then took backup of Azure VM1 and restoring it on VM2 took 1 Hour and 30 minutes.

To be more clear on the above point, I haven't started any writes on Azure VM1 before taking backup again.

Can anyone shed some light on what is different when we are taking backup from Azure VM and restoring it?

Thank you.


texts: PostgreSQL: translating user-defined calculations into executable calculation in trigger

texts:
I am looking to convert user defined machine readable string formulas into a set of queries and calculations, the result of which would then be saved in another non-normal table. The calcs would be executed via a trigger and a generalized/dynamic/flexible calculation function(s) that would execute when a certain table (bbg_pulls in the simple example below) is updated or inserted on.

For example, if the string passed into the calculation function was:

'[bp2][-1]/[bp5]'

Where [bp#] designates the table (ex: bp = bbg_pulls) and id and [+/-#] designate the period lag (day lag in my example) relative to now() for ID just to the left of it.

Then the query would be something like:

select val1/val2 as fin_calc
from
    (select val from bbg_pulls
    where
      id = 2
      and val_date = (select (CURRENT_TIMESTAMP - INTERVAL '1 day')::date)) as val1
    ,(select val from bbg_pulls
    where
      id = 5
      and val_date = (select CURRENT_TIMESTAMP::date)) as val2

(Note that all of the above made up and I have no way to test the queries so they may be syntactically off etc. Hopefully you understand my point though.)

My example above only includes division of two items in the same table, but other strings could be much more complicated, and I would need to incorporate things like percentile rank, the basic mathematically operates, absolute value, etc.

We use PostgreSQL 9.3.5. The queries above are dramatically over simplified for clarity.

What is the correct approach here? Warehouse/MDX/cube solution? Or is there something like this out there already?

My current approach/thinking is to go through all of the various calculations we use and define a separate query etc. in psql for each, create some logic to find out which type of query/function is desired and pass in the IDs as parameters to the specific query/function that was identified. In other words, not a generalized calculation generation function that can take any string calculation, but a set predefined functions and queries that all have to be defined separately and identified in the string formula that is passed in. One would exist for each minor difference in calculation, and we have a lot.

If this question is not answerable, please let me know and I'll take it down.


texts: Grant user from my database to execute xp_cmdshell

texts:
I'm working with SQL Server 2016 and I need to let an user from MyDatabase to execute xp_cmdshell. I do the following:

USE [MyDatabase]
GO

GRANT EXECUTE ON xp_cmdshell TO myUser
GO

myUser is an user from MyDatabase and when I run the above code, logged as sa, I get the following error:

  Permissions on server scoped catalog views or system stored procedures
  or extended stored procedures can be granted only when the current
  database is master.

Do I need to add the user to master database? I don't think it is safe.

I need this because I want to write a text file from a stored procedure. I have found the following article, http://www.nigelrivett.net/WriteTextFile.html, saying that I can write a text file using:

You will need to create the data to be output as in dynamic sql statements
The first command here creates or overwrites the file - the rest append to the file.
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'

I ask this because my stored procedure will generate a large string (nvarchar(max)) json and when I try to write it down with C# I don't get anything: neither the file or an exception. I've thought to write the json string inside the stored procedure (if I can).

The stored procedure ends correctly, I have checked in the debugger that I get something into the output parameter json (I know it because Visual Studio sets that there is not enough memory to show its contents), the File.WriteAllText is run because I have a breakpoint on it, and also, the path is absolute. But maybe, there is something that I ignore and I have to double check everything again.


texts: I'm doing MySQL exercises and I found some of PostgreSQL, and there is a part where before entering the data to the newly created tables, enter that command, SET DATESTYLE TO 'European'; I tried to put it in MySQL but it does not recognize it, what would be its equivalent?


texts: optimization tips recommended for this query

texts:
I have these queries

select decode(sum(txn_amount),'',0,sum(txn_amount)) as txn_amount, count(txn_id) as txn_num,
currency_code, (select rpt_descr from rptcodesmtb
where rpt_code = txnmtb.txn_type and rpt_type='txn_type'
and merchant_id = txnmtb.merchant_id) AS txn_type from txnmtb
where txn_date between TO_DATE('2010-09-01', 'YYYY-MM-DD') and TO_DATE('2019-09-25', 'YYYY-MM-DD')
and merchant_id = 2 and txn_type IN
('FTO','DOMACH','BUYCOUPON','INTBOOKTRF','SIDOMRTGS','RTGS','BP_SCHEDR','DOMRTGS','DOMESTICRTGS','BP','FT_SCHEDR','MC','DOMESTICACH','INTERNAL','DOMBOOK','NETBANK','PP_SCHEDR','PREPAID','ACH','DOMESTIC','SIDOMBOOKTR','SIBILLPAY','SENDGIFT','FTA','FTB','SIDOMACH','MC_SCHEDULER','SIDOMESTIC','DOMESTICBOOKTRF','REDEEMGIFT','FTL','MCC_SCHEDR')
group by merchant_id, txn_type,currency_code

I'm getting a total costs of 6062, the major costs is due to the date range selection. even after adding index for the where condition like create index txnmtb_idx on txnmtb(TO_DATE(txn_date),merchant_id,txn_type) i did not notice any decrease on cost, also if the function is changed from to_date to to_timestamp i'm getting lesser costs even if the column datatype for txn_date is DATE. The oracle DB seems to always do a full table scan regardless of index is present or not.

Also i would like to know if left JOIN is possible to remove this query select rpt_descr from rptcodesmtb where rpt_code=txnmtb.txn_type and rpt_type='txn_type' and merchant_id= txnmtb.merchant_id, the cost for this query is mentioned as 1

Explain plan is


texts: SQL Server - Snapshot is useful in production environments?

texts:
I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature.  In theory all seems like beautiful.
I also read that a snapshot will copy a database at a point in time and you can use this to restore a database.
In this answer there is a comment (by Peter Schofield, 2013) about SQL Server snapshots not having support, and is useful in a development environment for quick rollbacks.

[...] Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support[...]
[...] It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs.[...]

I would like to know if snapshots are really useful in production environments. What are some examples of usage in production, and please include personal examples about when you've used snapshots to provide a solution on production systems.
The principal objective is provide some examples of real usage and through these examples get some useful ideas for me and for everyone who will be reading this post.
In my case I use SQL Server 2017 Enterprise Edition in production environment.


texts: pg_dump can't write to drive C | Windows 10

texts:
Problem is just as the title says.

pg_dump literally can't write to anywhere on drive C: - the drive windows is installed on.

More details:

created Temp folder on C: drive
modified access - added user EVERYONE with Full Permissions
running pg_dump.exe --file "c:\\Temp" --host "localhost" --port "5432" --username "postgres" --verbose --format=c --blobs "ldmgmt"

Result:

pg_dump: [custom archiver] could not open output file "c:\\Temp": Permission denied

But if I change drive to D: - it works.

Anyone knows how to make it write to drive C:?


texts: Postgresql Streaming Replication Error: WAL segment removed

texts:
I want to set up PostgreSQL streaming replication, but get the following error:

FATAL:  could not receive data from WAL stream:
ERROR:  requested WAL segment 00000001000000000000006A has already been removed.

Master IP : 192.168.0.30

Slave  IP : 192.168.0.36

On Master:

I have created a user rep which is used solely for replication.

The relevant files inside Postgres config directory (/opt/Postgres/9.3/data):

pg_hba.conf:

host    replication     rep     192.168.0.36/32   trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.30'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

I've restarted the postgres service.

On Slave:

I've stopped the postgres service, then applied the changes to the two files:

pg_hba.conf:

host    replication     rep     192.168.0.30/32  trust

postgresql.conf:

listen_addresses = 'localhost,192.168.0.36'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

For replicating the initial database I have done:

On Master:

Internal postgres backup start command to create a backup label:

psql -c "select pg_start_backup('initial_backup');"

... for transferring the database data to slave:

rsync -cva --inplace --exclude=*pg_xlog* /opt/Postgresql/9.3/data/ 192.168.0.36:/opt/Postgresql/9.3/data/

...for internal backup stop to clean up:

psql -c "select pg_stop_backup();"

On Slave:

I've created the following recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.30 port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

Starting the postgres service on the slave starts without any errors but is still waiting:

ps -ef | grep -i postgres

postgres 12959     1  0 13:39 ?        00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres 12969 12959  0 13:39 ?        00:00:00 postgres: logger process
postgres 12970 12959  0 13:39 ?        00:00:00 postgres: startup process   waiting 00000001000000000000006A

Meanwhile, on master:

ps -ef | grep -i postgres

postgres  5930     1  0 13:39 ?        00:00:01 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
postgres  5931  5930  0 13:39 ?        00:00:00 postgres: logger process
postgres  5933  5930  0 13:39 ?        00:00:00 postgres: checkpointer process
postgres  5934  5930  0 13:39 ?        00:00:00 postgres: writer process
postgres  5935  5930  0 13:39 ?        00:00:00 postgres: wal writer process
postgres  5936  5930  0 13:39 ?        00:00:00 postgres: autovacuum launcher process
postgres  5937  5930  0 13:39 ?        00:00:00 postgres: archiver process
postgres  5938  5930  0 13:39 ?        00:00:00 postgres: stats collector process

psql command on the slave gives:

psql.bin: FATAL:  the database system is starting up

--> cd pg_log gives reason for waiting:-

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment  has already been removed

00000001000000000000006A segment is not in master's pg_xlog but it is in slaves pg_xlog

How can I solve this error?


texts: PostgreSQL, how to extract all the possible couples of a primary key in a table?

texts:
I would like to find out a PostgreSQL command that reads the content of a table, and returns all the possible couplings of the primary key of the table.

Something like this:

input_table (ID is the primary key):

- - - - - - - - - -
ID | label | name
- - - - - - - - - -
11 | aaa | Jenny

24 | bbb | Larry

35 | ccc | Trevor

48 | ddd | Jules
- - - - - - - - - -

The query should return all the possible couplings of the primary keys:

- - - - - - - - - -
ID_left | ID_right
- - - - - - - - - -
11 | 24

11 | 35

11 | 48

24 | 35

24 | 48

35 | 48
- - - - - - - - - -

Is it possible to design this query? If yes, how?

Thanks


texts: While altering tablespace shows error tablespace does not exist

texts:
SQL> select * from v$tablespace where name = 'EXAMPLE';

       TS# NAME                INC BIG FLA ENC     CON_ID
         4 EXAMPLE             YES NO  YES              3

Above query shows that i have a tablespace EXAMPLE

But following query says i have no tablespace with the same name.

SQL> ALTER TABLESPACE EXAMPLE RENAME TO EXAMPLE_NISAR ;
ALTER TABLESPACE EXAMPLE RENAME TO EXAMPLE_NISAR
*
ERROR at line 1:
ORA-00959: tablespace 'EXAMPLE' does not exist

I am logged in as sysdba


texts: Does the version of a SQL Server Witness matter?

texts:
Can you have a SQL Server witness in a mirror setup which has a higher SQL Server version than the mirror?

Ie. the mirror is on SQL Server 2012 Standard and the witness uses SQL Server 2014 Express.


texts:
I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of hours they worked. There are many employers who each employ many employees who each can technically clock in and out an unlimited number of times daily. My goal is to be able to easily select an employer, get all his/her employee, and be able to make a table for their hours for any given week. Here's how I was thinking to design the schema:

Employer
Employer id
Employee id (FK)
Name

Employee
Employee id
Employer id (FK)
Name
Password

Timesheet
Employee id (FK)
Employer id (FK)
Timestamp
A boolean indicating whether the employee is checking in or out

So every time an employee checks in or out I put an entry to the timesheet table. Then to grab a week, I grab all the timesheets for an employee within that week's range and compile them into a table. This doesn't seem to be the most efficient way, but I am not sure of a better way.

Any help is very much appreciated!


texts: Database consistency checker in postgresql

texts: Are there any DBCC(Database consistency checker) commands in PostgreSQL? I could find SQL server DBCC commands but not for Postgres? I read that postgresql has inbuilt functionality of perfomance tuning and no DBCC commands are available for postgres. Is it true?


texts:
I inherited a few SQL Servers (2014) with AlwaysOn AGs on AWS. I am totally new to this and we are talking live production environments. 2 different setups. Both have 2 servers each with one AG, so pretty simple. However, one set has a listener created and the other doesn't.

The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The GIS application accessing this server cannot connect to it after a failover. Once we failed back over to the original primary, it worked fine again. It doesn't use the listener to connect, instead it uses the cluster name.

I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

Can I just remove the listener since it is not used anyways?

But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Can someone point me in the right direction, please?


texts: is "%LIKE%" faster than executing multiple queries?

texts:
for example, each user on my site has 2 phone numbers (i.e. let say 1234 and 6789) .

but about performance , which is faster:

to save the numbers in one column(separated by comma,like [1234,6789] ), and thus, searching the columns with :

select  userID from table where number LIKE %1234%

or saving values into 2 columns

select  userID from table where number1=1234 or number2=1234

which will be faster? lets say i have thousands or millions of rows.