Relation does not exist while scanning schema My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS SQL to PostgreSQL. So I have checked if there is any problem from following queries:

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'loan'
      AND 50 * seq_scan > idx_scan -- more then 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

And I got the result:

ERROR:  relation "mv_transaction_view" does not exist
SQL state: 42P01

What is the solution for this? I have a materialized view named mv_transaction_view.

Use relid instead of relname:

SELECT relname                                 AS tablename
     , to_char(seq_scan  , '999,999,999,999')  AS total_seqscan
     , to_char(idx_scan  , '999,999,999,999')  AS total_indexscan
     , to_char(n_live_tup, '999,999,999,999')  AS tablerows
     , pg_size_pretty(pg_relation_size(relid)) AS tablesize
FROM   pg_stat_all_tables
WHERE  schemaname = 'loan'
AND    50 * seq_scan > COALESCE(idx_scan, 0) -- more then 2%
AND    n_live_tup > 10000
AND    pg_relation_size(relid) > 5000000;

Why?

Assuming you do not have the schema loan in your search_path, Postgres is unable to resolve the cast mv_transaction_view::regclass - looking up unqualified tablenames only in the schemas listed in the current search_path. Hence the error:

 ERROR: relation "mv_transaction_view" does not exist

Materialized views are also listed among "tables" in the view pg_stat_all_tables, that's not the problem.

The missing schema in the search_path is also not the problem. Just the messenger in a manner of speaking. The problem is a bug in your query. Imagine a table loan.tbl and a search_path of public, loan. Your query would just work fine. Or would it?
Now imagine an innocent (or malevolent) user creating a table public.tbl. Then you run your query again ...

Using relid instead is the safe solution. You don't even need to cast since that's the OID of a table, exactly what pg_relation_size() expects. (The cast oid -> regclass is implicit.)

Details:

How to check if a table exists in a given schema
How does the search_path influence identifier resolution and the “current schema”

Also note COALESCE(idx_scan, 0):
idx_scan can be NULL, and you wouldn't wanna miss tables with all sequential scans.

The manual on pg_stat_all_tables.


Lookup data while in mysql cli edit mode (external editor) (\e) I'm using the mysql cli in edit mode (\e).

> Select * from \e

Goes to vim and I finish typing my command.

Select * from test_table;

Only to realize I've forgotten to use the database I need.  Is there a way I can preserve this script in edit mode, switch back to the regular mysql mode, run my command, and then come back to edit?

Another example would be: I forget how a column name is spelled and I need to look it up after I've started typing a command in edit mode.

Thanks!

Well, sort of.

In vim, save your work to a different file...

:w myfile.sql [enter]

Then exit vim with an artificial error so the mysql client won't bother to re-read the temporary file.

:cq [enter]

Then clear the input buffer in mysql.

\c [enter]

Then do what you've gotta do.

Then re-launch vim

\e [enter]

The editor will have your last command in it.  Clear that out and then paste the temporary file back into the buffer.

:r myfile.sql [enter]

Keep editing, then save and exit and your new content will be returned to mysql for execution.


Oracle Backup and Recovery Issue I have backed up database located on Host1 and restored/recovered it on Host2.
After a while, I backed up DB on Host1 again.

Now I want DBs on Host1 and Host2 be the same using last backup.

How can I achieve this? Without deleting database on Host2?

You should be able to just do a restore/recover over top of the existing Host2.  If you are trying to avoid a complete recovery, then I suspect the answer is you can't, but there are two notable exceptions.

If you never opened the database on Host2 and you have all the archive logs between the first backup and the second, then you can recover Host2 using them.
If you have opened Host2, you still might be able to do this if you have flashback database enabled and can flash back to the point before you opened it.


How I can flatten a count table in postgresql that contains only 2 columns with crosstab? In my database I have the following table:
Mytable
id SERIAL,
category VARCHAR,
value VARCHAR

And I execute the following query:
select category, COUNT(*) from mytable group by category

What I want is to generate a single row value containing the following:
category1 | category2 |  category3
1234      | 3456      |  12345

The table return the following results:
 category   | Value
`category1` | 1234
`category2` | 3456
`category3` | 12345

Do you have any idea how to do this?
I have looked the crosstab function but required an extra column named row_name that in my cases does not exist. Also using a second query seems like a waste to me.
The

Use conditional aggregation:
select count(*) filter (where category = 'category1') as category1,
       count(*) filter (where category = 'category2') as category2,
       count(*) filter (where category = 'category3') as category3
from the_table;


Service Broker "Broker Enabled" option goes from true to false when performing backup/restore of DB We have two production servers joined by an AAG, and for a specific database, the Service Broker is enabled (Options -> Service Broker -> Broker Enabled : true. We use this for SignalR.
However, every time I perform a backup and then restore the database on a test environment, I have to manually enable the broker again (ALTER DATABASE DatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE), as it's disabled when the database has been restored on the test server.
Can any option be set when performing either the BACKUP or RESTORE command that retains this database option? I use a script for generating the BACKUP/RESTORE statements, but I don't understand why a specific database option goes from true to false just by performing a BACKUP/RESTORE. Here are examples of the BACKUP and RESTORE commands I generate:
BACKUP DATABASE [DatabaseName] TO DISK = 'B:\backups\full_DatabaseName_20200902_1.bak', DISK = 'B:\backups\full_DatabaseName_20200902_2.bak', DISK = 'B:\backups\full_DatabaseName_20200902_3.bak', DISK = 'B:\backups\full_DatabaseName_20200902_4.bak' WITH COPY_ONLY, NOFORMAT, CHECKSUM, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, BLOCKSIZE=65536

RESTORE DATABASE [DatabaseName] FROM DISK = 'B:\backups\full_DatabaseName_20200902_1.bak', DISK = 'B:\backups\full_DatabaseName_20200902_2.bak', DISK = 'B:\backups\full_DatabaseName_20200902_3.bak', DISK = 'B:\backups\full_DatabaseName_20200902_4.bak' WITH FILE=1,MOVE N'DatabaseName_data' TO N'E:\Data\DatabaseName.mdf',MOVE N'DatabaseName_log' TO N'F:\Logs\DatabaseName.ldf',MOVE N'DatabaseName_index' TO N'E:\Data\DatabaseName.ndf',MOVE N'DatabaseName_data_io' TO N'f:\Data\DatabaseName_io.ndf', NOUNLOAD, STATS = 10, RECOVERY, REPLACE

Can any option be set when performing either the BACKUP or RESTORE command that retains this database option?

Edit: you can restore the database with ENABLE_BROKER. Check the sample below:
RESTORE DATABASE [WideWorldImportersA]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters.mdf'
,  MOVE N'WWI_UserData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters_UserData.ndf'
,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters_InMemory_Data_1'
,  MOVE N'WWI_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters.ldf'
,  STATS = 5, ENABLE_BROKER
GO


PGBouncer Pause Waits for Remote Connection to Close I'm testing the pgbouncer "pause" feature to minimize database downtime when a database restart is required.

For this test, I have a remote psql session connected to my database through pgbouncer.  Pgbouncer SHOW CLIENTS shows my remote session as Active even with no queries running.  The same client shows Idle according to pg_stat_activity.  When I issue the PAUSE command, it hangs until I close my remote session.  Shouldn't PAUSE work without closing my connection?

At least with pool_mode=session, it looks like a 'PAUSE' command will succeed only if the other client(s) have not yet issued any queries in their sessions. If the other client(s) have already issued any queries, the 'PAUSE;' command hangs as you describe.

This basically makes sense from PgBouncer's point of view, as it doesn't need to give a real connection to a client before the client issues any queries, but once it does a 'PAUSE' must wait for that client to finish its session.


How can I join one table or other depending on a field value? I have one table and I'd like to decide which table join depending on the value of a field.
I have the table T and there is a field Type, so if Type=1 then I have to join with Table2 and if Type=2 I have to join with Ta

Short answer, you can't. Maybe you can do something like:

select ...
from t
left join t1
    on t.type = 1
    and t.x = t1.x
left join t2
    on t.type = 2
    and t.x = t1.x

to return something (must be same type) from either t1 or t2 you can use:

select coalesce(t1.y, t2.y) ...


Error calling stored procedure from trigger in Oracle Database 11g I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated.  I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to know what I'm doing wrong.

I've created the following Java source:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED TryJavaHelperSource AS
import java.io.*;
public class TryJavaHelper {
    public static void Run(String arg1, String arg2) throws Exception
    {
        // …
        return;
    }
}
/

And I created a stored procedure that calls this Java source:

CREATE OR REPLACE PROCEDURE TryJavaHelperRun (arg1 IN VARCHAR2, arg2 IN VARCHAR2)
AS LANGUAGE JAVA NAME 'TryJavaHelper.Run (java.lang.String, java.lang.String)';
/

I'm trying to create a trigger on this table:

create table TryTable (
    arg1 varchar2(20),
    arg2 varchar2(100)
);

I get an compile error when I attempt to create the following trigger:

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    CALL TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Here's the error I'm getting:

SQL> sho err
Errors for TRIGGER TRYTABLE_BEF_UPD_ROW:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/6      PLS-00103: Encountered the symbol "TRYJAVAHELPERRUN" when
         expecting one of the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "TRYJAVAHELPERRUN" to
         continue.

Why is this error occurring, and how do I fix it?

UPDATE:

It looks like it works when I take out the CALL statement.  What are the rules governing when to use CALL and when to just type the name of the function/procedure?

Your trigger doesn't need the CALL keyword.

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Generally, you should never use CALL in a PL/SQL block-- just execute the procedure.  I assume that CALL is some ancient leftover syntactic remnant from some ancient version of Oracle.  It allows you to replace the entire PL/SQL block that is the trigger body with a single CALL statement, i.e.

create or replace trigger TRYTABLE_BEF_UPD_ROW
  before update or insert on TryTable
  for each row
  call TryJavaHelperRun(:new.arg1, :new.arg2)
/

Personally, I've never come across a case where there was any benefit to ditching the "normal" PL/SQL syntax in favor of using CALL in a trigger.

This assumes that you can call the TryJavaHelperRun procedure successfully in general.


Save SQL server job output results to text file I would like to save results of the SQL job to a text file.

This is the query

SELECT sqltext.text,
req.session_id,
req.blocking_session_id,
req.cpu_time,
req.wait_type,
req.status
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Currently when I choose as below, it only written

  Job 'test2' : Step 1, 'check' : Began Executing 2019-07-31 23:04:17

text'

I need the query results saved in text file. Is there any way?

The job might not have completed . The output file will start with what you have received followed by the results.


PostgreSQL to SQL Server Migration Error Caused by Default Value Expression I am working on integrating databases from both MySQL and Postgres into SQL Server. My data migration from Postgres to SQL Server is failing because of the default value expression in Postgres.

This is the default value expression: date_part('epoch'::text, now())

Does anyone know what the SQL Server equivalent is? I managed to successfully migrate the data using using date_part('epoch'::text, now())=1 though I do not know if this is the real equivalent?

Can someone please tell me if this is in fact the SQL Server equivalent?

For "normal" (day, week, month, year, ...) in T-SQL you'd use the DATEPART function. (Check DATEPART (Transact-SQL)). However, this function doesn't deal with 'epoch'.

The definition of date_part('epoch'::text, now()) in PostgreSQL is: `time elapsed since the "epoch time" (1 Jan 1970), measured in seconds. In T-SQL, you would compute it with:

DATEDIFF(second,'19700101 00:00:00:000', getutcdate())


Instant File Initialization on Shared drive I asked something similar to this before, but previously I had asked about moving backups to a shared location. This time I am curious: if I want to restore a database on a shared drive, do I need to enable IFI on that server or just the server that is running SQL Server?

The reason I ask is I am restoring a pretty large database and its been stuck at 100% restored for the last couple hours. The wait type in sp_whoisactive is:

(28472716ms) `PREEMPTIVE_OS_WRITEFILEGATHER.

The only time I've seen that is when IFI is not on, but I do have it enabled on the SQL Server, but it is not enabled on the shared drive server.

Community Wiki answer generated from comments on the question, from Bob Klimes, Sean Gallardy, and Aaron Bertrand.

You'll need to enable Instant File Initialization (IFI) on the server holding the disk. Restart SQL Server for it to take effect.

You can test if IFI is working on the share by creating a database on the share with trace flags 3004 and 3605 turned on. Output is written to the SQL Server error log.

  If IFI is working, there will not be any entries for zeroing the mdf file.

IFI does appear to work with network shares, but for me the creation of a test database took 45 seconds on the share and less than 1 second locally. Even with IFI enabled, you might still have performance problems.


MySQL: initialize the Data Directory (--data-dir) for Operating System user other than mysql I have installed mysql 5.7.19 using rpm package in RHEL, my requirement is to initialize the data directory using mysqld, the MySQL server with using some other user 'SAM' rather than mysql user. i have created my.cnf file and created a symbolic link ln -s etc/my.cnf .my.cnf, Following are the contents of my.cnf
    [client]
    port=3307
    socket=/tmp/mysql.sock
    host=localhost
    default-character-set=utf8

[mysqld]
port=3307
socket=/tmp/mysql.sock
datadir=/home/data/db
log-error=/var/opt/mysql/mysql-error.log
sql-mode=""
default-storage-engine=MyISAM
long_query_time=5
max_allowed_packet=16M
# Accept 4.0.x style passwords
# old-passwords=ON
character_set_server=utf8

[mysqldump]
quick

I'm getting following logs in file(mysql-error.log) while running command and mysql server is not getting up mysqld --defaults-file=/home/etc/my.cnf --basedir=/usr/sbin/.. --pid-file=/var/opt/mysql/mysql.pid

18-06-06T05:02:31.933483Z 0 [Note] InnoDB: Foreign key constraint system tables created
2018-06-06T05:02:31.933518Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-06-06T05:02:31.935391Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2018-06-06T05:02:31.935417Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2018-06-06T05:02:31.936921Z 0 [Note] InnoDB: sys_virtual table created
2018-06-06T05:02:31.937905Z 0 [Note] InnoDB: 5.7.19 started; log sequence number 0
2018-06-06T05:02:31.938825Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2018-06-06T05:02:31.938932Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-06-06T05:02:31.939424Z 0 [Note] Salting uuid generator variables, current_pid: 12598, server_start_time: 1528261351, bytes_sent: 0,
2018-06-06T05:02:31.939459Z 0 [Note] Generated uuid: 'd1916718-6946-11e8-9f90-000c29429204', server_start_time: 3546021758129118237, bytes_sent: 50645584
2018-06-06T05:02:31.939475Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d1916718-6946-11e8-9f90-000c29429204.
2018-06-06T05:02:31.941093Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-06T05:02:31.941184Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-06-06T05:02:31.941191Z 0 [Note] Server hostname (bind-address): '*'; port: 3307
2018-06-06T05:02:31.941207Z 0 [Note] IPv6 is available.
2018-06-06T05:02:31.941211Z 0 [Note]   - '::' resolves to '::';
2018-06-06T05:02:31.941258Z 0 [Note] Server socket created on IP: '::'.
2018-06-06T05:02:31.942764Z 0 [Warning] Failed to open optimizer cost constant tables

2018-06-06T05:02:31.942851Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-06-06T05:02:31.942892Z 0 [ERROR] Aborting

can someone please answer is it possible to initialize of mysql server using other users rather than mysql

Note:  This is a fresh install, not an upgrade.  (This fact copied from a now-deleted Answer.)

From the website here, you can just modify this line

shell> bin/mysqld --initialize --user=mysql

and change it to:

shell> bin/mysqld --initialize --user=other_username

This user (who is NOT a user in the database server sense, but rather in the Operating System (OS) sense) only determines permissions on the underlying file system - i.e. who owns the data-dir for example, it has nothing to do with MySQL database users. For clarity, I would use different names for OS users and MySQL users.

With compilation of the source, you get even more fine-grained control over your system, but if all you want is to change the owner of the files on the underlying OS file system, then do it this way!

You can use --initialize-insecure and then login as root and change the password straight away, logging in as root with no password:

shell >mysql --user=root

and then

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
mysql> flush privileges;

Logout and then login with

shell >mysql -u root -p

After -p type nothing else and press Return. You should then be prompted for the password (MyNewPass) that you have just entered.


Granting specific permissions on ALL tables I have a tutorial that users need to type in PostgreSQL commands. I don't give the script to users simply because it's a learning experience. Everything in the script below is typed in except for the grants for table data, which is imported.

There should be a way to grant the permissions needed without having to include all the table names. If I can cut all that out I can have the users actually type the grant commands in instead id inserting from a script.

Any help would be greatly appreciated...

create database archive;

create database snort;

create user snort with password 'l0gg3r';

create user base with password 'an@l1st';

\connect archive;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;

\connect snort;

\i d:/winids/barnyard2/schemas/create_postgresql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql.sql;

\i d:/winids/inetpub/wwwroot/base/sql/create_base_tbls_pgsql_extra.sql;

GRANT INSERT, SELECT, UPDATE ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr TO snort;

GRANT ALL ON TABLE data, detail, encoding, event, icmphdr, iphdr, opt, reference, reference_ref_id_seq, reference_system, reference_system_ref_system_id_seq, schema, sensor, sensor_sid_seq, sig_class, sig_class_sig_class_id_seq, sig_reference, signature, signature_sig_id_seq, tcphdr, udphdr, acid_ag, acid_ag_alert, acid_ip_cache, acid_event, base_roles, base_users TO base;

If you're not specifying the table's schema, and you're not, then you can use ALL TABLES IN SCHEMA and rely on the default schema being PUBLIC or in the event you've changed your search_path just write the schema name below. From the docs on GRANT

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

So that looks like

GRANT INSERT, SELECT, UPDATE
  ON ALL TABLES IN SCHEMA public
  TO snort;
GRANT ALL
  ON ALL TABLES IN SCHEMA public
  TO base;


Ignore the joins without any result in SQL Server Situation
I'm making a SQL server query to filter data from the projects table (tblProjecten). This table got a lot of many to many relationships to other tables like region (tblProjectenRegio), organisation (tblProjectenOrganisatie), etc.
All the parameters I declare are optional. I've found to use coalesce to do this. It works fine if I don't use the joins inside my query here you got my query I've use first:
declare @themaid int             = 1        ; -- themeID
declare @trefwoord nvarchar(max) = ''       ; -- search query

select distinct p.*
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
      p.naam like '%' + @trefwoord + '%'                                    ;

Below my results for the different declarations (see also data below):

@themaid
@trefwoord
results
correct

1

166
OK

1
airco
166
OK

null
airco
166
OK

null

166, 185 , 415
OK

This works fine but If I add other conditional parameters like query below. I got totally other results.
declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select distinct p.*
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
                    left join tblProjectenStudiegebieden ps        on p.projectId = ps.projectid
                    left join tblProjectenOpleidingsType pot       on p.projectId = pot.projectID
                    left join tblProjectendoelgroep pd             on p.projectId = pd.projectId
                    left join tblProjectenOrganisator po           on p.projectId = po.projectId
                    left join tblProjectenRegio pr                 on p.projectId = pr.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
      ps.studiegebiedid = coalesce(@studiegebiedid, ps.studiegebiedid)   and
      pot.opleidingsID  = coalesce(@opleidingdtypeid, pot.opleidingsID)  and
      pd.doelgroepId    = coalesce(@doelgroepid, pd.doelgroepid)         and
      po.organisatorId  = coalesce(@organisatorid, po.organisatorId)     and
      pr.regioId        = coalesce(@regioid, pr.regioId)                 and
      p.naam like '%' + @trefwoord + '%'                                    ;

Here are the results (the other declarations are null):

@themaid
@trefwoord
results
correct
must be

1

NOT OK
166

1
airco

NOT OK
166

null
airco

NOT OK
166

null

NOT OK
166, 185, 415

This comes because the other tables haven't any data inside it. (see data below)

Question
My question is now can I ignore the joins without any result to make the last query working?
I've also tries to use inner and right joins but give the same results.

Data
Here you got some data:
tblProjecten:

projectId
naam

166
Attestering AIRCO PROJECT

185
Autoweb E-LEARNING

415
Bouw en Hout

tblProjectenThema:

themaId
projectId

1
166

2
166

2
415

3
415

6
185

tblProjectendoelgroep:

doelgroepId
projectId

I've been reading and re-reading the original question.  What I have come to is this:

Return records from tblProjecten where matches exist for parameters that have been supplied (are not null).  Where the parameters are not supplied (or null) then assume all matches for that child table

Effectively you're making your joins dynamic based on whether a parameter has a supplied value or not.

For that you can use:

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select p.*
from tblProjecten p
where p.naam like '%' + @trefwoord + '%'
and (
        @themaid is null
    or exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenOpleidingsType pot where p.projectId = pot.projectID and ps.studiegebiedid = @studiegebiedid
)
and (
        @opleidingdtypeid is null
    or exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid
)
and (
        @organisatorid is null
    or exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid
)
and (
        @regioid is null
    or exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid
)

I have changed things around a little.  Instead of doing joins we're doing an EXISTS with a correlated subquery.

Each one evaluates as:

If @parameter is null return true
Or... If @parameter is not null then check if there exists a matching record on the child table for that @parameter. If there is, return true

By doing this our joins effectively become optional (depending on whether we have a parameter value or not).

If I have your original intent correct, then an easy assumption is that you might want to use inner joins e.g.:

select distinct p.*
from tblProjecten p
inner join tblProjectenThema pt on p.projectId = pt.projectId
...etc...
where p.naam like '%' + @trefwoord + '%'
and (pt.themaId = coalesce(@themaid))

The reason you cannot do this is that tblProjectenThema could be empty and therefore no rows can ever be matched.  You would always get an empty result.

Alternative approach

The query above will start to suffer from performance as more tables need to be considered.  You could use OPTION(RECOMPILE) against the query.  Because you want optional, dynamic, parameters this can also be served by using dynamic SQL.

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select @trefwoord = '%' + @trefwoord + '%';
declare @sql nvarchar(max);

select @sql = '
select p.*
from tblProjecten p
where p.naam like @trefwoord
'

if @themaid is not null
select @sql += 'and exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)'

if @studiegebiedid is not null
select @sql += 'and exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid)'

if @opleidingdtypeid is not null
select @sql += 'and exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid)'

if @organisatorid is not null
select @sql += 'and exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid)'

if @regioid is not null
select @sql = +='and exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid)'

exec sp_executesql
    @stmt = @sql
    ,@params = N'@trefwoord nvarchar(max), @themaid int, @studiegebiedid int, @opleidingdtypeid int, @doelgroepid int, @organisatorid int, @regioid int'
    ,@trefwoord = @trefwoord
    ,@themaid = @themaid
    ,@studiegebiedid = @studiegebiedid
    ,@opleidingdtypeid = @opleidingdtypeid
    ,@doelgroepid = @doelgroepid
    ,@organisatorid = @organisatorid
    ,@regioid = @regioid
    ;


Security for Oracle linked server from SQL Server I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data from linked server.

So I tried mapping SQL Security User to Oracle user/pass by inserting it in "Local server login to remote server login mappings:" area then "For a login not defined in the list above, connection will:" set to "Not be made".

When I click OK, I get:

Access to the remote server is denied because no login-mapping exists

Is it not possible to map SQL login to a Oracle login? Is there any way to get this to work?

If the user that you added to use the Linked Server is not for the login you are currently using (and it is the only one you've set up), when you click OK, the connection test will fail because the current user will have no valid mapping to use the Linked Server.

To fix this simply create a valid mapping for your login as well. That way when you click OK the test will succeed because there is a valid mapping for the current user - if you use the same settings for the Oracle target in both mappings you'll have some confidence that it will be working for the other user(s).   Either that or ignore the error from the test and check specifically that the user you created the mapping for can use the linked server successfully.


What types of locks are held on global temporary tables? I am wondering if:

the isolation level is Read Committed
data is inserted or deleted or read

are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table as the traditional row store table.

For temporary table I believe there will be no blocking as it "lives" during the current context, so I am not able to read and insert data at the same time.

But for global temporary table, let's say I have many operations - some are inserting new data, some are deleting data, and some wants to read data is going to be deleted.

Let's say the structure of the table is:

GroupID EntityID
1       101
1       102
1       103
2       101
2       104
3       100

Are the same rules applied here?

I was curious for this question and wanted to try myself and see whether tempdb is a special database or just like another user database wherein default isolation "Read Committed" is applied or not.

Below are queries that I executed:

--Created two tables in tempdb, one named table and second one as gobal tempdb table

select * into tempdb.dbo.msgs from sys.messages

select * into ##msgs from sys.messages

Executed Update query for few records of these tables in next window

begin tran
go
update dbo.msgs set language_id = 1055 where message_id = 11242
go
update ##msgs set language_id = 1055 where message_id = 11242
go

Both the above statement updated 22 rows as expected.

Later on I ran select statement on these two tables in two different window and so far both are stuck:

One thing I noted that in the first query no record is coming at all on screen whereas in the second case, 8830 records are on screen and stuck afterwards( Not sure why?) however in both the case, query is stuck and running forever as they are waiting for update to complete. This is what was expected in the default behavior of MSSQL database.

I validated same using sp_whoisactive and could see there is database blocking as below:

Lock information for each of them is provided for details.

1st:

<Database name="tempdb">
  <Objects>
    <Object name="msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

2nd:

<Database name="tempdb">
  <Objects>
    <Object name="##msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" />
        <Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" />
      </Locks>
    </Object>
    <Object name="msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="UIX" request_status="GRANT" request_count="22" />
        <Lock resource_type="RID" page_type="*" request_mode="X" request_status="GRANT" request_count="22" />
      </Locks>
    </Object>
  </Objects>
</Database>

3rd:

<Database name="tempdb">
  <Objects>
    <Object name="##msgs" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Also cross-checked isolation level for each of these transaction using below command:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id in(63,66,67)

Result:

With all these, it could be stated that for tempdb also, isolation level is set as "Read Committed" and rules apply similar to other databases.

I have run these queries on MSSQL 2019 version.


INSERT... SELECT isolation? I'm not sure isolation is the right term, but I think it is. Let me know if not.

I'm doing an insert...select statement, from a table with keys into a lock table. I have some WHERE conditions by which I select the row I want.

From what I understand, first a select happens, then the result is used for an insert. The question is, is there a chance that while the insert is happening, my WHERE conditions no longer apply to the row I insert? ie, can another query change the rows I SELECT...INSERTed, or are they locked between the two queries?

No, MySQL builds a temporary result set from your query before performing the insert.

  When selecting from and inserting into a table at the same time, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.

Source: MySQL Documentation

Edit based on Comments

The insert is an atomic operation and so the data used to build the temporary result set will match the committed data in the source table at the precise moment that the query was executed (i.e. it is a snapshot of that point in time).

After the temporary result set has been built any modifications to the source table will not be reflected in the results being inserted into the target table - unless you run the query again.


Managing a checklist We want to track progress of some projects, and I thought I'd make a mysql table of the checklist items that we'll be tracking completion dates and correlated data for.  This would be an ordered list, but being likely to be updated periodically, inserting a value in the middle is likely, and would be a nuisance.  I was wondering if there's a way of doing an insert along the lines of alter table, i.e. "insert into table set blah=blah after id=4", where id is an auto_increment and everthing 4+ would get renumbered.  Perhaps something like this post on renumbering: https://stackoverflow.com/questions/2643371/how-to-renumber-primary-index

I guess you could do this variation on that post:

SET @i=5
UPDATE table_name SET id=(@i:=@i+1) where id >= 4;
INSERT into table_name SET id=4, blah='blah'

That doesn't seem too complicated, though doing it this way you probably wouldn't want it to be auto_increment (or at least it doesn't buy you much beyond the initial setup).  I'm open to a better idea though...

Don't try that approach.  Instead, create another column for the 'sequence number' of the actions.  Or simply use a DATETIME for ordering.  (Note, that has a resolution of 1 second; can two things happen in a single second?  If so, there are workarounds.)


Round-robin T-SQL problem with a twist I have a table where I need to populate values, which are incremented using given step size.  In addition I also have a notion of range, so that certain number of steps will fit inside the range (not necessarily evenly).  Once the end of range is reached, a round-robin process needs to happen.  The twist is that I also need to count how many times I did the round-robin and, once the given max of round-robins is reached, I need to sort of reset the count of my round-robins.  Since this is confusing, here is an illustration.

Let's create and populate a simple table:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE
tempdb.dbo.#mytable

CREATE TABLE #mytable (
id INT IDENTITY(1,1),
iteration INT,
step INT
)

INSERT #mytable
DEFAULT VALUES
GO 20

Now let's declare some variables and use them to update data in our table (note that the @max_iterations variable is not used, because I don't know how to leverage it and why I am asking my question):

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET     iteration = (id*@step_size)/@max_iteration_range,
        step = (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable

Here is the resulting output:

    id          iteration   step
    ----------- ----------- -----------
    1           0           7
    2           0           14
    3           0           21
    4           0           28
    5           0           35
    6           1           2
    7           1           9
    8           1           16
    9           1           23
    10          1           30
    11          1           37
    12          2           4
    13          2           11
    14          2           18
    15          2           25
    16          2           32
    17          2           39
    18          3           6
    19          3           13
    20          3           20

The problem I am trying to solve is reflected in the last three rows.  Since my @max_iterations variable is set to 2, once I exceed this value in the "iteration" column, I want to go back to 0 and start over.  The last three rows should be:

    id          iteration   step
    ----------- ----------- -----------
    18          0           6
    19          0           13
    20          0           20

I appreciate any suggestions on how to accomplish this.

Thank you!

P.S.  This problem stemmed from the need to generate SQL Agent job schedules, which are evenly spaced out, but have to run with frequency no less than once every X hrs/min, etc.  So I can't just continue incrementing the start time.

Once again, thank you to Vladimir for providing the formula!
The correct way to accomplish what I need is this:

DECLARE @step_size              INT = 7,
        @max_iteration_range    INT = 40,
        @max_iterations         INT = 2

UPDATE  #mytable
SET
        iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1),
        step =       (id*@step_size)%@max_iteration_range

SELECT * FROM #mytable


SQL Server database level roles for creating tables Is there a way to create or change a special role for creating tables? Our software developer team has db_datareader and db_datawriter roles but they can not create new tables.

I read this article related to database level roles. I don't want to grant db_ddladmin role because there is too much permissions in this role.

So my question is how can i give creating and deleting tables permissions for a database? Views, procedures and functions are okay too. All I need is I don't want to grant permissions related to security, login or backup operations.

If you want to create a role with specific rights, you could do this:

CREATE ROLE CreateObjects
GRANT CREATE TABLE TO CreateObjects
GRANT CREATE VIEW TO CreateObjects
GRANT CREATE FUNCTION TO CreateObjects
GRANT CREATE PROCEDURE TO CreateObjects
GRANT ALTER ANY SCHEMA TO CreateObjects

Create the test user:

CREATE LOGIN testlogin with password = 'StrongP@SSWORD123'

CREATE USER Testlogin FOR LOGIN Testlogin

Add the user to the role:

ALTER ROLE CreateObjects ADD MEMBER Testlogin

Test the user's permissions:

EXECUTE AS LOGIN = 'testlogin'

SELECT SUSER_NAME(),USER_NAME()

(No column name)    (No column name)
testlogin   Testlogin

CREATE TABLE dbo.test(id int)

DROP TABLE dbo.test
REVERT

Result

Commands completed successfully.

Or like Tom stated, add the user to the db_ddladmin role.

ALTER ROLE db_ddladmin ADD MEMBER testlogin


Maintenance Plan Using Powershell At the moment I have around 125 production instances, each with a script-based maintenance plan running as an agent job.

The tasks run are Index Reorg/Rebuild, Stats updates and Checkdb. Backups are looked after by Netbackup so they dont form part of the plans but for a couple of exceptions.

I moved all the instances last year to script-based maintenance plans from plans created with the SSMS wizard (hate those) and they're efficient and effective so overall I'm pleased.

I'm wondering whether it's feasible to take things a little further. I've recently been working on a powershell script that, when pointed at an instance, iterates through the databases on that instance and performs those three tasks on demand.

My question is whether anyone can see any downside by doing this for all instances, I.e. Having a single powershell script on our DBA server that iterates through a list of instances on a windows schedule and executes the maintenance tasks. Any errors would be handled / written out to logs etc.

The main benefit of this in my eyes that we won't be deploying mp jobs to new instances and configuring schedules. We will just be adding the name of any new instance to the instances the script must iterate through.

I'd welcome your thoughts.

I've recently been working on a powershell script that, when pointed
  at an instance, iterates through the databases on that instance and
  performs those three tasks on demand.

  My question is whether anyone can see any downside by doing this for
  all instances, I.e. Having a single powershell script on our DBA
  server that iterates through a list of instances on a windows schedule
  and executes the maintenance tasks. Any errors would be handled /
  written out to logs etc.

Your problems will be based on how you wrote your process. So just a few things I can think of right off to consider. Although any answer is going to an opinion of the writer.

Does it do parallel processing or sequential?

If you are using sequential processing you will never know when one particular server is going to have a load from the index maintenance or stats maintenance. As well, it could also vary each time it runs based on other processes running on a given instance. Something to consider is if your maintenance window is large enough to handle that fluctuation.

When you run it from one server are you using something like Invoke-Sqlcmd to run it or Invoke-Command (PowerShell remote).

This is considering when you run a command in PowerShell will you process any amount of data in PowerShell or do it all in SQL Server. If you do any kind of piping with the data consider how it will be processed. Certain things (piping to an executable) can require all the data to be cached in memory before it moves down the pipe to the next command.


Changed MyISAM to InnoDB, huge performance loss So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss.

Here is my.cnf:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instance=2
innodb_log_file_size=512M
key_buffer_size=24M
#70% of available RAM
innodb_buffer_pool_size=3072M
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000

I'm running a Amazon CentOS t2.medium instance (2 vCPU & 4 GB ram).

Here are a few tests I ran. I ran alot more but with the same theme. InnoDB takes way longer and loads the CPU more.

Anyone have any thoughts? I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

You said in your question

  I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

Your server class is too small for your InnoDB settings.

On Apr 14, 2011, I mentioned how the storage engines have distinct ways of caching.

MyISAM will only cache indexes in RAM. Data is always read from disk.
InnoDB caches data and indexes in the InnoDB Buffer Pool

I also noted how your innodb_read_io_threads and innodb_write_io_threads are maxed out. You only have 4GB of RAM or which you only have 1GB free (due to your 3GB or 3072M Buffer Pool). I would lower your threads from 64 to 8.

If you have the budget, you should go upgrade your server class to m3.large or m3.xlarge where you can have 4 CPU and 15 or 16G of RAM. You could then increase innodb_buffer_pool_size to 10G or 12G. Then, your remaining InnoDB settings would make sense. I would still lower your threads from 64 to 8.


Seeing AWS PostgreSQL-on-Aurora snapshot sizes of 0 GB - is this normal for a db of ~800mb? While working on some code to manage our manual snapshots, I noticed that a number of recent AWS PostgreSQL-on-Aurora snapshots show a storage size of 0 GB (older ones are at 1 gb). I thought it might be rounding in the wrong direction (the database is ~800 meg so far), but I wasnt sure if that was the reason for the 0 size.

I restored one of the snapshots with the intent of confirming that there's usable data in them. I am unable to connect to cluster restored from one of these snapshots despite using the same incoming IP and security group as the production cluster. In fact, I was unable to connect to it even after explicitly adding a SG that contained my IP. I've been using RDS (on "regular" postgres and MSSQL) for many years, so I doubt there is a "noob RDS" issue here - but I wouldnt discount a noob Aurora issue. Apparently I am missing some difference between Aurora (as PostgreSQL) and these other databases - or there's a cluster / instance / database problem that isn't yet revealing itself.

What am I missing?

AWS support responded and indicated that the misleading snapshot size data is a known issue with snapshots for clusters. They intend to fix it, but do not have an ETA on the fix. The connection issue cleared up without making any changes to credentials or other code.


How the indexes are managed? I am trying to understand how the indexes are managed for below common data types:
1- Numeric (Integer, Decimals)
2- String (Varchar, Char)
3- DateTime

I have few questions:

1- How the indexes are stored for different data types? For example if have Numeric data like (100, 101, 100, 900, 700) & string data like (AAA,BBB,CCC,AAA,ABC) how the numericcolumn & stringcolumn index will store this data on disk?

2- Is there any different between the retrieval of a Numeric & String indexes? For example if i try the SELECT statements on numericIndexed column & on sringIndexes columns how they are retrieved?

3- Is the indexes are stored different by SQL Server & Oracle? OR they use the same logic?
Regards.

For SQL Server, an index entry is stored pretty much the same way as a normal record except for the row header. Also, index and record entries are never on the same page

Now, anything I add to this would be copy/paste of articles by cleverer folk than me, so I'll refer you to Paul Randal: Inside the Storage Engine: Anatomy of a record

Also, all indexes in SQL Server are B-Tree. Tables with clustered indexes in SQL Server are the same as Oracle Index Organised tables

As for a SELECT, you have to consider if an index is "covering" or not usually. This means the entire SELECT can be satisfied from the index only. Otherwise it may not be used or generate key lookups. For more, see

SQL Server Index Basics
Using Covering Indexes to Improve Query Performance


Argument out of range error I have this query:

WITH
  I1 AS
  (
    SELECT
      *
    FROM
      XYZ A
    WHERE
      UPPER(A.DES) LIKE '%%' ESCAPE '\'  --'
      AND NVL(A.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= TO_DATE('2016-06-01', 'YYYY-MM-DD')
      AND NVL(A.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= TO_DATE('2016-06-08', 'YYYY-MM-DD')
      AND A.ID_OU IS NOT NULL
    ORDER BY
      IDFR
  ),
  I2 AS
  (
    SELECT
      I1.IDFR, I1.DES, I1.ID_OU,D1.VAL, T1.CODE
    FROM
      ABC D1, I1, DEF T1
    WHERE
      D1.IDFR = T1.IDFR
      AND D1.IDFR = I1.IDFR
  ),
  I3 AS
  (
    SELECT I2.* FROM I2
  ),
  I4 AS
  (
    SELECT
      *
    FROM
      I3
      PIVOT
      (
        MAX(VAL) AS VAL FOR CODE IN ('AB' AS ABC , 'XY' AS XY)
      )
    ORDER BY
      DES
  ),
  I5 AS
  (
    SELECT COUNT(*) TOTAL_NO_ROWS FROM I4
  ),
  I6 AS
  (
    SELECT I4.*, rownum row_number FROM I4
  )
SELECT
  I6.*,  I5.TOTAL_NO_ROWS
FROM
  I5, I6
WHERE
  row_number > 0 AND row_number < 3;

Sometimes it runs fine, but other times it throws this error:

  ORA-01428: argument '000000000000000000000000' is out of range

I implemented the same structure and data in another database (similar database configuration) and didn't face such error.

How to resolve this issue?

One possible reason:

Bug 18162779 : ORA-01428: ARGUMENT '000000000000000000' IS OUT OF RANGE

Try running the query after setting the below:

alter session set  "_disable_function_based_index" = TRUE;


pg_ctl: server did not promote in time I setup a two node postgresql 10 cluster to test replication. Following this guide

I was able to get replication running correctly. However, I want to test the process of the slave to master. When I stop the master node, and run pg_ctl promote -D on the slave, I get:

  pg_ctl: server did not promote in time

I've looked online and can't find anything related to being unable to promote in time. After running the command and receiving the error, I have to manually start postgresql and it is still running in a read only state.

Postgresql is running on Ubuntu 18.04.

To promote, pg_ctl writes a file called $PGDATA/promote, sends SIGUSR1, then waits a default of 60 seconds for the server to acknowledge that it is now read-write. Does that file exist? What is in the server log file?


I'm not sure why you care about this.  Normally you only care about the NEXT VALUE, as looking at the current_value is tricky with multiple sessions.

But you're essentially trying to determine if the sequence has been incremented.  So you can simply increment every sequence once after you create it.  EG

instead of

CREATE SEQUENCE SEQ_TEST START WITH 1

use

CREATE SEQUENCE SEQ_TEST START WITH 0
SELECT NEXT VALUE FOR SEQ_TEST


How to use NOTIFY with postgres to send previous record and new record Say we update a record in a database table in postgres. How can I set up NOTIFY, to send a json blob of the previous version of the record and the new version of the record? I have read about NOTIFY quite a few times and never figured out how to use it.

You can call it from a trigger and pass both OLD and NEW in the notify payload. But by default the payload accepts only a maximum of 8000 bytes total, so if your JSON BLOBs are bigger than 4000 bytes each you will need to insert at least the old value in a table and pass the key in the payload.
If you are attempting to build logical replication, you might be better off using those facilities in newer versions of Postgres. You should be planning to upgrade soon as you are now 4 major versions behind current.


can i secure sql server agent job with password I want to secure SQL Server Agent jobs with password or any other method is it possible?

One alternative method which might help you out is to save your stored procedures and UDFs that the job may use (if any) WITH ENCRYPTION.

However, given the nature of your question, I wonder whether you really need to start a bottom-up review of your database systems security first, liaising with business owners regarding what permissions currently exist, and what the desired position should look like.


Cannot login as new user on MariaDB When I create a new user in MariaDB, I cannot login as that user.

First I login to the database as root doing mariadb in a bash terminal.

I then run the following commands to create a database and a user:

CREATE DATABASE mydb;
grant all privileges on mydb.* TO 'myuser'@'localhost' identified by 'password';
FLUSH PRIVILEGES;

When I then run SELECT user,host,plugin FROM mysql.user;, it prints the following:

+-----------+-----------+-------------+
| user      | host      | plugin      |
+-----------+-----------+-------------+
| root      | localhost | unix_socket |
| myuser    | localhost |             |
+-----------+-----------+-------------+

However, when I then try to login as that user doing mariadb -u myuser mydb -p, I have no luck.

ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

I have no idea what is happening.

I am running the following MariaDB version:

mysqld  Ver 10.1.44-MariaDB-0ubuntu0.18.04.1 for debian-linux-gnu on x86_64 (Ubuntu 18.04)

Try granting to 'myuser'@'127.0.0.1', @'::1', or even @'%'.  In your logs, you will see 'Access denied for user' and what IP address you were trying to connect from.


Check if two datetimes are between two others Today I'm facing with problem, which I can't solve.

I need to check if two datetimes (2019-04-08 13:30:00 - 2019-04-08 15:30:00 ) are between or overlap two others (2019-04-08 14:00:00 - 2019-04-08 16:00).

Imagine that those datetimes are event's date. We can see that there is an event between 14:00 and 16:00 so we cant' have other between.

I've tried something like this:

SELECT * FROM Rezerwacja WHERE
(('2019-04-08 13:30:00.000' between Start and Stop) OR
('2019-04-08 15:30:00.000' between Start and Stop))

How to check if there is an "event" already ?

To check for overlaps, you only need 2 conditions, the start or interval A with the end of interval B, and vice versa:

WHERE
      '2019-04-08 13:30:00.000' < Stop
  AND Start < '2019-04-08 15:30:00.000'


restore compressed dump query in postgresql asks password I'm using ubuntu OS and I took backup using

sudo -u postgres pg_dump dbname | gzip > filename.gz

in the file path as...

/home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname | gzip > filename.gz

Later, I tried to restore it back using these following below...

 1. gunzip -c filename.gz | psql glialmine
 2. cat filename.gz | gunzip | psql
 3. sudo -u postgres  psql pg_restore -d dbname  filename.gz

all these 3 restore option I used asks password, but I have only one login and I tried it and its not accepting it I also tried root password and linux login password too but its not working.

For first two it asks password like below

Password:
psql: fe_sendauth: no password supplied

and for 3rd one it asks password and its error for my password like below

 Password for user pg_restore:
 psql: FATAL:  password authentication failed for user "pg_restore"

Find the postgresql installation folder containing pg_hba.conf. Edit the authentication chapter at the end of this file, adding

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

and restart. After that postgresql should not ask for the password to authenticate into any account if this is done from the same machine where the postgresql server is running.

Afterwards, change passwords to be sure you know them. Finally, remove the added lines from pg_hba.conf for more security. You can only bypass authentication because you already have root rights on that computer.


Installing SQL Server TDE on a Windows Cluster i'm going to be upgrading two Instances to Enterprise and thenI'm going to be installing TDE on two sides of a Windows Cluster. All of the databases are on one storage device and whichever Instance is active accesses the databases from that server.

After I install TDE and encrypt the databases my users will test. If successful, I'll fail over to the secondary and upgrade it to Enterprise and then install the certificate and key.

My question is, will there be a problem with that secondary Instance since it won't have Enterprise installed yet or the certificate and key?

I hope I've provided enough information and thank you all in advance for any help you can provide.

Thomas

Several versions of SQL Server do not let you upgrade a Standard CLUSTERED installation of SQL Server to Enterprise.  Are you sure you can do this in your environment?  To clarify: If you are already clustered, upgrading to enterprise is not supported in many scenarios.  Secondly, TDE is a enterprise edition option.  You're not going to get to failover and test it without being on enterprise, but maybe 2016 enabled it for STD edition, I haven't heard of that though.  Have you tested this solution yet with your team?

At the bottom of the MS supported upgrade scenario's it reads in the :

Supported Version and Edition Upgrades

"** Changing the edition of a SQL Server 2016 failover cluster is limited. The following scenarios are not supported for SQL Server 2016 failover clusters:
SQL Server 2016 Enterprise to SQL Server 2016 Developer, Standard, or Enterprise Evaluation.
SQL Server 2016 Developer to SQL Server 2016 Standard or Enterprise Evaluation.
SQL Server 2016 Standard to SQL Server 2016 Enterprise Evaluation.
SQL Server 2016 Enterprise Evaluation to SQL Server 2016 Standard."

The documentation makes it look like you can until you get to the end and read that tiny snippet.

As far as TDE goes, it depends on how you do it.  In my implementation, the master key is encrypted by the Windows DPAPl (I think that's the right acryonym) service, but then the generated key is in the Master DB that gets moved between nodes as you stated on the database instance level.


Well I could crate a dynamic pivot. Maybe it can help you guys with your own tasks someday:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(name)
                    from TblDataBaseSize
                    group by name
                    order by name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT date,' + @cols + ' from
             (
                select month(date) as date, name, convert(int,data_size_mb) as data_size_mb
                from TblDataBaseSize
            ) x
            pivot
            (
                avg(data_size_mb)
                for name in (' + @cols + ')
            ) p '

execute(@query);

The reason that i needed to create the dynamic is because I dont know how many databases I will have. This one above will create for a fixed numbers of databases ( or something different ).

select *
from
(
  select name, month(date) as mes, data_size_mb
  from TblDataBaseSize where name not in ('master','model','msdb','tempdb')
) src
pivot
(
  avg(data_size_mb)
  for name in (
[DB1], [DB2]    ,[DB3]  ,[DB4]  ,[DB5] ...
)

) piv;

I must say it didn't help me with anything because excel still cant understand what I need haha but it was a cool challenge.


Should I use backup compression for databases where all data is already compressed? In SQL server with enterprise edition, there are databases with all the tables/indexes compressed. Does it still make sense to do compressed backup?

Yes, in my opinion you should still use compression when backing up, even if the data is compressed. (Exceptions noted below.)

The compression used during backup is able to work across all of the data, unlike page and row compression, which are constrained to only considering the data in each individual page, one page at a time. Think about a phone book in a large metro are: imagine the difference between being able to use dictionary compression for all instances of the word Smith on one page, and then for all instances of the word Smith across all pages. That's similar to the improvement backup compression can add on top of already compressed data.

In my testing, using backup compression on top of page/row compression always yielded smaller backups and completed faster. There is also a lot of interesting commentary where others have confirmed the same. I don 't want to copy all of that here but you can read it at the following URL:

https://sqlblog.org/2010/03/15/yes-you-can-benefit-from-both-data-and-backup-compression

There are exceptions, of course: if your system is already heavily CPU-bound, compression isn't free, and it may make that problem worse. Also if your databases are encrypted with TDE, you'll find that the compression has little to no effect, because the output of that encryption is not a good candidate for further compression.


Warnings NoJoinPredicate This morning, our SQL Server 2016 started using high CPU percentage(90%) when normally is about 30%.

Reviewing cache plan with sp_blitzcache, I saw a new plan for an old query in the top of CPU time.

Full query plan is here.

This plan had a warning about no join predicate.

(@TokenCentro nvarchar(36),@Tarjeta nvarchar(max) ,@TarjetaAux nvarchar(max) )
SELECT   Socios.id, Socios.id AS IdSocio, Centros.id AS IDCentro, Centros.nombre,
         ISNULL(NombreWEB, Centros.Nombre) AS NombreWeb, Socios.Token, Foto, ISNULL(Socios.IdIdioma, 1) AS IdIdioma,
         AplicacionPropia, ISNULL(Socios.IdConf_Regional, 1) AS IdUnidad, Socios_Asignar_Centro.AplicacionWEB,
         CASE WHEN Socios.Email = '' THEN 1
              ELSE CASE WHEN Socios.Email IS NULL THEN 1
                        ELSE 0
                   END
         END AS ConEmail, Centros.HorarioVisible, Centros.PerfilApp, TactilEntrenoSocio, Centros.ColorKiosco,
         Conf_Regional.PrimerDia, ISNULL(Centros.IdGrupoApp, 0) AS IdGrupoApp, Centros.requiere_LOPD,
         ISNULL(Centros.LOPD, '') AS LOPD, Socios.aceptada_lopd, Centros.IntegracionDieta, Empresas.Chat, Centros.field,
         Centros.valoracionApp, Empresas.p_Premios, Centros.Reservas, ISNULL(CustomCss, '') AS CustomCss,
         Socios_Asignar_Centro.PuedeReservar, zonasHorarias.Zona, zonasHorarias.ZonaIana,
         ISNULL(Socios.TokenExten, '') AS TokenExtern, Centros.field1, Centros.tipoServicioPush,
         Socios.Nombre AS NombreSocio, Socios.Apellidos AS ApellidosSocio, Centros_Opciones.forzarLogin
FROM     Socios
INNER JOIN Socios_Asignar_Centro
ON Socios_Asignar_Centro.IdSocio = Socios.Id
INNER JOIN Conf_Regional
ON Conf_Regional.Id = Socios.IdConf_Regional
INNER JOIN Centros
ON Centros.Id = Socios_Asignar_Centro.IdCentro
   AND Centros.FechaBaja IS NULL
INNER JOIN Empresas
ON Empresas.Id = Centros.IdEmpresa
INNER JOIN zonasHorarias
ON Socios.idzonahoraria = zonasHorarias.id
INNER JOIN Centros_Opciones
ON Centros_Opciones.IdCentro = Centros.Id
INNER JOIN Centros_Tactiles
ON Centros_Tactiles.IdCentro = Centros.Id
WHERE    Socios.tarjeta IS NOT NULL
         AND Socios.tarjeta <> ''
         AND Socios.tarjeta <> '0'
         AND (
                 Socios.Tarjeta = @Tarjeta
                 OR Socios.Tarjeta = @TarjetaExtend )
         AND Centros_Tactiles.Token = @Token
         AND (
                 Socios_Asignar_Centro.FechaBaja IS NULL
                 OR Socios_Asignar_Centro.FechaBaja > CONVERT(DATE, GETUTCDATE()))
ORDER BY IdCentro, IdSocio DESC

After changing the length of the parameter from nvarchar(max) to nvarchar(50), the new plan was normal again. However, I can not see where the problem is.

Any ideas?

The first thing that jumps out to me is the difference in table access method on Socios. Both queries use the 79271_79270_Socios index but they use them in very different ways. The slow query does a seek to find all rows with Tarjeta not equal to ''. The query optimizer expects to read 6496920 rows out of 6498640 total rows in the table. A predicate is applied to filter out rows not equal to '0'. A filter operator is applied restrict the rows down to those that equal @Tarjeta OR @TarjetaAux, which are NVARCHAR(MAX) variables. This strategy seems odd and a not very efficient use of an index. You said that changing the data type helped so let's do some simple testing to see if SQL Server handles NVARCHAR(MAX) predicates in some special way.

I'm going to assume that the Tarjeta column has a data type of NVARCHAR(50) but I don't think that's particularly important for this demo. Here is my sample data:

create table #demo (col nvarchar(50));

INSERT INTO #demo WITH (TABLOCK)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

create index ix on #demo (col);

For this query I get a nice, simple index seek that has a low cost and only expects to read and return 1000 rows:

SELECT col
FROM #demo
WHERE col = '123';

This query has the same plan:

SELECT col
FROM #demo
WHERE col <> '0' AND col = '123';

It looks like the optimizer is smart enough to eliminate the col <> '0' filter because it's redundant.

If I cast my filter value to NVARCHAR(MAX) things take an unexpected turn:

SELECT col
FROM #demo
WHERE col = CAST('123' AS NVARCHAR(MAX));

The performance of the query is fine. It still only reads 1000 rows from the index. However, the plan is much more complicated. It looks like some information is missing from the XML and I can't figure out how to properly read it. Within the XML I found this:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(CONVERT(nvarchar(max),'123',0),CONVERT(nvarchar(max),'123',0),(62))">

Which seems like a hint that SQL Server has to do some kind of conversion to deal with the nvarchar(max) constant value. You can find more information about this in Dynamic Seeks and Hidden Implicit Conversions.

If I make the query a little more complicated I get a similar access pattern to what you experienced with the bad query:

SELECT col
FROM #demo WITH (FORCESEEK)
WHERE col <> '0' AND col = CAST('123' AS NVARCHAR(MAX));

The FORCESEEK hint was an attempt to make the query plan better but it didn't work. SQL Server will read all of the rows from the index and this plan has a much higher cost than before. Note that if we prevent SQL Server from applying an index seek on the <> '0' filter we can get a much more reasonable plan:

SELECT col
FROM #demo
WHERE LEFT(col, 50) <> '0' AND col = CAST('123' AS NVARCHAR(MAX));

However, a better solution is to change the datatype if possible. When switching to NVARCHAR(50) as you did I get a nice, simple index seek with this code:

DECLARE @Tarjeta nvarchar(50) = '123';

SELECT col
FROM #demo
WHERE col <> '0' AND col = @Tarjeta;

In conclusion, the unnecessary use of VARCHAR(MAX) appears to be resulting in extra work in some cases and leading to unnecessarily inefficient plans in others. Based on the information that we have, changing the variable's type seems to be the right preventative step.

Regarding the No Join Predicate warning, you always see that when SQL Server implements part of your query as a cross join. Check out StarJoinInfo in Execution Plans for further information on why that might happen. Sometimes cross joins are good for performance and the right thing to do. Sometimes they may not be ideal but the cross join may be part of a plan that is good enough or the best plan that was found before the optimizer ran out of moves. The query optimizer assigned a very low cost to the cross join based on row estimates. Joining 414 rows to 1 row in a cross join isn't really a big deal, but it could be a problem if those estimates are inaccurate.

It's also hard to say why this plan is suddenly worse than before, especially with what we observed with the simple NVARCHAR(MAX) testing above. Perhaps there was parameter sniffing and SQL server cached a plan generated from unlucky parameters. It's important to realize that SQL Server doesn't know anything about the values of the local variables in your plan without a RECOMPILE hint. The better query that you posted has hardcoded values which isn't a fair comparison to the other query which required SQL Server to guess or to use a cached plan.


Query Plan - Estimated Row Count Very High I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.

However, I then need to split the TaskName values into separate columns. For this I use APPLY (maybe there is a better way to do this?)

The Execution Plan returns 'Actual Number of Rows' value of over 100,000. Any idea why this is? There are only 1692 rows in the table!

Query plan is below.

https://www.brentozar.com/pastetheplan/?id=rJ6_JkKfQ

UPDATE: Tried below CASE query from @Biju jose but does not give desired output.

Output Required:

Output Returned:

Here is an alternative using CASE statement, Please test the code before run

INSERT INTO #TmpClientOnboardingTable
   SELECT
    f.FundCode
   ,f.FundName
   ,tr.Period
   ,td.TaskName
   ,td.TaskDetailID
   --,tr.LatestComment
   ,[Tasks].[OverallTaskStatus](tr.TaskDetailID,tr.TagID,f.FundID,tr.Period) as 'OverallStatus'
   ,[Tasks].[LatestComment](tr.TaskDetailID,f.FundID,tr.Period) as 'LatestComment'

   --,[TaskDetailID]
   --,[TagID]
   --,[StatusFlagID]
   --,[ApprovalLevelID]
   --,[ApprovedByID]
   --,[ApprovedDateTime]
   --,[LatestComment]
   --,[CommentDateTime]
   --,[CommentPostedByID]
   FROM [Tasks].[TaskRecord] tr
   INNER JOIN dbo.Fund f
   ON f.FundID = tr.FundID
   INNER JOIN [Tasks].[Tag] t
   ON t.TagID = tr.TagID
   INNER JOIN [Tasks].[TaskDetail] td
   ON td.TaskDetailID = tr.TaskDetailID
   INNER JOIN [dbo].[StatusFlag] s
   ON s.StatusFlagID = tr.StatusFlagID
   LEFT JOIN [dbo].[UserDetails] u
   ON u.UserDetailID = tr.ApprovedByID
   WHERE tr.Period = @Period
   AND t.TagID = 4; -- Client Onboarding View -- ('Cash Reconciliation', 'Non Trade Related')
   --AND tr.ApprovalLevelID = 1 -- analyst
   --ORDER BY f.FundCode

  --optional if its unique
  --CREATE CLUSTERED INDEX CL_idx ON  #TmpClientOnboardingTable (Fundcode)

    SELECT DISTINCT
  c.FundCode
 ,c.FundName
 ,c.Period
 ,MAX(CASE WHEN TaskName= 'Client Risk Assessment' THEN TaskDetailID ELSE NULL END) AS 'ClientRiskAssessmentParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Client Risk Assessment' THEN OverallStatus ELSE NULL END,'RED')) as 'ClientRiskAssessment'
 ,MAX(CASE WHEN TaskName= 'AML/KYC' THEN TaskDetailID ELSE NULL END) AS 'AMLKYCParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'AML/KYC' THEN OverallStatus ELSE NULL END,'RED')) as 'AMLKYC'
 ,MAX(CASE WHEN TaskName= 'Broker' THEN TaskDetailID ELSE NULL END) AS 'BrokerParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Broker' THEN OverallStatus ELSE NULL END,'RED')) as 'Broker'
 ,MAX(CASE WHEN TaskName= 'Checklist' THEN TaskDetailID ELSE NULL END) AS 'ChecklistParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Checklist' THEN OverallStatus ELSE NULL END,'RED')) as 'Checklist'
 ,MAX(CASE WHEN TaskName= 'Register of Assets' THEN TaskDetailID ELSE NULL END) AS 'RegisterOfAssetsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Register of Assets' THEN OverallStatus ELSE NULL END,'RED')) as 'RegisterOfAssets'
 ,MAX(CASE WHEN TaskName= 'Expense Budget' THEN TaskDetailID ELSE NULL END) AS 'ExpenseBudgetParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Expense Budget' THEN OverallStatus ELSE NULL END,'RED')) as 'ExpenseBudget'
 ,MAX(CASE WHEN TaskName= 'Decisions' THEN TaskDetailID ELSE NULL END)  AS 'DecisionsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Decisions' THEN OverallStatus ELSE NULL END,'RED')) as 'Decisions'
 ,MAX(CASE WHEN TaskName= 'Onboarding Review' THEN TaskDetailID ELSE NULL END) AS 'OnboardingReviewParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Onboarding Review' THEN OverallStatus ELSE NULL END,'RED')) as 'OnboardingReview'
