Configuring pg_admin 9.6 while there are users using it on Windows Server 20XX? I read something about pg_ctl reload (I'm not sure if that's on the Windows distro of pg)...also is there a way to verify the configuration isn't going to break it completely, like apachectl configtest?

I don't think that there is anything like that for PostgreSQL.

pg_ctl reload does check the syntax of the config file, but it only works with a running server.  If there is a syntax error it does report it, but it reports it to the running server's log file, not to the command line.

And if there is no syntax error, then it applies those changes which it can apply to the running server, which doesn't seem like what you want.

Some settings cannot be changed with a reload, but only a full restart.  Changings to those setting will be noted in the log file and then ignored.  Other things are called errors, but not syntax errors.  Those things will also get reported to the log file and ignored.  But these conditions won't block other changes from getting applied to the running server.

If you change "shared_buffer=1TB", that is valid syntax and valid semantics, so you wouldn't get a warning on that front if you tried to reload it.  But it also wouldn't apply the change, as shared_buffers can't be changed without a full restart.  Then if you tried to do the restart, your server would stop and then fail to start again, because your server (probably) doesn't have 1TB memory available to it.

Your comment asks specifically about adding users. That is a bit of a different topic than I had in mind.  In PostgreSQL, you can add users via SQL alone as long as they match to a pattern already existing in the pg_hba.conf.  If you do need to change the pg_hba.conf (to add hosts, or to tie specific hosts to specific users), that does require a reload, which can also be done in SQL via SELECT pg_reload_conf();.  The pg_hba.conf file is quite a bit simpler than the postgresql.conf file.  But there is still no "lint" like tool for it, that I know of. It never needs a restart to take effect (unless you change it to invoke new modules like ssl that themselves are not set up yet and need a restart to set up).  It only needs a reload.  If you introduce syntax errors to pg_hba.conf, then the reload will leave things alone, and complain to the log file.  If you were not paying attention to those complaints and issued a restart, you server would stop, and then refuse to start again due to the syntax errors in pg_hba.conf.  So good rules of thumb are to never issue a restart when a reload would suffice, and look in the log file after a reload.  Also, try to use wildcards to the extent feasible in the pg_hba.conf so that you can manager users via SQL rather than pg_hba.conf


Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the majority of my general SQL queries, mainly because this was drilled in to me when originally learning the language.

From my understanding, this isolation level acts the same way that WITH (NO LOCK) however I only ever tend to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Is there ever a time that I should be using WITH (NO LOCK) over SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
Does  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED stop other users from being locked out of the tables that I am reading?
If  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is used to stop locks, but I am only reading data, what is the point in using it? Is it only system intensive queries that would generate locks? Is it worth using it when running queries that would return in say, 5-10 seconds?
I have been told not to use  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED when reading data that would be used in updates, presumably to avoid updating dirty data. Would this be the only reason?
With the type of database that I am working on, there is a production and testing environment. We will very rarely be querying the production environment but when I need to, I will generally be using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in my query. I understand that dirty reads are possible with this. Aside from receiving data back that may not end up being committed to the database (and therefore throw my results out) what other types of 'dirty reads' could be possible?

Sorry for the mass questions.

It is terrible, that you learned it that way (sorry!).

READ UNCOMMITTED let's you read every row, yes. Even those who are currently used in an INSERT, UPDATE, DELETE operation. This is very useful if you need to take a quick look at some Data or in mission critical SELECT-Statements where a block would be very harmful.

In fact, you risk your integrity. It may occur that you read a row, which is currently used to be deleted or in a change. It can also appear that you read a wrong value. This may be really uncommon, but it can happen. What do I mean with that? Well, think of a row which is very broad (it has many columns with many long nvarchar-columns). An update occur on this row and sets new values. In rare cases it can happen to you, that you read only a half row. Another thing can happen for example, if a user changes his login values. He changes his mail + password. The mail is already set, but the password isn't. This way you have a inconsistent state.

I would suggest to forget about READ UNCOMMITTED. Just use it where it's really needed.

Another alternative for you can be to enable the READ_COMMITTED_SNAPSHOT database option - therefor you can use READ COMMITTED SNAPSHOT due to the enabled row versioning in your tempdb. This way you just read another (older) version of a row. This won't block your Queries. But it may occur that you read an old value too, but an consistent old value.

Another idea can be WITH(READPAST) instead of WITH(NOLOCK). You will read the old state of the table (a bit like in the SNAPSHOT ISOLATION), but you'll skip all currently locked rows instead.


Postgresql Log message without its statement If I want to write a message to the Postgres log I can do this:

DO $$
BEGIN
  RAISE LOG 'Justin Is Awesome';
END $$;

However, it logs the statement as well so I get 5 lines of output instead of one:

2016-07-12 16:38:13 UTC LOG:  Justin Is Awesome
2016-07-12 16:38:13 UTC STATEMENT:  DO $$
  BEGIN
    RAISE LOG 'Justin Is Awesome';
  END $$

I can reduce it to two lines with the following:

2016-07-12 16:36:04 UTC LOG:  Justin Is Awesome
2016-07-12 16:36:04 UTC STATEMENT:  DO $$ BEGIN RAISE LOG 'Justin Is Awesome'; END $$

However is it possible to reduce it down to one? Is there a way to send a line of text to the Postgres log without postgres logging the statement that generated the log?

If you want to suppress the "STATEMENT" information, then you could set log_min_error_statement = fatal (or panic).

If you want to keep the STATEMENT information but compress it into a single line with the main entry, then you could set log_destination = 'csvlog'.  Technically it will still be multiple lines, but the internal newlines will be escaped by inclusion within double quotes, which any good csv parser will handle for you.


