Why does SQL Server fail to create a procedure referencing a linked server? Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server:

2013-11-04 17:13:22 +00:00 INFO   Creating [dbo].[GetPublications]
2013-11-04 17:41:29 +00:00 INFO   Command RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand failed
2013-11-04 17:41:29 +00:00 INFO   RedGate.Deploy.PluginApi.Deployment.ConventionFailedException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> RedGate.Deploy.SqlServerDbPackage.Shared.Exceptions.SqlServerInaccessibleException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> System.Data.SqlClient.SqlException: Login failed for user 'red_gate_deployment_manager'.

One of the procedures I want to deploy refers to the distribution database on a linked server. The definition looks like this:

CREATE PROCEDURE dbo.GetPublications
AS
BEGIN
  SELECT publisher_db, publication, description
  FROM DISTRIBUTOR.distribution.dbo.MSpublications;
END;

In SSMS I connected to the target server as red_gate_deployment_manager and tried to create the procedure manually. I received a similar error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'red_gate_deployment_manager'.

A colleague explained that RGDM fails to create the procedure because it has no permission to connect to the referenced linked server.

In our environment, all linked servers impersonate the local login. Our solution therefore is to grant administrative control to RGDM on the linked server.

We worked around the issue, but I still didn't understand it.

Inconsistent Behavior?

Why does SQL Server raise an error at creation time rather than at execution time?

It's confusing because the behavior feels inconsistent. I can't think of a good reason for it to behave this way, especially when in other situations the error is deferred until execution time.

When I create a stored procedure that references a missing stored procedure:

CREATE PROCEDURE dbo.ExecuteMissingProcedure
AS
BEGIN
  EXECUTE dbo.MissingProcedure;
END;

SQL Server prints a warning instead of raising an error:

The module 'ExecuteMissingProcedure' depends on the missing object 'dbo.MissingProcedure'. The module will still be created; however, it cannot run successfully until the object exists.

However, when you try to execute it:

EXECUTE dbo.ExecuteMissingProcedure;

SQL Server raises a hard error:

Msg 2812, Level 16, State 62, Procedure ExecuteMissingProcedure, Line 4
Could not find stored procedure 'dbo.MissingProcedure'.

Some objects are allowed to be created to facilitate Deferred Name Resolution. The assumption is that if you create a procedure that references dbo.MissingProcedure, you will create dbo.MissingProcedure some time between the moment you create the procedure, and the first time you execute it. Another situation that works is a table that doesn't exist yet. I can say the following, even if dbo.table_name doesn't exist:

CREATE PROCEDURE dbo.procedure_name
AS
BEGIN
  SET NOCOUNT ON;
  SELECT column_name FROM dbo.table_name;
END

Again, because SQL Server is giving me the benefit of the doubt in this case. But it doesn't always. If dbo.table_name exists, but does not (yet) contain a column named column_name, I'll get an error:

  Msg 207, Level 16, Procedure procedure_nameInvalid column name 'column_name'.

SQL Server validates the column names if the object exists, just like it validates the remote server objects in your case - which it can't do, because the login doesn't (yet) have permission.

Now, why SQL Server is implemented such that it lets some things slide and not others, you'll have to ask the dev team. Erland Sommarskog has some great ideas on giving us the option to make the behavior more consistent; you should read Ideas for SET STRICT_CHECKS ON and vote for - and comment on - his Connect item.


Save output from multiple SQL SELECT commands to a file Within a Postgres function I would like to run a number of SQL SELECT commands and append the output to the same variable. After all the SELECT commands have run this output should be written to a file so that the user could read this report.

What is the best way to implement this?

Basic function to return rows from multiple SELECT statements:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS TABLE(a_id integer, txt text) AS
$func$
BEGIN

RETURN QUERY SELECT a.a_id, a.txt FROM tbl_a a;

RETURN QUERY SELECT b.a_id, b.txt FROM tbl_b b;

END
$func$ LANGUAGE plpgsql;

You could do the same with UNION ALL.

Simple & quick demo to output from tables with different structure:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS SETOF text AS
$func$
BEGIN

RETURN QUERY SELECT t::text FROM tbl_a t;

RETURN QUERY SELECT t::text FROM tbl_c t;

END
$func$ LANGUAGE plpgsql;

You can cast any type to text in PostgreSQL, even the composite type of a table row ...

Call:

SELECT * FROM f_get_rows()

To write this to a file (local to the server):

COPY (SELECT * FROM f_get_rows()) TO '/path/to/my/file.csv';

To get you started read these chapters in the manual:

CREATE FUNCTION
Returning From a Function
COPY

For a more specific answer you need to write a more specific question.


How can one drop all default values and allow NULL for all columns of a table? I'm working on a PHP script to modify the primary key (PK) of a table.  My current code is:

$q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';";
$keys = Query ( $q);
if ( $keys)
{
    $q = "ALTER TABLE $db.$table
            DROP PRIMARY KEY,
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}
else
{
    $q = "ALTER TABLE $db.$table
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}

This mostly works for what I need.  The only problem is that if I drop a field as a PK, it retains the NULL='No' and DEFAULT=0 properties from being a PK.

Since I'm going to define the PK later and I generally don't need not null and default constraints I would like to clear all not null values and default values.  Then I can proceed to define the primary key and know that the primary key is the only columns with default values and not NULL constraints.

Is there a way to do this with MySQL?  I could do it with PHP, but it would probably be more work and would be less elegant.

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE
You would have to issue this query
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will retrieve every column in the PRIMARY KEY.
You could then make this query generate the ALTER TABLE clauses for you as follows:
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will produce a comma-separated list of MODIFY COLUMN clauses you need to apply to the column after dropping the PRIMARY KEY. If you want to drop any of these columns, you have to change MODIFY COLUMN  to DROP COLUMN for the columns that need to be dropped.
Give it a Try !!!
UPDATE 2014-12-09 14:00 EST
Your comment

I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results

To give you a general idea of how query can help you, here is a table in MySQL on my laptop
mysql> show create table weird.vinner\G
*************************** 1. row ***************************
       Table: vinner
Create Table: CREATE TABLE `vinner` (
  `startnr` int(11) NOT NULL,
  `alder` varchar(25) NOT NULL,
  `kjonn` varchar(25) NOT NULL,
  `ovelseid` varchar(25) NOT NULL,
  `slutttid` int(11) NOT NULL,
  PRIMARY KEY (`startnr`,`slutttid`),
  KEY `slutttid` (`slutttid`),
  CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See the PRIMARY KEY. It has two columns: startnr and slutttid.
Now, if I run my code on that tabl, I get this list
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
    -> FROM information_schema.key_column_usage
    -> WHERE table_schema='weird'
    -> AND table_name='vinner'
    -> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))     |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The output creates a MODIFY COLUMN clause for both columns.
If you were dropping the slutttid column, you would use PHP to change MODIFY to DROP
MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid

You would be doing this kind of code to generate the clauses needed


UNIQUE index key violation I have a table with a PK and a unique non-clustered index, as follows:

CREATE TABLE Table1
(
    Id INT IDENTITY(1,1) NOT NULL,
    Field1 VARCHAR(25) NOT NULL,
    Field2 VARCHAR(25) NULL,
 CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Id ASC)
)

CREATE UNIQUE NONCLUSTERED INDEX IX_Field1_Field2 ON Table1
(
    Field1 ASC,
    Field2 ASC
)
WITH
    (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = OFF,
        ONLINE = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )

I have 2 jobs whose execution times I find are overlapping each other.  They both include the same INSERT into this table and frequently the job that starts last fails because it tries to insert a record into Table1 with a duplicate index key value.

INSERT Table1
SELECT Field1, Field2
FROM SomeOtherTable sot WITH (NOLOCK)
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE sot.Field1 = t1.Field1
    AND sot.Field2 = t1.Field2
)

From what I've been able to discern, the INSERT in Job1 is still executing when the NOT EXISTS from Job2 is evaluated resulting in Job2 trying to insert a duplicate key value.  It seems to me that the locking for Table1 is not happening as expected.

I'm at a loss as to why this is happening.  Would this have anything to do with the NOLOCK hint used in the INSERT?  I didn't think that that hint would include Table1 in its scope, only SomeOtherTable.

I know I can mitigate the duplicate key error by setting IGNORE_DUP_KEY to ON for the index, and that would be fine for us in this situation.  I would like to know, though, why the duplicate is showing up in the 2nd INSERT.

There is a lot to grasp if you want to fully understand all of the intricacies of locking and isolation levels in SQL Server, and your question is essentially leading you in this direction.

All of the information is available at http://msdn.microsoft.com/en-us/library/ms173763.aspx, although it's not an easy read.  An easier read is available at http://databases.about.com/od/sqlserver/a/isolationmodels.htm - pay particular attention to the "phantom reads" section.

For what you are trying to do you would need BOTH transactions running with
an isolation level of "SERIALIZABLE".  What this basically means is that they will run "as if" one was run after the other - that they were actually serialized.

What is happening is that BOTH the transactions decide that the same record needs to be inserted, and then BOTH try to insert that same record.  The lock that is taken by the test logic is separate from the lock that is taken for the insert, which allows this to happen.  SERIALIZABLE will stop this from happening with it's "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes" clause.  Another way to think of this is that the read locks from the test need to be held until the whole transaction completes.  This can be specified using a HOLDLOCK query hint; but I have to say that I've not personally played with using HOLDLOCK, but just with the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" version.