FROM #TmpClientOnboardingTable c
GROUP BY c.FundCode
        ,c.FundName
        ,c.Period

Also, the estimated number is high because you are joining to the same table for 9 times which bloats the number of rows.


How to set roles and users with correct permissions on Postgres? My current database uat has as owner postgres. But now I would like to set roles and users with the correct permissions. Something simple like:

roles: dba, developer, application

users: dba1, dev1, dev2, app1

Where dba can do anything on the database for administration purposes; developer can makes SELECT, UPDATE and INSERT, usual developer operations; app1 the same operations as the developer role, for now.

How can I make this correctly ? And who should be the owner of my production table in this new setup ?

UPDATE:

------------------------
-- INITIAL SETUP
------------------------

-- create uat database
create database uat;

-- dba role
create role dba with superuser createdb createrole nologin replication bypassrls;

-- dev role
create role dev with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- app role
create role app with nosuperuser nocreatedb nocreaterole nologin noreplication nobypassrls;

-- alter postgres password
alter role postgres encrypted password '';

-- create users

create user dev1 login inherit encrypted password '' in role dev;
create user dev2 login inherit encrypted password '' in role dev;
create user dev3 login inherit encrypted password '' in role dev;
create user dev4 login inherit encrypted password '' in role dev;

create user webapp encrypted password '' in role app;

