How to handle ~1k inserts per second Assuming one has about 1k requests per second that require an insert.

Now, there are a lot of answers to this on the internet... but they're technically wrong in this specific context. Yes, pretty much any RDBMS can handle 1k inserts per second on standard hardware but IF AND ONLY IF you drop ACID guarantees. It's surprising how many terrible answers there are on the internet. Such as "you can always scale up CPU and RAM" which is supposed to give you more inserts per second but that's not how it works. The limiting factor is disk speed or more precise: how many transactions you can actually flush/sync to disk. And this is the tricky bit.

On decent "commodity hardware" (unless you invest into high performance SSDs) this is about what you can expect:

SQLite: 30 inserts/s
MySQL: 80 inserts/s

This is the rate you can insert while maintaining ACID guarantees. This essentially means that if you have a forum with 100 posts per second... you can't handle that with such a setup.

Read requests aren't the problem. You can have many thousands of read requests per second no problem but write requests are usually <100 per second.

Thus, this question is specifically aimed at how one can handle 1k inserts per second while still maintaining ACID guarantees - assuming that a single node can handle about 80 transactions per second.

One way I could see this working is if you buffer inserts somewhere in your application logic and submit them as larger transactions to the database (while keeping clients waiting until the transaction is over) which should work fine if you need single inserts only although it complicates the application logic quite a bit.

My simple RAID 10 array running on old hardware with 300GB SAS disks can handle 200-300 inserts per second without any trouble; this is with SQL Server running on a VM, with a lot of other VMs running simultaneously.

With just a consumer grade SSD, you can expect 3,000 to 5,000 or more 4K I/Os per second.

What exactly is your question?


Multi tenancy by schema - Deny access to another tenants In a multi tenant environment, where the multiple tenants are in the same database and a schema is used to separate each one of them, how can I create a database user (MS SQL SERVER) for each schema, where the user has only access to his schema?

I know that I can do something like the code bellow, but I would like that by default a user has no access to any other tenant.

DENY SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
GRANT SELECT ON schema::[safe_schema] TO [user_name]

Thanks

The solution is to add the Grant permissions and do not add any read or write role to the user.

GRANT SELECT ON SCHEMA::[SCHEMA] TO [USER]

GRANT INSERT ON SCHEMA::[SCHEMA] TO [USER]

GRANT UPDATE ON SCHEMA::[SCHEMA] TO [USER]

GRANT DELETE ON SCHEMA::[SCHEMA] TO [USER]


Large Table handling We are having table that contains 6,616,711 (6.6 million) records in it. I want to know, How to manage this table? If there is SO question similar to this please give me link. Having following concern, too.

How to manage large database?
What is best system/hardware configuration for this type of scenario?
Partitioning this table will help us?

Partitioning a table will help you in maintaining.

Here are some useful articles on maintaining a large table:

Handling Large SQL Server Tables with Data Partitioning

The only downside is that it only exists in the Enterprise and Developer editions.

Simplify Database Maintenance with Table Partitions

And more info on hardware, horizontal, and vertical partitioning:

Partitioning

  Partitioning a database improves performance and simplifies
  maintenance. By splitting a large table into smaller, individual
  tables, queries that access only a fraction of the data can run faster
  because there is less data to scan. Maintenance tasks, such as
  rebuilding indexes or backing up a table, can run more quickly.

  Partitioning can be achieved without splitting tables by physically
  putting tables on individual disk drives. Putting a table on one
  physical drive and related tables on a separate drive can improve
  query performance because, when queries that involve joins between the
  tables are run, multiple disk heads read data at the same time. SQL
  Server filegroups can be used to specify on which disks to put the
  tables.

Also, there are a lot of useful answers in this thread

Hope this helps


Print to console as SQL executes When executing multiple SQL commands, such as in the pgAdmin SQL Editor, how can I print some strings to the console (output pane in pgAdmin). I want to monitor execution.

UPDATE x
SET a = b
WHERE a IS NULL
;

Print to console --> Finished x.

UPDATE y
SET a = b
WHERE a IS NULL
;

Print to console --> Finished y.

My question is the same as this StackOverflow Question. Or, as Craig Ringer interpreted that Question:

  Do you wish to observe SQL queries that are run by PgAdmin-III by printing them to standard error, viewed by launching PgAdmin-III on a cmd.exe interactive console?

I have seen remarks about \echo 'some_text'. But that gives me the error:

ERROR: syntax error at or near "\"
SQL state: 42601

The only way I have found is through a stored procedure

CREATE OR REPLACE FUNCTION print_notice(msg text)
  RETURNS integer AS
$$
DECLARE
BEGIN
    RAISE NOTICE USING MESSAGE = msg;
    RETURN null;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

So, executing SELECT print_notice('Foo ' || someText || ' !');

Will produce (for example) :

NOTICE:  Foo whatever !
query result with 1 row discarded.

I'm not sure if this can be done without the SELECT and the query ... row discarded message. But it works.


Sid's for service accounts in alwayson When creating new maintenance jobs for an alwayson setup with new service accounts, should we ensure that the sid's on both the nodes should be the same? I created a new login with the new domain service accounts and scheduled Olla Hallengren's scripts. I just wanted to check the sid of the service account  on both the nodes. They are different. Is this something which i should worry about?Thanks!

If they are Windows domain accounts/groups, you shouldn't have to worry about it as they should be identical on both nodes.

The orphaned user issue and SID syncing across both nodes is only applicable to SQL authorization logins.  With SQL authentication, you will have to sync the SIDs as, after a failover, the SIDs wont line up between the server level SID and db level SID.


How to get line that causes error when loading from csv to MySQL I'm trying to load data from CSV file, but occurs error

Error Code: 1366. Incorrect integer value: 'abcdxxx' for column 'col' at row 123545.

I Have file with multiple breaklines CRLF's in content, the line separator is LF, text tools cannot find returned string, so I suppose there is problem with charset. Do exists posibility to have returned values(line) from file that caused error server returned? I cannot find values that caused error.

Errors occured because of unescaped backshlashes. Values preceeded with \ were inrepereted as ascii signs, but they weren't. To avoid this error backslash - \ should be escaped by additional backslash - \ so occurences of backslashes should be changed to double backslashes - \\ .


SQL Server passing variable to a string in a stored proc I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for  ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says:

  Incorrect syntax near '+'

Is this possible ?

I need to see something like:

  Investigation is pending for 1234

My query:

    EXEC sp_wf_create_notification
        @processid,
        @vactivityid,
        @vstepid,
        1,
        @vidColumn,
        @vidColumnTable,
        @investigationidout,
        @owner,
        'Pending Investigation',
        'Investigation is pending for  ['+ @investigationidout +'] '
        @nextstepurl,
        @vstatus OUTPUT,
        @verror OUTPUT

The parameters supplied to a procedure must be constants or variables.
A function or the output of a function or an expression cannot be used.

You would have to first set a variable with the required expression:

declare @investigation varchar(100);
set @investigation = 'Investigation is pending for  ['+ @investigationidout +'] ';

EXEC sp_wf_create_notification
    @processid,
    @vactivityid,
    @vstepid,
    1,
    @vidColumn,
    @vidColumnTable,
    @investigationidout,
    @owner,
    'Pending Investigation',
    @investigation
    @nextstepurl,
    @vstatus OUTPUT,
    @verror OUTPUT
;

For OUTPUT variables, don't forget to add OUTPUT after its name.

See MSDN Specify Parameters:

  The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.


Why Won't a Database Backup if the Log File is Full? I've got a simple backup script for a database in the full recovery model on SQL Server 2008 R2:

backup database livendb to disk = '\\ehsjmaydb01\Data\livendb.bak' with init, format
backup log livendb to disk =  '\\ehsjmaydb01\Data\livendb_log.tran' with init, format

We turned on a new ETL script over the weekend which absolutely hosed my poor server. The transaction log filled up and TempDB filled up.

Looking at the history of my backup job, it failed with this message:

  The transaction log for database 'livendb' is full.
  To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
  [SQLSTATE 42000] (Error 9002)  BACKUP DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013).  The step failed.

When I came in this morning, I was able to back up the log first, then run a DB backup, and that's working just fine.

Question: Why does the database backup process require space in the log file?

BACKUP DATABASE is terminating abnormally

  Question: Why does the Database backup process require space in the
  log file?

Every data backup (full/differential) as the first step does a checkpoint. This is done for be able to grab as many as possible fresh data from disk.

Every checkpoint operation writes to the log about itself (at least 2 log records)

If your log is full even impossibility of writing checkpoint operation into log can fail your backup.

At the end of backup every full backup resets differential base, and this is also logged.

And there are other log writes in between of this two, you can see them using sys.fn_dblog after doing successful backup.

Here you can see the explanation of checkpoint and differential base:

Database Checkpoints (SQL Server)

How do checkpoints work and what gets logged

Base of a Differential Backup


Mysql 8, Query on user table garbles terminal. Is this an Installation issue? I have Mytsql 8.0.1 Community Edition installed on Debian 10.1

