Changing the default schema of a user In order for an application user (APP_USER) to use another schema (SCHEMA_OWNER) without the need for prefixing queries with the schema name, I have implemented a logon trigger that changes the session parameter CURRENT_USER.

This works fine except for sequences. When I do either of

select my_seq.nextval from dual;
insert into foo(bar) values(my_seq.nextval);

... I get the error

SQL Error [1031] [42000]: ORA-01031: insufficient privileges

Any ideas on why this may be and how to solve it?

Two possibilties comes to mind, maybe I need to

grant select privileges to SYS.DUAL of SCHEMA_OWNER? or
use the SESSION_USER instead of the deprecated CURRENT_USER parameter/variable?

Unfortunately I do not have admin rights on the database, else I would test it myself. I must work through a DBA, which means I have some difficulty in trying out different solutions and ask the question here in the hope to speed up the process.

Please note that workarounds like creating Oracle synonyms or views is not an option and likewise I cannot prefix all queries with the schema name since the application is a packaged product. Running as the schema owner is not an option either because of enterprise policy.

Sounds like you don't have SELECT access on the sequence. Ask the DBA to run:

GRANT SELECT ON SCHEMA_OWNER.MY_SEQ TO APP_USER;

Can you edit your post with the full error message you're receiving?

Regarding your comment. I suspect you've got SELECT ANY TABLE etc privs. These don't apply to sequences, as demonstrated below:

PHIL@PHILL11G2 > create user test1 identified by test1;

User created.

PHIL@PHILL11G2 > grant connect,select any table to test1;

Grant succeeded.

PHIL@PHILL11G2 > create sequence testseq;

Sequence created.

PHIL@PHILL11G2 > conn test1/test1;
Connected.
Loading glogin.sql

Session altered.

TEST1@PHILL11G2 > select count(*) from phil.bigtable;

  COUNT(*)
----------
   2300640

TEST1@PHILL11G2 >

TEST1@PHILL11G2 > select phil.testseq.nextval from dual;
select phil.testseq.nextval from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges


Distribution database (recovery pending) I was trying to create a distribution database and I succeded at first, but since I checked the box to configure SQL server Agent to start automatically, the last step of configuring the distribution failed, but the distribution database was created.

However, I haven't realized that, and in order to repeat the process from scratch, I deleted the data files and log files of the distribution database even before dropping the database, and I didn't realize that distribution database will be created in system databases. After deleting the mdf and ldf files I see recovery pending besides that database. Even after I tried to drop the dristribution database using sp_dropdistributor I still see the icon of that database with recovery pending. I just want to get rid of that database. What should I do?

Can you try below  (substitute with appropriate names - publisherdb, publisher, distribution database) :

-- Disable the publication database.
USE [AdventureWorks2012]
EXEC sp_removedbreplication @publicationDB;

**-- Remove the registration of the local Publisher at the Distributor.**
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO


How to Monitor Transaction Log Usage In SQL Server? I would like to monitor transaction log usage, regarding all the following aspects:

What Task\Job\Query is making it fill the drive\file.
Log file usage percentage.
What time the transaction happened.

Any relevant way of doing this which is tested would be helpful.

The easiest way is to buy an off-the-shelf monitoring tool. They all give you this kind of information - Idera SQL DM, Quest Spotlight, SentryOne SQL Sentry do these kinds of things with really low impact.

The next easiest way is to build something yourself. If you're going that route, I'd start by logging sp_WhoIsActive to a table - especially with the @get_transaction_info = 1 switch.

If you try the roll-your-own approach, you need to be aware that queries aren't the only thing that will cause the transaction log to grow. For example, if you're using replication, database mirroring, or Always On Availability Groups, SQL Server needs to retain history when one of those replicas is offline. To learn more about what's causing that, check log_reuse_wait_desc:

SELECT name, log_reuse_wait_desc FROM sys.databases;


How to search for a specific column name in all the tables in MySQL Workbench? In MySQL Workbench, is it possible to search for a specific column name in all the tables?

(Writing the string to look for in the field at the top right does nothing).

Thank you.

You can use the INFORMATION_SCHEMA database and the COLUMNS table in particular Example of use:

SELECT
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE table_schema = 'myDatabase'     --- the database you want to search
  AND column_name = 'name' ;          --- or: column_name LIKE '%name%'


How to troubleshoot "client driver has sent multiple requests while the session is in single-user mode" I'm working with a developer who is struggling to stop the following error:

  The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

When this error occurs, a typical outcome is a session that is holding locks, but has no SQL running (no row in sys.dm_exec_requests for the session_id), and causes blocking until it is killed. The cause of the blocking is straightforward, but how to stop the error from occurring is not.

Some facts about the problem:

The client is using Entity Framework (yes, I know...), with MARS enabled (yes, I know...)
SQL Server version is 10.00.5844 (SQL Server 2008 SP3 CU12). They probably won't upgrade major versions without extensive testing, but applying a newer CU is viable.
The client program shows up in sys.dm_exec_connections as .Net SqlClient Data Provider
The open connection appears to definitely have MARS enabled, as net_transport in sys.dm_exec_connections shows Session.
I'm told the application's connection string has asynchronous processing enabled, per Matt Neerincx's comment in this thread (I don't have access to the application servers to confirm this myself):

  If you set Async=true on the connection string it should prevent this problem.   If you are using MARS and using the same connection on multiple concurrent threads what can happen is the call to reset the connection can get slightly delayed and trigger this error.  If you set Async=true we perform additional locking in the client driver to prevent this from happening.

Further information about this error is scarce. It appears to suggest that the client's program is calling sp_reset_connection while a batch is still running. I could set up a trace to confirm this, but I'd have to record quite a lot of unrelated activity, and the problem is only happening once every few days. Further, I'm not sure that the developer would be able to take advantage of this evidence in order to solve the problem.

Is there any other technique I can use as a DBA or sysadmin to troubleshoot this further, or anything I can suggest to the developer that might provide him with useful information or make the problem less likely to happen?

I don't see it stated anywhere as to what version of the .NET Framework is being used, but given that this question was asked in May of 2014, and .NET Framework version 4.5 came out on 2012-08-15, the following note on SqlConnection.ConnectionString for the Asynchronous Processing / Async keyword seems relevant:

  This property is ignored beginning in .NET Framework 4.5.

So, it might help to find out the exact version of Entity Framework being used, and the target version of the .NET Framework.

However, it kinda sounds like this issue might be a result of connection pooling. I suspect connection pooling because after the error message about the connection being closed, the session was still around.

The first thing to try is to simply disable connection pooling by adding the following to the connection string:

Pooling=false;

If the problem never comes back, then this was most likely the issue. I hesitate to say that this was the issue since it isn't exactly proof. It could have merely reduced the frequency from "once every few days" to once every few weeks or even months. But, if this does improve things, yet the app hits the server frequently and you would prefer to use connection pooling if at all possible, then you can try to reintroduce connection pooling, but in limited sense: you can set a low value for the Connection Lifetime keyword:

Connection Lifetime=2;

This will have connections close upon returning to the connection pool, if they are over 2 seconds old. This allows for a series of quick query executions to go through without each of them spending time establishing the connection, while at the same time not allowing the connection to linger out there, possibly holding locks, etc.

Even if the error still occurs, by not having connection pooling keeping the session and connection alive, that should allow for proper cleanup, releasing of locks, etc.

Just to have this stated since it was questioned in comments on the question: Multiple Active Result Sets (MARS) is used by Entity Framework to do Lazy Loading. So if the app has been developed to use Lazy Loading, then turning off MARS is not an option.


Model Likes / Dislikes in SQL I'm trying to implement a behaviour similar to Facebook (okay, most similar to YouTube).

In my table I got various Products, each Product can be liked or disliked by a Customer. I don't want to let the users rate (let's say between 1 and 5 stars), so a simple like or dislike is enough.

What's the best way of representing that?

I've thought about three essential tables (next to Customers):

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, CreatedOn)

ProductDislikes (Id, CustomerId, ProductId, CreatedOn)

But that would mean to have two tables with nearly the exact same data.

Another idea was to have the following:

Products (Id, Name, ...)

ProductLikes (Id, CustomerId, ProductId, IsLike, CreatedOn)

But that would mean I cannot provide a third state later on, if I decided to do so.

And my last idea:

Products (Id, Name, ...)

ProductRatings (Id, ProductId, CustomerId, Rating, CreatedOn)

Where a "like" would be a Rating of 5.0, a "dislike" one of 1.0.

I would go for a variant somewhat between your second and third options:

Products (Id, Name, ...)

ProductVotes (Id, CustomerId, ProductId, Vote, CreatedOn)

The column Vote should be constrained to only allow the values Like and Dislike or, if you prefer, 1 and -1 -- the latter having the advantage of a simpler possibility to do the sum() of the likes and dislikes to get an overall vote.