-- grant privileges to dba role
grant all privileges on all tables in schema public to dba;

------------------------
-- RESTORE
------------------------
psql -U postgres -d uat -h localhost -W -p 5432 < uat.sql

But when I logged in as user dev1 for example, I can't do anything:

select * from guru_basket_security;

Listing access for this table:

    \z
                                                              Access privileges
     Schema |                         Name                         |   Type   |     Access privileges     | Column privileges | Policies
    --------+------------------------------------------------------+----------+---------------------------+-------------------+----------
     public | guru_basket_security                                 | table    |                           |                   |
     public | identifier                                           | table    | postgres=arwdDxt/postgres |                   |

I got these errors:

ERROR:  permission denied for relation guru_basket_security
ERROR:  permission denied for relation identifier

The concept of groups & users has largely been done away with in PostgreSQL, with the terms USER and GROUP may be interchangeably used with ROLE. See documentation.

Suffice to say that what you want to do is create your "group" role first like this:

CREATE ROLE devs NOLOGIN;

Then grant all desired permissions to devs. Now create the users with IN ROLE devs clause like this:

CREATE ROLE jsmith LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
CREATE ROLE sjones LOGIN INHERIT IN ROLE devs ENCRYPTED PASSWORD '...' ...;
...

This will allow "real" users to login & inherit all the same permissions but no one can explicitly become devs (or the "group" role).