Preparing to restore some dump files, I'm attempting to get a remote connection and cannot

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111 "Connection refused")

I was attempting to look at users and see what was there and got this:

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| root             | tuivhk    |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| root             | %         | $A$005$sH
~m%:G'{a*wGdR.BP9eON1eqM95YlN.1CJB6YeiQbiQ7l33i.wZuD |
| root             | tuivhk    | $A$005$=BLI-+,?)~5,Zh0QZAK-aX/a+N++a+/8BS|+IX+++1+gO/0++Y60y_8ZA |
| +y_-+.i+f-_che+a | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| +y_-+._e__i-+    | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| +y_-+._y_        | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| _--+             | +-ca+h-_+ | $A$005$d(+?aM^81^GZO+|Mg++Zdga4_Da-|UefKWXgyhBB++.DD+RYYDMDG__-+EA |
+------------------+-----------+------------------------------------------------------------------------+
6 _-+_ i+ _e+ (0.00 _ec)

+y_-+>

I could use some assistance figuring out what went wrong here.

Before I had this issue, I had to reinitialize my instance due to screwing up the mysql database when restoring backup dumps.

I fixed this issue by reinitializing the database

sudo service mysql stop
sudo mv /var/lib/mysql old_mysql
mkdir mysql; chown -R mysql.mysql mysql/
mysqld --initialize

Right here, here is where there was a problem. During the initial install I choose the mysql password rather than the new more secure authentication method. I wasn't sure how that would impact restoring dumps from a MySQL 5.7 instance.

I fixed the issue this way:

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password  BY 'password'

I did this for every row that had a 'root'@ user and this fixed the issue.

Obviously, the secured method has some control characters in the authentication_string that should not be directly output to terminal.


Which workarounds exists to missing relative path feature of SQLCMD :r? I'm not the first to notice the lack of this feature.
But I wonder if there are some useful workarounds.
I want to deploy a main script and some depended scripts in the same folder.
BTW Is there a ticket on Microsoft Connect for this?
Edit:
Locking at this, it seems equivalent to the question if a script running in sqlcmd mode can determine its path in the file system.
2nd Edit:
From SO I found a way to pass the path of the scripts into sqlcmd
sqlcmd -S myServer -d myDB -U user -P password -i file1.sql -v p1="D:\myscripts"

And I can verify that it is passed to File1.sql
PRINT '$(p1)'

:setvar path1 $(p1) + '\file1.sql'

:r $(path1)

But I don't find the right way to concat the path and the filename in the second line.
3rd Edit:
I found the solution to concat path and filename in here.
PRINT '$(p1)'

:r $(p1)\file1.sql

Are there still better ways?

Microsoft isn't improving sqlcmd - all their bets are on the PowerShell horse. When you're doing automated deployments like you describe, PoSH will give you more options for all kinds of dependency stuff. I'd focus there.


MYSQL query to find records between two date range I am working in a web application using PHP and MySQL.

I have two tables

Employee

employee_id     email_id
3             tl1@jasmine.com
4             agent1@jasmine.com
5             agent2@jasmine.com
6             om1@jasmine.com
7             tl2@jasmine.com

Coaching

coaching_id     emp_id  start_date  end_date
 1               4      2014-05-01  2014-10-02
 2               5      2014-12-18  2015-01-22

I want a query to find out the employees who are not enrolled for any coaching program between a specified date range such as Start Date "2015-01-22" and End Date "2015-03-12".

Now I am using query like this,

SELECT employee_id
FROM employee
WHERE sup_id = 3
AND employee_id NOT IN
(
    SELECT emp_id
    FROM (coaching)
    WHERE (start_date NOT BETWEEN "2014-12-26" AND "2015-01-30")
    AND (end_date NOT BETWEEN "2014-12-26" AND "2015-01-30")

)

I have a logic, " If any employee already joined a coaching program at given start_date and end_date, he is not eligible for any coaching program.

For Example : If the start date is '2014-12-26' and end date is '2015-01-30'. Then the employee with id 5 is not eligible for this coaching program as he is now on another coaching activity whose start date is '2014-12-18' and end date is '2015-01-22'. But employee with id 4 is eligible as well as all other employees who are not in any coaching activity

Where is the issue in my query

Thanks

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends).  Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
  FROM employee e
  LEFT JOIN coaching c ON c.employee_id = e.id
                      AND c.end_date >= '2014-12-26'
                      AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4


Foreign key in one table have high read performance or another table with two primary key? I want to know which one is better for high read performance. Foreign key in one table have high read performance or another table with composite key?

In Figure1 "idInvoice" is nullable some Sanads come without Factor

Q1- For example I want to list all Notes Invoice or Find is there any Invoice for special Note.
Q2- Which one is better if "idInvoice" can not be null.

Figure1

Table Invoice:
idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:
idNote //Primary key
type
liable
credit
idInvoice // Foreign key (Nullable)

OR

Figure 2

Table Invoice:
idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:
idNote //Primary key
type
liable
credit

Table InvoiceNote:
idNote //composite key - Foreign key
idInvoice //composite key - Foreign key

These two schemas represent different business rules. The first, with idFactor as a FK in Sanad, allows each Sanad to be connected to at most one Factor (if the foreign key column is nullable) or exactly one Factor (not nullable). The second, with a separate table, allows a Sanad to connect to zero, one or many Factors. Of course the second model is a superset of the first and can be limited to zero-or-one. There is no trivial way to do this in SQL Server, however. It will required additional constraints and triggers, meaning extra programing, debugging and maintenance.

You should implement the model which matches your business rules. This will force the data to be correct. It also self-documents the intent of the application. Sufficient performance can be achieved through sensible SQL and indexes.

To answer the actual question, it is likely to vary according to the ratio of reads to writes and whether Sanad field1, field2 etc. are returned. In schema 2 each row inserted in the intersection table will require two FKs to be checked i.e. two table reads. In schema 1 there will be 1 FK read. To check only the existence of a Sanad/Factor connection may be faster in schema 2 because the rows are shorter, there will be more rows per page and hence a better chance that the required page is in memory. This only applies if there is a small working set of "hot" rows. To read the data columns in scenario 2, however, will need reads of both interesction and Sanad tables; double to IO.

So, if working set size is bigger than RAM, and the vast majority of reads only check for existence of a link, and there are relatively few writes, and you're confident to do the extra programing to ensure data integrity, or you do actually have a many-to-many relationship, go with schema 2, otherwise stick with schema 1.


How to quickly increase column size in Oracle? In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command

alter table shipment modify (description varchar2(21) );

but it takes to much time. Is there any other (faster) way to increase column size?

Your two options are (depending on the use case):

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8753545300346585555

https://asktom.oracle.com/pls/asktom/asktom.search?tag=best-way-to-change-the-datatype-of-a-column-in-a-table-with-20-million


Is it possible to generate table with generated data without manual INSERT statements? Is it possible to use SQLCMD in SQL Server 2008 to create a table and generate data and insert into the table, without manually typing the INSERT statement for each row?

Example:

Suppose there are 10 weeks (1, 2, 3 ... 10) and 2 timings (11 am, 12 pm) and 5 venues (Room 1, Room 2 ... Room 5). Thus there will be a total of 10 x 2 x 5 = 100 rows.

So, is it possible to have a SQL statement that will generate these 100 rows when given the 3 data parameters (week, timing and venue)?

+--------------------------------+
| Week    |   Time   |   Venue   |
+--------------------------------+
| 1       |  11 am   |  Room 1   |
| 1       |  12 pm   |  Room 1   |
| 1       |  11 am   |  Room 1   |
|                                |
| 10      |  12 pm   |  Room 5   |
+--------------------------------+

You can use 3 CTE expressions to generate the data, then cartesian product them together.

Test table:

create table calendar
(
week integer,
time varchar(20),
venue varchar(20)
);

Weeks:

SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10;

Venues:

SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5;

Times:

SELECT '11 am' AS t
UNION
SELECT '12 pm' AS t;

Put them all together to make a test query:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;

Convert to an INSERT statement:

WITH weeks
AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM weeks WHERE n < 10
),
venues AS (
SELECT 1 AS v
UNION ALL
SELECT v+1 FROM venues WHERE v < 5
),
times AS (
  SELECT '11 am' AS t
  UNION
  SELECT '12 pm' AS t
)
INSERT INTO calendar (week, time, venue)
SELECT n as week, t as time, 'Room '+CAST(v AS VARCHAR) as venue
FROM weeks, venues,times
;


Determining Cardinality in a Logical Model How should one determine Cardinality in a Logical Model ?
Should it be based on how the rows of an entity relate to another entity or should we consider the natural relationship between the entities i.e. conceptual relationship between the entities ?
Example:  If I have an entity Course and an entity Course Type, what would be the cardinality ? Each course can have only one course type. For example, Bachelor of Arts is a course of course type Bachelors and Master of Science is of course type Masters
If I have Course Type as part of Course entity, then Course Type would only contain list of valid course types and it would be "many-to-one" (non-identifying) as there are many courses which will could 1 course type.
On the other hand if I model it in such a way that Course Type entity has Course ID (foreign Key) and Course Type , then the relationships between Course and Course Type is "one-to-one" (identifying).
Basically what I am trying to understand is, which one the following is right ?
each course has one course type  OR  many courses have one course type
How should one make this decision ? Are there any guidelines ?
P.S. : I am a beginner and using Oracle Data Modeler