How to store a query result into a variable? SQL Server 2008 I have two tables and I want to store the result query of my stored procedure
into a variable.

UserTable:

UserID int primary key identity
CredentialsID int
AccountType nvarchar(15)
ModifiedDate date

CredentialsTable:

Credentials ID int primary key identity
Password

Stored procedure:

Create procedure addFaculty
    @ID int
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin
        Insert into CredentialsTable values ('iamafaculty');

        Select CredentialsTable.CredentialsID
        from CredentialsTable
        where CredentialsTable.Password = 'iamafaculty';
    end
end

Executing the select statement will return a credentialsID which I want to
store in the @ID parameter. So I can use it on my other statements to be made.

If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query:

Create procedure addFaculty
@ID int OUTPUT
as
begin
    IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty')
    begin
        Insert into CredentialsTable values ('iamafaculty');

        Select @ID = CredentialsTable.CredentialsID
        from CredentialsTable
        where CredentialsTable.Password = 'iamafaculty';
    end
end

Is that what you are asking?


View SQL Server 2016 Login Audits I have Login Audits for both failed and successful. How do i view the actual logs? Where are they located?

https://docs.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio

Is there a way to ignore service accounts?

I would change the setting in properties to None and set up a security audit where you have better control on what you want to audit, for how long you want to retain the result and exclude account you do not want to audit.

Create a Server Audit Specification for Failed login only.  You can add other audit action type as you need.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit_Spec_FaileLogin]
FOR SERVER AUDIT [FailedLogin]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

This script will create a new login fail audit. Replace filepath, maxsize, max_files.  To exclude service account replace the value where I have ServiceAccount in the script.

USE [master]
GO

CREATE SERVER AUDIT [FailedLogin]
TO FILE
(   FILEPATH = N'C:\'
    ,MAXSIZE = 64 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE

)
WHERE ([server_principal_name]<>'ServiceAccount')
ALTER SERVER AUDIT [FailedLogin] WITH (STATE = ON)
GO

Use this process as documented in below link to read the audit file.

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

Two more resource to learn about security audit.

SQL Server Security Audit Basics by Feodor Georgiev
SQL Server Audit (Database Engine)


Check if database is restored to the most recent log file Folks,

I am running a copy of our production database as a backend for an Instance of SQL Server Reporting Services. The copy is being updated off transaction logs which are taken frequently (hourly) for backup purposes using RESTORE DATABASE WITH NORECOVERY / WITH STANDBY. I obviously cannot use the built-in transaction log shipping facilities as the database is already being BACKUP LOGged and trying to use log shipping would break both mechanisms' log chains.

But I need some way to monitor if my copy is up-to-date, ideally without changing the original database's schema or data. My first approach would contain retrieving the most recent log backup's last LSN using RESTORE HEADERONLY FROM DISK and comparing it to the LSN of my copy instance. But I am having difficulties finding an LSN which would be able to match the result from RESTORE HEADERONLY - what I see in sys.database_files seems to be a different thing entirely.

Where would I find matchable LSNs?

You should be able to use the restorehistory table on the server to which you are restoring to get the most recently restored LSN.

SELECT TOP 1 b.type, b.last_lsn
 FROM msdb.dbo.restorehistory a
 INNER JOIN msdb.dbo.backupset b
     ON a.backup_set_id = b.backup_set_id
 WHERE a.destination_database_name = 'YourDatabaseName'
 ORDER BY restore_date DESC

This was posted in a discussion of log files some years ago at: http://www.sqlbackuprestore.com/logsequencenumbers.htm

EDIT: As a further option, if you are using Enterprise Edition you can use database snapshots for reporting.  The database in this case remains in restore mode and the snapshot(s) read only.  If this is your environment, you could indeed use log shipping to keep your Reporting Server data up to date.


Locking: Allow INSERT but not UPDATE I would like to lock a table: INSERT should be allowed, but UPDATE should be disabled.
I am using a current PostgreSQL version.
I read Advisory Locks and explicit locking. Maybe I am blind, but I could not find a suitable solution for my goal.
Here are details to my use case:
There is a table which is roughly like logging: Several processes add new rows to the table at the same time.
Once in a hour a script processes the data which was added.
Now I want to be sure that this script has a fixed working set. No modifications should be done to the rows which the script is working on. But it is ok if new rows get added while the scripts run. These rows get processes with the next call of the script.

Sure, you can use row-level locks to write-lock all rows currently in the table with:
SELECT * FROM tbl FOR UPDATE;

Or similar.
But that prevents all writes, including DELETE, not only UPDATE - for the duration of the lock. And there is the remaining question about rows inserted after that: shouldn't those be protected from UPDATE, too?
The way you phrased it ("INSERT should be allowed, but UPDATE should be disabled."), it sounds like a more general matter of privilege management with GRANT and REVOKE, rather than locking.
Something like:
GRANT INSERT ON TABLE public.tbl TO public;
REVOKE UPDATE ON TABLE public.tbl FROM public;

Or:
GRANT INSERT ON TABLE public.tbl TO specific_role;
REVOKE UPDATE ON TABLE public.tbl FROM specific_role;

The actual commands depend on who has been GRANTed privileges in the first place, and who shall be prevented to update now.
Don't forget the special case of the table owner. The manual on GRANT:

There is no need to grant privileges to the owner of an object
(usually the user that created it), as the owner has all privileges by
default. (The owner could, however, choose to revoke some of their own
privileges for safety.)

While locks only last for the duration of a transaction, the effect is permanent - until changed explicitly. Also, locks are expensive. GRANT / REVOKE is almost cost-free.
You need the necessary privileges. Either be the owner of the table, be member in the role owning the table, be a superuser, or you have been granted the same privilege WITH GRANT OPTION.


How to import json table into PostgreSQL table? I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data:

[{
"token": "a978f9793a594d11860e9cf7962996d7",
"name": "vehicle.moving",
"description": "Vehicle is moving."
},
{
"token": "95e38bf08c2842f39f2d64ba8152b265",
"name": "vehicle.stopped",
"description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move."
}]

Would like the result to look like:

token                            | name            | description
---------------------------------+-----------------+--------------------
a978f9793a594d11860e9cf7962996d7 | vehicle.moving  | Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 | vehicle.stopped | Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.

My SQL currently looks like this:

\set content `type "F:\Datasets\attribute.json"`
insert into attribute select * from jsonb_array_elements(:'content');

Which just adds rows of quoted json to my table.
Have been bashing at this all day (SQL rookie) and would be very grateful for help.

That's what I would do:

SELECT v.*
FROM t
     ,jsonb_to_recordset(t.j::jsonb) AS v(token text, name text, description text);

Or in a 2-step query:

SELECT vr.*
FROM t
     ,jsonb_array_elements(j::jsonb) AS tt(recs)
     ,jsonb_to_record(tt.recs::jsonb) AS vr(token text, name text, description text);

source @dba.stex

dbfiddle


Importing CSV file - PostgreSQL 9.2 I'm trying to import data from a CSV file on a PostgreSQL 9.2 server, but I get errors when doing that.

Pgadmin:

ERROR: Cannot execute COPY FROM in a read-only transaction

Why am I getting that error if the DB is not read-only?

psql console:

ERROR:  invalid input syntax for type numeric: "unit_cost"
CONTEXT:  COPY billables, line 1, column unit_cost: "unit_cost"

Command used:

COPY dm.billables(code, info, unit_cost, unit_price) FROM '/var/lib/pgsql/sql/charge.csv' DELIMITER ',' QUOTE '"' csv;

How can I import the file?

UPDATE:

table dm.billables:

    CREATE TABLE dm.billables
    (
      billable_id bigint NOT NULL DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
      acco

unt_id bigint NOT NULL,
  code character varying(64) NOT NULL,
  info "text",
  m_unit "measurement_unit",
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  tax_enabled_price boolean DEFAULT true,
  tax_aggregate_id_price bigint,
  ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_last_used timestamp with time zone,
  is_demo boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
  CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost)
      REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
      REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
  CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
)

CSV file:

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,92,Angela Murray]",59,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) non-SLIANZ,60,Anthony Swindale]",56.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) SLIANZ,142,Bernadette  Cutelli]",49,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,11,Beryl Harrison(Harri)]",59,95

Your table is like this:

CREATE TABLE billables
(
  billable_id "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
  account_id bigint NOT NULL,           <<<<---- Can't be NULL...
  code character varying(64) NOT NULL,
  info "text",
  m_unit "measurement_unit",
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  <rest snipped...>

The fields you want to insert are

(code, info, unit_cost, unit_price)

For starters, you don't provide values for the field account_id which is NOT NULL

Your insert is like this:

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95,Alisha Davidson

I ran this SQL (on a slightly modified CREATE TABLE - don't have your other tables for FOREIGN KEYs - don't know what "measurement_unit" is (user-defined data type?)). New CREATE TABLE below.

INSERT INTO billables (account_id, code, info, unit_cost, unit_price)
VALUES (45, 'Interpreting Normal/AH','DO NOT CHANGE [OTH-INTERPSERV (2-5)
non-SLIAN,41,Alisha Davidson]',46.5,95);

And it worked - notice the single-quotes (').

Hopefully this will help to get you started.

New CREATE TABLE.

CREATE TABLE billables
(
  billable_id serial,  <<-- why not user serial here?
  account_id bigint NOT NULL,
  code character varying(64) NOT NULL,
  info "text",
  m_unit bigint,
  m_unit_custom character varying(64),
  unit_cost numeric(16,4),
  tax_aggregate_id_cost bigint,
  unit_price numeric(16,4),
  tax_enabled_price boolean DEFAULT true,
  tax_aggregate_id_price bigint,
  ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
  ts_last_used timestamp with time zone,
  is_demo boolean NOT NULL DEFAULT false,
  CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
  CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
  CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
);

[EDIT in response to OP's comments]

Start simple and then bring in complexity. Post your new .csv with the account_id.

Or, write an SQL statement like I did. See what errors occur (you should get none).

Start with no CONSTRAINTs - then add them gradually and see where your COPY starts to fail - it's debugging database style :-)


SELECT permission denied trying to import data into Excel We recently migrated to a newer version of a program and migrated its database from SQL Server 2000 to SQL Server 2008 R2. We had some Excel files which ran a few custom queries to pull data. I changed the values in the connection string to reflect our 2008 R2 setup:

DRIVER={SQL Server};SERVER=SQL01;UID=PSI;Trusted_Connection=Yes;DATABASE=PSI_DB_A;

Trying to do a refresh in Excel gives the error:

  The SELECT permission was denied on the object 'ARTRNTRX', database
  'PSI_DB_A', schema 'dbo'.

I researched this error on this site and numerous places on the web before posting; trying some of the things suggested.

The only difference is that with SQL Server 2000, the user "sa" had access to everything and every database. We wanted to avoid that in 2008 R2. So I created the PSI user and wanted to give it limited access. We just perform SELECT queries in these few Excel sheets anyway.

Using the SSMS, under Security > Logins > PSI > Properties > User Mapping > I gave the "PSI" user the "db_datareader" role for PSI_DB_A. Then under the Databases > PSI_DB_A database > Properties > Permissions > I select PSI user and granted them the "Connect" and "Select" permissions in the list.

But I'm still getting the SELECT permission error when I try to refresh the data in Excel. I'm at a loss here.

EDIT:

After a bit more digging, I noticed when in Excel I could connect to the SQL Server using From Other Sources > From SQL Server > Enter server name > Select Database PSI_DB_A from the list and it shows me views and tables. But only certain tables are showing, not all of them.

Going back into SSMS, I expanded the table list under the database, right-click a table (on one of the tables which was showing in Excel) > Properties > Permissions. Under users/roles the user "public" is shown which is given SELECT, etc. permissions. But on 90% of the other tables, "public" is not listed.

Why does "public" need to be added at the table permission level, when I defined a login user who should have complete read authorization on all tables in the database?

After reviewing your error. I tried to setup in my TEST environment and I have received an error.

If you setup connection from Excel and after selecting specific tables to test go to connection properties.

You will be able yo see connection string. Try to verify it with your query and see if it is changing any parameters in connection string from your query.

Try it and let us know any progress in it.


What Is the Next Best Ingredient to Add? I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients.

One of the features of this program is a function I’m calling the “NEXT BEST INGREDIENT” or NBI.  For example, if you already have salt, oil, and rosemary, what NBI will unlock the most additional recipes?

Let’s say the answer is beef.  If you buy beef, you’ll be able to create more new recipes than if you bought any other ingredient.

After buying beef, what is the NEXT BEST INGREDIENT one should add after that?  And so on.

The feature will allow a user to enter any number of starting ingredients (including zero).  So someone could start with 0, 3, or even 50 ingredients.  What should they add next?

I’m using a database of recipes (about 500K recipes in all) to compile results.

I can map out the individuals steps for writing out a specific algorithm.  But I need help writing a more generic algorithm.

Here’s a specific algorithm:

A user enters in 3 ingredients A, B, and C

Isolate all 4-ingredient recipes that use use A+B+C + BLANK.  Note the BLANK with highest frequency.
Isolate all 3-ingredient recipes that use A+B + BLANK… A+C + BLANK… B+C + BLANK. Note the BLANK with highest frequency.
Isolate all 2-ingredient recipes that use A + BLANK….B + BLANK…C+ BLANK. Note the BLANK with highest frequency.
Isolate all 1-ingredient recipes that simply use BLANK. Note the BLANK with highest frequency.
Calculate the BLANK with the highest TOTAL frequency.  This is the NEXT BEST INGREDIENT.

But I need a way to write a more generic algorithm for when a user enters N number of ingredients (anything from 0 ingredients to 100).

I can write out the rules in plain English – but my programmer needs a way to write a generic rule using coding logic.

Here it is in plain English.

When user searches N ingredients (including zero), instantly ignore any recipes that require N+2 ingredients or more.  And then isolate ALL remaining recipes that use ALL, SOME, or NONE of those ingredients such that there is 1 (and only 1) vacant slot for an “unsearched” ingredient.
Tally up the frequency of all “unsearched” ingredients.  Whichever “unsearched” ingredient has the highest frequency becomes the NEXT BEST INGREDIENT.

The goal is to make this search feature accurate (or as accurate as my database is) and fast.  A few ingredients is easy.  But when looking at 10+ ingredients, it may slow down a bit.

Any thoughts?

If you assume a 3NF design in the entity relationship diagram below:

Recipes are stored in a Recipe table, a recipe's ingredients are stored in  RecipeIngredients table, and the MyIngredients table contains an entry for each ingredient on hand.

Your Next Best Ingredient(s) is found with the following query:

SELECT Ingredients.IngredientID
  , Ingredients.IngredientName
  , count() RecipesCompleted
FROM Ingredients
 INNER JOIN RecipeIngredients on Ingredients.IngredientID = RecipeIngredients.IngredientID
 INNER JOIN (
   SELECT RecipeID
     , count() RemainingIngredients
   FROM Recipes
     INNER JOIN RecipeIngredients on Recipes.RecipeID = RecipeIngredients.IngredientID
     LEFT JOIN MyIngredients on RecipeIngredients.IngredientID = MyIngredients.IngredientID
   WHERE MyIngredients.IngredientID is null
   GROUP BY RecipeID
   HAVING count(*) = 1) RecipesWOneIngLeft
      on RecipeIngredients.RecipeID = RecipesWOneIngLeft.RecipeID
 LEFT JOIN MyIngredients on RecipIngredients.IngredientID = MyIngredients.IngredientID
WHERE MyIngredients.IngredientID is null
GROUP BY Ingredients.IngredientID
 , Ingredients.IngredientName
ORDER BY RecipesCompleted DESC

Let's break the query down a bit.  It works using the following theories:

By inserting your ingredients into the MyIngredients table allows, you're allowed to have N ingredients.
The next step is to use your algorithm to identify all recipes which are missing just one ingredient.  This is done in the RecipesWOneIngLeft sub-query
The final step counts the number of recipes which are completed by each ingredient and then orders the result so the NBI is first.

Consider rewording your plain english algorithm to the following:

When user searches N ingredients, isolate ALL remaining recipes such that there is 1 (and only 1) vacant slot for an “unsearched” ingredient. Tally up the frequency of all “unsearched” ingredients. Whichever “unsearched” ingredient has the highest frequency becomes the NEXT BEST INGREDIENT.


Oracke RMAN: Recover DB when backup folder has changed Scenario:

RMAN backp where made in this folder: /xxx/yyyy
Database was accidentally erased.
For weird reason, backup pieces are no longer in /xxx/yyyy, they are now in /aaa/bbb
I do an startup nomount, restore controlfile from new location /aaa/bbb
I put the DB in mount mode.
When I try to restore the BD it fails because backup pieces and not found (restored controlfile says backup pieces should be in /xxx/yyyy )
I try so set CHANNEL DEVICE TYPE DISK FORMAT to the new location (/aaaa/bbb) hoping that RMAN is smart enough to find the backup pieces, but that cannot be done with a backup controlfile.

How can I restore in this scenario where I do have all my backups in another folder than originally backed up to?

The entries of backup pieces are still pointing to /xxx/yyyy in the controlfile.

After you reach MOUNT stage, catalog the backup pieces from their new location. In RMAN:

catalog start with '/aaa/bbb';

After this, continue as you normally would.

Update:

For 9i, the above is not possible, there is no such option in 9i, you can not catalog backup pieces, this feature was added in 10g. The solution is in the below note:

how to restore or clone a 9i database to different host when backup location is different (Doc ID 1451140.1)

It suggests mounting the database (controlfile) with a 10g software, use the above catalog command, then shut down the instance, and continue with the 9i software.

I think it is easier to create a symlink /xxx/yyyy pointing to /aaa/bbb.


How to query status changes in time series? Given

create table rating (
    ID       int identity(1,1) primary key,
    PersonID int,
    Ratingdate date,
    rating   varchar(2)
);

insert into rating values ( 1, '2010-08-04' , 'A3');
insert into rating values ( 1, '2010-08-14' , 'A1');

insert into rating values ( 2, '2010-08-04' , 'G2');
insert into rating values ( 2, '2010-08-14' , 'G1');

insert into rating values ( 3, '2010-08-04' , 'G1');
insert into rating values ( 3, '2010-08-10' , 'G1');
insert into rating values ( 3, '2010-08-14' , 'G3');
insert into rating values ( 2, '2010-08-20' , 'G4');

I want a query, which returns all rows, where the rating changes for a given person.
For example here I want to exclude the row  ( 3, '2010-08-10' , 'G1') because the rating didn't change  since ( 3, '2010-08-04' , 'G1').

I know a solution using a cursor, but I wonder, if it could be done without cursors.
I tried a bit with analytical functions, but didn't found a solution.

Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.

Edit:

Here is the link to connect asking to add the lag and lead functions to SQL Server.

Please vote.

It seems like you want something like

select *
  from (select person_id,
               rating_date,
               rating,
               lag(rating) over (partition by person_id
                                     order by rating_date) prior_rating
         from rating)
 where rating != prior_rating
    or prior_rating is null
 order by person_id, rating_date

which yields the following

SQL> ed
Wrote file afiedt.buf

  1  select person_id,
  2         rating_date,
  3         rating
  4    from (select person_id,
  5                 rating_date,
  6                 rating,
  7                 lag(rating) over (partition by person_id
  8                                       order by rating_date) prior_rating
  9           from rating)
 10   where rating != prior_rating
 11      or prior_rating is null
 12*  order by person_id, rating_date
SQL> /

 PERSON_ID RATING_DAT RATING
---------- ---------- --------
         1 2010-08-04 A3
         1 2010-08-14 A1
         2 2010-08-04 G2
         2 2010-08-14 G1
         2 2010-08-20 G4
         3 2010-08-04 G1
         3 2010-08-14 G3

7 rows selected.


Necessary to use a checksum on a backup and not solely the 'verify backup' option? If want to ensure the integrity of a backup, would it be a necessity to use a checksum on all of my backups and not just the 'verify backup' option?

From MSDN

  CHECKSUM Specifies that the backup operation will verify each page for
  checksum and torn page, if enabled and available, and generate a
  checksum for the entire backup. This is the default behavior for a
  compressed backup.

All the verify backup option does is confirm that the header of your backup is readable, nothing else.

Neither of these options will tell you that you have a good backup! The only way to confirm that your backup is good is to restore it on another system


Pass datatable as parameter in PostgreSQL I have one datatable in asp.net C#. And I want to pass that datatable into PostgreSQL function as a table parameter. How is it possible?

Below example is same but it is in SQL Server. I need same thing but issue is that I have PostgreSQL as back-end, not SQL Server.

  Introduction

  SQL Server Stored Procedures support System.Data.DataTable as a
  parameter. We can pass the DataTable to the Stored Procedure using
  ADO.Net in the same way as we provided using the
  System.Data.SqlParameter class, but needs a few changes in the
  datatype.   Normally we provide DbType of SqlParameter for a normal
  parameter like varchar, nvarchar, int and so on as in the following
  code.

SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@StudentName";
sqlParam.DbType = DbType.String;
sqlParam.Value = StudentName;

  But in the case of a Table parameter, we do not need to provide a
  DbType as the parameter data type. We need to provide SqlType rather
  then DbType.

  Example

SqlParameter Parameter = new SqlParameter;
Parameter.ParameterName = "@PhoneBook";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = PhoneTable;

  The following example receives a list of phone books and stores them
  in a database using ADO.Net. The example retrieves the phone book
  details from the list and stores them into the DataTable and passes
  this table to the Stored Procedure named NewPhoneBook as a parameter.

//Phone book list
List<PhoneBook> PhoneBooks
 //CReating Table
 DataTable PhoneTable = new DataTable();

 // Adding Columns
 DataColumn COLUMN=new DataColumn();
 COLUMN.ColumnName="ID";
 COLUMN.DataType= typeof(int);
 PhoneTable.Columns.Add(COLUMN);

 COLUMN = new DataColumn();
 COLUMN.ColumnName = "ContactNumber";
 COLUMN.DataType = typeof(string);
 PhoneTable.Columns.Add(COLUMN);

 COLUMN = new DataColumn();
 COLUMN.ColumnName = "ContactName";
 COLUMN.DataType = typeof(string);
 PhoneTable.Columns.Add(COLUMN);

 // INSERTING DATA
 foreach (UserPhoneBook UPB in PhoneBooks)
 {
    DataRow DR = PhoneTable.NewRow();
    DR[0] = UPB.UserName;
    DR[1] = UPB.ContactNumber;
    DR[2] = UPB.ContactName;
    PhoneTable.Rows.Add(DR);
 }
 //Parameter declaration
 SqlParameter[] Parameter = new SqlParameter[2];
 Parameter[0].ParameterName = "@PhoneBook";
 Parameter[0].SqlDbType = SqlDbType.Structured;
 Parameter[0].Value = PhoneTable;

 Parameter[1].ParameterName = "@Return_Value";
 Parameter[1].Direction = ParameterDirection.ReturnValue;
 //Executing Procedure
 SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

You can find code reference here

I would never use these objects. They simply don't seem that useful. That said, PostgreSQL does have a method of passing a table to a function via jsonb. Likely that would be the ideal solution for you if you're looking to get the functionality described in Passing a Table-Valued Parameter to a Stored Procedure. All you'd have to do is map the DataTable to JSON, and then pass the JSON to the function.

To convert a Datatable to JSON you can use JSON.net. See also these Stack Overflow answers.

How to convert datatable to json string using json.net?
Convert datatable to JSON in C#

If the Datatable is too big to serialize to JSON, you may have to create a Foreign Data Wrapper around something that exports the DataRow objects, this would start you down the path mentioned in "Streaming Rows with a DataReader". Though if you don't need a server/client model, you can always dump the DataTable to CSV and read it with a Foreign Data Wrapper: to go down that route check out file-fdw.

I actually tried this. I ran into a ton of problems.

It wasn't even easy figuring out how to install a package with nuGet,
Having installed nuGet, despite being the #1 package on nuGet, it doesn't yet work with .NET Core 2.x
Installing the beta version of nuGet, I got it to work.

Converting a DataTable to JSON with .NET Core 2.x,

using System;
using System.Data;
using Newtonsoft.Json;

class Program
{
  static void Main()
  {
    // Get the DataTable.
    DataTable table = GetTable();
    // ... Use the DataTable here with SQL.
    string json = JsonConvert.SerializeObject(table, Formatting.Indented);
    Console.WriteLine(json);

  }

  static DataTable GetTable()
  {
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

    return table;
  }
}

You'd still have to connect to the db and drop the json into an INSERT statement, but that should be easily done.

cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);

Define myfunc(jsonb) on the server, and you should be good: you've passed a DataTable into PostgreSQL.


The Logical Type of The Document in CouchBase How should the logical type of a document get specified in CouchBase? Using a field for the type? Or employing separators in keys like product::app::123id?

Currently I'm putting a document type, inside the document itself, inside a string field named Type like say Product.

But I see this pattern of using separators in document id, like product::app::123id. Done some playing around it, couldn't get the type part (product) from the key (Of-course parsing it, by splitting is possible, which to me seems to have same overhead in both N1QL and views).

So how should the document type (app logic type) get specified?

Env: CouchBase Community, inside Docker on Ubuntu 14.04, using Go client gocb.

I talked about this recently in my blog post on data modeling.

It really depends on how your application will be interacting with your documents. If you're going to use a lot of N1QL, it probably makes sense to use the 'type' field like you are doing. (I don't know what tooling is available for Go, but Linq2Couchbase can help you manage this 'type' field).

However, if you plan to use direct key access a lot (because it's faster or it makes more sense to do so from your application(s)), then you might want to use a meaningful key to convey relationships or other information. E.g. if I have a "product::123", then I can infer that "product::123::locations" is a related document. I don't think there is a way to get "product" from that type of key without some kind of string parsing overhead (SPLIT or SUBSTR N1QL functions). Though it may be possible to design an index for that? You may want to ask the wizards in our N1QL forum.

Or it might make sense to do a combination of both. In general, there isn't a right or wrong way to design your keys. It really depends on the data access patterns you want to use.


Default binary log position for change_master_to in MySQL circular replication? I have two MySQL servers set up with replication - lets say A and B for ease.

Currently B is a slave of A

To allow me to switch over to A in event of failure, I want to make this replication circular, so A is also a slave of B.

I know how to set this up using "change master to", but having read the documentation I am not clear on how to make sure that A starts replicating from B at the very end of B's binlog so it doesn't start to try and apply a bunch of SQL queries to the master that I don't want it to?

Am I making any sense?

(-sorry if I have misunderstood)

(-assuming you are using binlog file and pos).

The key things to make sure of are that your servers have unique server-ids (if you have replication running already then this should be ok).

You also probably want to set:

auto_increment_increment
auto_increment_offset

values so that the two Databases don't generate overlapping primary keys.

http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html

After that on your Slave DB (B), simply run SHOW MASTER STATUS.

Use those details to issue the CHANGE MASTER statement on DB A.

What you should find is that anything executed on A is then replicated to B and vice versa.

However anything executed on A shouldn't be replicated back from B to A.

And as such, the only queries written back to A will be those that are executed directly on B.

Finally, you will want to set B to read_only to prevent anyone accidentally writing to it, until such time as you want them to.


What is the best use of memory in MySQL? I have a MySql server with 2Gb of memory. What's the best configuration to use then with best results? How can I "distribute" this memory fine to MySql server?

This strongly depends on the application using the database. A good starting point for optimization is the MySQL Performance Tuning Primer Script


How should I model an "either/or" relationship? Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has attributes such as license, source code url, etc.

So if I want to model another entity, OperatingSystem, how should I do it? There is an "is a" relationship to Software but an "either/or" relationship to FreeSoftware and NonFreeSoftware.

I think I'm missing something in the way I'm analyzing this hierarchy.

The way to manage this is that your sub-types have to be determined by the super-type (i.e. the PK of the sub-type is also a FK from the sub-type to the super-type.)

The challenge is understanding whether something is truly mutually exclusive or not.  The attributes of sub-types should apply only to those sub-types, but it may well be that some sub-types are mutually exclusive and some aren't.

If you have some mutually exclusive sub-types, then you can use a partitioning attribute on the super-type to indicate which of the (two or more) mutually exclusive sub-types apply.  This partitioning attribute can be used in with constraints or triggers to enforce the mutual exclusivity.

If you have sub-types that are not mutually exclusive, then they can exist without using any partitioning attribute.

Consider this data model:

You have three super-types, but the FREE_SOFTWARE and NON-FREE_SOFTWARE types are mutually exclusive, based on the SOFTWARE.free_not_free flag partitioning attribute.  Any given piece of software is also potentially an OPERATING_SYSTEM, regardless of whether or not it is free.


Insert into table from a query to a linked database hangs I have a schema with some database links to other schemas that we use to pull data from.  We pull the data into a staging table, massage it, and then do some merges into our schema/database.

The data in the linked schema is a little much to pull into test systems, so I want to only get the first 10000 records, which is easy enough.

select * from
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

This returns very quickly as in under 2 seconds.

However when I try to insert this into my staging table, it causes SQL Developer to hang.  Not a windows hang, the Script Output window just shows "ScriptRunner Task" with the moving bar back and forth showing that it's doing something.

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
select * from
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

When it does this I can see that there are DW locks, but nothing else is using the linked databases that I'm trying to query.

Any help would be greatly appreciated as I am very much a novice with Oracle.

Based on your question and other discussion comments, I didn't see any clue for network and blocking issues.

Here, I'd like to give following approaches to try which can help in your situation:

Approach#1:

Suspecting performance issue because of indexes in STG_TRANSIT table. In the sutuation of big STG_TRANSIT table
and there are indexes available, what if you might drop them and rebuild them after populating tables during off hours.

Approach#2:

Create temporary table -

create table temp_STG_TRANSIT
as
select * from
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

Perform bulk insert -

insert /* APPEND */ into STG_TRANSIT select a.* from temp_STG_TRANSIT as a;

Approach#3:

Trying with DRIVING_SITE hint which forces query execution to be done at a different site than the initiating instance, following query pattern can help in your context:

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
SELECT /*+ DRIVING_SITE(X) */ *
FROM
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
) X

Refer link for the details of DRIVING_SITE hint.


Advice on transaction log based reporting server I am in need of advice on setting up a remote read-only MSSQL 2008 reporting server using transaction logs.

From what I gather, true TLS setup requires both database servers to be aware of each other however in my case the server where the logs will be applied is in no way connected to the primary server.

I am using the following steps to replicate data to the reporting server approximately every 15 minutes.

Download incremental or full backup file posted to a SFTP server
Put database into single user mode
Apply backup (with replace, standby)
Put database into multi user mode

The problem I am having is the database is unavailable to applications during the period when the database is in single user mode. Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?

Thanks!

Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?

You need to write some manual code and change backups .Please see below article which we are also applying from Aaron bertrand

http://sqlperformance.com/2014/10/sql-performance/readable-secondaries-on-a-budget


Read Concern Majority when majority nodes are down I have a 6 node replica set (all of them data bearing) with 5 voting members. If I lose 3 voting members, I will not have a majority voting members and my replica set will become read-only.
In this scenario, if the application has a read concern of "majority", will the reads fail? I have read that this majority read concern introduces a "cache pressure" but will the reads fail?? Or do I need to disable the RC by the below options for basic reads (with majority) to work?

--enableMajorityReadConcern command line option to false.

replication.enableMajorityReadConcern configuration file setting to
false.

Using a read concern of "majority" does not cause the read operation to be submitted to multiple nodes.
When replication.enableMajorityReadConcern is true (default in MongoDB 3.6+) each node will note the most recent write operation that has been acknowledged by a majority of the voting nodes, and keep a snapshot of the data at that point in time.
A request using "majority" read concern instructs the mongod to read from that snapshot instead of the most recent write.
In your scenario, reads will continue normally with 3 nodes down.
In addition to keeping the snapshot at the latest majority commit, each node must also accept writes.  Keeping track of the not-yet-majority writes in addition to the majority snapshot requires some additional usage of cache.  If a cluster gets into a state where there is a primary and is able to accept writes, but is unable to replicate those writes to a majority of the voting nodes, this cache usage can increase to the point of causing system instability.
That can only occur when there is a voting member that does not contain data, i.e. an arbiter.
Since your scenario does not contain an arbiter, that cluster should not have the RCM-induced cache pressure issue.


How to reset statistics after UPDATE STATISTICS ... WITH ROWCOUNT For query tuning and testing purposes, you can manually assign a rowcount and pagecount to a table's index statistics by running UPDATE STATISTICS. But how do you recompute/reset the statistics to the table's actual contents?

--- Create a table..
CREATE TABLE dbo.StatTest (
    i      int NOT NULL,
    CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i)
);
GO

--- .. and give it a thousand-or-so rows:
DECLARE @i int=1;
INSERT INTO dbo.StatTest (i) VALUES (@i);

WHILE (@i<1000) BEGIN;
    INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest;
    SET @i=@i*2;
END;

A dummy query:

SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;

... will return the following query plan (the row estimate in the Index Scan is 1024 rows).

Run the UPDATE STATISTICS command..

UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

... and the plan looks like this, now with an estimate of 10 million rows:

How do I reset the rowcount to the actual contents of the table without using WITH ROWCOUNT?

I've tried WITH FULLSCAN, WITH RESAMPLE and WITH SAMPLE n ROWS, but the statistics rowcount remains 10 million rows. Inserting a row or even deleting all of the rows doesn't update the statistics, because the change is too small.

Use DBCC UPDATEUSAGE with the COUNT_ROWS option.

DBCC UPDATEUSAGE
(   { database_name | database_id | 0 }
    [ , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } ] ]
) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]

Documentation


How to display all entries that have had a box ticked since a particlar date Good afternoon,

I would be very grateful is someone would be able to offer some assistance to a query I have regarding some SQL I'm needing to implement.

In our database we have a range of customers that have specific properties/category tick boxes associated with them. We're wanting to display a list of all customers that have a certain tick box selected/ticked since a set date. This date is 05-07-17. Would this be possible? Kind of lost as how to display this. Se screenshot below..

