Advantages/Disadvantages of using a multicolumn clustering index What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns?

I have come across a table that has a unique non clustered index as a GUID but the clustering index is applied on the combination of 9 columns. In total the table has 29 columns.

Can anyone help me understand what might be the benefits of having such a clustering key? As per Kimberly Tripp  || Index Debate by Kimberly Tripp the clustering Index should be narrow, unique and static, but this case is totally opposite to what Kimberly states:

the primary key is susceptible to changes so its not static
It is definitely not narrow as it spans multiple columns and other non clustering indexes refer to it for mapping. so it is definitely expensive for IAM/PFS.

A narrow, static, unique clustering key is better in situations like these:

The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
No other field or combination of fields is guaranteed to be unique
Queries against the table usually retrieve one row and use a surrogate key to do so (e.g. because the query comes from an application that has learned the surrogate key, not a user who is searching by natural key)

A multi-column clustering key is better in situations like these:

No other table references this one (e.g. a fact table in a data warehouse)
Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations


If you only want online operations you should consider setting tokudb_disable_slow_alter=ON as per https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_variables.html#tokudb_disable_slow_alter.


What is the use of restore headeronly when performing a backup? When I monitor my backups with the following query:

SELECT      command, percent_complete,
            'elapsed' = total_elapsed_time / 60000.0,
            'remaining' = estimated_completion_time / 60000.0
FROM        sys.dm_exec_requests
WHERE       command like 'BACKUP%'
or command like 'RESTORE%'

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

I was wondering what was the use for it and if its execution time could be reduced somehow. It appears to take longer than the actual backup.

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

This might be part of restore steps that are performed internally by sql server.

You can look into the restore phases using DBCC TRACEON(3604, 3605, 3004);. Use it only for educational purpose on a NON PROD server.

As you can see above that there are essentially 3 main phases of restore - data copy, undo and redo phase along with other sub-phases where in it opens and loads the backup set as well.

  It appears to take longer than the actual backup.

To improve restore time, you should

Enable Instant file initialization.
Use T-SQL and restore with REPLACE for an existing database instead of dropping and restoring.
Make sure you do proper maintenance of msdb.
Check for VLFs. The more VLFs, the longer restore time.

Refer to Babysitting a slow backup or restore by @AaronBertrand.


dimension table with "like" condition In an effort to scale some existing code, we are removing logic from CASE statements and putting it in dimension/lookup tables.  Thus instead of CASE WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' is now handled by a gender dimension.  (A very simplistic example)

But some of our CASE statements use LIKE operators, including comparison strings like 'ABC%DEF' and '%ABC%'.

I am looking for suggestions on the best way to handle this.  Our fact tables have over a billion rows, and growing, so efficiency is key.  We are Oracle 11gr2, if that is applicable.

Here are four approaches that each have advantages and disadvantages depending on what the current bottleneck is.

Normalize by adding a surrogate key column

--Sql Server syntax (My Oracle knowledge seems to have
--                   rusted due to lack of use)
CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code char(10) not null,
  amount decimal(10,2) not null
)

SELECT
    order_id,
    CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

Transforms into:

CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code_key int not null,
--  source_code char(10) not null,
  amount decimal(10,2) not null
)