It should be based on logic. You should be able to express constraints in natural language. ERDs are great only if you already grok how to automatically verbalize diagrams. If not yet, then use plain text editor, it is better and more powerful than ERD.
For this example, starting with two predicates

Course type TYP exists. ctype {TYP}
Course CRS exists. course {CRS}

here are a few options based on constraints:

For each course, that course is of exactly one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS, TYP}
    PK {CRS}
    FK {TYP} REFERENCES ctype {TYP}

For each course, that course may be of more than one course type;
for each course type, exactly one course is of that type.
ctype {TYP, CRS}
   PK {TYP}
   FK {CRS} REFERENCES course {CRS}

course {CRS}
    PK {CRS}

For each course, that course is of exactly one course type;
for each course type, exactly one course is of that type.
course {CRS, TYP}
    PK {CRS}
    AK {TYP}

For each course, that course is of at most one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course may be of more than one course type;
for each course type, more than one course may be of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS, TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course may be of more than one course type;
for each course type, at most one course is of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

For each course, that course is of at most one course type;
for each course type, at most one course is of that type.
ctype {TYP}
   PK {TYP}

course {CRS}
    PK {CRS}

course_ctype {CRS, TYP}
          PK {CRS}
          AK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
FK = Foreign Key

In general, the constraint is verbalized as:
For each A, that A {is | may be} of {exactly one | more than one | at most one} B;
for each B, {exactly one | more than one | at most one} A {is | may be} of that B.
Term                        Range
---------------------------------
is of     exactly one       [1]
is of     at most one       [0,1]
may be of more than one     [0,*]


GRANT privileges to table elements What happens if I GRANT priveleges (for example SELECT) ON a table TO some group role? What privileges will the table's elements have in this case: indexes, sequences, columns? Should I explicitly GRANT privileges to them if I REVOKE ALL ON SCHEMA public FROM GROUP role and FROM PUBLIC previously?

Sequences are separate objects with separate privileges.

  Granting permission on a table does not automatically extend
  permissions to any sequences used by the table, including sequences
  tied to SERIAL columns. Permissions on sequences must be set
  separately.

Indexes belong to the table. To DROP or CREATE an index you must be the owner of the table (or superuser).

Columns go with table privileges. Granting privileges on the table grants applicable privileges to all columns automatically.

For DDL commands (ALTER TABLE ...) you must own the table again.

Since PostgreSQL 9.1 there are column level DML-grants additionally. But:

  A user may perform SELECT, INSERT, etc. on a column if he holds that
  privilege for either the specific column or its whole table. Granting
  the privilege at the table level and then revoking it for one column
  will not do what you might wish: the table-level grant is unaffected
  by a column-level operation.

More details in the manual about GRANT, where the quotes are from.


PostgreSQL doesn't use all the RAM it could I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config:

# <snip> the default config above

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 4GB
max_connections = 200

autovacuum = on
log_autovacuum_min_duration = 10000
autovacuum_max_workers = 5
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 25
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1

#log_statement='mod'
#log_statement='all'

logging_collector = on

Yet, query performance is very bad at times of high load. At those same times, the total RAM usage on the server is 4GB (out of 16GB available).

I can't help to think that these two issues are related. And that performance would increase if pg would only utilize more of the available RAM.

The server is Intel(R) Xeon(R) CPU E3-1240 v3 @ 3.40GHz, 16GB RAM. We have 2 disks (WDC WD1003FBYZ-0) but no RAID. The DB with performance issues is split between these disks using tablespaces. OS is Debian 7.5.

So, anything obvious I have missed? Am I barking up the wrong tree? Or is there truly something fishy here?

PostgreSQL relies on the operating system's disk cache for most caching. This cache is usually reported as "free" RAM by most tools, because modern operating systems use all but a little bit of the currently-free RAM for disk cache.

This is normal.

To confirm, use a better tool that shows buffers/cache separately from truly free memory. On Linux, free -m will show you more info.

Also note that for most systems the accounting for shared memory means that the memory use reported for PostgreSQL processes is pretty bogus. They tend to count all the shared memory touched by a process as used by that process, thus counting each shared memory page many times over. Or they tend to ignore shared memory entirely, not counting it at all. The best way to estimate PostgreSQL's actual memory use is to get the unshared memory use for each process, add them up, and add the shared_buffers size to the total.

BTW, shared_buffers is a second level cache. Any clean buffers in there probably duplicate what's in the OS's disk cache. So there's a system and workload specific size that's optimal. Too small and you start struggling to find free pages to read in, or forcing dirty buffers out too early for efficient write combining. Too big and you double-cache too many clean pages from the OS disk cache.


How do I write portable SQL that refers to a linked server? I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following:

INSERT INTO [TableName]
(...Columns...)
SELECT ...Columns...
FROM [ServerName\InstanceName].[Catalogue].[dbo].[TableName]
WHERE TableNameID = @TableNameID

This procedure exists in my Development environment, Test environment and Live environment.

The problem is that each copy of the procedure is subtly different because the server names are different for each environment. This makes managing deployment of script updates troublesome.

Is there a way to make the procedure portable so that each environment can run identical versions of it?

If not, is there anything I can do to make script deployment less prone to mistakes/errors?

The name of your linked server doesn't have to be the server's name.  You can use a generic name.

EXEC master.dbo.sp_addlinkedserver
    @server = N'COMMONNAME',
    @srvproduct=N'MSDASQL',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;',
    @catalog=N'database1'

Set up the linked server on each environment with the same name, but actually point them at different servers.


Merging two Select queries with different where statements I have this query below in which I am trying to get results for the month and year to date (account balance).

SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
UNION
SELECT  T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'YTD Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
ORDER BY T3.AcctCode

The results are as below:

The problem I have is that the Balance and Year to Date balance columns are stacked on on top of the other. How can I modify this query to have 4 columns, i.e: AcctCode, AcctName, Balance, YTD Balance? Using SQL Server 2008

Since the two parts are identical except for the WHERE conditions where the first is just a restriction of the second, you can use one query, with the 2nd condition and an altered condition for the 1st sum, i.e. a CASE expression:

SELECT T3.AcctCode, T3.AcctName,
       SUM(CASE WHEN T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
             THEN T1.Debit - T1.Credit
             ELSE 0 END)* -1          AS Balance,
       SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance]

FROM OJDT T0
  INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
  INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
  INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
  AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName ;


Restore Database SQL Server 2012 I'm a newbie when it comes to SQL Server, it would be helpful if someone could point me in the right direction.

We have a database file that is called main-db.bak.

This is the database file that we have cloned as it contains all the main things we need in the process of constructing new websites for our clients. Is it possible to do the following:

Have a copy of the database on the same SQL Server Instance
Create a new empty database
Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?

Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?

Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?

Lets take your questions one by one :

  Is it possible to do the following:Have a copy of the database on the same SQL Server Instance
  Yes it is possible. You just have to restore the database main-db.bak with move.

e.g. Provided you dont have secondary data files, below will work

-- find logical names

restore filelistonly from disk = 'path\main-db.bak'
go
restore database main_db_copy
from disk = 'path\main-db.bak'
with recovery, stats = 10,
-- below logical data and log file names will be found from the filelistonly mentioned above.
move 'logicalDataFileName' to 'PhysicalPath\main-db_data.mdf',
move 'logicalLogFileName' to 'PhysicalPath\main-db_log.ldf'

  Create a new empty database

No need to first create an empty database, as the restore with move will create it.

  Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?

Same as above. As restore with move will not over write unless you use restore with REPLACE explicitly to overwrite.

  Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?

Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use restore database to restore it.

  Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?

If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.

You can fully automate the restore process. Some examples can be found here and here.


Is it possible to check all constraints in a MySQL database? MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database.

Is it possible to check an entire MySQL database, table by table, row by row, to ensure that all constraints are satisfied?

Long ago I wrote a tool called oak-apply-ri. It is part of the openark-kit, which is generally well accepted. I'm beginning with the disclaimer that this tool is less popular, and I don't have much input about it (other than my own).

Nevertheless, it allows you to specify two tables that share a FK relationship, and it will verify whether the constraint holds.

You can ask it to actually resolve the differences via --action=delete or --action=setnull.

If you are suspicious, you may ask it to --print-only in which case it will not apply any changes to your database; instead it will print out the statements it would like to invoke on the database.

I'm happy to get any feedback on this.


Can I move rows between partitions by updating the partition key? I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.

The question:
Can you move rows of data within a partitioned table from one partition to another by simply updating the partition column so that it crosses the partition boundary?