Is it possible to grant a user the equivalence of ownership over a another users schema? Im trying to create a trainer/trainee environment in Oracle 18c XE. So far I have 4 trainee users which all have the hr-sample installed. Now, I plan on having one trainer user that is able see everything the users are doing in real time and can adjust if need be.
This topic seems to be discussed often but I have not seen one solution where the grantee can see newly created objects after a script like this has been run:
BEGIN
  FOR t IN (SELECT object_name, object_type FROM all_objects WHERE owner='TEST1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    IF t.object_type IN ('TABLE','VIEW') THEN
      EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||t.object_name||' TO TEST2';
    ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO TEST2';
    END IF;
  END LOOP;
END;

There has to be a way of giving another user select and create privileges on all objects including the ones that will be written in the future. Of course there is the possibility of writing a trigger but this just seems way too complicated for such an easy requirement.
Is it possible to grant a user the equivalence of ownership over a another users schema?

There is no direct way to do what you are describing, which is privilege by assertion on specific schemas. The closest thing you could do would be to grant the trainer proxy privileges to log on as the trainee to see their objects, or to grant a series of "ANY" system privileges (select any table, alter any table, insert any table, etc.) to the trainer that would allow them to see everything in the database. I wrote a brief blog on proxy authentication here: https://pmdba.wordpress.com/2014/09/05/shared-application-accounts-and-developers/. You can also find information on it here: https://oracle-base.com/articles/misc/proxy-users-and-connect-through.


Force SQL Server to use fragmented indexes? I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index fragmentation can easily go over 90%.

Right now I've addressed this by reorganizing the indexes on a daily basis and a rebuild on a weekly basis. I've also played around with fill factors etc to keep the fragmentation lower.

My main problem is that when the indexes gets too fragmented, SQL Server ignores the indexes and performs full table scans instead. When that happens, it almost kills the application since repeatedly scans of such large table is really heavy. I'm quite sure using a fragmented index would be faster in those cases.

Is there a way to let SQL Server use the index regardless of its fragmentation?

Index Hint

You can use an index hint to do that:

SELECT *
FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index)
WHERE u.Reputation = 2;

The downsides are:

Potentially changing a lot of code
If you rename an index, this breaks
If you change an index definition, it might not be the best index to use anymore

Plan Guides

You can also use a Plan Guide, which sets the plan you want in place.

The downsides are:

It's fickle; if your query isn't exactly the same, your plan might not get used
It's tough to set up; you have to get everything exactly right
Also the same stuff as an index hint, so uh... That's rough.

Query Store?

Since you're on SQL Server 2017, you may be able to use Query Store to force plans. This is much easier than using a Plan Guide, but you may end up with an unexpected plan.

Downsides:

Same stuff as above
Having to enable Query Store, if you're not already using it
Having to query Query Store to see if forced plans are failing

Alternatives

Without knowing more, I'd probably be more keen on investigating if the index use changes for other reasons, like parameter sniffing.


SQL Data Collector - SSIS required on source server? For SQL Data Collector:

Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one that the MDW database is on.

Nope, you don't need SSIS on the server you are collecting data from, bits of SSIS are installed anyway when you install SQL server and this is enough for the data collector to work.  All the system data collector sets work out of the box once you have set up the management data warehouse and enabled them.

The actual SSIS packages for data collector are stored within MSDB, rather than SSISDB as with a full SSIS server and they are executed by a process called Dcexec.exe.

The full architecture for how data collector works is detailed here:

Data Collector Architecture


threshold replica lag for secondaryPrefered mongod Example:
3 node on RS with mongos and mongoc(cluster)
On one Secondary node(on RS) we have replica lag, e.g. - 1h.
So, if my read preference is - secondaryPrefered and if I read data from RS, can it happen that I will read data from a node that has a replication lag?
Or does mongodb have some sort of protection mechanisms?

Yes, the data you read from SECONDARY might not be up-to-date. The obvious solution if you are no fine with it: read from the PRIMARY.
However, one hour sounds a lot. Did you configure a Delayed Member or did you configure storage.oplogMinRetentionHours?


Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level? If I create a table.

CREATE TABLE foo AS
SELECT CASE WHEN random() > 0.5 THEN x END AS x
FROM generate_series(1,10) AS x;

And, then I run the following in a transaction

BEGIN;
  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;

  --time

  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;
END;

Under what transaction level is my result guaranteed to remain the same in the transaction?

My ten cents, and based on the PostgreSQL documentation:

Assumption: your own transaction does not change any relevant value from table foo.

Having the same count(*) in both your queries means:

You cannot have dirty reads, because another transaction might have written more rows into your foo table between the first and the second select; and you should not see them.
You are not really reading any column, so nonrepeatable reads are not an issue.
You cannot have phantom reads, that is, if another transaction changes any row whose x column is NULL to a non-null value, your transaction does not have to notice those changes affecting the WHERE condition.
I don't really know how to judge serialization anomalies. My educated guess is that this isn't required. But this is really very open for debate.

Under these conditions, the table from Transaction Isolation Levels makes it clear that Repeatable read complies with the criteria of:

Not having dirty reads
Not having non-repeatable reads
Not having phantom reads (in PostgreSQL, but not demanded by the standard)

... as such, it would give you the same count(*) in both select statements.


How to get latest date from fees table and compare it to current date in mysql I have two tables members and fees ,fees table has a foreign key memberid which is a primary key in members .
I want to get those members who have not yet paid. When a member pays there are two fields fromdate and todate and both they are datetime.
So those fields helps us to know that the member paid from which date to which date .
So I want to pull out those members whose todate is older than the current date , remember since they pay monthly I want to pull out only
one latest todate  for each member  then I compare it with current date . Any help will be appreciated . bellow is what I have tried .

 select  members.name , fees.todate ,
    fees.usercode
    from members
    INNER JOIN fees
    ON members.memberid  =  fees.memberid
    WHERE
     fees.usercode ='KMTS472895'
    AND
    fees.todate < curdate()
    AND
    fees.todate = (SELECT MAX(fees.todate))
    AND
    todate !='0000-00-00 00:00:00'
    GROUP BY fees.memberid
    ORDER BY fees.memberid

///////////////////////////////////////////