HIVE SQL [Error 10025]: Expression not in GROUP BY key Here is my SQL statement:

my_table includes 10+ columns (e.g, day, ip_address, user, request, etc), including strings and numbers. I want to GROUP by & HAVING based on column 'ip_address', if more than 20 records.

SELECT day, ip_address, user, request
FROM my_table
WHERE DAY = current_date()
GROUP BY ip_address
HAVING count(client_ip)>20

I got this error message

Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key xxx

But I need to keep all columns.

As long as one ip address has more than 20 rows, all these rows will be returned. If less then 20, do not return.

WITH cte AS ( SELECT *, COUNT(ip_address) OVER (PARTITION BY ip_address) cnt
              FROM my_table
              WHERE DAY = current_date() )
SELECT *
FROM cte
WHERE cnt > 20;


Storing the last updated date of a row in a different table I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database.

The problem I am running into is that not all of the tables on the legacy database have last modified date columns and we don't want to break anything by adding new columns to the legacy tables.

The worry is that since there are a lot of different processes inserting records, that the columns might not have been specified in those processes, and since we are switching to the new schema it's not worth it to rewrite those processes yet.

See this code contributed by Evan Carroll as to the problem caused by adding another column.

CREATE TABLE f (a int);
INSERT INTO f VALUES (1);
ALTER TABLE f ADD b int;
INSERT INTO f VALUES (2);