For example, if I have a table that has a partition key:

CREATE TABLE SampleTable
(
    SampleID INT PRIMARY KEY,
    SampleResults VARCHAR(100) NOT NULL,
)

With the partition function that maps to the primary key:

CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS
RANGE LEFT FOR VALUES (10000, 20000);

Can I move a row from the first partition to the third partition by changing the SampleID from 1 to (say) 500,000?

Note: I'm tagging this as both sql server 2005 and 2008, since they both support partitioning.  Do they handle it differently?

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.


Backing up an Oracle 10G Express on Windows 2000 Server Oracle 10G Express installed on Windows 2000 Server Enterprise Edition.

I am trying to automate backups and on Oracle's website I have found the following documentation.

http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/backrest.htm

Activate Archive Log

SQLPLUS -> CONNECT SYS/password AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Backup the Database

Then using the backup.bat file provided by Oracle: D:\oraclexe\app\oracle\product\10.2.0\server\BIN\BACKUP.BAT, I can backup the database either manually or with Scheduled tasks

Using a test server, I have been able to backup and restore the database using the backup.bat and restore.bat files provided.  I have not seen anything however about recycling those backups.

Questions:

Does Oracle provide a script to cleanup backup files that are no longer needed?
or What can I do to avoid filling up the D: drive with backup files?
Is there a better method to backup Oracle 10G Express?

Thank you

I don't have the 10g version of Express Edition installed, but if it is like the 11g version, then your first question is answered in the comments section of the Backup.bat file itself.

For 11g this means the comments indicate that No cleanup script is needed because the script automatically keeps two backups in the Flash Recovery Area.  Whether there is a better method or not will depend on what your recovery requirements are.


How to decrypt dtsx package? I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so:

    Use SSISDB

    if object_id('tempdb..#package') is not null begin drop table #package end

    create table #package ( package varbinary(max))

    insert into #package (package) EXEC SSISDB.catalog.get_project 'my_folder','my_project'

    select * from #package

this returns a very long varbinary string which starts with 0x

Problem: when I try and convert the varbinary to nvarchar, it is still the same 0x... varbinary. I think it is encrypted.

Question: How can I decrypt these varbinary strings and convert them to a searchable format?

I think I need a key, but I'm not sure where those are stored.

Update:

So the package is not encrypted. As Billinkc said, it is zipped as a .ispac file.

These .ispac files first have to be exported from the database, then extracted into their components, which can be one or more .dtsx packages.

Microsoft provide a powershell script that will export every .ispac from your database:

https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f

<#
.SYNOPSIS
    Import folders and projects from local file system to SSIS catalog.
.DESCRIPTION
    Import SSIS projects from $ProjectFilePath. The folders under $ProjectFilePath
    will be imported as folders in the SSIS catalog, with all ispac files under that
    folder imported accordingly.

    The script will connect to the local SQL Server instance. It will first
    drop the SSISDB catalog if exists and create a new catalog with a fixed secret.
.EXAMPLE
    .\CatalogImport
#>

# Variables
$ProjectFilePath = "C:\SSIS"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

Write-Host "Removing previous catalog ..."

# Drop the existing catalog if it exists
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() }

Write-Host "Creating new SSISDB Catalog ..."

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1")
$catalog.Create()

write-host "Enumerating all folders..."

$folders = ls -Path $ProjectFilePath -Directory

if ($folders.Count -gt 0)
{
    foreach ($filefolder in $folders)
    {
        Write-Host "Creating Folder " $filefolder.Name " ..."

        # Create a new folder
        $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $filefolder.Name, "Folder description")
        $folder.Create()

        $projects = ls -Path $filefolder.FullName -File -Filter *.ispac
        if ($projects.Count -gt 0)
        {
            foreach($projectfile in $projects)
            {
                $projectfilename = $projectfile.Name.Replace(".ispac", "")
                Write-Host "Deploying " $projectfilename " project ..."

                # Read the project file, and deploy it to the folder
                [byte[]] $projectFileContent = [System.IO.File]::ReadAllBytes($projectfile.FullName)
                $folder.DeployProject($projectfilename, $projectFileContent)
            }
        }
    }
}

Write-Host "All done."

Fill out the connection string, and all your packages will get saved to C:\SSIS

From here, the packages need to be extracted, which I did using python:

import os
import zipfile
import pandas as pd
from sqlalchemy import create_engine
import urllib

conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=NynyaBeeswax;'
r'Database=Really_Cool_DB;'
r'Trusted_Connection=Yes;')

quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn  = engine = create_engine('mssql+pyodbc:///?`odbc_connect{}'.format(quoted_conn_str)).execution_options(autocommit=True)`

directory = 'C:\\SSIS\\'
directory_to_extract_to = 'D:\\Defeintely_not_local\\SSIS_Packages\\'

for subdir, dirs, files in os.walk(directory):
    for filename in files:
        if filename.endswith(".ispac"):
            #print(filename)
            #print (subdir)
            #print(dirs)
            folder = subdir.split('\\')[2]

            target_directory = directory_to_extract_to + folder

+ '\\'

            with zipfile.ZipFile(subdir + '\\' + filename, 'r')

as zip_ref:
                zip_ref.extractall(target_directory)
                #print(target_directory)
        else:
            continue

for subdir, dirs, files in os.walk(directory_to_extract_to):
    for file in files:

        if file.endswith('.dtsx'):

            folder = subdir.split('\\')[5]

            sql = """insert into pkg_xml_data (subdir,

package_name, bulkcolumn, loaddate)
            SELECT '{}','{}', CONVERT(XML, BulkColumn) AS

BulkColumn, GETDATE()
            FROM OPENROWSET(BULK '{}\\{}', SINGLE_BLOB) AS

x;""".format(folder,file, subdir, file)

        #print(filename)
            print(sql)
            #engine.execute(sql)


Get all (non repeated) distribuidores with their paises given a producto_solicitud I have this schema:

And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way:

SELECT
    nomencladores.fabricante_distribuidor.*,
  nomencladores.pais.*
FROM
    nomencladores.fabricante_distribuidor,
    nomencladores.pais
LEFT JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
LEFT JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1

But get this error:

[Err] ERROR:  invalid reference to FROM-clause entry for table "fabricante_distribuidor"
LINE 8:> ...r_producto_solicitud.fabricante_distribuidor_id = nomenclado...
                                 ^
HINT:  There is an entry for table "fabricante_distribuidor", but it cannot be referenced from this part of the query.

I've tried also this one:

SELECT
    nomencladores.fabricante_distribuidor.*
FROM
    nomencladores.fabricante_distribuidor
JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1

But this one return repeated results, any help?

I'm using PostgreSQL but assume that will be pretty much closed to any SQL statement.

2nd query:
Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to.

1st query:
An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and distribuidor_producto_solicitud - that happens before the result of this operation is joined to fabricante. Per documentation:

  In the absence of parentheses, JOINs nest left-to-right. In any case
  JOIN binds more tightly than the commas separating FROM-list items.

Bold emphasis mine.

The unconditional CROSS JOIN (comma-notation) between fabricante and the rest is a mistake to begin with. You probably want:

SELECT DISTINCT f.*, p.*
FROM   negocio.distribuidor_producto_solicitud d
JOIN   nomencladores.fabricante_distribuidor   f ON f.id = d.fabricante_distribuidor_id
LEFT   JOIN nomencladores.pais_distribuidor_producto_solicitud pd
                                                 ON pd.distribuidor_producto_solicitud_id = d.id
JOIN   nomencladores.pais                      p ON p.id = pd.pais.id
WHERE  d.producto_solicitud_id = 1;

Gives you one row for every combination of (fabricante, pais). The LEFT JOIN is there to include fabricantes that have no pais attached.

You need the DISTINCT, because there can be any numbers of rows in distributor for each fabricante.

With your verbose naming convention I suggest to use table aliases to make the query more readable.


Define MYSQL index with condition I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When action = 1, ref_transaction_id will be 0
When action > 1 (other values are 2,3,4,5), ref_transaction_id will be a non-zero integer. It actually the transaction_id of a previous record in the same table.

I want to create index to support for this query:

select *
from transactions
where `created`>="?" and `created`<= "?" and `ref_transaction_id`=0 and `action`=1

I feel like a composite index like (action, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.

Sounds like:

if action=1, then ref_transaction_id can only be 0, and
if ref_transaction_id=0, the action can only be 1

If that's correct, then the query may be simplified:

select *
from transactions
where `created` between "?" and "?" and `action`=1;

Notes:

add an index for action to help the query
use between to range check the date; suspect minor performance improvement

Unsolicited Observations

This sounds like a linked list (ref_transaction_ids) and a state machine (action). If that's true, or close, may want to consider putting that info into a separate "association" table,

....
`fk_id` bigint(20) NOT NULL,
`transaction_id` bigint(20) NOT NULL,
`fk_prior_id` bigint(20) NOT NULL COMMENT "replaces use of ref_transaction_id",
`action` smallint(6) NOT NULL,
....

Notes:

introduces fk_prior_id as a way of finding the ref_transaction_id using a field which is already indexed, id
consider adding the date fields depending on the
nature of the data
consider defining action as an enum to capture the state and improve readability; still stored as an integer internally but may be referenced using the defined "states" in queries.


PostgreSQL : drop column from view I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last added column fails with a ERROR:  cannot drop columns from view message. The problem is that this particular view has many references, both from and to, therefore I cannot just DROP CASCADE the darn thing!

Is there a reason why I cannot remove a newly added column from a given VIEW? Then, what can I do to accomplish this task?

(Note: the circumstances, here, are what they are, but I can very well see a similar situation, aka dropping a column from a view, in many other cases.)

PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW.

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

There is no fundamental reason why support for dropping columns couldn't be added, but nobody has done the work required to implement it yet.

CREATE OR REPLACE VIEW would have to recursively scan all dependencies and make sure that none of them referenced the column to be dropped. If they used SELECT * it'd have to remove the column from the expansion of * in the dependency then scan its dependencies too. There's a fair bit of work involved in doing that, and there are some areas where it's not clear how exactly dropping the column should behave especially when it comes to interactions with dump and reload. So nobody's wanted the feature enough to implement it yet. Patches and/or sponsorship of development are welcome.

You will have to drop the view and everything that depends on it, then re-create it and its dependencies. (The same used to be true for adding a column to a view; support for adding columns was introduced in 8.4).

Note that in general there's no expectation that DDL be reversible. The concept of "devolutions" is really flawed. For example, if you drop a column, then add it again, the data is still gone.


What does 'Vacuum' do in Menial's Base 2 application? I've had a look at the help documentation for Base 2, as well as Menial's product page, but haven't found an answer there. I've even clicked on the vacuum button and not noticed any change. What does that button do?

DISCLAIMER : Not a Menial Base 2 person
VACUUM is a sqlite operation for defragmenting tables and tablespaces. It also helps reclaim diskspace.
It defragments all sqlite tables by copying data from the sqlite tables to a temp tablespace. The VACUUM operation will then swap out the old tablespace and use the new one. Database pages lost due to DELETEs and UPDATEs will no longer exist.
MONyog also uses sqlite tables for collecting database statistics and features a VACUUM operation.


Code to simulate deadlock I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).

Thank you.

ADDED:

Reproducing deadlocks involving only one table

The best way would be to use tables you already have.
Create two tables -- table-a, table-b
For a test you can even update the same column with the same information
so you don't affect any real data.

For instance
UPDATE table_a set ID = ID where ID = 100;

Open two sessions to the same database.
On one, run

BEGIN TRAN
update table_a set ID=ID where ID = 100;

On two run

BEGIN TRAN
update table_b set ID=ID where ID =100;

Then, copy the update statements to the opposing sessions and run at the same time.
In one,

update table_b set ID=ID where ID =100;

In two

update table_a set ID=ID where ID = 100;

I just tried this now and got on MS-SQL

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 23) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


Creating an index on table A but with reference to table B Our company uses a 3rd party application for numerous aspects of the business. One aspect is dealing with customer payments and when selecting a window within the app to list all the payments ever made, the list returns pretty quickly. However, if an user then performs a filter on the list for a specific client name, this operation can take in excess of 6 minutes and returns about 15 rows out of a table that only contains 120,000 rows.

The underlying table does not contain the client name, this is held in another table (Clients). The link between the 2 tables is the clients reference number.
I have defrag'd the indexes on the database, but to no avail.
I have come to the conclusion that there is not a suitable index in place to improve this situation.

Q - Is it possible to create an index on the payments table but references the clients name in the clients table? Is this the solution to improve this situation or should I be doing something else (apart from telling the 3rd party vendor to improve this area in a future release)

note: I am a newbie if you haven't already guessed !

If I understand you correctly you have two tables (for this question):

CREATE TABLE Clients
(Client int, ClientName varchar(50), Lots Of Other Columns)

and

CREATE TABLE Payments
(PaymentID, Payment Columns, ClientID)

Obviously not actual code there to create your tables...

And your application is somehow joining those tables behind the scenes. Probably on the ClientID. So your user runs a filter on "Acme Company" and behind the scenes SQL Server is doing a JOIN statement.

Again - pseudocode

SELECT TheDataIWantFromPayments, TheCustomerDataIWant
FROM Payments
INNER JOIN Customers
ON Payments.ClientID = Customers.CliendID
WHERE Customer.ClientName = 'Acme Company'

So here is where having more information can really help. On first blush this could be a problem with a simple index create as a solution. Perhaps there is no index on the clientID in each table. Perhaps there is no index on the ClientName.

But there could be other factors at play, too. It is possible that the vendor issues this query as a contains search using the syntax of CompanyName LIKE '%Acme Company%' and then no index on CompanyName would help. It could be that the database design requires a lot of key lookups and an index isn't the solution.

Either way - 6 minutes for 15 rows is not acceptable. I would look at those tables and at least verify that the CompanyName and ClientID (or whatever the columns are that contain those values) are indexed on each side.

I would also strike up a conversation with the vendor. Even just you adding indexes could be an action that violates your support contract. Perhaps there are considerations that they already have. And you shouldn't have to be working through someone else's bad design or poorly performing application.

You can gather some data to help you. Play with the PAL tool and look at your performance data on your system. If you have a development/test environment and you can recreate this, perhaps through playing with Extended Events or SQL Trace you can see the query that issued and then run the query showing a query plan to get a sense for what is happening. But I would definitely loop the vendor in to a question like this.


Postgres update a table (only) if it exists I have a Server and several Client Applications. The Server has to be started before a client can be started. The clients then create tables in the database if they don't exists.

When the Server is started (some tables do not exist) and the following query gives me an exception:

UPDATE recipes SET lock = null
WHERE lock IS NOT NULL;

Relation >>recipes<< does not exists

I want to avoid this exception by checking if this table exists or not.

UPDATE recipes SET lock = null
WHERE lock IS NOT NULL AND
WHERE EXISTS (
   SELECT 1
   FROM   information_schema.tables
   WHERE  table_schema = 'public'
   AND    table_name = 'recipes'
   );

But this query does not work. Can you tell me where my mistake is?

You need a pl/pgsql block to do the IF:

DO $$
    BEGIN
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables
              WHERE  table_schema = 'public'
              AND    table_name = 'recipes'
            )
        THEN
            UPDATE recipes
            SET lock = NULL
            WHERE lock IS NOT NULL ;
        END IF ;
    END
   $$ ;


how can be changed hot_standby parameter on postgres 9.6 aws/rds? I have a postgres solution on aws/rds with a master and a read replica.

                         master    slave
hot_standby               on        on
transaction_read_only     on        on
pg_is_in_recovery()       true      true

Having the master set in this way the vacuum and analyze process don't work.
Any idea how to change this setting?
thank you!

Ideally the parameter hot_standby should be on only for slave server and pg_is_in_recovery() should be false for master server.

You can follow below steps to make these changes:

Check postgresql.conf file on master server and if the parameter hot_standby is set to on you can change it to off and then restart the server.
On the master server in the data file location, if there is a file called recovery.conf then just delete that file and restart the server.

Hope this will help.


do single AZ AWS RDS instances self heal? I'm aware of "multi AZ" databases "auto failing over" if one side goes down.  My question is, if you have a single AZ RDS database, and the database box "dies" does AWS automatically spin up a new one for you?  Can you expect corruption in these cases?

AWS will restart the database if the host has a problem .

"Automatic Host Replacement
Amazon RDS will automatically replace the compute instance powering your deployment in the event of a hardware failure."

I don't know if AWS can guarantee in case of hardware failure that you have all transactions. At least having automatic backup you have the status less than 5 min before the failure.

See https://aws.amazon.com/rds/details/


How can I create a MySQL database based on a SQLite schema? I have MySQL on my server and different SQLite databases on a client. I'm trying to find a way to create the same databases on the server.

Since a SQLite schema is actually a CREATE query, one way would be to write a script that extracts every critical part from the create query and converts them into a corresponding MySQL query, finally concatenate them all.

This seems quite a complex and hard-to-cover-all-situations method. I am wondering if there is any alternatives?

The three main things to change are:

Change AUTOINCREMENT to AUTO_INCREMENT
Replace double-quotes around database, table and variable names with backticks
Delete any lines with “sqlite_sequence” in them

You should then be able to run the SQL file in MySQL. If you encounter any errors, the fix should be apparent from the error message shown.


How do I alter index rebuild an entire database? I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'.  I try the following command:

ALTER INDEX all ON db REBUILD

And I get the following error:

Cannot find the object "db" because it does not exist or you do not have permissions

What am I doing wrong?

To do all tables:

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

There is no statement to do all tables in the database like you tried above

However, sometimes you don't want this, say for read only tables or huge tables. Also, some tables/indexes will hardly be fragmented.

A more intelligent way is to check fragmentation first then rebuild or reorganise. One common and widely used example is SQL Fool's script


How to emulate performant date dimension for SSAS model in tabular mode / direct query I have an indexed datetime column - lets call it "CreatedDateUTC" in table "Entity" in MSSQL db. Column contains UTC datetime. User connecting to db has known timezone offset. I have another table - DateDim with primary key column Date. This is to support date dimension for SSAS/Tabular model/Direct query scenario.

So on model level "Entity" is defined as SQL query

SELECT CAST(fn_AdjustCurrentTimezoneOffset(CreatedDateUTC) as Date) as CreatedDate
FROM dbo.Entity

The SQL query featuring DateDim from report then looks like

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(fn_AdjustCurrentTimezoneOffset(e.CreatedDateUTC) as Date) = d.Date

which is unbearably slow when [Entity] table grows large. Even if i resign on adjusting timezone (which I don't want to), it doesn't get much better - i.e. this query is slow as well:

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date

My feeling is the issue here is with CreatedDateUTC datetime - it needs to be converted to indexed CreatedDateDayUTC date upfront (not a problem since I query special reporting db). My problem is with the timezone - since users have different timezone offset I cannot simply precalculate this.

On SQL level I would be able to get around this by this ugly hack - precalculate three dates (Date1=DATEADD(day,-1,CreatedDateDayUTC)/Date2=CreatedDateDayUTC/Date3=DATEADD(day,1,CreatedDateDayUTC)) on Entity table and adjusting join to something like:

SELECT * FROM [Entity] e
JOIN [DateDim] d on CAST(e.CreatedDateUTC as Date) = d.Date
  and (e.Date1 = d.Date or e.Date2 = d.Date and e.Date3 = d.Date)

However this can't be translated to query defining "Entity" on model level (there has to be just one column which can create relationship do DateDim Date column).

Any idea how to get around this ?

Thanks to @GregGalloway to set me to right track.
I ended up with following schema:

1) I've added new mapping table [DateMap] that materialize mapping between UTC datetime (in those weird quarter-hours) to local time zone (again in  quarter-hours)

CREATE TABLE [dbo].[DateMap] (
  -- time zone code (set as context info for given user)
  [TimeZoneCode] smallint NOT NULL,
  -- quarter hours means datetime trimmed to xx:00, xx:15, xx:30, xx:45
  [UTC_QuarterHours] smalldatetime NOT NULL,
  -- local time zone
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  CONSTRAINT [PK_tmp_DateMap] PRIMARY KEY ([TimeZoneCode] ASC, [UTC_QuarterHours] ASC)
)

2) [DimDate] has primary key set to LTZ_QuarterHours

CREATE TABLE [dbo].[DimDates](
  [LTZ_QuarterHours] smalldatetime NOT NULL,
  [Date] date NOT NULL,
  [Year] int NOT NULL,
  [Month] int NOT NULL,
  [Quarter] int NOT NULL,
  CONSTRAINT PK_tmp_DimDates PRIMARY KEY ([LTZ_QuarterHours] ASC)
)

3) [Entity] table has new column [CreatedDateUTC_QuarterHours] - which means original utc date trimmed to quarter hours

CREATE TABLE [dbo].[Entity] (
  [EntityID] int NOT NULL PRIMARY KEY,
  [CreatedDateUTC] datetime,
  [CreatedDateUTC_QuarterHours] smalldatetime
)

4) Entity in model is defined as SQL query like

SELECT e.*, m.[LTZ_QuarterHours] FROM [Entity] e
JOIN [DateMap] m on e.CreatedDateUTC_QuarterHours = m.UTC_QuarterHours and
  m.TimeZoneCode = fn_GetCurrentUserTimeZoneCode()

Which is pretty fast.


Why are non-digits LIKE [0-9]? My server's default collation is Latin1_General_CI_AS, as determined by this query:

SELECT SERVERPROPERTY('Collation') AS Collation;

I was surprised to discover that with this collation I can match non-digit characters in strings using the predicate LIKE '[0-9]'.

Why in the default collation does this happen? I can't think of a case where this would be useful. I know I can work around the behavior using a binary collation, but it seems like a strange way to implement the default collation.

Filtering digits produces non-digit caracters

I can demonstrate the behavior by creating a column that contains all possible single-byte character values and filtering the values with the digit-matching predicate.

The following statement creates a temporary table with 256 rows, one for each code point in the current code page:

WITH P0(_) AS (SELECT 0 UNION ALL SELECT 0),
P1(_) AS (SELECT 0 FROM P0 AS L CROSS JOIN P0 AS R),
P2(_) AS (SELECT 0 FROM P1 AS L CROSS JOIN P1 AS R),
P3(_) AS (SELECT 0 FROM P2 AS L CROSS JOIN P2 AS R),
Tally(Number) AS (
  SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM P3
)
SELECT Number AS CodePoint, CHAR(Number) AS Symbol
INTO #CodePage
FROM Tally
WHERE Number >= 0 AND Number <= 255;

Each row contains the integer value of the code point, and the character value of the code point. Not all of the character values are displayable - some of the code points are strictly control characters. Here is a selective sample of the output of SELECT CodePoint, Symbol FROM #CodePage:

0
1
2
...
32
33  !
34  "
35  #
...
48  0
49  1
50  2
...
65  A
66  B
67  C
...
253 ý
254 þ
255 ÿ

I would expect to be able to filter on the Symbol column to find digit characters using a LIKE predicate and specifying the range of characters '0' thru '9':

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]';

It produces a surprising output:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9
178 ²
179 ³
185 ¹
188 ¼
189 ½
190 ¾

The set of code points 48 thru 57 are the ones I expect. What surprises me is that the symbols for superscripts and fractions are also included in the result set!

There might be a mathematical reason to think of exponents and fractions as numbers, but it seems wrong to call them digits.

Using binary collation as a workaround

I understand that to get the result I expect, I can force the corresponding binary collation Latin1_General_BIN:

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]' COLLATE Latin1_General_BIN;

The result set includes only the code points 48 thru 57:

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9

[0-9] is not some type of regular expression defined to just match digits.

Any range in a LIKE pattern matches characters between the start and end character according to collation sort order.

SELECT CodePoint,
       Symbol,
       RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk
FROM   #CodePage
WHERE  Symbol LIKE '[0-9]' COLLATE Latin1_General_CI_AS
ORDER  BY Symbol COLLATE Latin1_General_CI_AS

Returns

CodePoint            Symbol Rnk
-------------------- ------ --------------------
48                   0      1
188                  ¼      2
189                  ½      3
190                  ¾      4
185                  ¹      5
49                   1      5
50                   2      7
178                  ²      7
179                  ³      9
51                   3      9
52                   4      11
53                   5      12
54                   6      13
55                   7      14
56                   8      15
57                   9      16

So you get these results because under your default collation these characters sort after 0 but before 9.

It looks as though the collation is defined to actually sort them in mathematical order with the fractions in the correct order between 0 and 1.

You could also use a set rather than a range. To avoid 2 matching ² you would need a CS collation

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0123456789]' COLLATE Latin1_General_CS_AS


Does any kind of constraint in a database have a unique constraint name? I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters. The name that the database engine gives to these 'non-explicit' constraints depends on the implementation.

However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the constraints. If the programmer adds these constraints when the table is created (without specific add constraint syntax) then the Database Engine gives a name to these constraints that is comprised of random characters.

Are you saying constraint names can't be given at table creation time? That isn't correct:

create table foo(id integer primary key);

create table bar(id integer constraint pk_bar primary key);

select table_name, constraint_name from user_constraints;

TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007856
BAR        | PK_BAR

dbfiddle here

  However my question is: Does ANY kind of constraint have a specific name in the database engine? Even the NOT NULL constraints ? Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

It's exactly the same for NOT NULL constraints:

create table foo(id integer not null);

create table bar(id integer constraint nn_bar_id not null);

select table_name, constraint_name from user_constraints;

TABLE_NAME | CONSTRAINT_NAME
:--------- | :--------------
FOO        | SYS_C007860
BAR        | NN_BAR_ID

dbfiddle here

  Can those simple constraints like NOT NULL be referenced at a later time in an "ALTER TABLE" command?

Of course, just like any other constraint:

alter table bar drop constraint nn_bar_id;

dbfiddle here


How to migrate from SQL Server 2000 to SQL Server 2008 R2 Express Edition I have been trying to migrate a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

I naively tried using the SQL Server Export and Import wizard. This has worked to a degree, but seemed to try and import views as tables, and left stored procedures out altogether.

What is the least painful way of performing this sort of migration?

Whichever method you use, you'll need to be aware of any restrictions on SQL Server 2008 R2 Express that might be a show-stopper eg 10GB maximum size of any one database, single CPU, memory restrictions etc

Ideally, you'll also have tested how the application works with the new version of SQL Server. If it's a vendor product, do they support the new SQL version?

Backup and restore is the most robust method of moving a database as it leaves the original database online (if the SQL Server is left online), and it's part of what DBAs do every day. You'll need to set up the users on the new server etc.... and if it doesn't work, your original is still online. Backup and restore can be done through the GUI Management Studio.

Detach and re-attach is another option where you separate the data & log files from the original SQL Server, and physically copy them to the new SQL Server - and then attach them. It can be done through the GUI. This method is probably a bit less refined than backup and restore - just my opinion.

Whichever method you use, run DBCC CHECKDB on the new server to ensure all is well, and don't forget to schedule regular backups, checkdb, and reindexing etc.


PostgreSQL: Unable to run repmgr cloned database I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.

I am using the instructions on this guide: http://www.howtoforge.com/how-to-set-up-a-postgresql-9.0-hot-standby-streaming-replication-server-with-repmgr-on-opensuse-11.4

I'm using PostgreSQL version 9.1, repmgr 1.1.0 and Ubuntu 10.04 LTS.

I followed steps upto step-6 in the guide where I ran

repmgr -D /var/lib/postgresql/9.1/main -d pgbench -p 5432 -R postgres --verbose standby clone pgmaster

on pgslave.

Then I did a

/etc/init.d/postgresql start

on it and it the script (seemingly) finished successfully.
However, executing psql throws error:

  psql: FATAL:  the database system is starting up

Any help on proceeding further is welcome.

I searched around and realized that the problem was due to not enabling hot_standby on the standby server. I wrote a corrected, updated and simplified version of the above article as an odt for future personal reference. Here is the text in its entirety (converted to html from odt using openoffice.org) for those who are interested in setting up a single read-only clone of a database using repmgr:

Single Slave Streaming Replication with
PostgreSQL

Introduction
This guide aims to quickly help you configure a PostgreSQL 9.1
server with a database, and have it replicated to a slave that can be
used for read-only queries. There is no concept of failover involved
here, and the slave will only have a read-only copy of the master's
data.

1. Tools Needed

    A stable GNU/Linux Distribution (Recommended OS: CentOS 6.2 x86-64)
    PostgreSQL 9.1 – you may install from here: http://yum.postgresql.org/ (also make sure that the following packages or their equivalnets are installed)

        postgresql
        postgresql-client
        postgresql-contrib
        postgresql-server
        postgresql-server-dev
    2 workstations – a master that runs the primary database
    and a slave that runs the replicated read-only database (for the
    purpose of this document, their respective IP addresses have been
    replaced with pgmaster and pgslave so
    make those changes in /etc/hosts of both machines if you want to
    follow the following instructions word for word)

2. Installation Check and Password Creation

    Run /etc/init.d/postgresql start on both systems to check if PostgreSQL is functional or not.
    Run /etc/init.d/postgresql stop on both systems to stop PostgreSQL. We will not be using PostgreSQL till we finish some configuration related tasks.
    Set a password for user postgres on both the systems. This user by default has no password, but we need a password to help create an SSH tunnel between pgmaster and pgslave.
    Run sudo passwd postgres on both systems and type a new Unix password for both.

3. SSH Tunnel Creation

    On the pgmaster do the following:
    su postgres
    ssh-keygen -t rsa (press enter at every prompt)
    ssh-copy-id -i ~/ssh/id_rsa.pub pgslave  (you need to enter pgslave's postgres password)
    ssh pgslave and see if you are able to login without password
    Repeat the above steps on pgslave
    su postgres
    sh-keygen -t rsa
    ssh-copy-id -i ~/ssh/id_rsa.pubaster
    ssh pgmaster and see if you are able to login without password
    Make sure you log out from the remote machine after you finish checking connectivity

4. Editing postgresql.conf on pgmaster
You need to make the following changes in the file
postgresql.conf that resides in the configuration
directory inside /etc/postgresql/
on your machine pgmaster:

    listen_addresses = '*'
    wal_level = hot_standby
    checkpoint_segments=30
    archive_mode=on
    archive_command='cd .'
    max_wal_senders=2
    wal_keep_segments=5000

5. Editing postgresql.conf on pgslave
You need to make the following change in the file postgresql.conf
that resides in the configuration directory inside /etc/postgresql/
on machine pgslave:

    hot_standby=on

6. Editing pg_hba.conf on pgmastger
You need to make the following changes in the file pg_hba.conf
that resides in the configuration directory inside /etc/postgresql/
on machine pgmaster:

    host all all 192.168.5.0/24 trust
    host replication all 192.168.5.0/24   trust

7. Adding PostgreSQL bin folder to PATH
There are a bunch of nifty postgresql utilities we will be using
here, so lets set the PATH variable so that it knows where to find
them

    Execute locate pgbench
    The output will be something like /usr/lib/postgresql/9.1/bin/pgbench
    Excluding final word pgbench, copy the above path and add it to your path variable.
    Execute nano ~/.bashrc
    add line export PATH+=:/usr/lib/postgresql/9.1/bin/ (or whatever location the locate command revealed)
    Close the file and save changes
    You may need to log out and login (or open a new shell) for changes to take effect.

8. Loading pgmaster's PostgreSQL server with
dummy data

    On pgmaster start the PostgreSQL server first: /etc/init.d/postgresql start
    We create a test database and load it with some dummy data with the following commands:

        su postgres
        createdb pgbench
        pgbench -i -s 10 pgbench

    Alternatively open the database pgbench yourself, create a sample table and insert sample data into it.

9. Erasing pgslave's data and checking pgmaster
connectivity

    We are going to erase data directory of pgslave, so execute following on that machine:
    First stop PostgreSQL server /etc/init.d/postgresql stop
    Move into the PostgreSQL default data directory folder: cd /var/lib/pgsql/data (or to the data directory that is default for your installation)
    Empty the directory completely with rm -rf *
    Now execute psql -h pgmaster -d pgbench and see if you are able to access the database on pgmaster through pgslave.

10. Installing repmgr
Though single slave replication is fairly straightforward, we are
using a tool called repmgr to help make the process even more simpler. Here is how you install it:

    Grab repmgr source code from http://projects.2ndquadrant.it/sites/default/files/repmgr-1.1.0.tar.gz
    and copy it to /tmp for installation.
    Have the following packages installed to ensure that compiling repmgr is possible:

        make
        gcc
        postgresql-devel
        libxslt-devel
        pam-devel
        libopenssl-devel
        krb5-devel

    Extract the downloaded archive and enter the source code folder.
    Compile with make USE_PGXS=1
    Install with make USE_PGXS=1 install
    Execute below two commands to check if repmgr is installed correctly:

        repmgr --version
        repmgrd --version

11. Cloning pgmaster onto pgslave using repmgr

    Execute su postgres and login as the postgres user on pgslave
    Run this to clone database: repmgr -D /var/lib/pgsql/data -d pgbench -p 5432 -R postgres --verbose standby clone pgmaster
    Note that the -D parameter /var/lib/pgsql/data should be replaced with the appropriate location of the data folder on pgslave
    Also note that 5432 is the default port PostgreSQL runs in.
    When the command finishes executing (it is going to take several seconds to finish if you had used pgbench to insert random data) you may start PostgreSQL on pgslave with /etc/init.d/postgresql start

12. Testing streaming replication
Congratulations! You have successfully configured pgslave
to copy pgmaster's database via
streaming replication. Note that due to being in continuous recovery
mode, pgslave can only be used to
execute read-only queries and no
insertions or modifications are possible.

    Insert some values into sample_table on pgmaster.
    execute select * from sample_table; on pgslave's psql prompt after connecting to pgbench database.
    If everything worked properly, you should be able to view newly added tupples on the output from pgslave.

Conclusion
We just learned how to quickly configure a PostgreSQL database to
act as a read-only mirror to another database. Note that in case the
slave goes offline, it will automatically recover data from the
master and would be soon up-to-date. Streaming replication is at
database level, so you need to execute the command in part 11for each
new database that needs to be replicated via streaming replication.


Will changing a MySQL user's password break existing connections? If I update a user's password or privileges in MySQL, how does this affect existing connections?  In particular, if I change the replication user password, will it break replication for connected slaves?

Edit: Assume I am using FLUSH PRIVILEGES after updating the information.

According to MySQL 5.0 Certification Study Guide

Page 489 under the subheading says 'Statement Privilege Checking' says:

Each time the server receives a statement from a client, it check's the client's privileges to see whether it is allowed to execute the statement.

Since MySQL grants are checked in memory, then one of two things must happen
SCENARIO #1
If you ran standard GRANT commands to change the password, the password change is recording on disk via FLUSH PRIVILEGES; automatically. (See Section 34.1.2 "The Grant Tables")
SCENARIO #2
If you hacked the password in, as follows (shown in Section 35.5.1 Page 498):
SET @newpass = PASSWORD('whateverpasswordiwant');
UPDATE mysql.user SET PASSWORD = @newpass WHERE user='replication_user';

This stores the password on disk but does not push it into the in-memory copy of the MySQL Grants. As long as you don't run FLUSH PRIVILEGES; or restart mysqld, you have a chance to change it back. Newer versions of MySQL 5.5 may not be as forgiving.
SHORT ANSWER : For SCENARIO #1, Yes, for SCENARIO #2, more than likely.


Truncate text output of COPY command for postgres I've got a script that copies every table in a schema to a csv using the following:

psql -Atc "select tablename from pg_tables where schemaname='$schema'" --host=$host -w --user=$user $database |\

 while read TBL; do
    psql -c "COPY \"$schema\".\"$TBL\" TO STDOUT WITH DELIMITER AS '|' CSV HEADER" --host=$host -w --user=$user $database > "${TBL}_${timestamp}.csv"
  done

Which by and large works great.  Now I've been asked if I can:

Remove the milliseconds from the timestamp output
Stop CLOBS from being moe than 6000 characters

Is there any way I can continue doing what I'm doing?

Instead of COPY <table>, you can use COPY (SELECT <columns> FROM <table>), and work the truncation logic into the column list.

Of course, you'll need to build this list based on the type information in the catalog. This should do it:

  SELECT
    string_agg(
      quote_ident(column_name) ||
        CASE udt_name
          WHEN 'timestamp' THEN '::timestamp(0)'
          WHEN 'timestamptz' THEN '::timestamptz(0)'
          WHEN 'text' THEN '::varchar(6000)'
          ELSE ''
        END,
      ','
    )
  FROM information_schema.columns
  WHERE
    table_schema = '$schema' AND
    table_name = '$TBL';

This query just generates the <columns> string to put into the COPY statement. Run it inside your while loop, put the output in a variable (e.g. $columns), and then run psql -c "COPY (SELECT $columns FROM \"$schema\".\"$TBL\")...


Where does the stored procedure Searchinall come from? When I create a new database in SSMS either using the creation wizard or the CREATE DATABASE command the database will contain a stored procedure called Searchinall. This procedure has never been added to new databases previously, but since 2 days it will be created in every new database, no matter how I create it. I have tried creating a database from a different Computer on this server as well, but the stored procedure will still appear.
When I tried looking online for a procedure Searchinall I could not find any results, so I suspect it is not a new feature and nothing that should be created by default.
Could someone have maybe accidentally added it as a default stored procedure to be created in every new database on the server?
If so, how can I disable it?

When you create a new database in SQL Server the Model database is used as a template. Your new database will contain any stored procedurethat is in the model database.
From what you are describing it seems that the stored procedure, Searchinall is a custom procedure added to the model database by someone in your organization and then automatically included in any database that you created.


Postgresql - how to securely delete the storage freed when a table is dropped? For a system with sensitive information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system is persistent even though a particular data set won't be, I'm trying to figure out my options for securely deleting the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many languages, contexts, and databases.  I've looked and haven't been able to find a way to ask PostgreSQL to do the equivalent of shredding its underlying files before releasing them to the OS when a table is DROPped.  Is there a built-in way to ask PostgreSQL to do this? (I might just not have searched for the right thing - my apologies if I missed something and I'll gladly accept abuse along with links to relevant documentation)

A partial answer we're looking at is shredding the underlying data files for a given relation and its indexes manually before dropping the tables, but this isn't so elegant, and I'm not sure it is getting all the information from the tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk - either running a utility to do that, or periodically replicating the data volume, swapping in the results of the copy, then shredding the entire volume that was the source so its "free" space is securely overwritten in the process.

Are we missing something?  Are there other options we haven't found?  If we have to clean up manually, are there other places we need to go to shred data than the relation files for a given table, and all its related indexes, in the database's folder?  Any help or advice will be greatly appreciated.

drop table removes the table files from disk filesystem.

If you need them securely erased you could perhaps look for a filesystem that supports that.

On linux SSD filesystems, mounting with -O discard (or running fstrim after dropping) will hide the content of the deleted files from consumer-grade forensic tools (like photorec, dd, etc.) however nation-states with electron beam probes and the like may still be able to access the data if they can gain physical access to the media.

On magnetic disk running sfill will shred the deallocated blocks.


How to Capitalize letters that are after a special character? I have checked the answer to this question and couldn't find an exception for names that start after a special character like dot.
for example ".TOM WILSON"

I have tried this query

declare @a varchar(30);

set @a = '.TOM WILSON';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

Is it possible to ask SQL skip special Characters?
I am new to the concept and will appreciate any source that can give me a better understanding.

Here is one for Caps after any NON-ALPHA character (including spaces/special characters.

Edit: Oops, I didn't realize you wanted ALL letters to be capitalized.  Just thought you meant InitCaps.  I'll leave it up so you can work with it (should just be a matter of changing the REGEX) and mold it to what you need unless others would like me to take it down.

Inspired by (stolen from):
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function/

DECLARE @String VARCHAR(100)

SET @String = 'Hello.this is@aTest'
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next position that we
     -- need to update.  The collation here is essential to making this so simple.
     -- A-z is equivalent to the slower A-Z
SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
     -- Note the reason for the speed here is that ONLY places that need capitalization
     -- are even considered for @Position using the speed of PATINDEX.
  WHILE @Position > 0
SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;

SELECT @String;
-- RETURN @String; --Only for functions

--Output: Hello.This Is@Atest

And here it is as a function:

CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

------------------------------
RETURNS VARCHAR(8000)
     AS
  BEGIN

DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
     -- need to update.  The collation here is essential to making this so simple.
     -- A-z is equivalent to the slower A-Z
SELECT @String   = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
     -- Note the reason for the speed here is that ONLY places that need capitalization
     -- are even considered for @Position using the speed of PATINDEX.
  WHILE @Position > 0
SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
        @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
-----------------
RETURN @String;
    END ;


How do I pivot to summarize my entries by a specific area? I have a map where every row represents a bird and a row with the attributes shown in the excerpt of the attribute table:

round     | area | date         | species     | behaviour
---------------------------------------------------------
1         | 20   | 2018-04-13   | woodpecker  | NULL
1         | 55   | 2018-04-14   | woodpecker  | NULL
1         | 21   | 2018-06-01   | robin       | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | robin       | NULL
2         | 55   | 2018-05-28   | chaffinch   | NULL
2         | 20   | 2018-05-29   | chaffinch   | NULL
3         | 21   | 2018-06-01   | robin       | breedig
3         | 55   | 2018-06-01   | blackbird   | NULL

Every study area was visited n-times (see column "round", where "round" is global. That means, that when we did not
find a single bird on a specific round, we have no entry for this area on this date but the round will increase next time the area is visited)

What I need is a table like that one below. I have no idea, how to get this done with SQL. What I need is something like:

*Start with lowest round
    get area and count the species for this area
    next area
next round*

The wanted table:

area  | round | date        | woodpecker | robin | blackbird | chaffinch | sum
--------------------------------------------------------------------------------
20    | 1     | 2018-04-13  | 1          | 0     | 0         | 0         | 1
55    | 1     | 2018-04-14  | 1          | 1     | 2         | 0         | 4
21    | 1     | 2018-04-01  | 0          | 1     | 0         | 0         | 1
20    | 2     | 2018-05-29  | 0          | 0     | 0         | 1         | 1
55    | 2     | 2018-05-28  | 0          | 0     | 0         | 1         | 1
21    | 3     | 2018-05-28  | 0          | 1     | 0         | 0         | 1
55    | 3     | 2018-06-01  | 0          | 0     | 1         | 0         | 1

Do you know that you only have woodpecker, robin, blackbird, chaffinch? To pivot at all you must either

Constraint the value-set in the table that you're pivoting on. Because values become schema, and schema must be determined at the time you issue the query you would have to know all possible values.
Make two trips to the server, one to generate the original resulset of values. And a second to read the metadata from that resultset so you can issue a second query against it.


How to restore database using old full backup and current log file What is happened? My client has lost the hard disk. He has to restore database.

He has the full backup of database 15 days old.
He has ldf (log) file of this database.
He has NOT mdf file (it was located on that hard disk).

Does it have enough components to restore SQL Server 2008 database, if he used standard options in SQL Management Studio when he did full backup 15 days ago (still database structure has changed)?
Is it possible to restore at least database structure (it's possible to regenerate data from other source) that is was actual on crash moment?

Finally:

1) Is it possible to restore all database (data+structure)

2) Is it possible to restore database structure

Thank you.

ADDED:

LiteSpeed for SQL Server Enterprise (6.1 or 5.x) helped me.

1) The database was restored from full backup (15 days old backup)

2) Then LiteSpeed uses copy of mdf file (see previous step) and saved ldf file (log at crash moment) generates sql script of all changes (DDL/DML).

3) Then this script was applied to restored db in step 1)

Any way there were a lot of issues (bugs) of this application, but it helped to restore at least database structure changes.

It was essential that your database was in the Full recovery model and no transaction log backups were created in the last 15 days. That provided that all transactions that happened after the full database backup were still in your LDF file. Otherwise, there would have been a gap between your LDF and full database backup transactions and you would not be able to recover everything successfully (maybe that was the case)

There's another tool that can be used in the same scenario to read the transactions from the LDF file and create a redo script for them - ApexSQL Log.