Should I switch my database LOG volumes from IO1 to ST1 I've got a SQL 2014 database that's been running in AWS for a couple of years now, in a dual-server mirrored configuration, using separate EBS volumes for data, logs, etc.  When the system was created, the performance characteristics of the various EBS volume seemed to indicate that IO1 was the safest option for the kind of latency and throughput we needed for the log volume (it's a pretty busy database and at the time we were willing to pay extra to make sure the transition from self-hosted when smoothly).  Since then, AWS has added a new much cheaper volume type, ST1, that's backed by HDD instead of SSD (see AWS EBS volume types).  We're also now interested in cutting costs if we can do so without a major impact on performance.  Amazon specifically lists 'Log Processing' as one of the use cases for this type of volume.  The performance characteristics of ST1 allow for better throughput (MB/sec) but fewer IOPS than IO1 or GP2 type volumes, and given my understanding of database logs, that would seem to be perfect, so I'd like to make a switch.

However, in checking the CloudWatch metrics for these volumes (we actually have two databases on two servers with each server as principal for one of the database and as mirror for the other database), the 'Write Bandwidth (KiB/s) is FAR below the 500 MiB/s limit (generally <3 MiB/s), but the 'Write Throughput (Ops/s)' metric frequently goes up to about 2000.  The docs say that for ST1 the Max. IOPS/Volume is 500, assuming a 1Mib I/O size, but it's not clear whether the 500MiB/s limit comes from the IOPS limit or vice-versa.  It's also not clear whether this Ops/s is the same as IOPS.  Although logic would seem to indicate that they are, I have other volumes on those systems that I recently switched to ST1, and CloudWatch shows up to 10,000 Write Ops/s for those, so maybe not.

So, my questions are:

Would it be prudent to switch the log volumes to ST1?
If yes, what is the best way to go about doing this?

Taking the databases offline, even for a few minutes, to make the switch is NOT an option.

AWS may provide a 'seamless' 'online' conversion of these volumes (there is a warning that it may or may not be available for these volumes, possibly because they were created before that features was guaranteed to be available).  I converted the EBS volumes we use for backups on the same servers from GP2 to ST1 this week and had no problems at all, but if those volumes had performance issues during the conversion process, it wouldn't have affected the system anyway, as they only get used at night when we do the database backups.  There is no indication I can find about the performance impact of the conversion process itself, nor whether or not it can be cancelled should it cause issues.

The other two possible migration methods I can think of are:

Add new ST1 volumes to both the principal and mirror, add a new log file and set the log file parameters such that it will attempt to put all the log data into the new file.  Then run log backups, delete the old log file, and then remove the old log volume.
Add a new ST1 volume to the mirror, take the mirror offline, move the log file from the old volume to the new one, then bring the mirror back up, failover, and repeat with the principal.

Any advice or insight into the inner workings of EBS or SQL 2014 would be greatly appreciated.  For example, how exactly does EBS enforce performance limits on the various types of volumes?  Will SQL 2014 automatically increase the size of units it writes to the log in response to an increase in log file latency?  (If it automatically flushes to the log every time data is pending and the previous flush completes, for example, such a change might simply increase how much is written each time and have little impact on overall throughput, and I'm much more concerned with throughput than latency).

(First two paragraphs are repeats of my comments.)

When Amazon specifies "log processing" as a use case, I'd suspect they're talking about infrequently-flushed IIS logs and the like, not database logs. And although ST1 is specified as not suited for "small, random" workloads, my reading of http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html#inefficiency would be that it's not suited for "small and/or random" workloads. But this, and your questions of how the limits are enforced/calculated, are probably something you need to get clarified from AWS.

"Will SQL 2014 automatically increase the size of units it writes to the log in response to an increase in log file latency?" I'm pretty sure it won't by default. But if you can afford some data loss risk, then delayed durability may be just what you need: https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

If you do end up moving to ST1, AWS's online conversion option sounds far riskier than "Add a new ST1 volume to the mirror, take the mirror offline, move the log file from the old volume to the new one, then bring the mirror back up, failover, and repeat with the principal", which should work well for this. (Moving or dropping the primary log file with the database online is not an option.)


Set var="NEW.this_word_column" in a trigger function if "word" is present in a column name Context

I'm using PostgreSQL 10.12.

I want to be able to fire a trigger function on some specific tables with geographic data.

This trigger function is designed to create a PotGIS point into a column geom from lat and lon values.
I want to apply this same trigger function over several tables each time a new row is inserted.
The naming of columns holding latitude and longitude values is not consistent across tables but it follows a pattern...

What I am 100% sure is that:
 1. there are always two columns containing latitude and longitude values,
 2. the name of theses columns are not always the same in the
    different tables (otherwise it would have been too simple), but the word 'latitude' and the word 'longitude'
    always appear in their name and never appear in other column names.

E.g. in table A:

  _loc_longitude_ and _loc_latitude_

and in table B:

  _building_longitude_center and _building_latitude_center

for example (+ many others).

The trigger function is as follows:

CREATE OR REPLACE FUNCTION make_point_with_latlon()
  RETURNS trigger AS
  $$
  DECLARE
      varlon := NULL;
      varlat := NULL;
  BEGIN
      IF to_jsonb(NEW) SIMILAR TO '.*longitude.*' THEN
          varlon := NEW.the_column_whith_longitude_in_its_name;
      END IF;
      IF to_jsonb(NEW) SIMILAR TO '.*latitude.*' THEN
          varlat := NEW.the_column_whith_latitude_in_its_name;
      END IF;
      NEW.geom = ST_MakePoint(varlon, varlat);
  END
  $$
LANGUAGE 'plpgsql';

With the following triggers:

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table1
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table2
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

CREATE TRIGGER make_point_with_latlon_but
    BEFORE INSERT OR UPDATE ON schema.table3
    FOR EACH ROW
    EXECUTE PROCEDURE schema.make_point_with_latlon();

-- (...and many more other tables that need the same trigger).

I don't know how to search for the pattern .*latitude.* in the columns names passed to the function, and how to get their value into two variables that I can use to build the point with.
And my googling skills for searching these kind of stuff are far from perfect, hence returning noisy results.

Question

Is there any possibility to do that?

You can use INFORMATION_SCHEMA views and special variables: TG_TABLE_SCHEMA, TG_TABLE_NAME

Quoted from docs:

  When a PL/pgSQL function is called as a trigger, several special
  variables are created automatically in the top-level block. They are:

  TG_TABLE_NAME: Data type name; the name of the table that caused the
  trigger invocation.

  TG_TABLE_SCHEMA: Data type name; the name of the schema of the table
  that caused the trigger invocation.

Given next example:

create table a (id int primary key, latitude int, longitude int);
create table b (id int primary key, _loc_latitude int, _loc_longitude int);
create table c (result text);

create function trg_tables()
returns trigger as
$$
declare
  varlat text;
  varlng text;
  cmd text;
  vallat int;
  vallng int;
begin
  varlat := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%latitude%');

  varlng := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%longitude%');

  execute 'select $1.' || varlat
  using NEW
  into vallat;

  execute 'select $1.' || varlng
  using NEW
  into vallng;

  execute format('insert into c values (''lat: '' || ''%s'' || ''  lng '' || ''%s'' )',
                 vallat::text, vallng::text);

  return NEW;
end;
$$
language plpgsql;

create trigger trg after insert on a
  for each row execute procedure trg_tables();
create trigger trg after insert on b
  for each row execute procedure trg_tables();

insert into a values (1,11,1);
insert into b values (1,20,2);
select * from c;

| result         |
| :------------- |
| lat: 11  lng 1 |
| lat: 20  lng 2 |

db<>fiddle here

It's just an example on how to use INFORMATION_SCHEMA and special variables inside a trigger, but maybe a Postgres pro-user can give you a better solution.


ignore mysql schema on replication before moving from mysql 5.6 to mysql 5.7 I am planning to move from mysql 5.6 to mysql 5.7, so I going to upgrade one of my slaves instances to 5.7, and later on to switch it to a master.

Should I need to ignore mysql db before upgrading mysql 5.6 to 5.7 on this slave ?

replicate-ignore-db = mysql
replicate-wild-ignore-table=mysql.%

If so to what should I pay attention after upgrading or before switching my master to 5.7 (beside testing mysql 5.7 with my application)?

Any suggestions would be appreciated.

Here is my.cnf:

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]
read_only
max_connections=2500
log-slave-updates=true
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/data/mysql
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_support_xa=0
innodb_io_capacity=2500
innodb_adaptive_hash_index=1
innodb_doublewrite=0
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_max_dirty_pages_pct = 90
table_open_cache=2500
innodb_open_files=10000
open_files_limit=10000
optimizer_search_depth=0
innodb_file_per_table
max_connect_errors=10000000000
max_connections=2500
long_query_time = 1000
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 500M
sort_buffer_size = 8M
join_buffer_size = 8M
query_cache_size = 0
query_cache_limit = 0
query_cache_type=0
tmp_table_size = 1200M
max_tmp_tables=250
long_query_time = 2
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 42G
innodb_log_buffer_size = 8M
innodb_log_file_size = 400M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 100
server-id = 102
expire_logs_days        = 12
max_binlog_size         = 100M
relay-log=/data/binlogs/linuxdb1023306_r.-relay-bin
relay-log-index=/data/binlogs/linuxdb1023306-relay-bin.index
log-bin= /data/binlogs/linuxdb1023306-bin
binlog_format=row
innodb_locks_unsafe_for_binlog=1
max_connections=2500

Should I need to ignore mysql db before upgrading mysql 5.6 to 5.7 on this slave?

No, you should not.  MySQL should handle everything correctly when the replica is newer than the master by not more than one major release, e.g. 5.6 to 5.7 -- just like you have here.

The fact you appear to be using the ROW binlog format is good, but be sure you're using it on the master, too, for the best chance of continuous clean replication.

And do have a good cry while reading the official list of caveats and breaking changes.  Most of them are well-intentioned, at least.


Is it possible to re-initialize sequence How do I re-initialize the sequence value without drop and recreating them again
For example, the current sequence value is 3, now I want to restart it with 1 again.

This thread over on Stack Overflow has the information you are looking for. There are two main options:

Drop the sequence and re-create it, or
Use a trick, courtesy of Tom Kyte:

either as a one-off:

alter sequence seq increment by {minus_the_current_value} minvalue 0;

or you can create a procedure if you need to do this regularly:

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                      ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/


Problem with pattern matching on unicode characters I'm writing a piece of code that uses pattern matching on unicode characters but I'm running into an odd problem.  Some characters work and some don't.

DECLARE @Pattern nvarchar(50) = N'%[^a-z]%' -- Simple pattern
SELECT PatIndex(@Pattern, nchar(46797)), nchar(46797) -- Works
SELECT PatIndex(@Pattern, nchar(14843)), nchar(14843) -- Doesn't Work

The pattern should be pulling for any character that isn't a-z but for some reason it isn't catching some characters.  Does anyone know why some unicode characters would match and others wouldn't?

See if doing a binary collate fits what you need.  Here is a quick test.

USE Tempdb
GO

IF OBJECT_ID('PattMatch') IS NOT NULL
BEGIN
  DROP TABLE PattMatch
END
GO

CREATE TABLE PattMatch (COL1 NVARCHAR(50))
GO

INSERT INTO PattMatch
VALUES (nchar(46797)),(nchar(14843)),('ddddddd*'),('lettersand9999')
GO

DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'
SELECT PatIndex(@Pattern, COL1 COLLATE Latin1_General_BIN2), COL1 FROM PattMatch
GO

DROP TABLE PattMatch
GO

--your test
DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'
SELECT PatIndex(@Pattern, nchar(46797) COLLATE Latin1_General_BIN2)
SELECT PatIndex(@Pattern, nchar(14843) COLLATE Latin1_General_BIN2)


How large will the Transaction Log backup be? I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got way out of hand in the past year.

Now I want to fix the situation by first doing a full backup from SQL Server and then doing a transaction log backup. There is not much space on the server, so I'd like to know in advance how big the transaction log backup will be, approximately. I understand that the transaction log will only be truncated after backup, so I'm afraid the backup will be 90 GB as well and clogg the server.

So far I can't find any information on backup sizes, can someone explain what will happen size-wise? Thanks in advance!

Edit:
SQL Server version is Standard Edition, and the database recovery model is full.

If the logs are full, then yes you will probably be looking at needing the same space to backup them up.

If you're running SQL Server 2008 Enterprise then you may find that enabling backup compression will save you plenty of space, unfortunately the only way to really find out how much is to run the backup (do you have a dev environment where you could restore a copy to?).

The other option if you don't need the ability to restore to a point in Time previous to when you do this, would be to:

put the database into Simple Recovery mode
Checkpoint
put database back into Full Recovery
Take Full backup
Start taking log backups.

By putting the database into Simple Recovery you break the log chain, so when you move back to Full Recovery there will be no log as such for the first log backup to take. Don't forget to take the Full Backup so that SQL Server can start a new log chain.

As mentioned this will mean you'd lose the ability to recover the database to 15:25 21/10/2012 for example. But you'd still be able to restore to a full or differential backup.

Added 03/06/2013

Sorry, I completely forgot this option as well:

Do you have any remote storage options? You can just backup the transaction logs to a UNC path. This may be a  bit slower depending on your network but it'll mean you'll have a full transaction backup covering the previous period and can then start taking smaller regular backups.


PostgreSQL : avoid SUM over same values multiple times I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student.

CREATE TABLE student (
   id serial PRIMARY KEY,
   name text NOT NULL,
   bonus integer NOT NULL,
);

CREATE TABLE score (
  id serial PRIMARY KEY,
  subject text NOT NULL,
  score1 integer NOT NULL,
  score2 integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id),
);