Kind regards,
Chris

Select customer_field_names from database WHERE date_column >= '2017-05-07' AND tick_mark_field is NOT null;

That should show all listed fields where the date is at least 05/07/2017 and the check mark box is filled in, in some way. This assumes that they are in the same table.


Connect to PostgreSQL via a ssh tunnel using Cygwin I would like to connect to a Postgres server installed on a remote server using an ssh tunnel.
I have Windows so I use cygwin for SSH.

I do the following on cygwin:

# Create the tunnel
ssh -f user@remote_server -p ssh_port -L 5433:127.0.0.1:5432 -N

On pgAdmin, I set the host to localhost and the port to 5433 as well as the username/password/db.

When I try to connect, it is running, and then it displays an error message:

Error connecting to the server: server closed the connection unexpectedly.

On cygwin, I see this message:

Write failed: Broken pipe

The ssh process has actually been killed so I guess it crashed? But why?

How could I get more information to know what the problem is and especially where (pgadmin or ssh tunnel on cygwin...) ?

Thanks

I was finally able to make it work by removing the -f option when creating the SSH tunnel.

For an unknown reason, the error appears when using the -f option but it works fine otherwise and I can connect via pgAdmin using the SSH tunnel.


Is there a better alternative than LIKE for this query? I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is:

SELECT * FROM
TableA
WHERE LEFT(acccode,3) IN ('121','131','141','151','161')

Now, I want this condition to be true for all these codes except for one. Say, for the first one, I want it to look four more characters for anything like '121-111%'. How can I alter my IN operator there or do I have to just go with a LIKE operator with something like:

SELECT * FROM
TableA
WHERE acccode LIKE '121-111%'
    OR acccode LIKE '131%'...

and so on...?

To clarify: codes starting with 121-111, 131, 141, 151, 161 are the ones I want to select.

All columns are nvarchar.

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

In this case you could also consider

SELECT * FROM
TableA
WHERE acccode LIKE '1[3-6]1%'
      OR acccode LIKE '121-111%'

As a simplification though the fully expanded version might be more efficient.

If you have no useful index you might prefer just using

SELECT * FROM
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161')
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.


Is a partition a relation? Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?

When you Partition a table, physical tables are created and when you query table with where predicates on partition only partition tables queried, you can also query a particular partition.


Select value based on a test I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address.

I need to select all columns from the first table and another field 'blacklisted' that tells if the ip in that access log is blacklisted or not.

I used a left join and COUNT() function to count occurences for each ip address but this will return a number, while i just need a boolean.

Here is my SQL code:

SELECT usl.*, COUNT(blip.id) as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id