This causes the error,

Error Code: 1136. Column count doesn't match value count at row 1

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Then I can export just the new/updated rows and import them into the new database.

Would there be better ways of doing this?

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Your method will work, but that seems almost more likely to break something.. In that case, you're talking about doubling your write load. In the other case you're talking about adding a single column time stamp with time zone DEFAULT now() that won't otherwise be touched or seen,

unless someone wrote code that relies on *
and,

that code depends on the amount of columns returns.
or, reorders the columns (ie., sorts) and depends on the sorted order.

I would say adding a column is an extremely safe operation and the right thing to do.

See also

Create a trigger on all the last_modified columns in PostgreSQL

Trying a workaround

I tried creating a TRIGGER to add the column BEFORE INSERT on mysql, that also failed. So the only method you have is

another table if you're trying to insure against spurious use of INSERT without explicit column names.
replication

Example code (doesn't work)

CREATE TABLE f (a int, b int);

delimiter //
CREATE TRIGGER use_postgresql_insert
BEFORE INSERT ON f
FOR EACH ROW
BEGIN
  SET NEW.b = 1;
END;
//

INSERT INTO f VALUES (5);
ERROR 1136 (21S01): Column count doesn't match value count at row 1


group by, order by and pick last value of a column in each group I am using PostgreSQL 9.6. I have one "events" table. I need to group by an "item_id" column then order by "date" column in each group and then pick last value of "value_num" column in each group. Pls, help me how this could be done.

I suppose you want a "greatest-n-per-group" query. The easiest (but not necessarily the most efficient) way to achieve this in Postgres is using DISTINCT ON:

select distinct on (item_id)        -- defines the "group"
    item_id, date, value_num        -- show 1 row per group
from
    events
order by
    item_id, date desc ;            -- which order to be used


Execute a sql query inside IF() function Wherever I find an example of IF () function I only see something like below.

SELECT IF(500<1000, "YES", "NO");
+---------------------------+
| IF(500<1000, "YES", "NO") |
+---------------------------+
| YES                       |
+---------------------------+

But can I do something like below i.e executing a query?

IF(500<1000, CREATE DATABASE test1; , CREATE DATABASE test2;);

One way is to using prepared statements. But I want to directly execute the query inside it.

Learn about Stored Programs.  Write a Procedure that uses IF statements, not functions, to conditionally perform SQL statements.

Then invoke the Stored Procedure with a CALL statement.


Why is performance non-linear if I do many UPDATEs in a single transaction An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows.

Following some of the advice there, I've been trying to optimise running many UPDATEs in a single transaction. In the real scenario, we're batch processing data from another system, but I've got a smaller scenario for testing.

Given this table on postgresql 9.5.1:

\d+ foo
                                         Table "public.foo"
 Column |  Type   |                    Modifiers                     | Storage | Stats target | Description
--------+---------+--------------------------------------------------+---------+--------------+-------------
 id     | bigint  | not null default nextval('foo_id_seq'::regclass) | plain   |              |
 count  | integer | not null                                         | plain   |              |

I have the following test files: 100.sql, 1000.sql, 10000.sql, 50000.sql and 100000.sql. Each contains the following lines, with UPDATE repeated according to the filename:

BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
...
UPDATE foo SET count=count+1 WHERE id=1;
COMMIT;

When I benchmark loading each file, the results look like this:

              user     system      total        real   ms/update
100       0.000000   0.010000   0.040000 (  0.044277)  0.44277
1000      0.000000   0.000000   0.040000 (  0.097175)  0.09717
10000     0.020000   0.020000   0.230000 (  1.717170)  0.17171
50000     0.160000   0.130000   1.840000 ( 30.991350)  0.61982
100000    0.440000   0.380000   5.320000 (149.199524)  1.49199

The average time per UPDATE increases as the transaction includes more rows, suggesting that the performance is non-linear.

The earlier question I linked to suggests that indexes can be an issue, however this table has no indexes and a single row.

Is this just a case of "that's how it works", or are there some settings I can tune to improve the situation?

Update

Based on a theory in the current answer, I've run an additional test. The table structure is the same, but the UPDATEs all change different rows. The input files now look like this:

BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
UPDATE foo SET count=count+1 WHERE id=2;
...
UPDATE foo SET count=count+1 WHERE id=n;
COMMIT;

When I benchmark loading these files, the results look like this:

              user     system      total        real   ms/update
100       0.000000   0.000000   0.030000 (  0.044876)  0.44876
1000      0.010000   0.000000   0.050000 (  0.102998)  0.10299
10000     0.000000   0.040000   0.140000 (  0.666050)  0.06660
50000     0.070000   0.140000   0.550000 (  3.150734)  0.06301
100000    0.130000   0.280000   1.110000 (  6.458655)  0.06458

From 10,000 UPDATEs and up (once setup costs are amortised) the performance is linear.

(Note: I point out that this question is unpractical. So, it is completely inappropriate in order to evaluate the performance of PostgreSQL.)

I guess it is caused by the MVCC mechanism of PostgreSQL.

As well known, PostgreSQL's MVCC is implemented in over-writing mechanism. I'll show a concrete example using the pageinspector extention bundled in contrib subdirectory.

First, I start a transaction and do the first UPDATE statement:

BEGIN;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |      0 |     0 | (0,1)
(1 row)

UPDATE foo SET count=count+1 WHERE id=1;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |   2756 |     0 | (0,2)
     2 |   2756 |      0 |     0 | (0,2)
(2 rows)

When updating the data,  PostgreSQL reads and updates the fields of the first tuple header (t_xmax and t_ctid),  and then inserts the new (second) tuple.

Next, I do the second UPDATE statement:

UPDATE foo SET count=count+1 WHERE id=1;

SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid FROM heap_page_items(get_raw_page('foo', 0));
 tuple | t_xmin | t_xmax | t_cid | t_ctid
-------+--------+--------+-------+--------
     1 |   2755 |   2756 |     0 | (0,2)
     2 |   2756 |   2756 |     0 | (0,3)
     3 |   2756 |      0 |     1 | (0,3)
(3 rows)

After reading the first tuple, PostgreSQL reads the second tuple since the t_ctid field of the first tuple points to the second tuple (0,2). And then, PostgreSQL updates the fields of the second one and inserts the third one.

In this way, when many UPDATE statements are issued in a single transaction,  PostgreSQL must read and update the header fields of old tuples whenever inserting a new tuple.

This is my hypothesis. An weakness of this hypothesis is that the processing time order is O(n^2), so this may be wrong (it seem that the result of that benchmark doesn't fit with O(n^2)).

In any case, doing many UPDATE statements in single transaction is not a good way because it makes many dead pages that contain only  dead tuples, so you have to do VACUUM FULL (not VACUUM).


Test connection for linked server fails on desktop but not on server On SQL Server 2012 I have a linked server to Oracle 12c. On the server a test connection succeeds. Security is set to be made using this security content with an Oracle user and no local logins are defined. On my desktop the test connection fails with

Cannot create an instance of OLD DB provider "OraOLEDB.Oracle" for linked server. (Microsoft SQL Server, Error: 7302)

Checking Allow inprocess for the OraOLEDB.Oracle provider seems to have resolved the problem.


How can I create a view with self referencing table and a cyclic condition? I have a self-referencing table.

ID BIGINT NOT NULL PRIMAY KEY,
DELETED TIMESTAMP NULL,
PARENT_ID FK

How can I create a view of this table which each row's DELETED is NULL and any of its parent's DELETED is also NULL?

So far, I tried and it doesn't work as expected.

CREATE VIEW `OPERATOR_` AS
SELECT c.* from OPERATOR AS c LEFT OUTER JOIN OPERATOR AS p ON c.PARENT_ID = p.ID
WHERE c.DELETED_ IS NULL AND p.DELETED_ IS NULL;

example 1

ID    DELETED   PARENT_ID
0     2017...   NULL         NOT SELECTED
1     NULL      0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 2

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     2017...   0            NOT SELECTED
2     NULL      1            NOT SELECTED

example 3

ID    DELETED   PARENT_ID
0     NULL      NULL             SELECTED
1     NULL      0                SELECTED
2     2017...   1            NOT SELECTED

SELECT *
    FROM tbl a
    WHERE deleted IS NULL
      AND EXISTS ( SELECT * FROM tbl
                       WHERE id = a.parent_id
                         AND deleted IS NULL );

See if that works, then turn it into a VIEW.


Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0? -- Example 1: With Clustered Index

CREATE TABLE tbl5
(
    i VARCHAR(900) NOT NULL
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) )
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) )
    ,       ( REPLICATE('e' , 900) )
    ,       ( REPLICATE('f' , 900) )
    ,       ( REPLICATE('g' , 900) )
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- Example 2: With heap. When column 'i' is 392 m_freeCnt = 387. When 'i' = 393, I get a new page. Why can I not add more bytes to 'i'?

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) )
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) )
    ,       ( REPLICATE('e' , 900) )
    ,       ( REPLICATE('f' , 900) )
    ,       ( REPLICATE('g' , 900) )
    ,       ( REPLICATE('h' , 900) )
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1);
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space:

  The PFS has one byte for each page, recording whether the page is
  allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to
  80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