The query to join score1 & score2 looks like this:

SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name

If I add bonus to this query i.e. sum(sc.score1 + sc.score2 + st.bonus), it gets repeated for each student multiple times (depending on how many times student_id occurs in score table).

Do I have to use a subquery i.e. compute the sum of score1 & score2 first and then add that to bonus (see below) or is there a better way?

SELECT sq.name, sum(sq.bonus+sq.total) FROM
( SELECT st.bonus, st.name, sum(sc.score1 + sc.score2) as total
  FROM student st
  LEFT JOIN score sc ON sc.student_id = st.id
  group by st.name
) AS sq

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list.
Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name.
You probably also need coalesce() for students without any scores:

SELECT st.name,
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id, st.name, st.bonus ;

In newer versions of Postgres, you could use only the primary key of the student table in the group by:

SELECT st.name,
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id ;

If you want a subquery, this is one way:

SELECT st.name,
       coalesce(sc.score, 0) + st.bonus AS total
FROM student st
LEFT JOIN
    ( SELECT student_id, sum(score1) + sum(score2) AS score
      FROM score
      GROUP BY student_id
    ) AS sc ON sc.student_id = st.id ;


Another Dynamically alias column question I might be asking the same question as this, but perhaps if I ask it my way, a different solution may become obvious. I have this:

DECLARE @Table1 AS TABLE(
Table1ID Int Primary Key
,ColumnName varchar(max)
)
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(1000,'A')
INSERT INTO @Table1(Table1ID,ColumnName) VALUES(2000,'B')
DECLARE @Table2 AS TABLE(
Table2ID Int Identity Primary Key
,Table1ID Int
,FieldData varchar(max)
)
INSERT INTO @Table2(Table1ID,FieldData) VALUES(1000,'A DATA')
INSERT INTO @Table2(Table1ID,FieldData) VALUES(2000,'B DATA')
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT FieldData AS A
from @Table2
WHERE Table1ID = 1000
SELECT FieldData AS B
from @Table2
WHERE Table1ID = 2000

And I want to do this:

SELECT FieldData AS [Table1.ColumnName]
from @Table2
WHERE Table1ID = 2000

If you can use #temp tables instead of table variables, this becomes dead easy.

CREATE TABLE #Table1
(
 Table1ID Int Primary Key,
 ColumnName varchar(max)
);

INSERT INTO #Table1(Table1ID,ColumnName)
  VALUES(1000,'A'),(2000,'B');

CREATE TABLE #Table2
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

INSERT INTO #Table2(Table1ID,FieldData)
  VALUES(1000,'A DATA'),(2000,'B DATA');

Now, I can do this:

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM #Table2 WHERE Table1ID = @Table1ID;'
FROM #Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql, N'@Table1ID INT', @Table1ID;

Results:

B
------
B DATA

If you need to use table variables for some reason (as explained in an excellent answer by Martin Smith, they're actually usually less desirable than #temp tables, or indifferent even in ways where most people make assumptions that table variables are better), then you can use a really fugly hack based on this answer (also by Martin Smith, coincidentally).

First, you need to create a table type in your database (and creating a copy of this in model may be a good idea as well, because in some scenarios it may need to also exist in tempdb).

CREATE TYPE dbo.Table2 AS TABLE
(
  Table2ID Int Identity Primary Key,
  Table1ID Int,
  FieldData varchar(max)
);

Now, your code would be:

DECLARE @Table1 AS TABLE
(
  Table1ID Int Primary Key,
  ColumnName varchar(max)
);

DECLARE @Table2 AS dbo.Table2;

INSERT INTO @Table1(Table1ID,ColumnName)
 VALUES(1000,'A'),(2000,'B');
INSERT INTO @Table2(Table1ID,FieldData)
 VALUES(1000,'A DATA'),(2000,'B DATA');

DECLARE @sql NVARCHAR(MAX), @Table1ID INT = 2000;

SELECT @sql = N'SELECT FieldData AS ' + QUOTENAME(ColumnName)
  + N' FROM @Table2 WHERE Table1ID = @Table1ID;'
FROM @Table1 WHERE Table1ID = @Table1ID;

EXEC sys.sp_executesql @sql,
  N'@Table2 dbo.Table2 READONLY, @Table1ID INT',
  @Table2 = @Table2, @Table1ID = @Table1ID;

Results, again:

B
------
B DATA

From a simplicity (and portability / forward compatibility) perspective, I would argue for the former solution where you just use #temp tables instead of table variables and a TVP.


Do temporal tables log changes when there are none? Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema:

create table blah (
    foo int identity(1,1),
    baa int
)

and I were to run the following statement:

update blah set baa = baa

would it log changes as if I'd actually updated data?

I've had a look around the MSDN article for temporary tables and the one for Modifying Data but I can't find anything there specifying this.

Converting my comment to answer ..

If the value is actually updated, then it will log changes. If you update the same value, new versions for every row will be recorded, but the column values will remain the same.

Below is the working example :

CREATE TABLE dbo.blah1
    -- PK is a requirement !!
    (
    foo INT identity(1, 1) PRIMARY KEY
    ,baa INT
    ,SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    ,SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    ,PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON);

-- insert data
insert into dbo.blah (baa)
values (1)

Now check for the data

-- check data
    SELECT * FROM  dbo.blah;
    select * from [dbo].[MSSQL_TemporalHistoryFor_629577281]

perform dummy update

update blah
set baa = baa

Perform onemore dummy update .. there will be rows with same values but different systime - start and end.

You have to have a proper mechanism to clean up history table as that can grow significantly larger if you forget to trim it.


"Manage" option vanished on portal for Azure SQL database I had a database which I deleted and it used to have a "Manage" option which would open the online sql management portal.

I created a new database, now I don't have the "Manage" option anymore.

I cannot connect when I try to go to the same URL with my new server name.

I'm also having issues connecting to this new database using Entity Framework. The error message:

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: SQL
  Network Interfaces, error: 26 - Error Locating Server/Instance
  Specified)

The manage button is no longer available for V12 SQL databases on Azure. Official response from support engineer at Azure:

  The Manage button is not available on Azure SQL Database V12. It is
  only compatible with the previous version of azure SQL Database, V2
  (or V11). With the new database that you created, you may use either
  Visual Studio, or SQL Server Management Studio to manage them.


Data Corruption on base backups created with wal-e? I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. The WAL files are also archived to S3 by using the same tool.

At some point, when I had to replace the slave instance, I ran a wal-e based script that loads the base backup from S3 and writes it to $PGDATA. After that when I started up the database, I got the following errors:

...
Apr 10 10:15:29 dbc postgres[25865]: [12-1] 2014-04-10 10:15:29 UTC LOG:  restored log file "00000005000000B800000089" from archive
Apr 10 10:15:31 dbc postgres[25865]: [13-1] 2014-04-10 10:15:31 UTC LOG:  restored log file "00000005000000B80000008A" from archive
Apr 10 10:15:33 dbc postgres[25865]: [14-1] 2014-04-10 10:15:33 UTC LOG:  restored log file "00000005000000B80000008B" from archive
Apr 10 10:15:47 dbc postgres[25865]: [15-1] 2014-04-10 10:15:47 UTC LOG:  restored log file "00000005000000B80000008C" from archive
Apr 10 10:15:47 dbc postgres[25865]: [16-1] 2014-04-10 10:15:47 UTC FATAL:  could not access status of transaction 0
Apr 10 10:15:47 dbc postgres[25865]: [16-2] 2014-04-10 10:15:47 UTC DETAIL:  Could not open file "pg_subtrans/2214": No such file or directory.
Apr 10 10:15:47 dbc postgres[25865]: [16-3] 2014-04-10 10:15:47 UTC CONTEXT:  xlog redo hot_update: rel 1663/24577/5279107; tid 22/32; new 22/84
Apr 10 10:15:47 dbc postgres[25863]: [1-1] 2014-04-10 10:15:47 UTC LOG:  startup process (PID 25865) exited with exit code 1
Apr 10 10:15:47 dbc postgres[25863]: [2-1] 2014-04-10 10:15:47 UTC LOG:  terminating any other active server processes

The server does not start up.

Does this mean that my base backup is corrupted? Or does it mean that some WAL files are corrupted?

The master server is running Postgres 9.1.12 (Debian Wheezy), but the newly launched slave is running Postgres 9.1.13 (Debian Wheezy) - this happened because when installing the new machine, only the minor upgraded version of Postgres 9.1 was available in the repositories. My understanding is that this should not be a cause of the problem.