Use a CASE if you want, but you can always check if COUNT() > 0

SELECT usl.*,
    CASE WHEN COUNT(blip.id) > 0 THEN TRUE ELSE FALSE END as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id;


You mention splitting at the middle, but your requirement is adding a boundary to the beginning (before the first existing boundary). Consequently, no costly data movement will be needed as long as no existing values are less than or equal to 20140100. I'll assume that is the case here.

Since the first partition is on the wrong filegroup (PRIMARY), first move it to the proper monthly filegroup:

--move first partition to proper filegroup
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    MERGE RANGE(20140100);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201312];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20140100);

Then, create the needed historical partitions before loading data:

ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201311];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131200);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201310];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131100);
ALTER PARTITION SCHEME [ps_monthly_dateid]
    NEXT USED [201309];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131000);
...

The general best practice is to plan such than only empty partitions are split. Data movement performed during SPLIT and MERGE to changed boundaries requires about 4 times logging as normal DML. So also consider creating an empty partition on the proper filegroup (even if no files) in anticipation of loading prior data.


MySQL Permissions Necessary To Reset Password On my team there is a desire for some users to periodically update their MySQL password.

Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm correct, changing your password requires access to the mysql.user table (which would, in turn allow the user to modify the password of any other user on the server.)

If this is all correct, is there any way to secure the database in a way that a user can update their password, but not other users?

A user should be able to change his/her own password strictly using SET PASSWORD.

For a connected user to set the new password to mynewpass, just run the following:

mysql> SET PASSWORD = PASSWORD('mynewpass');

According to the MySQL Documentation on SET PASSWORD, if the server is a read-only enabled server, you need SUPER privilege to do this. Otherwise, you can do this any time. There is no need for another user to set someone else's password. If you need a super user to set it you can still use SET PASSWORD.

To set the password of 'someuser'@'10.1.2.30' to hisnewpass, run this:

mysql> SET PASSWORD FOR 'someuser'@'10.1.2.30' = PASSWORD('hisnewpass');

According to the MySQL Documentation on SET PASSWORD, this is the equivalent of:

UPDATE mysql.user SET Password=PASSWORD('hisnewpass')
WHERE User='someuser' AND Host='10.1.2.30';
FLUSH PRIVILEGES;

Using SET PASSWORD does not warrant manipulating mysql.user.


Difference between Hash, Merge and Loop join? In SQL Server you can specify the join hints:

HASH JOIN
MERGE JOIN
LOOP JOIN

What is the definition of those three join hints, and when should each be used?

From MSDN, in the topic of Advanced Query Tuning Concepts:

  SQL Server employs three types of join operations:

  Nested loops joins
  Merge joins
  Hash joins

  If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons. For more information about nested loops, see Understanding Nested Loops Joins.

  If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other. For more information, see Understanding Merge Joins.

  Hash joins can efficiently process large, unsorted, non indexed inputs.

But I believe that you should start with a more basic topic: Query Tuning and lastly go to using the query hints.


Amazon RDS - and binary log rotation New to RDS (used to having my own server in my own data center).

We are on MySQL 5.6 on RDS.

when a slave gets behind in replication lag, the master ceases to rotate out its binary logs.  It is, apparently, waiting to every slave to commit every transaction before rotating binary logs out.   I don't have a problem with this conceptually (as long as you have monitors on disk space on the master, its a good thing).

My question is academic -- what causes this to happen?
** I know with ASYNCHRONOUS replication, the master is unaware of the slaves' status

is this a function of synchronous replication? semi-synchronous replication?

I'd love some insight and perhaps a pointer to a white paper somewhere (I can not find one that addresses this specific issue of binary logs not being rotated out)

This doesn't appear to be anything intrinsic to MySQL Server, so I can't give you a proper citation on what "causes" this to happen -- but I strongly suspect it is an element of the design of RDS.

If you take a look at SHOW FULL PROCESSLIST; you'll notice there's always a user connected called "rdsadmin."  If you look in the mysql schema, you'll spot a table called rds_heartbeat2, with a recent epoch time x 1000 stored in its single row.  This is changed every few minutes, and my reasoned speculation is that that "rdsadmin" is doing this.

I selected a binlog at random to check this theory, and here's the first event:

use `mysql`; INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1393029075007)
ON DUPLICATE KEY UPDATE value = 1393029075007

I'll speculate, then, that the "rdsadmin" user is flushing the binlog, then immediately writing a new value to this table.  Reading the value from this table on the slaves would give the RDS supervisory systems a mechanism for determining/monitoring the slave's behavior, allowing it to purge binlogs that it knows have been fully processed or let them linger on the master if they haven't, so that a slave would not be caught without the necessary logs being available.

Instead of letting MySQL age out it binlogs on its own based on the global varible expire_logs_days, RDS seems to be managing this process externally, and quite possibly this process is responsible for archiving the logs out of sight so that they can be used for the native RDS point-in-time restoration feature.


Aggregate function / query in Oracle 11g I am working in Oracle 11g.

I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert)

I have written an inline view (with tons of LEFT OUTER JOINS and CASE statements) that sets up the following scenario:

ID (non-unique)  |  Received
----------------------------
        1               Y
        1               N
        2               N
        2               N
        2               Y
        3               N
        3               Y

I need to query the inline view:

If each ID has at least 1 row with a 'Y', return 'Y'.

Otherwise return 'N'.

So for the scenario above, I would return a 'Y'.**

I thought an aggregate function would work, and I've been reading through GROUP BY, ANY/ALL, and Count.  However, I did not see

Not asking anyone to write my SQL for me ... if anyone could even suggest a function or technique or article I could read / run down.  I guess I don't know enough about complex SQL to know what to search for.

If this is not possible to write, I guess I could look into doing it as a PL/SQL Function.  I know that I can use a cursor and track things with variables.  However, I thought it would be more efficient to write it as pure SQL (seem to recall the Oracle Optimizer can't see into functions/procedures).

Thank you very much in advance,

Philip

Simple, but "dirty":

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 3 as id, 'Y' as received from dual
)
select min(max(received)) from t group by id;

M
-
Y

Change input (e.g. change id to 4 in last line):

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 4 as id, 'Y' as received from dual
)
select min(max(received)) from t group by id;

M
-
N


Database is not accessible as transaction log file growing to full On one of our current prod servers, we have one of the data drives going out of space.[Somehow we have created 2 GB space for now]

Now we are planning to move one of the database in different drive which is almost 20 GB. But this database is not accessible state. I was thinking of backup/restore way to move, but it wont as it shows in unavailable state.

When I checked the SQL logs, it says transactional log of this database got full. So I checked its state using log_reuse_wait_desc which says "ACTIVE TRANSACTION".

How can I get the database available and ready for move so that I can move via backup/restore?

Additional Info: Database status shows "SUSPECT"
Please suggest.

First things first, if you need to kill the active user(s) immediately and make sure no more connections are made, you'll want to put the DB in single user mode.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Second you'll need to address the SUSPECT mode and hope it's an easy to fix file access issue.  Try these suggestions to fix your SUSPECT mode

Once that is fixed, do what you need to do with the database - CheckDB, backup, etc.  By keeping it in single user mode other users, including applications and any other user but you, will not be able to access the database.

To put it back in multi user mode use

ALTER DATABASE MyDatabase SET MULTI_USER

EDIT - I should say this does not resolve why you have an ever growing transaction log.  If it is of concern, you should try to resolve that prior to killing an active transaction with sp_whoisactive, monitoring tools, etc.  But don't let the log fill the drive or it will kill the transaction anyway and roll it back.


Design advice regarding a users/groups/tasks database scheme Here are my requirements:

one user can have many tasks
one group can have many task
one group can have many users
only invited user by a user can be added into a group

Does this db design appear to model these requirements?

In group table, group_id should be the FK of which? there are 2 table linked to that table. 1 FK to 2 PK? possible?

Try these design which will satisfy your requirements.

Group

 - Group Id Primary Key
 - Group Name)

User

- User Id Primary Key
- Name
- Email
- Created on
- Group Id references Group(Group Id), Referred User ID references User(User ID))

Task

- Task ID Primary Key
- Name
- Priority
- Date)

User-Tasks

- Relation ID Primary Key
- User ID references User(User ID)
- Task ID references Task(Task ID))


Any workarounds for lack of primary key on partitioned tables? I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not supported. Is there any workaround or suggestions for alternative ways of doing this anyone can suggest? Or should we use table inheritance instead.

Thanks.

The most common solution is to make an exception and not add the foreign key. I guess this must be very large tables and there are usually not that many of them, so a few exceptions for better performance are typically acceptable. You could try to enforce the foreign key relationship with triggers, though it is messy. I don't know of a better alternative for PostgreSQL 10.


DB2 PureScale on Linux Searching for DB2 PureScale supported hardware, I can see that for Linux distributions only, some System X and IBM BladeCenter BladeCenter HS22 are listed.

If I try to install PuseScale on a Fujitsu hardware, will it work. will IBM support it?

For future reference, after asking IBM we verified that DB2 10.1 PureScale feature cannot run on Fujitsu, only the hardware listed in the manual.

Maybe in future versions...


ALTER TABLE DROP COLUMN - server status 'Stopped' I am in the process of removing 4 columns from my (MySQL/innodb) table,

simple code,