CREATE TABLE source_codes (
  source_code_key int not null primary key,
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes ( advertising_campaign)
SELECT DISTINCT
   CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT
    order_id,
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes
            ON orders.source_code_key = source_codes.key

This approach is the cleanest from a pure dimensional modeling perspective. The logic in your CASE statements would be applied in an ETL process and the size on disk is reduced.  In Sql Server the CPU overhead is increased because a join is typically more cpu intensive then a CASE statement (I assume this is also true for Oracle).  If I/O is the bottleneck then this is a trade-off worth making and is the preferred approach.

Join on Business Key to Lookup table

CREATE TABLE orders (
  order_id int not null primary key,
  order_date DateTime not null,
  source_code char(10) not null,
  amount decimal(10,2) not null
)

CREATE TABLE source_codes (
  source_code char(10) not null primary key,
  Advertising_campaign varchar(20) not null )

INSERT INTO source_codes (source_code, advertising_campaign)
SELECT DISTINCT
    source_code,
    CASE
        WHEN source_code LIKE '%A11' THEN 'Acme 2011'
        WHEN source_code like '%A12' then 'Acme 2012'
    END advertising_campaign
FROM orders

SELECT
    order_id,
    source_codes.advertising_campaign
FROM orders INNER JOIN source_codes
            ON orders.source_code = source_codes.source_code

This approach would require one row in source_codes for each distinct source_code.  Size on disk increases due to source_codes table.  Disk IO remains the same or increases depending on the ability of the source_codes table to fit in memory.  This approach makes sense only in the case where the queries are CPU bound due to logic in the CASE statement that is horrendously complex.

Keep Existing Schema

If the bottleneck is the CPU and the cost of computing the CASE statements is less than the cost of joining to a another table then keeping your existing queries may be the best bet.

Keep Existing Schema + Move CASE logic into client application/reporting tool

This approach makes sense if the bottleneck is network bandwidth to the client or the CPU load is too high with either the JOIN or the CASE statements.  By moving the complex CASE logic off the server you can distribute CPU load.  This would be the most intrusive solution and should be considered last resort.


Why would an application use one central metadata table to hold metadata for all other tables? I've started working with an application that has an usual design:
In addition to the tables that contain the actual data, there is one metadata table that contains all the information about the other tables: their names, their columns, which columns link them to which other tables, and so on... all in a single table.
The application mostly queries this table to figure out how to query for other entities, how to query or update related tables, how to insert, delete.
The result of this is that the application's data access code is quite generic. Most of the data access functions just take a few parameters which represent the names of things (concrete tables and columns) to look up in the metadata table.
The main drawback, as far as I can tell is that when the schema changes, the concrete structures and the content of the metadata table need to be kept in sync or the application could break, although it seems that the metadata table is generated from a custom-written script that also creates/alters the other tables. Maintenance of that script and the original data model is another concern.
I also have to consider that this data access component may either replaced or preserved for new applications. If it were to be replaced, it would probably be with something more "normal" like hibernate or some other framework. I'm quite torn on which way to go since the current code, while a little odd-looking, seems to work fine.
Are there any hidden advantages/disadvantages that I might have missed that might help me make a decision?
Actually, what is this design pattern even called, in case I want to look up other implementations of it? I don't think I've ever seen anything like it before, I don't really know what to call it or how to research it.

I believe this is called a "Big Ball of Mud".  The not so hidden disadvantage is the system appears to have done away with normalization of the data model for the metadata.  This is likely lead likely lead to inconsistencies over time.  However, it is possible that the script you described, just rebuilds it from the database's metadata.

All the databases I have worked with, have a nice normalized data structure for the meta data in place of the single metadata table you are describing.  This involves the appropriate tables for the appropriate metadata.  Most cache the query and resulting optimized execution plan for recently used queries.

If the code is figuring out the required to query on the fly, by querying the metadata table, it is likely slower than it needs to be.

A query like select a,b,c from T, where d = ? can be supported by a single query with a parameter. This also prevents SQL injection when someone enters a value like 1 or 1 == 1.  On a desktop application it may not be that serious, but many companies have learned how serious it can be.

There is a reason many developers use hibernate, Spring, and other data access layers.  Some of these can build the database required to support the application, and also work with databases that have already been built.


is there some way to give me the order in which inserts should be made so all FK constraint are met? I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc.

Is there some good way (I am thinking some tool, script...) I could use that will look at the metadata of the DDL and tell me in which order should I insert data in the tables so all FK are met?

I obviously want to avoid trial and error...
The output of such script/tool would be for example:
1. insert into country/continent tables
2. insert person
3. insert ocupation/cv

If you are sure enough that all constraints would be met..You can think off turning off the foreign key checks and after making sure that all constraints are ok, you can turn on foreign key checks.
You can write a script to verify that if all constraints are ok.

use  following to turn off foreign key checks

  set foreign_key_checks=0

use following to turn on checks again after necessary verification

  set foreign_key_checks=1

Hope it helps


Does mysql stop a search automatically after finding 1 record when searching primary key with no limit? Having trouble finding this answer. If I search for a record by a primary key id does mysql automatically stop searching after it finds 1 record if there was no "limit" in the query?
Say a table has 1 billion records and I search for id = 1 and id is the primary auto incrementing key in the table. SELECT * FROM logs WHERE id IN(1) Will that query stop after it finds one or search the whole billion records even after finding a match?
I am asking because a model in a framework I am using that auto-builds db queries builds them like this including using "IN(id)" instead of id = # which is why the example shows it that way.
I want to use the database model a cleanly as possible but if it has a performance issue because of how it builds the query when dealing with 1 record I have to add more code to manually specify a limit. The model also builds updates the same way I assume mysql will handle the select and update on the primary key the same way (stopping after 1 found/updated or not).
Thanks.

It depends.
First, let me put aside WHERE id=1 versus WHERE id IN (1).  The Optimizer turns the IN version into the = version.  Hence, there is no performance difference when there is only one item in the in-list.  For multiple items, the answer gets more complicated (and not covered here).
Does id have an index?  Let me address 3 cases:

No index.  Then it may have to read each row of the table, checking each for id=1.  Sloooow.
A non-unique index.  And not LIMIT.  Now MySQL does not know whether there are no rows, one row, or thousands.  And WHERE id=1 simply says "get all of them".  So the underlying code will find the first one in the index, then read until id > 1.  So if there are N matching rows, it will "read" N+1 rows (but deliver only the N that you asked for).
A UNIQUE index.  Note: The PRIMARY KEY is UNIQUE.  [This probably your only question.]  First, it will have to dig into the index to see if there is a row with id=1.  But after that, it is smart enough to know to stop after 1 row.

In my experience, I think the difference between stopping at 1 and doing 2 is insignificant.  There are so many other things going on in a query, that I would be hard-pressed to measure the timing difference.
But I am not through lecturing.  What about ORDER BY x LIMIT 1?  Now the efficiency depends on other things.  Most important is whether both the WHERE and the ORDER BY can use the same index.  If not, the data is gathered, sorted, then finally 1 row is pealed off.  Corollary:  For a million-row table, this may mean that adding the LIMIT does not help performance.
To counteract that example, you are unlikely to say WHERE id=1 ORDER BY x -- why bother sorting a single row.


Adding indexes to production MySQL database without downtime? I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of course this locks the database and makes our application unavailable.

We have thought of creating a secondary database with the updated indexes and then migrating the database data over. This would involve some downtime.

Would there be a better way about doing this? That doesn't involve downtime?

Thank you in advance for your help

If you wanted to add indexes to a live table, there are two major approaches:
APPROACH #1 : pt-online-schema-change (Preferred Choice)
This tool is spectacular. It will create a temp table for you and populate it.
In oversimplified terms, it does this for mydb.mytable:
CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
RENAME mydb.mytable TO mydb.mytable_old,mydb.mytable_new TO mydb.mytable;
DROP TABLE mydb.mytable_old;

However, pt-online-schema-change goes way beyond this.
What if rows already copied to the temp table are updated or deleted ? Well,
pt-online-schema-change adds triggers to the source table to handle those post-INSERT changes for you. When the copy to the temp table is complete, the triggers are removed just before the switch.
There also are options to throttle (such as --max-load and --critical-load) or halt (such as --pause-file) the copy to the temp table if the live system has a high load or if the server has slave that fall behind a certain number of seconds (such as --max-lag).
You also have options to test the syntax only (--dry-run) or actually run the change (--execute).
There are options to set variables like sql_log_bin to 0 and check for foreign key constraints.
The limitations to this tool ?

Make sure there is enough room for the temp table before the switch happens. For example, if the source table is 200G, I would make sure there is at least 250G free on disk.
Momentary outage in seconds to switch the temp table and the old table.

APPROACH #2 : Online DDL
MySQL is also capable of performing DDL online using native SQL.
See my old posts

Aug 01, 2017 : Alter large table without downtime
Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity

Unfortunately, Online DDL does not have options to throttle or halt the operation.
EPILOGUE
These days I use pt-online-schema-change because I can control how fast or slow the process takes. I can even stop and resume it over a series of day. One time I, along with two other DBAs, performed pt-online-schema-change on a 30GB that had super high reads and writes. The Dev Team we worked with asked us to pause the copy to the temp table during the day and resume the copy overnight. We did this in a span of three days. Everything was successful and applications never suffered.


Very simple read-only SELECT not run in parallel in postrgresql I am trying to do a simple SELECT on a large table, but it is not being run in parallel:

my_db=> explain (analyze, buffers) select value from my_table;
Seq Scan on my_table (cost=0.00..94433357.34 rows=5441810434 width=4) (actual time=0.029..1519364.787 rows=4897721407 loops=1)
   Buffers: shared hit=1126394 read=38888859 dirtied=1926396 written=284
 Planning time: 2.277 ms
 Execution time: 2468388.489 ms

If I do a max() operation it runs in parallel:

 my_db=> explain (analyze, buffers) select max(value) from my_table;
 Finalize Aggregate  (cost=48552456.41..48552456.42 rows=1 width=4) (actual time=1347796.993..1347796.993 rows=1 loops=1)
   Buffers: shared hit=1173785 read=38870105 dirtied=30813
   ->  Gather  (cost=48552455.58..48552456.39 rows=8 width=4) (actual time=1347796.953..1347796.969 rows=9 loops=1)
         Workers Planned: 8
         Workers Launched: 8
         Buffers: shared hit=1173785 read=38870105 dirtied=30813
         ->  Partial Aggregate  (cost=48551455.58..48551455.59 rows=1 width=4) (actual time=1347790.862..1347790.862 rows=1 loops=9)
               Buffers: shared hit=1172674 read=38870104 dirtied=30813
               ->  Parallel Seq Scan on my_table (cost=0.00..46849720.06 rows=680694206 width=4) (actual time=0.213..1048428.195 rows=544206420 loops=9)
                     Buffers: shared hit=1172674 read=38870104 dirtied=30813
 Planning time: 0.761 ms
 Execution time: 1347985.127 ms

Why is the SELECT not done in parallel?

The table has several billion rows. The DBA has done some indexing - I don't know what that really means, but I have read that index scans are not parallel in PostgreSQL 9.6.8, but it is doing a sequential scan anyway.

I am using PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

The table has structure:

my_db=> \d my_table
 id       | bigint                         | not null default nextval('my_table_id_seq'::regclass)
 datetime | timestamp(0) without time zone | not null
 value    | real                           | not null
 my_id    | smallint                       | not null
 my_flag  | boolean                        | not null

my_db=> \d my_table_id_seq
 sequence_name | name    | my_table_id_seq
 last_value    | bigint  | 4971842011
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 22
 is_cycled     | boolean | f
 is_called     | boolean | t

The parallel SELECT MAX(value) still takes quite a while:

my_db=> \timing on
Timing is on.
my_db=> select max(value) from my_table;
 9333

Time: 1342377.208 ms

The query leader is the only process that communicates with the front end.  All the SELECTed data for the non-aggregated version of the query has to be marshalled up from the parallel workers to the query leader.  That is expensive, so using parallel query here would almost certainly be counter-productive.  You might be able to get one anyway by setting parallel_tuple_cost and parallel_setup_cost to zero.

When doing the count(*) aggregate, each parallel worker only needs to send up one piece of information, a partial count, to the leader.  This is more obviously worthwhile.


Can't drop an empty database in mariadb As the title says, I cannot drop an empty database. In PHPMyAdmin, the request says that it times out and from running it in bash it just goes on until I cancel it with mysqladmin -u root -p kill <pid>. Running use panel; then show tables; just displays Empty set (0.001 sec) What can I do to fix this? Thank you in advance.

If I can remember correctly, I fixed it by setting the innodb_force_recovorey level or I just purged the mariadb package and restored from .sql files


SQL Server memory benchmarking - how to fill up memory? Recently I was asked by my VMWare colleague how to cause a (non-production and bare install) SQL Server to use up the memory of a machine.

The solution I found in this forum post does not cause the SQL Server process to use up memory but instead SSMS due to it's output.

As SSMS is only 32 bit, it crashes after about 4 GB, so this is not useful for a  server with 600 GB memory...

The solution I came up with is a slight extension of the forum post I mentioned before (first make sure that your max server memory is set appropriately!):

DECLARE @sometable table (id uniqueidentifier)

insert into @sometable
SELECT NEWID()
FROM sys.columns A
CROSS JOIN sys.columns B
CROSS JOIN sys.columns C

Indeed you can see that SQL server starts to consume memory when running this query, and of course this does not cause SSMS to fail as there is only the usual "rows affected" output


Installing MySQL in Solaris I am trying to install MySQL 5.5 on Solaris 10.
I executed the code useradd -r -g mysql mysql where a group called mysql is already created using groupadd command.

I get the following error :
UX: useradd: ERROR: invalid syntax.

Is -r option not availablr on Solaris ?
Am I missing something.
I am newbie to Soalris.


Postgres tsrange index for small sliver of time I have a table (events) that has two timestampz columns (start_time and end_time). My program queries the table every minute for all records that are happening right now:
select * from events
where tstzrange(start_time, end_time, '[)') && tstzrange(:start, :end, '[)')
and status = 'PUBLISHED'

Where the bound variables for start and end are sent by the program tied to the current minute (truncated to the minute) and the next minute (truncated to the minute). Ex: 12:00:00.000000 and 12:00:01.000000.
The index for this, as per this excellent answer, is as follows:
create index concurrently
    events_activity
    on events USING gist
    (tstzrange(start_time, end_time, '[)'))
    where status = 'PUBLISHED';

I believe this should serve my purpose well -- the index has taken 90+ minutes to create so far, so I can't be certain. But assuming it does, I have one concern: it seems really wasteful. I base this on the assumption that it is keeping all records in the index for all of eternity, but my use case only requires the time range query for a small window of time.
So my question is: given that I only query for ranges of time that are really close to "now", is there some way to keep the index much smaller to reflect my narrow needs? Is there some where to constrain the index to just reflect records that I'm expecting to query for? Would adding the following to the end of the index automatically keep it small and constrained to my target window?
tstzrange(start_time, end_time, '[)')
  && tstzrange(now() - interval '30 minutes', now() + interval '30 minutes', '[)')

UPDATE: No, that won't work... functions in index predicate must be marked IMMUTABLE.
Thank you!

I think that there is no way to do this - making the indexed rows dependent on the current time would mean that the index has to change all the time, and that is something PostgreSQL doesn't do.
You could of course introduce a condition:
CREATE INDEX ON events USING gist
    (tstzrange(start_time, end_time, '[)'))
WHERE status = 'PUBLISHED'
  AND start_time > '2021-01-01 00:00:00';

and drop and re-create the index each year (or month).
But then your queries would have to contain the same condition that changes yearly, which does not seem practical.


What is the difference between ON SCHEMA and ON DATABASE triggers? We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference between them? Is it some legacy stuff? ON DATABASE should be used when using AFTER STARTUP or AFTER STARTUP because it's definitely related only to database but the same stuff that is done using ON SCHEMA might be done using ON DATABASE, so what's the difference? I can't find references in Oracle docs about that.

A trigger on schema (with no schema specified) doesn't fire for all schemas. It only fires when the triggering action is run by the user who owns that trigger.

So they are quite different, and are not interchangeable.

Here's an example that sets up a logging table, and three create triggers: on database, and on schema for users foo and bar:

Connected. -- as mat
SQL> create table mat.log (dt timestamp, who varchar(3),
  2      cur varchar(10), own varchar(42), obj varchar(42));
Table created.

SQL> create or replace trigger db_trig
  2  after create on database
  3  begin
  4    insert into mat.log values (systimestamp, 'db', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect foo/foo
Connected.
SQL> create or replace trigger foo_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'foo', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /
Trigger created.

SQL> connect bar/bar
Connected.
SQL> create or replace trigger bar_trig
  2  after create on schema
  3  begin
  4    insert into mat.log values (systimestamp, 'bar', user,
  5      ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME);
  6  end;
  7  /

Trigger created.

Now let's create a table in foo's schema, as foo:

SQL> connect foo/foo
Connected.
SQL> create table foo.foo_stuff (id number);
Table created.

And let's create a table in foo's schema, as bar:

SQL> connect bar/bar
Connected.
SQL> create table foo.bar_stuff (id number);
Table created.

Here's what we've logged:

SQL> select * from mat.log order by dt;

DT                             WHO CUR        OWN        OBJ
------------------------------ --- ---------- ---------- ---------------
25-NOV-12 07.52.03.797794 PM   db  FOO        FOO    FOO_TRIG
25-NOV-12 07.52.03.828670 PM   db  BAR        BAR    BAR_TRIG
25-NOV-12 07.52.03.865334 PM   foo FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.865579 PM   db  FOO        FOO    FOO_STUFF
25-NOV-12 07.52.03.894672 PM   bar BAR        FOO    BAR_STUFF
25-NOV-12 07.52.03.894911 PM   db  BAR        FOO    BAR_STUFF

6 rows selected.

So:

the two create trigger statements were logged by the "global" after create on database trigger. That trigger also logged everything else.
foo's after create on schema logged the table creation that was done by foo
bar's trigger logged the table creation that was run by bar himself, even though bar created a table in foo's schema.


Pages Input per Second Spiking Daily I'm still learning a lot about SQL Server and pretty green, but doing the best I can as an accidental DBA.

We have recently purchased a SQL monitoring software package, and after adjusting the threshold on alerting so I'm not bombarded with false positive warnings, I noticed that the pages input per second spikes every day at exactly the same time:  6:20:30 PM.  The only variation in time over the last week was just last night, when it alerted again at 6:19:39 PM.... very very close to the same time!

I have checked the server job schedules and it appears there are no jobs scheduled around that time.  The earliest job we have kicking off is at 9pm daily, so that's not it.

Can anyone tell me what I can look at (or for) that will help me find what is causing this to happen at exactly the same time every day?

Thanks much!

It is OS performance counter and refers to reads of page file. Generally it points to low memory condition on server. With your provided info no specification can be made about the issue. You need to monitor the server processes at that time. Also have a look at other applications consumption if exist on same server along with SQL Server.


Update records that match like regex from another table I have two tables - ProductCategories and Products:

ProductCategories
---
productid    categoryid
1
2
3
4

Products
--------
productid    productname
1            Apple
2            Apricot
3            Tomato
4            Watermelon

and I would like to populate categoryid in table ProductCategories in such manner that all products having names starting with "Ap" are set to categoryid 1, starting with "To" to category 2 and starting with "Wa" to category 3 resulting with table ProductCategories populated as follows

ProductCategories
---
productid    categoryid
1            1
2            1
3            2
4            3

I have tried this

UPDATE ProductCategories
SET categoryid = '1'
WHERE product_id IN
      (SELECT product_id
       FROM Products
       WHERE productname LIKE 'Ap%')

but query does nothing. I would have run modified query few times for other options ("To", "Wa", etc) to completely populate ProductCategories table.

Please try, if you could, this approach:

UPDATE
    ProductCategories,
    Products
SET
    ProductCategories.categoryid = CASE LEFT(UCASE(LTRIM(Products.productname)), 2)
                                      WHEN 'AP' THEN 1
                                      WHEN 'TO' THEN 2
                                      WHEN 'WA' THEN 3
                                      ELSE ProductCategories.categoryid
                                   END
    WHERE
        ProductCategories.product_id = Products.product_id

I assumed that product ID column is named product_id


Postgres - Create table with foreign key constraint too slow I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to "Account" table.

My CREATE TABLE statement looks like this:

CREATE TABLE "NewTable"
   (
       "column1" VARCHAR(100) NOT NULL,
       "column2" INTEGER NOT NULL,
       PRIMARY KEY("column1")
       CONSTRAINT "SomeFK" FOREIGN KEY ("column2") REFERENCES "Account" ("ID")
    )

The above statement executed for more than 25 minutes and didn't complete. We killed the transaction.

When we remove the foreign key constraint, it executes immediately.

Can someone let us know what is the problem? We initially thought it was locking the "Account" table for some reason, but when we looked at "pg_locks", we did not find any entry for "Account" - only an exclusive lock for "NewTable".

Your query got locked waiting for something - my bet is that it was waiting for some other transaction to finish.

When you're doing the create table, just issue (in another psql session):

select * from pg_locks where pid = XXX and not granted;

where xxx is pid of backend doing the create table.

This will show you what is the lock that the create table is waiting for.


Using MySQL active-passive with RBR DDL without breaking replication? new to RBR and am wondering how to remove a column in the middle of a table in an active-passive setup without breaking replication? Pre-RBR I would just have run the alter on one side and then failover writes. Running Percona 5.6 server in a scaled out environment.

ALTER will go through RBR, so I don't see it as being much different.

As long as

you don't do SELECT *, the fetches should remain OK.
no queries touch the column you are removing any time near the action, write queries should not be an issue.

If you are trying to avoid the table being blocked a long time, I see some use for the 'failover' you mentioned (but did not adequately detail).

What version do you have?  5.6 rebuilds "in place" and permits concurrent DML.  So, the 'failover' may be overkill.


Is there any relation between BSON storage for mongoDB and MySQL storage? I am working on an application where I need to make analysis that what will be the storage size of BSON structure if same data is stored which is stored in MySQL. Basically I am comparing MongoDB and MySQL storage. Is there any relation between them? or some formula which gives size of BSON storage?

In each database, there is no one answer to what size something is when it is stored.  In MongoDB there are several factors to take into account, for example:

What types are being stored (BSON having more types than JSON, and with each getting different allocations)
Should you include the (16 byte) record header?
Is the collection you are storing the data in using powerOf2Sizes?
Are you using the default storage engine? (pluggable engines were just announced at MongoDB World 2014 and Toku has provided a fork with very different storage characteristics for some time)
Do you need to include index size in the storage calculation?
Does running a compact/repair alter the storage characteristics?

That note about storage engines is a good one.  I am not a MySQL expert, but I know there are multiple options there (MyISAM, ARCHIVE, MERGE and others) that will again have very different storage characteristics just within MySQL itself.

There's no easy answer on this one I'm afraid - you are going to have to tightly control the data being stored, look for equivalent/fair types across the two databases to store.  Then you will need to familiarize yourself with the intricacies of each in terms of how to measure storage and how different actions might impact that storage.


postgresql crash in master node with streaming replication We have a master/slave postgresql cluster with streaming replication and pgpool.
Versions in both postgresql servers are.
postgres: 9.4
OS: Debian GNU/Linux 8 (jessie)

PGPOOL server:
version pgpool: pgpool-II version 3.3.3
os: Red Hat Enterprise Linux Server release 6.1
Problem:
We suffer a crash down of the postgresql service at master node with subsequent failover done by pgpool
Could not find the reason of this behavior in master node. We want to know if someone coult tell us, based on the recorded log messages, what could have caused this crash.
Following, some relevant lines from the postgresql and linux log.
postgresql.log
2020-12-20 21:06:04 UYT [25922]: [4-1] user=,db=,app=,client= LOG:  server process (PID 19303) was terminated by signal 11: Segmentation fault
2020-12-20 21:06:04 UYT [25922]: [5-1] user=,db=,app=,client= DETAIL:  Failed process was running: COPY public.act_hi_varinst (id_, proc_def_key_, proc_def_id_, proc_inst_id_, execution_id_, act_inst_id_, case_def_key_, case_def_id_, case_inst_id_, case_execution_id_, task_id_, name_, var_type_, rev_, bytearray_id_, double_, long_, text_, text2_, tenant_id_, state_, create_time_, root_proc_inst_id_, removal_time_) TO stdout;
2020-12-20 21:06:04 UYT [25922]: [6-1] user=,db=,app=,client= LOG:  terminating any other active server processes
2020-12-20 21:06:04 UYT [1910]: [3-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= WARNING:  terminating connection because of crash of another server process
2020-12-20 21:06:04 UYT [1910]: [4-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-12-20 21:06:04 UYT [1910]: [5-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-12-20 21:06:04 UYT [27108]: [3-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= WARNING:  terminating connection because of crash of another server process
2020-12-20 21:06:04 UYT [27108]: [4-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

Error found at /var/log/messages

Dec 20 21:06:04 server-name kernel: [20278231.774013] postgres[19303]: segfault at 7f7740354704 ip 00007f772c8ac700 sp 00007ffdac7ff4e0 error 4 in postgres[7f772c813000+5a1000]

Thank you all.

If that crash is repeatable, you probably have data corruption in your table. If the crash is not repeatable, the cause could be faulty memory.
At any rate, the odds are good that your hardware has problems. That would not surprise me, if you are as negligent with hardware maintenance as you are with software maintenance (9.4 is out of support).
Use reliable server grade hardware and keep your software versions reasonably recent.


Running a stored proc with execute as I'm using SQL Server 2016

I have a basic stored procedure using execute as that I would like to run, but it comes up with this error when I try (names have been anonymised):

  Msg 15404, Level 16, State 19, Procedure usp_executeastest, Line 0
  [Batch Start Line 2]

  Could not obtain information about Windows NT group/user
  'MyCompany\A.Smith', error code 0x54b.

Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_executeastest]
WITH EXECUTE AS 'dbo'
AS
BEGIN

SELECT * FROM [dbo].[TestTable1]

END

Now the database user 'dbo' is mapped to login 'MyCompany\A.Smith' so this explains why the error message mentions MyCompany\A.Smith.

I have tried running this proc by logging in as various users, including logging in as 'MyCompany\A.Smith' who is mapped to the dbo user, and the same error message comes up every time.

So why can't any user run this stored proc?

If you put dbo, it will look at the dbowner of the database, in your case (I guess) 'MyCompany\A.Smith'

Be sure the user you want to use in the execute as clause is part of sys.database_principals or sys.server_principals.

If not, you can fix this issue changing the owner of the database you use.

First, check the current owner :

select suser_sname(owner_sid)
from sys.databases
where name = 'MyDatabase'

If it's 'MyCompany\A.Smith' your procedure will not run.

So, change the owner, for instance :

sp_changedbowner 'sa'

And it will work.


Custom location for postgis extension .control file I am trying to install the postgis extension on my database, which resides on a web hosting service. The problem is that the control files seem to have to be found in /package/host/localhost/postgresq-9.3.2-1. I am using a hosting company where all we can access is what is in our respective home/ folders. The Postgres executables are found in the root directories which we cannot access.

I have successfully installed the PostGIS binaries, but now when I try to create the extension with CREATE EXTENSION postgis, I get the error ERROR: could not open extension control file /package/host/localhost/postgresql-9.3.2-1/share/extension/postgis.control. I cannot write anything to this directory since it is just intended to store the executables for anyone to access via some scripts the hosting provider as set up.

Can I have this .control file be somewhere else instead? Or am I just out of luck entirely?

This is not 100% proven, but the following pass of the documentation suggests you just cannot have your control files in other locations:

  extension_name

  The name of the extension to be installed. PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

I can imagine one can work it around on systems supporting symbolic links - but in your case this does not help as well, as you cannot create the symlink at the right location either.


Find the most recent row where no exact match on date is found I am trying to write a query that tries to extract the value of the exchange rate where:

Query date = the exchange date; and
Query currency = the specified currency

If it does not contain a matching value like on 5-Sep-2015, it should extract the value from the last date that contains a value. In this case, the value should be taken from 4-Sep-2015:

 Create Table ExchRate
 (  Exch_ID int,
    Currency varchar(3),
    Exch_value numeric(14,7),
    Exch_date datetime)

Insert INTO ExchRate
Values (1, 'SGD', 1.4173000, '2015-09-04 00:00:00'),
       (2, 'SGD', 1.4240000, '2015-09-07 00:00:00'),
       (3, 'SGD', 1.4291500, '2015-09-08 00:00:00'),
       (4, 'EUR', 0.8984700, '2015-09-04 00:00:00'),
       (5, 'EUR', 0.8955700, '2015-09-07 00:00:00'),
       (6, 'EUR', 0.8957000, '2015-09-08 00:00:00')

The result should be:

Currency   Exch_Date   Exch_Value
SGD        2015-09-05  1.4173000

Secondly, what if I am joining from another table to calculate the total cost in USD? What kind of SQL statement should I be using? In this SQLFiddle example, it should have exchange rate value for every PO with different PO dates.

How about the following query?  This query will take the exchange on the query date if it exists.  If not, it will find the most recent exchange date prior to the query date and use that date's exchange rate.

DECLARE @Query_currency VARCHAR(3) = 'SGD',
        @Query_date DATETIME = '2015-09-05 00:00:00'
SELECT TOP(1) Currency, Exch_date AS Observed_exch_date,
    @Query_date AS Exch_date, Exch_value
FROM ExchRate
-- Find the exchange rate on the query date for this currency
-- If there isn't one, find the most recent exchange rate
WHERE Currency = @Query_currency
    AND Exch_date <= @Query_date
ORDER BY Observed_exch_date DESC

Looking up exchange rates in batch

If you need to find the exchange rate for multiple rows at a time, you can use CROSS APPLY to find the most recent exchange rate for each row. If you make sure that you have an appropriate index on (Currency, Exch_date) that includes the Exch_value column, looking up the exchange rate for each row will perform a single seek with no additional sort needed.  You can see a full demonstration in this SQL Fiddle.

SELECT p.PO_Num, p.PO_Cur, p.PO_Date, e.Exch_date, e.Exch_value
FROM PO p
CROSS APPLY (
    -- For each PO, seek to the current or most recent exchange rate
    SELECT TOP 1 ex.Exch_date, ex.Exch_value
    FROM ExchRate ex
    WHERE ex.Currency = p.PO_Cur
      AND ex.Exch_date <= p.PO_Date
    ORDER BY ex.Exch_date DESC
) e


What procedure can be used to change system statistics in a production environment System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first implemented.

There isn't a need to change it regularly, however it is recommended to regather the system statistics when a big change occur, such as change of hardware.

When such a need occur, what procedure do you use to refresh it and ensure the results are beneficial?

For major database statistics regathers, it is best to forget the old stats and essentially regather statistics freshly on the whole system. I do not advise doing this frequently because it is a significant workload undertaking on your system and there are better methods of controlling statistics over time.

This would likely require a script to go out and gather all the tables that exist in your database and put them into a queue for the DB to analyze. After the DB has completed reanalysis you need to publish the changes for them to take effect.

Once you've forced the system to stage the new statistics, this is how to flush the old statistics out and push the staged statistics into production.

exec dbms_stats.flush_database_monitoring_info;

exec dbms_stats.import_database_stats('< table_name >','< table_value >','< user >',force=>true);

exec dbms_stats.export_schema_stats ('< user >','< table >','ACTUAL','< user >');

exec sys.dbms_stats.delete_system_stats();

exec sys.dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD'); #This is subjective based upon the workload the system was on during analysis

exec dbms_stats.gather_dictionary_stats();

exec dbms_stats.gather_fixed_objects_stats();

EDIT:
I cannot yet respond to the comment asking if there are ever any adverse effects from gathering statistics.

Statistics regathers cannot promise that they won't make one or many individual pieces of the system perform poorly. Sometimes SQL scripts will determine that there is a better path based on your new statistics that is actually a worse path in reality. Sometimes it'll make a quiet script that runs millions of times a day with no issue turn into a problematic script that halts the DB (until you go tell it to take the old path). It also cannot promise general performance increases on your database. It can only promise that the statistics on your tables will become fresh.

The other risk is that statistics gathering and analysis is an intensive process which can bog down the system if done under production stress. It is best to do a full statistics regather when you're guaranteed that the majority of users won't be on the system (overnight, during client downtimes, etc)

Finally, if you are gathering statistics while a user is making massive changes then that can cause some unruly performance changes. It's another reason to gather stats while minimal users are on the system or when no atypical changes are being made.


Oracle: guaranteeing unmodified database state with queries? If the first word of a SQL statement is either WITH or SELECT, is the statement guaranteed not to change the state of the database?  i.e. is this loop "safe"?

read SQL string
if string begins with SELECT or WITH
    process string
else
    error, do not execute

If the answer is no, what string(s) would allow data to be modified?

(note that this is a very specific question and not a request for general advice regarding database security.)

It's possible for a SELECT statement to modify the database, for example:

select mysequence.nextval increments a sequence
select myfunction executes a PL/SQL function which could have any sort of side effects

The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as

ORA-14551: cannot perform a DML operation inside a query
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

are returned:

SQL> set serveroutput on
SQL> create table t1 (x number);

Table created.

SQL> insert into t1 values (42);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

         X
----------
        42

SQL> create or replace function f1 return varchar2 is
   pragma autonomous_transaction;
begin
   execute immediate 'truncate table t1';
   return 'Table t1 truncated';
end f1;
/
  2    3    4    5    6    7
Function created.

SQL> select f1 from dual;

F1
--------------------------------------------------------------------------------
Table t1 truncated

SQL> select * from t1;

no rows selected


Partial copy of huge database I have started working in a project which has a big database, around 300GB. For security reasons I can not access database from my local web app. So I need to copy last 100,000 from each table.

To copy from a table to another, I know I can do:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

But how I can handle connecting to the other database?

One idea I have, it is to create a table, same structure, and use query above to move records and then dump those tables.

Is there a better way?

mysqldump -h source_server... --order-by-primary --where='id > ...' src_db, tbl  |
    mysql -h dest_server ...

But that assumes

id is the PRIMARY KEY
You can get the id 100K (or so) rows from the end:  SELECT id FROM tbl ORDER BY id DESC LIMIT 100000, 1
You can access both servers from wherever you run the pipeline.

Since you need 2 connections, there is no 'simple' way to do it from the mysql commandline tool or from client code (PHP, Java, etc).  Copying one row at a time would quite slow.  Employing LOAD DATA will be no better than mysqldump + mysql.


Please review my logical data model I have to design and implement a database on the study course. I want to make sure that my scheme is correct. I've created a model company and a system which needs a database.

A little background about the database: The database is designed for the company who test applications for external clients. Let's say that the company offers performing acceptance and compatibility testing services. The workflow of the company:

Seller (Client) requests for testing his application
The ticket is created
Ticket contains information about the application and what should be tested
Test Manager decides which Tester should test the application (ticket)
Tester reports defects to the application
When testing is done, tester set a result

Other businesses requirements:

Seller can requests for testing the application more than one time
Only one tester can test the application (ticket) in one iteration
History of testing should be collected and available

The updated model:

I'm not good into database designing, it's my first bigger project. So forgive me a basic mistakes :) If the diagram is not readable so I'd be glad to hear some advices how to make it pretty. Also, if someone see some weak points, please comment it.

I have a few general points.

Firstly, your diagram is a bit confusing! There are lines which merge into each other and the source and destination tables aren't clear.

Secondly (and this is a matter of opinion), you might want to use <table_name>_id as your PRIMARY KEY (e.g. ticket_id). It removes ambiguity, makes debugging easier and SQL easier to write and particularly read. My own personal convention is to use lower_case_with_underscores_id (and lower case for identifiers generally) and UPPER CASE FOR SQL. This makes code a pleasure to read and almost self-parsing.

Also, take a look here, it's from databaseanswers.org which can be a good first port of call for database modelling projects! Try and make your diagram look a little bit more like that!

Finally, you can download F/LOSS applications for bug-tracking - there's more of them than you can shake a stick at! Again, personal recommendation, you should look at Fossil - it's by D. Richard Hipp, the same guy who's given the world SQLite!

I've watched some podcasts by him and he's impressive! At least take a look at his schema - you can be sure that thought went into it! It's BSD licenced, but I'm not sure if your professor/lecturer would be happy about you claiming his work as your own, but if you clearly refer to it, that should be OK! :-)

Be prepared to make a lot of changes - as Helmuth Von Moltke said, "no plan survives contact with the enemy" - best of luck with your project!


Better performance with (Recompile) option, How? I've got a query which runs for 1 hour without any query hint, But executes in just 2 seconds flat with the "Recompile" query hint. When I got a look at the actual execution plan produced by both the queries, The query without the "recompile" hint has got more Table scans(It's actually a huge plan, and there were are a lot of scans).

To make sure the query plan is not being used from the plan cache and to rule out parameter sniffing, I cleared the entire cache using DBCC Freeproccache and did the tests again, Still no difference!.

So, From my understanding,

With Recompile option tells SQL server not to cache the Query plan and generate a brand new one.(Result : generates a good plan and Performs well)

Without recompile option tells SQL server to reuse the plan in cache if it's available(but it's not there since I cleared the plan cache), or generate a new plan.(Result : generates a bad plan and Performs poorly)

So in both the cases SQL server will be generating a brand new plan, why the difference in plan and a humungous difference in performance all with just an extra Recompile option!!!!.

What's probably going on is that you're running the query with different input values in your parameter. Let's look at this simplified example:

SELECT columns FROM table WHERE something=@var;

This query might return zero rows, a single row or millions of rows, depending on the value of @var. The first time you run this query (without RECOMPILE), SQL Server generates a query plan based on the value of the variable for that execution.

Suppose that value of @var returns a single row, the query can afford an execution plan that takes 0.1 seconds for each row it returns, completing your query in 0.1 seconds total. Now, if you apply that same plan to another value of @val where the query returns 1000 rows, that query is going to run for minutes and you would probably be better off with another plan, perhaps with different joins, aggregates, etc.

When you use WITH RECOMPILE, SQL Server will evaluate the query (and the variable values) every time it runs and recreate the execution plan every time. This will add a few milliseconds to each execution (to generate the plan) but the resulting plan will probably be better suited for your particular value of @val.

If you're fine with an execution time of two seconds, this won't be a problem. However, if your query needs to complete in milliseconds, I would start tuning indexes, updating statistics, etc.

In summary, yes, this sounds a lot like a parameter sniffing problem, and WITH RECOMPILE appears to be a good solution in your scenario. I can't make any more qualified guesses without looking at query plans, tables and indexes, statistics and the different parameter values. Hope that helps.


PostgreSQL CHECK constraint is evaluated wrongly Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced.

drop database if exists example;
create database example;

create table example_table (
    id integer not null,
    value integer not null,
    constraint pk primary key (id));

CREATE OR REPLACE FUNCTION checkunique()
  RETURNS boolean AS
$BODY$
begin
    return NOT Exists (
        Select 1
        FROM example_table AS a, example_table AS b
        WHERE a.id != b.id AND a.value = b.value);
end
$BODY$
LANGUAGE plpgsql STABLE;

alter table example_table add constraint unique_value check (checkunique());

The check constraint substitutes UNIQUE constraint because I need to store large VARCHARs in production case and there is a limit on size of indexed cell.

Now data that breaks the CHECK constraint can be inserted:

insert into example_table (id, value) values (0,0);
select checkunique(); -- returns true
insert into example_table (id, value) values (1,0);
select checkunique(); -- returns false

Even the database itself admits that the constraint function evaluates to false, however data are stored.

Now one can't insert any, even constraint satisfying data:

insert into example_table (id, value) values (2,2);
--error output:
--ERROR: new row for relation "example_table" violates check constraint "unique_value"
--SQL state: 23514
--Detail: Failing row contains (2, 2).

How can I make the constraint work correctly?

Environment: PostgreSQL 9.3.6, Fedora 20 x64

Well, your query here:

return NOT Exists (
    Select 1
    FROM example_table AS a, example_table AS b
    WHERE a.id != b.id AND a.value = b.value);

is not going to see the not-yet-inserted row, which is why it returns true on this second INSERT in your example:

insert into example_table (id, value) values (1,0);

Now, you could almost fix this by hacking up the query to something more like:

CREATE OR REPLACE FUNCTION checkunique(in_value integer)
RETURNS boolean AS
$BODY$
begin
    RETURN NOT Exists (
    Select 1
    FROM example_table WHERE value = $1);
end
$BODY$
LANGUAGE plpgsql STABLE;

ALTER TABLE example_table add constraint unique_value check (checkunique(value));

However, this is really not what CHECK constraints are supposed to be used for, and it actually introduces a race condition if you have multiple transactions writing to example_table at the same time (can you see how?). Use the UNIQUE constraints that PostgreSQL provides. If your values are too large for the UNIQUE constraint's B-Tree index, create the UNIQUE constraint on an MD5() of the value.


Error 1052 Field is Ambiguous using Inner Join Function Error '1052' Field is ambiguous. I understand the error that is happening, and I've looked at several places for the fix and then applied the inner join function in attempt to fix it. It's still not working, and it gives me the same error.

EventNo is a primary key in the eventrequest table and a foreign key in the Eventplan table. I'm trying to list the event number, event date, and count of the event plans while only including event requests in the result if the event request has more than one related event plan with a work date in December 2013. This is my code.

SELECT EventNo, DateAuth, COUNT(PlanNo)
FROM eventplan INNER JOIN eventrequest ON eventplan.eventNo = Eventrequest.EventNo
WHERE COUNT(PlanNo) > 1 BETWEEN '2013-12-01' AND '2013-12-31';

Qualify each column in the SELECT/WHERE and join clauses.

So, for example I've qualified the EventNo column so that it's read from the eventplan table in the SELECT clause - it's ambiguous because it is in both the eventplan and eventrequest tables.

SELECT eventplan.EventNo, DateAuth, COUNT(PlanNo)
FROM eventplan INNER JOIN eventrequest ON eventplan.eventNo = Eventrequest.EventNo
WHERE COUNT(PlanNo) > 1 BETWEEN '2013-12-01' AND '2013-12-31';

You may nave to do the same for DateAuth and PlanNo if they exist in both tables (in this specific query).

It's good practice to always fully qualify column names - can make the query easier to read. You can always use a table alias too to cut down on typing.


Populate monthly table from quaterly data I have below excel file that I am loading to ssis. The problem is that the granualrity for the date dimension is by month. Is there a way to load the data so that for Jan, Feb & Mar the value is -0.10409. April May June is 0.662643 and so on

Cheers

Aidan

If your date dimension has an Qtr Number attribute, you could join the import data to the date dimension on that field and you would create the required rows.


Mongodb create user with only read and write access Hi I created a new using using following command in mongodb,

db.createUser({ user: 'csquad', pwd: 'somepass123', roles: [ { role: "readWrite", db: "cloud"}]});

Now this user is able to login to db Cloud and do any activities. Including he is able to create a user with same readWrite access to database cloud.

Now I wanted to update this user to a new role so that he has only Read and Write access to any Document or create any collections in this DB. Should not have access to create new user.

How or what role I should assign in MongoDB

Even if I create a user with role "read" still he is able to create a new user with readWrite. And the "read" user is still able to create new collections / add documents

Basically any user in the "cloud" database is able to perform any task irrespective of Roles assigned to them. Only restriction is they are able to do all task in the specified database (like they can't login to admin DB etc..)

I look like that you haven't enabled authentication (--auth) at the database. Without authentication, there is no authorization. That's why all users can do now whatever they want.


Optimize simple query using ORDER BY date and text I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records

The data types of the table are:

fcv_id = serial
fcv_fecha_comprobante = timestamp without time zone
fcv_numero_comprobante = varchar(60)

The query is:

SELECT fcv_id, fcv_fecha_comprobante FROM factura_venta
ORDER BY fcv_fecha_comprobante, fcv_numero_comprobante

This query takes about 5 seconds, but if I take out the "order by" the query takes only 0.499 seconds

The problem I have is that I need to run this query in the shortest time possible,
so I search on google what can I do and create a composite index with the following query

CREATE INDEX factura_venta_orden ON factura_venta
USING btree (fcv_fecha_comprobante ASC NULLS LAST
           , fcv_numero_comprobante ASC NULLS LAST);
ALTER TABLE factura_venta CLUSTER ON factura_venta_orden;

But the query is taking the same time or even more.

I'm using Postgres 9.0.13, here is the EXPLAIN with 73436 rows

Sort  (cost=11714.03..11897.62 rows=73436 width=27) (actual time=1260.759..1579.853 rows=73436 loops=1)
  Sort Key: fcv_fecha_comprobante, fcv_numero_comprobante
  Sort Method:  external merge  Disk: 2928kB
  ->  Seq Scan on factura_venta  (cost=0.00..4018.36 rows=73436 width=27) (actual time=0.363..162.558 rows=73436 loops=1)
Total runtime: 1694.882 ms

Postgres is running on a Phenon II 1055T (3 cores) With 8 GB Ram and 500 GB disk.

How I can optimize this query?

According to your comment on a deleted post, you load all rows into a java module to conduct a search there. But searching is better done in the database itself - that's what a database is good at. Only return the rows you actually need.
If you really need all rows, there are many little things to make this faster. 1M rows will never be very fast, though.
Postgres 9.2 or later
You can make the index covering by appending fcv_id:
CREATE INDEX factura_venta_orden
ON factura_venta (fcv_fecha_comprobante, fcv_numero_comprobante, fcv_id);
This way, provided the table isn't updated too much, Postgres can retrieve results with an index-only scan.
The additional column comes last since it does not contribute to the sort order. Explanation:

Is a composite index also good for queries on the first field?

In Postgres 11 or later you could make that:
CREATE INDEX factura_venta_orden
ON factura_venta (fcv_fecha_comprobante, fcv_numero_comprobante) INCLUDE (fcv_id);
CLUSTER / pg_repack
I see you already found CLUSTER. You are aware that this is a one-time operation, that should help your cause, but needs to be re-run after enough updates?
There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER.
work_mem
This line in your EXPLAIN output:
Sort Method:  external merge  Disk: 2928kB

tells us, that sorting is not done in RAM, which is expensive. You could probably improve performance by tuning the according setting for work_mem

work_mem (integer)
Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. ...

Setting this too high may have adverse effects. Read the manual carefully. Consider increasing the setting only for the transaction with the big query:
BEGIN;
SET LOCAL work_mem  = '50MB';
SELECT ...;
COMMIT;

50 MB are an estimate based on your EXPLAIN ANALYZE output for 73k rows. Test with 1M rows to get the actual amount you need.


Recursive CTE to find unique slug I have an article table where I want the slug to be unique.

CREATE TABLE article (
   title char(50) NOT NULL,
   slug  char(50) NOT NULL
);

When the user enters a title e.g. News on Apple, I want to check the database to see if a corresponding slug exists e.g. news-on-apple. If it does, I'll suffix a numerical value until I find a unique one e.g. news-on-apple-1. Can that be achieved with a recursive CTE query instead of doing recursion in my ORM. Is there a good ballpark number where I should stop recursing and error out. I can imagine people using the same title a 1000 times which would result in 1000 queries just to create 1 article.

It's possible that my understanding of recursive CTE is incorrect and there's no better way to find a unique slug. Please suggest any alternatives.

First off, you do not want to use char(50). Use varchar(50) or just text. Read more:

Any downsides of using data type “text” for storing strings?

Assuming the following rules:

Basic slugs never end with a dash.
Duplicate slugs are suffixed with a dash and a sequential number (-123).

Note that all of the following methods are subject to a race conditions: concurrent operations might identify the same "free" name for the next slug.
To defend against it, you can impose a UNIQUE constraint on slug and be prepared to repeat an INSERT upon duplicate key violation or you to take out a write lock on the table at the start of the transaction.

If you glue the suffix to the basic slug name with a dash and allow basic slugs to end in separate numbers, the specification is a tiny bit ambiguous (see comments). I suggest a unique delimiter of your choice instead (which is otherwise disallowed).

Efficient rCTE

WITH RECURSIVE
  input AS (SELECT 'news-on-apple'::text AS slug)  -- input basic slug here once
, cte   AS (
   SELECT slug || '-' AS slug  -- append '-' once, if basic slug exists
        , 1 as suffix          -- start with suffix 1
   FROM   article
   JOIN   input USING (slug)

   UNION ALL
   SELECT c.slug, c.suffix + 1  -- increment by 1 ...
   FROM   cte     c
   JOIN   article a ON a.slug = c.slug || c.suffix  -- ... if slug-n already exists
   )
(
SELECT slug || suffix AS slug
FROM   cte
ORDER  BY suffix DESC  -- pick the last (free) one
LIMIT  1
)  -- parentheses required
UNION  ALL  -- if the basic slug wasn't taken, fall back to that
SELECT slug FROM input
LIMIT  1;

Better performance without rCTE

If you worry about thousands of slugs competing for the same slug or generally want to optimize performance, I'd consider a different, faster approach.

WITH input AS (SELECT 'news-on-apple'::text  AS slug
                    , 'news-on-apple-'::text AS slug1)  -- input basic slug here
SELECT i.slug
FROM   input        i
LEFT   JOIN article a USING (slug)
WHERE  a.slug IS NULL  -- doesn't exist yet.

UNION ALL
(  -- parentheses required
SELECT i.slug1 || COALESCE(right(a.slug, length(i.slug1) * -1)::int + 1, 1)
FROM   input        i
LEFT   JOIN article a ON a.slug LIKE (i.slug1 || '%')  -- match up to last "-"
                     AND right(a.slug, length(i.slug1) * -1) ~ '^\d+$' -- suffix numbers only
ORDER  BY right(a.slug, length(i.slug1) * -1)::int DESC
)
LIMIT  1;

If the basic slug isn't taken yet, the more expensive second SELECT is never executed - same as above, but much more important here. Check with EXPLAIN ANALYZE, Postgres is smart that way with LIMIT queries. Related:

Optimize a query on two big tables

Check for the leading string and the suffix separately, so the LIKE expression can use a basic btree index with text_pattern_ops like

CREATE INDEX article_slug_idx ON article (slug text_pattern_ops);

Detailed explanation:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Convert the suffix to integer before you apply max(). Numbers in text representation don't work.

Optimize performance

To get the optimum, consider storing the suffix separated from the basic slug and concatenate the slug as needed: concat_ws('-' , slug, suffix::text) AS slug

CREATE TABLE article (
   article_id serial PRIMARY KEY
 , title text NOT NULL
 , slug  text NOT NULL
 , suffix int
);

The query for a new slug then becomes:

SELECT slug
    || COALESCE((
          SELECT '-'::text || (max(suffix) + 1)::text
          FROM   article a
          WHERE  a.slug = i.slug), '') As slug
FROM  (SELECT 'news-on-apple'::text AS slug) i  -- input basic slug here

Ideally supported with a unique index on (slug, suffix).

Query for list of slugs

In any version of Postgres you can provide rows in a VALUES expression.

SELECT *
FROM   article
JOIN  (
   VALUES
     ('slug-foo'::text, 1)
     ('slug-bar',7)
   ) u(slug,suffix) USING (slug,suffix);

You can also use IN with a set of row-type expressions Which is shorter:

SELECT *
FROM   article
WHERE (slug,suffix) IN (('slug-foo', 1), ('slug-bar',7));

Details under this related question (as commented below):

the <set clause>'s <multiple column assignment>

For long lists, the JOIN to a VALUES expression is typically faster.

In Postgres 9.4 (released today!) you can also use the new variant of unnest() to unnest multiple arrays in parallel.

Given an array of basic slugs and a corresponding array of suffixes (as per comment):

SELECT *
FROM   article
JOIN   unnest('{slug-foo,slug-bar}'::text[]
            , '{1,7}'::int[]) AS u(slug,suffix) USING (slug,suffix);


Datetime to formatted Date Implicit Conversion I am looking at implicit conversions in my stored procedures and the biggest one I have in my prod database is a warning by the Cardinality Estimator in SQL 2014 for converting datetime to formatted date.

I have read the following questions:
Warning in query plan "Cardinality Estimate" and
DateTime to Format: Implicit Conversion but these fail to mention that FORMAT() removes the warning they are only describing that the problem won't be fixed, which is not a solution I looking for, FORMAT() is a viable solution.

Previously we used the formatting option on CONVERT() to format the date, but ANYTIME I use CONVERT() I get an implicit conversion.

CREATE TABLE #temptable ( [Birthdate] datetime )
INSERT INTO #temptable
VALUES
( N'1891-12-30T00:00:00' ),
( N'1918-10-31T00:00:00' ),
( N'1917-12-21T00:00:00' ),
( N'1906-10-29T00:00:00' ),
( N'1916-12-12T00:00:00' ),
( N'1916-11-08T00:00:00' ),
( N'1918-10-01T00:00:00' ),
( N'1913-03-18T00:00:00' ),
( N'1920-08-03T00:00:00' ),
( N'1919-10-22T00:00:00' )

SELECT CONVERT(VARCHAR(26),t.Birthdate,109) AS varConvert,
        CONVERT(NVARCHAR(26), t.Birthdate, 109) AS nvarConvert,
        TRY_CONVERT(VARCHAR(26),t.Birthdate, 109) AS tryCon,
        FORMAT(t.Birthdate, 'MMM dd yyyy') AS form
FROM #temptable AS t

DROP TABLE #temptable

The only not implicit conversion to a formatted date is the FORMAT() function:

Is there a way to not use the FORMAT() function and continue using the Convert function to return formatted dates? Ie do i need to extend or reduce the length of my formatted dates during conversion. I want to avoid implicit conversions, but re-writting all CONVERT() to FORMAT() will take more testing than changing the current CONVERT functionality.

The query you posted does indeed produce PlanAffectingConvert warnings in SQL Server 2016 SP1 (didn't try on earlier versions).

That's a bug - those convert statements shouldn't be throwing that warning since it doesn't affect cardinality in any way when they're just in the SELECT portion.

File a bug report for it at http://connect.microsoft.com and include your repro code, and edit your question to include a link to the Connect item so folks can upvote it.


Cannot login oracle database using other users I'm currently using oracle xe c18.  I can login using sqlplus / AS SYSDBA as oracle user.  However, the command does not work for other users.  It always shows an error ORA-01017: invalid username/password; logon denied.  I already give SUID and GUID to all installation files.  What's the problem?

The syntax you gave is using 'os authentication' to connect.  That is, the OS user issuing the command is a member of the os group "DBA".  (Or, on Windblows, "ORA_DBA").  I hope you did not change ownership or permission of any of the files.


Mysql separate username from client table for performance and use myisam for it I'm creating a database for a very heavy messenger like Telegram ...
I decided to use a different table for client-username and separate it from the clients table.

This is My 'client' table:

InnoDB (because we are going to have too many updates over select !)
ascii_general_ci

     ID       -> int (unsigned parimary auto-index)
     password -> char (64) (not-null ('char' get us better performance over 'varchar' since password-length is fixed-length (64) (because of hash))
     ...

This is my 'client_username' table:

MyISAM (because we are going to have too many SELECT over update !)
ascii_general_ci

     client_ID       -> int (unsigned unique)
     client_username -> char (16) (using 'char' for better performance duo to it's fixed-length type. also it's better to use 'char' type in MyISAM type)

I separated username because the client table is InnoDB and it's not good as MyISAM in SELECT.  So I separated username into a table with MyISAM type.  Here we have the best performance in searching for username, and also I did it because I want to choose 'char' type for my username and I heard 'char' type is faster than varchar nly in 'MyISAM' type.  Am I right about all of these points?

The MyISAM vs InnoDB Myth

  "InnoDB is not good as MyISAM"

That's an old wives' tale.  Erase it from your mind.

InnoDB has improved a lot since that rumor was started.
You now have user information split across two tables; the small overhead of doing such is probably worse than having all the info neatly together in one InnoDB table.

Bottom line:  Use InnoDB for all tables.  There are very few exceptions to this simple rule.  In no particular order:

InnoDB tables usually have a 2x-3x larger disk footprint.  But, so what, disks are huge.
COUNT(*) without WHERE is 'instantaneous' in MyISAM.
2-col AUTO_INCREMENT -- standard in MyISAM; clumsy to simulate in InnoDB.  (Rarely asked for.)
Performance in obscure cases.  (No specifics come to mind at the moment.)
One might quibble that the differences in FULLTEXT constitute an issue.

On the flip side, Oracle has taken the stand that MyISAM will be removed from MySQL.

The CHAR vs VARCHAR Myth

  "CHAR is better than VARCHAR"

Another _old_wives' tale.  Even in MyISAM, that quote is often taken out of context.

Even in context it is rarely valid
If you have variable length data, the savings for I/O is higher than the alleged savings of CHAR over VARCHAR.
In InnoDB, CHAR and VARCHAR are mostly implemented identically.

Bottom line:  Use CHAR only for strings that are truly fixed length.

The need-to-optimize-the-little-things Myth

I'll start with the 'answer' first.

Even before looking at the data, there are other tasks.

Receive the query, possibly across a WAN.  (Up to milliseconds.)
Parse the tokens in the query.
Figure out which table is being used for each column named in the query.
Open the tables.
Invoke the Optimizer to deduce the best way to perform the query.  This will involve locating all the possible indexes, doing probes into the tables to gather statistics, etc.
Run the query.

In the grand scheme of things, locating a record is far more costly than anything that is done with the record.  (This is a generalization, not an absolute.)

Locate the record -- perhaps via an index, perhaps "next after" the last record fetched.
Fetch the block containing the record.  This is probably cached in the buffer pool, but it might need to be fetched from disk.  So, this step might be nanoseconds, or it could be milliseconds.
Dissect the block to find the row in question.  This might include scanning the "history list" if multiple transactions are running and the "isolation mode" needs to be consulted to decide which copy of the row is "visible".
Now that you have the row, the columns need to be picked apart -- even with off-word-boundary issues, byte scans, NULL checks, length checks (eg, for VARchar, etc, we are talking nanoseconds per column.
"Endianness" slips in about here.  MySQL can handle big-endian and little-endian hardware architecture with binary compatibility.  This implies that for some hardware-dependent situations, it must swap bytes to get the column value into the right "endianism".
Do something with the column.  This may be simply copying it intact; it may be applying a function (collation, summation, sqrt, whatever).  Again nanoseconds.

Now, what was your question?  Oh, yeah, you were concerned about some tiny part of the last step.

General

Word boundaries, for various hardware, software, and design reasons are not worth thinking about.
For large tables, I/O is a much bigger factor in performance than fixed versus variable-length things.
Fixed length in MyISAM had very few advantages.  Most vanish when you aren't doing UPDATE or DELETE + INSERT.
All columns in a row needed to be "fixed", else it was "variable".
InnoDB possibly has zero benefits from "fixed".
MyISAM was designed before variable-length charsets (utf8, etc) were added.  Even CHAR is effectively variable length when using utf8.

(And stop reading any MySQL reference that is over a decade old.)


Error while running script to backup redo logs I am very new to DB administration and i was hired to manage an enterprise database.

We have a standby Oracle DB server that runs on Linux.

There is an FRA on that standby that gets filled up quickly, my predecessor wrote a script to back up the redo logs on that FRA to another drive and then delete the redo logs from FRA.

The problem.

whenever I run that script i get this error from /var/spool/mail/oracle

RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database:
            ORA-01017: invalid username/password; logon denied

Please help I can't afford that standby db not getting it's redo logs.

RMAN can not connect to the recovery catalog, because the username or password is incorrect.

Fix the username and password in the catalog database and the script.


Setting maxdop 1 vs setting the threshold for parallelism high I am looking at a possible fringe case for parallelism settings.

I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the database is strictly OLTP, and parallelism has little to no benefit for a CRM instance, I've always set CRM databases to maxdop 1.

However, I have in my care now a database instance that has:

Microsoft Dynamics CRM database
An ETL database that is used primarily for OLAP purposes

I've asked our business partners and the way they see the performance importance is as follows:

CRM has to be fast, period
ETL imports should not be throttled, but come second to CRM performance

As such, I'm inclined to sacrifice parallelism on the OLAP database, and set maxdop 1 to benefit the CRM maximally.
Yet some middle ground might exist.

If I were to set the cost threshold for parallelism to a number which excludes everything that CRM throws at it, but according to my query plans might grab some of the OLAP transactions, would there be any adverse effects for the CRM?

What, if anything, is the impact of keeping a threshold so high, it is almost never reached, versus setting maxdop 1, am I incurring some hidden cost in making SQL Server take the threshold into account when calculating query plans?

There is an easy solution if you have Enterprise (expensive) Edition in use:
Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


Improve query speed for large, wide table I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and will not be updated. (Most columns are omitted here to save space.)

    CREATE TABLE claims
   (
     spending double precision,
     releaseid bigint NOT NULL,
     priceinclude bytea,
     costsharing double precision,
     hcciprofode bytea,
     memberlinkeid bigint,
     age integer,
     orgid integer,
     gender character(1),
     membercity character varying(22),
     memberstate character(2),
     zip character varying(5),
     principaldiagnosis character varying(6),
     CONSTRAINT claims_pkid PRIMARY KEY (releaseid)
   )
   WITH (
     OIDS=FALSE
   );

Indexes:
"claims_pkid" PRIMARY KEY, btree (releaseid)
"claims_age_idx" btree (age)
"claims_memberlinkeid_idx" btree (memberlinkeid)
"claims_principaldiagnosis_idx" btree (principaldiagnosis)
"claims_serviceprovider_idx" btree (serviceprovider)
"claims_zip_idx" btree (zip)

The indexes shown above are the only ones I've added so far. I am running the database using Windows 7 with the following hardware:

64-bit Windows 7
32.0 GB RAM
Intel Core i7-4770 CPU @ 3.40 GHz

My main question involves what I can do to improve query speed without buying a dedicated server. I am trying to demonstrate a proof-of-concept to my supervisors to show them that SQL would be faster than the statistical package the currently use, STATA. I have optimized the default settings of PostgreSQL as follows:

shared_buffers=8GB
effective_cache_size=24GB
work_mem=1GB
maintenance_work_mem=1GB
checkpoint_segments=128
checkpoint_completion=0.9
wal_buffers=-1
default_stat_target=500

However, the queries are still slow. For example, the following query

EXPLAIN ANALYZE VERBOSE
SELECT * FROM claims
WHERE gender='F'
AND age BETWEEN 16 AND 20
LIMIT 100000;

returns this:

Limit  (cost=0.00..226622.43 rows=100000 width=427) (actual time=0.029..9509.028 rows=100000 loops=1)
Output: spending, releaseid, priceinclude, costsharing, hcciprofcode, htype,
hcciotpcode, drgmpr, medsurg, typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate, zip, admissiondate, serviceprovider,
servicenpi, ser (...)"
->  Seq Scan on public.claims  (cost=0.00..4778859.76 rows=2108732 width=427)
(actual time=0.028..9497.420 rows=100000 loops=1)"
Output: spending, releaseid, priceinclude, costsharing,
hcciprofcode, htype, hcciotpcode, drgmpr, medsurg,
typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate,
zip, admissiondate, serviceprovider, servicenp (...)"
Filter: ((claims.age >= 16) AND (claims.age <= 20) AND (claims.gender = 'F'::bpchar))"
"Rows Removed by Filter: 3194024"
"Planning time: 0.190 ms"
"Execution time: 9519.455 ms"

Should I be creating a new view or a smaller table to access frequently used columns? I have never created a database this large so I am quite perplexed as to what to do!

UPDATE: My goal is to be able to query this database in a reasonable amount of time for exploratory data analysis. 15 seconds for queries is reasonable; 5 minutes for queries on the whole table is not. I am not sure how best to deal with this large table in order to achieve that goal, which is what I would like help with. (Apologies if this is a little vague.)

For your example query:
         SELECT * FROM claims
         WHERE gender='F'
         AND age BETWEEN 16 AND 20
         LIMIT 100000;
try to add index on age.
But I suggest to use "SQL SERVER PROFILER" and "Database Engine Tuning Adviser" to monitor your queries, and then add suitable indexes, and make suitable changes.


Relational database for address model I want to design an "Address" model for all types of entities like users, businesses, etc.
I have two types of main models: one is User and the other is Business. Each one has different address types like below.
User
1.Contact Address
2.Billing Address

Business
1.Contact Address
2.something

So I created an address model with an addresstype column like this
Address
id
addresstype
user
addressline1
addressline2

Relationships:

User – One to many –> Business
User – One to many –> Address (User Column)

Now using the above relations, addresstype and user columns will be in a relation, but Business address is not relatted with address.
How can I design this one in an efficient way?

Here's what we did in LedgerSMB:

Entity table storing basic container info on individuals and businesses
location table storing address information.
Right now we have a join table between address and entity, but in future versions that will probably be merged with the address table.
Tables 'person' and 'company' which extend entity for that purpose.

That basic structure would work on MySQL too.  On PostgreSQL we do some magic with partial unique indexes to make sure that billing addresses are unique per entity, but contact addresses are not.  This would not be possible to enforce in MySQL as easily though it could be done with creative design.


Setting a default principal server in SQL Server 2008 This should be a pretty simple answer but I can't find anything on it searching the web.

SQL Server 2008 R2, mirrored with a witness:

Node1 (Principal)
Node2 (Mirror)
Node3 (Witness)

If Node 1 fails, Node 2 takes over as it should, however when Node 1 recovers, Node 2 remains the principal.

Is there a way to make Node 1 the primary/default server so that when it's available, it's always the principal server?

This is not an out of the box feature of SQL Server. And here's why:

After a fail-over scenario, when the formerly primary server is back online, that doesn't necessarily mean that it should be put back into the primary role. Just because the server is online doesn't mean it's necessarily totally healthy from a hardware/software perspective (after all, it just went down). What if the issue is going to happen again in an hour? Do you really want to be playing ping pong with mirroring nodes every time the primary is down? There should be a manual QA of the node before putting it (preferably after business hours) back into the primary role.

Simply put: failing over automatically outside of a disaster situation adds unnecessary risk to your database up-time.

This article on SQL Server Central provides the following stored procedure to facilitate the action you describe, but I'd caution against doing it once again.

Create Procedure dbo.dbm_FailoverMirrorToOriginalPrincipal
     @DBName sysname
As
Declare @SQL nvarchar(200)

Set NoCount On;

/*
     If database is in the principal role and is in a synchronized state
     then fail database back to original principal
*/
If Exists (Select 1 From sys.database_mirroring
          Where database_id = db_id(@DBName)
          And mirroring_role = 1 -- Principal partner
          And mirroring_state = 4) -- Synchronized
  Begin
     Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

     Exec sp_executesql @SQL;
  End

Set NoCount Off;


pg_stat_activity shows lots of easy queries running for minutes After application server start its connection pool fills quickly and application can't work properly.
I went through pg_stat_activity and noticed lots of queries running for minutes:

But postgres's slow query log does not show any long-running queries (i've set 100ms as minimum query time).

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2

I suggested that there are some locks on that table but SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED; shows nothing.

Structure of persistence_journal is pretty easy:

CREATE TABLE IF NOT EXISTS public.persistence_journal (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

persistence_journal contains 1 678 121 rows with ~37 000 unique persistence_ids with average 44 rows per persistence_id.

Running SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
 shows:

active  1
idle    43
idle in transaction 45

This "transaction" contains two pretty simple queries:

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2
select max("deleted_to") from "persistence_deleted_to" where "persistence_id" = $1

persistence_deleted_to contains zero rows.

System's top shows almost zero CPU load.

PostgreSQL 9.4, Ubuntu 14.04

Your problem are the "idle in transaction" sessions.  You should fix your application to avoid that.  The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).


Do id columns help (in speed) select statements? I have a table which contains data from aggregation software.
The columns mostly are int columns, but three of them are string columns.
It looks sort of like:

userId, someData1, someData2,     someData3, otherIdColumn, childId, websiteId
------------------------------------------------------------------------------

1, somestring,  someotherstring  justastring    3,             2,       1
1, somestring1, someotherstring1 justastring    3,             3,       3
1, somestring2, someotherstring2 justastring    2,             2,       1
1, somestring3, someotherstring3 justastring    1,             9,       5
2, somestring4, someotherstring4 justastring    4,             2,      10

and so on.

The table contains something like 80m rows for now.
When I'm trying to make a query using aggregation functions (SUM) and grouping by one of the string columns (someData1, someData2, someData3) it takes very VERY long time (more than 10 minutes for a query).

I'm trying to optimize the table right now, the first thing that I've did I've added indexes to the string columns, but I want to make it even faster.
I thought of adding an ID column (pk, ai, nn) as of it will be make the select queries faster.

What do you think about it? do you have any advice how more I can optimize this table? Note: I have only like 5 columns that I'm using group by. 3 of them is string columns, and they are making the problem.

Since you mentioned your typical queries involve grouping by string fields, I assume you have many duplicate values in these columns (otherwise grouping makes no sense).

You can definitely improve performance by creating lookup table[s] for values stored in someData1, someData2, someData3 with integer primary key and changing original someData1 to someData1_id . You still need index on someData1_id, but taking into account number of rows , the new index will be much smaller than index on string field, and as a result , much faster...


Difference in location of transaction isolation level declaration - inside/ouside a transaction? I have been looking though some stored procedured on our system today when I saw the following structure:

(pseudocode indicative of actual SP, server is using the default isolation level):

CREATE PROCEDURE ...
AS
BEGIN
  IF NOT EXISTS(SELECT ...)
  BEGIN
    RETURN;
  END

  BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    DML...
    DML...
    DML...
  COMMIT
END

I have not seen the transaction isolation level being declared inside a transaction boundary and was wondering what the effect of it was?

Is the isolation level restored once the transaction is committed (so if there were additional statements after the commit - what would their isolation level be)?

In this case, I assume there wouldn't be a difference if the isolation level were to be declared just before the transaction started. Is that the case?

Mostly, I was somewhat startled to see this, as I have only ever seen transaction isolation declared outside of a transaction, not within in and was wondering - why? What are the benefits of doing so.

Below are relevant excerpts from the SET TRANSACTION ISOLATION LEVEL documentation.

  Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

It goes on to say:

  If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Note that there is no mention of BEGIN TRANSACTION, COMMIT, etc. These transaction control statements do not affect the current session transaction isolation level.

I don't see a functional difference with specifying the transaction isolation level immediately after the BEGIN TRAN. Perhaps the developer wanted to make sure the level was obvious to the reader and thought the best place was at the transaction boundary rather than having to view the beginning of the proc code.


The Value of Certifications Questions

What certifications do you have?
Has your certification (or lack of one) ever worked for or against you?
Do you intend to reach out for certification?
Do you renew your certifications? If so, does your employer actively support the renewal?

I don't have any current database certifications; I only have the ones that my place of work requires me to have for being a sysadmin, and even those will likely lapse as I was declared to "not be a sysadmin" a couple of months ago.

Certification might help to differentiate you between different people when you're looking to get a job -- it's basically someone vouching for you, claiming that you have a given set of skills.

But if it came down to a person who's been doing stuff for years, vs. someone with a piece of paper that said they could pass a test, I'd go with the person with experience.  I've seen way too many certified Oracle DBAs who had no clue what they were doing.  (and when their systems started lagging like hell, refused to even look at their database until I "proved" that it wasn't something the application was doing wrong ... of course, we had to show them how to get statistics out of their database, as they had no clue -- they were glorified operators, making sure backups ran, and occassionally adding accounts for new users).

At one point, my work had set me up with an account on BrainBench, as I was required to get third-party certification avery few years in addition to my place of work's annual computer security tests.  In a few weeks I had managed to pass over 70 tests, which then meant I got 'job role certificates' in over 2 dozen roles.  I even managed to pass tests for software I had never used before (eg, Checkpoint Firewall-1), and some of the tests I really wonder about (why does a test on websever administration ask questions about SMTP headers, but none about HTTP headers?).

...

So, to confuse matters -- I'm a contractor for the US Federal government.  My employeer (the contracting company) wants me to have lots of certifications to make me look impressive as they use my resume to try to get more work for the company.  My place of work (government) just wants me to be able to do the job, and couldn't care less if I was certified or not.  If I need a new skill, I can get the time from my place of work, and the funding from one of the two to take classes ... but I haven't needed to in 6+ years.

If I want to take classes, just for the hell of it, so long as I can justify it benefitting my work, my employeer will pay out of my 'education benefit' (which right now is halved as we're within 12 months of our contract's end date), but I'd have to either get separate permission from my ATR (the civil servant I report to) if he's willing to cover my time, or if it's okay to take off during the middle of the day (like I did when I was working on my Master's degree)

...

So, to answer the questions:

Wilton Cake Decorating, levels 1-3
They might've helped me land my last job that I had a long list, but none were directly applicable to the job I was hired to perform. (at the time, Oracle, Sun iMS, LDAP, etc.)
No, but my employeer would if I wanted to.