Interestingly, if I do an rsync of the master's data directory to the slave, and then start the slave with streaming replication setup, the slave comes up fine.

Because of this it is not clear if I have a corruption in my DB data directory (which is on an AWS EBS drive) or if there is something wrong with the backup tool wal-e. Any help is appreciated.

I've experienced this problem with wal-e up to 0.9.2. Manually creating the pg_subtrans directory seems to have got replication working again.


Can converting MyISAM to InnoDB cause problems? I am changing an old MySQL database table which is still in MyISAM. I'd like to change it to InnoDB, but I don't know whether it can cause problems and it's a pretty important table so I don't want to break anything.

So my question: can it cause problems when I convert a MyISAM table to InnoDB?

Yes, it can cause a number of troubles.  However, troubles are rare.

All the 'troubles' I know of are listed here.

(Do not change tables in the databases mysql or information_schema or performance_schema; that will cause serious trouble.)


mariadb / mysql wont change table_cache / table_open_cache I've configured my i7 64gb ssd server with centos7 with the following my.cnf

[mysqld]
key_buffer = 12M
max_allowed_packet = 16M
table_cache = 4096
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size = 48M
read_buffer_size = 32M
read_rnd_buffer_size = 104M
myisam_sort_buffer_size = 100M
myisam_max_sort_file_size = 1024m
#myisam_max_extra_sort_file_size = 1024m
myisam_repair_threads = 1
thread_cache_size = 32
query_cache_type = 1
query_cache_size = 512M
query_cache_limit = 8M
max_connections=50
wait_timeout=60
tmp_table_size = 256M
max_heap_table_size = 64M
#innodb_io_capacity = 400
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 24M

But when i run mysql_tuner.pl
i still get

TABLE CACHE
Current table_open_cache = 482 tables
Current table_definition_cache = 4096 tables
You have a total of 1027 tables
You have 482 open tables.
Current table_cache hit rate is 15%
, while 100% of your table cache is in use
You should probably increase your table_cache

Of course i restarted my mariadb instance. but i have no clue.
Search turned up empty as well.

Hope someone could help out Cheers.

The operating system has a low limit.  In Unix, see ulimit -n, and change it.  Some versions have an unnecessarily low limit of 1024.

Most, but not all, things set by my.cnf (my.ini) stay set.  table_open_cache is one that is changed based on the environment.  Use SHOW VARIABLES to see the real values.  Or SHOW VARIABLES LIKE 'table_open_cache' to see the one setting.


Why is .bak file size increasing/doubling with each consecutive backup on SQL Server? I'm running a simple DB on SQL Server 2012 Express.

Just today, when I backup the database, the .bak file size doubles to what it was from the previous backup just minutes before. I have done several backups today (through SQL Server Management Studio -> backup type: full), and with each one the .bak file keeps on doubling.

In SQL Server Mgmt Studio, when I right click on my DB -> 'Reports' -> 'Backup and Restore events' -> expand 'Successful Backup Operations':

The report here shows that the latest backup size was recorded as 55MB, but when I go to the actual .bak file it is 260MB. Each other backup today was recorded also as 55MB size, while their corresponding .bak files are multiple times larger (and growing with each backup operation).

What could be going wrong? I haven't made any changes to the database since this started happening.

The backup file contains multiple copies of the database since it is not being "initialized" or "formatted" with each backup.

Go to the 'Options' tabl on the "Back Up Database" dialog box, and select the "Overwrite all existing backup sets":


SSRS 2017 add datasource "Something went wrong" when trying to add, view or change data source(s) in SSRS 2017 I get popup saying "Something went wrong, please try later".
Setup os SSRS is as follows:
three SSRS instances behind KEMP load balancer. Communication from clients to KEMP is done over SSL (port 443) and from kemp to SSRS instances over HTTP (port 80). rsreportserver.config file has this tags on all three SSRS servers, where SSRS.domain.com points to the KEMP virtual IP (for client access).
SSRS.domain.com
https://SSRS.domain.com/reportserver
When I try to add, view or change data source(s) I get this error in RSPortal.log
ERROR: OData exception occurred: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 10.2.17.11:443
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
--- End of inner exception stack trace ---
10.2.17.11 is the IP of the one of SSRS instances buth what baffles me is why is connection trying to be made using port 443 (TLS) when KEMP is configured to communicate with SSRS instances over port 80.
SSRS instances don't have port 443 configured, only port 80.
Everything else works fine, only datasources are the problem.
SSRS version in question is 14.0.600.1109, native mode.
Any help would be appreciated.

After further investigation I solved the problem by configuring SSRS instances to use both HTTP and HTTPS - NLB is communicating over HTTP but if needed (in case of data sources) SSRS instances accept communication over HTTPS


Migrate Oracle BLOB to Postgres BYTEA or OID After the BLOB columns of Oracle (11g r2) tables are migrated into a Postgres (9.4.5) table such as using Ora2Pg, I have two questions:

1) Which data type will the new column assume, is it BYTEA or OID?

2) Will the (BLOB) data be copied over correspondingly?  Or I need to do a separate migration of the data?

1) BYTEA.  Because oid is an unrelated concept.

2) Work mostly out of box, see https://github.com/darold/ora2pg/issues/100 .


MSSQL - find immediate past iteration of field Bit of a difficult to explain one here, I have a table with product SKUs of different generations (or iterations) and I need to identify the most recent iteration that exists for each.
Timeline: Product X gets entered into the table, after a few months product X gets a revision and X(B) appears and is entered, sometimes iterations or revisions get skipped in this table. This is always done in an alphabetic ascending way - (B) is the first iteration, (C) follows, etc.
I need to identify what the immediate past iteration is for every one of these.
Example data set in column A and desired result in column B - can this be achieved or is it an impossible task?
Thank you in advance.
SKU; previous iteration (if applicable);
ABCD; ;
ABCD(B); ABCD;
ABCD(E); ABCD(B);
ABDX(B); ABDX;
ABDX(F); ABDX(B);
ABDX; ;
BCDF(P); BCDF(C);
BCDF(C); ;
BCDX; ;
BCDX(B);BCDX;
BCDX(G);BCDX(B);

If you're using SQL Server 2012 or later you can use LAG, although you'll need to handle the lack of normalization first:
SELECT
  SKU
 ,CASE
    WHEN SKUPrefix <> LAG(SKUPrefix,1,'') OVER (PARTITION BY 1 ORDER BY SKU) THEN ''
    ELSE LAG(SKU) OVER (PARTITION BY 1 ORDER BY SKU)
  END AS PriorSKU
FROM
  (
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
  FROM
    <Your Table>
  ) SKUFix

One might as "Why not partition by SKUPrefix and avoid the CASE statement and the answer is if a suitable index of the table (clustered or otherwise) starts with SKU then the query will be able to execute without sorting the data.
Prior to 2012 things are... uglier:
WITH SKUTemp AS
(
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
   ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SKU) AS SKURank
  FROM
    <Your Table>
)
SELECT
  SKU.SKU
 ,PriorSKU.SKU AS PriorSKU
FROM
  SKUTemp SKU
LEFT JOIN
  SKUTemp PriorSKU
    ON PriorSKU.SKUPrefix = SKU.SKUPrefix
        AND PriorSKU.SKURank = SKU.SKURank - 1

DDL to test:
CREATE TABLE #SKUTest
(
  SKU   CHAR(8)   NOT NULL
)
GO

INSERT INTO #SKUTest
VALUES
  ('ABCD')
 ,('ABCD(B)')
 ,('ABCD(E)')
 ,('ABDX')
 ,('ABDX(B)')
 ,('ABDX(F)')
 ,('BCDF(C)')
 ,('BCDF(P)')
 ,('BCDX')
 ,('BCDX(B)')
 ,('BCDX(G)')
GO

Results:
SKU         | PriorSKU
ABCD        | NULL
ABCD(B)     | ABCD
ABCD(E)     | ABCD(B)
ABDX        | NULL
ABDX(B)     | ABDX
ABDX(F)     | ABDX(B)
BCDF(C)     | NULL
BCDF(P)     | BCDF(C)
BCDX        | NULL
BCDX(B)     | BCDX
BCDX(G)     | BCDX(B)


Recover accidentally deleted rows from a table My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup.

Is it possible to restore the deleted rows from the transaction log (.ldf) file? If yes, how?

If your database is in full recovery mode you can also try third party tools such as ApexSQL Log or SQL Log Rescue.

These tools will attempt to read your transaction log and reconstruct statements.

You can also try reading transaction log manually using fn_dblog function but it’s going to be complex since this is not a well-documented function.


Why and When the pg_stat_activity's most of the columns are null? Recently, I have noticed that when I query the pg_stat_activity for few users most of the columns are [null] (pgAdmin 4) in the result.

Only thing I can see is datid, datname, pid, usesysid, usename, application_name, query, rest all are [null].

There is an interesting part here. When I execute

select * from pg_stat_activity where query = '<insufficient privilege>'

is returning same rows as

select * from pg_stat_activity where state is null

and same as

select * from pg_stat_activity where client_addr is not null

What could be the reason for this?

(I am using RDS Aurora Postgresql 9.6.12)

That must be because you don't have permission to see these user's activities.

