What is the recommended way to autostart a Postgres cluster using a nonstandard data directory? I am running PostgreSQL 9.5 on Linux Mint 18.

At installation, apt creates a cluster in /var/lib/postgresql/9.5/main.
Environment settings (postgresql.conf, pg_hba.conf, pg_ident.conf) are stored in /etc/postgresql/9.5/main.
postgresql.conf sets data directory to /var/lib/postgresql/9.5/main.

I have a root partition that is deliberately small, and want to have my cluster on a larger data partition. I ran initdb in /data/pg_data. I am able to start and stop this cluster manually using pg_ctl. My question is, how do I start Postgres using this cluster at system startup?

I read 17.3 Starting the Database Server in the Postgres docs. That page describes (a) setting the PGDATA environment variable, or (b) having the server start the postgres daemon at boot time.

Using PGDATA

I tried to accomplish this by adding the following line to /etc/environment:

PGDATA="/data/pg_data"

I then restarted. Postgres started, but is still using default data directory. echo $PGDATA indicates environment variable was set, and if I stop running cluster, pg_ctl start without data directory specified does start desired cluster. But it's not handling it at boot time.

Using init scripts

I can't figure this one out.

The docs say:

  Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories.

  On Linux systems either add

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

  to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.

Linux Mint uses init.d folder. I found a postgresql file in there and opened it. It is a 60 line bash script. The first few lines are:

# Setting environment variables for the postmaster here does not work; please
# set them in /etc/postgresql/<version>/<cluster>/environment instead.

[ -r /usr/share/postgresql-common/init.d-functions ] || exit 0

. /usr/share/postgresql-common/init.d-functions

So, first I look in /etc/postgresql/9.5/main/environment. That file is empty except for comments. The first line says # environment variables for postmaster process. Is something supposed to go in here that points to my desired data directory?

The file includes /usr/share/postgresql-common/init.d-functions. That is a 112 line file that clearly points to /etc/postgresql/<version number> and attempts to start all clusters that it finds there. It also clearly references postgresql.conf. So it seems that this is how the default cluster is being started, but it is not clear to me that this can or should be edited.

Are You using the official packages[1], supported by the PGDG?

You can do 2 things:

edit the startup script file (or make a simbolic link) pointing to the new directory.
Edit the /etc/postgresql/[version]/[cluster]/pg_ctl.conf to point to the new directory;

Please, take a look a documentation from debian website[2] to see more details.

EDIT: Looks like what you are looking for it's the pg_createcluster[3] utility.

Hope it helps!

References:

http://apt.postgresql.org
https://wiki.debian.org/PostgreSql
http://manpages.ubuntu.com/manpages/trusty/en/man8/pg_createcluster.8.html


Restore DIfferential backup on azure managed instance? I am migrating my database from on-premise to Azure managed instance. In order to move the database I'm using the organization weekly full backup, and I am asking them to take a diff after they take the application offline (the databases are very large).

Question: Can I restore a differential backup on azure managed instance?

When I try to restore the full with norecovery than apply the DIFF. I get an error: "restore with norecovery is not a supported option in azure managed instance"

Question: Can I restore a differential backup on azure managed instance?

No. Borrowing from my answer here, Managed Instance only supports the restore of full backups; no log or diff. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs or diffs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.


How to log value that cause unique key violation in SQL Server I am inserting data into a table from temp table as below:

INSERT INTO TBL_PERSON (PropertyID, PERSONID, FULLNAME)
SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts

The unique key constraint is defined in Tbl_Person using combination of PropertyId, PersonId and FullName columns.

When this query is executed, how can I find out which combination of values are causing unique key constraint exceptions?

I want to log these values with the exception message.

is there a reason this logic won't work for you?

--Find all duplicates and save to temp table
select * into #Duplicates from
(
    SELECT DISTINCT PropertyID, PERSONID, FULLNAME FROM #SalesContacts
    INTERSECT
    SELECT  PropertyID, PERSONID, FULLNAME FROM TBL_PERSON
) a

--Log the duplicates
insert into ErrorLoggingTable(PropertyID, PERSONID, FULLNAME, ErrorMessage)
select PropertyID, PERSONID, FULLNAME, 'Duplicate Found' from #Duplicates

--Remove the duplicates from the #SalesContacts table
delete a from #SalesContacts a join
#Duplicates b on b.PropertyID and b.PersonID and b.FullName

--Insert the rest
insert into TBL_PERSON (PropertyID, PERSONID, FULLNAME)
    select PropertyID, PERSONID, FULLNAME


Error when restoring Postgres Database on RDS I am following this to restore an externally backed up Postgres DB onto my RDS instance - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

However, when I try to restore I get the following errors -

CREATE EXTENSION
psql:database.sql:2615: ERROR:  must be owner of extension plpgsql
CREATE EXTENSION
psql:database.sql:2629: ERROR:  must be owner of extension pg_trgm

I believe this has to do with assigning superuser privileges to the user. However, I know this is not possible to do in RDS. How can I workaround this?

These errors are harmless and can be ignored.

They stem from COMMENT ON EXTENSION statements in the dump.


Checking SQL AlwaysOn backup target with fn_hadr_backup_is_preferred_replica, different answers with different permissions I've implemented AlwaysON backup with Ola's script, those are running fine on designated server configured for backup. I would like to automatically check backups using sql-scripts fired from a monitoring system using a dedicated account just for this. This account has no permissions except public und view server state. If this account selects the following:

select sys.fn_hadr_backup_is_preferred_replica ('TestDB) AS IsPreferredBackupReplicaNow

It gets 0 as an incorrect answer. Firing the same query using sysadmin permissions give 1 as correct answer.

Anyone knows why this is like that and what permissions are needed to get a correct result? MSDN has no word about permissions.

Thanks in advance

Build: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

You have to GRANT the user view server state and  view definition on availability group.


Can I GRANT SELECT on all schemas in a database? I need to create role users of which can only read (select) data.
I can do it for a particular schema.
But I need it to work with all existing and future schemas (existing at least). How can I do it?
I've tried
GRANT select ON DATABASE my_db TO my_role;

But it says that I can't:

0LP01 invalid_grant_operation

p.s: I need to grant rights on select to all schemas in db, I'm just trying to do it with db in query as a option, but if you know how to do give role permission on select for the hole db, I'll be glad to see that too

You can wait for PostgsqlSQL v14 and its pg_read_all_data system role.
Other than that, you will have to grant USAGE on all schemas and SELECT on all tables individually.
GRANT SELECT ON ALL TABLES IN SCHEMA will make that job much easier.
My recommendation is to issue all these grants not to the end user role, but to an intermediate role that you then grant to the end user. That way it will be easy to drop qaread.


Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS? The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table cause the problem. Any page that updates, inserts, or deletes an entry of the jobs "table" takes around 10 seconds to load. (E.G. UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)
SELECT queries seem to execute as before, though they seem to be slower if an update is taking place at the same time.
The table has around 180,000 entries. I noticed in the PHPMyAdmin view, that in addition to the "normal" index on the primary field, there is an index on the "entry_date" field (see image). Could that be an issue in this case? I've no idea why an index on that field would have been created.
If not, what else could be the source of the problem? I've checked space on the disk, which seems OK. (7 GB available) according to df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL DEFAULT '0',
    `entry_date` date NOT NULL DEFAULT '0000-00-00',
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00',
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL,
    `description` text COLLATE latin1_german2_ci NOT NULL,
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL,
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
    `status` tinyint(4) NOT NULL DEFAULT '0',
    `cat_id` int(4) NOT NULL DEFAULT '0',
    `price` decimal(4,2) NOT NULL DEFAULT '1.00',
    `emailcount` smallint(5) NOT NULL DEFAULT '-1',
    `emailcount2` int(11) NOT NULL DEFAULT '-1',
    `recemailcount` int(11) NOT NULL DEFAULT '-1',
    `archive` tinyint(4) NOT NULL DEFAULT '0',
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL,
    PRIMARY KEY (`job_id`),
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

UPDATE - Firstly thank you to all the contributors. I appreciate it very much. So over the last couple of days, the problem stopped, which made trying to find what the issue might be even more difficult. But now it seems to have returned once again. Let me start by providing the machine type hosted on google cloud: g1-small (1 vCPU, 1.7 GB memory). I'll continue updating this with the further information that's been requested by those commenting.

To answer the specific question:

No, an extra index on (entry_date) or two would not hurt performance of updating a different, title column.

Additionally:

No, the version of MySQL, 5.6, is not too ancient, even if some modern features (like window functions) are missing. You should have decent performance with decent hardware.

We can only speculate on the issue in hand but what could be wrong or explain it:

old, inefficient hardware: check the disk specifications, measure it's preformance.

fragmented index/table. Check MySQL docs and old questions/answers here of how to defragment MyISAM tables (OPTIMIZE TABLE).

Last but not least:

Your table is using MyISAM engine, which is old news. InnODB has replaced it as the default engine in MySQL years ago. There is no active development of this engine. It lacks several features compared to InnoDB (transactions, foreign key constraints, etc) and performance in most work loads. I strongly suggest you change your table (after testing of course that your apps and procedures do not break) to use InnoDB.

Importantly to your Question, InnoDB can perform SELECT and UPDATE at the same time (usually).  MyISAM completely locks the table; the update or select must finish before the select or update can even start.

When switching from MyISAM to InnoDB, be sure to adjust key_buffer_size and innodb_buffer_pool_size.


Difference between two XMLTYPE data types In Oracle Database, there are two XML-related data types, SYS.XMLTYPE and PUBLIC.XMLTYPE.
What is the difference between them?

That's simple.

Check the object type of the "public" object

select object_type
from all_objects
where owner = 'PUBLIC'
and object_name = 'XMLTYPE';

the above returns

OBJECT_TYPE
-----------
SYNONYM

So we have to look into all_synonyms:

select owner, synonym_name, table_owner, table_name
from all_synonyms
where synonym_name = 'XMLTYPE';

returns:

OWNER  | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME
-------+--------------+-------------+-----------
PUBLIC | XMLTYPE      | SYS         | XMLTYPE


Invoices kept in document with user information in no sql database is recommended? I have a application which has tenant details like

{
"name" : "",
"Address" : "" ,
...
Bills :[
     current : {"rent":"" ,"electricity":"" ,"date":""},
     previous : [{"rent":"" ,"electricity":"" ,"date":""},
                {"rent":"","electricity":"" ,"date":""}]
]
}

I have Bills of a tenant in the tenant document itself ,
is this model recommended / acceptable ?

The reason would be silly ,
I thought since the scope of bill is only viewing and won't do any computations on bills , So keeping it in a separate document would be a extra work . So I kept in this tenant document itself .

So Keeping bills in this type of document is good or bad ?

If I get some clarity it would be nice , Thanks .

Almost always with databases, it depends on what you're going to do with the data after you're stored it.

How are you going to retrieve and process it?

If you only ever access this data by Tenant, then carrying a block of "stuff" around with each Tenant "document" is probably OK.
If you wanted to start querying and comparing bills across your tenants, though, this structure might be inefficient.

  ... won't do any computations on bills ...

Do you not have to summarise your rental income for your annual Tax Return?

That would mean pulling out any Current or Previous "rent" entries that fall within the last Tax Year - I suggest that might get just a bit fiddly with the data structure you have (and, although you've not shown any, the Date formats that you're using).


Storage strategy for combination of all table items Let's say I've one table with items with the following fields:

id_item
name
description

& one table with the user information, following like:

id_user
email
name

Now I'd like to let vote my user for any combination of all the items (like a head-to-head competition)

The thing is now that I'm struggling to find a suitable database model. Following I can't decide between the following two options:

Create a relation table which creates for each combination of item an own hash. Then I would have a second table storing the results:

CREATE TABLE combination
(
    hash VARCHAR NOT NULL,
    id_1 INT NOT NULL,
    id_2 INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (id_1)
        REFERENCES items(id_item)
    FOREIGN KEY (id_2)
        REFERENCES items(id_item)
);

CREATE TABLE result
(
    id INT NOT NULL,
    hash INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (user_id)
        REFERENCES user(id_user)
);

Create a result table which, creates for each combination and user vote.

CREATE TABLE result
(
    id INT NOT NULL,
    item_id_1 INT NOT NULL,
    item_id_2 INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    FOREIGN KEY (item_id_1)
        REFERENCES itme(id_item)
    FOREIGN KEY (item_id_2)
        REFERENCES item(id_item)
    FOREIGN KEY (userID)
        REFERENCES user(id_user)
);

My thoughts are especially about:

checking for which combination the user already answered/voted -> finding the missing
triggers for creating new combination pairs
storage efficiency (saving hash + user or saving 2x ID + user)

Here's a way to count how many of each pair of items is upvoted:

CREATE TABLE pairs (
    item1 ...,
    item2 ...,
    ct INT
    PRIMARY KEY(item1, item2) )

INSERT INTO pairs
     (item1, item2, ct)
     VALUES
     (?, ?, 1)   -- value list
ON DUPLICATE KEY UPDATE
     ct = ct + 1;

It has a flaw of saying the pair (x,y) is different than (y,x).  If that matters, there are two workarounds:

On input:  Change "column list" to

    (LEAST(?,?), GREATEST(?,?), 1)

Or, on output: combine the counts for matching pairs.

(I don't know why you mentioned "users".)


Recover corrupted blocks in Oracle 10g XE I am using Oracle 10g XE. I've noticed corrupted block error in datafile 1 in alert log. As I've found Oracle XE doesn't support Block Media Recovery(needs to be Enterprise Edition).

So I did the following:

shutdown immediate;
startup mount;
recover database;
alter database open;

But corrupted blocks are still there.

Should not have my recover command solved this problem? Why not?

Edit

I have restored before recover and now it works. So the following steps are working:

 shutdown immediate;
 startup mount;
 restore database;
 recover database;
 alter database open;

Does recover just looks for the SCN and if the SCN is current for the datafile then recovers nothing? Not even corrupted ones?

As I guess recovery is just to apply archive logs(or online logs) to catch up to the current SCN, but not correcting corruptions.

As you discovered by yourself, RECOVER is just the process of applying logs .

Since corruption occurs at physical level, you must restore the affected datafiles from a backup taken BEFORE the corruption occurs. RESTORE rebuilds the physical attributes of the datafile, so, any physical corruption (eg. disk failure) will be fixed.


Automatically convert views to underlying query I've been moved onto a new project which uses numerous VIEWS in MySQL (5.7) queries. From what I understand, Views in MySQL are not the most performant and shouldn't be used for the sake of saving a bit of typing. I'm trying to profile the performance so I have an idea of whether or not it's a bottleneck but I'm having trouble trying to unravel a view to it's underlying query.

I can see the SQL query for the view but then that SQL contains its own view lookup and there's a chain. Is there a way, ideally using MySQL WorkBench, to have the merged view outputted so I don't need to do it by hand? There can be chains of 4 or more views and my head is just confused trying to put the parentheses in the right places around the right SELECT...WHERE/JOIN queries and I'm sure there's a way to automate this.

Thanks

I don't think there is a way to get the "merge", unless it is in the JSON; see below.

Meanwhile, run EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON SELECT ... to see what it says.  I think it will, with dubious clarity, tell you whether it is doing the "merge" or "materialization".


Disable parameter sniffing for better performance - good idea? I have an optimized query in SQL Server 2012 that takes consistently ~1-3 seconds to run.

It works fast when I run it in SSMS, but when application does it (through sp_executesql), it is really slow, about 13 minutes for larger data.

After some digging and reading Slow in the Application, Fast in SSMS?, I concluded that there is a trouble with parameter sniffing (there is one parameter).

I disabled it with OPTION (QUERYTRACEON 4136) and it works fast with sp_executesql.

Is it a good idea (should I keep the option to disable parameter sniffing) or should I tune query so it works with sp_executesql and parameter sniffing?

Query/index tuning is always a good idea but independent of parameter sniffing. Ideally, every execution plan would optimal and reusable but trade-offs must be made when the optimal plan varies depending on the supplied values (non-trivial queries).

You could use the OPTIMZIE FOR UNKNOWN query hint (or local variables) for non-trivial queries so that the plan is generated based on average density estimates rather than the stat histogram of the provided parameter value. This will generate a more stable plan that can be cached and reused. However, this compromise plan could be less efficient for certain parameter values and very costly is some cases. Another option is the OPTIMZIE FOR hint with specific values, which is appropriate when the plan generated for unknown values isn't satisfactory.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution. That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second. Consequently, you need to consider your workload, variation in parameter values, and execution frequency to determine the optimal approach for your workload.


Change expired user password on sql server from linux I have got a colleague (software developer) working on Linux only. He connects to our SQL Server using a sql login with password. Occasionally the password expires and he has to choose a new one. Up to now he then heads over to a colleague with windows workstation and uses SSMS to change the password. I have just figured out that sqlcmd offers a parameter -z to change the password...however this parameter is not available within sqlcmd for linux. Azure Data Studio does not seem to support a password change either.

Is this it and you have to change the password using windows or is there any option from Linux which I haven't figured out?

There may be a better solution to this, but I often faced similar issues with Mac users whose passwords would expire, and due to lack of SSMS were unable to reset themselves.

I took to having a SQL Agent job to email any known non-Windows users of their password expiration 7 days out so they had plenty of time to issue the password reset themselves. This annoyingly requires keeping track of these users, but in a Microsoft shop it was manageable as it was only a few people.

In this case, if the account is already locked, ask your DBA to reset it and inquire about some sort of warning system like the above in the future. Or, like you said, find a willing Windows user to help you out.


EXECUTE sp_executesql i have a problem with EXECUTE sp_executesql  and stuck with it.
my query is as follows

   DECLARE @TABLE_NAME VARCHAR(5)='#T'
   DECLARE @TABLE_ID INTEGER=0
   DECLARE @QRY NVARCHAR(MAX) =''
   DECLARE @TABLE VARCHAR(3)
 DECLARE CRSR_WAGES CURSOR FOR SELECT Code,Name , ROW_NUMBER() OVER (ORDER BY U_Prrty, DocEntry) Slno, COUNT(Code) OVER (PARTITION BY OBJECT) ColNos
                                     FROM  [@CCS_WAGE]
                                     WHERE  U_DISPP = 'Y' AND U_DEDWT='E'
OPEN CRSR_WAGES
FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
WHILE @@FETCH_STATUS=0
BEGIN
    SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID)
    SELECT @TABLE AS 'TABLE'
    SET @QRY='(SELECT empno, amt AS '+'['+@WAGE_DESC+']'
    SET @QRY=@QRY+' INTO '+@TABLE
    SET @QRY=@QRY+' FROM CCS_PYMONTHRESULT WHERE wgcod='''+@WAGECOD+''')'
    SELECT @QRY
    EXECUTE sp_executesql @QRY
    SET @TABLE_ID=@TABLE_ID+1
    DECLARE @STAR AS NVARCHAR(50)='SELECT * FROM ' + @TABLE
    SELECT @STAR
    EXECUTE sp_executesql @STAR
    FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
        END
CLOSE CRSR_WAGES
DEALLOCATE CRSR_WAGES

but the problem is when execute this error message comes like this
Invalid object name '#T0'.
Invalid object name '#T1'.
Invalid object name '#T2'.

Assuming that the temporary tables that you are referencing exists, then your problem is session scope. Temporary tables marked with a single # are visible to the session that they are created in, they are also known as local temporary tables.

The problem that you have is that when you execute a piece of dynamic sql it runs in a new session - hence that session cannot see the temporary tables that you have created.

To fix the problem you could do one of 3 things:

Create the temporary table within your dynamic sql
Use a global temporary table using ## as these are visible across all sessions
Re-design your code to insert into a table variable and return that as an output parameter to sp_executesql

I hope this helps you.


Most efficient way to retrieve data by timestamps I'm using PostgreSQL 9.2.8.

I have table like:

CREATE TABLE foo
(
    foo_date timestamp without time zone NOT NULL,
    -- other columns, constraints
)

This table contains about 4.000.000 rows. One day data is about 50.000 rows.

My goal is to retrieve one day data as fast as possible.

I have created an index like:

CREATE INDEX foo_foo_date_idx
ON foo
USING btree
      (date_trunc('day'::text, foo_date));

And now I'm selecting data like this (now() is just an example, i need data from ANY day):

select *
from foo
where date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date)

This query lasts about 20 s.

Is there any possiblity to obtain same data in shorter time?

EDIT:

EXPLAIN ANALYZE of select above:

"Index Scan using foo_foo_date_idx on foo (cost=0.01..20762.83 rows=10421 width=1534) (actual time=0.180..249.126 rows=19773 loops=1)"
"  Index Cond: (date_trunc('day'::text, now()) = date_trunc('day'::text, foo_date))"
"Total runtime: 251.883 ms"

I have changed a day for 15-12-2015. What is more, I have replace * with only specific columns. Explain plan:

"Index Scan using foo_foo_date_idx on foo (cost=0.00..57876.52 rows=29184 width=62) (actual time=22.384..28243.356 rows=33153 loops=1)"
"  Index Cond: ('2015-12-14 00:00:00'::timestamp without time zone = date_trunc('day'::text, foo_date))"
"Total runtime: 28251.876 ms"

Last query could cached i think then... Any other explaination?

To check if an index is working, use SELECT COUNT(*) rather than SELECT *, otherwise you're not only measuring index performance but also the time it takes to transfer all the data across your client application, which in this case is a whooping 50,000 rows!

You can significantly simplify your query and index by date datatype instead of timestamp, which is smaller and therefore it should perform better:

CREATE INDEX foo_foo_date_idx ON foo
USING btree ((foo_date::date));

select count(*) from foo
where foo_date::date = CURRENT_DATE;

You could reduce that index even further by using a smallint calculated as the difference between a set date (e.g. 1/1/2010 +- 32768 days = Apr-1920 to Sep-2099):

CREATE INDEX foo_foo_date_idx ON foo
USING btree (((foo_date::date - '2010-01-01'::date)::int2));

select count(*) from foo
where (foo_date::date - '2010-01-01'::date)::int2 = ('today'-'2010-01-01'::date);


Dealing with single quotes in a dynamic query I'm building a query as a string in my application layer to be executed over jdbc.

The string is constructed as follows:

"SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN "
                + "WHERE "
                + "UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN' AND "
                + "UNIQUE_PHYSICIAN.FIRST_NAME = '"
                + firstP.getFirstName()
                + "' AND "
                + "UNIQUE_PHYSICIAN.LAST_NAME = '"
                + firstP.getLastName()
                + "' AND "
                + "UNIQUE_PHYSICIAN.POSTAL_CODE = '"
                + firstP.getPostalCode() + "'";

This works fine, until we hit a doctor in our database containing a single quote, such as "JEANNE-D'ARC" as her first name. It turns the query into the following:

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D'ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

How can we resolve this?

EDIT: as I understand it, I should be using parameters as something. This query is going into a program and we can't necessarily control what doctor's names come in, so I guess this is a SQL injection vulnerability, right?

You can escape single quotes by using 2 single quotes, that way your query would work. I'm not sure about the java syntax for a replace but your query should look something like this

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D''ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

instead of this

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D'ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

In any case, as has been suggested parametrized queries or stored procedures should be investigated (again, i'm not sure how to parametrize queries in java) .

You should also be very careful about SQL injection, what happens for example if someone enters a name "';drop table patients;--" ? Suppose the line breaks are not sent to SQL as I would expect from your example you get this:

SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = ''; drop table patients;--ARC'     AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'     AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

OWASP is a good resource on security and has a resource on Java SQL injection and prepared statements here

From that page:

  Prepared Statements

  Variables passed as arguments to prepared statements will
  automatically be escaped by the JDBC driver.

  Example: ps.1

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();


Read Only Users on secondary replica AlwaysOn Greeting .
I have a question. I have set up an AlwaysOn availability groups configuration for DR.
Meaning I have a node1 in Site A and a Node2 in site B acting as a DR geographical let say 50 mile apart. The Mode is set Async, Can i read SSRS report from the Node2 ? Am using SQL Server 2016 enterprise
If so how ?
Thanks in advance

You can set an Asynchronous secondary to be readable and connect to it for read-only workloads. See the documentation here.

There are, however, a number of considerations for this:

You must licence the secondary replica. Read workload counts as production workload, so you must have a fully licensed secondary and not rely on Software Assurance coverage which provides a free warm standby licence.
Your data, in an Async replica, may not be current with production. Depending on your latencies, this could result in your reports returning incorrect data. You need to understand your current latency averages and determine if this is acceptable.
You said "Can i read SSRS report from the Node 2?" - If you mean can you run reports from a report server database in the Availability Group from the secondary, no you cannot. SSRS must still access the ReportServer databases in an AG on the Primary Replica. If instead, you meant can you point your SSRS report datasource at a secondary database, yes you can.
You should ensure your read-only routing URLs and lists are configured correctly so that you can utilise read-only routing without hard-coding secondary replica names in connection strings. This helps future-proof against architectural changes over time (new replicas added, server name changes etc).


Migrate schema updates onto existing schema So we have three development environments from a programming point of view.
Each environment has its own postgres database.

Let's say a table gets an extra column in our development environment, we need to update the acceptance environment as well when the code is merged back. Other changes can be column renames etc.

This is needed because we are still developing the app quite heavily, but it is still important that the production environment can keep up on a database level as well.

So the question is: how can I propagate schema updates to a different database without loss of data.

I suggest that you use a tool such as Liquibase or Flyway which is designed to manage such schema changes in a database and keep the configurations consistent and known.

http://www.liquibase.org/

https://flywaydb.org/

There are other tools available and they usually have plugins to maven, Puppet & Chef etc...it'll come down to a google search and personal preference.


How to get a JSON array without JSON_ARRAYAGG in MySQL I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT JSON_ARRAYAGG(column_name) FROM table_name;
I got to this piece of SQL: SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name; which  works almost like JSON_ARRAYAGG. The problem is when there is no rows it returns [null] instead of null.
What is the most efficient way of replacing a JSON_ARRAYAGG function with some equivalent? I would like to avoid something like this:
SELECT IF(
  (SELECT GROUP_CONCAT(column_name) FROM table_name) IS NOT NULL,
  (SELECT JSON_ARRAY(GROUP_CONCAT(column_name)) FROM table_name),
  NULL
);

Finally I've found a solution. I create a string array using CONCAT and GROUP_CONCAT functions and then cast it to JSON.
SELECT JSON_REPLACE(
    data,
    '$.numbers',
    IF(
      (
        SELECT COUNT(number) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ) > 0,
      (
        SELECT CAST(CONCAT('[', GROUP_CONCAT(CONCAT('"', number, '"')), ']') AS JSON) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ),
      NULL
    )
) AS json FROM foo;


Lost connection to mysql while restoring dump. Is it safe to resume? I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS.

The dump was created with the following command:

mysqldump
--all-databases \
--single-transaction \
--compress \
--order-by-primary > dump.sql

During the restore process I lost connection to the RDS instance.

Can I simply restart the restore process of the dump by re-initiating the restore process?

Or, asking more generally:

What excactly happens when restoring an sql dump?
Will already created tables simply be overwritten? Will the database delete old entries and copy over the new database state?

(n.b. I've never used AWS or RDS so there may be some differences). But as a general rule a MySQL DUMP file is just a series of CREATE TABLE and INSERT INTO statements, that when combined create your database.

Ultimately it depends on what options were chosen as to what does and doesn't get included. But I think that by default it includes the --add-drop-table option. So if you open the file (which is basically a text file) you should see:

DROP TABLE tablename;
CREATE TABLE tablename. . .
INSERT INTO tablename. . .

for all of your tables.

Restarting the loading process will simply start from the beginning of the file and carry out the actions accordingly.

(I assume that the import has actually died - its not uncommon (in my experience) to lose connection to MySQL during the import due to the time taken, but the process continues in the background. You can use SHOW PROCESSLIST to check this).


Testing if temp table exists in iSeries SQL stored procedure What would be the syntax for testing if there is already a global temporary table in DB2 for IBM i 7.1?

I tried this after DECLARE GLOBAL TEMPORARY TABLE:

SELECT * FROM QSYS2.SYSTABLES WHERE SYSTEM_TABLE_SCHEMA = 'QTEMP';

and it returned an empty result set. The permanent tables seem to be included into that view, but not the temporary tables.

Ultimately I need to conditionally drop a temp table or truncate/delete data if it exists.

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement.  This will create your table in QTEMP regardless of whether one already exists or not.


SQL Server, odbc newbie question My new job has some sql server 2014.  I am an Oracle DBA.

I need to help a client.

They are having issues creating the ODBC connection from their laptop to the database server (or whatever MS calls a database)

I have access to MS SQL Server 2014 Management Studio.

Questions:
How can I create an ODBC connection to this database on my laptop to test and provide a procedure for the client?

Do I need to create myself a user to test?

(In oracle I would install the client and give them a tnsnames and an alias to use and maybe use tnsping or sqlplus to check the connection)

EDIT1:

I use Oracle SQL Develper and it uses jdbc.  maybe you can help me setup my first JDBC connection.

EDIT2:
Tried the ODBC data source and it failed

EDIT3:
Checked if 1433 is enabled and looks like something is connecting to this server;

Active Connections

Proto  Local Address          Foreign Address        State

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

TCP    xxx.xx.xx.xxx:3389   xxx.xx.xx.xxx:56010    ESTABLISHED

TCP    xxx.xx.xx.xxx:56984  xxx.xx.xx.xxx:2074     ESTABLISHED

If you're using SQL Server Management Studio (SSMS), you don't need to "create" a connection via ODBC, you can just connect directly to the server via the SSMS user interface:

In SQL Server, the equivalent of an Oracle "database server" is called a "SQL Server Instance".  It can contain many databases, which are similar to a "schema" in Oracle.

So, when you connect via SSMS, you connect to the instance, which takes the form of:

COMPUTERNAME

For a "default, unnamed" instance, or for a "named" instance:

COMPUTERNAME\INSTANCENAME

INSTANCENAME gets resolved to a port automatically by the SQL Server Browser Service; if that service is not running, or is blocked by a firewall, you may need to connect using the port number, as in:

tcp://COMPUTERNAME,0000

where 0000 is the port number for the instance.

If you don't have access to the instance, you'll see an "authentication failed" error message.  If SSMS cannot contact the instance because of a firewall, or the server is not listening on the port you specified, you'll see an error indicating that, similar to:

  "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.   Verify that the instance name is correct and that SQL Server is configured to allow remote connections. "

To create a connection via ODBC, you open the ODBC Data Source Administrator from the Windows Control Panel.  You can either create a "User DSN" or a "System DSN", either way choose the SQL Server Native Client 12.0 driver:

Fill in the details (use the servername as detailed above):

Accept the defaults for the rest of the configuration screens if you plan to use integrated Active Directory domain authentication.  If the login will be made using a SQL Server Login, enter those details on this screen:


Two records in the same table must be related somehow I've inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two "structures" are combined into one larger "structure", so there should be a way of linking them.
Should I use an "association table" to link the two IDs, or is there a better way for this sort of thing? Perhaps I've answered my own question, but I thought I'd ask if there's another way...
Another option would be to delete the second record, at the same time as I add a reference to that second record as a new column, in the first record.
Maybe this is a ridiculous open-ended question for this site, but surely I'm not the first person to run into this...
Thanks,
Sean

I believe the requirement to record "these 2+ items are used to make this 1 bigger item" is called a BOM. (Bill Of Materials).
The only set of BOM tables I've seen in action used 1 table for each depth.  Your BOM would only need the two tables.
TL;DR - use the "association table".


Database functional dependency BCNF help I understand most of BCNF and have been able to complete numerous problems with it, I'm just having trouble applying it to the following situation:

R(A,B,C,D,E) FDs: { A->D; BE->A; CD->E }

Here's what I have so far:

Relationship1: (A,D)
Relationship2: (A,B,E)
Relationship3: (B,C,E)

However with this setup the CD->E functional dependency isn't considered. Should that be taken into account in a new relationship, or should D (or maybe even A?) be added to Relationship 3?

For what it's worth, I determined that:

BCE+ = ABCDE
BE+ = ABDE
AC+ = ACDE

I'm just not sure where to go from here. Thanks in advance for the help.

Look up "dependency preservation".

It is a known phenomenon that when decomposing relation schemas (along some given FD), certain other FD's can become inexpressible.

In a complete logical design, they have to be reinstated as a constraint on the database.  However, normalization theory does not cover constraints.  Normalization theory deals only with relation schemas, not with constraints between them.

If you first decompose over CD->E (leaving ABCD / CDE), you can still further decompose over A->D (leaving AD / ABC / CDE), but you will now have "lost" the BE->A dependency.


SQL Server 2005 not collecting logins in the log file I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine.

I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and successful logins. For some reason though there is nothing in the logins for fails or successes in the SQL Server logs when I know there have been logins for both.

I have searched out the reason for this and have not been able to come up with anything helpful, so I am hoping that someone here could give me a little direction. This is working on my other SQL Servers, so I know where to look for the results, but there are none there.

Per SQL Server Books Online...

  Changing the audit level requires restarting the service.

Try restarting your SQL Service and then check to see if logins are being properly audited in your Error Log.


Row estimate more accurate for primary key column than for others? Let's create two test tables in a PostgreSQL 13 database:
CREATE TABLE foo (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  value int NOT NULL
);
CREATE TABLE bar (
  id bigint PRIMARY KEY,
  category_id bigint NOT NULL,
  foo_id bigint REFERENCES foo (id),
  value int
);
CREATE INDEX bar_category_id_ix ON bar (category_id);

and disable autovacuum for these tables:
ALTER TABLE foo SET (autovacuum_enabled = false);
ALTER TABLE bar SET (autovacuum_enabled = false);

Insert 500000 (half of a million) records into foo, transfer them to bar and analyze the tables:
INSERT INTO foo (value) SELECT * FROM generate_series(1, 500000);
ANALYZE foo;
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 1, id, value FROM foo WHERE value <= 500000;
ANALYZE bar;

Optionally ensure that only ANALYZE (no autovacuum) was performed on these tables:
SELECT relname, last_autovacuum, last_vacuum, last_autoanalyze, last_analyze FROM pg_stat_user_tables WHERE relname IN ('foo', 'bar');

Insert another chunk of 500000 records (but don't run ANALYZE):
INSERT INTO foo (value) SELECT * FROM generate_series(500001, 1000000);
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 2, id, value FROM foo WHERE value > 500000;

Since we did not run ANALYZE table statistics is outdated, its related to the stage when foo and bar contained half of million records. Now let's check the query plans:
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 2;
----
Nested Loop  (cost=0.85..12.89 rows=1 width=40)
  ->  Index Scan using bar_category_id_ix on bar  (cost=0.42..4.44 rows=1 width=28)
        Index Cond: (category_id = 2)
  ->  Index Scan using foo_pkey on foo  (cost=0.42..8.44 rows=1 width=12)
        Index Cond: (id = bar.foo_id)

and
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id;
---
Hash Join  (cost=32789.00..71320.29 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..17351.64 rows=999864 width=28)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

I understand that 1st query plan has wrongly estimated only 1 row (rows=1) for condition category_id = 2 because the statistic is outdated (the ANALYZE was performed before inserting records with category_id = 2). (1) But then, how did the 2nd query plan arrive at a good estimation (rows=999864) for condition bar.foo_id = foo.id?
Also if we run:
EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 1;
----
Hash Join  (cost=32789.00..73819.95 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..19851.30 rows=999864 width=28)
        Filter: (category_id = 1)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

(2) Why does the planner estimate 999864 rows for condition category_id = 1? The statistics should show about 500000 of rows satisfying it?
NOTE: I came to these questions because empirically I observed that conditions containing only primary key columns will produce a better query plan even if the table was not analyzed, but I did not find anything about this behavior in PostgreSQL official documentation.

The "magic" you observed lies in this detail of the query planner. Quoting the manual:

In any case, the planner will scale the values it finds in pg_class to
match the current physical table size, thus obtaining a closer approximation.

The values it  in pg_class are reltuples and relpages - the number of live rows and data pages on disk.
Since the physical size will have approximately doubled, Postgres will expect approximately that many rows, which explains the pretty accurate estimate for the sequential scan.
Value frequencies in pg_statistic are outdated and cannot be scaled as trivially as the row count.  You'd need to run ANALYZE for that. Explains the estimate for the index scan.
I don't see how the PK would play any special role in this.


Behaviour of performance counter "Checkpoint Pages/sec" under load I'm working on performance analysis of reports, resulting from different test cases.  I'm using MS SQL Server 2016 Dev edition

Regarding one of the performance counters ('Checkpoint Pages/sec'), I understand the meaning of the counter. During a 22 second interval, the graph drops from 400 to 200 written changed pages per second, and then returns to 400.

Result from the graph:

1-13 seconds, value is around 400
14-16, falls down to 200, then returns to 400
16-38, value is around 400
39, falls to 200 then returns to 400
61, falls to 200 then returns to 400
84, falls to 200 then returns to 400

The values in the performance counter 'Disk Writes/s' are identical to 'Checkpoint Pages/sec'.

I expected these counters to be a straight line (without the peaks and valleys).

Can you explain this behavior?  What do the drops in Checkpoint Pages/sec from 400 to 200 mean?

Checkpoints, like most things in SQL Server, are a big topic.  A good place to start is this Microsoft Docs page: Database Checkpoints (SQL Server)

To respond to your main question though, check out this specific quote from that page:

  The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

So "checkpoint pages / sec" will vary based on a lot of factors:

the type of checkpoints you're using (automatic or indirect)
if using automatic checkpoints, the value of the "recovery interval" server-level setting
if using indirect checkpoints, the value of the TARGET_RECOVERY_TIME database-level setting
disk latency

  Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 50 milliseconds.

the transactional throughput currently happening on the database

All that to say: seeing peaks and valleys in the number of pages being flushed to disk by checkpoint operations is completely normal.

If the purpose of the performance testing you mentioned is to normalize (as much as possible) the number of pages flushed to disk per second, consider reading the documentation page I linked to above and adjusting those settings.


Restore Database or Restore Files? I am copying a database from a live environment to test environment.
I've made a .bak file from the live database and copied the file to the test server.

Question is, should I use "Restore Database" to add it to the test server or "Restore Files and Filegroups"?

(sometimes the database will already exist, sometimes it won't.  In either case I want to just have a clone of the original and don't care about overwriting)

After a bit of playing around it appears both achieve what I want - so what is the real difference, and does it matter which method I use for my situation?

Short answer - unless it's a huge SQL database and you're doing clever things with it then just ignore the filegroup backup, and stick with the database backup.

Long answer - With SQL server you have databases and filegroups, a database contains all of the data within that database and is nice and straight forward. A filegroup on the other hand contains either all or some of the data within the database. Filegroups hold the physical files (eg the mdf files) which make up the database, and in most cases there will only be one of them, normally called Primary, which will contain the one mdf used.

Now in a large installation you might find that your database mdf files are too large to fit onto one disk, so you might want to split the database across multiple mdf files, and at this point filegroups come into play. If you split all the tables which are regularly used and updated into one mdf on a fast HDD and put it in the Primary filegroup, and then put the less used tables used more for reference onto a slower HDD in a secondary filegroup, you could make best use of the system, however you might not want to bother backing up the secondary mdf, since the data within that file very rarely gets changed. You can therefore choose to do a filegroup backup instead of a complete database backup, and only backup the Primary filegroup, therefore reducing the amount of storage space required for your backup.


INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so'

but this command is throwing the below error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
''rpl_semi_sync_master' SONAME 'semisync_master.so'' at line 1

Please help regarding this. We are using Win7 Professional SP1 32-bit OS.

Thank You

I think the problem with your attempt is that you put quotes around the plugin name, along with the fact that you used a .so suffix to the library name.

Step-by-step:

Check that your MySQL install is dynamic_loading capable:

mysql> show variables like '%dynamic%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>

Check that your plugin directory is set correctly:

mysql> show variables like '%plugin%';
+---------------+-----------------------------------------------------+
| Variable_name | Value                                               |
+---------------+-----------------------------------------------------+
| plugin_dir    | C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\ |
+---------------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Then verify the .dll exists in that location:

The plugin install should then work OK:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.dll';
Query OK, 0 rows affected (0.01 sec)

mysql>


What to expect from a benchmark? I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration.  Meaning, that even before I start tuning PostgreSQL configuration, I would like to understand if what I'm seeing in the benchmark results correlates to the physical reality of the hardware.

I would really, really love if there was a table somewhere showing transactions per second as function of disk speed and/or available memory and/or CPU speed / number of cores. Or, a formula to calculate the best performance for the test, with hardware values plugged into it.

If there isn't a formula, but you still feel confident that you can tell what's a "good performance" given these specs, I'd still want to hear from you:

2 vCPUs Intel(R) Xeon(R) CPU E5-2676 v3 @ 2.40GHz
4 GB of RAM
SAN should be comparable to 5000 IOPS 100 GB EBS (NVMe)

What I'm getting right now is around 500 TPS on the TPC-B-like built-in test. Does this number make any sense? What would you hope to achieve given this sort of system? (Again, this system is not supposed to be a production database server, it is used to benchmark the SAN). If it doesn't make sense to benchmark on a less capable system (you cannot extrapolate from the results the behavior on a real system), then what kind of hardware (virtual of course) would you suggest for the more realistic test?

I'm not a hardware expert, but why do you think a SAN should be comparable to a NVMe?

Sorry, there is no simple formula.

At what scale did you initialize the benchmark tables? that is, what number for pgbench -i -s 20? How many clients did you run the benchmark? (What numbers did you provide for -jand -c?)  Did you run pgbench on the same server as was running the database?  For how long did you run it?  The system can absorb write requests very fast for a while.  Then either checkpointing kicks in, or RAM becomes clogged with dirty pages.  For write-heavy benchmarks, I'd be hesitant to draw conclusions from any run less than 30 minutes.

I suspect that what you are benchmarking here is how fast your storage can complete (or claim to complete) a sync request.  Does it get much faster if you do the below?

PGOPTIONS='-c synchronous_commit=off' pgbench ....

If you want to strip out the database itself and just test the underlying sync speed, you can use "pg_test_fsync".  Make use the file it uses gets placed on the correct storage, either by changing directories or by using "-f" option.


Exclusionary Left Joins I'm trying to join 2 different tables (Table99 and Table2) to Table1.  The first is a straight-forward RIGHT JOIN.  The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2, and bring back results from Table1 that don't exist in Table2

Example:

 FROM Table1 tbl1
      RIGHT JOIN Table99 tbl99 ON tbl1.CARD_ID = tbl99.CARD_ID
            AND tbl1.DATE_OPEN = tbl99.DATE_OPEN

      LEFT JOIN Table2 tbl2 ON tbl1.ACCOUNT_NUMBER = tbl2.ACCOUNT_NUMBER
           AND tbl1.APPL_ID = tbl2.APPL_ID'

WHERE tbl1.ACCOUNT_NUMBER <> tbl2.ACCOUNT_NUMBER

The Left Join clause is where I'm trying to pull in all of the results from tble1 that do NOT exist in tbl2

bring back results from Table1 that don't exist in Table2

You can use NOT EXISTS

SELECT
    T1.*
FROM
    TABLE1 T1
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND T2.APPL_ID = T1.APPL_ID );

Update

It doesn't matter how many JOIN's you use before the left anti semi join, it returns all rows from the left side that does not exists on the right side, according the predicate: WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND  T2.APPL_ID = T1.APPL_ID

SELECT
    T1.*
FROM
    TABLE1 T1
RIGHT JOIN
    TABLE99 T99
    ON T99.CARD_ID = T99.CARD_ID
    AND T99.DATE_OPEN = T1.DATE_OPEN
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND  T2.APPL_ID = T1.APPL_ID
                );

Let's imagine 3 tables with next ID's:

 T1    T2    T9
----  ----  ----
 01    01    01
 02          02
 03    03
 04    04    04
 05          05

Give me all ID's that exists in T1 and T9.

SELECT ID FROM T1 JOIN T9 ON T1.ID = T2.ID;

01,02,04,05

and now, give me all ID's of this group that does not exists in T2:

pseudo-code:

SELECT ID FROM (01,02,04,05) WHERE NOT EXISTS IN (01,03,04);

02,05

NOTE: I've replaced table names by their values to make the final result more clear


Deciding on optimal index combinations I have a table with 8 columns, no primrary key, and many, many indexes. Now I wonder whether they're as optimal as can be.

The table looks as follows:

CREATE TABLE `search` (
  `a` bigint(20) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned DEFAULT NULL,
  `d` int(10) unsigned DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is used for searches, and searches contain constant values to look for, and a specific range of two columns (i.e. a, and b). Never together.

An example of a query that occurs very often:

SELECT DISTINCT S.a
  FROM search
 WHERE S.c = 12
   AND S.f = 'foo'
   AND S.h = 'bar'
   AND S.a < 1234567890
 ORDER BY S.a DESC
 LIMIT 200;

An was to have two composite indexes, containing all columns used for constant comparisons, with a single range column appended to it. This resulted in the following two indexes:

KEY composite_a (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `a`)
KEY composite_b (`c`, `d`, `e`, `f`, `g`, `h`, `i`, `b`)

However, this feels excessive, and it still resorts to temporary tables, and file sorting if only a subset of the columns is used for searching.

A different option was to add every single combination with the range columns appended to it (which works quite well), but fills up storage rather quickly. To give a sense of size, it's a table containing a few billion rows. This caused the need for a significantly larger machine with more memory, because of indexes growing into the several terabytes range.

My question is: am I missing an index 'trick'? Is there an efficient combination of index to solve this problem?

That one example needs

INDEX(c, f, h,  -- these can be in any order
      a,        -- this must be after the first 3
      ... )     -- others can follow

Your suggestions with (c, d, ... are not very useful.

If you are expecting to have lots of different queries, I recommend you

Study what is actually used by users.  You will find that there are a few common columns or pairs of columns, and some columns that are rarely used.
Then add a few indexes, no more than, say, a dozen.
Generally don't make indexes with more than 3 columns.
Have the indexes scrambled around.  The two you suggested may as well be replaced by simply (c,d,e,f,g,h,i) since this will be quite useful for both.
Study this for how to generate the optimal index for a given select.


Remove similar and successive rows I have a table with many entries each describing the commerce state of an item.

The table has the following structure:

CREATE TABLE CommerceState
(
    Id INT NOT NULL PRIMARY KEY
    , ItemId INT NOT NULL
    , Listings INT NOT NULL
    , UnitPrice BIGINT NOT NULL
    , Quantity INT NOT NULL
    , [Timestamp] DATETIME NOT NULL
);

At certain intervals I'm saving the current state of an item into this table. It can happen that two consecutive updates contain the same data and just differ in the timestamp.

What I want to do now is to remove those kind of duplicates. I couldn't come up with a script so far. I just got solutions to completely delete all duplicates without respect to the actual timeline.

To visualize what I would like to achieve:

Before:

+ Id + ItemId + Listings + UnitPrice + Quantity + Date                    +
| 1  | 1234   | 10       | 100       | 50       | 2015-10-22 15:55:00.000 |
| 2  | 1235   | 10       | 25        | 150      | 2015-10-22 16:00:00.000 |
| 3  | 1234   | 9        | 100       | 50       | 2015-10-22 16:05:00.000 |
| 4  | 1235   | 5        | 25        | 30       | 2015-10-22 16:10:00.000 |
| 5  | 1235   | 10       | 25        | 150      | 2015-10-22 16:15:00.000 |
| 6  | 1235   | 10       | 25        | 150      | 2015-10-22 16:20:00.000 |
| 7  | 1234   | 10       | 100       | 50       | 2015-10-22 16:25:00.000 |
| 8  | 1234   | 10       | 100       | 50       | 2015-10-22 16:30:00.000 |
| 9  | 1234   | 10       | 100       | 50       | 2015-10-22 16:35:00.000 |
| 10 | 1234   | 9        | 100       | 50       | 2015-10-22 16:40:00.000 |

After:

+ Id + ItemId + Listings + UnitPrice + Quantity + Date                    +
| 1  | 1234   | 10       | 100       | 50       | 2015-10-22 15:55:00.000 |
| 2  | 1235   | 10       | 25        | 150      | 2015-10-22 16:00:00.000 |
| 3  | 1234   | 9        | 100       | 50       | 2015-10-22 16:05:00.000 |
| 4  | 1235   | 5        | 25        | 30       | 2015-10-22 16:10:00.000 |
| 5  | 1235   | 10       | 25        | 150      | 2015-10-22 16:15:00.000 |
| 7  | 1234   | 10       | 100       | 50       | 2015-10-22 16:25:00.000 |
| 10 | 1234   | 9        | 100       | 50       | 2015-10-22 16:40:00.000 |

Any ideas/suggestions on how to write a T-SQL script that does exactly this?

One way to do this is to look up the previous row for each item and check if that row has the same data. If so, we can delete the row:

DELETE c
FROM CommerceState c
CROSS APPLY (
    -- For each row, find the previous row for this item
    SELECT TOP 1 prev.id, prev.Listings, prev.UnitPrice, prev.Quantity
    FROM CommerceState prev
    WHERE prev.itemId = c.itemId
        AND prev.[Timestamp] < c.[Timestamp]
    ORDER BY [Timestamp] DESC
) d
-- If that row matches the current row, we'll delete the current row
WHERE d.Listings = c.Listings
    AND d.UnitPrice = c.UnitPrice
    AND d.Quantity = c.Quantity

Here is a full test script that creates your dummy data, runs the query, and confirms the results.

You can optionally add the following index in order to optimize the lookup of the most recent row, but it would depend on your workload whether the index is worth the extra overhead it incurs when inserting and deleting data.

-- Optionally add this index if you deem the tradeoff to be worthwhile
CREATE INDEX IX_CommerceState
    ON CommerceState (ItemId, [Timestamp])
    INCLUDE (Listings, UnitPrice, Quantity)

With the index, here is the query plan:


Snapshot agent stuck at 48% We are having to redo a publication/subscription and while running the snapshot agent it stalls at 48% with the message "The process is running and is waiting on a response from the server."  Eventually it times out.  I increased the timeout, but even after 4 hours it's still stuck at the same place.

I can't figure out what it's 'waiting on' or how to get it to progress further.  I've tried deleting and recreating the publication from scratch but same results.  Anyone have ideas on how I might troubleshoot it to figure out what the issue is?

Check the history of the agent job for the Snapshot agent on Whatever server is running as the Distributor. Drill down into the history of the agent job and you should find an error. (The error is often listed in the step preceeding the one that is retrying). Will probably be lack of space, etc.

If this does not reveal anything useful then you may need to switch on verbose logging to try and get a better error of the root cause. Its a combination of changing the agent profile and configuring a logging location external to SQL Server. These posts should get you there :-

https://stackoverflow.com/questions/1724322/where-is-the-log-file-located-when-verbose-history-logging-is-turned-on-for-sql

http://blogs.msdn.com/b/repltalk/archive/2010/03/17/troubleshooting-snapshot-agent-hang.aspx


Database design for multi foreign keys For example, I have two tables: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, QuestionID, AnswerContent).

Now, I have a Vote table that support vote for both Question and Answer. I have three ways for designing database:

1. Vote(VoteID, QuestionID, AnswerID) // always QuestionID or AnswerID is null

2. Vote(VoteID, PostID, type) // type = 0. PostID is Question. type = 1. PostID is Answer

3. VoteAnswer(VoteID, AnswerID)
   VoteQuestion(VoteID, QuestionID)

I don't know which database design I should follow in those cases, using ORM and just using native query.

Thanks :)

i'd go for a different approach:

Post(PostID, PostTitle, PostContent, PostParentID)

the field PostParentID is a reference to PostID on the same table: if that field is null then is a question, if is filled then is an answer to the question with id PostParentID.

with the above structure a single table is required to store votes.

there is the potential issue that a post may have an 'answer' as a parent because there are no easy constraints (a trigger is not a constraint and i don't like triggers anyway ^^) to avoid such association but it should be a very limited issue.

with this approach it can be expensive to make statistics because a count of the votes cast on answers require a join with the Post table; having votes on answers and questions in different tables would make such jobs easier and possibly more performing.

it is a winning solution imho if the major task is to access the single question (get votes/counts for a single question/answer) because the logic is simpler, with fewer objects involved and to maintain (I always suppose to interact using stored procedures) and it is the same for answers and questions.


Should my database be on the same server as my web server? We are about to add user login and management to a high traffic website.  I am wondering if I should put the postgres database on the same server or put it in a different node in the internal networked setup.

Reason I have been thinking of this is I believe the database would be more secure if it was only accessible via internal networking.

There are a number of reasons as to why it is advisable to have your database exist on a separate server to your web application.

In my opinion chief amongst these are the security implications. For instance a simplistic network architecture would see your web server in a DMZ, or public subnet, and your database server in a private subnet. The web server can accept inbound traffic from the untrusted internet (providing you are not using a load balancer etc.) whereas your database subnet only accepts inbound traffic from the public subnet.

For more info see this similar stack overflow post here.
The second point in the top answer around scalability is also a key argument to bare in mind.


SQL Server 2008 R2 - Capture or be notified when an sql handle is ran I'd like to monitor a particular SQL handle and find out who (process id) is running the SQL.

Is there a way to retrieve either a list of proc ids who executed the SQL handle in last N minutes or be automatically notified when that query is ran? or is only option is to run sys.dm_exec_requests continuously?

For SQL Server 2008 R2 or earlier, I'd recommend running a SQL Server Profiler session against the server.

On the General tab, in the template dropdown, select "Blank", then on the Events Selection tab, choose "SQL_StmtStarting" from the list of events.  Click the "Column Filters" button, and fill it out similar to this:

When you run this profiler session, the results will only show you rows where the TextData column contains the text you entered.  Alternately, if you have a sql handle you specifically want to track you can enter that in the filter under SqlHandle instead of TextData.

On SQL Server 2012 and above, I'd recommend using Extended Events to do this, however that is out of the scope of this question.


Error 13542 enabling system-versioning for existing tables I'm trying to enable system-versioning for some existing tables in a SQL Server 2016 database, which contain data. I'm following these instructions from Microsoft.

One of the tables looks like this:

CREATE TABLE [dbo].[ClientBeacon](
    [ClientId] [int] NOT NULL,
    [BeaconId] [int] NOT NULL,
    [FromDate] [datetime] NOT NULL,
    [ToDate] [datetime] NULL,
    [Deleted] [bit] NOT NULL,
    [ModifiedByUserId] [nvarchar](128) NOT NULL,
    [ModifiedOn] [datetime] NOT NULL,
    [Timestamp] [timestamp] NOT NULL,
    CONSTRAINT [PK_ClientBeacon] PRIMARY KEY CLUSTERED
    (
        [ClientId] ASC,
        [BeaconId] ASC
    )
)

And the script I'm trying to run looks like this:

CREATE SCHEMA History;
GO

ALTER TABLE dbo.ClientBeacon
   ADD
      SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
      SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE dbo.ClientBeacon SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.ClientBeacon));
GO

The result of running the script is the following error:

Msg 13542, Level 16, State 0, Line 4
ADD PERIOD FOR SYSTEM_TIME on table 'test.dbo.ClientBeacon' failed because there are open records with start of period set to a value in the future.
Msg 13510, Level 16, State 1, Line 11
Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

There isn't much I can find about this error. There's a suggestion in a comment on this post that using UTC in the default might be an issue if UTC is ahead of the server time, but I'm in the UK on daylight savings time, an hour ahead of UTC. I've tried using GETDATE just in case, but the same error occurs.

I had similar issue and splitting alter table into two separate statements solved it. Try

ALTER TABLE dbo.ClientBeacon ADD
  datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT F_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
  SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59');

GO

ALTER TABLE dbo.ClientBeacon ADD
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO


Postgres Server access restricted to only one database from list of databases I am trying on the server side to restrict a user connecting on LAN to seeing and accessing only 1 database from the list of databases I have in my system.

How can I achieve this?

I have tried

host    db_name     all       (connecting_ip)/32          krb5

host    db_name     all       (connecting_ip)/32          ident

host    db_name     all       (connecting_ip)/32          crypt

host    db_name     all       (connecting_ip)/32          trust

These parameters have been changed in the pg_hba.conf file, and even after that, it still does not work.

Please help with a solution on how to fix this issue so that any user accessing a database in a server with a list of databases can see and access only the required database.

If you make changes to pg_hba.conf, you need to reload PostgreSQL.

But I think you'd be better off revoking the CONNECT privilege for that user. (Or possibly revoking ALL privileges.) Skeleton syntax is

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

Something like

REVOKE CONNECT ON DATABASE database-name FROM fishy_user;

In PostgreSQL, each role (user or group) can be granted permission to access different databases if that's what you want to do. See Database Roles


Does the 'reads' column in SQL Profiler mean the same as IOPS? I just executed a query that went pretty fast and the SQL Profiler said it used 30.000 reads.
Now I'm looking at this page: https://cloud.google.com/compute/docs/disks/performance and see that a standard persistent disk has 3000 reads IOPS.

Does that mean that my query would take 10 seconds when run there? Or does SQL reads and read IOPS mean a totally different thing?

One "read" in Profiler simply equates to reading one 8kb page.

This does not equate to IOPS, nor does it necessarily mean that data was read from disk. When performing a read, SQL Server will read data from disk only if that page does not already exist in the buffer cache. All data passes through the buffer cache before being used to satisfy your query.

Therefore, you can think of this number as not having anything to do with disk IO, but instead being a measure of the amount of data read from memory.


Asynchronous batch commits in Oracle We have an Oracle Database data warehouse that averages 120 commits per second (according to AWR).  The top wait activity is log file sync, which is to be expected... For now, overall response times are satisfactory, however, we are concerned with scalability, going forward.

Unfortunately, due to the nature of the vast majority of these transactions, the usual fix of "change your application to commit less often" isn't an option.

We are giving serious thought to changing these commits over to asynchronous batch commits via: COMMIT WRITE BATCH NOWAIT;

We are certainly aware that this would be breaking the "D" in ACID, namely that committed transactions could be lost in the event of an instance crash.  However, most of the aforementioned commits is logging information where if we lose a transaction here and there from an instance crash, the consequences of those lost transactions is very minimal.

I understand that with batch nowait commits, the commits are buffered in the log buffer (which we can tune via the log_buffer init parameter, if necessary).  However, i'm unable to find answers to the following questions:

1) Do the batch commits span multiple connections?  In other words, if we:

 open a connection
 do work
 commit write batch nowait;
 close the connection
 open another connection
 do work
 commit write batch nowait;
 close the connection
 rinse, repeat...

Are both of those commits batched or does Oracle flush the commits to disk for each connection when the connection is closed?

2) Is there anyway to find out how often the batched commits are written to disk and become "durable"?

3) Are the batched commits written to disk serialized?  In other words: if the application batch commits "A" work, then batch commits "B" work, is "A" work always written to disk before "B" work or is it possible for "B" work to be written to disk before "A" work is written to disk, instance crashes, "A" work is lost but "B" work is durable?

I went ahead and opened an Oracle SR, and this is what Oracle had to say:

  1) Do the batch commits span multiple connections?

No. Oracle will write data from Buffer to redologs once sufficient redo is generated by the session. Since your session is closed Oracle will write it immediately.

  2) Is there anyway to find out how often the batched commits are
  written to disk and become "durable"?

No. I have checked all oracle Documents and Metalink but that information is not available.  The most advanced documentation about Asynchronous Commits is available from this link.

  3) Are the batched commits written to disk serialized?

From what I understood from the documentation, they are serialized. Since transactions are written to Disk when "sufficient" redo is generated, I expect A will be written before B. Worst case they will be written at the same time. Please note that this not true with Multiple sessions like "A" from Session 1 and "B" from Session 2.


If user owns a database fixed role are they the admin of the role? I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role:

What permissions am I granted?
I am the admin of this role?

SQL Server knows four types of roles:

fixed server roles
fixed database roles
server roles
database roles

The fixed roles are system supplied and you should not change them. In fact trying to change the owner on a fixed server role aborts the connection with a level 20 error.

For the non-fixed roles the following applies:

The owner of a securable has the CONTROL privilege on that securable. That is true for any securable, including databases, schemata, procedures and roles.

In the case of a role there is no additional privilege that is implied. In particular, the role owner is not automatically a member. The role owner also does not have any permissions on the other role members.

What the CONTROL privilege does imply is the ability to add and remove members from the role, change the owner on the role and drop the role.

Keep in mind however that the role owner can add themselves as a member to that role and therefore can easily acquire the permissions granted to the role itself.

This all is true for both server roles and database roles.

I just wrote a blog post about this with a little more detail: http://sqlity.net/en/2169/role-owner/


MySQL creating a concat_WS delimiter function, one part not working I have a function that looks like this:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len INT UNSIGNED;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len <= 0 END REPEAT;
   return erg;
END;

I use the function like this:

WHEN CHARACTER_LENGTH(mac) = 12
    THEN
        CONCAT(CONCAT_UNTIL(UPPER(mac), ':', 2), ',')
END AS fullMac

So the field "mac" looks like this:

555FFFEE1001
555FFFEE1004
555FFFEE1005
555FFFEE1006
555FFFEE1007
555FFFEE1008
555FFFEE1009
555FFFEE1019

So I expect to see this:

55:FF:FE:E1:00:01,
55:FF:FE:E1:00:04,
55:FF:FE:E1:00:05,
55:FF:FE:E1:00:06,
55:FF:FE:E1:00:07,
55:FF:FE:E1:00:08,
55:FF:FE:E1:00:09,
55:FF:FE:E1:01:19,

But instead when I run the function I get this:

55:FF:FE:E1:00:1,
55:FF:FE:E1:00:4,
55:FF:FE:E1:00:5,
55:FF:FE:E1:00:6,
55:FF:FE:E1:00:7,
55:FF:FE:E1:00:8,
55:FF:FE:E1:00:9,
55:FF:FE:E1:01:9,

I've been messing around with all sorts of different fields, but haven't had success.  What is my logical error and can someone help me with the correct function call?

http://sqlfiddle.com/#!9/ba747/1

You have off-by-one error (in the function and in the expected output too)

555FFFEE1001 should expand to
55:5F:FF:EE:10:01 and not
55:FF:FE:E1:00:01

So all your computations were offset by one character - in the SUBSTRING inside the loop of your function you try to get "last" two characters of a mac, but there you put len as poswhich means you want to start with 12th character - but thats the last one so you cannot get two of them - this means that you loose the first character in the end.

You have to dynamicaly accout for how many characters you want to cut from the end when giving the pos:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len int unsigned;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len - repeatEvery + 1, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len <= 0 END REPEAT;
   return erg;
END//


Postgresql: Pattern match against array elements? Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array of elements, like:

SELECT
   lognum
   ,array_agg(flightnum) as flightnums
FROM logs
GROUP BY lognum;

Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this:

SELECT
   *
FROM (
      SELECT
         lognum
         ,array_agg(flightnum) as flightnums
      FROM logs
      GROUP BY
         lognum
     ) s1
WHERE
   '8%' like ANY(flightnums);

But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wild card is on the left of the operator. Of course, turning it around to be:

WHERE ANY(flightnum) like '8%'

gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)?

Please note that this is a simplified example demonstrating just the section I am having difficulty with.

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM (
                      SELECT lognum,
                      flightnums,
                      generate_subscripts(flightnums, 1) AS s
                      FROM (
                             SELECT lognum,
                                    array_agg(flightnum) AS flightnums
                                    FROM logs
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=#

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays


Business Continuity Plan -- How can I connect to DR Server if PROD site is completely Down I have two servers in one DataCenterA (PRODUCTION SQL SERVERS AOAGs) and one server in another DatacenterB (DR BCP AOAGs).What if DataCenterA is completely Down.Now how can I let the Application stay Online.My SQL Servers has AlwaysOn Availability Groups.Can I connect to application by changing the connection string as "SQL\Instancename" in web.config file or using BCP AOAGs Listener which we created for AlwaysOn AGs BCP Listener ?
Please Guide me on this.

If you have:

2 Datacenters
1 Server in Each DC
Using SQL Server AlwaysOn Availability Groups

Then the following happens:

The Primary Datacenter Goes Down, Completely

How do you continue business continuity?

Assuming that the Availability Replicas are both set to Synchronous...

Quorum will have most likely been lost - you'll need to force quorum on the server in the DC that is still up. This can be completed a variety of ways. Powershell: Start-ClusterNode -FixQuorum , services: net start clussvc /fq , etc.
Connect to SQL Server now that the cluster was forced quorum. Failover the Availability Group. This will bring the listener with it. Whether Synchronous or Asynchronous is set, this can be accomplished by executing ALTER VAILABILITY GROUP [Name] FORCE_FAILOVER_ALLOW_DATA_LOSS
Test connectivity.

Depending on the settings of the listener in the windows cluster, your clients may need to flush their dns cache (to pickup the new ip if they are legacy clients).

Once the first DC comes back online we'll want to make sure to reconfigure and re-evaluate the situation.

Resources that will help you:

https://msdn.microsoft.com/en-us/library/hh781257.aspx (Solutions guide for HA/DR)
https://msdn.microsoft.com/en-us/library/dn425173.aspx (MSIT case study for HA/DR)


SQL Server - storing ulong as primary key I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as varbinary(8).

Now, having a varbinary as a PK doesn't sound like a good idea. So what are the alternatives to store ulong values?

I'm assuming by ulong that you mean a 64-bit unsigned integer.

Your best option if you truly need to span the entire range of a ulong is to use NUMERIC(20,0) or larger.  If you need to enforce the range of values to those valid for your data type, use a check constraint to restrict the values to between 0 and 18,446,744,073,709,551,615.  Alternately, you could use VARBINARY(8), but your application would have to be base-aware.


Can I restore a .bmp file of full database backup to an Oracle 12c EE instance running on RDS? I've got an Oracle 12c EE database set up on an RDS box with no SSH access. I do, however, have SQL Developer and SQL PLUS tools.

Is there any way I can restore a full backup into this database using these tools (or any other remote management utility)?

After exchanging several comments I came to know that you have Data Pump file of full database. Anyway you can use the SQL Developer to import export database objects. Just follow the steps:

1) Launch the SQL Developer and go to View menu and click on DBA.

2) Then add database connection to perform Data Pump operations.

3) Click on Data Pump and select the appropriate job wizard.

4) Right click on Data Pump and Data Pump import wizard.

Useful link:
 Using DBA Features in SQL Develope


Do MySQL can do automatic EXPLAIN on every query? We only have two Junior DBAs, no Senior DBAs, no one to guide us. We need to review each and every query for almost 30 large projects.  Each query took between 5 minutes and two hours depending on the query. As a reviewer, it was a real drag on my time.
My question is, do MySQL can do automatic EXPLAIN on each query, without having to run EXPLAIN by hand and log the result of un-optimized queries? Or maybe by cronjob? If possible, how to do this?

Enable MySQL's Slow Query log.
Anything that takes more than the defined time will get written into that log and you can take a look at it.
Or, better still, throw it back at the Developer that wrote the query and get them to take a look at it and then then write a better query.
"Teach a man to fish ..." and all that ...
Databases are everybody's responsibility, not just the DBA's.


Calling a function inside of JSONB? Is is possible to call a function, like now() when inserting/updating a JSONB column in Postgres?

I was thinking something like the following where time would be set to the return value of now():
SELECT '{"value": 5, "time": now()}'::JSONB;

Sure, JSON doesn't support a special date type, (neither does JavaScript), JSONB only supports

string
number
boolean
null

So just use string interpolation, it'll get stored the same way:

SELECT (
  '{"value": 5, "time": "' || now() || '" }'
)::JSONB;

If you're trying to simply get NOW() from the server in one call, you can certainly do that using jsonb_set, or jsonb concatenation (||). Here are the docs

SELECT '{"value": 5}'::JSONB || jsonb_build_object('time', now());

In a third party library this may look like..

INSERT INTO table (json)
VALUES ( ? || jsonb_build_object('time', now()) );

It's still ugly. I wouldn't store a datatime in a JSONB, unless I had to:

It will be slow.
It will take more space.

As an aside, one of the alternatives to JSONB that Pg could have chosen is BSON which doesn't store dates as strings..


Authority create table on custom schema My default schema dbo.

I not give my users permission to create table.

User authority ; datareader,datawriter

CREATE TABLE dbo.newtable ...

  CREATE TABLE permission denied in database 'DB'

But, my users i want to be able to create a table in the other schema. How can I do that?

CREATE TABLE support.newtable ...

Thanks for your help.

1- Create DB Role [db_support]

2- GRANT ALTER ON SCHEMA::support TO [db_support] DENY ALTER ON SCHEMA::dbo TO [db_support]

3- GRANT CREATE TABLE TO [db_support]

4- CREATE TABLE sup.asd (asd int)

  Command(s) completed successfully


sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.) I used my personal laptop computer (Samsung RV520)
 OS: Windows 7 home premium (x64 bit)
 SQL Server 2012 Express version 11

I encountered an error when execut the code below.

error message:
  Msg 4861, Level 16, State 1, Line 47
Cannot bulk load because the file "bulk-campaign" could not be opened. Operating system error code 5(Access is denied.).
Msg 4861, Level 16, State 1, Line 47
Cannot bulk load because the file "bulk-campaign.Error.Txt" could not be opened. Operating system error code 5(Access is denied.).

 bulk insert project1.dbo.campaign
 from 'C:\Users\admin121\sql-server-2014-laotu\sql2012_class2_20141023\Project1_Bulk_Insert\textfiles\campaign.txt'
 with
 (
   firstrow = 2,
   maxerrors = 100,
   errorfile = 'bulk-campaign'
   );

this forum post offered suggestion, but I can't find the correct SQL Server name to give permission on the folder.

Please advise how can I find and add correct SQL Server Name to the the file folder.

I did some research online, it suggested to grant server role bulkadmin to the current login will resolve the issue

alter server role [bulkadmin] add member [boglenetwork\admin121];

After granting the role,I still receive the same error message.

my database name is project1, user associated with the database is dbo.
when i try to grant dbo additional user role e.g. acessadmin, system prompted me MS SQL server error: 15405

image1
image1
image1
image1
image1

Run SQL Server Configuration Manager and under SQL Server Services find your SQL Express instance and see what the Log On As is set to. Alternatively, you can right click the server name in SSMS, Facets, then scroll down to "Service Account" to see it.

If it's the default NT AUTHORITY\NETWORK SERVICE then grant access to a computer account called YOURDOMAIN\YOURCOMPUTERNAME$. You may also need to grant access to the person executing the command if they're logged on using AD.
Regarding your other user-level access issues, if a user is part of the db_owner role as shown in the screenshot they have the highest database-level access possible and no other database roles are needed. And if they're the database owner (separate to db_owner but pretty much the same privilege wise) then you can't grant additional database-level rights nor do you need to.


Should I use WHERE NOT EXISTS or LEFT JOIN for getting the ids of relationship table? I have users table:

CREATE TABLE `users` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `flname` varchar(60),
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB

And a relationship table:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `from` mediumint(8) unsigned NOT NULL,
 `to` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `from_2` (`from`,`to`),
 KEY `to` (`to`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

I want to fetch those users that is not in a specific user's circle (Like G+). It means that user has not yet added those people to his/her circle. I tried the below query but it was empty:

SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)

The below query returns 3 records:

SELECT uid,flname FROM users WHERE flname LIKE'%john%'

Now users in the name of john who has been added by uid=60 should not have be shown! The result of this query is empty. I can't figure that out, what I'm doing wrong?

The subquery should be correlated:

SELECT uid
FROM users
WHERE flname LIKE '%john%'
  AND NOT EXISTS
      ( SELECT *              --- doesn't matter what you put here for EXISTS subqueries
        FROM relationship
        WHERE `from` = 60
          AND `to` = users.uid        --- this line added
     ) ;

(unrelated to the issue)

Whoever told you that it's a good idea to use reserved words like to and from as column or table names, deserves a kick.

You can have the same results with a LEFT JOIN / IS NULL or a NOT IN query:

SELECT uid
FROM users
WHERE flname LIKE '%john%'
  AND uid NOT IN
      ( SELECT `to`
        FROM relationship
        WHERE `from` = 60
     ) ;


Login failed for user NT AUTHORITY\IUSR I have some developer colleagues in my office that are having a problem similar to this one:

db connection uses an anonymous logon instead of the domain user

the connection string (he is working on his local machine) is:

Data Source=localhost\Development;Integrated Security=SSPI;Database=COLA;Connection Reset=FALSE

and it is getting the error message below:

Login failed for user 'NT AUTHORITY\IUSR'.

where this login AUTHORITY\IUSR came from - we don't need it?

How can we make the Integrated Security=SSPI; get his local login instead of the AUTHORITY\IUSR?

Following the links in the comments provided by Biju jose and vonPryz

Understanding Built-In User and Group Accounts in IIS 7

Configure ASP.NET Impersonation Authentication (IIS 7)

we were following these steps:

To grant an anonymous account rights on the network by using IIS Manager:

Click Start, type INetMgr.exe, and then click Enter. If prompted, click Continue to elevate your permissions.

In the Connections section, click the + button next to the name of your computer.
In IIS Manager, double-click the site that you want to administer.
In the Features View, double-click Authentication.
Select Anonymous Authentication, and then click Edit in the Actions pane.
In the Edit Anonymous Authentication Credentials dialog box, click the Specific user option, and then click Set.
In the Set Credentials dialog box, input the user name and password desired, and then click OK.
But instead of doing the number 6 we just changed the setting to Application Pool Identity as you can see on the second picture below.

Changing the IIS settings to Application Pool Identity allowed the Windows Authentication to kick in and the active directory account was used to login into sql server.

That account had been previously configured to do so, therefore no more login problems for that user.

I would add that: Is “NT AUTHORITY\ANONYMOUS LOGON” login “mandatory”?


Writing SQL statement in sqlcmd to hide the milliseconds for column type of time I have a table called testtable that have a column named expire_time of type [time]

The problem is that when I type the following in sqlcmd:

select * from testtable

It will return the following:

===================================
|   ID   |  expire_time           |
===================================
|  1     | 17:18:19:0370000       |
|  2     | 20:11:19:0680000       |
===================================

How should I parse my SQL statement such that it will display the following?

===========================
| ID   |  expire_time     |
===========================
|  1   |   17:18:19       |
|  2   |   20:11:19       |
===========================

If you just need to remove the nano-seconds for display purposes you could convert the time value to a string using the below which will give you the format you desire:

SELECT ID, CONVERT(NVARCHAR, EXPIRE_TIME, 108) AS EXPIRE_TIME FROM TESTTABLE;

However, if you do decide to do this and then decide you need to perform calculations on the information, you would need to cast it back to a time value; which would give you the nano-seconds again but they would be set to zeros.

Alternatively, you could handle the formatting of the data in the object that is consuming it; for instance a SSRS report (which has the Format() function) or in an ASP.NET Web Form (which has a multitude of ways to handle this). It really depends on what your ultimate goal is that you want to achieve.

I hope this helps you.

Further Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx