Does .bak file also contain backup of ldf file? I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively.

My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) sized backup file, or does it only contain mdf backup?

I need to know this since I don't have this much of space on my hard disk.

I'm using SQL 2008 R2 standard edition

A full backup will contain all used pages in both files (you can convince yourself of this by restoring a full backup: the new files end up the same sizes with the same amount of content, which means the backup must have contained the information required to achieve that). Both files, especially the transaction log, will contain block that are allocated from the filesystem but not currently in allocated to an object in the database so this will be smaller than the combined size of the actual files.

To get an indication of the size of a full backup, run EXEC sp_spaceused on that database. For example on one of my databases this returns database_size = 70015.81 MB and unallocated_space = 2768.83 MB so a full backup will be approximately 67247 MB in size. With a log the size of your example I expect that unless you've not taken any backups recently there will be a log of space unallocated in the log files that therefore won't affect the backup size.

If you are using SQL Server 2008R2 or later (or 2008 Enterprise Edition) then you can compress the backup with the WITH COMPRESSION option which will generally make it much smaller. This will take CPU time of course, but may actually make the process of taking the backup faster by reducing the amount of IO involved.


Mysqldump error : Couldn't execute 'show table status I have a database that I want to export. It has about 6 GB. PhpMyAdmin could not handle it, so after reading a little bit about it I am trying to do it from the console, unsuccessfully. When I run :

./mysqldump -u root -p mydatabase > /Users/myuser/Documents/mydatabase.sql

It gives me this error :

mysqldump: Couldn't execute 'show table status like 'USDT@002dXVG\_day'': Lost connection to MySQL server during query (2013)

Is someone encountered with the same problem ?

The database size itself is very big and that seems to be the reason of lost connection to mysql server.

You can try the below command which will try to take the backup after increasing the net_write_timeout and max_allowed_packet

mysql -u root -p -e "SET GLOBAL net_write_timeout=300; SET GLOBAL max_allowed_packet=1024*1024*1024;"

This command will increase the timeout and max_allowed_packet size.
After this, run the below command to take the dump

./mysqldump -u root -p mydatabase > /Users/myuser/Documents/mydatabase.sql

If you still get error, please provide exact error you are getting in the mysql error logs.


Postgresql: create role, schema and change ownership but not as superuser I need to create various schema in a database and give ownership of these to specific users. I want to do this in a script, and I rather not be the postgres user or a superuser.
My user pat has createdb and createrole privileges.

pat=> create role toto with login password 'pass';
pat=> create schema toto;
pat=> alter schema toto owner to toto;
ERROR:  must be member of role "toto"

Is there a way to do this without being postgres or superuser ? or should I give pat superuser privileges while running the scripts and drop it after? What is the best procedure?

Per documentation,

  You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, [...]

So in your case, you either need to be superuser or be a member of the role you are transferring the ownership to.

Ultimately, while I applaud your efforts, you're going to have a hard time managing this without some superuser use.  So I'd focus on locking down the superuser use, maybe wrap the steps in a few security-definer functions.


Get Total in a single line in SQL Server I have a very simple SQL Query which is getting results in the following way

COLUMN A           COL-B

WINDCHILL   99.998889
WINDCHILL   96.000000

Now how i can modify my query so that I get the total in a single line?
COL-A         COL-B

WINDCHILL   195.99

The necessary query is as;

 SELECT
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      [IR.TimesheetHoursProjectTask] p
 WHERE
      p.ActualWork > 0 AND
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL)
 GROUP BY
      ISNULL([Task IT Assets], [Project IT Asset]), ActualWork
 ORDER BY
      p.ActualWork DESC

You need to modify the GROUP BY and the ORDER BY clauses.

Try this:

SELECT
       ISNULL([Task IT Assets], [Project IT Asset]) AS Asset ,
       SUM(P.ActualWork) AS taskHours
 FROM
      IR.TimesheetHoursProjectTask p
 WHERE
      p.ActualWork > 0 AND
      ([Task IT Assets] IS NOT NULL OR [Project IT Asset] IS NOT NULL)
 GROUP BY
      ISNULL([Task IT Assets], [Project IT Asset])
 ORDER BY
      SUM(P.ActualWork) DESC

As an aside, note that your table is literally named "IR.TimesheetHoursProjectTask" and not "TimesheetHoursProjectTask" under the "IR" schema. To specify the later, using the [] notation, it would be [IR].[TimesheetHoursProjectTask].


MongoDB count and get limited data I'm trying to get the count all the data for the query and as well as get the limited data (for pagination) but both the console output gives me the count. What is that I'm doing wrong. Pls help

const curFind = fbUser.find(find, 'firstName gender age birthday facebookId profileURL email imageUrl preferences blocked flames likes rejects location reported')

  curFind.count(function(e, count) {
    console.log(count);
    curFind.skip(0).limit(10).hint( { $natural : 1 } ).exec(function(err, data) {
      console.log(data);
    });
  });

As per MongoDB BLOG Here In certain situations, count can be avoided for better performance Counting things can be hard on a database. The obvious things to count, number of items in a collection or index, are easy to keep track of, but as soon as you start trying to count records based on a query, things get slower and slower. This is because the database has to do more work scanning practically every item in the database to see if it matches.

MongoDB does have an optimisation for a fast count where all the queried fields are indexed and the query is based around equivalence, but only in that circumstance.


Is it safe to restart PostgreSQL if process terminated for some reason? I am writing a docker-compose config with a container with PostgreSQL in it. docker-compose has a configuration options called restart. If I enable it then whenever the Postgres process stops it will restart Postgres rightaway. However it is (probably) not relevant how it is restarted.

I know that in MySQL it is not safe to start the database right after it stopped because MyISAM tables may be broken. One has to manually repair MyISAM tables first with an unrelated command-line tool and then start the db.

So I wonder if it is generally safe to just restart PostgreSQL in case something is wrong. Or maybe we should first backup, repair, read logs and only then try to start it back.

A related situation would be a power outage for instance. What do I do after that?

Postgres generally handles server restarts pretty well.  Assuming that all the correct hardware is in place (battery-backed cache, etc.), bringing up Postgres after a crash will automatically cause it to enter recovery mode, where it will attempt to replay all the WAL files since the last CHECKPOINT.  Once it has finish replaying up to a consistent state, you are free to use the database and continue with your business.  Having said that, however, it's up to you as the DBA to perform consistency checks as per your business requirements to ensure there is no corruption.

Disclosure: I work for EnterpriseDB (EDB) as a Support Engineer


Is there a difference between RANGE and LIST method for partitioning by consecutive integer values? I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in bulk approximately annually.

Most of our queries include a WHERE year = N condition. In a few cases we do a self-join ON T1.year = T2.year - 1 or similar. So I'd like to explore partitioning by the year to see if we get any performance benefit. But since I don't store a full date, it's not exactly a range, and since I want on year per partition, it's not exactly a list.

So, assuming that I have a table definition that starts like this:

CREATE TABLE foo (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, year SMALLINT UNSIGNED NOT NULL
, ...
) ENGINE=MyISAM

(Note: I'm leaving the bad PRIMARY KEY in this example so the answer below that addresses it will still make sense - but it's tangential to the real question, and not a problem for my actual schema. For the purposes of answering, please assume PRIMARY KEY (id, year) is defined instead.)

Assuming every value of year that might end up in the table is accounted for in one explicit partition (no years < 2001 and no LESS THAN MAXVALUE partition for the RANGE case), would the following definitions be equivalent? If not, what's the difference?

-- Definition 1

PARTITION BY RANGE (year) (
  PARTITION p0 VALUES LESS THAN (2001)
, PARTITION p1 VALUES LESS THAN (2002)
, ...
);

-- Definition 2

PARTITION BY LIST (year) (
  PARTITION p0 VALUES IN (2000)
, PARTITION p1 VALUES IN (2001)
, ...
);

The 2 will be equivalently impossible ;-). If you try that, you will get the following error:

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Which is one of the biggest limitations of partitioning. Any unique key must contain the columns of the partitioning function. So either:

Drop the primary key (not usually a good idea)
Add the year to the primary key (so you can have duplicated ids)
Create your own partitioning (forcing you to maintain yourself the constraints and making the queries a bit more difficult)

Independently of that, I can see at least one big difference (and the reason why I would recommend you to use LIST) which is that even if your application cannot insert lower values, it is not restricted at database level using the range. Not only that could potentially break our data consistency in case of an application error, it would be advantageous if you had a query like this:

SELECT * FROM foo WHERE `year` = 1999;

That also has a potential impact on performance, if you run EXPLAIN PARTITIONS on that query with the RANGE partitioning, the full first partition would be scanned (using an index or not); in the second case, partition pruning can automatically return the empty set. In practice you may not see a big difference if you do things correctly (the partitions will be physically equivalent), but I cannot see any case where RANGE is better, while I can for LIST.

Addendum: If you are thinking about changing the physical structure of the table, and you are running a recent version of MySQL (5.5, 5.6), you should think about changing to InnoDB. I am not saying that you should, there are reasons to maintain the format, but it is something that you may want to ask yourself, as the table will have to be recreated anyway for partitioning.


How best to maintain SQL log file sizes I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.

Right now, I'm taking a full backup of the databases and logs every day at 5am. Some of the log files have ballooned up to 300gb and even after taking a backup they don't reduce in size. I can get them to reduce in size by running something similar to:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);

When I check the LSNs of the backup files I see something like:

RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN:  15781000014686200001
SecondLSN: 15802000000665000001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN:  15802000000665000001
SecondLSN: 15805000000004100001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN:  15805000000004100001
SecondLSN: 15808000000004200001