Statements can contain sensitive data, so they are only visible to superusers by default.

Some rows in pg_stat_activity also contain many NULLs because they belong to background processes.


How to restore output from pg_dump into a new table name I am dumping a large postgres table (~15m records) like this:

pg_dump -h myserver  -U my_user --table=mytable  -Fc -Z 9 --file mytable.dump mydb

The above creates a mytable.dump file. I now want to restore this dump into a new table called mytable_restored

How can I use the pg_restore command to do this?

This doesn't sound like the right tool for the job in the first place. Couldn't you just COPY...TO, then COPY...FROM?  or insert into new_table select * from old_table, if they are in the same database?

But given that you already have the dump file, the most straightforward way might be to load to a scratch database, rename the table, and retake the dump from there, then drop the scratch database.

Alternatively, you could use "pg_restore" without a "-d" option to dump the custom format file to a plain text dump file, then edit that file manually to change the name in each location it occurs.  Editing a file with 15 millions rows can be quite a chore, but it should be possible with something like "vi", or perhaps "sed" (but there you have to be careful not to change more than you intended to).


Informix Migrate Schema I'm busy trying to migrate an informix database schema (with no data) to an informix database running on a different server.

I have exported the schema from the database using

dbschema -t all -d olddb schema.sql

Then I am trying to import it into my new database

dbimport /tmp/schema.sql -d newdb

I am getting the error

Cannot open sql script file

Looking through informix forums they are saying that this is because it cannot find the sql file but how can that be when I a specifying where it is?

Also if I go into the directory where the sql file is and try

dbimport schema.sql -d newdb

I get the same error

Never mind it seems dbimport won't work in this case because the schema wasn't generated with dbexport.

It worked when I did

dbaccess newdb /tmp/schema.sql


Second index on junction table with composite primary key: include both or one column I'm working on a database that will be very read-intensive, and uses a junction table that's read often and in both directions.

The junction table contains two columns, IDA and IDB

The primary key for the junction table will be a composite key for both columns.

I want to create an index so the database can quickly seek the corresponding IDA for a specific IDB.

Should that index contain both IDB and IDA? Or should it just contain IDB?

To put it another way, should my table look like this:

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    CONSTRAINT AK_ExJunction_IDB_IDA UNIQUE (IDB ASC, IDA ASC)
)

Or like this:

CREATE TABLE ExJunction(
    IDA INTEGER NOT NULL,
    IDB INTEGER NOT NULL,
    CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
    INDEX IX_ExJunction_IDB NONCLUSTERED (IDB ASC)
)

Or, should I do something else altogether?

I'm having trouble conceptualizing this, since I've always thought of nonclustered indexes containing both the index key and the primary key. But the index key is already contained in the primary key, so, are these the same (both containing IDB and IDA), or different?

You should go for the first one.

With the second option you will end up with the first one anyway but this is implicit and so not necessarily obvious to all potential consumers.

(SQL Server always adds the clustered index key to all non clustered indexes - if they are not declared as unique they will become part of the key)


Set maximum cpu usage to a single job in SQL server Is it possible to restrict cpu usage for a job in SQL Server?

I found Resource Governor does work for an instance of mssql for a specific users group. However, I only need to set the maximum cpu usage to a single Job not to the instance of mssql as there is only one user that performs all the operations in the database.

You can set the job to be owned by a specific login, and use the Resource Governor to limit CPU for that login.

CREATE RESOURCE POOL [Pool_ThatJob] WITH
(
    max_cpu_percent=50,
    cap_cpu_percent=50 -- 2012+ only
);
GO

CREATE WORKLOAD GROUP [Group_ThatJob]
  USING Pool_ThatJob;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT CASE ORIGINAL_LOGIN() WHEN N'that_login'
    THEN N'Group_ThatJob' ELSE N'default' END);
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

This won't take effect if the job is already running; it will come into play the next time the job starts (or if that user establishes any other sessions).


Declare table as not replicable How can I make sure that a certain table is not selected as an article in a publication? So that this table is never replicated (even by error).
The replication for the database is setup by different people and one particular table should never be included in the replication. Can we make that table non selectable in the publication wizard (the step where the articles are added to the publication) or is there any way we can use the database schema to define that this table is not available as article in a publication?

Type of replication used: merge replication

Thanks!

The only way I know to do that is to leverage this limitation:

  Tables published for transactional replication must have a primary
  key.

Publish Data and Database Objects

So replace the table's primary key with a unique clustered index called pk_MyTable_PreventReplication or somesuch.

Merge replication requires a special ROWGUIDCOL column on the table, which you can prevent with security or a DDL trigger.


PostgreSQL insert not occurring serially I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key)

So if, for example I have ids like 1521, 1522, 1527, the id of the next insert will be 1523 and not 1528 as I expected and would like to occur.

The missing ids may be due to the fact that the database that was migrated from MySQL.
The id has the following definition:
Data type: bigint
Default value: nextval('fleetmanager.workorderjob_id_seq'::regclass)
Not null
Storage: Plain

I use PostgreSQL version 10 on Windows.

How can I get it to always do insert after the last id?

There is absolutely nothing wrong with Postgres to act like this and it has a reason. According to Postgres's documentation (in Notes section):

  sequence objects cannot be used if "gapless" assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

And again in another page it says:

  Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.


Run stored procedures in other schemas from stored procedure I have a current process: a large procedure in one schema.
There is a plan to have A, B1, B2, B3 schemas (B-schemas are identical, could be B-n schemas) in Oracle 12c database.
Schemas B1, B2, B3 have synonyms to A.
Schema A stores all the connections information to other schemas.

Is it possible to run a stored procedure in A and call the stored procedure in other B-schemas (dynamically determine schemas to run and do it in parallel).

There is also .Net part and it is better to put this logic there and make calls to the B-schemas stored procedure in different threads?

You can call procedures in schema B from procedures in schema A. Depending on the version of Oracle, you may have to grant execute privileges on each procedure explicitly from B to A, using the "with grant" option, rather than through a role. Consult documentation for your specific version to verify what permissions are required.

In general I would also recommend putting orchestration logic into the back-end (database) rather than client-side code. Keep your back-end and front-end business logic separate.


Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' I have an application which is getting data from Oracle(11.2.0.4) Materialized view, there is expected behaviour about materialized view will go INVALID when
any DML action happens on the associated tables or assocaited objects are invalid.

My materialized view opotions are as listed below and confirming that associated objects are valid all the time.

CREATE materialized VIEW mv_emp_test1
NOLOGGING COMPRESS build IMMEDIATE
REFRESH FORCE
START WITH (SYSDATE) NEXT (SYSDATE+5/1024)
as
......

Materialized view go into INVALID state when it freshes after every 5 minutes and become valid if any of the following method applied:

alter materialized view mv_emp_test1 recompile;
or
exec dbms_mview.refresh('mv_emp_test1');

Also, checked related documents and bugs, Doc ID 264036.1 in metalink has included the exact scenario but didn't find proper workaround so that auto refresh in every 5 minutes.

That is the expected behaviour.

If the data in the base table changes and the view isn't up to date anymore its status is not valid anymore. If you recompile or refresh it will become up to date and is valid again. So you don't have to bother about the invalid state of your view.

This question was already posed on stackoverfow.com and answered by Andrew Brennan:

  See MOS Doc ID 264036.1:

    Dependencies related to MVs are automatically maintained to ensure
    correct operation. When an MV is created, the materialized view
    depends on the master tables referenced in its definition. Any DML
    operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on
    any dependency in the materialized view will cause it to become
    invalid.


What is the purpose of keeping filegroup or filestream backup in MSSQL? I'm a new DBA for SQL SERVER. Currently I'm learning about filestream on SQL server. So I tried to restore only filegroup of filestream to other database, but I get an error message:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  File 'testdb1' was not backed up in file 1 on device 'C:\data\New
  folder\FSbackup.bak'. The file cannot be restored from this backup
  set. RESTORE DATABASE is terminating abnormally. (Microsoft SQL
  Server, Error: 3144)

Is it possible to restore only filestream to other database or restore only an old version of filestream to the current database of FILESTREAM?

So I tried to restore only filegroup of filestream to other database, but I get an error message

As Laughing Vergil originally mentioned in a comment, your question is answered in this great Stack Overflow post: Restore filegroup to different database

The gist of the answer is that you always have to start by restoring the database with the PRIMARY filegroup.  Then you can restore additional filegroups (like the filestream one) after that.  This called a "piecemeal restore" and is described in the documentation.

  Is it possible to restore only filestream to other database

No, as mentioned above it needs to be restored along with the PRIMARY filegroup.

  or restore only an old version of filestream to the current database of FILESTREAM?

Tibor responded to this part in a comment.  The basic answer is "no."  This is because of the way database recovery works - you can't restore different parts of the database that are from different points in time.

As a side note, there is a restriction specifically related to filestream filegroups:

  If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported.


Is it better to have a CASE expression or a JOIN with a dimensional table? I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:

SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(timestamp,'week') = 1 then 'Tuestday'...
FROM fact_table

Or I could create a dimensional table like the following:

day_number day_name
0          Monday
1          Tuesday

and then make a JOIN with the fact table:

SELECT day_name
FROM fact_table
LEFT JOIN dm_days
on day_number = date_part(timestamp, 'week')

Which one is better?

What if instead of a date is some indicator (for instance where the traffic comes from: newsletter, SEO, affiliates...) that does not require any function (like date_part) to be obtained? For example, would this be better:

SELECT CASE id_channel WHEN 0 then 'SEO'
WHEN 1 then 'Newsletter'...
FROM fact_table

Than this?
    SELECT channel_name
    FROM fact_table
    LEFT JOIN dm_channels
    on channel_id = id_channel

For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)

SELECT to_char(timestamp, 'Day')
FROM fact_table

In general the table form is, to me, cleaner (when there is no suitable function)

Perhaps with an in-line table if there is a good reason to not have a permanent table.

SELECT day_name
FROM fact_table
LEFT JOIN (VALUES (1,'Monday'),(2,'Tuesday'),(3,'Wednesday')) AS days(day_number ,day_name )
on day_number = date_part('dow',timestamp)::int


What does autogrow run in the background (Is it a DDL statement?) I am currently running into the following intermittent error from my applications logs

  Commit - ERROR: Code=0x80004005 Source=Microsoft OLE DB Provider for SQL ServerDescription=Insert bulk failed due to a schema change of the target table.

I am unable to find out what DDL statement would be causing this and have found nothing in the SQLServer logs themselves. (I assumed offline indexing, but that is not happening)

My hypothesis is that the autogrow event is set too low and the intermittent problem is caused when it gets triggered.

Is this a valid hypothesis and is there a method of testing it?

As documented by By Parikshit Savjani in
Insert bulk failed due to a schema change of the target table:

This error says that there have been changes in the schema that require the re-compilation of the plan and Insert bulk cannot support recompiles.
Any Operation on the Target tables which would require the recompilation of the query will cause this error messages.
In a normal scenario, the Schema Change could be addressed by a re-compile, however it turns out that for BCP re-compiles are not possible because at compile time, we are reading metadata from the input stream and we set up our statement based on that, and then basically discard that metadata. If we recompile, that metadata is completely lost, and compilation is no longer possible.

There is no reason that auto growth of a file would cause a plan recompile.
I would suspect auto statistics update as the most likely option but the link does mention some other possibilities:

Parallel Bulk Inserts happening on the same table, and foreign keys are enabled on the table.

Parallel Online rebuild index operation running.

It also mentions that bulk inserting with the option to ignore constraints will bump the internal metadata version, which can lead to this error. The workaround is to drop the constraints before the bulk operation, then recreate them afterward.
You could turn off autostats for the table using norecompute for the duration of the operation and then update them afterwards.


Error message when importing data into SQL (Import & Export Data Wizard) I am trying to use the Import & Export Data Wizard to import the contents of a CSV file into a table in MSSQL.

I have no issues if I import all columns as strings however, I need one column to be a float. (In the CSV, column 4 is a currency amount with two decimals e.g. $200.30).

Whenever I set the value of column 4 to a float, I receive the following error during the import:

  Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
  conversion for column "Column 4" returned status value 2 and status
  text "The value could not be converted because of a potential loss of
  data.".  (SQL Server Import and Export Wizard)

Here is an example of the data which I am importing:

+----+------+----------+-----------+--------+
|  1 | 2014 | December | 400089323 | 8.03   |
|  2 | 2014 | December | 400319749 | 45.65  |
|  3 | 2014 | December | 400377067 | 38     |
|  4 | 2014 | December | 400524693 | 0      |
|  5 | 2014 | December | 400536070 | 125.31 |
|  6 | 2014 | December | 400663546 | 266.95 |
|  7 | 2014 | December | 400705907 | 218    |
|  8 | 2014 | December | 400763025 | 244.39 |
|  9 | 2014 | December | 400782510 | 12.89  |
| 10 | 2014 | December | 400794040 | 61.04  |
+----+------+----------+-----------+--------+

This is the datatype of column 4:

The rest of the columns are "numeric [DT_NUMERIC]" except column 2 which is "string [DT_STR]".

I have tried different datatypes for column 4 such as numeric & currency. This did not work.

FYI: I am using SQL Server 2012.

Import it as a string and do a derived column to convert it to a float. Whatever rows fail conversion will be re-directed to the error output and you can at least see what the problem is.

Flat File connections fail completely when they encounter problems. It's generally more productive to bring them in as loosely as possible and adding structure inside SSIS.

You almost certainly don't want to be using floats anyway. Use 'numeric' once they're inside the package.


Query to obtain indexes information - why does it produce duplicated values? SELECT  OBJECT_NAME(S.OBJECT_ID)                                  AS 'TABLE NAME'
       ,I.NAME                                                    AS 'INDEX NAME'
       ,I.INDEX_ID                                                AS 'INDEX_ID'
       ,USER_UPDATES                                              AS 'TOTAL WRITES'
       ,USER_SEEKS + USER_SCANS + USER_LOOKUPS                    AS 'TOTAL READS'
       ,USER_UPDATES - ( USER_SEEKS + USER_SCANS + USER_LOOKUPS ) AS 'DIFFERENCE'
       ,SUM(PS.[USED_PAGE_COUNT]) * 8                             AS 'INDEXSIZEKB'
       ,IXS.PAGE_COUNT                                            AS 'PAGECOUNT'
       ,IXS.AVG_FRAGMENTATION_IN_PERCENT                          AS 'FRAG'