Would fit in page, indeed, but the PFS only says that the page is '95_PCT_FULL' and the record inserted is fairly big and close to the free space as-per-PFS (95% of 8060 is 403), so the INSERT chooses to allocate a new page.

With a B-Tree the record position is deterministic due to key order, so the engine must load the page and check the free space, and the record will fit.


"Access is denied" when connecting SSMS to Integration Services I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name:

  Connecting to Integration Services service on the computer 'FooDB' failed with the following error: "Access is denied."

  This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does have permission to access the SQL Server Integration Services service through DCOM.

This is a routine problem with a well-documented solution. For example, see the solutions here and here.

However, I've tried all the solutions I know of, and the problem remains.

In more detail, I've done the following:

Verified that the users connecting have the DCOM permissions listed in the articles linked to above on MsDtsServer100:

Launch and Activation Permissions: Allow Local Launch, allow Remote Launch, Local Activation, Remote Activation
Access Permissions: Allow Local Access, allow Remote Access
Configuration Permission: Allow Read

Confirmed with a packet sniffer that all traffic related to the connection is successfully coming through the firewall. The last packet shown before the TCP connection is torn down is a reply from the server containing the Windows status code for 'access denied' inside an MSRPC header.
Tested adding the users to the 'Distributed COM Users' group and/or the local administrators group, then restarting the servers. This allowed the users to connect to SSIS from SSMS using the local node names (FooDBN1, FooDBN2), but they still get an 'access denied' error when connecting to the the cluster network name (FooDB), which is what they're accustomed to using, and what works on our other clusters.