I don't believe I'm breaking my log chain by shrinking the log files. Reading up on this, I do believe I'm hurting my performance because those shrunk log files have to re-grow themselves.

Questions:

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?
My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?

The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log use to occur.  If you aren't backing up the transaction log, or not frequently enough then there will be not available VLFs and that will cause the transaction log to grow (provided that autogrowth is set) to accommodate additional transaction log entries.

  2.At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?

Routine and scheduled file shrinkage is not a good idea.  Only when you need to reclaim much needed space should you consider a DBCC SHINKFILE.  Also, when you are continuously growing your transaction log, you could be hindering other things such as the recovery of the database.  With too many VLFs in the transaction log (a common problem when the transaction log is only grown by a small storage increment) the amount of time to recovery the database could be longer than desired.

  3.My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Nothing will happen, that is a completely legal operation.  See this below graph from MSDN.  Where there is a black dot, those two operations can not occur at the same time.  As you can see, a database backup and a transaction log are allowed concurrently.

The takeaway here is you should be backing up your transaction log more frequently.  NTFS file growth isn't the only problem you could run into by not backing up your transaction log more frequently.  If you were to have storage failure and your transaction log is lost, then you can only restore to the point in time of your last transaction log backup.  If the transaction log is lost, you won't be able to backup the tail of the log and restore to point-in-time of the failure.  In your case, you could potentially lose 24 hours worth of data.  But if you backup your transaction logs every, say, 30 minutes then your maximum data loss would be 30 minutes.  In that case, if your transaction log is gone, and you have your full backup and your intact log chain you could restore to that last log backup.

TechNet documentation on Transaction Log Truncation


Database Design - Storing Stadium Capacity for Current and Past Events I am trying to design a database as a personal/passion development project to track current and historical football game information. I am having trouble with table designs that will allow me to store data that might change over a period of time.  For instance, stadium capacity may grow or shrink based on a renovation of the stadium.

In this simple diagram, I have three tables:

But with this design, I will only store the current capacity of the stadium so if Stadium A increases capacity by 5,000 in 2018 - all events before would reflect the current capacity and not the capacity when the event occurred.

My initial thought it that I would need an additional table - maybe StadiumCapacity - that would store the stadium ID, a capacity number, and the years that stadium held that capacity and possible a MostRecent column.

Does anyone have experience with designing a database for sports related information and would this be the most efficient way of tracking this information?

Three options:

Option One

Create a history table storing all data that can change about the stadium. This could also include its name, though its location is probably set. Lets call this "StadiumHistory". Your Schedule table would then use this as its FK. The StadiumHistory table would have a FK to the Stadium table which uniquely identifies the stadium and its location.

Option Two

Ask yourself "what is my eventual goal?". The answer is probably "to create a system which can be queried really quickly, where calculations including aggregation functions of teams/goals/etc can be performed very quickly to enable me to identify likely results or predict future outcomes based on a variety of factors, or to examine trends etc from the past".

If this is your eventual goal, you may want to keep your 3NF database above plus have it regularly export to another database, one which has dimension normalization - a data warehouse. Your stadium then becomes a Dimension table, probably expanded to "Location" with your results a Fact table. This Dimension table would contain values (which would often repeat) for all of the fields you have specified in Stadium. In fact, you could expand the "Location" table then to include weather information for the day, pitch condition, crowd attendance etc.

This approach has many advantages long term, but requires a new database (development) and an appropriate ETL process to load it. My experience tells me, you will eventually be building this if your database records historical information and you want to begin aggregating information for trend analysis. If you find yourself writing history tables for everything, you will find the interfaces for this difficult to maintain in your current database.

Option Three

Forget about anything that changes. Is it really important that you record the capacity on any day? If not, don't worry about the history of recording stadium capacity. Your database then only contains the most up to date stadium information.

This is the simplest option - it means you don't have to change your database design.


Two physical servers with sql server on same network IS there a way I can remotely access sql server DB having two different servers in the same network/public IP?

The first Server is configured to listen to 1433 port but we make the connections using No-Ip provider.

As both servers will be under the same DNS, how can I configure my app/sql server to connect to the DB in the second server?

Solved that by changing SQL port on SQL Server Configuration Manager, but you'll always have to specify the port for every connections that is different from the standard 1433 port. If you have an APP, you'll also have to change the connection string method to set up a different port or work the code to get it from an external file.


SQL Server Service Accounts Max Character Length I've been considering a new naming convention for all SQL Server service accounts for instances in our enterprise.  I ran into some issues on my lab environment.

Is there a character limit to SQL Server service account names?

DOMAIN\SQL-Service-COMPUTER-NAME
DOMAIN\SQL-Agent-COMPUTER-NAME
DOMAIN\SQL-SSRS-COMPUTER-NAME
DOMAIN\SQL-SSAS-COMPUTER-NAME
DOMAIN\SQL-SSIS-COMPUTER-NAME

Within SQL Server, the datatype for the name column in sys.database_principles is sysname, which is essentially nvarchar(128). However, the sAMAccountName attribute in Active Directory specifies the login name must be 20 or fewer characters.

A similar question was posted within the forums on SQLServerCentral:

  https://www.sqlservercentral.com/forums/topic/max-length-of-ad-names-for-security


MySQL chain replication I have 3 servers A, B and C. What I want to do is replicate databases like this A->B->C, where A is replicating DB1 and DB2 to B and B is replicating those same databases to C, but I would also like to add an additional database from B that will replicated to C.

In other words, I want to replicate databases like this:

A (DB1, DB2) -> B (DB1, DB2) + DB3 -> C (DB1, DB2, DB3)

Is this possible?

Yes

ServerB will just replicate all databases to ServerC (ignoring replicate-ignore-db for now). There is no setting that limits ServerC to getting data that exists only on ServerA

That is,  A(master) ->B (slave) is unrelated to B(master) -> C(slave) and A and C have no knowledge or replication dependency on each other


How can I lock rows FOR UPDATE without returning data over the connection? I want to do something like this:

begin;
select * from foos where owner_id=123 and unread=true order by id for update;
update foos set unread=false where owner_id=123 and unread=true;
commit;

The goal is to avoid deadlock when two processes execute the UPDATE simultaneously. Problem described more here: Why am I getting a deadlock for a single UPDATE query?

In the statement where I acquire the lock, I don't need any info about the rows. I just want to lock those particular rows. Is there a way to do this (elegant or hacky) which tells postgres to not do any of the work of actually giving me the data?

About the goal to avoid deadlocks: see my answer to your related question:

Why am I getting a deadlock for a single UPDATE query?

About the question in the title:

You can use an empty SELECT list with a locking clause:

SELECT FROM foos WHERE owner_id = 123 AND unread ORDER BY id FOR UPDATE;

Still locks all selected rows, but does not return any data. Still returns empty rows, though, so you still get a row count like usual.

Or, inspired by a_horse's comment:

SELECT count(*) AS locked_rows
FROM  (SELECT FROM foos WHERE owner_id = 123 AND unread ORDER BY id FOR UPDATE) sub;

This way you get a single result row with the number of locked rows. (You can abort in case of 0 rows.) Rows are locked all the same. The SELECT list in the subquery can still be just empty (shortest, cheapest).

Aside: and unread=true is just a noisy, less efficient way of saying AND unread.

If unlocked can be NULL, consider to use unread IS NOT true in both statements.


how to get 'Queries per second avg' using SQL (not 'mysqladmin status') When I start mysqladmin, it prints out several statistics, including "Queries per second avg":

# mysqladmin --defaults-file=../mylogin.cnf status
Uptime: 4568115  Threads: 859  Questions: 3703806462  Slow queries: 19415  Opens: 10300505  Flush tables: 247  Open tables: 2000  Queries per second avg: 810.795

Can I retrieve this "Queries per second avg" value within an SQL query? We use MariaDB 10.1.x

For those running MariaDB and MySQL (If using 5.7, show_compatibility_56 enabled)

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM information_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM information_schema.global_status
WHERE variable_name = 'Uptime') B;

For those running MySQL 5.7, show_compatibility_56 disabled

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM performance_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM performance_schema.global_status
WHERE variable_name = 'Uptime') B;


How to get max field value length for each TEXT field in all tables in database I am trying to get information about the data in my database.  I have several columns in several tables that or type 'TEXT'.  I want to perform a query that will create a result that looks like:

|table_name|column_name|max_length|
-----------------------------------
|my_table  |name       |35        |

I have the query to get all the tables and columns:

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'text'
ORDER BY table_name

But I can't figure out how to now go through each of those tables and columns to get the max length of each column.  I do have the query to get the max length of a specific column and table using:

SELECT 'my_table', 'name', MAX(LENGTH(name)) FROM my_table

How can I make a query that will get the max length dynamically based on the results of the query to get all tables and columns?

No need for PL/pgSQL.

You can use a variation of this solution to do everything in a single query.

select table_schema,
       table_name,
       column_name,
       (xpath('/row/max/text()',query_to_xml(format('select max(length(%I)) from %I.%I', column_name, table_schema, table_name), true, true, '')))[1]::text::int as max_length
from information_schema.columns
where table_schema = 'public'
  and data_type = 'text'
order by table_schema, table_name, column_name;

Evan's comment got me thinking if there is a way to do this with a single statement per table (although I doubt that this performance optimization is really important for a "get me some information about my database query")

With a bit of XML juggling, it is indeed possible:

with counts as (
  select table_schema,
         table_name,
         query_to_xml(format('select %s from %I.%I', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name),
                      true, true, '') as result
  from information_schema.columns
  where table_schema = 'public'
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema,
       table_name,
       substring(x::text, 2, strpos(x::text,'>') - 2) as column_name,
       (xpath('./text()', x))[1]::text::int as max_length
from counts, unnest(xpath('//row/*', result)) as c(x);

For the pagila demo database this returns:

table_schema | table_name                 | column_name | max_length
-------------+----------------------------+-------------+-----------
public       | actor_info                 | film_info   |        830
public       | customer_list              | notes       |          6
public       | customer_list              | name        |         21
public       | film                       | description |        130
public       | film_list                  | actors      |        216
public       | film_list                  | description |        130
public       | nicer_but_slower_film_list | actors      |        201
public       | nicer_but_slower_film_list | description |        130
public       | sales_by_store             | manager     |         12
public       | sales_by_store             | store       |         19
public       | staff_list                 | name        |         12

The above first generates one statement per table containing one max(length(..)) for each column.

The result is then parsed and turned into rows again. It would be possible to aggregate that back into a single JSON.

To get one row per table, with the column information as a JSON value, the following can be used:

with counts as (
  select table_schema,
         table_name,
         query_to_xml(format('select to_jsonb(t) as cols from (select %s from %I.%I) t', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name),
                      false, true, '') as result
  from information_schema.columns
  where table_schema = 'public'
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema,
       table_name,
       (xpath('//row/cols/text()', result))[1]::text::jsonb as column_sizes
from counts;

For the pagila demo database this returns:

table_schema | table_name                 | column_sizes
-------------+----------------------------+------------------------------------
public       | actor_info                 | {"film_info": 830}
public       | customer_list              | {"name": 21, "notes": 6}
public       | film                       | {"description": 130}
public       | film_list                  | {"actors": 216, "description": 130}
public       | nicer_but_slower_film_list | {"actors": 201, "description": 130}
public       | sales_by_store             | {"store": 19, "manager": 12}
public       | staff_list                 | {"name": 12}


Are there any tools to go from ERD to DB design and vice versa? I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa.

For the record, the database i'm using is MySQL.

Since you're using MySQL, you might want to take a look at MySQL Workbench.

It might not quite display the ERDs you're expecting but it gets the job done.

And it does have an import feature. I have only used it to import SQL files but apparently you can import from existing MySQL databases.

Also, a long time ago I found the following tools; but were not quite useful to me back then (perhaps they've matured by now):

Database Design Tool - DDT
Mogwai ERDesigner


Show all variations of 2 characters in postgres How would I do this in postgres :

Lets say I have an x and a y and I want to show all possible combinations :

xx
yy
xy
yx

How can I make postgres do this for me ?

with characters (c) as (
  select unnest(string_to_array('xy', null))
)
select *
from characters c1
  cross join characters c2

Edit:

Apparently this does not work in 8.4, but the following should:

with characters (c) as (
  select unnest(regexp_split_to_array('xy', ''))
)
select *
from characters c1
  cross join characters c2

Thanks to Bruno for testing this.


Spliting changes in separate collection/table or not? I am using MongoDB to store products, user accounts and stuff on my server and so far it's going nice.

But now I'm stuck deciding how do I "split" things apart, like:

I have a database where products are stored, but now I want to log every change made (who made it, when, etc).

Should I put that log (array) in the same (product) document, or should I separate the log in a separate collection?

products:
{
   _id: 1234
   product_name: "Some product"
}

product_changes:
{
  _id: ...
  ref_id: 1234
  changes: ["...","..."]
}

or

products:
{
   _id: 1234
   product_name: "Some product"
   changes: ["...","..."]
}

I'm afraid that if I attach too much Objects in one document, that the performance will suffer, and if I separate everything, things begin to "fragment".

Of course, in the example, "changes" is just a tiny array, but in real usage it may be a array with a lot of objects each with time, user_id and changes made. And summing all this up, might turn this single document big.

Any help on this is greatly appreciated.

Since you have 16MB limitation of document size in MongoDB also your access pattern doesn't require to store the messages in the main document. you can go with the following model

Parent Referencing:

Store all your messages in a different collection and refer the main document id as parent document id

or

Embed only the frequently accessed (first 10 or 20) messages with the main document and store all messages in a different collection. So for the first load, you don't need to do $lookup . Only to see the other messages you need the second collection.


Private access for a SP working on a table I have a Stored Procedure that is dropping, recreating and then applying keys and indexes to a table, but the process gets interrupted by other database tasks and then fails to apply the require keys and indexes.

The table is considered the main table within the Db and gets accessed later on by many apps and website so it is crucial that the indexes get applied.

Are there any tools within SQL Server that will allow me to isolate access to the newly made table until the keys and indexes have been applied ?

Are there any tools within SQL Server that will allow me to isolate access

DDL statements are covered by transactions as well as DML ones, so wrapping the whole process in a transaction may bu the solution there.

Stored Procedure that is dropping, recreating and then applying keys and indexes to a table
its automated to run overnight

Could you edit your question to describe why you are doing this that way?
If it is being run every night (or otherwise on a regular basis) this seems an unusual task to be performing. If you are just looking to optimise the indexes then you can rebuild or reorganise them without needing to drop and recreate (and even then you may want to reconsider: stale stats, fragmentation, and other issues, are not likely to be bad enough that you need to do this as regularly).
If you are dropping the whole table to clear the data (your sentence could be read as just rebuilding the indexes+keys+constraints or rebuilding everything) then using TRUNCATE TABLE should do the job and do so far more efficiently - no need to recreate the other objects at all. IF you can't truncate the table because there are foreign keys referring to it then you'll need to disable those and recreate them at the end of the process, being sure to wrap the process in a transaction with relevant locks to stop applications potentially creating data that breaks the constraints while they are inactive), but that should be a lot quicker than rebuilding everything.


How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace? I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping.  Here's the error I get:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

I have taken the advice (or at least tried to) of the error message and created a new data file. Here's the command I used for that:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ABCDEFG/temp02.dbf'
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 4096M;

Now when I look into DBA_TEMP_FILES I see both the old and new files associated with the TEMP tablespace, yet when I attempt to re-run my query, I get the same error message.

So, my question is why does this error still happen, given that the query I'm running is big but not that big. Did I create the data file correctly? How can I diagnose this further?

Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max gb per datafile.

run the following for actual allowed size:

select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

--------   --------------------   --------------

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
       x.*
from   dba_data_files x

MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).

If your datafile has a low max size or autoextend is not on you could simply run:

alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

Also it is generally a good/common practice for you to have a tablespace that is dedicated to indexes (it doesn't seem that you do, because you didn't specify the tablespace in your create index statement, and it is using the default system tablespace), so after creating such a tablespace, you would run something like this (in your case):

CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
tablespace name_of_ts_for_indexes

(After creating that tablespace and a datafile for it), via:

create tablespace name_of_ts_for_indexes datafile
'c:\app\xyz\oradata\orcl\name_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;

That way your tables would be on one table space and the indexes on another.

Thanks to Brian D. for additional Information.


PostGIS Intersects Query takes lot of time to get the result I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB.

I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables contain geometry(multipolygons).

-- Table: evidensapp_seniangcbr

-- DROP TABLE evidensapp_seniangcbr;

CREATE TABLE evidensapp_seniangcbr
(
  id serial NOT NULL,
  hazard character varying(16) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_seniangcbr_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_seniangcbr
  OWNER TO postgres;

-- Index: evidensapp_seniangcbr_geom_id

-- DROP INDEX evidensapp_seniangcbr_geom_id;

CREATE INDEX evidensapp_seniangcbr_geom_id
  ON evidensapp_seniangcbr
  USING gist
  (geom);
ALTER TABLE evidensapp_seniangcbr CLUSTER ON evidensapp_seniangcbr_geom_id;

-- Table: evidensapp_polystructures

-- DROP TABLE evidensapp_polystructures;

CREATE TABLE evidensapp_polystructures
(
  id serial NOT NULL,
  bldg_name character varying(100) NOT NULL,
  bldg_type character varying(50) NOT NULL,
  brgy_locat character varying(50) NOT NULL,
  municipali character varying(50) NOT NULL,
  province character varying(50) NOT NULL,
  geom geometry(MultiPolygon,32651),
  CONSTRAINT evidensapp_polystructures_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE evidensapp_polystructures
  OWNER TO postgres;

-- Index: evidensapp_polystructures_geom_id

-- DROP INDEX evidensapp_polystructures_geom_id;

CREATE INDEX evidensapp_polystructures_geom_id
  ON evidensapp_polystructures
  USING gist
  (geom);
ALTER TABLE evidensapp_polystructures CLUSTER ON evidensapp_polystructures_geom_id;

With my query below it takes almost 15mins. to get the result.

-- Executing query:
SELECT  COUNT(evidensapp_polystructures.brgy_locat ) as High,evidensapp_polystructures.brgy_locat as Barangay, evidensapp_polystructures.municipali as Municipality
FROM  evidensapp_seniangcbr  INNER JOIN  evidensapp_polystructures  ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )  WHERE  evidensapp_seniangcbr.hazard  = 'Low' GROUP BY  evidensapp_polystructures.brgy_locat , evidensapp_polystructures.municipali
Total query runtime: 896688 ms.
23 rows retrieved.

It is not acceptable. So, how do I optimize it? I've done spatial indexing and clustering based on this site.

I suggest to calculate the intersection of the geometries beforehand only once and store it in an additional table evidensapp_seniangcbr_polystructures_intersection containing an id, evidensapp_seniangcbr_id pointing to evidensapp_seniangcbr.id and evidensapp_polystructures_id pointing to evidensapp_polystructures.id.

Then you can get rid of the geometry intersection in

INNER JOIN  evidensapp_polystructures ON ST_Intersects( evidensapp_seniangcbr.geom ,  evidensapp_polystructures.geom )````

and instead use a join using the ids

INNER JOIN evidensapp_seniangcbr_polystructures_intersection intersec ON evidensapp_seniangcbr.id = intersec.evidensapp_seniangcbr_id
INNER JOIN evidensapp_polystructures ON evidensapp_polystructures.id = intersec.evidensapp_polystructures_id

Off course this only works, if your data does not change. Otherwise you need a trigger doing the intersection and filling the evidensapp_seniangcbr_polystructures_intersection table.


Using variable for database name I am having difficulty in using a variable name for the database. The below script searches through all databases and finds database that match a certain condition. The matching databases are added to table #t.
I then loop through each database by setting the @DB1 to each database in turn. The problem I'm getting is when it comes to using the database name in @DB1. Any idea how I can get each of my matching databases to update?

create table #t (
    ID int IDENTITY,DBName sysname not null
)
go
EXEC sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.SDE_server_config'') is not null insert into #t (DBName) select ''?'''