FROM       SYS.DM_DB_INDEX_USAGE_STATS     AS S
INNER JOIN SYS.INDEXES                     AS I ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID
INNER JOIN SYS.DM_DB_PARTITION_STATS       AS PS ON PS.[OBJECT_ID] = I.[OBJECT_ID]  AND PS.[INDEX_ID] = I.[INDEX_ID]
INNER JOIN SYS.TABLES                      AS T ON T.OBJECT_ID=S.OBJECT_ID
INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS  (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON IXS.OBJECT_ID=T.OBJECT_ID
WHERE OBJECTPROPERTY(S.OBJECT_ID, 'ISUSERTABLE') = 1
      AND S.DATABASE_ID = DB_ID()
      AND USER_UPDATES > ( USER_SEEKS + USER_SCANS + USER_LOOKUPS )
   -- AND I.INDEX_ID > 1
GROUP BY S.OBJECT_ID ,I.NAME ,I.INDEX_ID ,S.USER_UPDATES, S.USER_SEEKS, S.USER_UPDATES, S.USER_SCANS, S.USER_LOOKUPS, IXS.PAGE_COUNT, IXS.AVG_FRAGMENTATION_IN_PERCENT
ORDER BY 'DIFFERENCE' DESC, 'TOTAL WRITES' DESC, 'TOTAL READS' ASC

I made this query to list index id, writes, reads and etc, but all values are duplicated only with pagecount and fragmentation different. what am I missing?

If you check where the different column values come from (PAGE_COUNT and AVG_FRAGMENTATION_IN_PERCENT), you will see they are from the system function SYS.DM_DB_INDEX_PHYSICAL_STATS.

Just before joining with SYS.DM_DB_INDEX_PHYSICAL_STATS you have joined SYS.INDEXES with SYS.DM_DB_PARTITION_STATS, so your row is detailed by object_id, index_id, partition_number. Try joining by these 3 against SYS.DM_DB_INDEX_PHYSICAL_STATS (since the function result is actually detailed by these 3):

INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON
    IXS.OBJECT_ID = T.OBJECT_ID AND
    IXS.index_id = I.index_id AND
    IXS.partition_number = PS.partition_number


Whats the best practice when using Amazon Redshift as a cache I have tested Amazon Redshift as a cache. So I save data by a key. But with the time the cache becomes slower and slower. Once I do a VACCUM on the database it get backs to the speed. But should i run VACCUM continuously in the database ?.  Or is there a better way of using redshift tables for continuous reads, writes and deletes.

I would not recommend using redshift as a cache. Have you considered Redis?

Redshift saves all data to disk in 1MB blocks, in an order established by your sortkey, and distributed between nodes based on your distkey. When ever you create, update, delete you are appending data to the last blocks of the database. These blocks that hold all the new changes are not sorted until you vaccume the database.

Hence, when you use redshift as a cache you are very quickly fragmenting your data, making your queries slow.


Automate SQL Server install and restore DB I’ve been tasked to automate our sql server express installation and with that, create a database from a .bak file.  I found a way to automate the install using a Configuration.ini file which works great.  The part I’m having trouble with is restoring a database as part of (or after) the process.

Does anyone know if this is even possible?  I know 3rd party tools like Red Gate can probably do it but they are rather expensive and the company I work for does not have a big budget for development tools.

A great choice is dbatools. They have a command (among many, many others) that can help you with this:

Restore-DbaDatabase

An example,

Restore-DbaDatabase -SqlServer server1\instance1 -Path \\server2\backups\$

It can do a lot more complicated stuff, too! Check out the documentation for more info.


Can I "Execute T-SQL Statement Task" against all user databases? I'm setting up a maintenance plan on Sql Server.

I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as you can with pretty much every other task type.

Is there a way to do this, perhaps with an "in-place" parameter in the script that would be used to substitute the database name?

I want to run a nightly script in the maintenance plan, but it needs to be able to put the database name in the script.

Should I call a stored procedure from the maintenance plan that iterates through all the databases desired using dynamic SQL to execute my statement?

This doesn't address your primary question but I can't help but propose a different approach than the one you're currently taking. You should be backing up the transaction log frequently enough that, in the event of a disaster, you won't lose any more data than your SLA allows. If you can afford to lose up to 30 minutes of data, then you should probably be backing up the transaction log every 15 minutes. You can certainly do this through the maintenance plan wizard (including picking which databases to run on, and one option is definitely "All user databases"):

Your current approach of switching to simple, running a shrinkfile, then switching back to full, is quite self-defeating. You're in full recovery model but you're not getting any of the benefits from it. Backing up the log throughout the day will keep your t-log size down as backing up the log allows space within the file to be re-used. If you don't need point-in-time recovery, then switch to simple recovery. This allows the space within the log file to be re-used automatically. In either case you shouldn't have to manually shrink the file anymore - which is very expensive process to repeat every night (never mind one that impacts users every time it auto-grows during the day).


Will this Database design logic work and what should be used? A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com

What he essentially wants to do is provide external sales reps within a specific industry access to use this CRM application for managing all of their contacts, clients and communication/tasks.

The key difference between highrisehq and this system is that he wants to allow each of these sales reps the ability of "sharing" data with one another. Now what I mean by this is the following:

Lets assume there were two separate CRM account users within the system, each of these accounts have thousands of contacts and hundreds of relationships (linking companies to contacts, contacts/companies to projects, and contacts to messages/tasks/notifications).

Now... what he wants to do is allow both of these sales reps each with their own  independent accounts to be able to share information with one another.

My thought was that the first thing which should happen here is a "share" request be initiated through the system by one user to the other user. Upon this share request being accepted both accounts now have the "ability" of sharing info with either other.

My thought was that once these accounts are linked that user can now show up as a "sharing user" and show up in all areas where you would normally set the view/edit permissions for individual users.

Where things get complicated in my view is how to show the "activity stream" for example because essentially now the system is not just showing all the activity for my account but also all the activity for external accounts for which the user has been granted access.

Furthermore... if a user has lets say 100 accounts he is sharing information with is there anything I can do to ensure the system does not slow to a crawl?

What is the most logical, best and least expensive db logic which should be used to create these relationships so a query on a single account returns data at the same speed as when having to access data from multiple accounts?

Any other key aspects which you guys feel should be considered or are "must have" features?

I really have to encourage you to look at the way DB schemas are handled by existing CRM systems and consider whether (1) you wouldn't be further off customizing one of these systems and (2) you're not biting off more than you can chew.

Most CRM systems I've seen have extensive customization capabilities built into them.  Typically, this includes the ability to extend and/or modify the definitions of the built-in entities and the ability to add new entity types and relationships.  Many of these CRM systems also have the ability to set up some pretty funky security setups, and some might be able to do something similar to what you're suggesting.

I'd also encourage you to try to pry a business scenario out of this client, as well.  What's got him convinced that sales reps are going to share information?  If they're working together on a team, then existing CRM systems have this covered already.  If not, then I'd be a little suspicious about the premise of information sharing.  Understand that sales reps are going to follow the money with respect to their behavior.  If they're paid to share information, then it'll happen; otherwise, you're going to have to beat them with a stick to share anything.


Truncating log while database is in use New to SQL server.
I have to truncate log on printing database as the server is almost out of space:(other stuff on server too)
I know I must :

USE master
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE (TransactionLogFileName, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT
GO

My question is.
Can I do it while database in use? It's used all the time being the printer database.

First thing is after changing recovery model from full to simple you loose point in time recovery. Is this database production database(which i feel it is)? if so you are not doing correct thing. If database fails at this point in time you would loose point in time recovery and would loose data are you ok with it.

If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, its strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction lo

  My question is. Can I do it while database in use? It's used all the time being the printer database.

Yes you can do it. But is it not possible for you to take transaction log backup on some other drive. Transaction log backups will truncate logs(if no long running transaction still requires it) and then you can shrink the log file. You must also note that shrinking logs also cause performance issue if you are doing it for just one time its ok.

Why did log file grew out of proportion have you thought about it ? I guess because you did not took transaction log backups. Please schedule proper transaction log backup for the database

if you don't require point in time recovery why not let database in simple recovery mode and you can still take full and differential backups.


What does  mean in Relational Calculus? I saw a Tuple Relational Calculus formula, and it contained the symbol <>

I couldn't find any reference to it anywhere - can anyone tell me what this means?
(perhaps this question will be a future first Google result)

<> is not defined in relational calculus (or anywhere in math). It however is often used in programming languages (like SQL) to express "not equal". The correct sign would be the equal sign with a vertical line through its center. (= and | on top of each other).


"Logon failed for login 'sa' due to trigger execution." When No SA Login Triggers Defined Last night, I received some error messages in my SQL Server instance stating:

"Logon failed for login 'sa' due to trigger execution."

The instance has a number of logon triggers enabled, but after verifying them, none of them would reject the 'sa' login. The logon triggers that exist are all in this format:

CREATE TRIGGER [forbid_bar_connection_for_foo]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'foo' AND APP_NAME() like 'bar%'
    ROLLBACK;
END;
GO

and they exist as a stop-gap until we can get proper security in place. (I know that this is insanely easy to bypass.)

In none of the triggers do we check ORIGINAL_LOGIN() against 'sa'.

Around the same time that I saw this for the first time (followed by many of the actual logon triggers firing, far more than they should've), there appears to have been an out of memory event. A new database was being brought in, and there's a stack dump preceding this by about 30 seconds regarding a non-yielding IOCP Listener.

My assumption is that there was a memory issue at play, and this caused the logon triggers to not fire correctly, and thus rollback access to sa. Is this reasonable? Is there another explanation for a logon trigger not allowing a login that it does not check against?

Thanks for any insight!

"Logon failed for login 'sa' due to trigger execution."

For security reasons logon triggers don't report errors to the end user, but they are captured in the SQL Log.  From Docs:

  all messages originating inside the trigger that would typically reach
  the user, such as error messages and messages from the PRINT
  statement, are diverted to the SQL Server error log.

Logon Triggers

  Is there another explanation for a logon trigger not allowing a login that it does not check against?

Of course you could always have a bug in one of your logon triggers.  Check the SQL Logs.  And of the Logs don't clearly indicate which trigger failed and why, then add more telemetry (PRINT or RAISERROR) to your triggers so that next time they will.


mongodb user for ubuntu EC2 instance I am trying to install mongodb on Ubuntu EC2 instance. However, I am confused about what user the DB would run as:

If I follow: http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/

Then it says : "mongodb"

If I follow : http://docs.mongodb.org/ecosystem/tutorial/install-mongodb-on-amazon-ec2/

It says : "mongod"

I think it leads me to inconsistent state. There was a process running is ps output for mongodb but sudo service mongodb status or stop says: Unknown Instance.

What should be the user of mongodb?

The reason for the difference is that the articles describe two different versions of Linux.  For the install MongoDB on Ubuntu tutorial you are talking about Ubuntu.  If you are following the install MongoDB on Amazon EC2 tutorial then you are talking about Amazon Linux.

Ubuntu uses deb/apt based packages (which gets you the mongodb user) and Amazon Linux uses rpm/yum based packages (which gets you the mongod user).  You are using Ubuntu according to your question, hence the relevant user for you is "mongodb".

I would point out though, that this likely has nothing to do with the issue you are having with the service status or shutting it down.  That's more likely some sort of problem with the wrong PID being referenced by upstart, or possibly an issue with MongoDB starting in the first place (check your logs).

I would recommend:

Stop MongoDB manually (log in with the mongo shell and issue the shutdown command)
Start the service again, with sudo service start mongodb
Verify that it started correctly (logs, login with the mongoshell again)
Re-check with sudo service mongodb status


COUNT and GROUP BY performance I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country.

I use this query:

SELECT country, COUNT(*) as hits
FROM hits_table
WHERE timestamp >= CURDATE()
GROUP BY country

But the dataset is huge. So if I add LIMIT 10 to only get the top 10 countries will it improve the performance?

SELECT country, COUNT(*) as hits
FROM hits_table
WHERE timestamp >= CURDATE()
GROUP BY country LIMIT 10

Also I don't need exact results I only need some approximate results. So can't I make the query so it skips every 2nd result. Will it improve the performance? Are there any other methods?

Short answer: No.

Detailed answer: Unless you add/already have an index there that covers the country column the database engine has no way of telling the total number of instances to count. Even if you limit the output the entire table has to be scanned to retrieve every instance first. Of course, since this is a logging table, with potentially a large dataset, I'd avoid adding indexes to the table so:

not to increase INSERT time
to minimize space/memory usage the indexes would otherwise require

Solution: create a second table that will keep a running total for all countries, that gets updated by a trigger on the hits_table or directly by the application that inserts the data into hits_table. Something in the lines of:

CREATE TABLE `country_hits_totals` (
  `country` VARCHAR(255),
  `date`    DATE,
  `visits_total` BIGINT
);

This would make the table very small and allow you to create a PRIMARY KEY on the country and date columns, dedicating minimum resources. Once that key is set up, you could employ a trivial upsert statement (see this SQL Fiddle session for a full test case with a trigger-based usage):

INSERT INTO `country_hits_totals` (
  `country`,
  `date`,
  `visits_total`
)
SELECT
  NEW.country,
  NEW.timestamp,
  1
ON DUPLICATE KEY UPDATE `visits_total` = `visits_total` + 1;

Query blocking note: In a heavy traffic scenario you ideally want to query country_hits_totals with non-blocking statements only. Otherwise the upsert on the trigger gets delayed, and by extensions entire transaction that kicked it off. In a perfect world country_hits_totals would get copied over to a data warehouse at the time of least daily traffic, and you'd run your BI queries on that warehouse. That said, this is large traffic/enterprise territory and possible solutions depend on how quickly you want to get the data. Say, if you need an update only once a day you're better off pre-aggregating data onto country_hits_totals on a schedule instead of relying on a trigger updating the rows live.


Why Postgres uses a custom defined index instead of the default one on the primary key I have a table called alpha with many columns, the primary key is just alpha_id. I have also an index (super_index) on that table that uses alpha_id and status, which is just another column.

I run a query against it,

EXPLAIN SELECT alpha_id,
       name,
       descr,
       data,
       to_char(past_date, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
       status,
       failure,
       FROM job
WHERE alpha_id = 'example_value';

and despite the fact that Postgres has created automatically an index for the primary key

pk_alpha

and that my only condition is using it, this one is never used, and super_index is always visited. Here is also the result of the explain command,

Index Scan using super_index on alpha  (cost=0.14..8.16 rows=1 width=798)
Index Cond: ((alpha_id)::text = 'example_value'::text)

I have read that all the decisions are based on statistics, but I do not understand the logic behind this one.

Additional info

The table and super_index are actually created using Liquibase, but this is what I get using pgAdmin,

    CREATE TABLE public.alpha
(
    alpha_id character varying(48) COLLATE pg_catalog."default" NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    descr text COLLATE pg_catalog."default",
    data text COLLATE pg_catalog."default",
    past_date timestamp without time zone NOT NULL,
    status alpha_status NOT NULL DEFAULT 'Suspended'::alpha_status,
    percentage_complete double precision NOT NULL DEFAULT 0.00,
    failure text COLLATE pg_catalog."default",
    CONSTRAINT pk_alpha PRIMARY KEY (alpha_id)
)

CREATE INDEX super_index
    ON public.alpha USING btree
    (alpha_id COLLATE pg_catalog."default", alpha_status)
    TABLESPACE pg_default;

pg_alpha is the index created by Postgres by default on the primary key, and I do not have it listed in the Indexes list on pgAdmin, so I do not know how to retrieve its definition.

Due to padding for memory alignment, adding a small column to an index can often take up no extra room.  The index might even be smaller, if it is fresher and so more densely packed.  And even if not, the cost estimate for looking up one row in an index is very weakly dependent on index size, so the cost is likely to be a tie between the two indexes.  When there is a tie between indexes they are broken arbitrarily, and it seems like it is the one created most recently that is usually chosen.


Lockdown database access on certain tables I have a dev team who have a number of SQL databases used as a back end for mostly excel front end.
This originally came from an excel spreadsheet until it got made into SQL databases.
At first, they were not too concerned about security and decided to give access to a domain group logon that contains basically all the organisation's users.  Over time they have put more sensitive information into the databases and have realised that anyone in the organisation can view the information if they were to use another tool, power BI or SSMS due to the domain logon.
They say that they still need to use the domain logon as everyone needs access but wants to secure the sensitive information on certain tables but still be accessible to their queries from excel.
They have suggested Signing Stored Procedures with a Certificate but I am concerned they will want to try this with all their code placed in SPs, removing the domain logon, and this would create numerous amounts of admin to maintain all of them.
How am I able to implement this in SQL Server in another way?

There's no need to bother with certificate signing of stored procedures.
If I'm reading your question correctly, your requirements are essentially this:

All data is in one SQL Server Database
All authentication is done via a domain group, which most individuals are part of
End users primarily access data through Excel, but do use other tools
For most data, it is OK for users to freely query the tables directly
For some data, users should not be able to read the raw data directly, but should instead only use approved queries

The last requirement is the change you're looking to implement, for which you are considering cert-signed stored procedures.
The answer is in stored procedures, but there should be no need to bother with cert-signing.
Ownership chaining!
SQL Server allows for ownership chaining, which essentially means if you GRANT EXECUTE on a stored procedure, the procedure will be able to access the data it needs, and return an accurate result, even if the user doesn't have direct access to the data.
An example
First, lets set up a database, a SQL Login (though, in your case, you'd just use your domain group), and some data.
--Create a database to test with
CREATE DATABASE TestingPermissions;
GO

--Create a login to test with
CREATE LOGIN ReadOnlyUser WITH PASSWORD = 'SuperDuperSecure!';
GO

--And create a corresponding user
USE TestingPermissions
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;

--And a couple tables
CREATE TABLE dbo.PublicData (
    PublicId int,
    PublicText varchar(100)
    );

CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );

--And some data
INSERT INTO dbo.PublicData (PublicId, PublicText)
SELECT object_id, name
FROM sys.objects;

INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT object_id, column_id, name
FROM sys.columns;

Now, rather than adding the user to the db_datareader fixed role, we'll grant data explicitly to the table(s) with data they can access directly:
-- And only grant permissions to the one public table!
GRANT SELECT ON dbo.PublicData TO ReadOnlyUser;

And when we query that data as the login we created. We'll get results when we query the public table that we granted SELECT on, and we'll get an error for the private table we have no permissions on.
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
--This will error
    -- Msg 229, Level 14, State 5, Line 43
    -- The SELECT permission was denied on the object 'PrivateData', database 'TestingPermissions', schema 'dbo'.
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Now, we'll create a stored procedure which does that second query within the procedure, and we'll grant EXECUTE on that procedure to the limited access login.
--We create a stored procedure & Grant execute to the read-only user
CREATE OR ALTER PROCEDURE dbo.GetMyData
AS
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyData TO ReadOnlyUser;
GO

Now, ReadOnlyUser has EXECUTE permission on the procedure that access dbo.PrivateData, but doesn't have access to query dbo.PrivateData directly.
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyData;
--This will error with the same error as before
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Cross-Database problems
Ownership chaining won't cross database boundaries, so in the above example, if dbo.PrivateData were in a separate database, that EXEC dbo.GetMyData would actually return a permissions error. Cross-database ownership chaining can be enabled, but brings with it some security risks, but we can still avoid it.
A more complicated example
Continuing where the last example left off, lets move dbo.PrivateData into a different database
--Put all the private data in one database
USE master
CREATE DATABASE RestrictedAccess;
GO
--And create a corresponding user
USE RestrictedAccess
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;

--And move dbo.PrivateData into this database
CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );
INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT PublicId, PrivateId, PrivateText
FROM TestingPermissions.dbo.PrivateData;

--Now that its moved, Drop the table in the other DB
DROP TABLE TestingPermissions.dbo.PrivateData;
--And drop the procedure that we broke
DROP PROCEDURE IF EXISTS dbo.getMyData;

And we can create a stored procedure that gives access to just that aggregated data, without accessing the raw data directly. We grant execute to this procedure. In real life, this might be a bit more complicated, and probably would accept some parameters to limit the data.
CREATE OR ALTER PROCEDURE dbo.GetPrivateData
AS
SELECT priv.PublicID,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetPrivateData TO ReadOnlyUser;
GO

And we can alter the original GetMyData procedure to call this GetPrivateData procedure, rather than grabbing the data directly. Now, inside GetPrivateData, I'm doing an INSERT..EXEC... to put the data into a temp table, and then join to the temp table.
USE TestingPermissions;
GO
CREATE OR ALTER PROCEDURE dbo.GetMyDataWithInsertExec
AS

CREATE TABLE #PrivateData (
    PublicID int,
    PrivateCount int
    );
INSERT INTO #PrivateData (PublicID, PrivateCount)
EXECUTE RestrictedAccess.dbo.GetPrivateData;

SELECT TOP 1
       pub.PublicText,
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN #PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyDataWithInsertExec TO ReadOnlyUser;
GO

And when we test it as our user, we'll see that the procedure gives access, and querying the data directly gives an error.
USE TestingPermissions;
GO
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyDataWithInsertExec;
--This will error with the same error as before
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Views work too.
Ownership chaining can work with views, too. In all the places where I used a procedure to "cover" direct data access, I could have used a view similarly. There are differences & trade-offs. In general, views give users more access to go after the data in new & interesting ways, where stored procedures give you more control. This also means that stored procedures can give more protection to ensure data is only accessed in "approved" ways. Depending on what your controls are, and how you need to balance those things, you might find that the right thing for you could be a view, or a stored procedure.
Here's an alternate example using a view:
--Create a view to get Private data
USE RestrictedAccess
GO
CREATE OR ALTER VIEW dbo.SummaryPrivateData
AS
SELECT priv.PublicID,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT SELECT ON dbo.SummaryPrivateData TO ReadOnlyUser;
GO

--Then just query that view from the public database
USE TestingPermissions;
GO
SELECT TOP 1
       pub.PublicText,
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.SummaryPrivateData AS priv
    ON pub.PublicId = priv.PublicId

No cert signing necessary
It's just a matter of making your grants granular enough that you specifically grant access to the things people should have access to, and don't grant access directly if the user shouldn't have direct access. Stored procedures, views, and functions can be used as the abstraction layer, to give access indirectly to data.