CREATE TABLE `members` (
  `memberid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `companyid` int(11) unsigned DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `initials` varchar(5) NOT NULL,
  `surname` varchar(30) NOT NULL,
  `datejoined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `usercode` varchar(12) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `cell` varchar(15) DEFAULT NULL,
  `cell2` varchar(15) DEFAULT NULL,
  `pic` varchar(100) DEFAULT NULL,
  `idnumber` varchar(30) DEFAULT NULL,
  `rank` varchar(30) DEFAULT NULL,
  `gender` varchar(12) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address2` varchar(50) DEFAULT NULL,
  `branch` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `Company_name` varchar(100) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `Comments` varchar(500) DEFAULT NULL,
  `Company_reg_number` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `next_of_kin` varchar(100) DEFAULT NULL,
  `next_of_kin_number` varchar(100) DEFAULT NULL,
  `patient_code` varchar(50) DEFAULT NULL,
  `medNumber` varchar(100) DEFAULT NULL,
  `medPlan` varbinary(100) DEFAULT NULL,
  PRIMARY KEY (`memberid`),
  KEY `companyid` (`companyid`),
  CONSTRAINT `members_ibfk_1` FOREIGN KEY (`companyid`) REFERENCES `company` (`companyid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;

///////////////////////////////////////////////////

CREATE TABLE IF NOT EXISTS `fees` (
  `feesid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feestype` varchar(50) NOT NULL,
  `amountpaid` decimal(10,2) DEFAULT NULL,
  `memberid` int(11) unsigned DEFAULT NULL,
  `companyid` int(11) unsigned DEFAULT NULL,
  `paidfor` varchar(100) NOT NULL,
  `paiddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fromdate` datetime NOT NULL,
  `todate` datetime NOT NULL,
  `usercode` varchar(12) NOT NULL,
  `paidby` varchar(30) NOT NULL,
  `recieved_by` varchar(30) NOT NULL,
  `qty` int(5) NOT NULL,
  PRIMARY KEY (`feesid`),
  KEY `memberid` (`memberid`),
  KEY `companyid` (`companyid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1595 ;

Try this

select  members.name , fees.todate ,
fees.usercode
from members
INNER JOIN
(
select max(usercode) as usercode
, memberid
, max(todate) as todate
from
fees
WHERE
 fees.usercode ='KMTS472895'
 GROUP BY fees.memberid
) as fees
on members.memberid = fees.memberid
where fees.todate < curdate()
order by members.memberid


Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred

from member to member,
from member to organization,
from organization to organization, and
from organization to member.

Considerations

In order to build a database for such a scenario, I have created the following three tables:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32),
  account integer
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid),
  account integer
);

CREATE TABLE TransferHistory
  "from" integer, -- foreign key?
  "to" integer, -- foreign key?
  quantity integer
);

I think that the TransferHistory table is necessary to show who/what sent funds to whom/what.

The problem is, since Members and Organizations are different tables, how can I reference them from the TransferHistory table?

For instance, the data involved can show up as follows:

Account      Account      Quantity
-----------  -----------  --------
 1072561733  38574637847       500
38574637847   1072561733       281

That would suggest that accounts need to be recorded in the same table, but accounts are for two different kinds of owners (members and organizations), each of which are retained in their respective table.

I could create a table called Accounts, so now I would have four tables:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32),
  accountid integer references Accounts(accountid)
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid),
  accountid integer references Accounts(accountid)
);

CREATE TABLE Accounts (
  accountid serial primary key,
  state integer
);

CREATE TABLE TransferHistory (
  "from" integer references Accounts(accountid),
  "to" integer references Accounts(accountid),
  quantity integer
);

...but now I have to ensure that each foreign key from Members and Organizations tables does not point to the same Account row in Accounts table...

...or I could have an Accounts table having two foreign keys, one pointing to Members and another to Organizations (and one foreign key column would have to contain a NULL mark at all times). But now things get a bit confusing with respect to queries. The database design would be, generally speaking, as follows:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32)
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid)
);

CREATE TABLE Accounts (
  accountid serial primary key,
  member integer references Members(memberid),
  organization integer references Organizations(organizationid),
  state integer
);

CREATE TABLE TransferHistory (
  "from" integer references Accounts(accountid),
  "to" integer references Accounts(accountid),
  quantity integer
);

So, does anybody have a suggestion on how to solve this problem?

If the intention is to build a relational database, it is really helpful to first carry out (a) an analysis of the business context of interest —in order to delineate a conceptual schema— in terms of entity types, inspecting their properties and associations before (b) thinking in terms of tables, columns and constraints —aspects that correspond to the logical level—. Following this course of action, it is much simplier to capture the meaning of the business domain with accuracy and then reflect it in an actual, well constrained, SQL-DDL design.
One of the numerous advantages offered by the relational paradigm is that it permits managing the data in its natural structure; hence one has to “find” such structure before employing relational instruments to manage it. It does not matter if the scenario at issue is related to a personal project (as you pointed out via comments): the more realistic you define it, the more you will learn from its development (if that is the purpose of this effort). Of course, a realistic personal project may evolve into a comercial one with relatively minor adaptations.
Business rules
In order to present a first progression that you may like to use as a reference, I have formulated some of the conceptual-level business rules that are among the most significant ones, and they are enumerated as follows:

A Person owns zero-one-or-many Accounts
A Person is primarily distinguished by its Id
A Person is alternately distinguished by his/her Firstname, Lastname, BirthDate and Gender
An Organization owns zero-one-or-many Accounts
An Organization is primarily differentiated by its Id
An Organization is alternately differentiated by its Name
An Organization starts operating at a FoundingDate
An Account is the Transferor in zero-one-or-many Transfers
An Account is the Transferee in zero-one-or-many Transfers
An Account is primarily identified by its Number
An Account is issued at an exact CreatedDate
In a Transfer, the Transferor must be different from the Transferee
A Person may log in via zero-or-one UserProfile

Since the associations —or relationships— (1) between Person and Account and (2) between Organization and Account are very similar, this fact reveals that Person and Account are entity subtypes of Party (basically, either an individual or a group of individuals), which is in turn their entity supertype. This is a classic information structure that arises very frequently in multiple conceptual models of diverse kinds. In this manner, two new rules can be asserted:

A PartyType classifies zero-one-or-many Parties
A Party is either a Person or an Organization

And two of the previous business rules can be consolidated into a single one:

A Party owns zero-one-or-many Accounts

Which can be also be stated from the point of view of the Account entity type:

An Account is owned by exactly-one Party

Expository IDEF1X diagram
Consequently, I created an expository (simplified) IDEF1X† diagram that synthesizes the rules formulated above, and it is shown in Figure 1:

Party, Person and Organization: Supertype-subtype structure
As demonstrated, Person and Organization are depicted as mutually exclusive subtypes of Party.
The Party supertype holds a discriminator (i.e., PartyTypeCode) and all the properties (or attributes) that are common to its subtypes, which, in turn, have the properties that apply to each of them.
Account
The Account entity type is directly connected with Party, which provides a subsequent connection between (i) Account and Person, and between (ii) Account and Organization.
Since it is possible that, in the real world, (a) a bank Account is not transferable, i.e., its Owner cannot change and (b) an Account cannot start being current or enabled without an Owner, the PRIMARY KEY of this entity type may be comprised of the properties PartyId and AccountNumber, so you should analyze the scenario even more thoroughly to define this point with high precision.
Transfer
On the other hand, the Transfer entity type presents a composite PRIMARY KEY made up of three properties, i.e., TransferorAccountNumber, TransfereeAccountNumber (role names I assigned to distinguish every one of two Account properties involved in each Transfer instance) and TransferDateTime (which tells the exaxct Instant when a Transfer occurrence was performed).
Factors about AccountNumbers
Be also aware that, in actual banking systems, the format of an AccountNumber data point is usually more complex than a “mere” integer value. There are different format arrangements, e.g., the one that corresponds to the International Bank Account Number (IBAN), defined by the ISO 13616 standard. This aspect implies, evidently, that the (1) conceptual analysis and the later (2) logical definitions require a much more exhaustive approach.
Illustrative logical SQL-DDL declarations
Then, as a derivation from the previous analysis, I have declared a logical design where

each table represents an entity type,
each column stands for a property of the respective entity type, and
multiple constraints are set up (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual tier.

I have supplied notes as comments that explain some of the features that I esteem particularly important with respect to the aforementioned structure, that is shown below:
-- You have to determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on the business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE PartyType (
    PartyTypeCode CHAR(1)  NOT NULL, -- This one is meant to contain the meaningful values 'P', for 'Person', and 'O' for 'Organization'.
    Name          CHAR(30) NOT NULL,
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);

CREATE TABLE Party ( -- Represents the supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- Denotes the subtype discriminator.
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE Person ( -- Stands for a subtype.
    PersonId        INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Party to Person.
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    --
    CONSTRAINT Person_PK        PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK        UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Organization ( -- Represents the other subtype.
    OrganizationId  INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY simultaneously, enforcing a association cardinality of one-to-zero-or-one from Party to Organization.
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    --
    CONSTRAINT Organization_PK        PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK        UNIQUE      (Name), -- ALTERNATE KEY.
    CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
        REFERENCES Party (PartyId)
);

CREATE TABLE UserProfile (
    UserId          INT       NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Person to UserProfile.
    UserName        CHAR(30)  NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT UserProfile_PK         PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK         UNIQUE      (Username),
    CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
        REFERENCES Person (PersonId)
);

CREATE TABLE Account (
    AccountNumber   INT       NOT NULL,
    OwnerPartyId    INT       NOT NULL, -- A role name assigned to PartyId in order to depict the meaning it carries in the context of an Account.
    CreatedDateTime TIMESTAMP NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    --
    CONSTRAINT Account_PK        PRIMARY KEY (AccountNumber),
    CONSTRAINT AccountToParty_FK FOREIGN KEY (OwnerPartyId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Transfer (
    TransferorAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber.
    TransfereeAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber
    TransferDateTime        TIMESTAMP NOT NULL,
    Amount                  INT       NOT NULL, -- Retains the Amount in Cents, but there are other possibilities.
    Etcetera                CHAR(30)  NOT NULL,
    --
    CONSTRAINT Transfer_PK             PRIMARY KEY (TransferorAccountNumber, TransfereeAccountNumber, TransferDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT TransferToTransferor_FK FOREIGN KEY (TransferorAccountNumber)
        REFERENCES Account (AccountNumber),
    CONSTRAINT TransferToTransferee_FK FOREIGN KEY (TransfereeAccountNumber)
        REFERENCES Account (AccountNumber),
    CONSTRAINT AccountsAreDistinct_CK  CHECK       (TransferorAccountNumber <> TransfereeAccountNumber),
    CONSTRAINT AmountIsValid_CK        CHECK       (Amount > 0)
);

As noted, there is no need for the retention of ambiguous and problematic NULL marks in the columns of any of the base tables.
If you want to know if an Account involved in a certain Transfer is owned by an Organization or a Person, you can derive such information in a single SELECT statement via, e.g., the Transfer.TrasnferorAccountNumber, the Account.PartyId, and the Party.PartyTypeCode columns.
So that you can ensure that a Party can own at most one Account (as stated in comments), then you should fix a UNIQUE constraint for the Account.PartyId column. However, in real world scenarios, e.g., in a bank, a Person can own zero-one-or-many Accounts, thus I esteem that a one-to-zero-or-one association does not appear realistic.
As previously mentioned, the approach proposed in this answer is supposed to be utilized as a reference that you can extend and adapt on your own. Naturally, extensions and adaptations made at the conceptual level should be reflected in the logical model.
I tested the declaration of this structure in (i) this db<>fiddle and in (ii) this SQL Fiddle, both running on PostgreSQL 9.6 (you initially attached the tag of this database management system).
Integrity and consistency considerations regarding the tables Party, Person and Organization
With the layout described above, one must guarantee that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart and, in turn, ensure that said “subtype” row is compatible with the value contained in the supertype “discriminator” column.
It would be very handy and elegant to enforce such circumstances declaratively but, unfortunately, none of the major SQL platforms has provided the proper mechanisms to do so (as far as I know). Therefore, it is quite convenient to make use of ACID TRANSACTIONS so that these conditions are always confidently met in a database.
Similar scenarios
In case you are interested in other business domains in which supertype-subtype structures come about, you might like to see my answers to

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”,
“Advice on a basic design, first time database design”, and
“How to model an entity type that can have different sets of attributes?”.

Relevant resource

These Stack Overflow posts cover very relevant points concerning the data type of a column that holds a currency datum, like Transfer.Amount, in PostgreSQL.

Endnote
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0.

Now I am using features of SQL Server like:

Common Table Expressions (I have a table with a reference to itself composing a n-level parent-chilren hierarchy)
Stored Procedures
Bulk Uploads

I didn't find enough reference on NuoDB to understand if it is suitable to do these things:

Large select operations with hierarchy logics (now I use a recursive CTE on the 'items' table to accomplish this)
Complex updates/inserts thru some kind of algorithm (stored procs would be ok)
Bulk insert/update of 100.000+ elements (I am using bulk upload feature of SQL Server  .NET client and it works ok)

Besides this, I am also interested in partitioning, anybody has some experience in partitioning with NuoDB?

Thanks

The manual for CREATE PROCEDURE states: "Not Yet Implemented"

The documentation of the SELECT statement neither shows a WITH nor an alternative like connect by (as e.g. Cubrid does).

For bulkloading there is nuoloader but there doesn't seem to be an API for it.

But you will probably get a better answer if you post directly to the NuoDB forum: http://www.nuodb.com/community/forum.php?

I had a look at NuoDB as well and was pretty disappointed by the lack of SQL features. I guess that's not their main focus right now.


How to drop user@% on the MySQL CLI? How to drop 'user'@'%' on the MySQL CLI using the mysql -e command?

mysql -uroot  -p -e "'myuser'@'%'";

This does not work.

You have to surround the % character with single quotes

DROP USER user@'%';

or put the user in single quotes as well

DROP USER 'user'@'%';

From the CLI, you would run something like this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="DROP USER 'user'@'%'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

An alternative method would be delete it from mysql.user

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="DELETE FROM mysql.user"
SQL="${SQL} WHERE user='user'"
SQL="${SQL} AND host='%';"
SQL="${SQL} FLUSH PRIVILEGES;"
mysql ${MYSQL_CONN} -ANe"${SQL}"


Where does the mysqldump command store the backed up databases? I am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful. Where does it place the backed up databases?

I run the command like this:

$ mysqldump -h localhost -u username - p database_name > back_up_db.sql

but have no idea where back_up_db.sql is stored.

By default it does it to the working directory at the time the command was made. You need to redirect the output to a file if you need to save it.

The place you put the backup is up to you. Ideally, it should be on a different machine in a different physical location. This is why you usually do a remote MySQL dump over SSH and get the data on a different machine. You just pipe the console output over ssh into a file on a different machine.


Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted? I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc.

I'm using Mysql 5.5.x

Before doing the production system, I am doing a trial run on a copy of the DB on a test machine - just in case.

I backed up the tables with mysqldump using this information.

Some tables take more than the timeout (600sec) to convert, and so I get an error 2013:

ALTER TABLE voice.members ENGINE=InnoDB Error Code: 2013. Lost connection to MySQL server during query

When I check the table later, it's listed as InnoDB.

show table status in voice like "members";

Here are my questions:

Can I assume that the conversion went as intended?
Are there any obvious checks I should perform to ensure that integrity is not compromized?
Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?

QUESTION #1

  Can I assume that the conversion went as intended?

Never assume a conversion until you test it. Thank you for backing it up in advance.

QUESTION #2

  Are there any obvious checks I should perform to ensure that integrity is not compromised?

For any given table mydb.mytable you converted to InnoDB, please check two things

TABLE COUNT

Take a count of all the rows

SELECT COUNT(1) FROM mydb.mytable;

Run the same count on the MyISAM backup.

CHECKSUM

Run a checksum of the whole table

CHECKSUM TABLE mydb.mytable;

Run the same checksum on the MyISAM backup.

SUMMARY ON QUESTION #2

You should get the same count and checksum values for both the InnoDB and MyISAM tables.

SUGGESTION FOR QUESTION #2

If you would like to manage the InnoDB conversion in stages, try doing this next time

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Use the table, test it in staging, kick the tires.

If you are satisfied the table is fine, drop the old table

DROP TABLE mydb.mytable_old;

or retain it as a backup

ALTER TABLE mydb.mytable_old RENAME mydb.mytable_myisam;

QUESTION #3

  Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?

If you want convert a live MyISAM table to InnoDB, see my post Converting live MyISAM table to InnoDB

If you are very skeptical or fearful of data loss, your idea of single user access is fine.

You could just restart mysql like this

service mysql restart --skip-networking

Login to mysql as root@localhost. Do the conversion, then

service mysql restart

GIVE IT A TRY !!!


Adding standard set of constraints (rules) to PostGIS `raster` type column Following on from my related prior question, let me elaborate this topic a bit.

raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag defined as follows:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql

Output:

-C  Set the standard set of constraints on the raster
column after the rasters are loaded. Some constraints may fail
if one or more rasters violate the constraint.

When I am importing my raster file like this:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql -d -I -C -M -F -t 100x100 -s 4326
us_tmin_2012.01.asc chp05.us_tmin_new | psql -h localhost -p 5432 -U postgres -d pgrouting

Output:

ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
NOTICE:  Adding scale-X constraint

Few constraints have been applied to this new table by the -C flag.

pgrouting=# \d+ chp05.us_tmin_new

Output:

Indexes:
"us_tmin_new_pkey" PRIMARY KEY, btree (rid)
"us_tmin_new_rast_gist" gist (st_convexhull(rast))
Check constraints:
"enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]))
"enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),

The standard constraints comprise the following rules (although these constraints are acting independently on all the incoming raster tiles):

Width and height: This rule states that all the rasters must have the same width  and height.
Scale X and Y: This rule states that all the rasters must have the same scale X and Y.
SRID: This rule states that all rasters must have the same SRID.
Same alignment: This rule states that all rasters must be aligned to one another.
Maximum extent: This rule states that all rasters must be within the table's
maximum extent.
Number of bands: This rule states that all rasters must have the same number
of bands (means this ensures that all the coming rasters must have a specific number of bands, it's not comparing existing raster tiles).
NODATA values: This rule states that all raster bands at a specific index must have the same NODATA value.
Out-db: This rule states that all raster bands at a specific index must be in-db or out-db, not both.
Pixel type: This rule states that all raster bands at a specific index must be of the same pixel type.

NOW, in order to run ST_MapAlgebra function, I had to drop these std constraints individually, which I did using:

ALTER TABLE chp05.us_tmin_new DROP CONSTRAINT enforce_scalex_rast

in pgAdmin SQL Editor for each of those std constraints. But now I don't know how to bring these standard constraints back? The following command is not working:

ALTER TABLE chp05.us_tmin_new ADD CONSTRAINT enforce_scalex_rast unique (rast);

and giving the following error:

ERROR:  data type raster has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

OK, so you:

Dropped a bunch of constraints
Did some work
Attempted to add a completely unrelated random constraint and that failed

You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint.

You must:

take a note of each constraint name and expression;
Drop them
Do the work
Re-create them with matching name and expression

So, say you have the constraint "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21])) on table chp05.us_tmin_new. To recreate it, you must use the same constraint expression and name. So that'd be:

ALTER TABLE chp05.us_tmin_new
ADD CONSTRAINT "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]));

See how it conveniently matches the psql output for the constraint? You can just copy and paste it.

I have no idea why you were attempting to create a unique constraint instead, when it could not possibly have the effects you described above.

(BTW, your descriptions are slightly inaccurate. Those check constraints don't assert that all the rasters have the same number of bands, etc. They assert that all constraints have number-of-bands equal to some particular value. In other words, the constraint isn't relative to other values in the table, it's checked independently for each row.)


Windows PostgreSQL Update Timezone Data Turkey government has announced that Turkey will stay on GMT+3.

We've waited for OS pathces to update tzdata on PostgreSQL.
Now, timezone patch for Windows is installed.

On Linux, restarting PostgreSQL service is enough.
But on Windows, restarting did not help.

postgres=#select extract(timezone from '2017-01-01'::timestamptz);
date_part
---------
7200

On linux;

postgres=#select extract(timezone from '2017-01-01'::timestamptz);
date_part
---------
10800

I need to update timezone datas for PostgreSQL on Windows.

Can anyone help me to solve this?

Also I am sure that timezone change patch is installed.

PostgreSQL versions are 9.2 and 9.5

Today PostgreSQL released new minor versions.
In these versions timezone datas updated to 2016h.

Released versions;

9.6.1
9.5.5
9.4.10
9.3.15
9.2.19

Also you don't have to upgrade your installation.

You can move new timezone datas to your installation and restart the service.
It's all done.
It's worth the wait.


Removing editioning from an Oracle user I maintain an Oracle Enterprise 11.2.0.3 database where one user has editioning enabled.  I understand that this was an irreversible choice according to the documentation.

If I wish to revert that user to a non-editioned status can I export their objects, drop and recreate the user and then import the old objects?

Are there any consequences?
Which edition will Oracle export?
should I drop all child editions first?

This question is caused by the assessment of the team that editioning is not used and does not offer any benefits when we have generous time periods for scheduled downtimed. It prevents any materialized views being created by the editioned user if they select non editioned object from another user. This is required for one project due soon and a data warehouse in the near future.

Based on Phil's test case I backed up the the production database using exp.

I dropped the editioned user, recreated them and imported the data with no issues.  The user had views and packages which were versioned but as long as you set the edition to the most current one you are good to go.

This query showed that the recreated user was not using editioning

SELECT username,editions_enabled
FROM   dba_users
WHERE editions_enabled = 'Y';


Caching A Query Daily How to ensure a query runs only once a day in MySQL? Once it runs, returned result should be cached, and even though returned data were changed in database, my query's results should remain the same during the same day. Is that possible?

Point of my this question:

I want to put some statistics on the dashboard of my admin panel. But calculating the statistics keep long time. So, If these calculations is done only one time, it really saves lots of time of my clients and my server.

Yes, it is possible. Use MySQL's EVENT mechanism. As the documentation says, it's the MySQL equivalent of cron or the Windows Task Scheduler. An overview is available from here and an example here. They can also be seen as TRIGGERs fired by a time rather than by SQL.

Be sure to enable events via the global system variable event_scheduler as explained here.

You can set up an event to run at 00:01 and store the result in a table and then your app can refer to that table throughout the day, safe in the knowledge that the value won't change until 1 minute past midnight the following day (or whenever suits you).


Dynamic Pivot Table Sort by Month and year I have used dynamic pivot query using the script below.

    select @cols = STUFF((SELECT ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL
    GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
     , DATEPART(MONTH, CONVERT(DATETIME, createDate))
    ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');

I am getting the following result.

Description   January   February  March April
xxxxxx           44       20        0    12

I want the following

Description   January 2017   February2017   March 2017  April 2017
xxxxxx           44             20              0          12

Could you please suggest the script for this.

Thanking you,

Hem

This is one option:

It uses a subquery to get the date values but notice I've moved the ORDER BY clause out of the subquery.

declare @dt table (mydate date);
insert into @dt values ('20170101'),('20170201'),('20170301'),('20170401'),('20170501'),('20170601'),('20170701'),('20170801');

declare @cols varchar(1000);

select @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(mn.[MonthName], '-', CAST(mn.[Year] AS VARCHAR(10))))
                      FROM (SELECT YEAR(mydate) [Year], MONTH(mydate) [Month], DATENAME(MONTH, mydate) AS [MonthName]
                            FROM @dt
                            GROUP BY YEAR(mydate), MONTH(mydate), DATENAME(MONTH, mydate)) mn
                ORDER BY [Year],[Month]
                      FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

select @cols;
GO

| (No column name)                                                                                |
| :-------------------------------------------------------------------------------------------------------- |
| [January-2017],[February-2017],[March-2017],[April-2017],[May-2017],[June-2017],[July-2017],[August-2017] |

dbfiddle here


What happens during "big" inserts on a table with a clustered composite key? My SQL knowledge is limited, so the terms I'll be using are most likely not the right ones.

I have a table that will store test results, for multiple locations.

The tests will be recorded in different databases at different locations (no network connection) and the "master" location will regularly "import" the test results from the other locations.

I plan to have a clustered composite primary key on the LocationId (int) and Date (datetime) columns, in that order. The reasoning is that it should keep all the results for a location together and I will almost never do a query by date range, but rather by date range and location.

The row size will be 80 to 100 bytes and the number of test results should not exceed a few millions. A typical "import" will insert 50 to 100 thousands results from another location.

What will happen during imports? Will SQL "move" the existing rows to maintain the clustering, or will it let the table become "fragmented"? Could this cause a big performance hit if the import is done one row at a time? Should I rather just not bother with the ordering of the rows and just add an identity column as the primary key and an index on the Date column to help with my queries?

Holy cow, you've got a lot of questions in here. Let's break this down.

Q: Will SQL "move" the existing rows to maintain the clustering, or will it let the table become "fragmented"?

Think of a database as a collection of pages - literal pieces of paper laid out on your desk. Think about the dictionary for now. If you wanted to add more words to the dictionary, you could add them in place if the pages had empty space.

When you first start out with an empty dictionary, this is relatively easy. But think about a mature dictionary with thousands of paper pages in it, all full.

When you want to add more words to that mature dictionary, odds are there isn't going to be any space left on the page. SQL Server will "tear" a page - it will take a brand new page somewhere else, and move some of the words over onto that new page. The new page would be at the end of the dictionary. The good news is that immediately after that action, there's now a half-empty page at the end of your dictionary, and also at the middle, both with space to add words.

If you happen to be adding them in that order, that is. (This is why the way you load data becomes increasingly important.)

Could this cause a big performance hit if the import is done one row at a time?

Forget the index for a second - adding data one row at a time is just plain inefficient regardless of the indexing structure. SQL Server is a set-based system - whenever you can work in sets, you probably should.

What happens when I query the data?

You didn't ask this, but I'm asking it for you, hahaha.

Think back about the aftermath of our inserts. Now we've got a dictionary that's mostly ordered, but when you get to a few points of the dictionary, you'll have to jump to the back to read from a few other pages. If these pages are all cached in your memory (RAM, buffer pool, etc) then the overhead just isn't going to be that large. Most memory access is random anyway - it's not like SQL Server stores your dictionary in memory in order.

On the other hand, if you need to fetch the data from conventional magnetic hard drives (spinning rust), then you can end up getting a bit of a performance benefit if that data is stored in order. The real design goal here, though, is to get the data from RAM instead of getting it from drives. The difference between defragmented data on disk versus fragmented data on disk is nowhere near as significant as the difference between getting it from disk versus getting it from RAM.

Should I rather just not bother with the ordering of the rows and just add an identity column as the primary key and an index on the Date column to help with my queries?

Bingo: this is the difference between physical database design and logical database design. Programmers have to worry a lot about physical database design initially, but as long as your database is under, say, 100GB in size, you can fix logical design in post, so to speak. Put an identity field on there for starters, cluster on it, and then after being live for a few months, revisit the index design to maximize performance.

Now, having said that, once you're experienced with this type of decisionmaking, then you'll be better equipped to guesstimate indexes right from the start. Even so, I don't even usually put much thought into index design initially. Users never seem to query the data the way I would have expected.


Update a dynamic number of rows with an amount proportionally Since my English are not perfect I will try explaining with an example

I have tableA that I want to update the Amount

groupID reservationID  Date        Amount
26      1              2018-06-01  NULL
26      2              2018-06-01  NULL
26      3              2018-06-01  NULL
27      4              2018-06-04  NULL
27      5              2018-06-04  NULL

And tableB that holds the total amount per Group

groupID  Date        Amount
26       2018-06-01      60
27       2018-06-04     100

I trying to find a way that

groupID 26 amount of 60 can updated the first table
groupID26 rows with 20 each (60 / 3)
groupID 27 amount of 100 can update the first table groupID27 rows with
50 each (100/2)

In other words a query that will be smart enough to divide the total amount in tableB  the in the number of rows in tableA where:

tableA.Date = tableB.Date
AND tableA.groupId = tableB.groupID

Thanx

Here is a sample code ddl

DECLARE @tableA TABLE
(group_id       numeric(10,0)   null,
 reservation_id numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

DECLARE @tableB TABLE
(group_id       numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

INSERT @tableA
SELECT 26,1,'2018-06-01',NULL
INSERT @tableA
SELECT 26,2,'2018-06-01',  NULL
INSERT @tableA
SELECT 26,3,'2018-06-01',  NULL
INSERT @tableA
SELECT 27,4,'2018-06-04',  NULL
INSERT @tableA
SELECT 27,5,'2018-06-04',  NULL

INSERT @tableB
SELECT 26,'2018-06-01',60
INSERT @tableB
SELECT 27,'2018-06-04',100

You can solve it this way:

declare @a table
(groupID int, reservationID  int, [Date] date, Amount decimal(10,2));

insert into @a values
(26,      1,              '2018-06-01',  NULL),
(26,      2,              '2018-06-01',  NULL),
(26,      3,              '2018-06-01',  NULL),
(27,      4,              '2018-06-04',  NULL),
(27,      5,              '2018-06-04',  NULL);

declare @b table
(groupID  int, [Date] date,  Amount decimal(10,2));

insert into @b values
(26,       '2018-06-01',      60),
(27,       '2018-06-04',     100);

with cte as
(
select a.*,
       b.Amount / count(a.reservationID) over (partition by a.Date, a.groupId) new_amount
from @a a join @b b
        on a.Date = b.Date
        AND a.groupId = b.groupID
)

update cte
set Amount = new_amount;

select *
from @a;


Does an index where the last column is unique and already indexed make sense? I found a unique index on (RetailerID, ID) in our database.  The ID column is the primary key so it's already uniquely indexed.  I can't see how an index on (RetailerID, ID) could help when looking up an individual record by RetailerID and ID (or just ID).

I suspect it might be helpful when querying all the IDs under a particular RetailerID, since it covers such a query.  Is that true?

But then why not just make the index on RetailerID include ID?  Perhaps the unique index on (RetailerID, ID) covers the query in the same way but performs better as a result of being a unique index.

As I said, there's a unique primary clustering key on ID alone.  There's also a unique index on (RetailerID, ID). Since ID is already unique, querying where RetailerID = x and ID = y shouldn't be any more efficient than simply querying where ID = y.  (ID) is unique and (RetailerID, ID) is unique, so ID is associated with at most one RetailerID.  Therefore, if a predicate has ID, then including RetailerID in the predicate is redundant.  The clustered index would always be faster.

An index on (RetailerID, ID) could be helpful for a query where the predicate is just on RetailerID and the select is on just ID, because it would cover the query, but for queries where ID is in the predicate, RetailerID in the predicate is redundant, and since it has no other included columns, it would be less-efficient than the clustered index when selecting any column other than ID.  That's why I'm not seeing the point of this index, given the predicates used (which always involve ID).

I can think about one scenario where such index is needed. If you want another table to have a Foreign Key constraint that refers (RetailerID, ID), you must create a unique constraint (index).  This pattern makes sense in some cases, for instance if you want to have a common parent table which holds common attributes, and multiple detail tables, and at the same time enforce that details are saved in the proper table.

Update. Example to illustrate an idea  :

vehicle (vehicle_id, vehicle_type_id, year, model, ...
         PK (vehicle_id),
         Unique (vehicle_type_id,car_id),
         CHECK (vehicle_type_id IN ('car','truck'))
);

car (vehicle_id, vehicle_type_id default 'car',
     num_passengers, car_type, ....
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle,
     CHECK (vehicle_type_id ='car')
);

truck (vehicle_id, vehicle_type_id default 'truck',  load,
     dimensions, ... ,
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle,
     CHECK (vehicle_type_id ='truck')
);

Thus you enforce that truck's attributes are always stored in truck table, car's - in car, and at the same time refer to vehicle if vehicle type is not important, say vehicle_owner  can look like vehicle_id, person_id, date_from ,date_thru, FK (vehicle_Id) ref vehicle(vehicle_id)).


How to model dependent categories (parent-child-relation)? Idea / Goal
I have two tables: Article (represents a Product) and Category. The relation is like this: An Article can be assigned to only one Category but a Category may have at least one Articles, therefore, 1:n (n >= 1).
Now, I want to build a category tree that holds up to 5 dependent Category entries. The category-hierarchy is constant. I have given each Category a level field that tells me how deep I am in the tree (1-5, 1 being the main category, 5 being the last leaf possible). Now I still need to know how to navigate ... I thought that I could just set a parent_category field that points to the upper category. The main categories, however, do not have a parent_category respectively they are set to null.
With this approach, I could query the databases for each level. This would make sense since I want to navigate through my categories and then see the sub-categories on entering a category (imagine a URL like: %host%/category/sub-category).
Example
Please note: The data represented here is arbitrary. But the structure would be identical to this example below (I have omitted some other fields because they don't matter for this question):
Article

| id | name            | fk_category |
| -- | --------------- | ----------- |
| 1  | Apple           | 4           |
| 2  | Orange juice    | 3           |

Category

| id | name     | level | parent |
| -- | -------- | ----- | ------ |
| 1  | Fruits   | 1     | null   |
| 2  | Domestic | 2     | 1      |
| 3  | Juice    | 3     | 2      |
| 4  | Whatever | 3     | 2      |

The URL %host%/fruits/domestic would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Category
WHERE level = 3

And then give me the names: "Juice" and "Whatever".
The URL %host%/fruits/domestic/whatever would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Article
WHERE fk_category = 4

This should give me "Apple" (... and any other Articles that are within that category).
I am not sure yet how I should implement it that the Category also knows if it is a node without children. I could set a boolean for has_children.
Question
How would you model this relationship? Is it okay to model it the way I had in mind? Is there a better alternative? Does it violate any rules?

First you have to separate the nodes subordination from the nodes itself. You need a pivot table trees having exactly two columns: nodeID -- parentID. The node having parentID equal to NULL is a root node for certain tree. Pivot table trees can contain more than one tree in it. This table requires a primary key (nodeID) and an unique multicolumn key (nodeID, parentID) - each node should be listed once and each node can have only one parent. An additional restriction - node can not refer to itself as a parent.
CREATE TABLE trees (
  nodeID INT UNSIGNED NOT NULL,
  parentID INT UNSIGNED, -- nullable for root nodes
  PRIMARY KEY ( nodeID ), -- each node is listed once
  UNIQUE KEY  ( nodeID, parentID ), -- no duplicates
  CONSTRAINT treeselfref CHECK ( nodeID <> parentID ) -- can't be parent for itself
)

Then you need a set of procedures for nodes manipulations:
AddNode( nodeID, parentID )
DeleteNode( nodeID ) -- you have to manage orphan childs
DeleteBranch( nodeID ) -- and all its childs/subchilds
GetParent( nodeID )  -- for the given node
GetChilds( parentID ) -- whos parent is parentID
GetBranch( parentID ) -- childs and their childs up to the leaves, ranked
GetLeaves( nodeID ) -- all leaves attached to the node
GetPath( nodeID ) -- full subordination path from the root up to the node, ranked
MergeBranches( nodeIDa, nodeIDb )
MoveBranch( nodeID, newParentID )
. . . . .

An exact set of procedures and their logic depend on the items/categories nature. So here is the sample code for the GetLeaves( nodeID ) procedure, just for illustration of the basic principle:
WITH RECURSIVE cte (nodeID, parentID) AS
( SELECT w.*
    FROM trees AS w
   WHERE w.nodeID = _nodeID  -- seed node for branch
  UNION ALL
  SELECT q.*
    FROM cte   AS z
    JOIN trees AS q ON q.parentID = z.nodeID  -- childs, recurrent
) SELECT s.nodeID
    FROM cte        AS s
    LEFT JOIN trees AS r ON r.parentID = s.nodeID
  WHERE r.nodeID IS NULL; -- nodes not listed as parents for other nodes i.e. leaves

Here is the code for GetPath( nodeID ) procedure that returns a list of all parent nodes from the tree root up to the given node, with rank (distance):
WITH RECURSIVE cte (rank, nodeID, parentID) AS
( SELECT 0 AS rank, w.*
    FROM trees AS w
   WHERE w.nodeID = _nodeID
  UNION ALL
  SELECT z.rank+1, q.*
    FROM cte   AS z
    JOIN trees AS q  ON q.nodeID = z.parentID
) SELECT s.*, f.name
    FROM cte        AS s
    JOIN nodes      AS n ON n.ID = s.nodeID
    JOIN categories AS f ON f.ID = n.categoryID
   ORDER BY s.rank DESC

When invoked for nodeID = 123 it will return something like that:
+----+------+--------+----------+
|rank|nodeID|parentID|name      |
+----+------+--------+----------+
|  5 | 1    | NULL   | root     |
|  4 | 7    | 1      | edible   |
|  3 | 11   | 7      | fruit    |
|  2 | 31   | 11     | domestic |
|  1 | 42   | 31     | apple    |
|  0 | 123  | 42     | mcintosh |
+----+------+--------+----------+


Idea for manking tables for a pharmacy OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not.
So, my Dad works in a pharmacy and all the work there is on paper and is done manually. He asked me if I can develop an app which He can use. I'm stuck at the part where I gotto decide how to design the table for the system. My app is built on QT/C++ and I'm using Sqlite with it.

Here's the database problem, There are 6 different types of items coming the store, Eg. tables & capsules, injections, ointments, etc. Now for each item in each of these category, we have to maintain the following entries unique key, received date, received amount, issues date, issued amount, expiry date and batch number.
Now this store is resupplied with items almost every week, and issues items to OPD frequently, also each item can receive different batch numbers which in turn has different expiry date.
I want to design a table diagram which satisfies the problem.

~Thanks in advance.

Example:

Lets say we have a medicine CROCINE (which comes under the category tables & capsules) coming in in week 1. The batch number of the received medicine is abc-123 and its expiry date it dd-mm-yyyy, and we received a quantity of 5000 units. Later in the week 1000 units are issued to the OPD. Further in the week another 2000 units are issued to the OPD. So the updated balance of CROCINE in the store is, 2000 units. Now, in the next week, ie. week 2, 2000 units of CROCINE is received with batch number def-456 with a different expiry date. Also the balance is updated. Like this it goes on and on. :p

Normalization is hard :/

Guys I had a go, but I doubt it's correct - please be gentle. For example we can SUM to get current stock, but the joins seem kind of unnecessary instead of using a figure stored somewhere. I would love to see the thought process of someone experienced designing this. Anyway:

Product
---
product ID
product name
category

1, aspirin, tablets
2, toxazon, injections
3, ibuprof, tablets

Batch
---
batch ID
product ID
expiry date

asp-001, 1, 2015-03-01
tox-001, 2, 2015-02-10
tox-002, 2, 2015-02-28

Transaction
---
batch ID
transaction date
amount

asp-001, 2015-01-01, 200
tox-001, 2015-01-30, 200
asp-001, 2015-02-01, -100
tox-002, 2015-02-07, 500


Security implications of running SSRS and an OLTP database on the same server I am in the process of building a new environment for my company's Customer Relationship Management (CRM) system. This system is a commercially available product which uses SQL Server as its back end database. The CRM system also uses SQL Server Reporting Services (SSRS) to query the CRM database to provide reports. The database which is queried by SSRS is the live OLTP database - there is no separate reporting database.

There is some debate in my team on whether it is acceptable from a security point of view to run SQL Server Reporting Services on the same server which hosts the CRM database, or whether it must be run on a separate server.

The specific security concern is that running SSRS on the server increases the attack surface, which increases the likelihood that the server will be compromised by a bad guy. If the SSRS server were compromised it would be better if it weren't also hosting the CRM database with all its valuable data.

The counter argument is that if a bad guy wants the data, they will attack the CRM database server directly and won't be fooled by the presence of an SSRS server nearby. Also, all of this is on the internal network, so should be at relatively low risk of attack in the first place.

Is running SSRS and an OLTP database on the same server contra-indicated for security reasons?

The Moment you add Entry points for a system of course, naturally, you have increased the attack surface.
Besides this purely mathematical reason, you should think about:

Who will design reports
For designing reports someone, maybe the same person, will craft queries
Preferably you use stored procedures at least inside a separate, code-only, database
There you have the first permissions you will have to give inside your system for selecting data and creating objects. If done with care the risk is extremely low to zero. So keep it simple.
Above that on the server level you have the Reporting Services Service. Here you are strongly advised to keep to established security best practices such an “use dedicated service accounts”, especially since SSRS has a Login on the SQL Server box for accessing its system databases. Again, no problem if you isolate all that.
And then you of course have the Data Source Credentials which will be a Windows or even SQL Login with just the permissions necessary for running the above queries – at best only prepared stored procedures for mitigating the risk of elevating privileges.

So, long story quick answer: mixing services initially creates a higher risk for taking over the other one, but by following security best practices very strictly, in my opinion, the risk is not higher than if on a separate server where one could still make similar mistakes. In high security environments there will for that very reason be very strict overall rules to rule out such scenarios.


Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux:

"C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --blobs --clean --create --if-exists --compress=0 --dbname="DATABASE_NAME" --file="P:\PG11_dump_20190217"

This is the end of the log:

...
pg_dump: dumping contents of table "schema.table"
pg_dump: finished item 255955 TABLE DATA table
pg_dump: finished item 255956 TABLE DATA table
pg_dump: pg_dump: dumping contents of table "schema.table"
dumping contents of table "schema.table"
pg_dump: finished item 255953 TABLE DATA table
pg_dump: finished item 255952 TABLE DATA table
pg_dump: could not fsync file "P:\PG11_dump_20190217/291267.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/293909.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/294060.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/295715.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/296142.dat": Bad file descriptor

I searched for the pg_dump: could not fsync file: Bad file descriptor error but could not find any help.

Maybe of importance: The database is about 3.6 TB and I am using the PostGIS extension.

Question: How to prevent this error or work around it?

Additional question: Could I do a file level backup and copy the data folder from windows to linux, given an identical Postgres version and both servers stopped?

Question: How to prevent this error or work around it?

fsync ensures that the data is physically written to disk, so that in case of a crash or loss or power a few seconds after the backup, no data is lost in unflushed memory buffers. Unfortunately it doesn't seem to work in your case. You may add --no-sync to avoid this step, although the errors shown are probably harmless, in the sense that surely they don't affect the contents of the backup. You may also use sync as an external program if you're wary of skipping the sync.

  Additional question: Could I do a file level backup and copy the data
  folder from windows to linux, given an identical Postgres version and
  both servers stopped?

No. The data files are not portable in general, and especially not across totally different operating systems.


How to determine character encoding in Hyper SQL database I have Hyper SQL database and I don't know what is current character set. Is it set for whole DB or it can be different for every table? How to evaluate encoding in my base?

HSQLDB stores text data as Unicode. You can verify that by opening the .script file for the database and looking at the data.

For example, for a table with a row containing "Montréal" the .script file contains

CREATE MEMORY TABLE PUBLIC.TABLE1(ID INTEGER PRIMARY KEY,TEXTCOL VARCHAR(50))
...
INSERT INTO TABLE1 VALUES(1,'Montr\u00e9al')

where \u0039 is the Unicode code point for the letter é.


How to model a relationship with variable tables (polymorphism)? What is the best way to model the following kind relationship?

Let's say I have tables representing two types of entities: company and person. I have a customer table with an entity_id field, which should refer to either a company or a person.

If I simply store a company_id or a person_id in entity_id then I won't know which table to look up the entity in. The first solution which has occurred to me is to have a entity_type field in customer which would take the values "company" or "person" to tell me which table my foreign key in entity_id refers to. I'm not sure if this is the best way though.

This seems like it should be a fairly common problem in database design, so I'm hoping there is a design pattern I can adopt.

I've used the word polymorphism in the title in the broader sense, so I'm not referring to inheritance. In my example, a company is not a person (or vice versa) but they are still interchangeable. Other questions I've found on Stack Exchange sites have assumed inheritance is also a requirement, so I'm not sure that there is a suitable duplicate.

There seem to be 2 main ways to model such a situation:

(1) Use supertype/subtypes.  However, as we are not discussing "object oriented" design, the second option may be a bit more appropriate.

(2) Use a so-called ARC, which expresses a XOR relationship.

Suppose we have an entity TRANSACTION.  Each customer can be involved in one ore more transactions AND a customer is either a COMPANY or a PERSON (XOR).
Our ERD/relational model could look something like the ones below.  Notice the "arc", that is drawn across both relationships.

As for the implementation: you can code a CHECK constraint (in order to avoid triggers), which will enforce the XOR (exclusive or).  The complete DDL code looks something like this (notice the CHECK):

CREATE TABLE company (
  c_id NUMBER PRIMARY KEY
);

CREATE TABLE person (
  p_id NUMBER PRIMARY KEY
);

CREATE TABLE transaction (
  t_id NUMBER PRIMARY KEY
, c_id NUMBER
, p_id NUMBER
);

ALTER TABLE transaction
ADD CONSTRAINT fkarc CHECK (
     ( (c_id IS NOT NULL) AND (p_id IS NULL) )
  OR ( (p_id IS NOT NULL) AND (c_id IS NULL) )
);

ALTER TABLE transaction
ADD CONSTRAINT transaction_company_fk
FOREIGN KEY ( c_id ) REFERENCES COMPANY ( c_id ) ;

ALTER TABLE transaction
ADD CONSTRAINT transaction_person_fk
FOREIGN KEY ( p_id ) REFERENCES PERSON ( p_id ) ;

(Tested on Oracle 12c)


Does postgres efficiently re-use space from deleted rows? From the docs:

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row [...] But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

When vacuum runs, does it efficiently free the space occupied by the deleted rows by rewriting the entire block, or does block fragmentation occur with new rows fitting into available 'holes' only if they are smaller than the deleted row that has made way for them?

VACUUM rewrites the entire block, efficiently packing the remaining rows and leaving a single contiguous block of free space (though this space isn't zeroed and the physical disk file might contain the remnants of deleted rows which of course are in no way visible to the database user).

test schema:

--#psql postgres postgres
select oid from pg_database where datname='postgres';
/*
  oid
-------
 12035
*/
create schema stack;
set search_path=stack;
create table foo(bar text);
insert into stack.foo(bar) values('row 1');
insert into stack.foo(bar) values('row 2');
checkpoint;
select * from foo;
/*
  bar
-------
 row 1
 row 2
*/
select relfilenode
from pg_class c join pg_namespace n on n.oid=c.relnamespace
where nspname='stack' and relname='foo';
/*
 relfilenode
-------------
      446488
*/

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 f093 9d83 0000 0000 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 e09f 3c00 c09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0208 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is row 2
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0100 0100 0208 1800 0d72 6f77 2031 0000  .........row 1..

delete and vacuum:

delete from foo where bar='row 1';
vacuum stack.foo;
checkpoint;

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 e8b5 9e83 0000 0500 2000 e01f  8........... ...
0000010: 0020 0420 0000 0000 0000 0000 e09f 3c00  . . ..........<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is free space
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

insert new row:

insert into stack.foo(bar) values('row 3');
checkpoint;

final physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 c8ec 9e83 0000 0100 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 c09f 3c00 e09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2476 4c14 0000 0000 0000 0000 0000 0000  $vL.............
0001fd0: 0100 0100 0208 1800 0d72 6f77 2033 0000  .........row 3.. <---- this is row 3
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

clean up:

drop schema stack cascade;


SSIS Package Truncation may occur due to inserting data from data flow column I've a dataflow in SSIS 2008. It reads the data from flat file and inserts in to database.

The flat file is CSV. For some reason, all the fields in flat file connection manager seems to have 50 OutPutColumnWidth.

Could someone please suggest how to fix it. I'm getting error below:
Truncation may occur due to inserting data from data flow column

This warning message displays when any time source column data type length will be more than then length of destination datatype. To resolve the warning message check here -


Getting all databases which a sql logon can connect I need to know all databases in a SQL Server on which a user can connect. With sys.databases I get all databases and with sys.server_principals or sys.syslogins I get all server logons. But I can't find a table which contains a connection between these tables. Has anyone an idea how to solve this? Thanks.

You should be able to do something like this:

EXECUTE AS login = 'loginname';

SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1;

REVERT;

For reference see EXECUTE AS and HAS_DBACCESS


MS SQL Server 2016 Oprimal Disk Configuration New server build for SQL SERVER STANDARD 2016 install. Total of 12 disks: 6 standard 15K HDs and 6 SSDs. This is the disk setup I am going for.
Could this setup be improved on, if so how what would change?

I could move TEMP DB mdf to DATA Drive and have TEMP DB log with other DB Logs on SSD LOG drive.

This will be for a SQL Server Production environment only.
Optimizing for speed but also HA to an extent.
Backups will get copied to an offsite location throughout the day.
Server will be mirrored not AG. Although mirroring is deprecated, due to install being Standard Edition I don't see benefits of AG at this point with 2 servers.
Biggest waits are LATCH_EX followed by CX_PACKET

Also looking to replicate some data for reporting purposes.

LOG average file size is 9.16 MB over past 6 months, Log backup every 15 mins
MAX size has been 389.47MB in past 6 months

TEMP DB info for past 6 months

Looks fine to me.

Because of the way that SQL Server schedules writes, keeping logs and data on separate volumes doesn't save as save as much performance as many think for many workloads (it does have a large effect on some workloads though, so YMMV), and I expect this will be less still on SSDs (as you don't have the latency of head movements flipping between the log and data files to worry about) so if you expect a lot of tempdb activity you might be better off keeping the logs with the data files and leaving tempdb on its own to keep the IO bandwidth of that array to itself.

Of course this is very dependant on actual application workloads so my gut thought above could be completely wrong for your DB(s), so if possible benchmark before committing to anything in production.

Also Cody's comment regarding spare drives is critical: make sure there are some on-site or very nearby. From a HA standpoint mirroring will protect you to an extent should a whole array fail (without mirroring take "or nearby" from the above and make sure there are definitely spares on-site and ready to go) but I'd still feel safer knowing an array can start rebuilding ASAP after a single drive failure to reduce the chance of a dual drive failure. Also check with your infrastructure provider to make sure the machine supports hot-swap (or if not set your recovery plans accordingly to take into account entirely stopping one mirror temporarily while a replacement is installed).


Adding database-specific privs for user gives table 'db' is full, MariaDB, *tiny* toy database On Windows 10, running 10.4.14-MariaDB - mariadb.org binary distribution
Using phpmyadmin, logged in as root.
I have a rather small WordPress database named PBALL_WP that I loaded from a mysqldump file as the root user through the mysql command-line client. I created a new user named PBALL, and wish to give that user all privileges on database PBALL_WP. Here are the users:
User accounts overview
User name   Host name   Password    Global privileges   User group  Grant   Action
Any         %           No          USAGE                           No      Edit privileges
PBALL       %           Yes         USAGE                           No      Edit privileges
pma         localhost   No          USAGE                           No      Edit privileges
root        127.0.0.1   No          ALL PRIVILEGES                  Yes     Edit privileges
root        ::1         No          ALL PRIVILEGES                  Yes     Edit privileges
root        localhost   Yes         ALL PRIVILEGES                  Yes     Edit privileges

So for User PBALL I click on "Edit privileges". I don't want to give it global privileges (do I need to?), so I click on the "Database" button on the next screen, which brings up a panel for Database-specific privileges. I select "pball_wp" from the selection box of database names and click "go". That brings up the following screen of privileges for user PBALL on database PBALL_WP, and I check the box for "Check All" and click "Go".
     Edit privileges: User account 'PBALL'@'%' - Databases pball_wp
Database-specific privileges Check all
Note: MySQL privilege names are expressed in English.

 Data       Structure               Administration
 SELECT     CREATE                  GRANT
 INSERT     ALTER                   LOCK TABLES
 UPDATE     INDEX                   REFERENCES
 DELETE     DROP
            CREATE TEMPORARY TABLES
            SHOW VIEW
            CREATE ROUTINE
            ALTER ROUTINE
            EXECUTE
            CREATE VIEW
            EVENT
            TRIGGER

Doing that produces the following:
Error
SQL query:
GRANT ALL PRIVILEGES ON  `pball_wp`.* TO 'PBALL'@'%' WITH GRANT OPTION;
MySQL said:
#1114 - The table 'db' is full

I believe that table 'db' refers to the table in the database named 'mysql', and the documentation says that this table records database privileges. So that seems to make sense.
But the mysql table only has three rows in it! Here it is:

Anything I've been able to find about this error has to do with actually exceeding a limit in disk space or pending transactions or something like that. This is basically a new installation (xampp 7.4.10) with this small pball_wp database the very first database loaded into it. Any ideas what I can do to figure out why this is happening? I did have problems getting phpmyadmin working at all, which seemed to stem mostly from the initial configuration specifying "localhost" instead of "127.0.0.1". And I notice that there appear to be two root accounts, one with no password on 127.0.0.l and the other with a password on localhost. I login with a password to phpmyadmin. I definitely got all screwed up trying to get things running at all, I had to keep stopping and restarting httpd in order to get logged in to phpmyadmin...
I'm really at wit's end with this... Help please!
EDIT: Here is what I could find out about the db table. It shows DATA_FREE as 0, but it's 0 for most all tables. As far as I know, that's just the amount of unused allocated space (overhead), and shouldn't trigger a "table full" error.

EDIT 2: I just tried manually inserting a row into the db table with phpmyadmin, and get the same error. So it's consistent - consistently crazy!!!

This isn't really an answer, in that I still have no idea why this was failing. It just provides a workaround. I noticed that the db table used the "new" storage engine "aria", so I converted it to use the traditional MyIsam storage engine, like so:
alter table db engine=myisam

After doing this, the problems posted disappeared.


Is there a way to speed up JOIN ON LIKE? I have millions of things in a table. I'd like to assign groups to the things based on theirs prefixes.

I tried to add all the indexes but the planner still does nested loop left join.

A demo case SQL:

create table things as
select
    i as id,
    left(md5(random()::text), 8) as name
from generate_series(1, 100000) as i;

create table match_group_rules as
select
    i as id,
    trunc(random() * 5 + 1) as group_id,
    left(md5(random()::text), 2) as rule
from generate_series(1, 100) as i;

create extension if not exists pg_trgm;
create index match_group_rules_rule on match_group_rules (rule);
create index match_group_rules_rule_pattern on match_group_rules (rule text_pattern_ops);
create index things_name_idx on things (name);
create index things_name_pattern_idx on things (name text_pattern_ops);
create index things_name_gin_trgm_idx on things using gin (name gin_trgm_ops);
create index things_name_gist_trgm_idx on things using gist (name gist_trgm_ops);

explain
select *
from things t
left join match_group_rules r
    on t.name like r.rule || '%';

The demo case planner output:

Nested Loop Left Join  (cost=0.00..176543.25 rows=100000 width=57)
  Join Filter: (t.name ~~ (r.rule || '%'::text))
  ->  Seq Scan on things t  (cost=0.00..1541.00 rows=100000 width=13)
  ->  Materialize  (cost=0.00..2.50 rows=100 width=44)
        ->  Seq Scan on match_group_rules r  (cost=0.00..2.00 rows=100 width=44)

Questions:

What makes the planner ignore the indexes?
Is there a quick way to compute the groups for the things?

The initial problem is the left join.  That restricts it to using things as the driving table, but once you do that there is no way to use the indexes, as pg_trgm and text_pattern_ops offer ways to index for texts matching a given pattern, not for patterns matching a given text.

In order to use an index on the left join, you would have to first do a nested loop with match_group_rules driving, memorize all the rows of things retrieved, and then go back and make up NULL rows for the things not found.  There is no inherent reason that I can see that PostgreSQL wouldn't be able to do this complex operation, it just isn't implemented.  You can do it yourself with a CTE and a UNION ALL:

with foobar as
(select t.id as t_id, name, r.id, r.group_id, r.rule
   from things t
   join match_group_rules r
     on t.name like r.rule || '%'
)
select * from foobar
    union all
select id, name, NULL, NULL, NULL from things t2 where not exists
 (select 1 from foobar where t2.id=foobar.t_id);


How to backup and restore in oracle with RMAN I want to complete a task of backup and restore using oracle in the simplest way possible because I never did it before and I don't really know how to do it. What I want to do is backup the database, and inside the database I'll have a user with one table. I'll back up in this state, and I want to delete that table, and restore the database in a previous state where it had the table. All I tried is here, but still I can't solve it. Oracle isn't restoring correctly a backup

I'm asking again so maybe now somebody knows how to do it.

When it comes to recovering data, restoring the database is actually the last weapon in the Oracle DBA's arsenal.

Look into the "Flashback" suite of technologies; there are about six of them, all using slightly different methods to achieve slightly different things.

For example, to recover a dropped table, you might use "Flashback Table", which simply gets that one table back from the Recycle bin (unless some clever Bod uses "drop table .. purge" to obliterate it completely).

To recover data that's been deleted, you can use "Flashback Query" to query the table as it was in the past (but only as far as back as Guaranteed Undo will support).

The quickest way to recover the database to a point in time is "Flashback Database", which uses completely separate Flashback Logs to [very] rapidly roll the whole database back to a point in time (this time limited by the amount of Flashback logs in the FRA).

To do a full-blown recovery of the database to a point in time, you need the database to be in archivelog mode and you need to be backing up the database, controlfiles and archived redo logs.  Assuming you've got all of that:

RMAN>
run
{
  set until time "to_date('20190528120000','YYYYMMDDHH24MISS')";
  restore database;
  recover database;
  alter database open resetlogs;
}

BTW, the most pointless activity you can do as an Oracle DBA is to omit the "set time" clause in the above.  That will destroy the database, overwrite it with its [latest] backup and then roll forward the archived redo logs, thereby putting the database back into exactly the same state as when you started the operation!


Default new record value from the table If I had a table called person with the fk country_id.
Assuming that we have a table called country, which has a default_country column. And all rows except one (for USA), have default_country set to 0, only USA row has default_country to 1.

So if I m inserting person record, and I don't provide country_id, how can I default all new records to have country_id save with the id from row USA.

I know I can set a default column value to id (which I hardcode), but I don't know if its possible to get that from the table, here is what I mean:

"COUNTRY_ID" NUMBER(38,0) DEFAULT -1 CONSTRAINT PR_CN_NOT_NULL NOT NULL

-1 is the hardcoded. it would be nice if I could select it from country table using default_country flag rather than knowing the ID in advance, any ideas?

It's not possible with DEFAULT. Docs says:

  Restrictions on Default Column Values
  Default column values are
  subject to the following restrictions:

  A DEFAULT expression cannot contain references to PL/SQL functions or
  to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date
  constants that are not fully specified.

  The expression can be of any form except a scalar subquery expression.

So DEFAULT (SELECT country_if FROM country where default_country =1 and rownum=1), and even  DEFAULT (SELECT 1 FROM DUAL) fail because it cannot contain subquery .   You cannot work it around by defining your own function either.
The only way is to create row level before insert trigger .


Postgres crosstab query on multiple columns I am trying to build a crosstab query on multiple columns.

To accomplish that I first created a type:

create type product_status as (product varchar(255), status varchar(255))

Then I created my query:

select pivot.*
from crosstab(
    $$
    select
        uniqueid,
        (product, status)::product_status,
        count(*)
    from policycase
    inner join policy
    on policy.id = policycase.selected_id
    group by 1,2
    order by 1,2
    $$,
    $$
    values(
--          ('(HOUSEFIRE,HISTORICAL)'::product_status),
--          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )
    $$
) as pivot (
    uniqueid bigint,
--     housefire_hist smallint,
--     housecontent_hist smallint,
    liability_arch smallint
)

Which works and gives me correct results back. However when I uncomment the commented lines in the example above, I get the following error message:

  [Exception, Error code 0, SQLState 42601] ERROR: provided "categories"
  SQL must return 1 column of at least one row Line 1, column 1

Two tables are used:

The policycase has a id, selected_id and a status

The policy has a id, uniqueid and product

The values clause here:

values(
          ('(HOUSEFIRE,HISTORICAL)'::product_status),
          ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)
    )

produces a single line with 3 columns, whereas you want 3 lines with a single column, as in:

values   ('(HOUSEFIRE,HISTORICAL)'::product_status),
         ('(HOUSECONTENT,HISTORICAL)'::product_status),
         ('(LIABILITY,ARCHIVED)'::product_status)