Also, I haven't found altering membership of these groups necessary on other clusters.

On the other clusters I've checked, I can connect SSMS to SSIS using the cluster name without any non-default configuration.

I realise this might be more appropriate for ServerFault and am OK with the question being migrated if needed, but it's also an SQL Server issue and I think users here might be more likely to have dealt with it before.

Platform details:

Windows Server 2008 R2 SP1
SQL Server 2008 R2 SP2
2-node active-passive cluster with a single SQL Server instance

Could anyone suggest what I should be looking at next here?

Update: this mysteriously just started working today, but only for members of the local administrators group. Nothing has changed as far as I can tell.

Long shot perhaps, but worth checking file

\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini

or the equivalent on your setup. You may have to manually edit it with the instance name. Otherwise SSIS connections might be looking for an msdb of a default SQL instance that doesn't exist.


Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)? My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. Do I have to stop current process before reconfiguration, or can I modify the setup and activate it while running?

Current streaming setup:

conninfo = host=conn_host user=conn_user dbname=conn_db
streaming_conninfo = host=conn_host user=streaming_user
backup_method = postgres
streaming_archiver = on
slot_name = barman
minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 31 DAYS'

Future ssh setup:

ssh_command = ssh postgres@conn_host
conninfo = host=conn_host user=conn_user dbname=conn_db
backup_method = rsync
reuse_backup = link
archiver = on
network_compression = true
minimum_redundancy = 3
last_backup_maximum_age = '3 DAYS'
retention_policy = 'RECOVERY WINDOW OF 31 DAYS'
wal_retention_policy = main

Just change the configuration.  It will take effect next time a backup is started.  If there is a currently executing backup it will not switch midstream.  You can let it finish, or kill it and start a new one with the new configuration.


Does osql.exe lock tables while exporting? While I am exporting a table from SQL Server using osql.exe, is the table locked? Will it interfere with other processes?

I am using SQL Server Workgroup Edition (10.52.4000.0).

osql.exe ( and sqlcmd.exe ) are just programs to send T-SQL commands to the engine.  Whether or not your tables will be blocked during your export will depend on what exactly it is you're doing to export them.  If you're running SELECT statements into a .csv with transaction isolation at no higher than the READ COMMITTED default level, you should only be able to encounter blocking on tables that include BLOB data ( like VARCHAR( MAX ), for instance ).  Of course, if you're just spitting out to .csv you might want to look at using bcp.exe instead anyway.

There will be locks placed on the tables regardless of how you're exporting the data ( Schema locks at least ), but those locks won't be an issue and cause blocking if you don't overdo your isolation level.  If you're doing a metadata only export, the locks will be placed on the metadata tables instead of the actual tables themselves.


Is there a configuration option that lets me limit the percent of a hard drive that Cassandra uses? I'm doing some testing with a small hard disk and I'd like to basically stop Cassandra in its tracks if a write or compaction or any other service brings that usage of the disk above a certain percentage. Is there any way to do that? How could I approach this problem?

Thank you! :)

I dis not saw such a option by Cassandra configuration, but you can use OS quota to limit disk usage.

Or more sophisticated is to write own small script to stop Cassandra, when Cassandra disk usage is too high. It will not be very long.