ALTER TABLE database_name.table_name
DROP COLUMN A,
DROP COLUMN B,
DROP COLUMN C,
DROP COLUMN D;

It has been running for close to 20 hours now. It's a large table 30GB / 8560000 rows, large for what i have experience with anyway, local instance on an old desktop computer.

When I check the Server Status on the Administration panel it is telling me that the Server has 'Stopped'. However in the Action/Respose window at the bottom of the Query window that row is showing 'Running..' and the icon is that spinning/thinking animation you expect.

There is around 4 KB/s Traffic but no read or writes, InnoBuffer usage is at 99.9% which should be around 30GB, CPU is between 1.5 and 2.

I can open another instance of the server and that shows it is running but still has the old columns listed in the Schema. (I have have refrained from querying the db through that instance)

I am just wondering what is happening exactly?
Is this 'normal' and I should just be patient?

Open another connection to the same server (just click the home screen tile again, which you used to open the current connection). It will tell you if the server is available or not. Maybe the UI got stuck somehow and did not update properly when the operation ended. Don't forget the connection could also have timed out and you need to re-connect (which usually happens automatically, but somethings things just go wrong).


SQL Server 2014 AOAG + Replication? I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers participating in the Always On Availability Groups.

Why does the documentation warn against doing that, will it break Always On? Does Always On prevent replication from working? Or is there some other reason?

IN SQL Server 2014 and earlier, the replication jobs will not continue to work after failover if you put the Distribution Database in an AG.

If you upgrade to SQL Server 2016 or 2017, this becomes a supported configuration:

  SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3 introduces support for
  replication distribution database in an AG

Set up replication distribution database in Always On availability group


Is it possible to add a spatial index to a partition with Postgres 10? I am creating declarative partitions like:

CREATE TABLE mydata.my_features (
    id bigint,
    my_feature1 character(1),
    my_feature2 integer,
    ...
    my_geom geometry
) PARTITION BY LIST (my_feature1);

And using some ad-hoc functions:

CREATE TABLE partitions.%I PARTITION OF cores.rents_core FOR VALUES IN (%L) PARTITION BY LIST (my_feature2)

CREATE TABLE partitions.%I PARTITION OF partitions.%I FOR VALUES IN (%L)

The partitions get created and populated successfully once the data get inserted.

However, when I create the table at the beginning, if I try to add an index like suggested in this Postgres official documentation resource:

  Create an index on the key column(s), as well as any other indexes you might want for every partition. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)

like a spatial index:

CREATE INDEX "mydata_my_features_geom_idx" ON mydata.my_features using gist(geom);

I get an error like:

  ERROR:  cannot create index on partitioned table "my_features"

Is that actually possible add this kind of index or I am doing something wrong?

As documented in the manual

  Partitions may have their own indexes, constraints and default values, distinct from those of other partitions. Indexes must be created separately for each partition

(emphasis mine)

So as you are using Postgres 10, you need to create that index manually on each partition (the part you quoted from the manual even shows that the indexes are created for each partition, not for the partitioned table).

With Postgres 11, indexes can defined on the master table and will automatically be created on each partition.

If you need partitioning you should consider upgrading to Postgres 11 anyway as that massively improved the partitioning capabilities (including performance)


Log shipping secondaries in STANDBY stuck in 'Restoring...' - SQL Server 2012 SP3 Yesterday, I configured Log shipping between 2 SQL Server 2012 SP3 servers for a total of 13 databases.

Some specifics:

I'm using 'Azure files' to transfer the log files (CIFS accessible BLOB storage)
I did the configuration using SSMS
All backups got transferred and restored just fine
STANDBY mode (and disconnect users) was used on all databases,
I verified this in msdb.dbo.log_shipping_secondary_databases
The log shipping process worked for all databases yesterday (they were all in read only mode after the first transaction logs were replayed on the secondary (also verified in msdb.dbo.log_shipping_secondary_databases).

This morning, I noticed the following:

Only 5 out of my 13 databases are in read only mode.
The rest is stuck in 'restoring' state.
The databases that are stuck, are those that have a very low load (or possibly no load at all)
In msdb.dbo.log_shipping_secondary_databases, the last_restored_date is not current with the last execution of the agent jobs for the DBs that are stuck.
The agent jobs seem to have executed without a problem (job succeeded) on those databases.

Can somebody give me a clue as to what's going on here?

Please do restore verifyonly of your log backups just to make sure they are restorable. Your issue is similar to the one defined in  Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking. Try those suggestions as well.

Few more things I would like to suggest is, if your log backups are valid try doing restores with NORECOVERY option to make sure the issue is with only STANDBY option. Also, I am assuming this is not an corruption issue. If it is then it is better to rebuild logshipping rather than fixing the corruption. This link should also help you out for some more suggestions: https://social.technet.microsoft.com/Forums/windowsserver/en-US/9c39fc61-0a59-402b-91fb-17ac1f03da7b/sql-2012-log-shipping-to-report-server?forum=sqldisasterrecovery


PgAdmin 4 - How to connect to database via ssh tunnel as posgres when its password is empty? I am trying to connect to a Postgres database at my DigitalOcean instance via PgAdmin 4.

I create an SSH tunnel as root, provide the PgAdmin with my identify file. I want to login into database as postgres user which doesn't have a password.

So PgAdmin doesn't let me log in and requires the password for the postgres user.

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

[

Does user postgres have to have a password if I want to connect via SSH tunnel? Is it possible? And what should I do in order to connect?

Generally it does.

Your SSH tunnel is configured so that the remote SSH server connects to PostgreSQL through a TCP connection to localhost. And generally, a default pg_hba.conf maps this type of connection to the md5 authentication scheme, for which a password is required. Otherwise any user with a shell account could connect to PostgreSQL, which would be wrong as a default configuration.

The more common way to use PostgreSQL through a SSH tunnel is to set a password to the PostgreSQL databases accounts that you need to connect to. Use the \password command inside psql or the ALTER USER SQL statement.


Ola Hallengren's IndexOptimize for Updating Statistics exclude schema I am using Ola Hallengren's IndexOptimize for Updating Statistics only. Can I exclude a schema from the UpdateStatistics?

Ola covers this scenario in the documentation.
For the @Indexes parameter:

The ALL_INDEXES keyword is supported. The hyphen character (-) is used to exclude indexes, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

If you want to do "everything on the server, except one schema in one database" you'd supply parameters like @Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -Db1.Schema1.%'.
If you want to do "everything on the server, except never the staging schema in any database" you'd supply parameters like @Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -%.staging.%'.


This is a terrible pattern, but it doesn't actually break any normalization rules.  The reason is that it's actually a change in what you are modeling.  Instead of your database modeling, say, Employees, it models Entities, Attributes, and Values.


Postgres 9.6 reload or restart after config changes I need to change a couple of log config settings in postgresql.conf. How do I know which parameters need a restart or reload?

Is there a list of parameters that need a restart?

Try this query:

SELECT name, context
FROM pg_settings
WHERE name ilike '%log%';

The pg_settings view gives the context for any setting. sighup is a reload and postmaster is a restart.


User Mapped to a certificate in SQL I need to create a user ,who can log on to a contained DB.In this context what is the benefit of using a user mapped to a certificate?

The benefit of users mapped to a certificate is to use certificate signatures to allow a signed object (like a stored procedure) to have permissions the user executing it doesn't have at all.

For instance, you could have a stored procedure that does something unutterably horrible (SUMMON Cthulu), and you have to allow that for X bad business decision, but you don't want to give Bob permissions to just SUMMON any monster - what if they summoned a gremlin in your system!

In this case, you could create a user mapped to a certificate, and then sign the stored procedure with that certificate.  The certificate mapped user is given the GRANT SUMMON right, while Bob gets only GRANT EXECUTE spSummon_Cthulu.

Thus, Bob can execute spSummon_Cthulu, and that SP is allowed to SUMMON('Cthulu') because the user mapped to the certificate that signed the SP has that right.

WARNING: Every time spSummon_Cthulu is changed, you'll need to resign in.  This is deliberate - that way even if Bob is allowed to alter the procedure, and Bob changes it to SUMMON('gremlin'), the changed SP no longer has a valid signature... and is thus no longer allowed to use the SUMMON command.

P.S. SUMMON is used as a fictional example for simplicity - xp_cmdshell 'bcp xxx' vs xp_cmdshell 'format D:' is a better horrific example, but involves cross-database permissions that are more complex than is necessary.

P.P.S. this may also make more sense if you consider it in the context of a database with multiple schemas not all owned by the same owner.


Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data?

For example, this is exactly what I want to do, except it is a cumulative sum, not a percentile:

select *
from
(SELECT trade_date, daily_val, id, company_id, sum(daily_val)
    over (partition by company_id order by trade_date rows between unbounded preceding and current row) AS sum
FROM daily_data
where (company_id = 858 or company_id = 726) and wh_calc_id = 156) as x
order by company_id,trade_date

This is what I want to do:

select *
from
(SELECT trade_date, daily_val, id, company_id, percent_rank(daily_val)
    over (partition by company_id order by trade_date rows between unbounded preceding and current row) AS pctl
FROM daily_data
where (company_id = 858 or company_id = 726) and wh_calc_id = 156) as x
order by company_id,trade_date

(Note: the only difference between the two is sum() vs percent_rank())

It seems I need a percentile rank function available in PostgreSQL 9.5 that takes a sorted expression argument. I know it exists for more recent versions:
https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

And the 9.3 version doesn't seem to take an argument:
https://www.postgresql.org/docs/9.3/static/functions-window.html

I really don't want to upgrade PostgreSQL as I already have a lot going on right now.

Here is some simplified example output of what I want (created in Excel):

trade_date  daily_val   pctl    company_id
1/1/1900    1484.881621 1.000000    1
1/2/1900    1465.465315 0.000000    1
1/3/1900    1471.244094 0.500000    1
1/4/1900    1475.109578 0.666000    1
1/5/1900    1484.881621 0.750000    1
1/6/1900    1508.863507 1.000000    1
1/7/1900    1519.089431 1.000000    1
1/8/1900    1504.819339 0.714000    1
1/9/1900    1523.210769 1.000000    1
1/10/1900   1514.990335 0.777000    1
1/11/1900   1550.595515 1.000000    1
1/12/1900   1529.454907 0.909000    1
1/13/1900   1546.324863 0.916000    1
1/14/1900   1542.077672 0.846000    1
1/15/1900   1561.396941 1.000000    1
1/16/1900   1519.089431 0.533000    1
1/17/1900   1498.774337 0.312000    1
1/18/1900   1486.847801 0.294000    1
1/19/1900   1488.819195 0.333000    1
1/20/1900   1506.83871  0.473000    1
1/21/1900   1500.777505 0.400000    1
1/1/1900    149.113683  1.000000    5
1/2/1900    155.505015  1.000000    5
1/3/1900    154.15655   0.500000    5
1/4/1900    160.425942  1.000000    5
1/5/1900    156.29799   0.750000    5
1/6/1900    164.058078  1.000000    5
1/7/1900    159.935225  0.666000    5
1/8/1900    158.537455  0.571000    5
1/9/1900    154.147639  0.125000    5
1/10/1900   155.722962  0.444000    5
1/11/1900   155.505015  0.300000    5
1/12/1900   162.766282  0.909000    5
1/13/1900   169.264949  1.000000    5
1/14/1900   167.73179   0.923000    5
1/15/1900   162.280915  0.714000    5
1/16/1900   159.906452  0.533000    5
1/17/1900   161.04602   0.687000    5
1/18/1900   157.656379  0.411000    5
1/19/1900   154.478044  0.166000    5
1/20/1900   154.576546  0.210000    5
1/21/1900   154.147639  0.050000    5

ntile doesn't work either.

In Postgres 9.4+, you can use the hypothetical-set aggregate function percent_rank() in a LATERAL join like this:

SELECT *
FROM   daily_data d, LATERAL (
   SELECT round(percent_rank(d.daily_val) WITHIN GROUP (ORDER BY daily_val)::numeric
              , 6) AS pctl_calc
   FROM   daily_data
   WHERE  company_id = d.company_id
   AND    trade_date < d.trade_date
   ) x
ORDER  BY company_id, trade_date;

The CROSS JOIN LATERAL (, LATERAL for short) is safe, because a subquery with an aggregate function always returns a row. (Else you might want LEFT JOIN LATERAL (...) x ON true.)

Minor difference: The first row in each group returns 0, not 1 like in your example. Easily fixed, if you need it.

This would do the same job in Postgres 9.3:

SELECT d.*
     , round(CASE WHEN x.ct = 0 THEN numeric '1'  -- or 0 ?
                  ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM   daily_data d, LATERAL (
   SELECT count(daily_val) AS ct
        , count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
   FROM   daily_data
   WHERE  company_id = d.company_id
   AND    trade_date < d.trade_date
   ) x
ORDER  BY company_id, trade_date;

I use count(daily_val) for the full count, not count(*) to ignore NULL values - necessary if daily_val can be NULL, .

SQL Fiddle.

Or, without LATERAL join for even older versions. May be faster, too:

SELECT company_id, trade_date, daily_val, pctl
     , round(CASE WHEN ct = 0 THEN numeric '1'  -- 0 ?
                  ELSE ct_lt / ct END, 6) AS pctl_calc
FROM  (
   SELECT d.*
        , count(x.daily_val) AS ct
        , count(x.daily_val < d.daily_val OR NULL)::numeric AS ct_lt
   FROM   daily_data d
   LEFT   JOIN daily_data x ON x.company_id = d.company_id
                           AND x.trade_date < d.trade_date
   GROUP  BY d.id  -- pg 9.1+
   ) sub
ORDER  BY company_id, trade_date;

Assuming id is the PK for a simplified GROUP BY, requires pg 9.1 though.


Selecting Records from a duplicate column whose value in another column is less than Below is an example of my table:

  Column A         Column B       Column C
    Tom              Blue           10
    Tom              Orange         12
    Alex             Blue           30
    Alex             Orange         29
    Lisa             Orange         15
    Lisa             Blue           14

Output:

Column A      Blue        Orange
 Tom           10           12
 Lisa          14           15

The output above returns when there are two duplicate values in Column A, Column B Blue value in Column C is less than Column B Orange value in Column C.

So far I have... select Column A, Column B as "Blue", Column B as "Orange" from table where Blue < Orange;. Obviously where Blue < Orange doesn't work but that's the logic.

I think I need to write a subquery but I cannot figure this out.

Create Table

create table t1(
col1    varchar2(20),
col2    varchar2(20),
col3    number
);

Sample Data

insert into t1
select 'Tom','Blue','10' from dual
union all
select 'Tom','Orange', '12' from dual
union all
select 'Alex','Blue','30' from dual
union all
select 'Alex','Orange', '29' from dual
union all
select'Lisa','Orange','15' from dual
union all
select 'Lisa','Blue','14' from dual;

Select Query

select * from
  (
      select *
      from t1
  )
  pivot
  (
    MIN(col3) for col2 in ('Blue' as blue,'Orange' as orange)
  ) where blue<orange;

Output

COL1  BLUE    ORANGE
Tom   10      12
Lisa  14      15

dbfiddle


Removing InnoDB indexes causes database table almost not accessible and slow I have a server with 32GB RAM. I have given almost 70% InnoDB buffer pool to it. I have table with almost 40 million rows. Recently, I tried to remove one of the indexes and replace it with another. My whole system could not work any more and it was not able to see my db.

I could see a lot of this error in my application:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after statement closed.

Also I saw a lot of too "many file open" errors on my application due to this process.

I had to stop the database, truncate the table, and start afresh. Is this an InnoDB limitation? In future, what can I do to avoid this? What other limitations do I need to be aware of?

Is it not advisable to remove indexes when the size reaches big as few millions like this?

Is it a good reason for my to swap to TokuDB?

Below is my table description:

CREATE TABLE IF NOT EXISTS `mainData` (
  `mainDataID` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `gpdateTime` datetime NOT NULL,
  `linkID` int(5) NOT NULL DEFAULT '0',
  `eventID` int(11) NOT NULL DEFAULT '0',
  `dataInsertDateTime` datetime NOT NULL,
  PRIMARY KEY (`mainDataID`,`dataInsertDateTime`),
  KEY `gpdateTime` (`gpdateTime`),
  KEY `linkID` (`linkID`),
  KEY `eventID` (`eventID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50425561 ;

This was on a running production system with heavy inserts going in so that is why all almost halted. When tables reach a huge sum in million how to best to say remove and replace a new index?

My version is 5.1.73. I know kind of old; as you know CentOS goes for stability. What I did was removing the gpdateTime index via PHPMyAdmin and everything started to fall apart. My OS which I am running is Centos 6.8.

5.1 ALTERs work faster if you perform them simultaneously:

ALTER TABLE mainData
    DROP INDEX foo,
    ADD INDEX (col1, col2);

You can perform that in the UI window that lets you perform arbitrary SQL.  It will be twice as fast (in older versions) than doing the DROP and ADD separately.  This is because each ALTER (not each clause in it) involves a complete copy of the data, plus a complete rebuild of the resulting set of indexes.


Disable / bypass authentication in PostgreSQL I need to run benchmarks (pgbench) in different environments on different versions of PostgreSQL. Authentication works inconsistently and is a generally huge headache / adds a lot of fragile workarounds around the code that has no reason to care about access to the database.

Is there any way I could do something similar to MySQL:

update user set authentication_string=password(''),
    plugin='mysql_native_password'
    where user='root';

and be done with it forever?

I would also be interested in being able to run the database and utilities as super-user (root).  Again, I have no interest in all the security dance, there's nothing to protect, but the price for authentication is too high (weeks of work in order to make scripts work reliably, while they aren't even the main focus of my work).

The default Linux distribution of PostgreSQL (https://www.postgresql.org/download/) creates the system user "postgres", simply by installing the package from there.
It also has the default behaviour, that this user may access the database without password.

This is set up via the config file pg_hba.conf (https://www.postgresql.org/docs/9.2/auth-pg-hba-conf.html):

# Rule Name: local access as postgres user
local   all     postgres                                ident

So, on Linux you simply can change to this user with

  su - postgres (for root users)

or

  sudo -u postgres -i (for sudo users)

Windows has a similar command "runas" since XP/2003.

When connecting to Postgresql server via a command like

psql

the server asks the operating system for the executing user and that's it.
You won't even need to set a password.
Because pg_hba.conf defines local superuser access for this user.

createdb test
pgbench -i test
pgbench test