How can I re-structure my activities table? I've created a feed but I'm not sure whether it's okay or not, hopefully will get some answers to correct it if there's anything wrong.

I'm using MySQL.

When a user does something, his activity is saved in activities table as you can see, but this user isn't alone. He has friends. I use feeds table to create a feed for a friend of this user. I'm using fan-out model (I hope it's clear. If there are 10 friends of a user, 10 new records are added to the feeds table when that user does something)

The question is, sometimes users upload photos. When a user uploads a photo, activity_type_id becomes 5 (photo) and I hold images in activity_image to know which post has which image(s). So, if a user is uploaded a photo, I deal with 4 tables to serve these images when a friend of his view his feed : feeds > activites > activity_image > images

activity_image

id
activity_id
image_id

activities

id
user_id
activity (text)
activity_type_id (text, photo or something else) (foreign key from activity_type table)
created_at
(I removed other columns since they are pretty irrelevant)

feeds

id
subscriber_id (foreign key from users table)
activity_id (foreign key from activities table)
created_at

I was wondering, would it be a good idea to get rid of activity_image table altogether and keep image_id's (even image names, but then I would have violated normalization) in the activities table's image column in an array?

That way I will deal with three tables (feeds > activities > images) tops rather than 4, (2 if I store image paths in the activities table) so that way performance will be better.

What's your opinion on this? What should I do?

I would prefer to maintain the activity_image table to support more than a single image per activity. Assume, you are storing the actual images in a separate file server and not storing as BLOB in the table itself.

I don't see what performance issues you are facing? Maybe, a bit of elaboration would be better for the sake of clarity.

I would prefer to store the image path along with other image related details in activity_image table itself, mostly the image path value would be constant unless you decide to move away the images to other folder location.

How huge the tables are? How the queries are written? Do you have proper indexing strategy? Is the query columns are hitting the index precisely? What datatypes are used for indexed columns? Is paging concept implemented on your application? At one search how large the resultset is? Are you restricting user's search and load more when the user scroll (assume web/mobile app)?


Of the multiple cnf files in /etc/mysql/conf.d/ dir, which would be the last one to be read (and will eventually take precedence)? For some reasons (and only for some days to come) we are going to have 2 cnf files in /etc/mysql/conf.d directory, apart from the default one /etc/mysql/my.cnf. Now I know this can be disastrous, and we are working to resolve this.

But for the sake of knowledge ....

The config of /etc/mysql/my.cnf will be over-ridden by the cnf file in /etc/mysql/conf.d/

But between the 2 cnf files in /etc/mysql/conf.d, lets say a.cnf and b.cnf, which one would take precedence and why?

MySql version is "Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1"

If I understand it correctly, there can be only one config file. But mine ends with

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

and so it is directly responsible for "merging" those additional ones into itself.

The !includedir directive is covered in the manual https://dev.mysql.com/doc/refman/5.5/en/option-files.html which states:

  There is no guarantee about the order in which the option files in the directory will be read.

So it probably depends on the OS and filesystem but you should not depend on it anyway, even when it seems "stable" for now.

I sugget using the !include directive instead and maybe a different folder for those config files where the order matters.


Why my BEFORE UPDATE trigger takes values from the NEW record? I'm updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and creates a second one. On save the result is stored in the table table1 as 2 records, one with the original id (the PK) and the other with a new id. But I need to keep the original polygon (for history) and I try to do this with a trigger function that simply should make a copy of the original record from table1 to another table table2, totally similar to the first one, except that I removed the SERIAL and the PK.

My trigger:

CREATE TRIGGER area_reduc_1
    BEFORE UPDATE
    ON table1
    FOR EACH ROW
    WHEN ((pg_trigger_depth() < 1))
    EXECUTE PROCEDURE save_area_before_upd();

My trigger function is:

CREATE OR REPLACE FUNCTION field_survey.save_area_before_upd()
    RETURNS trigger AS
$BODY$
BEGIN

IF (TG_TABLE_NAME = 'table1') AND (TG_OP = 'UPDATE') THEN

    INSERT INTO table2
    SELECT DISTINCT *
    FROM table1 d
    WHERE NEW."id" = d."id" AND NEW.area_ref = d.area_ref
    ;
END IF;

RETURN NEW;

END;
$BODY$

BUT in table2 I end up with attribute values that are the ones I recorded in QGis, i.e. the ones coming from NEW! Why and how can I solve this?

I use QGis 2.8.2 (but that should not matter) and PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit.

Use record type OLD to access the old values as you do NEW to access the new values when updating a record in a table.

Refer: http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html


Restore PostgreSQL database with same locale settings as source database I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine.
My development machine is Windows 7-based, providing PostreSQL 9.y.

I want to restore the Ubuntu PostreSQL database on my development machine.

I noticed that the Ubuntu database uses the following locale settings:

character set encoding=UTF8
collation order, string sort order=en_US.UTF-8
character type, character classification=en_US.UTF-8

When I restore the database on the Windows machine without specifying locales, it will be set up with

character set encoding=UTF8
collation order, string sort order=German_Germany.1252
character type, character classification=German_Germany.1252

My plan is to have the database on my development machine as similar as possible to the database on my Ubuntu machine. So my idea was to first create a database on my Windows machine with production-matching locales, then restore my Ubuntu database prod-db.backup backup file into that created database:

createdb --host=localhost --username=postgres --encoding=Unicode --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --owner=prod prod-db
pg_restore --host=localhost --username=postgres --format=custom prod-db.backup --dbname=prod-db

This ideas does not work as the createdb on Windows will complain with the error invalid locale name en_US.UTF-8.

I went on a hunt to find the Windows locale names that match the Ubuntu locale names, including a solution to use the template0 template database, experimenting with different locale identifiers such as en_US.UTF-8 and en_us_utf8 I found scattered in the Internet ... but no solution works.

Is there a locale identifier for Windows that matches Ubuntu's en_US.UTF-8?
Or is locale German_Germany.1252 identical (enough) to en_US.UTF-8 so that I can stick to it and not worry about locales - I want to make sure that database queries behave identical when it comes to aspects such as query result set ordering.

These are the exact steps I take to import a copy of a PostgreSQL 9.5 database (exported on Linux using en_US.UTF-8 encoding) into PostgreSQL 9.3 on Windows 7 or PostgreSQL 9.5 on Windows 8.1. You need to create the database with the appropriate encoding prior to loading the SQL file, otherwise the encoding from Linux (en_US.UTF-8) will prompt Windows to use a default encoding.

REM WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details.
chcp 1252

C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "CREATE DATABASE my_db WITH TEMPLATE=template0 ENCODING='UTF-8' LC_COLLATE='american_usa' LC_CTYPE='american_usa'"

REM Grant privileges to the appropriate database user.
C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "GRANT ALL PRIVILEGES ON DATABASE my_db TO myuser"

REM Import my_db.sql. Ignore the following error (it comes from a line in the SQL file, but we have already created the database with the correct locale, so we are ok):
REM psql:my_db.sql:22: ERROR:  invalid locale name: "en_US.UTF-8"

C:\PostgreSQL\9.5\bin\psql.exe -o nul --quiet -U postgres -d postgres_password -f my_db.sql

As discussed here, the locale on Windows should be american_usa (on Windows 7). On Windows 8.1, either american_usa or en-US (not en_US as with Unix) will work.


data_precision and data_scale Missing from Oracle's all_tab_columns I need to know the data_precision and data_scale values for the fields in a view in Oracle 12c. I am not the owner of the view and I do not have access to the underlying tables.

When I query all_tab_columns for this view, all of the view's columns report null for data_precision and data_scale.

Yet, I can see in the view that many fields are numbers such as number(12,6) or whatever. For example:

Yet we can see that all_tab_columns describes this column as:

(If the picture's are not clear, we have a column with values like 0.0003, yet all_tab_columns is null for data_precision and data_scale.

Basically, all_tab_columns would lead me to believe that the field is a number(22), which is wrong.

How can I find out the actual data-type of the columns in this view? Why are integer types indistinguishable from decimal types in all_tab_columns?

Note: I tried to reproduce this with a trivial example, but instead of reproducing the issue, I got the expected/desired behavior.

e.g., this works fine:

create table foo
(
    bar number(19,6)
)
;

create view baz
as
select bar from foo
;

select
*
from all_tab_columns
where table_name in ('FOO', 'BAZ')
;

OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE
LD_PRICING,BAZ,BAR,NUMBER,,,22,19,6
LD_PRICING,FOO,BAR,NUMBER,,,22,19,6

I do not see NUMBER(12,6) anywhere in your output. That column is simply a NUMBER without precision specificed.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values (0.00025);

1 row created.

SQL> select * from t1;

        C1
----------
    .00025

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T1' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22

SQL>

Specifying NUMBER is not the same as NUMBER(22,0) or NUMBER(22):

SQL> create table t2(c1 number(22, 0));

Table created.

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T2' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL> insert into t2 values (0.00025);

1 row created.

SQL> select * from t2;

        C1
----------
         0

SQL>  create table t3(c1 number(22));

Table created.

SQL>  select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T3' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL>


Would it be correct to assume that inner query ordering will follow into my outer query? Is it spelt out someplace in the documentation that if I do this:
SELECT *
FROM (
  SELECT *
  FROM foo
  ORDER BY bar ASC
  LIMIT 1000
)
LIMIT 10
OFFSET 10

then the inner query ordering will carry on into the outer query?
Or do I need to repeat the inner query ORDER BY in my outer query? This is assuming that we don't have any joins.

You cannot depend on any ordering unless you have ORDER BY in the outer query. The ordering will depend on the execution plan chosen.
In the case of the present query I cannot think of a reasonable execution plan that would not preserve the ordering, but who knows what the future will bring for the query optimizer?
I would write a second ORDER BY. Sorting 1000 rows is cheap, and you can be certain then.


Check connection details to SQL Server 2000/2005 I want to migrate some databases hosted in SQL Server 2000/2005 to SQL Server 2014.

I don't have direct access to the databases and I want to know all applications connecting to it and connection details. Is there any best way to get this information? I tried using DB profiling but its not allowed as its taking up lot of space on hosted DB server which causing disruptions.

Any better way like a SQL statement or remote connection monitoring will be helpful.

Thanks in advance for all your help.

I am not sure regarding SQL Server 2000 since I did not work on it before, but if you want to check active transactions on SQL 2005, you can execute the following command SELECT * FROM sys.dm_tran_session_transactions. to check active sessions execute sp_who.
Useful commands:

check active queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

or

select
P.spid
,   right(convert(varchar,
        dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
        121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                ,'MIRROR HANDLER'
                ,'LAZY WRITER'
                ,'CHECKPOINT SLEEP'
                ,'RA MANAGER')
order by batch_duration desc

to check free disk space: https://www.mssqltips.com/sqlservertip/1706/determine-free-disk-space-in-sql-server-with-tsql-code/
to check Server status I recommend to use activity monitor (you can use resource monitor as well).


Sync offline PostgreSQL server I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the remote server. Is there any solution for this issue.

I am using Debian.

One-way read-only replica

If one is a read-only replica and the other one is the master, you can and should use WAL archive shipping for this. The master generates WAL and stores it somewhere accessible to both servers. Some like to use WAL-E to store it in S3, but you can just as easily have the replica scp it from the master on demand or whatever.

The replica, whenever it is online, fetches WAL and replays it to catch up with the master. It can also remove WAL from the archive after successful replay if there's only one replica. (Otherwise deciding when to remove WAL is ... hard).

The replica can run read-only queries, but nothing read/write.

Two-way read/write

If you want two-way disconnected sync, then I suggest you start with the chapter in database theory named "El Dorado", then continue reading into "Magic". Everybody wants this, and it does exist, but always with some difficult compromises. The biggest: how do you deal with conflicts where both modify the same record? How do you handle read-modify-write cycle inconsistencies when disconnected?

You need a database plugin that has application specific merge logic, or to write your application very carefully so it can survive last-update-wins merge logic. If you're doing that, and really keen to try very experimental code, keep an eye on http://wiki.postgresql.org/wiki/BDR_User_Guide, an in-development two-way logical replicaiton feature for PostgreSQL. Otherwise, you may have some success with Slony-I or Bucardo, external replication tools for Pg.

If at all possible, design you app not to require this, or have your app deal with the synchronization at a higher level using application specific logic, where you record changes (with triggers or with app logicl) and then replay them using your application's knowledge of what the data means.


Easily show rows that are different among multiple tables Imagine you have several tables with same structure (each table, one year of data):

table states_2017

stateID propertyID  owner         area  timestampGot
1       1           David Smith   15    123456789
2       2           Amanda Green  74    123456799
3       1           David Smith   15    123456999

table states_2018

stateID propertyID  owner          area timestampGot
1       3           Amanda Green   12   123459999
2       1           Peter Black    11   123499999
3       4           Jack Stone     62   123999999
4       2           Amanda Green   70   129999999

Then you have table properties with basic information about properties:

table properties

propertyID  defaultStateID  defaultStateYear
1           1               2017
2           2               2017
3           1               2018
4           3               2018

In the example above, state of propertyID 1 changed from its default state (stateID 1 in states_2017) to the most recent state (stateID 2 in states_2018). Similarly, state of propertyID 2 changed. How to show these changes (using MySQL)?

Desired output:

propertyID  owner         area  timestampGot
1           David Smith   15    123456789
1           Peter Black   11    123499999
2           Amanda Green  74    123456799
2           Amanda Green  70    129999999

The wanted query is not trivial at all but it would be a bit easier to write if there was one table and not one per year. So let's create a VIEW:

CREATE VIEW states AS
    SELECT stateID, propertyID, owner, area, timestampGot, 2010 AS year
    FROM states_2010
    UNION ALL
    ...
    SELECT stateID, propertyID, owner, area, timestampGot, 2017
    FROM states_2017
    UNION ALL
    SELECT stateID, propertyID, owner, area, timestampGot, 2018
    FROM states_2018
    ... ;

Then, I assume that you want to get data from the earliest and the latest date per product. So we can use one of the (many) ways to get a greatest-n-per-group result (applied twice, one for the earliest date and another for the latest):

select
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- earliest timestamp
  on  s.propertyid = p.propertyid          -- for this property
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year, si.timestampgot
        limit 1
      )
union all
select
    p.propertyid,
    s.owner,
    s.area,
    s.timestampgot,
    s.stateid
from properties as p
  left join states as s                    -- latest timestamp
  on  s.propertyid = p.propertyid
  and (s.year, s.timestampgot) =
      ( select si.year, si.timestampgot
        from states as si
        where si.propertyid = p.propertyid
        order by si.year desc, si.timestampgot desc
        limit 1
      ) ;


Update column with data from another table I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and I want to update the third:

C [ID, column1, column2,column3]

I am updating another third table using this query.

UPDATE C
  set column1=t.firstTab, column2=t.secondTab, column3=t.thirdTab
  from (select A.column1 as firstTab, B.column2 as secTab,
              (A.column1 + B.column2) thirdTab
        from A, B limit 1; ) as t ;

I got:

UPDATE 0

When I run this query:

select A.column1 as firstTab, B.column2 as secTab, (A.column1 + B.column2) thirdTab
            from A, B limit 1;

I got results. Am I missing something ?

Sample data: http://sqlfiddle.com/#!15/e4d08/5

The proper form would be (assuming current pg version 9.3 for lack of information):

UPDATE C
SET    column1 = A.column1
     , column2 = B.column2
     , column3 = A.column1 + B.column2
FROM   A
JOIN   B ON A.id = B.id  -- ??? not specified in question!
WHERE  C.id = A.id      --  ??? not specified in question!
AND   (C.column1, C.column2, C.column3) IS DISTINCT FROM
      (A.column1, B.column2, A.column1 + B.column2);

The last WHERE clause is optional to avoid empty updates that would not change anything (but still write a new row version at full cost).

ypercube already gave a basic explanation in his comment:

  You don't get duplication. Your derived table is cross joining A and B (i.e. without any joining condition) and then choosing an arbitrary row (LIMIT 1 without ORDER BY). It then uses the values from that arbitrary row to update all rows of table C. If you want different values to be used for different rows of C, you'll have to join the 3 tables (using JOIN - ON and WHERE)

Refer to the manual on UPDATE for details.


Why set Auto Update Statistics to False? I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been implemented.

I'm seeing daily blanket index rebuilds (I can deal with this one) and also daily manual updating of statistics.

Around half of the databases have been set to Auto Update Statistics = False, for reasons which are not clear other than I am told it is to reduce 'Performance Issues'...

I always thought, and worked to, best practice of setting this to True and felt the Manual Update was not necessary if this setting was True. Am I wrong?

Can anyone explain what the benefit would be in having this set as False, but doing a daily manual update instead?

I should mention that some of the databases are highly transactional (millions of Inserts, Deletes, Updates per day) Others are low in terms of transaction rates, and some are all but read-only. There is no rhyme or reason though as to which have the Auto Update setting set to False. It appears to be a lottery.

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats.  I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds.  Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were quite large.  There wasn't much value in updating stats during peak periods in this situation and auto-update stats would violate the SLA.  Consequently, maintenance was done during non-peak periods via a scheduled job.

Another option is to turn on both AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC database options.  This will allow queries to proceed with execution plans based on stale statistics rather than incur the overhead of updating stats synchronously.  This is especially appropriate for an OLTP workload as long as the server is sized to accommodate the query workload plus the background stats update.


MySQL Replication Error I am getting an Error on Slave as:

  could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977

As I am directly getting updates from master so why this happens.

How can we handle this in an effective manner so that there is no data loss on the slave.I do not want to set up the whole replication again for maintaining the data integrity.

Thanks..!

Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G

For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'. This indicates that you should look up the value 174465 in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL

Replication is Broken : Look for Tell-Tale Signs
If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error

If Seconds_Behind_Master is a Number

Replication is running
When Seconds_Behind_Master > 0, Replication is Catching Up.
When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working
MySQL replication problem: slave has become out of sync


Alternate ways to increase column size I have a database that is in production, and in some instances will have millions of records.  It has been requested that we increase the sizes of several columns, which is for the most part pretty easy.  But in one instance, we have a computed column that depends on the column I need to resize.  I've come up with this SQL script to make the change:

/**** Drop the index so we can drop and recreate the column ****/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[my_table]') AND name = N'column_index')
DROP INDEX [column_index] ON [dbo].[my_table] WITH ( ONLINE = OFF )
GO

ALTER TABLE my_table DROP COLUMN computed_column
ALTER TABLE my_table ALTER COLUMN main_column NVARCHAR(100)
ALTER TABLE my_table ADD computed_column AS (dbo.ComputeColumn(main_column)) PERSISTED
GO

/**** Recreate the index ****/
CREATE NONCLUSTERED INDEX [column_index] ON [dbo].[my_table] ([computed_column] ASC)
GO

The problem is, besides instances where there are millions of rows and it will likely create a heavy load on the DB server; I have to be able to run this script while the code is still being used.  If I drop the computed column, then any code that references that column will fail until it's recreated.

So I've been asked to find an alternate way to increase the column size of main_column without impacting computed_column.  Can that be done?

If the queries that are accessing the table that need to work while you're making this change are read-only, you could create a view that has the original column and uses the function in place of the computed column, then in a transaction rename the table and create a synonym with the original name that points at the view, then make your changes, start another transaction where you drop the synonym and rename the table, then finally drop the view. If the table needs to be read-write in this time, this may not be sufficient.

This isn't strictly doing what you asked (changing main without affecting computed), but I still think it has the desired effect - minimal impact on users.


How to move a PostgresSQL/ PostGIS to another drive Firstly I'd like to say I know little about databases (GIS background) and I might have bluffed it all so far.
But we wanted to set up a database for use with QGIS.
My understanding was that the PostgresSQL made the server and PostGIS add on made the databases on that server.
I'm not operating with a network or dedicated servers I have a C drive and a Q drive. We installed PostgresSQL (servers?) and PostGIS (database?) on my Q drive where I created and manipulated all my data.
But now we want to move it all to a portable drive (E) so that others can access the database by pluging into the drive is this possible?

and if so how does someone who does not understand databases nor SQL do it?

Cheers
matthew

There are two things:

the software directory containing the binaries like postgres and related files
the data directory that you can find with the SQL statement

SHOW data_directory;

On Debian Linux the configuration files are in a separate location, but since you are speaking about "drive letters", you are probably using Windows, where the configuration files are usually in the data directory.

You can simply copy the software directory and the data directory to your external disk after you have stopped PostgreSQL.

You can start the server with

<software directory>\bin\pg_ctl start -D <data directory>

If you want to start PostgreSQL through a Windows service, you may want to delete and re-create the service:

Delete the existing service:

<software directory>\bin\pg_ctl.exe unregister -N postgresql-11

The exact name to use for this, depends on your installation. You need to look it up in the "Services" management console (right click on the service, then choose "Properties" - make sure you close that dialog before proceeding). Once the old service is removed, create a new one  pointing to the new data directory:

<software directory>\bin\pg_ctl.exe register -N postgresql-11 -D <data directory> -S auto

Make sure you enclose the data directory in double quotes if it contains spaces.


Is there an underling difference between TIMESTAMP and ROWVERSION when creating columns with each? I'm having problems with SSDT detecting a change to a "TIMESTAMP" column where both the target column and model.xml column definition are of type "TIMESTAMP".  When this happens, the following alter statement is generated which fails trying to alter a TIMESTAMP column:

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyColumn] TIMESTAMP NOT NULL;

My guess is that target column was created as ROWVERSION rather than TIMESTAMP and this is tripping up SSDT as when I update the model.xml column def to "ROWVERSION" a difference is NOT detected and the ALTER statement is not generated.

I know that these two statements are functionally equivalent:

alter table [MyTable] add [NewColumn] TIMESTAMP not null
alter table [MyTable] add [NewColumn] ROWVERSION not null

and that ROWVERSION is a alias for TIMESTAMP..

However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = 'MyTable'

Is there an underlying "hidden" difference between columns created as ROWVERSION vs TIMESTAMP? - one that is being picked up by the Diff engine in SSDT?

Thanks in advance for any help!

I can't reproduce this on SQL Server 2017 or 2019 with Visual Studio 2019, so it would be interesting to know:

what version of sqlpackage.exe are you using?
what version of Visual Studio and SSDT are you using?
what version of SQL Server are you targeting?
what is the actual table definition in SSDT?
what options are you specifying when publishing?

Despite not being able to repro the issue, I do have some suggestions.

Whenever SSDT is doing some repetitive or redundant like this, I try to make sure that my definition in the model (SSDT project) matches the target, as sometimes things get transformed prior to being stored in SQL Server's metadata.

As you noted, the metadata is out of sync in this case.  The column is stored as timestamp regardless of whether you specify rowversion or timestamp in the CREATE TABLE statement.

  However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

This behavior is related to these types being data type synonyms.  Synonyms don't leave any trace that one was used in a DDL statement:

  However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There's no record that the synonym was specified in the statement that created the object.

What you're reporting is backwards from what I would expect, but it's possible that SSDT is trying to enforce this deprecation warning from the rowversion docs page:

  The timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

I'd suggest you update the SSDT project to use rowversion to avoid this problem.


Do long names for jsonb keys use more storage? Consider the example: Two tables foo and bar, each with a jsonb column.

For foo, there are a million rows where the value of the jsonb is [{"a":123}].
For bar, there are a million rows where the value of the jsonb is [{"very_long_key_not_premature_optimization_at_all":123}]

The json key in bar is 46 characters longer than that in foo. Would the size of bar be 46 million bytes greater than foo?

Yes key length matters

The bigger the key, the more space to store it. JSONB doesn't do anything special to keys.

Test Cast

Sample Data

# CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

# CREATE TABLE bar AS SELECT '{"very_long_key_not_premature_optimization_at_all":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

Table sizes

Now look at the tables

# \dt+ foo;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description
--------+------+-------+----------+-------+-------------
 public | foo  | table | ecarroll | 42 MB |
(1 row)

test=# \dt+ bar;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description
--------+------+-------+----------+-------+-------------
 public | bar  | table | ecarroll | 89 MB |
(1 row)

Source Code

You can see it in the source here

str = TextDatumGetCString(in_datums[i * 2 + 1]);
len = strlen(str);

v.type = jbvString;

v.val.string.len = len;

ZSON Extension

You may consider checking out ZSON extension which provides JSONB cross-row dictionary compression

  ZSON is a PostgreSQL extension for transparent JSONB compression. Compression is based on a shared dictionary of strings most frequently used in specific JSONB documents (not only keys, but also values, array elements, etc).

  In some cases ZSON can save half of your disk space and give you about 10% more TPS. Memory is saved as well. See docs/benchmark.md. Everything depends on your data and workload though. Don't believe any benchmarks, re-check everything on your data, configuration, hardware, workload and PostgreSQL version.


You only have SELECT privilege on mysql.user and mysql.db.
You need to have more than that. If you could be granted
GRANT UPDATE,INSERT,DELETE on mysql.user TO 'dbadmin'@'10.%';
GRANT UPDATE,INSERT,DELETE on mysql.db TO 'dbadmin'@'10.%';

This will allow you to CREATE USER as it must be able to write to those two tables.
With you current grants on mysql.user and mysql.db, you cannot create any user.


How to log within a stored procedure that is encapsulated within a transaction I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I actually want to do is to log if the execution of sp_B or sp_C fails and if so, which were the arguments passed to the failing stored procedure.
But as sp_B and sp_C do some inserts in some tables, I need to do a XA ROLLBACK if sp_B or sp_C fails.

So my question is: How can I log into a table while I am within a transaction
(or how to execute an insert statement outside of the transaction scope)?

To give you a little example of what I want to do (I've simplyfied the code and renamed most of the parameters/variables used to focus on the problem, I would never dream of using parameter names like a, b or c ;-) ):

The stored procedure sp_A looks like this:

CREATE PROCEDURE `sp_A`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  XA START 'sp_A';

  CALL sp_B(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -1;
    SET l_errorMessage = CONCAT('CALL sp_B(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  CALL sp_C(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -2;
    SET l_errorMessage = CONCAT('CALL sp_C(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  XA END 'sp_A';
  XA COMMIT 'sp_A';
  SET c = 1;
END;

The stored procedure sp_B looks like this (sp_C looks similar):

CREATE PROCEDURE `sp_B`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  INSERT INTO `someTable` (`a`, `b`) VALUES (a, b);

  IF l_errorOccured THEN
    SET c = -1;
    SET l_errorMessage = CONCAT('INSERT INTO `someTable` (`a`, `b`) VALUES (', a, ', ', b, ');');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  SET c = 1;
END;

sp_Log just inserts data into a table which I want to use as my logging table.

So actually the logging within sp_A works just fine (because the call to sp_Log is outside a transaction) but the logging within sp_B or sp_C won't work because by rolling back the transaction started in sp_A, the inserts done by the call of sp_Log within sp_B or sp_C are reverted of course.

(I have simplified the example, in sp_B and sp_C. There is more going on than just one simple insert statement and just to have more fun, there are transactions started and commited/rolled back within sp_B and sp_C as well.)

I would appreciate any hints you could give me.
Thanks!

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.


Configuring Dynamics GP's (SQL Server) Disks I am an accidental DBA at my work, and when I took it over, it was quiet a mess.  Fortunately, we have the green light to build new systems for an upgrade to our accounting system:  Microsoft Dynamics GP.  I would like to follow or exceed all recommendations from Microsoft.  Our current environment has significant performance issues.  I would like to get this forum's opinion of our SA's plan for our new system's configuration, compared to what is recommended by Microsoft.

As far as disk configuration, Microsoft recommends:

OS/APP RAID 1
SQL Log RAID 1
SQL Data RAID 5
SQL TempDB RAID 1
SQL Backups RAID 0.

What our SA has available is: A VMware 6 datacenter with an VNXe 3200 SAN.  That SAN has 11  x 600 GB 10k RPM drives and 9 x 2TB 7.2k RPM drives.  It also has 6 x 200GB flash drives to be used as very fast cache of the most frequently accessed data.  So the recommended hard disk specs don’t seem to take into account a datacenter that is as virtualized as ours.  He has pooled all the 10k RPM drives into a RAID 6 + 1 hot spare and all of the 7.2k RPM drives into a second RAID 6 + 1 hot spare and labelled them Performance Pool and Capacity Pool, respectively.  The more platters he puts into a volume, the more performance he can get out of it, plus all the redundancy that comes with a RAID 6 and a hot spare.  However, he would like to house a significant portion of the database in that cache.

Given that our accounting system and its SQL Server installation will not be alone in this configuration, but mixed together with other company systems (such as the exchange server), is this satisfactory or should I push that the accounting system have its own separate RAID configuration more in line with Microsoft's recommendations (except the RAID 5 part...I heard a puppy dies every time RAID 5 is used)?

While I understand you want to follow Microsoft guidelines, in my opinion a lot of the Dynamics best practices are outdated.

I personally don't work with GP but with AX and I tend to follow the SAN vendor guidelines for SQL Server rather than the guidance that Microsoft defined in the days of direct attached storage.

I haven't worked with EMC a lot but they clearly have a SQL Server best practices document which states:

  Most SQL server database environments can benefit from storage pool
  based  configurations

I'd just run some benchmarks before migrating to the new hardware and double-check with the SAN vendor and go with their recommendations.

For example, the recommendation says to use RAID1 for tempdb, but if you can get your hands on some nice NVMe PCI express cards those will perform lots and lots faster even if they aren't the "recommended" setup.


Remote connection scheme without Windows Authentication/Domain in detail There is a legacy C++ system that needs to be "translated", that uses ODBC for connecting to remote SQL Server Database.

System is distributed to many different OS and different domains, some always online, some updated on connection, some updated via sending/receiving updates in the form of files.

Each user has been registered in the SQL Server and with certain user roles on the database.

When a user is trying to login to the system, he actually sends his id and password to the application, which in turn are packed with a common/central SA account and query the central / distributed database where a table exists with all the relevant id/passwords, depending on the role of the local domain.

If the user has been registered in the database and his id and pass are a match, then he is given the green light to enter the application and his user id and password are thereafter used on every transaction, somehow variably hard-coded, in order to avoid to system questioning the id and pass on every recordset buildup.

My question is how this scheme should be modified to comply with established best practices on remote SQL Server access.

PS: Should a creation of database users is preferred, how a new user can insert his credentials to create a new profile on the database without elevating his role to more than datareader / datawriter

how safe is this remote SQL Server access scheme

The shared account credentials embedded in the application can be read by anyone who controls a computer on which the application runs.  No matter how you try to hide the credentials, you can never hide from a user with a debugger, who can just set a break point on the function that handles the password and examine the memory.

And from the description, it sounds like having access to the shared user credentials would, at the very least, allow a user to perform actions as some other user.  So unless you really trust all these users, this scheme isn't very good.

  When a user is trying to login to the system, he actually sends his id and password to the application
  ...
  whether there is an alternative or better to say a best practice on this problem.

Create a SQL Server Login or Contained Database User for each application user, instead just storing the users in a table, and use the credentials presented by the user to authenticate to SQL Server.


ORDER BY using custom priorities for text columns This is a sample table:

name | cat
----------
hugo | sal
bob  | mgr
mike | dev
jeff | mgr
john | dev

Both name and cat being Varchar.

cat reflects a category for these names, but I like to assign a priority to them, followed by a query to list them all, ordered by this priority.

What ways are possible? I could extract cat into a second table, build a foreign key etc. But given my app is very simple and the structure might be non-changeable: What possibilities do I have to list the mgr names first, followed by the dev names, followed by the sal names?

Assuming you can't change your structure, you can use a CASE statement in your ORDER BY:

ORDER BY case cat when 'mgr' then 1 when 'dev' then 2 else 3

If you can change your structure, however, you can create a Category table including the category code and a ranking, join your sample table to the Category table and sort by this ranking.


How to store data that depends on dynamic number of parameters I can't get my head around this problem. I'm trying to find a good way to store this in a PostgreSQL database.

Each client of my company needs to visualize data that depends on a dynamic number of parameters.

Example:

clientA wants to store the prices of its products. A price is defined by product_id and store_id. I need a way to query the database, giving the product_id and store_id and get back a price.

clientB wants to store the power of its cars. A power is defined by car_id, car_variation and fuel_type. I need a way to query the database, giving the car_id, car_variation and fuel_type and get back a power.

Each client must be able to define its own "dimensions" or "parameters" that define the data they want to vizualize.

I can either :

put all the data in the same table, with A LOT of empty columns for each row ==> seems dumb
create a new table for each client, with only the right columns in each of them? I'm not used to having a table per client but maybe it's the way to go
a third way I did not think of that tackles this exact problem

maybe you want a table that has a composite type as part of the key.

create extension hstore;

create table stats (client int, key hstore, value text , primary key (client, key));

insert into stats values
  (1,'"product"=>"gum","store"=>"711"','USD 0.49')
 ,(1,'"product"=>"soda","store"=>"711"','USD 0.99')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"nitromethane"','950kW')
 ,(2,'"vehicle"=>"prefect 351","fuel"=>"ULP-97RON"','600kW')
;

select * from stats where client=2 and key=hstore(array['vehicle','prefect 351','fuel','nitromethane']);

or perhaps with the value column as hstore also, perhaps he wants to store "loudness" and "power"


SQL Server Version Store Performance differences between RCS and ASI I understand that Read Committed Snapshot (RCS) operates at statement level, and Allow Snapshot Isolation (ASI) operates at transaction level, and both store row versions in tempdb. I also understand that ASI must be enabled in the DB, and in the settings of each connection.

My question is concerning the level of load that is placed on tempdb under RCS versus ASI. For example, for the same workload, would the load on tempdb be similar, or would the load on tempdb typically be higher (because it has to store more row versions)?

Just turning on the database setting will affect for how long the versioned rows need to stay in tempdb (since transactions that could be running in true snapshot would have repeatable read). I.e., enabling ASI will keep row versions for a longer time than enabling (only) RCSI.

As for performance for the applications we can't say. Only enabling ASI doesn't change client application behaviour unless they also explicitly ask to run with snapshot isolation. I.e., doesn't change default behaviour for apps.

Assuming that all your apps consistently are modified to use the snapshot isolation (pretty unlikely) then the performance will be pretty similar between the two. Readers aren't blocked by writers. You can get into edge cases with update conflicts and how they are handled with error handling, re-try etc. But there are so many dependencies here, so that cannot be generalized - and those are edge-cases anyhow so it is highly unlikely that they will have a measurable impact.


How to take pg_dump of a very large postgres database? I have a production database server running Postgres 8.4 on Cent OS 6.5.The volume of data has increased recently and within few months database now has 18500 tables amounting to approx 350GB database size.

Problem is now I can't even take a pg_dump of this database, Every time I try  I get OutOfSharedMemory error for max_number_of_shared_locks (current value is 256 instead of default 64) ?

How can we take dump of such a huge database ? please help..

Hello you need to change max_locks_per_transaction in the postgresql.conf file typically located in /var/lib/pgsql/data or wherever you installed the data directory for PostgreSQL.

Which was already answered here

You will have to restart the PostgreSQL Service.

The answer above also talks about having to possibly increase shared memory. Here is a link to a PostgreSQL article on that subject.


Can we reduce row lock contention on an index We have few queries which check their status into a table every 5 seconds and update their status..
Below is how the query looks like(all table columns are in update query)
update table1
set
Name='somename'
DetailMessage='Error'
LastUpdate=getdate()
where id=14

Recently this table has run into blocking issues and above update query is lead blocker.When  i ran Sp_Blitzindex, it showed no missing indexes,bur row lock contention of 284 minutes..
Below are few steps that i did so far..
1.Ensured Foreign keys are indexed(they are also Primary key)
2.Create indexes for select statements which can benefit from new index,so that lock contention on this index is reduced
3.I also reduced Fillfactor to 10, earlier each page used to store 93 rows, now it stores only 7 rows per page(still not able to give myself a 100% logical explanation on  how this change would help... )
Any suggestions further..Let me know if you need any further details
Below is the schema of table(changed column names) and table has only 350 rows and queries update this table at frequency of 20/30 queries every 5 seconds...
create table dbo.table1
(
[ID] [int] NOT NULL,
[Name] [varchar](500) NULL,
[DetailMessage] [nvarchar](max) NOT NULL ,
[LastUpdate] [datetime] NOT NULL
)

PS: This is a third party query and we can't modify source database with changes like changing isolation level...I can add indexes only
SQl version:SQl 2012

If this third party app is holding those row locks long enough to cause contention, and

you can't change that behavior,
and the blocked queries are read queries, then

One option would be to enable Read Committed Snapshot Isolation in that database:
ALTER DATABASE [YourDatabaseName]
SET READ_COMMITTED_SNAPSHOT ON;

This should stop those UPDATE queries from blocking your read queries.
Note that this will affect the results of the read queries, since they will be seeing a point-in-time snapshot of the data being read, rather than being blocked waiting for the update.  It will also increase load on tempdb.  For a detailed treatment of implementing RCSI, check out Kendra Little's post here:
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


Query is slow in SQL Server 2014, fast in SQL Server 2012 During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues.

One of the queries that completes within seconds on SQL Server 2012 seem to be hanging on SQL Server 2014.

SELECT  DISTINCT
    src.[Id]
FROM
    [stg].[BaseVolumes] src
JOIN
    [tmp].[Dates] d ON src.[CalWeek_Nmbr] = d.[CalYrWkDense_Nmbr]
WHERE
    EXISTS (SELECT  *
            FROM
                (SELECT     ctry.[ISOCode]                  AS  [Mkt_Code]
                            , so.[Code]                     AS  [SlsOrg_Code_AK]
                            , so.[DistributionChannelCode]  AS  [DistChnl_Code_AK]
                            , prd.[SupplierCode]                AS  [SKU_Code_AK]
                            , cl6.[Code]                        AS  [CstHierLvl06_Code_AK]
                            , lp.[BaseDateID]                   AS  [Dte_EK]
                 FROM   [PM_APP].[edw].[BaseVolumeDayCurrent]   lp
                 JOIN   [PM_APP].[dbo].[Country]                ctry    ON  lp.[CountryID] = ctry.[ID]
                 JOIN   [PM_APP].[dbo].[SalesOrganisation]      so      ON  lp.[SalesOrganisationID] = so.[ID]
                 JOIN   [PM_APP].[dbo].[Product]                prd     ON  lp.[ProductID] = prd.[ID]
                 JOIN   [PM_APP].[dbo].[CustomerLevel6]         cl6     ON  lp.[CustomerID] = cl6.[ID]
                 WHERE
                     lp.[ModifiedByApp] = 1) lkp
            WHERE
                src.[Mkt_Code]                  =   lkp.[Mkt_Code]
                AND src.[SlsOrg_Code_AK]        =   lkp.[SlsOrg_Code_AK]
                AND src.[DistChnl_Code_AK]      =   lkp.[DistChnl_Code_AK]
                AND src.[SKU_Code_AK]           =   lkp.[SKU_Code_AK]
                AND src.[CstHierLvl06_Code_AK]  =   lkp.[CstHierLvl06_Code_AK]
                AND d.[Dte_EK]                  =   lkp.[Dte_EK]
        )

Row count:

BaseVolumes:            23108
Dates:                  18628
BaseVolumeDayCurrent:   108115503
Country:                249
SalesOrganisation:      29
Product:                18446
CustomerLevel6:         295

Wait stats showing high CXPacket, but there is one hanging task with SOS_SCHEDULER_YIELD, (print from sys.sysprocesses)

 spid | blocked | waittime | lastwaittype          | cpu     | physical_io
 76   | 0       | 9886044  | CXPACKET              | 13902   | 31192
 76   | 0       | 0        | SOS_SCHEDULER_YIELD   | 9829719 | 83077
 76   | 0       | 11248    | CXPACKET              | 11110   | 0
.
.
.

Attached are the execution plans from SQL Server 2012 and SQL Server 2014.

Execution plan SQL Server 2014
Execution plan SQL Server 2012

Any help much appreciated.

The most likely situation is that the new SQL 2014 Cardinality Estimator is yielding a poor row estimate for one or more joins in your query and this has led SQL Server to choose an inefficient plan.

If you are able to run the query in SQL 2014 with "include actual execution plan" turned on, you can use the query below in another tab to view the real-time progress of rows flowing through each query operator.  I noticed that you only have an estimated plan for 2014 (compared to an actual plan for 2012), presumably because you cannot run the query to completion in SQL 2014.  So this could give you more insight into the actual rows flowing through the query in 2014 and may lead you to a way of tweaking the query that runs efficiently using the new Cardinality Estimator.

In the meantime, until you are able to optimize the query you could use QUERYTRACEON with trace flag 9481 for this query or you could follow Brent Ozar's advice of running the database at the SQL 2012 compatibility level, carefully testing your queries with the new Cardinality Estimator, and only updating the compatibility level to 120 (SQL 2014) once satisfied with these results.

/* Live query progress in SQL 2014 */
SELECT session_id,node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
    CAST(SUM(row_count)*100 AS float)/NULLIF(SUM(estimate_row_count),0) AS percent_complete,
    SUM(elapsed_time_ms) AS elapsed_time_ms,
    SUM(cpu_time_ms) AS cpu_time_ms,
    SUM(logical_read_count) AS logical_read_count,
    SUM(physical_read_count) AS physical_read_count,
    SUM(write_page_count) AS spill_page_count,
    SUM(segment_read_count) AS segment_read_count,
    SUM(segment_skip_count) AS segment_skip_count,
    COUNT(*) AS num_threads
FROM sys.dm_exec_query_profiles
WHERE session_id <> @@spid
GROUP BY session_id,node_id,physical_operator_name
ORDER BY session_id,node_id;


How to get server cpu, memory, network usage from SQL Server For reporting purposes, I want to get SQL Server box CPU, memory, network (not SQL Server process alone) and show it in report. Do you have any idea how to pull this? My initial thoughts are run wmi queries through task scheduler to get the data every 15 minutes and insert into database table and use it in reports

My report format will be like below

Server name:
CPU usage
Memory usage
Network usage
Disk space usage:

My questions:

can this be done in any,other way other than wmi/power shell
I will also need to get web servers data
will you be able to give one sample query for one server.

If you are running SQL Server 2008 and up, then you can use Performance Data Collector on SQL Server. This will get you all the metrics (and even more) that you need.

Below is from my TEST server :

If you want for webserver, then your best bet is to use Powershell. Even you can generate nice reports with charts out of your collection.


Query to update column in one table based on conditions met in another table I have two tables with the following structure (rest of the columns omitted for brevity) :

tblORDERS

+-----------+-------+
|Order_ID   | Status|
+-----------+-------+
| 100       | 0     |
| 101       | 0     |
| 102       | 0     |
+-----------+-------+

tblUNITS

+------------+-------+----------+
| Unit_ID    | Status| Order_Id |
+------------+-------+----------+
| 1          | 1     |   100    |
| 2          | 1     |   100    |
| 3          | 1     |   100    |
| 4          | 1     |   101    |
| 5          | 1     |   101    |
| 6          | 0     |   101    |
| 7          | 0     |   101    |
+------------+-------+----------+

Order_ID is a Foreign Key on tblUNITS. There can be many 'units' inside of one 'order'. I need to create an UPDATE statement that will check tblUNITS to see if all units with the same order_id are in status 1, indicating "complete" . If they are, then I want status in tblORDERS to be updated to status 1, indicating "complete".

Can anybody help me out in writing such query?

I think this solution will work.

--demo setup
DROP TABLE IF EXISTS tblOrders;
DROP TABLE IF EXISTS tblUnits;
CREATE TABLE tblOrders (
  Order_ID INTEGER,
  Status INTEGER
);

INSERT INTO tblOrders
  (Order_ID, Status)
VALUES
  ('100', '0'),
  ('101', '0'),
  ('102', '0');

  CREATE TABLE tblUnits (
  Unit_ID INTEGER,
  Status INTEGER,
  Order_Id INTEGER
);

INSERT INTO tblUnits
  (Unit_ID, Status, Order_Id)
VALUES
  ('1', '1', '100'),
  ('2', '1', '100'),
  ('3', '1', '100'),
  ('4', '1', '101'),
  ('5', '1', '101'),
  ('6', '0', '101'),
  ('7', '0', '101');

--solution
UPDATE o
SET o.Status = 1
FROM tblOrders o
--check to see if there are units for the order
WHERE EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
            AND u.STATUS <> 1
        )
--verify update
select * from tblOrders

| Order_ID | Status |
|----------|--------|
| 100      | 1      |
| 101      | 0      |
| 102      | 0      |


Replacing SQL Server TDE soon expiring certificate What is the best way to replace a soon expiring certificate? Downtime should be avoided. Databases size is 100GB.

If the certificate expires TDE will continue to work so don't panic there but you should still replace it to keep with your security policy. Because the certificate is used to encrypt the Database Encryption Key (DEK) and not the data, it is quite easy to change the certificate.

Create or import a new certificate and then use it to encrypt the DEK.

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256 --Your choice of algorithm may be different
ENCRYPTION BY SERVER CERTIFICATE 'YourNewCertificate';

UPDATE

I made a mistake in my code above. It is not possible to alter the encryption algorithm and the certificate used in one statement. This was not required anyway so the following code is sufficient:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE YourNewCertificate;
GO


Determine Which Parameters Passed Into Stored Procedure Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?

You can run a Profiler Trace using the default template.  It will include the RPC:Completed Event.  When you review the trace log you will see the values of the parameters passed and you can determine which were left out, therefore using the default values.


How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259? I got an error while trying to send an email:

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

Here is my query:

DECLARE @Table TABLE (
server_name varchar(50),
database_name varchar(20),
byuser varchar(50),
size_mb numeric (20,0),
size_gb decimal (9,2),
backup_started datetime,
backup_finished datetime,
total_time varchar(50),
expiration_days INT,
isdamaged bit,
iscompressed bit,
iscopyonly bit,
databasecreationtime datetime,
physicaldevicename varchar(100),
isolderthanaday bit
);
insert into @Table (server_name,database_name,byuser,size_mb,size_gb,backup_started,backup_finished,total_time,expiration_days,isdamaged,iscompressed,iscopyonly,databasecreationtime,physicaldevicename,isolderthanaday)
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 1 ELSE 0 END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''D''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notifications',
    @recipients = 'example@example.com',
    @subject = 'Full backup in last 24 hours',
    @query = N'select * from @Table',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ',';

I tried setting max byte size for mail to 10MB but that didn't help. The problem is associated with @query paramteter. What would you suggest?

P.S sp_ineachdb is a customized procedure

From your example, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either.


Data File Size Stays Same Whereas Log File Size Is Increasing I am using the following snippet to track the data and log file size increase after certain operations:

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

The problem is, after each write operation, log file size increases as expected but the data file size stays the same for 4 or five operations and then suddenly increases.

Why would this be?

Probably because the function FILEPROPERTY reports either whole pages user or even whole extents used (documentation is unclear on which, but that detail doesn't really matter). The point is that storage inside the database file is pre-allocated for data. That means that some storage is pre-allocated and now you have free space that can accommodate some new rows until it has to grow the next time. This pre-allocation inside the database files doesn't really happen in a transaction log file - at least not the level that FILEPROPERTY reports on.


Unable to configure oracle 11g net server I installed Oracle 11g on my Windows Server 2008 workstation. After I try to configure oracle net server with port number 1521. But ONS error say to me as "Use another port number. This port is already in use.". How can I solve this problem?

Go to www.sysinternals.com - download the Sysinternals suite - and then run Tcpview and check what executable is running on port 1521. You can sort by port by clicking on the "Local port" column. If you try and run netstat -abn from the command line, you'll (in all likelihood) get an error "The requested operation requires an elevation". Just why this error occurs when this information is available through Tcpview is a mystery.

Do you have an Oracle instance already installed? I've installed Oracle on a number of Windows machines and I've never come across port 1521 being used by anything else. In any case, Tcpview will tell you which executable is blocking your Oracle port.

[EDIT - after further research]

I was reading around this and I came across this post, which shows that running resmon.exe will give you the information htat you want - Go to the "Listening Ports" tab, "Ports" column, but as pointed out, the GUI is truly awful.

From the same question here, from the command line you can run netstat -aon | find /i "listening"``, but as I mention, I can't get the corresponding process name using the b flag.


ALTER innoDB table from row to page compression My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affect the newly created tables only.
Thus, I create a replica table and use INSERT INTO SELECT.
I wonder if it is possible to ALTER a table to change the compression type (from row to page)?
We can ALTER an ordinary table to use page compression as
ALTER TABLE t1 PAGE_COMPRESSED=1;

My question is about a safe way to remove ROW_FORMAT=COMPRESSED and add PAGE_COMPRESSED=1.

I tried different methods and read many articles. The standard way to do so is
ALTER TABLE t1 KEY_BLOCK_SIZE=0 `PAGE_COMPRESSED`='ON';

However, this will leave KEY_BLOCK_SIZE on all keys.
Some say this is just the SHOW CREATE TABLE display, as KEY_BLOCK_SIZE=8 standards for the largest block size for the keys, but the value is exactly that of the COMPRESSED table. If the table was created with KEY_BLOCK_SIZE=1, keys will have KEY_BLOCK_SIZE=1 after uncompression.
Some, on the other hand, say this bug has been fixed in MySQL 8, but I confirm it exists in MariaDB 10.5.9.
VERDICT
ALTER is a clean way, but I favour INSERT INTO SELECT for two reasons:

ALTER writes the uncompressed table on disk while INSERT writes the compressed pages on the disk.
We can create new table on a separate disk to reduce I/O.


Calculate and show DATETIME difference on PIVOTING query I have the following PIVOTING query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

SELECT *
FROM (select  jsc.applicationid
              ,[the date of change]=MIN(jsc.changedate)
              --,jsc.[oldStatus]
              ,jsc.[newStatus]
              ,C1.countryName
              ,[Name] = ja.firstname + COALESCE(' ' + JA.LASTNAME, '')
              ,nationality=C2.countryName
              ,[Agency Name] = a.name
      from dbo.applicationStateChange jsc
      INNER JOIN dbo.application app
            ON app.applicationID = jsc.applicationID
      INNER JOIN dbo.applicant ja
            ON ja.applicantID= app.applicantID
      INNER JOIN dbo.agency a
            ON a.agencyID = app.agencyID
      INNER JOIN dbo.country C1
            ON C1.countryCode = ja.countrycode
      INNER JOIN dbo.country C2
            ON C2.countryCode = ja.nationalitycode
      WHERE 1=1
      --  AND jsc.[oldStatus] = 'OLA'
      AND jsc.[newStatus] IN ('OLA', 'AWA', 'TBM')
      --AND ja.countrycode = 'GB'
      AND YEAR(jsc.changedate) >= 2010
      GROUP BY jsc.applicationid
               --,jsc.changedate
               --,jsc.[oldStatus]
               ,jsc.[newStatus]
               ,C1.countryName
               ,ja.firstname
               ,JA.LASTNAME
               ,C2.countryName
               ,a.name
) RADHE
PIVOT (MIN([the date of change]) for [newStatus] IN ([OLA], [AWA], [TBM]) ) as KRISHNA
WHERE 1=1
AND [OLA] IS NOT NULL
AND [AWA] IS NOT NULL
AND [TBM] IS NOT NULL
ORDER BY  applicationid

That gives me results like these:

Question:

How could I calculate X and Y on the above picture and display the result there?

X - should be the amount of time (ok it can be number of days to keep it simple) between AWA and OLA.

Y - should be the amount of time (ok it can be number of days to keep it simple) between TBM and AWA.

Let me know if this produce the desired result.

First I've set up an example on dbfiddle that returns unpivot data similar to the result of your question.

CREATE TABLE foo
(
  applicationId int,
  countryName varchar(20),
  name varchar(20),
  nationality varchar(20),
  agency varchar(10),
  newStatus varchar(10),
  dateChange datetime
);

INSERT INTO foo VALUES
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'OLA', '20170101'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'AWA', '20170110'),
(1001, 'Mexico', 'Juan', 'Mexico', 'CIA', 'TMB', '20170105'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'OLA', '20170203'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'AWA', '20170207'),
(1002, 'Germany', 'Michael', 'Germany', 'FBI', 'TMB', '20170212'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'OLA', '20170301'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'AWA', '20170311'),
(1002, 'Rusia', 'Boris', 'Rusia', 'SNA', 'TMB', '20170305');

Then I enclosed your pivot query inside a CTE function, and finally it calculates datediff using data returned by pivot query.

WITH p AS
(
    SELECT *
    FROM   (SELECT *
            FROM   foo) SRC
    PIVOT (MIN(dateChange) for newStatus IN ([OLA], [AWA], [TMB])) pvt
)
SELECT applicationId,
       countryName,
       name,
       nationality,
       agency,
       OLA,
       DATEDIFF(DAY, [OLA], [AWA]) X,
       AWA,
       DATEDIFF(DAY, [AWA], [TMB]) Y,
       TMB
FROM P
ORDER BY applicationId;
GO

This is the final result:

applicationId | countryName | name    | nationality | agency | OLA                 |  X | AWA                 |  Y | TMB
------------: | :---------- | :------ | :---------- | :----- | :------------------ | -: | :------------------ | -: | :------------------
         1001 | Mexico      | Juan    | Mexico      | CIA    | 01/01/2017 00:00:00 |  9 | 10/01/2017 00:00:00 | -5 | 05/01/2017 00:00:00
         1002 | Germany     | Michael | Germany     | FBI    | 03/02/2017 00:00:00 |  4 | 07/02/2017 00:00:00 |  5 | 12/02/2017 00:00:00
         1002 | Rusia       | Boris   | Rusia       | SNA    | 01/03/2017 00:00:00 | 10 | 11/03/2017 00:00:00 | -6 | 05/03/2017 00:00:00

dbfiddle here


PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration) I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment.

I am having a hard time understanding the differences between these 2 types of replication in terms of (1) Configuration (2) How the 2 servers Master/Slave perform in each scenario

Replication on PostgreSQL (9.2+) is essentially XLOG files of 16MB in size (depending on frequency settings for creating each file) are being created on Master and sent by some method to the Slave.

My Setup (for purposes of this question)

Configuration of Postgresql.conf on Master
archive_command= 'rsync -av %p postgres@[SlaveIP]:[wal_archive_folder]/%f'

Configuration of Recovery.conf on Slave to read log files
restore_command = 'cp [wal_archive_folder]/%f \"%p\"'
primary_conninfo = 'host=[MasterIP] port=5432 user=postgres'

My question is what part of this configuration makes this "streaming" replication versus "log shipping"? My master is configured to use rsync to send logs to the slave (is this log shipping?)
My slave is configured to be able to connect to the master in recovery.conf (is this streaming?)

Second part of the question: What is happening? I understand there is another protocol on PostgreSQL via WAL_sender & WAL_receiver. But I am not clear if this is used for streaming only and if so, how is the rsync being used in the Master?

:) Thank you!! And sorry if this is an obvious question. I've been doing a bunch of reading blogs/books but having a hard time understanding. Postgres wiki is so in depth that it takes a long time to get through it all (and I have deadlines)

"Streaming replication" refers to continuous sending of WAL records over a TCP/IP connection between the master and the replica, using the walsender protocol over replication connections. The master reads its own WAL from pg_xlog and sends it to the replica on demand. It's configured with a primary_conninfo directive in recovery.conf and pg_hba.conf entries on the master to permit replication connections. You also need wal_keep_segments and some other options covered in the docs.

"Log shipping" refers to periodic sending of WAL records as whole WAL archives via a file transfer protocol to an archive location from which the replica can then fetch them. It's configured with a restore_command directive in recovery.conf and an archive_command in the master. PostgreSQL doesn't care where the files are or how they're transferred, only that the archive_command puts them there and the restore_command fetches the required archive; this permits the building of systems like PgBarman and WAL-E.

Streaming replication doesn't have as much lag, as records are sent as they are generated. However, it requires both master and replica to be online and able to communicate directly. It also requires the replica to keep up well enough that the master still has on-disk copies of the WAL the replica needs, and generally requires you to spend extra pg_xlog space on retaining extra WAL for the replica.

Log shipping replication has more lag because the replica only sees WAL once a whole archive is sent. However, it can work even when the master and replica can't communicate directly over TCP/IP by using a shared storage location. It continues to work even if the replica is down for a while, because the master will have discarded the WAL from pg_xlog only after archiving it, so the WAL is still in the archive and usable by the replica even though the master can't send it by streaming anymore. Note that archive_command never gives up, so pg_xlog can fill up if archiving is failing; for that reason it's better to archive to a reliable location and then have the replica server fetch from that location.

In general you actually combine the two, i.e. use both. In that case, streaming replication gets used when everything's going fine. If the replica gets too far behind and the master has discarded xlogs it requires, a connectivity problem arises, etc, then the replica will switch to reading archived WAL until it's caught up. It'll periodically re-try switching back to streaming until it succeeds.

If you're only going to use one, use log shipping, because streaming replication without log shipping fallback is (until PostgreSQL 9.4) potentially prone to replication lag causing failures that force a replica to be re-built .

PostgreSQL 9.4 changes this a bit, because streaming replication can now use "replication slots". That lets the master keep track of how much WAL a replica needs, and avoid throwing it away until the replica has replayed it. So there's no more need for wal_keep_segments if you use a replication slot (not the default).

See my article streaming replication slots in PostgreSQL 9.4.

9.4 also introduces the foundations for streaming logical replication, which is yet another mechanism, designed for use by logical replication systems like Londiste, Slony-I, and the new bi-directional async multi-master replication feature.


Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write? I have this existing table for IP storage:

CREATE TABLE [dbo].[IPAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IPv4Address] [varchar](15) NULL,
    [IPv6Address] [varchar](45) NULL,
 CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED
(
    [IPv4Address] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I want to convert this table to use an integer (or several integers) to store this IP information. I would like to make IPv4Address and IPv6Address computed columns based on my new columns for storage.

The problem I'm facing is that I have some legacy clients hitting this database, who need to write directly '192.168.1.54' into my IPv4Address column. I would like the database to intecept this, do a conversion to integer, and store it in a new column on the table defined as int.

Is it possible to

Script a conversion of my existing table, converting all of my "string" IPs to their integer values and storing those in an int column, and making these "string" columns computed
Allow legacy clients to "write" to these computed columns and intercept that data, convert it to an int, and store it transparently to legacy clients?

I am fairly sure I can write a script to conver the data, but I don't want to waste my time if I cannot make this transparent to legacy clients of my database. So any guidance will go a long way.


What is the privilege for account unlock in Oracle I want to understand the privileges better, but I'm not able to find some useful info using Google.

I thought that I needed ALTER USER privilege to be able to unlock user and change password. But currently I'm on a machine, where I can change the password (also for other users), but I'm not able to unlock locked user. Why is that?

I tried also

SELECT * FROM USER_SYS_PRIVS ;

ant the result is:

TEST_USER   CREATE OPERATOR NO  NO
TEST_USER   ALTER SESSION   NO  NO
TEST_USER   CREATE SESSION  NO  NO
TEST_USER   CREATE DATABASE LINK    NO  NO
TEST_USER   CREATE SYNONYM  NO  NO
TEST_USER   CREATE INDEXTYPE    NO  NO
TEST_USER   CREATE MATERIALIZED VIEW    NO  NO
TEST_USER   CREATE TRIGGER  NO  NO
TEST_USER   GLOBAL QUERY REWRITE    NO  NO
TEST_USER   CREATE SEQUENCE NO  NO
TEST_USER   CREATE CLUSTER  NO  NO
TEST_USER   CREATE TYPE NO  NO
TEST_USER   CREATE PROCEDURE    NO  NO
TEST_USER   CREATE VIEW NO  NO
TEST_USER   CREATE TABLE    NO  NO

Error, when tried unlock:

alter user other_user account unlock
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

you have not shown the roles TEST_USER is granted.  Likely they have been granted the DBA role which allows ALTER USER.

see the documentation for details.  This query will show the roles a user has

SELECT * FROM DBA_ROLE_PRIVS where grantee = 'TEST_USER'

of course this requires privileges too, unless you have the CATALOG role, if you select from this table you can alter a user


Using Postgresql logical replication, how do you know that the subscriber is caught up? Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are:

pg_stat_replication

and it's sibling:

pg_stat_subscription

I've read the documentation for these, but they don't say how to know if a replica is actually synced, and interpreting these tables didn't seem obvious to me. Can anybody explain?

I used replication on postgres 10 last year and I had same problem understanding how to monitor the process, as documentation is not clear enough. Anyway I'll try to give you an example as to check if monitoring is going on.

On PUBLISHER side there are few things you can check:
- pg_catalog.pg_publication;
- pg_catalog.pg_publication_tables;
- pg_current_wal_lsn();

I'll create a publication "test_publication" with two tables: t_1 and t_2. I wont cover the needed pre-requisites (user, roles and so on).

test_logical_replication=# create publication test_publication for table t_1, t_2;
CREATE PUBLICATION
test_logical_replication=# select * from pg_catalog.pg_publication;
pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-----------------+----------+--------------+-----------+-----------+-----------
test_publication |       10 | f            | t         | t         | t
(1 row)

test_logical_replication=# select * from pg_publication_tables;
    pubname      | schemaname | tablename
    ------------------+------------+-----------
     test_publication | public     | t_1
     test_publication | public     | t_2
    (2 rows)

On SUBSCRIBER SIDE:

test_logical_replication_subscriber=# create subscription test_subscription CONNECTION 'dbname=test_logical_replication host=XXX user=repuser' PUBLICATION test_publication;
NOTICE:  created replication slot "test_subscription" on publisher
CREATE SUBSCRIPTION

Interesting information are on table pg_catalog.pg_stat_subscription.
Here important columns are:
- received_lsn: Last write-ahead log location received
- last_msg_send_time: send time of last message received from PUBLISHER
- last_msg_receipt_time: Receipt time of last message received from PUBLISHER
- latest_end_lsn: Last write-ahead log location reported to PUBLISHER
- latest_end_time: Time of last write-ahead log location reported to PUBLISHER

You have to check these columns to catch what is happening.
First check if two db are in sync;

PUBLISHER SIDE:

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EB83768     << this is the location on Wal file where we are now, before starting new insert

We can check on SUBSCRIBER that at moment the two database are in sync,because
value returned by function pg_current_wal_lsn() on publisher match the value in
columns received_lsn and latest_end_lsn on subscriber:

test_logical_replication_subscriber=# select received_lsn, latest_end_lsn from pg_catalog.pg_stat_subscription;

received_lsn    | latest_end_lsn
----------------+------------------
 0/8EB83768     | 0/8EB83768

I'll add 4000 rows on table t_1, and see what happens on PUBLISHER:

test_logical_replication=> insert into t_1 select id+1, txt||'--BB' from t_1;
INSERT 0 4000

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4B9D0             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DE78             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0            <<< same value, wal sending has finished
(1 row)

let's look how pg_catalog.pg_stat_subscription values change during replication on SUBSCRIBER:

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |    last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+------------------------------+----------------+-------------------------------
 0/8EC4B9D0   | 2018-12-17 11:39:56.014564+01 | 2018-12-17 11:39:56.07322+01 | 0/8EC4B9D0     | 2018-12-17 11:39:56.014564+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 0/8EC4BA08   | 2018-12-17 11:39:56.737101+01 | 2018-12-17 11:39:56.736303+01 | 0/8EC4BA08     | 2018-12-17 11:39:56.737101+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 0/8EC4DE78   | 2018-12-17 11:40:04.184765+01 | 2018-12-17 11:40:04.183937+01 | 0/8EC4DE78     | 2018-12-17 11:40:04.184765+01
(1 row)

 test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |   last_msg_receipt_time    | latest_end_lsn |        latest_end_time
--------------+-------------------------------+----------------------------+----------------+-------------------------------
 0/8EC4DEB0   | 2018-12-17 11:40:17.153797+01 | 2018-12-17 11:40:17.153+01 | 0/8EC4DEB0     | 2018-12-17 11:40:17.153797+01
(1 row)

As you can see, on subscriber the 4 columes shows how WAL is arriving from publisher and how it is applied. The difference in time in columns last_msg_send_time and last_msg_receipt_time can give information about lag between publisher and subscriber.
In this case the two server are on different subnets on the same datacenter.
Take in count that the two server I used are test servers and ARE NOT perfect in sync between them. (subscriber server has not an ntp server configured at all).

Hope this help. Regards
MarcoP.


Check partition function if list of value exists, if not iterate through list and add new partition value to the partition function? I have an azure sql db with a partitioned table with a partition function on varchar datatype.

In an Etl process I will get a list of partiton values as a parameter that i will the need to:

check partition function if the partition values exist, if not iterate through list of value and add to partition function.
for the list of partition values that is supplied as a parameter, iterate through the $partition function to get the partitionids into a new variable as a comma seperated list
truncate the partitioned table with the command: truncate parttable with partitions (@newvariable)

What I got so far in code is:

-- Table, Partition Function, Partition Scheme

CREATE PARTITION FUNCTION PF_TEXT(varchar(20)) AS RANGE RIGHT FOR VALUES ('A', 'B', 'C', 'D', 'E')

CREATE PARTITION SCHEME PS_TEXT AS PARTITION PF_TEXT ALL TO ([PRIMARY])

CREATE TABLE [dbo].[fact_partitioned](
    [Col1] [int], NOT NULL
    [Col2] [int], NOT NULL
    [TEXT] [varchar(20)] NOT NULL
) ON PS_TEXT ([TEXT])

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

DECLARE @cmd nvarchar(max);
DECLARE @partitions nvarchar(max);
DECLARE @partitionids nvarchar(max);

SET @partitions = 'A','B','C'

-- Code to check Partition Function for @partitions values exist?
-- For @partitions values that don't exist, iterate @partitions values
and add to partition function.

--code to iterate @partitions values over $partition.PF_TEXT(@partition)
add result to @partitionids

SET @cmd = N'TRUNCATE TABLE dbo.pt WITH (PARTITIONS (' + @partitionids + N'));';

PRINT @cmd;

--EXEC sys.sp_executesql @cmd;

SQL Server table partitioning is natively range partitioning. Although you can implement list partitioning (i.e. partitioning columns exactly match boundaries), take care to ensure exact boundary matches to avoid excessive data movement and logging during SPLIT operations. Consider creating a CHECK constraint on the fact table matching the boundary values (e.g. TEXT IN('A','B','C', …).

Below is an example script based on the DDL and sample data in your question, which assumes (at least) a clustered index exists on the fact table. I added some additional boundaries to illustrate new boundaries between existing ones can be introduced during the ETL process that without requiring data movement, assuming partitioning column values match boundaries exactly. See inline comments for details of each step.

The move from staging to the fact table took a couple of seconds in my test. Be sure to update fact table stats afterward.

CREATE PARTITION FUNCTION PF_TEXT(varchar(20))
    AS RANGE RIGHT
    FOR VALUES ('A', 'B', 'C', 'D', 'E', 'M', 'S');
CREATE PARTITION SCHEME PS_TEXT
    AS PARTITION PF_TEXT
    ALL TO ([PRIMARY]);

CREATE TABLE dbo.fact_partitioned(
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    TEXT varchar(20) NOT NULL
        INDEX cdx CLUSTERED ON PS_TEXT (TEXT)
) ON PS_TEXT (TEXT);

--load some example data into fact table
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.fact_partitioned WITH(TABLOCKX) (Col1, Col2, TEXT)
SELECT 1,1,'A'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'B'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'C'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'D'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'E'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'M'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'S'
FROM t1g
WHERE num <= 1000000;
GO

--example ETL objects and data
DROP TABLE IF EXISTS dbo.fact_partitioned_staging;

--staging table must have same indexes as target (aligned)
CREATE TABLE dbo.fact_partitioned_staging(
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    TEXT varchar(20) NOT NULL
        INDEX cdx CLUSTERED ON PS_TEXT (TEXT)
) ON PS_TEXT (TEXT);

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.fact_partitioned_staging WITH(TABLOCKX) (Col1, Col2, TEXT)
--replaces existing partition
SELECT 2,2,'C'
FROM t1g
WHERE num <= 1000000
UNION ALL
--new partion BETWEEN 'E' and 'M'
SELECT 2,2,'G'
FROM t1g
WHERE num <= 1000000
--new partion AFTER 'S'
UNION ALL
SELECT 2,2,'X'
FROM t1g
WHERE num <= 1000000;
GO

--dynamic list partitioning load
SET XACT_ABORT ON; --best practice with explict transactions
DECLARE @SQL nvarchar(MAX);
BEGIN TRY

    BEGIN TRAN;

    --get distinct incremental load TEXT values from staging
    DECLARE @incremental_load_boundaries TABLE(
          incremental_load_boundary varchar(20) NOT NULL PRIMARY KEY
        );
    INSERT INTO @incremental_load_boundaries(incremental_load_boundary)
        SELECT DISTINCT TEXT
        FROM dbo.fact_partitioned_staging;

    --recreate new partition function/scheme with existing partition boundaries plus new distinct TEXT values
    IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE name = N'PS_TEXT_STAGING')
        DROP PARTITION SCHEME PS_TEXT_STAGING;
    IF EXISTS(SELECT 1 FROM sys.partition_functions WHERE name = N'PF_TEXT_STAGING')
        DROP PARTITION FUNCTION PF_TEXT_STAGING;
    SELECT @SQL =
        N'CREATE PARTITION FUNCTION PF_TEXT_STAGING(varchar(20)) AS RANGE RIGHT FOR VALUES ('
        + STRING_AGG(QUOTENAME(boundary,''''),',')
        + N');'
    FROM (
        --existing target table boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT')
        UNION
        --staging table boundaries
        SELECT incremental_load_boundary
        FROM @incremental_load_boundaries
        ) AS boundaries;
    PRINT @SQL;
    EXECUTE sp_executesql @sql;
    CREATE PARTITION SCHEME PS_TEXT_STAGING AS PARTITION PF_TEXT_STAGING ALL TO ([PRIMARY]);

    --partition staging table using new partition scheme with end state boundaries
    CREATE CLUSTERED INDEX cdx ON dbo.fact_partitioned_staging(TEXT)
        WITH(DROP_EXISTING=ON)
        ON PS_TEXT_STAGING(TEXT);

    --SPLIT existing partition function to add new boundaries
    --no data movement needed when existing partition column values exactly match existing boundaries
    SELECT @SQL = STRING_AGG(
        'ALTER PARTITION SCHEME PS_TEXT NEXT USED [PRIMARY];ALTER PARTITION FUNCTION PF_TEXT() SPLIT RANGE('
        + QUOTENAME(new_boundaries.boundary, '''')
        + N')'
        , ';')
    FROM (
        --existing target table boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT_STAGING')
        EXCEPT
        --all boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT')
        ) AS new_boundaries
    PRINT @SQL;
    EXECUTE sp_executesql @sql;

    --truncate partitions to be reloaded
    SELECT @SQL =
        N'TRUNCATE TABLE dbo.fact_partitioned WITH(PARTITIONS('
        + STRING_AGG(CAST(partition_number AS varchar(10)),',')
        + N'));'
    FROM (
        SELECT DISTINCT $PARTITION.PF_TEXT(incremental_load_boundary) AS partition_number
        FROM @incremental_load_boundaries
        ) AS existing_boundaries;
    PRINT @SQL
    EXECUTE sp_executesql @sql;

    --switch in incremental load partitions
    SELECT @SQL = STRING_AGG(
        N'ALTER TABLE dbo.fact_partitioned_staging SWITCH PARTITION '
        + CAST(partition_number AS varchar(10))
        + N' TO dbo.fact_partitioned PARTITION '
        + CAST(partition_number AS varchar(10))
        , ';')
        + N';'
    FROM (
        SELECT DISTINCT $PARTITION.PF_TEXT(incremental_load_boundary) AS partition_number
        FROM @incremental_load_boundaries
        ) AS existing_boundaries;
    PRINT @SQL
    EXECUTE sp_executesql @sql;

    --assert staging table is now empty
    IF EXISTS(SELECT 1 FROM dbo.fact_partitioned_staging)
        RAISERROR('Assertion failed: staging table is not empty',16,1);

    COMMIT;

END TRY
BEGIN CATCH
    IF @@ROWCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO