LISTEN / NOTIFY privileges I have a single postgres database, with two users; Alice and Bob.

I would like to be able to do a NOTIFY alice_channel 'sensitive data' without Bob being able to sneakily LISTEN in just by guessing that the channel name is 'alice_channel'.

In practice the channel names are very hard to guess, but this is security through obscurity at best.

Am I correct in believing that there is no way to prevent a database user from using (abusing) LISTEN & NOTIFY? i.e. there does not appear to be any associated privileges that can be granted or revoked.

Is this a dead end?

There are no permissions on LISTEN and NOTIFY. It would not have made sense to have any until support for notify payloads was introduced in more recent versions.

If you want access control, create a table with the information that you want and then send a NOTIFY that's empty or has nothing but a primary key for that table in it. SELECT the row(s) of interest from the table, which can have access control, to get the sensitive data. This used to be the only way to use LISTEN and NOTIFY to send specific data before payloads were added.


DB and operating system packages updates is it ok to apply security updates to the packages of the operating system that hosts an Oracle Database? Or is it better to not touch them, since the system is perfectly working? I have an Oracle Database installed on a Red Hat Enterprise Linux 6, on which some packages have security updates available, and I am not sure about what to do.

You should apply patches. Clone your database, apply the patches to the clone and see if it affects its operation. Test, test, test. But security patches aren't issued on a whim. Are they mandatory? Recommended or optional patches? But at some point you will have patches you do need to apply.


Covering Index Changes Execution Plan but is not used I have the following occasionally slow running query:

SELECT C.CustomerID
FROM dbo.Customers C WITH (NOLOCK)
WHERE C.Forename = @Forename
    AND C.Surname = @Surname
OPTION (RECOMPILE)

CustomerID is the Primary Key on the Customers table. The Customers table also has the following two non-clustered indexes:

CREATE NONCLUSTERED INDEX idx_Forename ON Customers (Forename ASC)
CREATE NONCLUSTERED INDEX idx_Surname ON Customers (Surname ASC)

When I run the query with both a surname and forename entered the query optimiser uses the index 'idx_Surname' as in the following execution plan:

This query takes over two minutes to complete for this particular search and finds no results. For the values entered @Forename has no matches in the Customers table while @Surname matches 31,162 records. When I only search by the @surname the 31,162 records return in under a second with the following plan:

In an attempt to optimise the query for searches containing both Forename and Surname I added the following covering index:

CREATE NONCLUSTERED INDEX idx_Surname_Covering ON dbo.Customers (Surname) INCLUDE (Forename)

The query with both Forename and Surname then returns in less than one second. However, the covering index is not used in the actual execution plan:

So,

Is the covering index required or is there a better way to improve the performance and
Why does the additional covering index cause the change of index in the actual execution plan from idx_Forename to idx_Surname?

p.s. the query above is an isolated example, when in use either surname or forename or both may be searched for and the Customers table also includes other searchable columns with their own indexes. This detail was not considered relevant to the question so I have not included it.

1) Is the covering index required or is there a better way to improve the
  performance

Best index

The best index would be the most covering, selective index for the queries accessing the table.

Take for example in your table, you have 50000 rows where the firstname = John , but only one where the last name = 'McClane', Should you create the index with John as the First key value or McClane?

Answer:

It depends... If you are always searching for John Mcclane, then its an open and shut case of indexing the lastname first. But what if there are also queries searching for Constanthin Smith? You could have over 5000 Smiths, but only five Constanthin's.

As a result, it depends on your queries and what you are seeking on, how much they are executed, ....

If your queries are going to always seek on both the firstname and lastname, then it is the simple case of picking the more selective one as the first key column.
Keeping in mind that read performance improving should stay bigger than write performance declining.

Ofcourse, nobody restricts you to create two indexes, one with (firstname,lastname )and one with (lastname,firstname).

(Your update / insert / delete statements might).

Not considering filtered indexes and whatnot, the best index for your example would be:

CREATE NONCLUSTERED INDEX idx_Forename_Surname ON dbo.Customers (Forename,Surname)

  2) Why does the additional covering index cause the change of index in
  the actual execution plan from idx_Forename to idx_Surname?

I don't think that this was just because of the index, but because of the statistics created as a result of the index creation.

Even though these stats are the same as the one in idx_Surname, my guess is that they are with a bigger sample rate (100), as they are created with 'fullscan'.

If an auto update stats occured on the statistics created by the index idx_Surname, they might have had a smaller sample rate, resulting in bad estimates (E.g. 1% Sample rate).

You could try removing the idx_Surname_Covering index and its statisics and updating the stats on dbo.Customers with  100% sample rate (fullscan) to test this theory.

UPDATE STATISTICS dbo.Customers WITH FULLSCAN

Which hopefully changes your plan to use the better seek.

If this is why your query changed, and updating the stats with fullscan on maintenance windows is not a viable option, you could change the sample rate


How to use GROUP_CONCAT for a column and COUNT(*)? I get the ids of a query by GROUP_CONCAT.
SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...)

How can I merge this query with a COUNT(*) group by
SELECT ArticleID,COUNT(*) FROM TagMap WHERE Tag IN(...) GROUP BY ArticleID

to get GROUP_CONCAT(ArticleID) with order of COUNT(*)?
In other words, in the first query, I want to make GROUP_CONCAT(ArticleID) order by the number of tags found for each ArticleID.

This would helpful, Give a try once
SELECT GROUP_CONCAT(ArticleID)
   ,COUNT(1) AS CNT
FROM (SELECT Tag
         ,ArticleID
         ,COUNT(1) AS CNT
  FROM TagMap Tag IN (...)
  GROUP BY Tag
           ,ArticleID) tmp
  GROUP BY Tag
  ORDER BY cnt


How do i know a table size and row counts for each month, for the past six months? Using sql server can i get the row count and size of the table as well as the database size for the past six months?

Unless you are tracking tables, table size can not be known. To get current size:

sp_spaceused 'TableName'

To get row count:

Select count(*) from 'TableName'

To get row count with a timestamp field:

    Select
          DATEPART(YEAR, 'timestamp_field') AS 'Year',
          DATEPART(MONTH, 'timestamp_field') AS 'Month',
          DATEPART(DAY, 'timestamp_field') AS 'Day',
          COUNT(*) AS Check

    from
         'TableName'
    group by
         DATEPART(DAY, 'timestamp_field'),
          DATEPART(MONTH, 'timestamp_field'),
          DATEPART(YEAR, 'timestamp_field')
ORDER BY  'Year',
          'Month',
          'Day'

Database growth:

SELECT
[database_name] AS DB,
DATEPART(day,[backup_start_date]) AS Day,
DATEPART(month,[backup_start_date]) AS Month,
DATEPART(year,[backup_start_date]) AS Year,
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(m,[backup_start_date]),DATEPART(d,[backup_start_date]),DATEPART(y,[backup_start_date]),backup_start_date;

for database size details. Check this link
Create table size tracking report


Massive MSreplcommands table, 2 billion rows, cleanup job not deleting commands I have a runaway distribution database that is 640gb in size. I have narrowed it down to the msreplcommands table which is holding 2 billion rows.

I have checked all publications and found 2 of them having immediate sync set to 1. I changed this to 0. Reran the cleanup job and it deleted about 240K rows only. Ran this:

SELECT *  from MSrepl_transactions (nolock) where entry_time < getdate() -5;

Found entries as old as 30 days.

The problem is I am running out of space very fast and all delete attempts lock the msreplcommands table and thus lock replication. We are a 24/7 shop and I have no server downtime possible. Is there another way to delete this amount of data without locking replication?

Hi T.H, Thanks for the suggestion. I found that link earlier and decided to try it. I've done some investigating and found very old entries in the MSrepl_transactions table going back 32 days using the following query:

SELECT distinct cast(entry_time as date) as entry_time, publisher_database_id, xact_seqno, count(publisher_database_id) FROM [distribution].[dbo].MSrepl_transactions group by entry_time, publisher_database_id, xact_seqno order by entry_time

The process at https://blogs.msdn.microsoft.com/chrissk/2009/12/29/how-to-resolve-when-distribution-database-is-growing-huge-25gig/ can help with this.

To prevent it happening in future you may need to look at the configuration of the Distribution clean up: distribution job.


select one row based on a string I have a table like this:

and I want to have the output like this:

If I use case to this, I'm not getting a unique value for each pc. I used:
case
when software like '%7zip%' then software
else 'no'
end;

and I'm getting for computer_id something like this:

I don't know what function to use and if I can do this with 'case'.
Thank you!

Below is an example using a CTE with conditional aggregation.
WITH inventory_per_computer AS (
    SELECT Computer_id, SUM(CASE soft WHEN '7Zip' THEN 1 ELSE 0 END) AS Installed
    FROM dbo.SoftwareInventory
    GROUP BY Computer_id
    )
SELECT Computer_id, CASE Installed WHEN 0 THEN 'no' ELSE '7Zip' END AS Software
FROM inventory_per_computer
ORDER BY Computer_id;


What is the meaning of & in where clause What does & indicate in below query? What is the meaning of it?

select id, name
from test
where num & 1 = 1;

It's a bitwise and operator.

See the documentation here.

Wikipedia goes into lots of detail on bitwise operations.

Logically, the query you give:

select id, name
from test
where num & 1 = 1;

... will only return odd numbers, due to the lower bit in any integer always being 1 when a number is odd.