Declare @RowCount int
Declare @Counter int
Declare @DB1 varchar(max)

set @RowCount = (Select Count(*) from #t)
set @Counter=0

While @Counter <= @RowCount
Begin
select @Counter = @Counter + 1

set @DB1 =  = (select DBName FROM #t WHERE ID = @COUNTER)

:SETVAR DatabaseName @DB1

USE $(DatabaseName);

UPDATE dbo.SDE_server_config
        SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;

End

You dont really need a loop and there is no need to use the undocumented sp_MSforeachdb as there is a better alternative to it - written by Aaron Bertrand.

You can use below tsql - Run in SSMS by pressing CTRL+T:

set nocount on

select 'if OBJECT_ID(''dbo.SDE_server_config'') is not null'+char(10)+'begin '+char(10)+' UPDATE ' + quotename(name) + '.dbo.SDE_server_config SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;' + char(10) + 'end '+char(10)+ 'else print ''there is nothing to update ...!;'''
from sys.databases
where database_id > 4
    and state_desc = 'ONLINE'

You can put above select statement in a variable and do and EXEC and save it in a .sql file and then use sqlcmd to just directly call that .sql file and run it on multiple servers.


Can I design a table that will use some columns just in certain times? I have a User with one or more saved Documents, but each Document has specific attributes in Brasil for example we have:

RG(General Register) have the following attributes:

A number
Expedition Date
Emission Org

CPF(Like a Social Security Number) have the following attributes:

A number

CNPJ(Like a National Registry of Legal Entities) have the following attributes:

A Name (of a company)
A Number

CNH(Driver's License) have the following attributes:

A Name (Of a person)
A Number
Expedition Date
Valid Date

Then I designed the following model:

FK Document Type will control what attributes of table will be null, for example ifFK Document Type appoint to CPF  the Valid Date attribute will not be used.

That's the correclty way to design a system with more than one type of document? I think that is not good make one table to each document type.

Instead of storing number of repeating DocType names you store the ids of the DocType descriptions from the reference table. This is a common practice called normalization.

The sparsed table with nullable attributes is good enough while the number of possible attributes isn't too big. Sometimes it's more efficient to store all the attributes in the aux table docid -- attrname -- value containing only not-null values and join them whenever you need.

SELECT ud.*, da.*
  FROM UserDocuments  AS ud
  JOIN DocsAttributes AS da ON da.doc_id = ud.id
 WHERE ud.ID = 12345      -- for certain document only

The only difference is that by join you'll get not the single row but the table like that:

            UserDocuments      |       DocsAttributes
 ------+----------+------------+--------+----------+-----------
    id | FK_users | FK_DocType | doc_id | attrname |     value
 ------+----------+------------+--------+----------+-----------
 12345 |      456 |          7 |  12345 | RGNumber |       9876
 12345 |      456 |          7 |  12345 |  RGEDate | 2019-05-01
 12345 |      456 |          7 |  12345 |   RGEOrg |       7777
 12345 |      456 |          7 |  12345 |   CPFNum |      88888

This table can be easily parsed on the app side.

An advantage of that approach is that you can add as many attributes as you want. Attributes names can be separated into the reference table for the normalization reasons.

The main disadvantage is that all attributes should be converted into the same type (VARCHAR() for example) and some functionality can be lost - like datetime arithmetics and/or timezone conversions.

Having a number of tables for each type of document isn't a good design.

There is no universal recipe and you have to decide what approach is suitable for you. Sometimes single table with hardcoded list of attributes is preferrable. Sometimes an expandable table of EAVs (entity-attribute-value) fits better.


What is meant by the RMAN Duplicate "noncurrent point in time"? When doing a RMAN DUPLICATE from backup connected to the target, how current does the duplication make the database when no until time is set?

The closest thing I can find in the documentation is the following statement:

  Restores and copies the duplicate data files and recovers them with
  incremental backups and archived redo log files to a non-current point
  in time.

How current is this non-current point in time? Does it apply to all the Archive Logs it has available to it at the start of the duplicate, or are they perhaps only available when it reaches the recovery part of the duplicate?

It's the moment of the last redolog archival, before some point in time during the RMAN duplicate command. You can test this easily, if you either manually provoke redolog switches (and thus subsequent archivals), or even better: write some script to provoke very frequent and unnecessary redolog switches. In which you perform some insert/update/delete, to further track things down.


Hive metrastore Database in Postgresql External We recently migrated hive metastore database from embedded postgresql(coming with cloudera hadoop) to external postgresql(standalone in vm server). After that we notified that there is increase of 5 to 10 seconds in query execution. May i know why it happens?. Postgresql config setting almost same except archive_mode which is on external postgresql, while in embedded postgresql it is off.

For me it looks like an issue with the VM and/or network and not Postgres, if it is true that nothing else has changed, but only the "dedication" to another machine.

We had a bad VM configuration - my VM was on the same rack as our Scientists run their stuff. So each time they did anything - all resources went there.

Look at your VM, how much RAM and Processing power does it really have?
If you can afford it - give your your postgres more RAM and config it to cache most of the DB (or all). And if you can afford it - buy a dedicated server for Postgres. You'll have one for below $1k (with couple of SSDs and 32GB RAM).

For the rest: listen to a_horse_with_no_name, and give us additional info (and hell, if you use pg91 - upgrade to pg96, if you use anything below pg96 - upgrade!)

-- should be a comment, but my reputation is not enough for it.


FK relationships between schemas in azure SQL don't work I'm having an issue with migrating my SQL 2014 database to an azure database.  I'm able to get everything migrated except for any FK relationships between tables that aren't the in same schema.  for example, 2 tables:

    CREATE TABLE [dbo].[User] (
        [UserGUID]          UNIQUEIDENTIFIER CONSTRAINT [DF_CoBRAUser_CoBRAUserGUID] DEFAULT (newsequentialid()) NOT NULL,
        [UserName]               NVARCHAR (400)   NOT NULL,
        [OrganizationName]       NVARCHAR (100)   NOT NULL,
        [Title]                  NVARCHAR (100)   NULL,
        [Department]             NVARCHAR (100)   NULL,
        [FirstName]              NVARCHAR (100)   NULL,
        [LastName]               NVARCHAR (100)   NULL,
        [UserImage]              VARBINARY (MAX)  NULL,
        [DateCreated]            DATETIME         NOT NULL,
        [DateLastModified]       DATETIME         NOT NULL,
        [MotherTongue]           NVARCHAR (50)    NULL,
        [LockoutEndDateUtc] DATETIME NULL,
[ClusterID]              INT              IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED ([UserGUID] ASC)
    );
GO
CREATE UNIQUE CLUSTERED INDEX [IX_User_ClusterID] ON dbo.User ([ClusterID])
GO
create schema [CConfigD]
    GO
    CREATE TABLE [CConfigD].[Alarm] (
        [AlarmGUID]   UNIQUEIDENTIFIER CONSTRAINT [DF_Alarm_AlarmGUID] DEFAULT (newsequentialid()) NOT NULL,
        [CreatedBy]         UNIQUEIDENTIFIER NOT NULL,
        [AlarmText]         NVARCHAR (1000)  NOT NULL,
        [DateCreated]       DATETIME         NOT NULL,
        [DateLastModified]  DATETIME         NOT NULL,
        CONSTRAINT [FK_Alarm_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([UserGUID])
    );

This works fine on my sql 2014 box, if i try to run a dacpac onto the azure database i get the following error:

  Msg 1776, Level 16, State 0, Line 8 There are no primary or candidate
  keys in the referenced table 'dbo.User' that match the referencing
  column list in the foreign key 'FK_Alarm_User'. Msg 1750, Level 16,
  State 1, Line 8 Could not create constraint or index. See previous
  errors.

is this just not allowed and i didn't know it?  Is there a setting i need to enable in order to allow this sort of foreign key?

The error message is self-explanatory. You need a primary key or unique index/constraint on the UserGUID column in dbo.[User].

ALTER TABLE dbo.[User]
  ADD CONSTRAINT PK_User
  PRIMARY KEY (UserGUID);

If that is there (you edited it in later), you shouldn't get that error message.

If you are only getting this error message when deploying a dacpac, can you post that dacpac somewhere? I can't make that code fail on Azure SQL Database by running it directly, but it's possible the dacpac has creation scripts listed in the wrong order, or the constraints are separated out into a different (later) step and aren't in the right order either, or the dacpac is only deploying changes to one schema and the other one hasn't been updated.


Cannot Find Job Associated Active Email Notifications We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via the email operator as setup on the associated server. The email is definitely sent from the SQL server in question as when I query the msdb in the sysemail_mailitems the email the recipients received definitely exists as shown in the screenshot below. All we want to do is change the recipients for the notification but only if we knew where....

It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.

---- Search Job Steps
SELECT
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,
JS.command

FROM
msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps JS
ON J.job_id = JS.job_id

WHERE
JS.command LIKE '%XXXXXX%'

---- Search Stored Procs, might want to loop through all databases

USE XXXXX

SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(O.schema_id) AS SchemaName
O.name AS SPName,
MO.definition AS SPDefinition

FROM
sys.sql_modules MO
INNER JOIN sys.objects O
ON MO.object_id = O.object_id

WHERE
MO.definition LIKE '%XXXXXX%'

Replace the WHERE clause with a short bit from the email itself that you have confidence would be in the body of the definition. It's not obvious from your post if some of that content in the email is dynamic or the same every time.


Postgres - Inject 0 values when rows do not exist I have 3 tables that I need for reporting:

    *dates*
date_sk | full_date  | day_number_of_month
1       | 2013-01-01 |       1
2       | 2013-02-01 |       1
3       | 2013-03-01 |       1
4       | 2013-02-02 |       2
5       | 2013-02-03 |       3

    *person*
person_sk   | person_id  | person_name
1           |   10       |   John
2           |   11       |   Bob
3           |   12       |   Jill

    *person_portfolio*
person_portfolio_sk | date_sk | person_sk | res_value | report_month
1                   |   1     |     1     |     15    |  2013-01-01
2                   |   1     |     2     |     10    |  2013-01-01
3                   |   1     |     3     |      1    |  2013-01-01
4                   |   2     |     1     |     30    |  2013-02-01

(imagine the 'dates' table filled with every date for the past 10 and next 10 years which includes every day of every month. My reporting is for a monthly level which is why i need day_number_of_month = 1 for the first of every month)

I have been struggling to find out, for comparison reporting purposes using a date range, how to replace no entries during that timeframe with 0 values for the person. Here is the query I have tried:

SELECT
 p.person_id,
 COALESCE(pp.res_value,0)::NUMERIC(16,2) AS res_value,
 pp.report_month
FROM person p
LEFT JOIN person_portfolio pp
ON p.person_sk = pp.person_sk
LEFT JOIN date d
ON d.date_sk = pp.date_sk
WHERE person_id IN ('10','11','12')
AND pp.report_month >= '2013-01-01' --From Date
AND pp.report_month <= '2013-05-01' -- To Date
AND d.day_number_of_month = 1
ORDER BY p.person_id DESC;

The output I want to return would end up being 15 rows total. 3 people x 5 months of data = 15 total rows. It should look like this:

person_id   | res_value | report_month
10          |   15      |   2013-01-01
10          |   30      |   2013-02-01
10          |   0       |   2013-03-01
10          |   0       |   2013-04-01
10          |   0       |   2013-05-01
11          |   10      |   2013-01-01
11          |   0       |   2013-02-01
11          |   0       |   2013-03-01
11          |   0       |   2013-04-01
11          |   0       |   2013-05-01
12          |   1       |   2013-01-01
12          |   0       |   2013-02-01
12          |   0       |   2013-03-01
12          |   0       |   2013-04-01
12          |   0       |   2013-05-01

but I am only getting these results:

person_id   | res_value | report_month
10          |   15      |  2013-01-01
10          |   30      |  2013-02-01
11          |   10      |  2013-01-01
12          |    1      |  2013-01-01

So basically... is there currently a feasible way that I could inject the 0 value rows into the results when there is no entry for the 'report_month' for a specific person(s)? I would appreciate any kind of help as I have been working on this for 2 weeks now trying to complete this report. Thanks!

You need to create a pseudo date range and join to it...  Like such:

select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date;

Once you have your series, you can join to it and use COALESCE to fill in 0's for where there is no data..

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
LEFT JOIN <something with a date> t1 on d.day = t1.day

If you need to join to something that DOESN'T have a date (for example, your "person" table above), then just join with the condition being "TRUE" (to make a cartesian join, so every person gets every day)

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
JOIN person p ON TRUE


T-SQL View -- How to 'pre-fetch' schema using scalar function, then populate using table query I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to return table data under a modified/prefixed schema.

For example, I have two tables (parent ACTION table and child ACTIONSUB table), both with many columns, and both with identical columns names --

ACTION

  RowGUID  UID         Ref  Name  LastUpdate  etc...

ACTIONSUB

  RowGUID  UID  fkUID  Ref  Name  LastUpdate  etc...

As part of our build process, we regenerate our library of table-access functions, one function per table. These functions can accept a delimited list of UID values, or will return all rows if passed NULL. As stated above, these functions' main purpose is to return all column data from the table with a modified/pre-fixed schema, so that we can JOIN them together into a single View. So, using our table-access functions, our tables above would return --

fn_TAF_ACTION(null):

  AC_RowGUID      AC_UID                    AC_Ref     AC_Name     etc...

fn_TAB_ACTIONSUB(null):

  ACSUB_RowGUID   ACSUB_UID   ACSUB_fkUID   ACSUB_Ref  ACSUB_Name  etc...

This approach works, until we have very large tables and/or join data from many tables (functions); then our View performance can seriously degrade.

Our views are simple query statements, usually with calculated columns added to the select list. A simple example --

CREATE VIEW vw_ActionSub2Action_01
AS
   SELECT
      acsub.*, ac.*,
      IsNull(ac.AC_Date1, ac.AC_Date2) AS [AC_ComboDate],
      ac.AC_Ref +'.'+ acsub.ACSUB_Ref AS [ComboRef], etc...
   FROM
      fn_TAF_ACTIONSUB(null) acsub
   LEFT JOIN
      fn_TAF_ACTION(null) ac ON acsub.ACSUB_fkUID = ac.AC_UID

I'm wondering if these Views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the View.

Is it possible?

Something like --

-- first, setup View schema using TAF functions to return empty data set
SELECT acsub.*, ac.*, '' AS [AC_ComboDate], '' AS [ComboRef], etc...
FROM fn_TAF_ACTIONSUB(0) acsub
LEFT JOIN fn_TAF_ACTION(0) ac ON acsub.ACSUB_fkUID = ac.AC_UID

-- then, populate the View by querying the tables
SELECT acsub.*, ac.*, IsNull(ac.Date1, ac.Date2) AS [AC_ComboDate], ac.Ref +'.'+ acsub.Ref AS [ComboRef], etc...
FROM ACTIONSUB acsub
LEFT JOIN ACTION ac ON acsub.fkUID = ac.UID

Is there a way to combine the above process into a single view definition?

PREFACE

I realize our above use of Views may be inherently non-performant; however, our system uses these Views, and performance is reasonable in most cases, so pushing a 're-design' of our processing logic is not feasible.

UPDATE 1

Our Table-Access-Functions (TAFs) are Table-valued Functions that return a table with a modified schema. They do not use BEGIN...END blocks. Here is an example...

CREATE FUNCTION [dbo].[fn_TAF_ACTION] (@UID varchar(max))
RETURNS TABLE
AS
RETURN (SELECT
  AC_RowGUID = RowGUID,
  AC_UID = UID,
  AC_Ref = Ref,
  AC_Name = Name,
  ...etc...
FROM Action
WHERE UID IN (SELECT IntValue FROM dbo.CsvToInt(@UID)) OR @UID IS NULL)
-- CsvToInt function simply allows us to pass a string of comma-delimited UIDs

I'm wondering if these views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the view.

No, this isn't possible with a view. You could do something broadly along these lines with a multi-statement function (MSUDF), but:

This would still require a static schema definition for the table variable; and
Careful design would be required to avoid terrible performance; and
All potential base query predicates must be supplied as optional parameters

A multi-statement function would potentially materialize the entire 'view' result set in a table variable, before any predicates in the base query were applied. If the result is large, this overhead will likely be prohibitive.

You could push predicates into the MSUDF using parameters, but then the function body becomes a mess of conditional predicates of the form column = @value OR @value IS NULL, which would require OPTION (RECOMPILE) in the MSUDF to optimize well.

Note that even with the best possible outcome, the users of the view will have to change their queries from view references to MSUDF references (with all parameters specified, DEFAULT or NULL passed for those that are not needed). This may not be workable.

All that said, you need to be certain what is causing the performance problem in the first place. The question does not supply an example of a problematic query plan, so some of what follows is educated guesswork:

There are two features of the current scheme that leap out at me:

The use of ISNULL and calculated columns prevents the optimizer from pushing base-query predicates down into the underlying in-line functions and base tables. You might get better results for the ISNULL part by rewriting the LEFT JOIN as a UNION ALL of an inner join and an anti-semi-join of the two tables. Properly written, this would expose only base column names, allowing the optimizer to push predicates successfully.
The in-line functions use an MSUDF to split the supplied CSV into row values. This is a pattern that often causes optimization problems, because the size and distribution of the result is unknown. A query plan that is optimal for a CSV of '1,2,3' is likely not optimal for a NULL CSV. Again OPTION (RECOMPILE) could help with this. It will simplify the compiled logic for the NULL case, avoid parameter-sniffing issues, as well as providing cardinality information for the MSUDF result. Distribution statistics will still not be available though.


log shipping backup fails every so often on only one server I was asked to change the DFS share for our log shipping on all our SQL servers (SQL 2008 on windows 2008) to point to the new share due to some IO issues on the previous one. But now, ocassionally, on one of the important production server, I get the following error every so often. Please note that I do not get this for every transacton file and also that the error seems to be occuring only from one of the server. Once the log shipping fails with this error, our DR logshipping is broken (ofcourse) But all the consecutive log shipping backups for this server just continue working fine.

Message
2012-06-18 09:40:35.37  * Error: Backup failed for Server 'XXXX'. (Microsoft.SqlServer.SmoExtended)
2012-06-18 09:40:35.37   Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
2012-06-18 09:40:35.37   Error: Write on "\\...FILE23.trn" failed: 2(The system cannot find the file specified.)
BACKUP LOG is terminating abnormally.
100 percent processed.(.Net SqlClient Data Provider) *
2012-06-18 09:40:35.39  ----- END OF TRANSACTION LOG BACKUP   -----

My questions:

Why would the backup for a transaction fail every few hours only? The consecutive backups just run fine.
What are the factors I need to check for this type of issue?
Why would I get an error as "cannot find the file" when it is supposed to be doing a write?

Any help on this is deeply appreciated.

Thanks in advance

Since your network drive is available most of the times and the backup job only fails randomly, I would first check the network. It could very well be the network issue. I would also look at the intervals the backup job fails, to see if there is a pattern to it.

Coming to restarting the logshipping part - the easier method is to restore the full backup and the standby server and reconfigure. But it depends on the size of your database, resources available etc


Oracle Illegal use of long datatype when moving tablespace to bigfile I am currently migrating all the tables in my database to a bigfile tablespace. I have written a script to do this and all tables are migrated successfully except one. I am given the error:

Error report:
SQL Error: ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"

And I am converting the tablespaces by:

alter table mytable move tablespace bigfile_tablespace;

Changing the column type isn't an option.

I have a script that creates all the tablespace from scratch using bigfile and it goes through fine with this table so I am little stuck on this...

To move the table without changing the column datatype I believe you'll need to export the table then import it into the new tablespace.

Note that if you're using Oracle 10g or later use expdp and impdp instead of exp/imp. (See: http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm)

You should use a command like:

  exp file=mytable.dmo log=mytable .log tables=mytable  feedback=1

To export the table. Then drop it in the old tablespace and create it in the new tablespace.

Then use:

  imp file=mytable.dmo log=mytable.log full=y feedback=1 ignore=y

To import the table. Hope this helps.

References:

http://www.orafaq.com/wiki/Import_Export_FAQ
http://psoug.org/reference/export.html
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm


ERROR: data type text[] has no default operator class for access method "gist" Whenever I try to try to create a GIST index on text[], I get the above error.

CREATE TABLE bar(f)
AS VALUES
  (ARRAY['foo','bar','baz']);

CREATE INDEX ON bar USING gist(f);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm trying to create an Exclusion Constraint on bar, so I need a GIST index.

smlar Extension

I was able to create a gist index on a text[] column using the smlar extension (written by Teodor Sigaev of text search fame)

# git clone git://sigaev.ru/smlar.git
# cd smlar/
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install
# psql

postgres=# create extension smlar;
postgres=# create index on bar using gist(f _text_sml_ops);


How to update multiple rows without using JSON I have a situation. In this example I want to update multiple notification definitions. Incoming data is very dynamic array object by user controlled. Sometimes new notifications added or removed.
My way of doing this is delete all unused notification definitions before insert new values. notifications.new_notification_definition procedure If given values are already exists in database update if not insert it. In this way I can update multiple rows. But I have a problem. It doesn't work. I don't know why sometimes new values are not inserted. Sometimes it does... Please help me what is best way to update multiple rows ? I have more problems like this.

DELETE FROM nd USING `notification_definitions` AS nd
WHERE
    `origin`     = 'table'       AND
    `type`       = 'date'        AND
    `table_name` = 'table_13' AND
    NOT EXISTS (
       SELECT NULL FROM $table WHERE `notification_definition_id` = nd.`id`
    );

UPDATE `notification_definitions` SET `is_active` = 'no' WHERE `table_name` = 'table_13';

CALL `notifications.new_notification_definition`('table_13', ?, ?, 123, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 2, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 7, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 30, ?, ?);

Procedure notifications.new_notification_definition

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `notifications.new_notification_definition`(
IN `in_table_name`          VARCHAR(100),
IN `in_primary_field_label` VARCHAR(250),
IN `in_field_label`         VARCHAR(250),
IN `in_days`                INT(11) UNSIGNED,
IN `in_color`               VARCHAR(20),
IN `in_message`             VARCHAR(250)
)
BEGIN

-- Select old definition
SELECT `id` INTO @id FROM `notification_definitions`
WHERE
    `origin`              = "table"                  AND
    `type`                = "date"                   AND
    `table_name`          = `in_table_name`          AND
    `primary_field_label` = `in_primary_field_label` AND
    `field_label`         = `in_field_label`         AND
    `days`                = `in_days`
LIMIT 1;

IF @id IS NULL THEN
INSERT `notification_definitions` SET
    `origin`              = "table",
    `type`                = "date",
    `table_name`          = `in_table_name`,
    `primary_field_label` = `in_primary_field_label`,
    `field_label`         = `in_field_label`,
    `days`                = `in_days`,
    `color`               = `in_color`,
    `message`             = `in_message`,
    `created_at`          = NOW(),
    `updated_at`          = NOW();
ELSE
UPDATE `notification_definitions` SET
    `color`      = `in_color`,
    `message`    = `in_message`,
    `is_active`  = 'yes',
    `updated_at` = NOW()
WHERE id = @id
LIMIT 1;
END IF;

END ;;
DELIMITER ;

EDIT, Fixed problem

While I was posting this question at night I was tired and didn't think well. Now I have a new question and still remaining main question too. Problem was here
SELECT id INTO @id

After I changed
SET @id = (SELECT id query) it solved, I don't know why? My guess is @id is never NULL after set. Last value is still used even row wasn't found. Can you explain to me why ?

I changed my strategy to update multiple rows.
First Update old rows field is_active = 'no' and INSERT ON DUPLICATE KEY UPDATE new values with is_active = 'yes' as James suggested. And lastly DELETE rows did not updated. Is there any better way to update multiple rows ?

I want to answer myself and close this question. Because I think I solved this problem. I appreciated James's comment for gave me an idea.
In my current solution looks like this.

UPDATE `accessable_menu` SET `is_active` = 'no' WHERE `user_role_id` = $USER_ROLE_ID;

INSERT INTO `accessable_menu`(`user_role_id`, `menu_id`, `is_active`)
VALUES (inputs..., 'yes')
ON DUPLICATE KEY UPDATE `is_active` = 'yes';

DELETE FROM `accessable_menu` WHERE `user_role_id` = $USER_ROLE_ID AND `is_active` = 'no';

Update old rows field is_active = 'no' and insert new values on duplicate key update with is_active = 'yes'

Then delete all rows didn't updated. It might be better performance than delete all rows and insert again. Because it deletes only few rows.


The issue of sp_MScheckIsPubOfSub stored procedure We have SQL Server 2008 R2 Merge replication set up.

We have 20+ push subscriptions.

Once in a while I get the following error message during the sync:

Error messages: The merge process could not set the status of the subscription correctly.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963)
Could not find stored procedure 'dbo.sp_MScheckIsPubOfSub'.
(Source: MSSQLServer, Error number: 2812)

I have found the code for the mentioned SP & ran it against the subscriber database & everything worked fine.

My question is: why is this stored procedure getting deleted every now and then by itself ? And how I can troubleshoot this issue?

Please check for orphaned rows for deleted subscriptions in sysmergesubscriptions and delete them.  For example, the subscriber that this is failing on may have duplicate entries in sysmergesubscriptions, one being orphaned from a previously deleted subscription.

This has been the cause of this error and solution for clients of mine in the past, ymmv.  I hope this helps.


Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP Please refer to the diagram in the link below.  While looking at my goals.

http://i.stack.imgur.com/GkVWb.png

GOAL:

Get PostgreSQL 9.1 running on 3 terminals on the 'on site' location.
'on site' have 'streaming replication' running between these DB's 'on-site' locations.
'on site' have fail-over to one of the 'on-site' 'slaves'.
between 'master' 'on site' and 'slave data warehouse' I'll just pull log files.

I've read a bunch about Linux but haven't found much about Windows XP. All the machines 'on site' have the same hardware.  Unfortunately I can't use linux in the 'on site' environment.

Again been googling but not finding much anyone know of a good tutorial(s) to get this type of setup pieced together!

I'm going to keep looking and if I find anything I'll make sure to post it back here.

Thanks!

This is an old question but figured I would provide some pointers here.  The first place to start, naturally, is with the documentation, which is excellent.  It will work exactly as written on any supported OS.

This being said, if you are on Windows, some of the specifics of PostgreSQL are sufficiently UNIXy to be confusing.  Its thus helpful to remember what streaming replication is in PostgreSQL, which is a continuous disaster recovery which never completes.  So in addition to the replication docs you want to read all about point in time recovery and incremental backups.

The key aspects of replication is the recovery.conf which configures the PostgreSQL system to basically continually recover to now() with logs provided by another server.  If you think about it from that perspective, the docs and tutorials make more sense.  You do need to know where the data directory of Postgresql is however.


How can I avoid this circular relation? I based my account/user table design following this article. So I have set my table relationships to look something like this:

User <- Memberhip -> Group -> Account

With this design, a user is tied to group via a membership table. A group belongs to an account. so far so good.

Now I want to add a new resource catalog that belongs to an account, but can be accessed by a group. A group may have different access levels to different catalogs; it may have full edit access to one catalog, but read-only to a different one. A catalog may be accessed to by different groups. Since this is a many-to-many relationship, a pivot table is required which will also determine the groups access level to each catalog:

I tried putting the catalog table directly under a group, but with this approach, the account is not the direct owner of the catalog but the group is. its harder to give access to different groups. I could also create new groups for every new catalog, but then existing groups can belong to only one catalog.

How can I fix this design so that is no circular relation in it?

In my opinion, you can't remove the relationships you have modeled.

The general reason for avoiding such "circular" models is that in 2 years, when the next person maintains this database, they will think that they can go from group to catalogue via account or role - and technically, they can, but the route they choose determines the meaning of the relationship.

If they go via account, it is saying "This group's account owns the following catalogues".

If they go via role, it is saying "This group is permitted to access these catalogues, with these role permissions".

Both are valid relationships.

In my opinion, set really good column names that describe the relationships. If your DBMS supports adding comments/documentation on DB objects, do so (even though this feature seems little used in practice) - it is very hard to lose the documentation when it is part of the object definition (as opposed to some wiki page or text document).

Consider adding views called 'GroupCatalogues' and 'AccountCatalogues', which give a fairly good indication of what they will show, and encapsulate the joining rules in those views so that correct data is shown if your developers access the views rather than the tables directly.

Consider whether you can revoke direct access to the tables to enforce developers access the views.


How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL? According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some performance issues.

What is a good way to determine if there are uncommitted transactions (bonus points if there is a way to know if the connection they're on is idle or not)?

Thanks very much!

If you want to see how many idle connections you have that have an open transaction, you could use:
select *
from pg_stat_activity
where (state = 'idle in transaction')
    and xact_start is not null;

This will provide a list of open connections that are in the idle state, that also have an open transaction.
Beware that the state column is present only in PostgreSQL 9.2 and above.
Having said that, I cannot recreate an open connection in the idle state that has an open transaction.  Perhaps someone else can provide details on how to do that.


wal_level both hot_standby and logical I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication.

Now, I want to replicate only a few tables from the master server to an analytic server using pglogical. For this, we need to enable the logical wal_level. So how can I enable it without breaking the existing replication?

This is what the documentation says about the topic:

  wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

From the text it's not overly clear, but logical is the highest level of all, meaning it includes everything the replica level does.  That means you can safely set wal_level to logical, it won't break your existing replication.


Fixed query plan for a stored procedure I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation.

The problem is that I cannot find any working example (nor did I manage to figure it out by myself) for setting a fixed plan for a stored procedure, or a statement inside the procedure.

I've read lots of blog posts, but all of them just copy/paste examples from microsoft docs.

Anyone more creative/expirienced?

Here is an example of forcing a plan for a statement inside a procedure using a plan guide:

USE tempdb;
GO

CREATE PROCEDURE dbo.test
AS
BEGIN

    SET NOCOUNT ON;

    SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;

END
GO

-- Use Ctrl+M to capture the execution plan
-- You will see loop joins used
EXEC dbo.test;

Now generate the execution plan for this statement with a forced join strategy:

SELECT svp.name AS login_name, dbp.name AS user_name
FROM sys.database_principals AS dbp
INNER JOIN sys.server_principals AS svp
    ON dbp.sid = svp.sid
OPTION (HASH JOIN);

Copy the execution plan XML and paste it to the query window. Replace any occurence of ' (single quote) with '''' (four single quotes).

Your final parameter for the sp_create_plan_guide procedure will be in the form:

@xmlPlan = 'OPTION (USE PLAN N''your plan goes here'')'

Now you can see why four quotes: escape once to put the XML inside a string literal, escape twice to put the XML inside a string literal inside another string literal.

Make sure that the XML plan is pasted right after OPTION (USE PLAN N'' : any whitespace or newline will make it invalid.

DECLARE @planXML nvarchar(max) = 'OPTION (USE PLAN N''your modified XML plan here'')';

EXEC sp_create_plan_guide
    @name = 'UseHash',
    @stmt = 'SELECT svp.name AS login_name, dbp.name AS user_name
    FROM sys.database_principals AS dbp
    INNER JOIN sys.server_principals AS svp
        ON dbp.sid = svp.sid;',
    @type = 'OBJECT',
    @module_or_batch = 'dbo.test',
    @hints = @planXML
GO

-- Use Ctrl+M to capture the execution plan
-- You will see hash joins used
EXEC dbo.test;


Index Generation in Postgres We have a database with a single table made up of around 700 million entries.
We update the database by adding new entries on a server then transfer the server to the production server using pg_dump:

pg_dump -c database > /tmp/database_gen

(by the way we use postgres 8.4)
we export the database to the production server by using psql.
The pg_dump generated file has instruction on how to create and fill the table.

The problem is with the index creation. Postgres fills the table then spend days creating the index. That was OK, until postgres couldn't create the index anymore due to the fact that it has no more diskspace because it uses a lot of disk space to for the temporary files for sorting and creating the index. Normaly the database takes around 200GB but during the index creation the used disk space increases to 600GB then after the creation it goes back to 200 GB.

My question is : can we create the index in several steps, like create the index for half the table then add the rest of the table and update the index?

Has anyone had the same issues ?

Thanks

If you create the index before loading the table, the time taken to load the data will be significantly increased.

pre load:

create table my_table1(val integer);
create index my_index1 on my_table1(val);
insert into my_table1(val) select generate_series(1,100000) order by random();
Time: 31755.858 ms

post load:

create table my_table2(val integer);
insert into my_table2(val) select generate_series(1,100000) order by random();
Time: 15344.130 ms
create index my_index2 on my_table2(val);
Time: 4073.686 ms

If you are ok with that, with pg_restore you can:

Load just the schema using --schema-only
Create the index with --index
Load the data using --data-only

Of course "Buy more storage" may well be the best answer here...


fulltext_stoplists to database_principals issue When I run a DBCC check against the database, it informs me that there is an issue with the sys.fulltext_stoplists object.  It appears that the principal_id in stoplists does not have a relationship with database_principals:

What would be the best way to address the issue? It is difficult to modify data in system tables.

The sys.fulltext_stoplists doc says the column principal_id is the ID of the database principal that owns the stoplist. You can specify it as one of the Arguments of the CREATE FULLTEXT STOPLIST command:

AUTHORIZATION owner_name Specifies the name of a database principal to
own of the stoplist. owner_name must either be the name of a principal
of which the current user is a member, or the current user must have
IMPERSONATE permission on owner_name. If not specified, ownership is
given to the current user.

It appears the owner of the object no longer exists. You can assign a new owner using the ALTER AUTHORIZATION like this:
ALTER AUTHORIZATION ON FULLTEXT STOPLIST::CustomStopword TO SomeExistingUser;

After that, run the DBCC check again to see if everything is ok.


While Loop - Parent/Child Tree I'm trying to recursively loop through and return all child_id's that have the root element of 9.

The structure:

->9
->->8
->->->17
->->22
->->->11

Parent Child Link Table:

+----+-----------+----------+
| id | parent_id | child_id |
+----+-----------+----------+
|  1 |         9 |        8 |
|  2 |         8 |       17 |
|  3 |         8 |       33 |
|  4 |         8 |       18 |
|  5 |         9 |       22 |
|  6 |        22 |       11 |
|  7 |        22 |        4 |
|  8 |         3 |        5 |
+----+-----------+----------+

Procedure (so far):

BEGIN

DECLARE x INT(11)

SET x = 0;
SET @elements = "";
SET @node = _root_; -- 9
SET @child_count = count_children(@node) -- function returning the child count of @node;
SET @children = get_children(@node); -- function returning the child id's of @node

-- check IF node has children
WHILE x <= @child_count DO
    SET @elements = CONCAT(@elements,x,',');
    SET x = x + 1;
END WHILE
SELECT @elements;

END

Desired Output: [8,17,33,18,22,11,4]

Question: How can I modify my procedure to be able to return all child_id's of the parent?

try this,

declare @t table( id int, parent_id int,child_id int)
insert into @t values
(1 , 9 ,  8 ),(2 , 8 , 17 )
,(3 , 8 , 33 ),(4 , 8 , 18 )
,(5 , 9 , 22 ),(6 ,22 , 11 )
,(7 ,22 ,  4 ),(8 , 3 ,  5 )

declare @parentinput int=9

select t.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput

union
select t1.* from @t t
left join @t t1
on t.child_id=t1.parent_id
where t.parent_id=@parentinput


view current session roles for another session In Oracle, I can view the currently set roles for my current session using the query SELECT * FROM SESSION_ROLES.

Is there any way that I can see this information for another session?

SESSION_ROLES view shows list of roles assigned after you makes session in instance as those roles granted to your connected users. you can view other users granted roles using below:

select   * from   dba_role_privs;

If you want to know list of roles of particular user, use below query:

select   * from   dba_role_privs where grantee='USER_NAME';

Please note that you must have DBA privilege to view above. and you can't see list of roles using currently connected others session. you can only see what role the can be use after getting connected.


mysqldump still fails even with mysqldump --column-statistics=0 mysqldump still fails even when using mysqldump --column-statistics=0

# mysqldump --column-statistics=0

Just returns

# mysqldump: [ERROR] unknown variable 'column-statistics=0'

Am I missing something obvious here?

EDIT: Even trying to get the version gives me an error.

mysqldump -v
mysqldump: [ERROR] unknown variable 'column-statistics=0'

Mysql version is:

# mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1221
Server version: 5.7.27-0ubuntu0.16.04.1-log (Ubuntu)

Which mysql:

# which mysql
/usr/bin/mysql

It's an Ubuntu server running ServerPilot. The ultimate aim is to run the SiteGround WordPress migration plugin, but it's failing with the Column Statistics error in it's log.

Also tried from the comments:

root@vultr:~# mysqldump --no-column-statistics
mysqldump: [ERROR] unknown variable 'column-statistics=0'
root@vultr:~# mysqldump --skip-column-statistics
mysqldump: [ERROR] unknown variable 'column-statistics=0'
root@vultr:~#

and then even mysqldump on it's own errors:

# mysqldump
mysqldump: [ERROR] unknown variable 'column-statistics=0'

And what would be the use of this parameter?:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump --column-statistics=0 sakila -u root -p >d:\temp\stats.sql
Enter password: ********

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump  sakila -u root -p >d:\temp\no-stats.sql
Enter password: ********

C:\Program Files\MySQL\MySQL Server 8.0\bin>d:

D:\TEMP>dir *stats.sql
 Volume in drive D is HDD
 Volume Serial Number is D46B-804B

 Directory of D:\TEMP

17-11-2019  15:41         3.387.169 no-stats.sql
17-11-2019  15:41         3.387.169 stats.sql
               2 File(s)      6.774.338 bytes
               0 Dir(s)  62.853.836.800 bytes free

D:\TEMP>fc stats.sql no-stats.sql
Comparing files stats.sql and NO-STATS.SQL
***** stats.sql

-- Dump completed on 2019-11-17 15:41:41
***** NO-STATS.SQL

-- Dump completed on 2019-11-17 15:41:58
*****

D:\TEMP>


SQL Server Troubleshooting Replication and Long-Running fn_dblog query I am trying to get (pull) Transactional Replication working again for a subscriber.

Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to Subscriber tab.

In the distributor to subscriber history, I see 'concurrent snapshot for publication 'XX' not available'. In the publisher to distributor history, I see the Log Reader Agent initializing and then waiting for a response from the server (every 5 minutes the message is logged again).

Ultimately the execution of sp_replcmds fails.

I think there may be many unreplicated transactions which are causing the Log Reader Agent to hang. Following Troubleshooting LogReader Timeouts

I ran dbcc opentran and then attempted to run

select  [Transaction ID], count([Transaction ID]) as 'Count log entries'
from::fn_dblog('42006:31381:2',null)
where [Description] = 'REPLICATE'
group by [Transaction ID]

This query did not return any results after over 40 minutes. Moreover, when I just run SELECT count(*) FROM ::fn_dblog(NULL, NULL) I am not getting results after a long time (20 minutes).

I am starting to think there is something up with the size of my transaction log, or some kind of corruption, or something.

Any help would be greatly appreciated.

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post


PostgreSQL connection URI fails, but parameters work Running postgreSQL 13.2 with ssl on.
I need to connect to a database from a third party application that requires a connection URI, but it is not working. So I tested connecting with psql using two different formats with the same credentials. One worked and one didn't.
The following command logs me into mydb as user myuser:
# psql -U myuser -d mydb -h 127.0.0.1 -p 5432 -W
Password:
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydb=>

However this command fails:
# psql postgresql://myuser:MYPASSWORD@127.0.0.1:5432/mydb?sslmode=require
psql: error: FATAL: password authentication failed for user "myuser"

I am using exactly the same credentials. I've verified it more than 10 times. Removing "sslmode=require" does not fix the problem.
My pg_hba.conf file contains:
host   mydb   myuser   127.0.0.1/32   password

I made it the first line in my pg_hba.conf file, so it can't be getting hung up on any other line.
What am I doing wrong?

The password I was using happened to contain %d0. That works fine when logging in using the parameters. But if you use a connection URI, it is interpreted as a percent-encoded value. Just by coincidence, my password contained the percent sign followed by a valid hex number.
Changing the password, or properly percent-encoding the connection URI, is the solution to this problem. There are several characters that have to be percent-encoded in a connection URI.


why i am getting The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo' When I am only trying to create a table my "DBA" gave reader, writer, "ddladmin" to the database and I am getting this error when trying to create a basic table:

  The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

Can somebody please let me know the answer.

There are two possibilities here:

Your table create script includes an attempt to send an email
Your DBA has placed a DDL trigger on the database to send an email notification when objects are created.

In the first instance, confirm that there are no attempted email sends from your script.

For the second the DBA might not have set the permissions correctly on the trigger itself, and so it is attempting to send an email using your credentials.
Work with the DBA to either grant you permission on msdb..sp_send_dbmail so that emails will flow, or to fix up the trigger.


What's a good client tool to access Oracle RDBMS? I'm looking for a good client tool to replace "Oracle SQL Developer" (the tool I currently use) which supports:

Oracle DB 10g, 10gR2, 11g
Running SQL code
Debugging SQL code (with walk-through... click error, jumps to error line)

Do you know of one, preferably free?

I haven't found anything 'free' that works as well as Oracle SQL Developer. In the past it was pretty buggy and frustrating. I am on a 3.0 Early Adopter release now and very happy with the improvements. I am a heavy user and have had no crashes in the 3 months since going to this release. I wasn't sold on the interface early on but I am familiar with it now and very productive with it. I also make great use of the User Defined Reports -- all the SQL Queries I had floating in text documents for troubleshooting I wrap into their own custom reports.

Toad is an option... but can be pricey. It satisfies your want-list. If you want to pay for all the bells and whistles and have to support a lot of differenct databases it might be worth it for you.

A lot of developers  here use AllRoundAutomations "PL/SQL Developer" tool, so many in fact that we bought a site license. Single seats can be had for US$180 per seat. It fits your requirements. I have not personally used the debugger but it is a mature product, I would expect it to work as advertised.


Composite index of three columns in MySQL and WHERE by 1st and 3rd column. Index usage Let's consider a table with 3 varchar columns and a composite index on all of them in the order of (a,b,c):

CREATE TABLE `test` (
      `a` VARCHAR(255) NOT NULL,
      `b` VARCHAR(255) NOT NULL,
      `c` VARCHAR(255) NOT NULL,
      INDEX(`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

Will the composite index be used in case of WHERE a = const AND c = const condition? Here it is stated it couldn't be used, however, if we launch EXPLAIN for such a table/query on a DB Fiddle we see a in possible_keys and keys, so looks like it is going to be used at least for the first part of the condition.

UPDATE: I think I used wrong wording in my initial question. I would like to know if the composite index could be used in case of WHERE a = const AND c = const and to object to the following clause in the link above:

  Notice that if you have a composite index on (c1,c2,c3), you will have
  indexed search capabilities on one the following column combinations:

  (c1) (c1,c2) (c1,c2,c3)

I do realize that it could not be used in case of (c2,c3), but I'm asking about (c1,c3)

There are few factors that affects MySQL decision whether to use an index or no.

This composite index can be used in some cases, like in the fiddle you provided; and in another cases, it won't be used.

Add the following to your fiddle to see the different possibilities:

explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf'
delete from test where a='dsfasdfsdfasd';
explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf';
explain select * from `test` where `a` = 'ABC' and `c` = 'dfsadfsdf';

Notice that in the first scenario, the index was chosen because it will avoid doing a full table scan.
In the second scenario, since any way a full table scan will be done, it is more expensive to use the index, so the optimizer decided not to use the "possible" key.

In first scenario, field a's cardinality is 2. i.e. there are two different values of this field in the whole table, and they are 50% each. This means that using the index, only 50% of the table will be scanned, so it is better to use the index.

In the second, the cardinality of field a is 1. i.e. only one distinct value exists in the table, and this is the value I am asking for in the where condition. So, all rows will be scanned. The optimizer decides that is it cheaper to scan that table than to scan the index and the table.

Extra piece of info: In the last statement, I am checking a against a value that doesn't exist. The index will be used in this case.

UPDATE

Composite index on (c1, c2, c3) would sometimes be used if you query based on (c1, c3). It will use the first part (c1) for the speed (to seek the first entry). (c3) part will not increase the speed of seeking, but will make fetching data faster, as it will be fetched from the index in this case.

HTH


set right permission to get connection ip for a user mapped to login I have a problem with properly settings the rights permissions for my user to get access to connections ips.

I know that, to get the conn ip I must set the VIEW SERVER STATE to the login that I use to connect to db. I know hot to do it and it works ok. The problem is when I want to execute a stored procedure that is executed as other user. This user (mapped to the login with the above permission) doesn't have permission to get the ip. What I have missed?

A script to reproduce:

CREATE LOGIN TestLogin WITH PASSWORD=N'TestLogin',
                                    DEFAULT_DATABASE=[master],
                                    CHECK_EXPIRATION=OFF,
                                    CHECK_POLICY=OFF

CREATE USER TestUser FOR LOGIN TestLogin

use master
GRANT VIEW SERVER STATE TO TestLogin

With this I can log to the database and execute the query:

SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID

But when I add the procedure I can't execute it:

CREATE PROCEDURE TestProc
WITH EXECUTE AS 'TestUser'
AS
BEGIN
    SELECT client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID
END

GRANT EXECUTE ON OBJECT::dbo.TestProc TO TestUser

All I got is

Msg 297, Level 16, State 1, Procedure TestProc, Line 5
The user does not have permission to perform this action.

sys.dm_exec_connections will require the permissions you've stated of VIEW SERVER STATE. However, by looking at the code you only ever want to get the IP of the session the user is already in, so there is no need for the over-provisioning. Using CONNECTIONPROPERTY will fulfill the same request and NOT require any extra permissions.

I'd make it something as such:

CREATE PROCEDURE TestProc
AS
BEGIN
 SELECT CONNECTIONPROPERTY('client_net_address')
END

GRANT EXECUTE ON dbo.TestProc TO TestUser

You can then test:

USE YourDatabase
GO

EXECUTE AS LOGIN = 'TestLogin'
 EXEC dbo.TestProc
REVERT