Error 18456 Serverity 14, State 38 I have an application that logs into SQL Server 2014 that uses a single login to access the database when a user opens up the application.  In other words I have 6 users using the same application with different logins, but the application itself is logging into SQL server with a single login.

One of my users is having issues.  When she logs into application the first time, she gets in fine, but if she tries to reopen it, it fails.  If she reboots her computer, she can log in one time again.  (Not acceptable)

The errors I'm seeing in the SQL Error logs are

  Error 18456; Severity 14; State 38
  Error 18451, Severity 14; State 1
  Error 18056; Severity: 20, State 23
  Error: 18456; Severity: 14 State: 23

One location for finding out about many of these states is a post by Aaron Bertrand at:

https://sqlblog.org/2011/01/14/troubleshooting-error-18456

The error you are asking about are:

Error: 18456; Severity: 14 State: 23
Access to server validation failed while revalidating the login on the
connection.
Error 18456; Severity 14; State 38Failed to open the database specified in the login properties. (or)  Cannot open database "" requested by the login. The login failed.
Error 18056; Severity: 20, State 23

This is apparently related to connection pooling when the client could not reuse a spid.   If this is the same problem you can look at this Microsoft Connect item, but it has been closed as "Not Reproducible."

https://connect.microsoft.com/SQLServer/feedback/details/468478/sql-server-2008-periodically-does-not-accept-connections

and then:

Error 18451, Severity 14; State 1

SQL Server is in the process of shutting down. During shutdown, only members of the sysadmin fixed server role can make new connections.

This suggests that your installation needs some examination and perhaps updated to a more recent patch level.


Reset Key Names to Defaults? I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names.

In SSMS is there any (preferably automatic) way to reset the name of a key to what the default name would be if it were newly created?

There's no way to do this automatically but you can query the catalogue views to find constraint names not in the desired pattern then generate the desired script that way.

Something like

DECLARE @Script NVARCHAR(MAX);

WITH FK
     AS (SELECT *,
                'FK_' + OBJECT_NAME(parent_object_id) + '_' + OBJECT_NAME(referenced_object_id)                  AS provisionalName,
                ROW_NUMBER() OVER (PARTITION BY parent_object_id, referenced_object_id ORDER BY create_date) - 1 AS RN
         FROM   sys.foreign_keys),
     Combined
     AS (SELECT name COLLATE DATABASE_DEFAULT        AS name,
                OBJECT_SCHEMA_NAME(parent_object_id) AS schemaName,
                provisionalName + CASE
                                    WHEN RN = 0 THEN ''
                                    ELSE CAST(RN AS VARCHAR)
                                  END                AS provisionalName
         FROM   FK
         UNION ALL
         SELECT name,
                OBJECT_SCHEMA_NAME(parent_object_id)       AS schemaName,
                type + '_' + OBJECT_NAME(parent_object_id) AS provisionalName
         FROM   sys.key_constraints)
SELECT @Script = x.rename.value('.', 'NVARCHAR(MAX)')
FROM   (SELECT 'exec sys.sp_rename ''' + QUOTENAME(schemaName) + '.' + QUOTENAME(name) + ''', ''' + provisionalName + ''';
'
        FROM   Combined
        WHERE  provisionalName <> name
        FOR XML PATH(''), TYPE) x(rename);

PRINT @Script;
--EXEC (@Script);


Updating data when data is from an external source A government organization provides data in a series of files which can be re-engineered and imported into a database. The files are updated monthly. I have been able to reengineer the database in MySQL and import the data. I've also created additional tables on the database and added my own data. My issue is I'm unsure as to how to handle updates to the database.

How do I import the data every month and sync it with the database from the previous month, which would include the tables that I added?

If the imported data has artificial ids (such as from AUTO_INCREMENT), and your tables are referencing that data via those ids, and each "update" changes those ids, then you need to rethink.

If the imported data has "natural" keys that do not change with each "update", then you are safe.

If the "update" is really a complete set of data, then to reload, do this for each table

DROP TABLE IF EXISTS old;   -- from last month
LOAD DATA .. INTO new ... -- or whatever load mechanism works
RENAME TABLE real TO old, new TO real;

Keep the old tables around until next month -- you may have to revert, or you may want to compare old to real to see, or react to, what changed.  See LEFT JOIN to discover what is added/removed.  Use a plain JOIN to see what values have changed.


Query Pane Docking in pgAdmin3 I just started using pgAdmin3. I do not like having to have a new window when I want to run ad hoc SQL queries. Is there a way to do dock it within the top level app and actually show in the main space (to the right of the connection hierarchy view on the left)?

There is no way to do that. The only panes available in the main window are listed in the menu under View: Object browser, SQL pane, Tool bar.

You can ...

... select a passage in the query tool, then hit F5 to execute just the selection. This way you can have various queries on a page for ad-hoc calls.

... Copy SQL from the main window to query tool automatically with this option.


SQL Server job failure I have a job that executes every 15mins on SQL Server 2008 and it comes up with the following error at least once in two days. But, it runs successfully the next time it runs.  The account that is running the stored procedure has db_owner permission on the database. Any idea why?

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229).  The step failed.

Check this link: http://msdn.microsoft.com/en-us/library/ms191188.aspx
It allows you to enable Ole Automation Procedures. This change would affect the entire instance. This is turned off by default due to security.

Other Ole procedures are here: http://msdn.microsoft.com/en-us/library/ms190501.aspx


Large table with geospatial data - slow queries during updates I have a large table with millions of rows (geospatial data) which is getting updated several times per day.

Selects by lat/lon are very fast (0.02-0.04 seconds) most of the time, but during updates the same queries can take as long as 5 seconds. Profiling shows them hanging either in the "sending data" or "statistics" state. Of course there is a lot of I/O going on during these mass updates which are also quite time-consuming because every row needs to be updated.

The storage engine used is InnoDB, though I had to keep buffer pool size at 128M (a larger buffer pool appears to put more strain on the server during update/cleanup operations which even results in failed/stalled connections).

Any suggestions for tuning? I wondered if maybe there is a way to specifically limit I/O write operations or prioritize Selects over Updates/Inserts (with the latter ones not being that time-critical).

Table structure:

+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| vt                | datetime    | NO   | PRI | NULL    |       |
| lat               | double      | NO   | PRI | NULL    |       |
| lon               | double      | NO   | PRI | NULL    |       |
[lots of data fields]

my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=100
thread_cache_size=1
long_query_time = 1

open_files_limit=8192
table_cache=3000
table_open_cache=4000
table_definition_cache = 3000
query_cache_limit=256k
query_cache_size=100M
query_cache_type=1
query_cache_min_res_unit = 2k
thread_cache_size=5
read_buffer_size=1M
sort_buffer_size=2M
join_buffer_size=2M
max_heap_table_size=4G
key_buffer_size=3G
tmp_table_size=4G
innodb_file_per_table=1

You can use INSERT DELAYED to avoid blocking SELECTs:

  When a client uses INSERT DELAYED, it gets an okay from the server at
  once, and the row is queued to be inserted when the table is not in
  use by any other thread.

  Another major benefit of using INSERT DELAYED is that inserts from
  many clients are bundled together and written in one block. This is
  much faster than performing many separate inserts.

Unfortunately, there is no equivalent for UPDATE. You can do REPLACE DELAYED (which first deletes the existing row if one exists and then inserts a new row), but that's probably not useful to you.

A few other maybe useful optimizations:

innodb_flush_method= O_DIRECT

innodb_flush_log_at_trx_commit= 2

Maybe consider if the geometry types (POINT) would be useful instead of your lat, lon doubles. Geometry types are indexable in InnoDB as of MariaDB 10.2.

Looking at your configuration, I think your query cache is set quite large. (Though it might make sense in your specific case, I don't know.) This can result in degraded performance due to lock contention, as the query cache is locked while it's updated.

I can see how the query cache might make sense in your use case if you're not writing to the database all that frequently. Note however that MySQL is retiring support for the query cache in MySQL 8.0. I don't know what the fate of the query cache is on the MariaDB side.


What index to use with lots of duplicate values? Let's make a few assumptions:

I have table that looks like this:

 a | b
---+---
 a | -1
 a | 17
  ...
 a | 21
 c | 17
 c | -3
  ...
 c | 22

Facts about my set:

Size of the whole table is ~ 1010 rows.
I have ~ 100k rows with value a in column a, similar for other values (e.g. c).
That means ~ 100k distinct values in column 'a'.
Most of my queries will read all or most of the values for a given value in a, e.g. select sum(b) from t where a = 'c'.
The table is written in such a way that consecutive values are physically close (either it's written in order, or we assume CLUSTER was used on that table and column a).
The table is rarely if ever updated, we're only concerned about read speed.
The table is relatively narrow (say ~25 bytes per tuple, + 23 bytes overhead).

Now the question is, what kind of index should I be using? My understanding is:

BTree My issue here is that the BTree index will be huge since as far as I know it will store duplicate values (it has to, since it can't assume the table is physically sorted). If the BTree is huge, I end up having to read both the index and the parts of the table that the index points to. (We can use fillfactor = 100 to decrease the size of the index a bit.)
BRIN My understanding is that I can have a small index here at the expense of reading useless pages. Using a small pages_per_range means that the index is bigger (which is a problem with BRIN since I need to read the whole index), having a big pages_per_range means that I'll read a lot of useless pages. Is there a magic formula to find a good value of pages_per_range that takes into account those trade-offs?
GIN/GiST Not sure those are relevant here since they're mostly used for full-text search, but I also hear that they're good at dealing with duplicate keys. Would either a GIN or GiST index help here?

Another question is, will Postgres use the fact that a table is CLUSTERed (assuming no updates) in the query planner (e.g. by binary searching for the relevant start/end pages)? Somewhat related, can I just store all my columns in a BTree and drop the table altogether (or achieve something equivalent, I believe those are clustered indices in SQL server)? Is there some hybrid BTree/BRIN index that would help here?

I'd rather avoid using arrays to store my values since my query will end up less readable that way (I understand this would reduce the cost of the 23 bytes per tuple overhead by reducing the number of tuples).

BTree

  My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end up having to read both the index and the parts of the table that the index points too...

Not necessarily — Having a btree index that is 'covering' will be the fastest read time, and if that is all you want (ie if you can afford the extra storage), then it is your best bet.

  BRIN

  My understanding is that I can have a small index here at the expense of reading useless pages. Using a small pages_per_range means that the index is bigger (which is a problem with BRIN since I need to read the whole index), having a big pages_per_range means that I'll read a lot of useless pages.

If you can't afford the storage overhead of a covering btree index, BRIN is ideal for you, because you have clustering already in place (this is crucial for BRIN to be useful). BRIN indexes are tiny, so all the pages are likely to be in memory if you choose a suitable value of pages_per_range.

  Is there a magic formula to find a good value of pages_per_range that takes into account those trade offs?

No magic formula, but start with pages_per_range somewhat less than the average size (in pages) occupied by the average a value. You are probably trying to minimize: (number of BRIN pages scanned)+(number of heap pages scanned) for a typical query. Look for Heap Blocks: lossy=n in the execution plan for pages_per_range=1 and compare with other values for pages_per_range — i.e. see how many unnecessary heap blocks are being scanned.

  GIN/GiST

  Not sure those are relevant here since they're mostly used for full text search, but I also hear that they're good at dealing with duplicate keys. Would either a GIN/GiST index help here?

GIN may be worth considering, but probably not GiST — however if the natural clustering really is good, then BRIN will probably be a better bet.

Here is a sample comparison between the different index types for dummy data a bit like yours:

table and indexes:

create table foo(a,b,c) as
select *, lpad('',20)
from (select chr(g) a from generate_series(97,122) g) a
     cross join (select generate_series(1,100000) b) b
order by a;
create index foo_btree_covering on foo(a,b);
create index foo_btree on foo(a);
create index foo_gin on foo using gin(a);
create index foo_brin_2 on foo using brin(a) with (pages_per_range=2);
create index foo_brin_4 on foo using brin(a) with (pages_per_range=4);
vacuum analyze;

relation sizes:

select relname "name", pg_size_pretty(siz) "size", siz/8192 pages, (select count(*) from foo)*8192/siz "rows/page"
from( select relname, pg_relation_size(C.oid) siz
      from pg_class c join pg_namespace n on n.oid = c.relnamespace
      where nspname = current_schema ) z;

name               | size    | pages | rows/page
:----------------- | :------ | ----: | --------:
foo                | 149 MB  | 19118 |       135
foo_btree_covering | 56 MB   |  7132 |       364
foo_btree          | 56 MB   |  7132 |       364
foo_gin            | 2928 kB |   366 |      7103
foo_brin_2         | 264 kB  |    33 |     78787
foo_brin_4         | 136 kB  |    17 |    152941

covering btree:

explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=3282.57..3282.58 rows=1 width=8) (actual time=45.942..45.942 rows=1 loops=1)                                                   |
|   ->  Index Only Scan using foo_btree_covering on foo  (cost=0.43..3017.80 rows=105907 width=4) (actual time=0.038..27.286 rows=100000 loops=1) |
|         Index Cond: (a = 'a'::text)                                                                                |
|         Heap Fetches: 0                                                                                |
| Planning time: 0.099 ms                                                                                |
| Execution time: 45.968 ms                                                                                |

plain btree:

drop index foo_btree_covering;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=4064.57..4064.58 rows=1 width=8) (actual time=54.242..54.242 rows=1 loops=1)                                     |
|   ->  Index Scan using foo_btree on foo  (cost=0.43..3799.80 rows=105907 width=4) (actual time=0.037..33.084 rows=100000 loops=1) |
|         Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.135 ms                                                                                |
| Execution time: 54.280 ms                                                                                |

BRIN pages_per_range=4:

drop index foo_btree;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21595.38..21595.39 rows=1 width=8) (actual time=52.455..52.455 rows=1 loops=1)                                   |
|   ->  Bitmap Heap Scan on foo  (cost=888.78..21330.61 rows=105907 width=4) (actual time=2.738..31.967 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Rows Removed by Index Recheck: 96                                                                                |
|         Heap Blocks: lossy=736                                                                                |
|         ->  Bitmap Index Scan on foo_brin_4  (cost=0.00..862.30 rows=105907 width=0) (actual time=2.720..2.720 rows=7360 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.101 ms                                                                                |
| Execution time: 52.501 ms                                                                                |

BRIN pages_per_range=2:

drop index foo_brin_4;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21659.38..21659.39 rows=1 width=8) (actual time=53.971..53.971 rows=1 loops=1)                                   |
|   ->  Bitmap Heap Scan on foo  (cost=952.78..21394.61 rows=105907 width=4) (actual time=5.286..33.492 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Rows Removed by Index Recheck: 96                                                                                |
|         Heap Blocks: lossy=736                                                                                |
|         ->  Bitmap Index Scan on foo_brin_2  (cost=0.00..926.30 rows=105907 width=0) (actual time=5.275..5.275 rows=7360 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.095 ms                                                                                |
| Execution time: 54.016 ms                                                                                |

GIN:

drop index foo_brin_2;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :--------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21687.38..21687.39 rows=1 width=8) (actual time=55.331..55.331 rows=1 loops=1)                                    |
|   ->  Bitmap Heap Scan on foo  (cost=980.78..21422.61 rows=105907 width=4) (actual time=12.377..33.956 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Heap Blocks: exact=736                                                                                |
|         ->  Bitmap Index Scan on foo_gin  (cost=0.00..954.30 rows=105907 width=0) (actual time=12.271..12.271 rows=100000 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.118 ms                                                                                |
| Execution time: 55.366 ms                                                                                |

dbfiddle here


Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting While writing a query other day a thought came to me and have stuck in my mind.

What is preferable, first checking if a value for a unique column exists and then inserting  or insert and let db raise unique constraint error? Will it even matter?

Edit: As suggested below in answer that this issue depends on database I am adding tag postgresql.

Let the DB raise an error.

Testing first isn't safe for concurrency because you'll get a collision eventually because 2 threads may pass the "NOT EXIST" and both will try to write. This applies to both "READ COMMITTED" and MVCC/Snapshot lock strategies.

You can use lock hints to force isolation, but you reduce performance.

I call this the JFDI pattern (SO link). For "update if exists" then see this here: Need Help Troubleshooting Sql Server 2005 Deadlock Scenario. These are SQL Server. MySQL has INSERT IGNORE which handles this gracefully. Not sure about the rest


Same function in SELECT and WHERE clause Beginner question:

I have an expensive function f(x, y) on two columns x and y in my database table.

I want to execute a query that gives me the result of the function as a column and puts a constraint on it, something like

SELECT *, f(x, y) AS func FROM table_name WHERE func < 10;

However this doesn't work, so I will have to write something like

SELECT *, f(x, y) AS func FROM table_name WHERE f(x, y) < 10;

Will this run the expensive function twice? What's the best way to do this?

Let's create a function that has a side effect so that we can see how many times it is executed:

CREATE OR REPLACE FUNCTION test.this_here(val integer)
    RETURNS numeric
    LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE WARNING 'I am called with %', val;
    RETURN sqrt(val);
END;
$function$;

And then call this like you do:

SELECT this_here(i) FROM generate_series(1,10) AS t(i) WHERE this_here(i) < 2;

WARNING:  I am called with 1
WARNING:  I am called with 1
WARNING:  I am called with 2
WARNING:  I am called with 2
WARNING:  I am called with 3
WARNING:  I am called with 3
WARNING:  I am called with 4
WARNING:  I am called with 5
WARNING:  I am called with 6
WARNING:  I am called with 7
WARNING:  I am called with 8
WARNING:  I am called with 9
WARNING:  I am called with 10
    this_here
──────────────────
                1
  1.4142135623731
 1.73205080756888
(3 rows)

As you see, the function is called at least once (from the WHERE clause), and when the condition is true, once again to produce the output.

To avoid the second execution, you can do what Edgar suggests - namely wrap the query and filter the result set:

SELECT *
  FROM (SELECT this_here(i) AS val FROM generate_series(1,10) AS t(i)) x
 WHERE x.val < 2;

WARNING:  I am called with 1
... every value only once ...
WARNING:  I am called with 10

To further check how this works, one can go to pg_stat_user_functions and check calls there (given track_functions is set to 'all).

Let's try with something that has no side effect:

CREATE OR REPLACE FUNCTION test.simple(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT sqrt(val);
$function$;

SELECT simple(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE simple(i) < 2;
-- output omitted

SELECT * FROM pg_stat_user_functions WHERE funcname = 'simple';
-- 0 rows

simple() is actually too simple so it can be inlined, therefore it does not appear in the view.  Let's make it un-inlinable:

CREATE OR REPLACE FUNCTION test.other_one(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT 1; -- to prevent inlining
SELECT sqrt(val);
$function$;

SELECT other_one(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE other_one(i) < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    13 │      0.218 │     0.218

SELECT *
  FROM (SELECT other_one(i) AS v FROM generate_series(1,10) AS t(i)) x
 WHERE v < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    23 │      0.293 │     0.293

As it looks, the picture is the same with or without side effects.

Changing other_one() to IMMUTABLE changes the behaviour (maybe surprisingly) to the worse, as it will be called 13 times in both queries.


Trying to create a table with foreign keys and its missing a few rows This is what I have so far:

CREATE TABLE GRADE
(
    COURSE_NO   VARCHAR2(8),
    STUDENT_ID  VARCHAR2(15),
    GRADE   VARCHAR2(3),
    PRIMARY KEY(COURSE_NO,STUDENT_ID) ENABLE
);

ALTER TABLE GRADE
ADD FOREIGN KEY (COURSE_NO)
REFERENCES COURSE (COURSE_NO)ENABLE;

ALTER TABLE GRADE
ADD FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENT (STUDENT_ID)
ON DELETE CASCADE ENABLE;

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES ('CS100','123','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','234','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','456','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS100','567','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','678','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','789','C');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','890','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('CS200','901','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','012','B');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','013','A');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','014','C');
INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE100','015','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE220','016','B');           -- This one doesn't work

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
       VALUES('EE200','017','A');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('EE200','018','B');           -- this one either

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
       VALUES('MA220','019','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
     VALUES('MA220','020','B');

INSERT INTO GRADE(COURSE_NO,STUDENT_ID,GRADE)
    VALUES('MA220','021','B');           -- This insert doesn't work

The foreign keys are defined on course_no and student_id. Assuming the error is a foreign key violation (big assumption, please post the full error in future questions) it must be one of those two at fault. Other rows reference course EE200 so it's not that; so student 018 doesn't exist in the student table. Similarly MA220 so student 021 is missing. With EE220 / 016 there are no comparators so either or both could be missing. Given your record I'd suggest the student.

This is basic stuff and fundamental to the concept of foreign keys. You should read up on the concept and ensure you write data in a sequence that respects the constraints you have defined. Otherwise you are in for a world of pain.


Are toast columns compressed also in shared_buffers? Reading here I cannot find a clear answer:
http://www.postgresql.org/docs/9.1/static/storage-toast.html

I need to know if setting storage to EXTERNAL for a particular column, I can gain a little performance. Most of my data is retrieved from SHARED_BUFFERS.

I'm curious to know if decompression is done for every query working on that field.

Yes, they are.

shared_buffers is block-oriented, and stored in the same format as on-disk.

This is generally good for performance, since the decompression is very fast, and the compression means that more fits in shared_buffers. The only case it might hurt is if you have enough storage to fit the whole dataset in shared_buffers uncompressed too.


Prevent Inserts without Column List We have a database running on SQL 2008 R2, in 2000 Compatibility mode.

Is there a way to configure the database so that implicit insert statements (i.e. those without a column list) are invalid?

I want to stop developers writing scripts like this:

Insert into foo
values (2, 3)

and instead use

Insert into foo (Val1, Val2)
values (2, 3)

especially within stored procedures, but across the board if at all possible.

No, there is no such configuration available.

Of course, you are not the only one concerned about style. You might find some posts by Alexander Kuznetsov helpful in pursuing your overall reliability goals.

One of his posts is: Developing Low-Maintenance Databases

https://www.simple-talk.com/sql/database-administration/developing-low-maintenance-databases/

He has an online book entitled: Defensive Database Programming

https://www.simple-talk.com/books/sql-books/defensive-database-programming/

But SQL Server has no 'strict checks' although Erland Sommarskog has proposed that something should be done about this.  (No traction yet, though.)

http://www.sommarskog.se/strict_checks.html

So, perhaps you can create a process that examines the text of a stored procedure, but it will not be simple.  And you can have rigid code review.


how to limit amount of rows in jsonb_agg & jsonb_build_object I have this query:
SELECT jsonb_agg(jsonb_build_object('me_id', me_id)) from message

and I get the following result:

However I want to limit the amount of objects in the array to a certain number like 3.
How can I do this? Adding LIMIT 3 is not working.

LIMIT 3 will work just fine if you add it in the right fashion, that is before the rows are aggregated:
SELECT jsonb_agg(jsonb_build_object('me_id', me_id))
FROM (SELECT me_id FROM message LIMIT 3) AS subquery;


How to create new table with same constraints and indexes? I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table.

I know, I would like to create another table with same structure and values as well keys and indexes also.

create table Dummy (id integer ,name varchar(20),salary integer
Constraint PK_Con_id primary key(id))

insert into Dummy values(11,'AAA',1000);
insert into Dummy values(12,'BBB',2000);
insert into Dummy values(13,'CCC',3000);
insert into Dummy values(14,'DDD',4000);

select * from Dummy;

create nonclustered index IX_Name
on Dummy(Name)

Now I am creating Dmy table but Keys and Constraints not reflect in Dmy table in SQL Server 2008 R2.

SELECT *
INTO Dmy
FROM Dummy

SELECT INTO is not going to do this for you, because while it maintains the column names and data types, it doesn't keep a lot of the other aspects of the table, such as constraints, indexes, etc. The only thing it really keeps outside of the columns is the IDENTITY property if one of the columns has it.

Right-click the original table in Object Explorer, and select Script Table As > Create To > New Query Window. You should have a CREATE TABLE script for the original table, including all the constraints, etc. Note that you may have to set some of these settings (Tools > Options > SQL Server Object Explorer > Scripting) to true in order to get all of the table attributes you want, so this may take some trial and error:

Now, just hand-modify that script to specify the new table name, and make sure to adjust all of the constraint and index names such that they are unique (how much work this will be will depend on whether you have an established naming convention or if you just take what SQL Server will throw at you).


Cannot connect to MS SQL 2008 R2 by DBVisualizer. "Native SSPI library not loaded" error I try to connect to MS SQL 2008R2 database by DBVisualizer.
I use jTDS driver, but following error occurs

An error occurred while establishing the connection:

Long Message:
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Details:
   Type: java.sql.SQLException
   SQL State: 08S01

I added ntlmauth.dll to jre/bin but it didn't help.
I have java 1.6.0_25.

This isn't a really widely used tool for SQL Server database administrators.  For better answers, I'd contact DBVisualizer's support.


SQL Server error log monitoring In SQL server instance(s) I manage; I see a lot of activity and messages in SQL server error log for e.g. backup or transaction log backup completed etc.. We have quite a few databases in one instance and amount of error log messages is sometimes quite a few to dig into.

As an accidental DBA, What approach or script can I use to filter the error log for date range specified or finding or filtering troubling errors out of lots of informational messags?

I meant to ask, is there is a T-SQL script or DMVs you can use to filter the error log?

I suggest that you circumvent reading the error log directly and instead implement SQL Agent Alerting.  Under this methodology, you'd simply create alerts for, say, when SQL Server throws an error (I recommend separate alerts for severity levels 19 -25) and tell SQL Server to email you when the error occurs.

You can then generally ignore error log except for specific troubleshooting situations.

General details are here. The details for setting up SQL Agent alerts specifically using SSMS are on TechNet are here and are the same in Books On-Line here.

Hope this helps,
-Kevin

@kekline


Cannot update parent rows with trigger on the same table I currently have the following two tables in my database

CREATE TABLE folders (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  parentId NUMBER,
  CONSTRAINT pk_folders PRIMARY KEY (id),
  CONSTRAINT fk_folders_parent FOREIGN KEY(parentId) REFERENCES folders(id)
);

CREATE TABLE files (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  folderId NUMBER NOT NULL,
  CONSTRAINT fk_folders FOREIGN KEY (folderId) REFERENCES folders(id)
);

There are also row triggers on the files table to update the bytes column of the folder when a row in files is inserted, deleted, or updated. This is in order to keep track of the total size of the folder. The triggers work fine in this case and successfully update the folder with the total bytes of all files contained in that folder.

CREATE OR REPLACE TRIGGER ari_files
AFTER INSERT ON files
FOR EACH ROW
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + :NEW.bytes
  WHERE f.id = :NEW.folderid;
END ari_files;

I would like to also have the folders update their parent as well so that each folder would keep the sum of its files + any files contained in subfolders. The problem I am running into is when I try to add a trigger on the folders table to update the bytes value for its parentId. I'm getting a mutating table error (ORA-04091) because the triggers on the folders table are also updating the folders table (although it is always going to be a different row and there won't be any loops in the hierarchy)

CREATE OR REPLACE TRIGGER aru_bytes_folders
AFTER UPDATE OF bytes ON folders
FOR EACH ROW
WHEN (OLD.parentId IS NOT NULL)
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + (:NEW.bytes - :OLD.bytes)
  WHERE f.id = :OLD.parentId;
END aru_bytes_folders;

Is there something that can be done to get this to work?

The closest that I've gotten so far is creating a temporary table and having row triggers populate the table and then having a statement trigger to read from the temporary table and process the update on its parent (which would trigger more updates). The problem that I ran into with that was that the temporary table was containing previous updates that had been processed already and the database was getting stuck in a loop.

It looks like what you want to do is actually have a summary of the sum of used bytes in each folder, right? I believe that triggers in Oracle are not the best choice of technology to provide you this functionality. This could be better modeled using a view, or even better, a materialized view.

The problem with triggers is that they will serialize the DML to the table and will not scale too well. Imagine a situation where you are updating and inserting lots of rows... for each one of them you'll have an update to the folder table.

If you model your problem with a view instead you would not incur the overhead of the extra DML on the folder table. A simple query like this should do the trick:

SELECT fd.name AS folder_name,
       SUM(f.bytes) AS folder_size
  FROM folders fd, files f
 WHERE fd.id = f.folderId
 GROUP BY fd.name;

You may then make this query persistent by creating a view (the sum will run every time you query the view) or a materialized view with fast refresh on commit (in this case the materialized view stores the calculation as a pre-computed table, useful if the table is huge and the sum is costly).

The materialized view "fast refresh on commit" feature delegates to Oracle the task of recalculating the agregation every time the underlying table(s) changes.

The Data Warehousing Guide has an in dept explanation on this feature:
https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG015


Permission for sequence in another schema Postgres 9.3
Debian 7.0

I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same user needs usage on the sequence of the primary key of this table, but it says "ERROR: permission denied for sequence"

The original table and its sequence belongs to schema A. This users's schema B has an insert-able view of this table T. I cannot grant usage on schema A for this user, otherwise he will be able to see the names and definition of all my tables.

The question is: Is there some way to create some kind of view for this sequence so he can call nextval() and currval()? The goal is making this sequence usable for this restricted user without giving him access to the main schema where the sequence actually belongs.

This can be done.
The column default for your serial primary key is typically defined as:
ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id
SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass);

Two options:
1. Move sequence (my preference)
to the public schema - or any schema with sufficient privileges:
GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:
ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:
GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).
2. Function wrapper with SECURITY DEFINER
If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):
CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...
Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.


How to enable windows authentication on server that is not part of domain We have an SQL server instance running on a standalone server (not part of the organizations domain network). Is there any way that we can use windows authentication to logon to sql server without having to add the remote server to the domain. I hope my question is clear, what is want is to be able to logon to sql server using windows authentication from my client, but I am not able to add windows users in sql server because the server is not part of the domain and so cannot identify the domain users that I am trying to add. Is there any way to get around this?

Thanks in advance.

Plan answer: No.

Let me explain:

Windows Authentication is just that, authenticating with a Window's login. So if you are dealing with a workgroup computer where you are on a domain it will not work, because that server can only authenticate a local account.

If you are talking about connecting with SSMS, you can try the runas coomand through command prompt and use the "netonly" parameter. I use this for connecting from workgroup to domain.


How can I send an email when objects get invalidated? I have couple stored procedures and a package.

Whenever this stored procedure and package goes invalid, I need to send an email.

How can I achieve this?

Can you please give me an idea how this can be done.

The scope of this question requires some work on your part but as a rough guide:

get an email package.  You can roll your own using UTL_SMTP (Pre Oracle 10) or it's succesor UTL_MAIL (Oracle 10 onwards).  However this is such a basic functionality that many people have written and shared their own versions. For Oracle 11 and beyond do not forget to allocate permissions for your database to access the ports required.
create a package to manage your errors.  Today you want to see when a package has been invalidated, tomorrow it will be something else. Plan for the future.... You would need to create a procedure with a cursor along the lines of
SELECT owner,
   object_type,
   object_name,
   status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
open the cursor, when you find something, use the email package to mail who you want
finally, if you find a problem, fix the problem: use the handy code here: http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php to recompile the package

Edit: the original poster asks how to track incidents.  More information is needed such, Oracle version, what kind of incidents, how often do you want to receive notifications.


How to install RODBC driver for SQL Server 2016 R Services I'm trying to get started with R services on SQL Server 2016. I was following this example from MS on youtube.

2 min 22sec into the vid John shows a reference to library(RODBC)... which I don't have. So I have downloaded the RODBC driver from CRAN

According to the documentation:

  RODBC is simple to install, and binary distributions are available for
  Windows from CRAN

But it provides no further details about how to install it. It mentions an ODBC Driver Manager but not much more than that.

So.. How do I install RODBC?

Do I unzip it to a specific path?
Is there a utility or tool required to install it?
Do I run a script?

To install R packages in SQLServer 2016, you first have to launch R GUI (somewhere like D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe).

If your server is connected to the web, you can launch a :

install.packages("RODBC")

Otherwise, download the package (see https://cran.r-project.org/web/packages/RODBC/index.html), put it your server, for instance in a folder D:\R_Packages, and launch the R command :

setwd("D:/R_Packages")
install.packages("RODBC_1.3-14.zip", repos=NULL)

This second method is not very convenient in case of package dependencies : you'll have to download / install manually all the packages needed....

Anyway, in you want RODBC to access SQLServer databases, maybe you don't need it.
Have a look at sp_execute_external_script procedure which help you to connect directly R and SQLServer.


SQL Join too slow on postgresql 9.0 vs postgresql 9.1 There is a big difference in execution time, altough the ammount of table data are pretty much the same in both databases.
On production DB - Postgresql 9.0 DB hosted by webprovider it takes 29seconds, versus of Postgresql 9.1 installed on virtual machine.

Also seems when I remove the second condition in ON section it runs fast but when there are both ON conditions it runs super slow.

a) I've tried to change type from varchar(255) to text and vice versa for type reference_number_1 so it would match document_number type varchar(255).

b) Tried to REINDEX command for both tables

SELECT dro.document_number, dro.document_year, dro.document_date, dro.process_id
FROM data_recent_orders dro
LEFT JOIN data_tracking_system dts
ON
dts.reference_number_1_metaapp = dro.document_number AND
dts.reference_number_1_year_metaapp = dro.document_year
WHERE
dro.document_type = 'Sofortrechnung'
LIMIT 10000;

Postgresql 9.0

Postgresql 9.1

Seems the optimalization came with adding combined index on both two fields.

This was unnecesarry on postgres 9.1 machine but on postgres 9.0 adding combined index boosted the performance rocket like.

Thank you very much, for the answers!


Cross-Database Impersonation Fail for Service Account I would like to enable an application that dumps a big ol' heap of data into a Staging area to be able to kickoff the stored procedure that transforms & loads the data into the Production area. If possible, I'd like to not grant the application any access to the production area.  Unfortunately, EXECUTE AS seems to be failing me. For reference, the DBs in question are running on 2008 R2, but I'm recreating the behavior on my 2016 localhost. As well, the service account in question is a domain credential, but I've recreated the behavior with a SQL Auth credential for portability.

The Setup

use [master]
create database Prod;
create database Stage;
create login ServiceAccount with password='Password1234';
go
use Prod
create table tbl1 ( i int );
insert tbl1 select 1;
go
use Stage
create user ServiceAccount for login ServiceAccount;
go
create proc spLoadStageToProd as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go
grant execute on spLoadStageToProd to ServiceAccount;
go

The Testing

From the Stage database, exec spLoadStageToProd; succeeds. Predictably, exec as login = 'ServiceAccount'; exec spLoadStageToProd; fails with the error...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Okay, so I'll try execute as owner so that the proc has the permissions it needs to do the work but the (developer who has the password for) ServiceAccount doesn't get to muck about in my Production area.

alter proc spLoadStageToProd
with execute as owner
as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go

Shoot! Well now a simple test from Staging of exec spLoadStageToProd; returns the error:

Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35]
The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context.

"DOMAIN\peter" is of course me: the doof with sysadmin server role...

Well that's weird enough... I tried alter authorization on object::spLoadStageToProd to dbo; but the same error (Msg 916) pops up. What's more... exec as login = 'ServiceAccount'; exec spLoadStageToProd; now returns...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Screw it! alter authorization on object::spLoadStageToProd to sa;...

Msg 15151, Level 16, State 1, Line 41
Cannot find the user 'sa', because it does not exist or you do not have permission.

...

...

...sad.

So... clearly I'm misunderstanding something fundamental. I tried enabling cross-db ownership chaining just in case that was the sticky wicket but no luck. After fruitless googling and a quick scan of these two dba.se questions, I'm pretty stuck. Where am I going wrong?

How do I get this SProc to run on elevated cross-database permissions regardless of who calls it?

The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on.

If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of who calls it, that's the end-goal.

I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the ServiceAccount one-by-one.

Courtesy Cleanup Script

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go

Correct: Impersonation / EXECUTE AS, by default, is quarantined to the initial database. In order to get around this you need to do either set the initial database to TRUSTWORTHY ON (not a great option, so should be viewed as a last resort), or create a Certificate that can be in both the initial DB and the destination DB that can be used to link the desired permissions (and then you don't need Impersonation at all).

I have several examples of doing this across various answers here on DBA.StackExchange. Try these first:

Stored procedure security with execute as, cross database queries, and module signing (this one goes through, step by step, showing how Impersonation, Cross-DB Ownership Chaining, and TRUSTWORTHY work, and then how module signing eliminates the need for all three of those options).
Permissions in triggers when using cross database certificates

Now, given the new information added to the question regarding:

  The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on.

and:

  If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of who calls it, that's the end-goal. ... I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the ServiceAccount one-by-one.

the direction to take can be narrowed down. Usually the goal is to be as granular as possible with the permissions, so it sometimes takes some extra steps to get to a least-privileged setup. But that doesn't mean that you can't take fewer steps to be less granular when the situation calls for it.

One of the (several) wonderful aspects of module signing is that you are effectively giving permissions to the code, not to people. So you can control the who can make use of the permissions by properly setting permissions on whatever code (i.e. Stored Procedures, Functions, Triggers, etc) is being signed.

Assuming you want both Server/Instance -level and Database-level permissions, even if you don't end up using most of what will be allowed, you can do the following:

Create a Certificate in [master].
Create a Login from that Certificate.
Add the Certificate-based Login to the sysadmin Fixed Server Role.
Switch to the Stage DB
Re-create the same Certificate in this DB (either by backing it up to a file on disk OR by extracting both the Certificate and the Private Key to variables or a local temporary table as shown in my other answers).
Sign the Stored Procedure with this Certificate via ADD SIGNATURE.

That's it! The Stored Procedure, regardless of who executes it, can do anything it was coded to do. And if that code changes, you will need to do the ADD SIGNATURE again. But the Certificate-based Login cannot be Impersonated, so nobody can use it to get "sa" privileges. And, if you use "encrypt by password" when creating the Certificate and do not give that password to anyone, then nobody will be able to sign other modules with it.

In this setup, the only on-going administration would be re-signing the Stored Procedure if you keep changing it.

Also, regarding your final attempt (i.e. alter authorization on object::spLoadStageToProd to sa;): that doesn't work because "sa" is a Login (Instance / Server -level), not a User (Database-level). So you can only grant permissions on database-level items to principals found in sys.database_principals.


Access Web App query 3 Tables with aggregatios - Need a workaround I'm designing an Access 2016 Web App for inventoring our computers.

The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC.

The computer-value in RAM and CPU are lookups on Computer.DNS
So in SQL it would be something like

SELECT Computer.DNS,SUM(CPU.Cores),SUM(RAM.Size) FROM Computer,CPU,RAM GROUP BY Computer.DNS

The result from the GUI-Query aboth is

[

Both SUM coloumns are (for most PCs) wrong.

The formula it's using (which are wrong!):

Cores: COUNT(CPUs)       * realAmount

RAM  : COUNT(RAM-sticks) * realAmount

Can anybody show me why it's doing that and what I did wrong?

I tried using

Cores: Sum([CPU].[Cores]-Count([RAM].[Size]))

in the "Field"-field but I get the error "Cannot have aggregate function in expression"

I have one workaround going which is making a seperate query for each 1:n-relation which connects DNS and SUM(foo) and one query which has the results of all the queries. Then in the little ERM for the query I connect every DNS entry of the queries.
For 1:1 relations in the DB I can just use the standard table in the overallQuery.

still looking for other workarounds though


Cant see SQL Server Agent in SSMS I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?

If you have Management Studio Express installed (any version prior to SQL Server 2012 SP1), it simply doesn't contain any facility for SQL Server Agent (no matter which edition you're trying to manage). You can fix this by getting the very latest version from here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

You want the file SQLManagementStudio_x64_ENU.exe (or the one with x86 in the name if you have a really old 32-bit machine). This is the first version of Management Studio that has been offered fully functional and license-free.

Once you install this version of Management Studio, you will have access to SQL Server Agent when managing any instances of the SQL Server engine that support SQL Server Agent. Of course that won't help you create jobs on instances of SQL Server Express, since it doesn't have Agent at all.


T-SQL performance tuning - Query running slower I've been tasked by the dev manager as a DBA to look into performance tuning the code as attached in the link provided below.

Basically, this is a stored proc that uses a CTE and then joins the CTE with various tables. However, the stored proc execution is surpassing way more than 9 minutes and I need to basically bring the execution time down as much as possible either by rewriting the code or adding indexes.

Looking at the actual execution plan it seems that the most expensive operation seems to be "sort" operation incurring 97% of the overall cost. We have a non clustered index on the sorted column but the sort order is asc and the code does a desc.

I am thinking of changing the sort order in the index to desc but again it uses a row_number() function which requires an order by to be explicit.

Is there a way I can reduce the cost or rewrite the query or maybe add an index or two to reduce the execution time?

;WITH cte_MaxStatus as
(
       SELECT
              QueuedTxUpdateID, ObjectID, QueuedAt, ErrorMessage,fkSfUpdateStatus, fkSfUpdateAction, ObjectType, ProcessedAt,
              rn = ROW_NUMBER() OVER(PARTITION BY ObjectType, ObjectID ORDER BY QueuedAt DESC)
       FROM dbo.SfQueuedTxUpdates
)

SELECT   MOT.ObjectName,/*mot.Description, */ ObjectID, QueuedAt, ErrorMessage, ua.SfUpdateAction, us.SfUpdateStatus, cte_MaxStatus.ProcessedAt
FROM cte_MaxStatus
       LEFT JOIN dbo.SfUpdateAction UA ON cte_MaxStatus.fkSfUpdateAction = ua.SfUpdateActionID
       LEFT JOIN dbo.SfUpdateStatus US ON cte_MaxStatus.fkSfUpdateStatus = US.SfUpdateStatusID
       LEFT JOIN DBZ.dbo.ModuleObjectTypes MOT ON cte_MaxStatus.ObjectType = MOT.ObjectTypeID
WHERE fkSfUpdateStatus <> 3
              AND cte_MaxStatus.rn = 1

Full SQL Script

SQL PLAN

It's worth pointing out that the estimated cost of the sort (268491.28 optimizer units) probably isn't realistic. The query optimizer thinks that it will need to sort 180 GB of data at that step. However, the sort is performed with a memory grant of 24 GB and it doesn't spill to disk. In fact, the most used memory during the plan is 8 GB. With that said, based on the information that we have the sort is probably the performance bottleneck.

It should be possible to define an index that removes the need for that sort. That's a perfectly valid optimization strategy. If you're looking for an alternative you could consider a parallel apply pattern. The idea is to use parallel nested loops to do a bunch of little sorts instead of one large sort which scales better. I don't know how well it will work with MAXDOP 2 and I can't see your data, but it could be worth a try. Something like this might work:

SELECT MOT.ObjectName,/*mot.Description, */ ObjectID, QueuedAt, ErrorMessage, ua.SfUpdateAction, us.SfUpdateStatus, cte_MaxStatus.ProcessedAt
FROM DBZ.dbo.ModuleObjectTypes MOT
CROSS APPLY
(
    SELECT
    s.QueuedTxUpdateID, s.ObjectID, s.QueuedAt, s.ErrorMessage, s.fkSfUpdateStatus, s.fkSfUpdateAction, s.ObjectType, s.ProcessedAt,
    rn = ROW_NUMBER() OVER(PARTITION BY s.ObjectID ORDER BY s.QueuedAt DESC)
    FROM dbo.SfQueuedTxUpdates s
    WHERE s.ObjectType = MOT.ObjectTypeID
) cte_MaxStatus
       LEFT JOIN dbo.SfUpdateAction UA ON cte_MaxStatus.fkSfUpdateAction = ua.SfUpdateActionID
       LEFT JOIN dbo.SfUpdateStatus US ON cte_MaxStatus.fkSfUpdateStatus = US.SfUpdateStatusID
WHERE fkSfUpdateStatus <> 3
AND cte_MaxStatus.rn = 1;

In the query plan, what you're looking for is nested loops with index seeks against SfQueuedTxUpdates on the inner side. There should be one sort per ObjectTypeID instead of one large sort. The query above may not be sufficient to get a nested loop join plan. You may need to use a superfluous TOP or other tricks (see the linked video for an explanation of the technique).

Note that I'm making an assumption that doing an INNER JOIN to DBZ.dbo.ModuleObjectTypes won't change the results. If that's not true, you could put distinct ObjectType values from SfQueuedTxUpdates into a temp table. Using recursion to get those distinct values can be more efficient than scanning the entire index. It depends on the ratio of rows in the table to distinct values.


mysqldump --no-data and --single-transaction I am using mysqldump to take snapshots of the structure of a MySQL 5.0.77 database. I don't want the data, so I use the --no-data option.

If I use --single-transaction am I needlessly locking tables?

If you only want a dump of the schema there is really no point in using the --single-transaction option. This option is more useful towards obtaining consistent data from transactional tables. In fact, while using this option, the dump is still vulnerable to being corrupt if any of the following statements are executed: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

You can read more on the description for this option on the MySQL Documentation for 5.0.


Tablespace error SQLCODE=-289, SQLSTATE=57011 We are trying to bulk insert ~200 million records as initial load into a DB2 database.

During the process we get the following error:

  SQLCODE=-289, SQLSTATE=57011, SQLERRMC=TEST_IS

Does it mean we ran out of disk space?
How to add additional space?

Please find the following details of the tablespace:

it is a Database-managed space.
type is large table space,
EXTENTSIZE is 32
PAGESIZE is 32768

I am not a DBA and not able to understand the error.

This probably means you ran out of space within the DMS tablespace. DMS tablespaces pre-allocate their storage. They can be set to auto-grow when full but perhaps this one isn't, or it's hit the maximum size specified.

For more information on the particular error you can use the "db2 ?" facility. In this case, the error page is particularly verbose (there are many sub-categories) so here's a sample of the output:

$ db2 ? sql0289                                                                                [109/109]

SQL0289N  Unable to allocate new pages in table space
      "<tablespace-name>".

Explanation:

One of the following conditions is true on one or more database
partitions:

1

         One of the containers assigned to this SMS table space has
         reached the maximum file size. This is the likely cause of the
         error.

2

         All the containers assigned to this DMS table space are full.
         This is the likely cause of the error.

[... lots more error codes ...]

User response:

Perform the action corresponding to the cause of the error:

1

         Re-create as an AUTOMATIC STORAGE table space or an SMS table
         space with more directories (PATHs) such that: (number of
         directories) >= (max tablesize / maxfilesize) or re-create.
         Note that maximum file size is operating system dependent.
         Re-creating the table space as AUTOMATIC STORAGE allows you use
         storage groups.

2

         Add new container(s) to the DMS table space and try the
         operation again, after the rebalancer has made the new pages
         available for use.

[... lots more responses ...]

sqlcode: -289

sqlstate: 57011

   Related information:
   ALTER TABLESPACE statement

As it states, the most likely cause is the containers for the tablespace are full, so add some new containers (or extend the existing ones) with ALTER TABLESPACE (there's several examples at the bottom). You will likely need to ask your DBA to do this (as the manual page states, this requires SYSADM or SYSCTRL privilege).


Options for setting NOLOCK hint in dataset queries Some context:
At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by using the WITH (NOLOCK) hint for tables in the query.

Because (a) it's quite obtrusive, and (b) it's easy to forget the hint for one of the tables, we moved to a second approach setting TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED (which is fine) at the top of each dataset's query.

As you may guess, it's still easy to forget the hint for one of the datasets. So this leads to the question:

Question: What are the options for sending NOLOCK hints along with report queries?

PS. I realize this is to some extent an XY-problem (with a lot of my other options for X, such as optimizing the query, not doing reporting on the operational database, etc), but tried to make this a valid question on itself nonetheless.

Options:
Here are the options mentioned above, with added options about which I'm curious if they would work:

Set WITH (NOLOCK) hint for each table. (obtrusive, very easy to forget)
Set isolation level to READ UNCOMMITTED for the entire query. (still easy to forget)
Is it possible to specify this at the report level? E.g. make sure all dataset queries for one report will be run without locking.
Is it possible to specify this at some other SSRS level? E.g. perhaps set this for a certain Report Folder, or by utilizing an extension?
Is it possible to specify this at the data source / connection string level? E.g. have all relevant reports use a certain "No-lock-data-source"?
Related to the previous option: perhaps it's possible to specify a default locking hint for a specific "no-lock-sql-user" (the one that's used in the connection)?
???

Which options are viable? Are there options I've missed?

Have you considered READ_COMMITTED_SNAPSHOT row versioning for the database?

Kim Tripp has a good article about it at http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

READ_COMMITTED_SNAPSHOT allows better functionality than WITH (NOLOCK) in that it provides absolute point-in-time consistency for long-running aggregations or queries with increased throughput because of reduced lock contention.


How to split tables without affecting all stored procedures We have merge replication setup between corporate database and remote clients. Lately we realized that people working on the corporate server do not need data to be tracked by merge replication as this data does not need to flow down to remote clients. We have decided to split tables between remote clients and people working on the corporate server.

Eg: Table1 has all data for remote clients as well as for people on corporate server. After table splitting, we want it to be Table1 for remote clients and Table2 for people on the corporate server.

To accomplish this I will have to change all my stored procedures for inserting and updating records in table1 and table2. First I have to find records in table1 and in case I don't find records in table1, I have to search records in table2 and then perform crud operations on the record.

Is there any easy way to accomplish this? A way where I don't have to change every stored procedure? I am aware of creating a view over table1 and table2, then create an instead of insert trigger on the view and write logic in trigger to insert into table1 and table2. Is anybody aware of any other way of accomplishing this kind of change?

I may be missing something, but instead of splitting tables, have you considered parameterized filters?

  Parameterized filters allow different partitions of data to be sent to
  different Subscribers without requiring multiple publications to be
  created. For example, a table can be filtered so that data for a given
  sales representative is replicated only to that representative.
  Parameterized filters have a variety of options that allow you to
  tailor filtering to optimize performance and best match your data and
  application requirements. For more information, see Parameterized Row
  Filters.


Why would a select on a single table refer to another table? I have a simple select on a table, but the execution plan shows that another table is involved, in another database. This is definitely a table and not a view and I can't see database auditing turned on so what could cause this?

ok, I actually just worked it out; there is a security policy in place which is causing this, strange though as not even profiler picked that up
Roger


Adding a running count of distinct values in MySQL 5.7 I have a table that records which hardware was used for a given run (job / batch) on a tool. Vastly simplified, the table looks like:

CREATE TABLE hardware (
  `run_name` varchar(8) NOT NULL,
  `hardware_used` varchar(45) DEFAULT NULL,
PRIMARY KEY (`run_name`)
);

run_name   hardware_used
pk123          h1
pk124          h1
pk125          h2
pk126          h2
pk127          h2
pk128          h3
pk129          h1

I want to get a running count of how many times each hardware has been used:

run_name   hardware_used   times_used
pk123          h1              1
pk124          h1              2
pk125          h2              1
pk126          h2              2
pk127          h2              3
pk128          h3              1
pk129          h1              3

In lexicographical order of run_name, create a running tally of how many times a given hardware_used has been seen.

In SQL Server, PostgreSQL, and MySQL 8, I think it could be done using Window Functions. Eg: the using SQL Server's OVER clause and Partitions, but I'm stuck with using MySQL 5.7.

Is something like this possible to generate in pure SQL?

SELECT t1.run_name, hardware_used, COUNT(*) times_used
FROM hardware t1
JOIN hardware t2 USING (hardware_used)
WHERE t1.run_name >= t2.run_name
GROUP BY t1.run_name, hardware_used
ORDER BY t1.run_name;

fiddle


How to omit multiple columns where is not null? I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended.  I suspect my error is somewhere within the WHERE clause, which is designed to exclude any NULL values in the resulting output.  I've included a copy of the corresponding table below the output for my most recent query:

SELECT vendor_name,
       vendor_address1 || ' ' || vendor_address2 || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

The resulting output:

Here is the corresponding table-view:

Thank you.

The screenshot of your data and output clearly shows the problem. The concatenation operator automatically takes care of NULL and you don't need to do anyhting. But you don't have NULL in your tables, you have 'NULL', a string, in your tables.

SQL> select 'A' || ' ' || null || ' ' || 'B' from dual;

'A'|
----
A  B

SQL> select 'A' || ' ' || 'NULL' || ' ' || 'B' from dual;

'A'||''|
--------
A NULL B

If you really had NULL in your table, SQL Developer would display (null) in the data grid.

Clean your data, then try again.

update ap.vendors set vendor_address2 = null where vendor_address2 = 'NULL';

Alternatively, you can turn string 'NULL' to a real NULL on the fly:

SELECT vendor_name,
       vendor_address1 || ' ' || decode(vendor_address2, 'NULL', null, vendor_address2) || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
ORDER BY vendor_name;

But don't do that. Take care of your data.


Possibility on huge MySQL database replication I have a mysql database with size 2.4TB which has lots of reads and writes happening continuously. I want to replicate this database as a backup.
But it's almost impossible to get a dump from this database with it's size. Is it possible to create a slave replica without a dump of the master?

Plan B:  Stop MySQL; copy the disk.

But first, turn on binlogging so that you will be able to sync up after you get the Slave going.


How to add CYCLE to BIGSERIAL in phpPgAdmin I'm trying to apply CYCLE to a BIGSERIAL in phpPgAdmin.

I've seen how to do it with a query, but I'd much rather be able to do it from phpPgAdmin.

Can this be done with phpPgAdmin?  If so, how?  If not, how can CYCLE be applied to a pre-existing BIGSERIAL column?

I don't use phpPgAmin.

To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column".

A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence.

When you define a column as serial Postgres automatically creates a sequences and applies the necessary default expression.

The sequence is usually called <table_name>_<column_name>_seq. But you can run a query to get the name of the generated sequence:

select pg_get_serial_sequence('your_table_name', 'your_column_name');

Once you know the name of the sequence, you can do the following:

alter sequence your_sequence_name cycle;

(Note that you need to commit that if you are not using auto-commit mode).

Here are some relevant links to the manual:

ALTER SEQUENCE: http://www.postgresql.org/docs/current/static/sql-altersequence.html
Details about the serial types: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL
Information functions: http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE


Event Booking System: Database Design I want to make an event (concert, theater, cinema,..) booking website

venues table:

-ID
-name
-address

stages table:

-ID
-venue_id
-title
-seats

-seats is json and contain seat row and column

First question is which one is better?
Create another table for the seats of each Stage or a column for any field of seats properties?

Then I need to have a showtimes table to set the price and event_id for each seat.

I want to know, if there is an optimal way to create the showtime table instead of creating a row for each seat?

When designing a new database, it's important to know before hand how it will be used.  Of course, it's difficult to have perfect knowledge of exactly how your database will be used before hand, because you may be prototyping, your business needs may change over time, etc.

I've found that a good rule of thumb for designing a relational database for a transaction processing system is to assume that each tangible thing your system tracks gets its own table.

Consider the following ERD for your situation:

In your case you have VENUEs that each have one or more STAGEs.  Each stage has many SEATs.  However, note that you want to keep track of PRICEs for seats, so for the sake of sanity in maintaining pricing data, your seats are arranged into SECTIONs.  The price is set at the section level instead of at the individual seat level.  The price also depends on the show time (EVENT) because you might have different prices for different days of the week or different times of day (week day matinee is cheaper than Saturday evening, for example).

You have multiple show times, but there is probably going to be many things common to an event with multiple show times, so you want an EVENT table and a child SHOW table.  This lets you keep show and event (show time) information in third normal form (3NF) which is always a good place to start with a transactional system database.

Assuming that you also want to keep track of who is actually in each seat, you need to have TICKETs and CUSTOMERs in your database as well.

To address your specific question about how to track seats, as rows or as columns, I strongly suggest you track seats as rows.  Relational database management systems like MySQL are built to easily handle rows of data.  If you handle seats as columns, you would probably need a different seat table for each stage, since the seats will be different for each one.  That would make your queries very awkward and complex.  If you are going to use a relational database for your data, then use it the relational way and put things in rows and facts about things in columns.  Your code will be simpler and your life will be better.


MongoDB - changing data directory name changes data I am witnessing a very bizzare behaviour of MongoDB.

I launch MongoD on a test system (Windows 10, 64bit) like this:

"C:\Program Files\MongoDB\Server\3.6\bin\mongod.exe" --dbpath "C:\MongoData"

I then shut down the server and replace the MongoData directory with a data set from a production server. I start mongod again, but I get a collection with 4 documents, which is what I used to have before I replaced my test data set with a real one.

That's not right, because I would expect to see loads of documents in a real data set. I then try dozens of things, including pointing MongoD to the data folder in its original location, which gives me all the documents from the real collection. Phew, the data is safe. But what is happening in my test system?

I have noticed that if I rename the folder in my test system to MongoData2 and lanuch mongod pointing at it then I get my proper production data set. but if I rename it back to MongoData and launch mongod pointing at this folder I get 4 documents again.

What is causing this?

As per MongoDB documentation here To use an alternate dbpath, specify the path in the configuration file (e.g. C:\mongodb\mongod.cfg) or on the command line with the --dbpath option.

  Default: /data/db on Linux and macOS, \data\db on Windows

The directory where the mongod instance stores its data.

Changed in version 3.0: The files in --dbpath must correspond to the storage engine specified in --storageEngine. If the data files do not correspond to --storageEngine, mongod will refuse to start.

--directoryperdb

Uses a separate directory to store data for each database. The directories are under the --dbpath directory, and each subdirectory name corresponds to the database name.

Changed in version 3.0: To change the --directoryperdb option for existing deployments, you must restart the mongod instances with the new --directoryperdb  value and a new data directory (--dbpath ), and then repopulate the data.

For standalone instances, you can use mongodump
on the existing instance, stop the instance, restart with the new --directoryperdb
value and a new data directory, and use
mongorestore to populate the new data directory.
For replica sets, you can update in a rolling manner by stopping a
secondary member, restart with the new --directoryperdb
value and a new data directory, and use initial
sync to populate the new data directory. To update all members,
start with the secondary members first. Then step down the primary,
and update the stepped-down member.


Oracle Select with xmlquery() error - How to ignore errors? In trying to extract a subset of data from a Oracle DB table, one of the columns is XMLType, which is extracted with xmlquery() for relevant information.

The problem is that the XML data is from an outside source, which often gives us malformed XML with unescaped special characters.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00244: invalid use of less-than ('<') character (use &lt;)
Error at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

It would be ok for my purposes to ignore these rows.

How can I ignore these parse errors and have Oracle continue on with the SQL statement?

Thanks

I don't think you can directly. I don't know of a way of putting the XML parser into a sort of "lax" mode where it would ignore validation errors, and you can't catch exceptions inside plain SQL.

One approach to deal with this (except harassing your outside source so that they produce valid XML, which is the solution) would be to validate the XML as you store it, and only process data that has been validated. You can do that simple PL/SQL code. Here's a minimal example of one way to approach this.

Sample table and data:

create table foo(id int, xml clob, valid int default null);
insert into foo(id,xml) values (1, '<?xml version="1.0"?><root></root>');
insert into foo(id,xml) values (2, '<?xml version="1.0"?><root><a foo="<"></root>');
commit;

Validation function:

create function is_valid_xml(xml_param clob)
return int
as
  scratch xmltype;
begin
  select xmltype(xml_param) into scratch from dual;
  return 1;
exception
  when others then
    return 0;
end;
/

Applying the validation function

SQL> update foo set valid = is_valid_xml(xml) where valid is null;
2 rows updated.

SQL> select id, valid from foo;
ID | VALID
----------
 1 |     1
 2 |     0

You can then query/report based your usual criteria plus the valid column.


What will be the relation between Employees and Players, given that some players are also Employees? I have a requirement where I need to store information on Employees in a company that owns(?) a sports team and also store information on Players from other teams that are not Employees of this company. The Players in the team(i.e owned by the company) are also considered Employees hired by the company.
For the design, what I have done is create two Entities called Employees and Players and have a Player id key as FK in the Employees Entity. The Employee Entity also has Employees that are just employees and not players. With the player id fk, Employees that are Players will have a player id fk, while other employees will not. That way I have information about Employees, player or just normal employees, in Employee entity. And in the Players entity I only have information specific to all the players hired by the company or just other players from other teams(not owned by the company). Is this the right way to go? I haven't shown the subclass/subtype relation between Employees and Players entity because not every player in the Players entity is an Employee. Am I in the right direction?
I know this is not the very best explanation I guess but please do ask for any clarification. Thank you

can you give an example of exactly why there's a N:N with unique constraints for both Emp_id and player_is?

CREATE TABLE employee (employee_id INT PRIMARY KEY, ...);
CREATE TABLE player (player_id INT PRIMARY KEY, ...);
CREATE TABLE junction (employee_id INT NOT NULL,
                       UNIQUE (employee_id),
                       FOREIGN KEY (employee_id) REFERENCES employee (employee_id),
                       player_id INT NOT NULL,
                       UNIQUE (player_id),
                       FOREIGN KEY (player_id) REFERENCES player (player_id));

Unique constraints forbid one employee to be two separate players and backward.
If a player is not an employee than junction table does not contain a row for him, and backward.


Pass a table array as a parameter to an upsert function in postgresql i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will insert/update ALL the records at once as ONE TRANSACTION? if record number x fails,it should roll back undo any records that had been inserted/updated before x?
here is my upsert function:

CREATE OR REPLACE FUNCTION save_weights(rec tbl_weightment)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN

    update tbl_weightment set
    vname=rec.vname,
    iquality=rec.iquality,
    vhooks=rec.vhooks,
    tstamp=rec.tstamp
    vtare=rec.vtare,
    vgross=rec.vgross
    WHERE id=rec.id;

    IF FOUND THEN
        myoutput:= 'Record successfully updated';
        RETURN myoutput;
    END IF;

    BEGIN
        INSERT INTO tbl_weightment SELECT(rec).*;
        myoutput:= 'Record successfully added';
    END;
 RETURN myoutput;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

can this function be modified to be like save_weights(rec tbl_weightment[]) ??
any help will be highly appreciated

I think yes.  Haven't tried with actual INSERTs or UPDATEs yet, but this function works:

CREATE TABLE a (id integer);

CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS
$body$
SELECT b.*
FROM unnest($1) b;
$body$
LANGUAGE sql;

This way you can write your INSERT statement as

INSERT INTO tbl_weightment
SELECT (r).*
FROM unnest(rec) r;

And UPDATE as

UPDATE tbl_weightment
SET
    vname = r.vname,
    iquality = r.iquality,
    vhooks = r.vhooks,
    tstamp = r.tstamp
    vtare = r.vtare,
    vgross = r.vgross
FROM unnest(rec) r
WHERE id=rec.id;


Group activities in activity feed by users and products On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there.

One of them is the grouping. I've built it so that if multiple users buys the same 2 or more products the activity feed shows it as one activity: "user1 & user2 bought product 1, 2 & 3".

The problem is that if user1 buys product 1 & 2 and user2 buys product 1, 2 & 3 it appears as two different activities: "user1 bought product 1 & 2" AND "user2 bought product 1, 2 & 3".

I would like it to show as "user1 & user2 bought product 1 & 2" and then another saying "user2 bought product 3"

I've made a simplified sqlfiddle of what I've got so far. Try changing it by deleting NULL, 'Bought', 1, 3

Is this something that is possible to do in MySQL or should I do it in PHP?

You need to group users by activity and product in the first step, and then group products by the groups of users.
I use ORDER BY with GROUP_CONCAT(), so that the aggregated usernames are concatenated in the same order.

SELECT x.usernames
      ,x.activity
      ,GROUP_CONCAT(p.product_name ORDER BY p.product_name) AS product_names
FROM   products p
JOIN  (
   SELECT a.activity
         ,a.productId
         ,GROUP_CONCAT(u.name ORDER BY u.name) AS usernames
   FROM   activities a
   LEFT   JOIN users u ON u.id = a.userId
   GROUP  BY 1, 2
   ) x ON x.productId = p.id
GROUP  BY 1, 2
ORDER  BY 1, 2;

-> sqlfiddle

Returns:

USERNAMES  | ACTIVITY   | PRODUCT_NAMES
-----------+------------+------------------------------
John       | Bought     | The Dark Knight Rises
John,Peter | Bought     | Batman Begins,The Dark Knight


Use of integer instead of interval (of one type) Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.

Advantage: 2 bytes instead of 12 bytes (we have many such columns).

Is this line of thinking ok or am I overlooking something?

Perfectly ok - especially since you can add and subtract integer to / from  date directly in Postgres.

However, int2 might not be better than a plain integer. True, integer occupies 4 bytes instead of 2, but many operations are optimized for integer. Among other things, integer is the default numeric type for numbers without decimal point. For int2 you often need to add an explicit cast to avoid errors.

Also, while we are talking about a single column, you will most probably gain nothing in regard to disk storage or RAM. Some closer understanding of storage mechanisms is needed here, in particular padding and data alignment.

If you have several int2 columns in a table  (or even "many" as you mention) and / or know what you are doing, the odds for int2 get better.

Details:
Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

If in doubt, run a test and measure:
Measure the size of a PostgreSQL table row