texts: Estimating the size (of the records) and the overhead in Postgres

texts:
Consider the following table in Postgres 9.4:

CREATE TABLE t
(
  a1 bigserial,
  a2 bigint NOT NULL,
  a3 bigint NOT NULL,
  a4 integer,
  a5 timestamp with time zone NOT NULL,
  a6 timestamp with time zone NOT NULL DEFAULT now(),
  a7 bigint NOT NULL,
  a8 bigint NOT NULL,
  a9 real,
  a10 integer,

  CONSTRAINT kkkey PRIMARY KEY (a1)
)

What are the estimated costs to save this table?

A record costs:

size(bigserial)
+ size(bigint)
+ size(bigint)
+ size(integer)
+ size(timestamp)
+ size(timestamp)
+ size(bigint)
+ size(bigint)
+ size(real)
+ size(integer)
= 8 + 8 + 8 + 4 + 8 + 8 + 8 + 8 + 4 + 4 = 68 bytes

Database Page Layout of Postgres gives rather detailed information how the records land on the secondary storage, but I am not sure how to get all the numbers together.

The Linux reports

blockdev --getbsz /dev/sda1
1024

Questions:

(1) Are there any helper functions to assess the storage costs per Row (so one does not need to do those complicated computations by hand)

(2) How to put the numbers together, i.e., estimate the overhead costs for each row?

(3) How to estimate the costs for the primary key index?


texts: Database Design: Normalization and NULL values

texts:
I have normalized my table from 0NF to 2NF (hopefully correct).

How do I now go from 2NF to 3NF?

Also, ResponseToTransactionId is a recursive foreign key that will contain NULL values a lot of the time. Is this bad design?


texts: How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump)

texts:
I have Postgresql versions 8.4 and 9.1 installed.  For any given Postgresql command, how do I specify a specific version of the command to run?  (e.g., psql, pg_dump, pg_ctlcluster, pg_restore, ...)

My question is motivated by wanting to do a pg_dump in preparation for an upgrade from 8.4 to 9.1, and I want to know which pg_dump version I am running.

I'm running on Ubuntu 10.04 Natty.


texts: how can I get only the SQL server logins, when we use Export-dbalogin

texts:
when we run the export-dbalogin, it will will generate a script(.sql) file, which we will run the in the destination server, then the logins will be migrated, but this command will give both windows as well as SQL server logins. I only want SQL server logins.

I tried to use Get-dbalogin to generate a list of SQL server login and kept them in a variable, then I tried to pass that variable to the -login switch in Export-DbaLogin but it is not working.

Thanks In Advance.


texts:
I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database.

Unfortunately the Express Edition lacks SQL Agent.
What approach would you recommend?


texts: Transaction log exploding during index reorganization

texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

My client is running SQL Server 2005 x64 Standard on a beefy server with 16 GB of RAM, and separate drives for data, log and temp.  Daily backups include log backups.

Their accounting system (Accpac) database is around 45 GB and has been running on this server for the past 3 years with no problem.

About 3 or 4 months ago, during weekly maintenance plans that do an integrity check, index reorganizations, and update statistics, the log file ballooned to twice the size of the database and the job failed due to a disk full error.

At first, I thought the backups were not running so I dumped the log. I know I should not do this, but I didn’t really have any choice.  Then, I began monitoring the nightly backups.  All was good until the next index reorganization occurred.  The same problem occurred with the log running out of space and I realized it was happening during this task.

I moved the log files to the db disk and the db files to the log disk since the db disk was larger.  I wanted to see if I could at least get the job to finish by giving it more room.  Now it ballooned to over 3 times the size of the database and again filled up the disk.

For now, I have just turned off the weekly index reorganize task which is not really a good long term solution.

I'm just using the reorg index task that is in the maintenance plan designer GUI.  I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this.

Also, I do have "compact large objects" checked but assuming this is for blob data?  I don’t believe there are any BLOBs in this database.

Oh, I'm a developer wearing a DBA hat, so be gentle with me.

I forgot to mention recovery model is "bulk" vs. "full" - I believe this was set by the package vendor.


texts: Full text index search returns an unexpected result

texts:
When I search TaxIdentityNumber using contains an unexpected result is returned; the result contains numbers that are not matched to query. For example, I search using '"5689523657*"' and get 5681254870, 5458976235, etc. as result. I rebuilt the index, but get the same result. What should I try? What did I miss?

The full query:

SELECT *
FROM [Contractors] [contractor]
INNER JOIN [ReportDates] [reportDate]
    ON [contractor].[ReportDateId] = [reportDate].[Id]
LEFT JOIN [Persons] [tempPersons]
    ON [contractor].[PersonId] = [tempPersons].[Id]
LEFT JOIN [Organizations] [tempOrganiztions]
    ON [contractor].[OrganizationId] = [tempOrganiztions].[Id]
LEFT JOIN [Organizations] [tempHolding]
    ON [contractor].[HoldingId] = [tempHolding].[Id]
WHERE
    CONTAINS(([contractor].[TaxIdentityNumber]), '"5689523657*"')

Running on SQL Server 2008 Standard Edition. Query to create full text index:

CREATE FULLTEXT CATALOG SPFTI;
GO
CREATE FULLTEXT INDEX ON [Contractors]
(FullName, TaxIdentityNumber, RelatedOrganizationFullName)
KEY INDEX ContractorPK
ON SPFTI;


texts:
I have a table which has a Filestream column which I would like to move to another table.

Is this possible without having to do a "manual" move of the data (create target column; issue UPDATE/INSERT to target column from source column; DELETE source column)?

I'm hoping there might be a way to simply "detach" the column and move it to a different table.

I found a similar/related question about altering Filestream tables which makes me think that it isn't possible, but hoping that someone might be able to help!


texts:
I'm running a remote script that collects data from the web nightly.  Each morning I import the latest data to my database on my Mac using a command line import to phpMyAdmin, adding to the number of rows from the night before. I then access the data locally using a Python script.  Is there a limit to the size of a table that I can have either in terms of being able to store the data or access it via a query in Python?  And, if so, is there a way to check and see how close I am to the limit?

Please note that I'm not concerned with the import size since I'm just importing daily snapshots and inserting those newest rows.


texts: Error 1456 when trying to SET a Witness using TSQL

texts:
Trying to run the following command:

ALTER DATABASE myDB SET WITNESS = 'TCP://myServer.local:7024';

I keep getting the following error:

  Msg 1456, Level 16, State 3, Line 21
  The ALTER DATABASE command could not be sent to the remote server instance 'TCP://myServer.local:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Tried checking firewall ports, making sure certs are valid, making sure endpoints are granted access.

SQL Logs show the following message:

  Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'

I have partner-partner quorum working fine but need the Witness to achieve full quorum. Using certificates to authenticate (no Domain). Has anyone come across this before?


texts: pg_restore restore FK constraints with explicit --table argument

texts:
I started using the -t argument of pg_restore to only restore some tables, which has worked well for read-only workflows.

The problem is that the -t seems to not restore the FK constraints nor sequences applied to the table/column.

Regarding the former (sequence), it seems that the tables restored with -t which had an id column with a modifier of not null default nextval('report_id_seq'::regclass) was restored with just not null, so the sequence isn't applied, which is an obvious.

Is there a way to use pg_restore with explicit tables using -t that also restore foreign-key constraints and sequences?


texts: Managing Security for a Log Shipped db across two (unconnected) domains

texts:
SQL Server 2008 (soon to be 2012).

We have a vendor that is providing us with log shipped backups of our production database.  We use this log shipped database for BI staging, reporting, etc. I've seen several blogs and articles discussing pass through authentication and trusted domains, but we have no access at all to their domain.  Is is possible to create an AD user or group on their side and have it function on our side?

As a workaround - probably unsupported, we take the log shipped db offline, copy the datafiles to a shell db and bring both dbs back online.  This gives us a read/write copy of the database and allows us to configure security; and make any other changes we might need.

I'm wondering if there is any way to have the vendor just create Windows or SQL Server logins (say with specific SIDs to match our AD account SIDs) and therefore keep us from having to make copies of the db in order to manage security.  I'm hoping the question is clear enough, I'll try to rephase it here:

Is it possible to create a login on one server, move the db to another server, and have an AD login already there that keeps the user from being orphaned?


texts: Tighten query caching scope, and periodical recaching

texts:
Let's say I have a query that I want to be cached, and only that query, any other query is not relevant.

Since my database receives a lot of traffic, sooner or later my caching space would run out, and that cached query would get invalidated. Can I set up some sort of mechanism (in MySQL only, no external tools) that enables caching only on that query and lets say, every night it updates the cached results?


texts:
Is there a shortcut to delete your current line of SQL without highlighting the full line?  I want to delete the line that my cursor is on without selecting the whole line.

So in visual studio CTRL + L will delete the full line.


texts:
Currently I am dumping my database (running under Linux Mint), from the command line, using the following:-

mysqldump -u[user] -p[password] MyDatabase > MyDatabase.sql

Now rather than dumping to a self-contained file I need to dump to a Project Folder   so that I have a .sql file per table. However despite trawling the web and a couple of MySQL books I have been unable to find an option for this. Can anyone help?


texts: Counting rows in a table based on subtable result


texts: Why Postgres replication write_lag is measured in seconds

texts:
I'm running pg_receivewal locally, without compression, it stores segments on the same drive with the db files. Here's the replication statistic:

imbolc=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 12920
usesysid         | 10
usename          | postgres
application_name | pg_receivewal
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2019-08-25 13:18:00.504531+07
backend_xmin     |
state            | streaming
sent_lsn         | C/80009218
write_lsn        | C/80009218
flush_lsn        |
replay_lsn       |
write_lag        | 00:00:09.838314
flush_lag        | 00:00:40.028949
replay_lag       | 00:00:40.028949
sync_priority    | 0
sync_state       | async

If I set synchronous_commit = off, write_lag still remains significant, above 2.5 seconds.

So my questions are:

why the write_lag is so high?
does it mean that in case of the db crash last 10 seconds of transactions can be lost?
is there a way to improve it?


texts: Move extended property comments to create table comment

texts:
I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database project in order to get rid of the extended properties in future but keep the column description information.

The usage of SSDT with source control makes the extended properties in the deployed version of the database obsolete.

So the description info of this:

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Customer article number.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdPos', @level2type = N'COLUMN', @level2name = N'ArticleNo';

should get added to the columns definition like to this:

CREATE TABLE [dbo].[OrdPos] (
    [ArticleNo] VARCHAR(10) NOT NULL  -- Customer article number.
...

The Create Table scripts have already been created. The extended properties are all included in those scripts below the create table statements (as imported into the project). It is way too much to do it manually.

Did by chance anyone already have a similar task to solve with a good approach for some kind of automation?


texts: What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share?

texts:
The idea here is to perform a double hop and delegate the credentials of the client (leeand00@example.org), that have been sent to the SQL2.example.org server to SQL3.example.org (admittedly this is an SMB file share and not an SQL Server)

My belief is that I need to delegate the credentials in order to access the file share, since the error message that I get when trying to access a VIEW on the SQL2 server which pulls from a Linked Server using the VFPOLEDB Provider which points to the SMB Share on SQL3 when connecting with a client using a KERBEROS auth_scheme without the SPNs setup is:

OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure hatsum, Line 5
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME".

So this leads me to believe that an SPN on the SQL2 and SQL3 computers on the correct port will delegate the client credentials to the SMB Share.  (Using Constrained Delegation of course...)

Any idea what sort of SPN / AD Computer Delegation settings I need to pull this off?

Please note that when connecting using any other sort of auth_scheme, be that NTLM or SQL, I am able to query the Linked Server without issue; but when using the KERBEROS auth_scheme it yields the error message above.

I also know I can list the SPNs that are already set using setspn -l <server-name>


texts: User / Client IP restrictions in Postgres running on RDS

texts:
I'm looking to restrict connection access for specific accounts based on the client IP. All of our services are running on AWS so I want an account that has R/W permissions to only be able to connect to Postgres when coming from inside our containers.

I would be able to configure pg_hba.conf if this was a native version of Postgres running but that is not possible with RDS. The only access management I can see is completely blocking access by IP with no regards to the credentials being used. Credential management is for RDS access but since we are connecting directly to Postgres none of that is used.

Has anyone found a way around this issue?


texts: Windows Storage Server, SQL Server, regular files -- means to protect from insider threats?

texts:
This is my first post here, and I am by no means qualified as a Windows Server or Storage Server 2008 or SQL Server 2008 administrator. I am mostly a Unix guy, however tasked with a job to investigate if our firm (a small software startup), IT infrastructure, which includes Windows Server 2008, Windows Storage Server, and SQL Server used exclusively by Finance, HR, Sales, Legal and Administration depts. Devs are all on Unix.

What I need to get some ideas around is, how to protect the Windows based infrastructure from insider attacks. More precisely, against rogue system-administrators. Our current system administration role is played by a temp. In theory, having access to all (or most) passwords/login credentials, he can do whatever he likes.

Is there then some technology (s.a. audit logs, tamper-proof backups etc.), that can be setup (like onetime), whose credentials are available only with an extremely trusted person (maybe CEO himself), and all other system admin tasks can be performed by this temp chap.

In worst case, if the admin removes, deliberately corrupts/modifies certain files, or alters database content, such acts can be:

Tracked down
Proven
Rectified

I understand that this is probably not a pure DBA question, and pardon me for willfully posting this, but I didn't find a more appropriate SE forum, but found quite a few interesting Windows Server infrastructure discussions here.


texts: Is it possible to bulk insert using CTE in MySQL?

texts:
I created this small sample fiddle to play with the model: https://www.db-fiddle.com/f/b37ASVuSxna9wEoWn9hZDw/0

I have a table called folder, which represents a folder hierarchy with a parent_id column pointing to the parent folder. The root folder in a tree has NULL as its parent_id.

I have another table that specifies access restrictions to folders for users and user groups, it is called folder_access. It has a foreign key relation to the folder table, and then it has columns for user_id, user_group_id and a flag called access (which is an int column, but it is used as a bitwise flag that signifies access like read, write, delete (not important in relation to this question)).

I am implementing an API that gets a list of folder ids, a list of either user ids or user group ids and a single access value. The API should then insert or update rows in the folder_access table like this:

Insert a row for each combination of folder id and user id (or user group id)
Also insert a row for each descendant folder in the hierarchy
If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

I implemented a version without the second requirement above:

public async Task SaveFolderAccess(long areaId, List<long> folderIds, List<long> userIds, List<int> userGroupIds, Access access)
{
    var values = new List<string>();
    foreach (var folderId in folderIds)
    {
        foreach (var userId in userIds)
        {
            values.Add($"({areaId},{folderId},{userId},NULL,{(int)access})");
        }

        foreach (var userGroupId in userGroupIds)
        {
            values.Add($"({areaId},{folderId},NULL,{userGroupId},{(int)access})");
        }
    }

    await dbSession.ExecuteSqlRawAsync(
        @$"INSERT INTO folder_access (area_id, folder_id, user_id, user_group_id, access)
            VALUES {string.Join(",", values)}
        ON DUPLICATE KEY UPDATE access = {(int)access}");
}

I know I can create a common table expression to find all the descendant folder ids for a particular folder, like this (replacing {folderId} with the actual value):

WITH RECURSIVE CTE (id) AS
(
SELECT f.id
    FROM folder AS f
    WHERE id = {folderId}
UNION ALL
SELECT f.id
    FROM CTE JOIN folder AS f
    ON f.parent_id = CTE.id
)
SELECT id FROM CTE

But is it possible to combine this into one big command that leverages both the CTE and the "ON DUPLICATE KEY UPDATE"?


texts:
I have to create INDEX but which with this query ?

SELECT *
FROM TABLE
WHERE A = 1
  AND B = '5427dbfac0a8280d74bc672c72636a4f'
  AND C = 'BIL'
  AND D = 'B'
  and E = '01'
GROUP BY F,G,E,H,I
ORDER BY B,C,E,G,H,I;


texts: Oracle 11, incremental backup goes wrong

texts:
Got a pretty big instance  on a one-week backup cycle with an incremental (differential) strategy .. or rather, that's what it was supposed to be. Somehow the daily differentials seem to come out as cumulative!? The size of our instance means that this is a bit of a problem - daily backup window in excess of 3 hours is not acceptable at the end of the week.

Our schedule:
Once every week we do an

backup incremental level 0
  as copy tag 'sometag'
  skip readonly
  database;

and once a day

backup incremental level 1
  for recover of copy tag 'sometag'
  database plus archivelog;

We've configured block-change-tracking for the database

The problem is, that the result is a cumulative incremental backup, which means an increasing backup-window every day, with a reset in the weekends.

What are we doing wrong?

Thoughts:

Change Tracking is getting screwed up?
Strategy incompatible with
image copies?


texts:
I have a PostgreSQL query:

SELECT COUNT(*)
FROM "issues"
INNER JOIN "assets" ON "issues"."asset_id" = "assets"."id"
WHERE "assets"."project_id" = 1
AND "issues"."active" = 't'
AND "issues"."phase" IN ('open');

which runs in 1057.612ms with the following query plan:

> Aggregate  (cost=104434.64..104434.65 rows=1 width=0) (actual
> time=1057.289..1057.289 rows=1 loops=1)    ->  Merge Join
> (cost=4449.93..103041.81 rows=557132 width=0) (actual
> time=1011.340..1052.960 rows=23355 loops=1)
>          Merge Cond: (assets.id = issues.asset_id)
>          ->  Sort  (cost=4421.47..4482.58 rows=24444 width=4) (actual time=30.097..35.907 rows=24241 loops=1)
>                Sort Key: assets.id
>                Sort Method: external sort  Disk: 336kB
>                ->  Seq Scan on assets  (cost=0.00..2639.85 rows=24444 width=4) (actual time=0.009..15.827 rows=24241 loops=1)
>                      Filter: (project_id = 581)
>                      Rows Removed by Filter: 54307
>          ->  Index Only Scan using index_issues_on_asset_id_and_active_and_phase_and_severity on issues
> (cost=0.43..88451.15 rows=1790279 width=4) (actual time=0.019..659.942
> rows=1878712 loops=1)
>                Index Cond: ((active = true) AND (phase = 'open'::text))
>                Filter: active
>                Heap Fetches: 0  Total runtime: 1057.612 ms (14 rows)

If I turn sorting with set enable_sort=off; I get a significant performance boost:

> `Aggregate  (cost=112186.16..112186.17 rows=1 width=0) (actual
> time=85.151..85.151 rows=1 loops=1)    ->  Nested Loop
> (cost=0.43..110793.33 rows=557132 width=0) (actual time=0.027..80.705
> rows=23355 loops=1)
>          ->  Seq Scan on assets  (cost=0.00..2639.85 rows=24444 width=4) (actual time=0.010..16.213 rows=24241 loops=1)
>                Filter: (project_id = 581)
>                Rows Removed by Filter: 54307
>          ->  Index Only Scan using index_issues_on_asset_id_and_active_and_phase_and_severity on issues
> (cost=0.43..3.78 rows=64 width=4) (actual time=0.002..0.002 rows=1
> loops=24241)
>                Index Cond: ((asset_id = assets.id) AND (active = true) AND (phase = 'open'::text))
>                Filter: active
>                Heap Fetches: 0  Total runtime: 85.184 ms (10 rows) `

Obviously I want the better performance. How can I get it without turning off sorting? What can I do better? I've analyzed and vacuumed the tables and it speeds things up by milliseconds (but not hundreds of milliseconds)

select version();

PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit


texts: Does shardCollection acquire lock in mongo db

texts:
We are planning to shard a collection that contains few hundred gigs of data. I am going to use sh.shardCollection api for this. This is supposed to take hours to complete. I couldn't find any reference in the docs explaining whether the command acquires lock on the collection or not.

In short, all I want to know is whether collection is going to be available for read and write during while shardCollection is executing or not.


texts:
Hi can you help me to split the name into firstname, middle name, lastname using a pl/sql procedure.

This my procedure:

create or replace procedure proc_name(name in varchar2, a out varchar2, b out varchar2,c out varchar2) is
begin
    if regexp_count(name, '/') = 2 then
      a := regexp_substr(name, '[^/]+', 1, 1);
      b := regexp_substr(name, '[^/]+', 1, 2);
      c := regexp_substr(name, '[^/]+', 1, 3);
    end if;
end proc_name;

Here is what it should do. Input: sharmi/devi/elango

The output is:

 firstname:sharmi
 middle name: devi
 lastname : elango

I got output for above test cases.

Suppose my input is: /devi/

Then my output should be:

 firstname: null
 middle name: devi
 lastname: null

How could I make it work?


texts: Number of locks exceeded when adding foreign key constraint

texts:
I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows):

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size

I guess the client is acquiring a lock for each row, so I tried locking the table first without success:

>LOCK TABLES a WRITE;
Query OK, 0 rows affected (0.00 sec)

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size

Why exactly are there so many locks, i.e. what is being locked in this query?
Why didn't locking the table solve the problem? Do I need to do the lock in a different way or lock something else as well?
If the only solution is to increase the innodb_buffer_pool_size, how large does it need to be for this query?

I obviously don't need a sustainable solution - just need to do this the one time. For example, the box isn't a dedicated database server but I could increase the pools size brief it for this query and then set it back to something reasonable afterwards.


texts: How to apply a patch in PostgreSQL on Windows?

texts:
How to apply a patch in PostgreSQL? I found an advice, but it seems to be suited for *NIX only. My PostgreSQL (9.3.1 EDIT: 9.3.4 now) was installed from an exe installer on Windows XP (32 bit), so I didn't find my case in the advice.

I'm not talking about minor version upgrade now - I can download the installer through Stack Builder, and there is already a well answered question on what to do with it. But what can I do if I need something that is not available through the StackBuilder, or even without an executable installer (in my case, a patch distributed only in a .patch file)?


texts: Awful performance on joined queries while great separately

texts:
I have a simple queries:

SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2]
     , t2.[FloatField3]
FROM
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField1], t.[FloatField2], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)
) t1
INNER JOIN
(
SELECT t.[IntField], t.[TDateTimeField], t.[FloatField3], dbo.fn_roundToCloser10Min(t.[TDateTimeField]) AS 'KhDateTimeField'
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)
) t2
ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
--ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField

When the query is executed as you see now it runs in less than 1 second. If I run the last two line like this instead

--ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField
ON t1.IntField=t2.IntField AND t1.KhDateTimeField=t2.KhDateTimeField

It takes more than 1 minute. The difference is obvious: joining a persisted value against joining a computed value. Running the queries with IO and time statistics will show exactly the same query plans and IO figures, but CPU is huge in the second case.
I've been thinking about how to reorder/rewrite the query to make it run in 1 second but I haven't succeed. The following works as fast as I expect but involves more code than I would like to:

declare @temp1 table (IntField int, TDateTimeField datetime2(7), FloatField1 float, FloatField2 float);
declare @temp2 table (IntField int, TDateTimeField datetime2(7), FloatField3 float);
insert into @temp1
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField1], t.[FloatField2]
FROM [DB].[dbo].[Table1] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208);

insert into @temp2
SELECT t.[IntField], dbo.fn_roundToCloser10Min(t.[TDateTimeField]), t.[FloatField3]
FROM [DB].[dbo].[Table2] t
WHERE t.[TDateTimeField]>CAST('2016-03-01T00:00:00' AS DATETIME)
  AND t.[TDateTimeField]<=CAST('2016-03-10T00:00:00' AS DATETIME)
  AND t.[DateInt]>= 20160303
  AND t.[DateInt]<= 20160310
  AND t.[IntField] IN (206,207,208)

SELECT t1.[IntField], CONVERT(varchar,t1.[TDateTimeField],121) AS 'TDateTimeField'
     , t1.[FloatField1], t1.[FloatField2]
     , t2.[FloatField3]
FROM @temp1 t1 JOIN @temp2 t2 ON t1.IntField=t2.IntField AND t1.TDateTimeField=t2.TDateTimeField

It's obvious that the improve comes because data is pre-calculated and pre-stored, hence reducing the amount of data, just before joining.
Does anybody have a better idea?

Thanks!!

EDIT: Actual query plans added (first and second query respectively). You'll see they are almost identical, and I've checked the CPU costs in them and didn't see anything weird...


texts: How to print empty string as a result from xml node creation using oracle in a procedure

texts:
i want to print one of the node value as Author ="" empty string in xml node element as below:
This syntax is in oracle:

CREATE OR REPLACE FUNCTION GetRtData
(
    pf_wkstn_oid_sh number,
    pf_wkstn_oid_lng number
)
RETURN varchar2
IS RtData varchar2(10000);
BEGIN
    SELECT REPLACE(XMLElement("TableData",
                         XMLAGG(XMLElement("rt",XMLAttributes(field_data_type as field_data_type,'' as Author)))
                       ).getStringVal(),'></rt>','/>')
    INTO RtData
    FROM pmry_c_rt_flng
    where wkstn_oid_sh = pf_wkstn_oid_sh
    AND wkstn_oid_lng = pf_wkstn_oid_lng;
    return RtData;
END;

it is returning as below.

SQL> select GetRtData(100,4223) from dual;
GETRTDATA(100,4223)
----------------------------------------------------------------------------
<TableData><rt FIELD_DATA_TYPE="N"/></TableData>

even Author has removed in oracle while creating the node using as ( '' as
Author) in the above result.

i want the result as below format:

<TableData><rt FIELD_DATA_TYPE="N" Author=""/></TableData>

once i am using as

' ' as Author
<TableData><rt FIELD_DATA_TYPE="N" Author=" "/></TableData> one extra space which is not empty string.

so can any one help me how to handle this one to make as Author = "" only in oracle.


texts: Upgrading Remote Distributor in SQL Server Replication

texts:
I'm upgrading my remote distributor from 2005 to 2008r2, it is performing transactional replication only. this is a prerequisite to completing an upgrade of the Publisher to 2008r2.

Script replication environment.

STOP REPLICATION FROM PUBLICATIONS
Run commands to flush distribution database
Backup distribution DB
Remove SQL Server 2005
Install SQL Server 2008 R2
Service Pack SQL Server
Assign Permissions to Snapshot Directory
Check PAL permissions
Recreate replication Publications
Recreate replication agents
Recreate replication subscriptions

reapply indexes to the subscribers? (if a new snapshot is applied)
reapply maintenance jobs

if the snapshot directory remains the same with snapshots taken in 2005, will a new install of SQL 2008r2 use these snapshots or will it generate a new snapshot ?
 (not a massive issue at 3am, but just for my estimated timings)

Do I need to remove the distribution ServerName from sys.servers on the Publisher Server ? is anything left in the system tables that I should clear out manually ?

I wanted to change the security to be windows based security as its currently running under 'sa' login. (obviously not good). But have had issues getting this to function after changing the initial config, so thought it might be useful to do it at initial reinstall and switch to 'sa' if unsuccessful.

Thanks


texts: What would I need to do to make sure my DB policies have to pass a security audit?

texts: I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system.  I'm really new to this process and any guidance would be appreciated.


texts: Import from one table in two tables where I need the auto-generated primary key in the second table

texts:
followning scenario:

3 Tables

1 Table name,street,country,bank

This table needs to be imported to

Table1 Address(Primary Key Autoincrement ID),name,street,country

Table2 Address,bank

So I need the Address Value from the first insert for the second insert in table2

Database Server ist MSSQL 2008


texts: Tools to ingest data to database in periods

texts:
We have an application that can work with Oracle or MySql or SQLServer databases. Also, there is a 3rd party data source that we need to have it's latest data records in our database every 15 minutes. We can call some API or raw data endpoint to pull data from 3rd party source.

If we write an application to do this job, it should do the following operations:

Call the 3rd party endpoint every 15 minutes (Calling the endpoint)
Convert the data to our database format (Data integration)
Insert data in our tables (Oracle or MySql or SQLServer)

So, is there any predefined tool to do this job for us or we should develop this application in-house? This is some kind of database sync job but it should be able to work with different databases and also it should be able to do the data integration.


texts:
How can I get the table names without a header or footer or anything else from a specified Postgres database with psql? That is, if I have tables called "table1" and "table2" then the output will be:

table1
table2

and only that. I've been trying various combinations of things, but it seems like it should be a lot easier than I'm making it.


texts:
I'm trying to enforce groups of activities do not have duplicates without using triggers or materialized views if possible.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number) ;
INSERT INTO groups (group_id, activity_id) values (1,1) ;
INSERT INTO groups (group_id, activity_id) values (1,2) ;
INSERT INTO groups (group_id, activity_id) values (2,2) ;
INSERT INTO groups (group_id, activity_id) values (2,3) ;

The next query should return no duplicates if all groups are made of unique activities. I'd like to enforce that this query never returns any duplicates:

SELECT LISTAGG(ACTIVITY_ID, '|') WITHIN GROUP(ORDER BY ACTIVITY_ID) FROM GROUPS GROUP BY GROUP_ID ;

My current solution is to insert the result of the LISTAGG query into a table with a unique constraint and use that to control duplicates with my application code handling any constraint violations by searching for the existing row (and thereby getting the group ID to reuse the existing one)


texts: export and import all the permissions on schema objects within a database SQL Server 2008

texts: Is it possible to export all the users roles and their permissions on schema objects within a database in a .sql file and import them later in an other database ?


texts:
I'm trying to promote the usage of a database versioning system (http://code.google.com/p/flyway/) on the project I'm currently working on - and to that end when we make changes to the database, we're propagating those changes through a series of deltas. It's still very early doors at the moment, so we've had the luxury of being able to make "destructive" deltas and re-populate the DB post-delta.
We are nearing the point where we need to be able to restructure the DB with production data, and so I want to be able to ideally move data around the database within MySQL alone.

For example - I want to take the column contents of TABLE_1:the_value, create the column on TABLE_2 and then copy the values from TABLE_1 to TABLE_2 WHERE id_org and id_club match

TABLE 1
--------------------------------
| id_org | id_club | the_value |
--------------------------------
|   213  |    213  |    1      |
--------------------------------
|   214  |    213  |    2      |
--------------------------------
|   212  |    210  |    5      |
--------------------------------

-- intervening DDL that adds "the_value" to table 2
-- 2nd row on table 1 not copied across due to absence of matching id's

TABLE 2
--------------------------------------------
| id_org | id_club |    foo    | the_value |
--------------------------------------------
|   213  |    213  |     bar   |     1     |
--------------------------------------------
|   212  |    210  |     bar   |     5     |
--------------------------------------------

I've used the FOR EACH ROW construct with Triggers before, but cannot find documentation on the MySQL site that suggests or demonstrates how to use that to iterate over a result set in order to copy it to the target table. That may be more of a reflection of my inability to locate the appropriate information..

Can anyone offer me any pointers on this matter?


texts: Why would you use a managed service account rather than a virtual account in SQL Server 2012?

texts:
In SQL Server 2012, service accounts are created as virtual accounts (VAs), as described here, as opposed to managed service accounts (MSAs).

The important differences I can see for these, based on the descriptions:

MSAs are domain accounts, VAs are local accounts
MSAs use automagic password management handled by AD, VAs have no passwords
in a Kerberos context, MSAs register SPNs automatically, VAs do not

Are there any other differences?  If Kerberos is not in use, why would a DBA ever prefer an MSA?

UPDATE: Another user has noted a possible contradiction in the MS docs concerning VAs:

  The virtual account is auto-managed, and the virtual account can access the network
  in a domain environment.

versus

  Virtual accounts cannot be authenticated to a remote location. All virtual accounts
  use the permission of machine account. Provision the machine account in the format
  <domain_name>\<computer_name>$.

What is the "machine account"?  How/when/why does it get "provisioned"?  What is the difference between "accessing the network in a domain environment" and "authenticating to a remote location [in a domain environment]"?


texts:
Very new to database creation, still learning the basics of power query in excel.
Boss gave me the task of sorting out employees and their user access to several scattered systems so it's easier to see who has what,
is John Smith has this Microsoft license, is an admin for SYS1, and a user for SYS2.
I need to be pointed in the right direction about what that model would look like to point to the type of info above, or even query all the SYS2 admins or SYS2 users.
Any suggestions on resources to review?


texts:
I have a 5 TB database file (don't ask why) that I am cleaning up. I was already able to free over a TB and I will probably end up at 2.5 TB.

All best-practice advice aside, I need to now shrink this file. I am currently running this statement:

DBCC SHRINKFILE(1,4000000)

I can see that it is doing something, as it is sometimes waiting for a pageiolatch_sh and sometimes for a pageiolatch_ex. However, after 24 hours of execution time, the database has not shrunk by a single page. Usually when executing a file shrink like the above, you can see an impact on the file size immediately. Before, I tried to shrink the database by just a single GB and that also did not cause the file size to change at all. I stopped it after 24 hours.

This problem has been lingering for a while but we are at a point were we need to make progress, as the drive space is urgently required for other databases.

BTW, DBCC CHECKDB does not find any problems with that database, so this behaviour can't be caused by some circular page linkage.


texts: PostgreSQL UNIQUE constraint error message

texts:
On Appendix A: PostgreSQL error codes, it says:

  For some types of errors, the server reports the name of a database object (a table, table column, data type, or constraint) associated with the error; for example, the name of the unique constraint that caused a unique_violation error. Such names are supplied in separate fields of the error report message so that applications need not try to extract them from the possibly-localized human-readable text of the message.

Bold emphasis mine. I have the following table:

CREATE TABLE recipes (
    id SERIAL,
    title TEXT UNIQUE NOT NULL,
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

When I try to insert a new row with a duplicate title, I get the following error message in pgAdmin3:

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(mytitle) already exists.

Or, using PHP:

["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "23505"
    [1]=>
    int(7)
    [2]=>
    string(117) "ERROR:  duplicate key value violates unique constraint "title_unique"
                 DETAIL:  Key (title)=(mytitle) already exists."
  }

According to the paragraph from the PostgreSQL documentation, shouldn't the constraint name title_unique be found in a separate field of the error info?

I'm using PostgreSQL 9.4.5.


texts: Status show SSL is in use even though I did not set up certificate on the client side?

texts:
I want to set up SSL connections to a distant MariaDB server. I followed the instructions here. I generated the certificate for the server, restarted it, and got this:

+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| have_openssl        | YES                                             |
| have_ssl            | YES                                             |
| ssl_ca              | /var/packages/MariaDB10/etc/ssl/ca-cert.pem     |
| ssl_capath          |                                                 |
| ssl_cert            | /var/packages/MariaDB10/etc/ssl/server-cert.pem |
| ssl_cipher          |                                                 |
| ssl_crl             |                                                 |
| ssl_crlpath         |                                                 |
| ssl_key             | /var/packages/MariaDB10/etc/ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.2r-fips  26 Feb 2019                |
+---------------------+-------------------------------------------------+

I also generated the client certificate. However, to make sure I understand the process, I proceeded step by step, and did not install them. I first changed the user settings to REQUIRE SSL. Then

mysql --user=user --password=password

fails with a

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

So far so good. However,

mysql --user=user --password=password --ssl

succeeds, even though I did not provide any client certificate. status shows

SSL:                    Cipher in use is DHE-RSA-AES256-GCM-SHA384

which, if I understand correctly, means that the connection is indeed using SSL. How is that possible, is it the expected behavior?

I am using MariaDB 10.3.


texts: 11gR2 didn't ask me to set username or any password

texts:
I have installed Oracle 11gR2 (latest version 11.2.0.1.0) on my system. During the installation it didn't ask me to set username or any password. I'm unable to start sql plus because i don't have the username and password.
Can anyone tell me what is the default username and password or what is the default password for SYS or SYSTEM?
Thanks


texts: SSRS 2016 Report Portal - KPI option no available in drop down

texts:
Is there a way to enable the KPI selection in reporting services 2016 web portal?
I eagerly wanted to try this feature, I then wasted 2 hours looking for it, still haven't found it. In most examples it seems to be under the "New" drop down.

I'm on the home portal page , I did create a dataset and datasource. Still no KPI option. I've remoted onto the host server, the user has System Administrator and System User rights in SSRS.


texts: MYSQL - Run a query based on the results of a query

texts:
Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written.

select @tbl:=column_name as'Table',
       ( select max(length(@tbl))
         from project) as 'used_length',
       character_maximum_length as 'defined length'
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'project'
  and column_name = 'announcement_notes'
group by column_name,
         character_maximum_length
order by 1

Unfortunately, it's not running the max(length) query - it is returning the length of the label - in this case announcement_notes is 18 characters long.

Table              used_length  defined_length
-----------------  ------------ ---------------
announcement_notes 18           2000

[if that wasn't clear - the return from the query is announcement_notes, 18, 2000).

however, checking the actual length
select max(length(announcement_notes)) from project
returns 404 characters.

Any help would be greatly appreciated.


texts: How can I create new login in SSMS 2008?

texts:
When I attempt to create new login in MSSQL Server 2008, I get the following errors:

I have tried everything I know, but I can't get it to work.

What should I do and how I can solve these issues?


texts: Reclaiming unallocated space from tempdb database in SQL Server Express 2012

texts: I need to reclaim space from tempdb database which has more than 50 GB unallocated space. I have tried DBCC SHRINKDATABASE but it didn't do anything. Please note, I am aware that it has performance implications but I have no choice. I run SQL Server Express on a laptop and really need this 50 GB space. Also I have tried restarting SQL Server/laptop and have ensured that no process is running right now.


texts: Can I grant privileges to a role without specifying an object?

texts:
I am trying to create a role to simplify granting a set of privileges that I often give to users. I am wondering if there is any way to grant these privileges on only the objects owned by their schema without knowing what the schema name will be ahead of time?

For instance, I want to assign the role student_access to the users JOE and MARY without having to specify what tables or procedures they have these privileges on. They should have all privileges that the role grants on all objects owned by their schema.

Thanks.


texts: Are there negative effects to creating multiple copies of an access database?

texts: I've been working on updating an existing database for a company and for version control have been making multiple copies (so far I'm at 10) and making different kinds of changes to each one. I am wondering however if there are any negative effects do doing this? These copies are on a shared drive but are separated from the original database (which people are still using).


texts: sdo_tune.quality_degradation returning negative values

texts:
While running the sdo_tune.quality_degradation function on one of our Oracle databases most of the results were around 1 but a number had values like -556127.225.

Does this mean that these indexes have suddenly become incredibly efficient and should return results before I finish typing the query?

I'm guessing it's a sign that something is wrong somewhere.

Any ideas?


texts: Postgres - Importing multiple rows with Foreign key constraint

texts:
I can't seem to find an answer for my problem, maybe I'm not asking the right question but here it goes:

I have basically two tables in pgAdmin, let's say student and grades, eventually with a foreign key constraint (student_id referencing student(id)

CREATE TABLE student (
    id CHAR(2),
    name CHAR(4),
PRIMARY KEY (id));

CREATE TABLE grade (
    student_id CHAR(2),
    grade INT,
FOREIGN KEY (student_id) REFERENCES student(id));

What I'd like to do is copy/import two files into the databases. All good for students but my grade.csv files will contain non-existing student IDs and I want the import to ignore and not insert them. Instead of that, the command fails, nothing is imported.

What would be a correct and efficient way to do this?


texts: JOIN on LIKE when input udtt count is > 1

texts:
This query accepts a User-Defined Table Type variable and then joins that with a table.  If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input.  This is essentially what I have now, but it's quite slow for larger inputs (testing around 500 takes around 5min).

DECLARE @inputCount INT = ( SELECT COUNT(*) FROM @inputUDTT )
SELECT b.* FROM @inputUDTT a
INNER JOIN Tabl b ON (@inputCount>1 AND b.name = a.val)
    OR (@inputCount<2 AND b.name like '%' + a.val+ '%')

I've also tried having the SELECT COUNT(*) directly in the join and formatting the condition as a CASE WHEN switch, but neither helped.  I know I could alter this into a dynamic query, but that seems excessive for something that seems like it should be fixable in a simpler method.


texts:
I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source.

Is there a non proprietary solution or is this feature even supported by the new SQL Server 2016?
I checked that as well could not find anything specific though.


texts: How to pass value to ON CONFLICT clause that's not a column?

texts:
Basically, I'm trying to do multiple inserts in a single command in postgresql, where my ON CONFLICT has a per-insert parameter.

Here is the query (formatted for psycopg2):

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text,
     priority, type, addtime, state)
VALUES
    (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s,
      %(priority)s, %(type)s, %(addtime)s, %(state)s)
ON CONFLICT (url) DO
    UPDATE
        SET
            state           = EXCLUDED.state,
            starturl        = EXCLUDED.starturl,
            netloc          = EXCLUDED.netloc,
            is_text         = EXCLUDED.is_text,
            distance        = LEAST(EXCLUDED.distance, web_pages.distance),
            priority        = GREATEST(EXCLUDED.priority, web_pages.priority),
            addtime         = LEAST(EXCLUDED.addtime, web_pages.addtime)
        WHERE
        (
                web_pages.ignoreuntiltime < %(ignoreuntiltime)s
            AND
                web_pages.url = EXCLUDED.url
            AND
                (web_pages.state = 'complete' OR web_pages.state = 'error')
        )
    ;

I want to be able to pass multiple VALUES tuples, but %(ignoreuntiltime)s can vary per value-tuple.

Is there any way I can specify it as a additional parameter in the values tuple, or similar, for each row?

What I want is kind of:

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text,
     priority, type, addtime, state, not_a_column)
VALUES
    (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s,
     %(priority)s, %(type)s, %(addtime)s, %(state)s, %(ignoreuntiltime)s)
    ....(more value tuples here)....
ON CONFLICT (url) DO
    UPDATE
        SET
            state           = EXCLUDED.state,
            starturl        = EXCLUDED.starturl,
            netloc          = EXCLUDED.netloc,
            is_text         = EXCLUDED.is_text,
            distance        = LEAST(EXCLUDED.distance, web_pages.distance),
            priority        = GREATEST(EXCLUDED.priority, web_pages.priority),
            addtime         = LEAST(EXCLUDED.addtime, web_pages.addtime)
        WHERE
        (
                web_pages.ignoreuntiltime < EXCLUDED.not_a_column
            AND
                web_pages.url = EXCLUDED.url
            AND
                (web_pages.state = 'complete' OR web_pages.state = 'error')
        )
    ;


texts: How to make foreign key reference to column in parent table of specific table

texts:
I am using mysql database, by using it I created five tables course, section, subsection, school and school_course without any problems

CREATE TABLE `course` (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE `section` (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    courseId BIGINT NOT NULL,
    FOREIGN KEY (`courseId`) REFERENCES `course` (`id`)
);

CREATE TABLE `subsection` (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    sectionId BIGINT NOT NULL,
    FOREIGN KEY (`sectionId`) REFERENCES `section` (`id`)
);

CREATE TABLE `school` (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE `school_course` (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    schoolId BIGINT NOT NULL,
    courseId BIGINT NOT NULL,
    FOREIGN KEY (`schoolId`) REFERENCES `school` (`id`),
    FOREIGN KEY (`courseId`) REFERENCES `course` (id),
    UNIQUE (`schoolId`, `courseId`)
);

The problem comes one when I created school_course_subsection table

CREATE TABLE `school_course_subsection` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `schoolId` BIGINT NOT NULL,
    `courseId` BIGINT NOT NULL,
    `subsectionId` BIGINT NOT NULL,
    FOREIGN KEY (`schoolId`, `courseId`) REFERENCES `school_course` (`schoolId`, `courseId`),
    FOREIGN KEY (`subsectionId`, `courseId`) REFERENCES `subsection` (`id`, `sectionId.courseId`)
                                                                             ^^^^^ ERROR
);

What I want to do is check if the inserted row's

schoolId is belongs to courseId
subsectionId is also belongs to courseId; in other words subsection's section's courseId must belongs to courseId

if the two conditions are met the row will inserted without any errors otherwise it will not

My question is in school_course_subsection table how to make

courseId foreign key reference to

courseId column which is in section parent table of subsection table


texts:
I have a PostgreSQL database on a docker container with 50MB of data:
SELECT pg_size_pretty(pg_database_size('db_1'));
 pg_size_pretty
------------------
 50 MB

but the volume size is 8.5GB :
sudo du -hd1 db_volume
8.5G    db_volume

and my docker-compose is like this:
version: '3.8'

services:
  r3-db:
    image: postgres:12
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: PASSWORD
      POSTGRES_USER: USER
      POSTGRES_DB: db_1
    volumes:
      - ./db_volume/:/var/lib/postgresql/data:Z

I try using VACUUM(FULL, ANALYZE, VERBOSE) on any table but the file size of the database doesn't change!
The volume contains many files named core.NUMBER with size of 145MB,

How can I reduce disk usage of the database?


texts: What difference does it make if my COLLATION_CONNECTION is utf8mb4_0900_as_cs or utf8mb4_0900_ai_ci

texts:
My default collation_connection was 'utf8_generic_ci' and it caused an issue today with a polish character not fitting, thowing an error (the table has utf8mb4_0900_as_ci)

So I changed the connectin to utf8mb4_0900_as_cs but I don't know if that can affect performance, matching or returned data beyond the utf8 encoding.
Is there any difference between ci and cs in the connection collation ?

I of course understand the difference in column/table collation.


texts: Oracle 11g XE - Giving granting privileges to a stored procedure

texts:
I need to use a stored procedure to give certain privilges over a view to users.

However I don't know how to give the procedure the necessary privileges to grant users privileges.


texts: How to prevent SQL Log from being too full

texts:
I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even though there are a lot of transactions, the actual data is pretty small.

Now the problem I have is that when the log reaches his maximum value, which is pretty big to being with, the database will not accept anymore transactions. I can't really put unrestricted growth since I do have a physical limit to respect.

My initial understanding was that once the log reaches full size , it will simply take the oldest transaction and delete them to make space for the new ones, which would be ok in our case. However, this does not seem to be the case here. I am no expert on SQL so I am at a lost here.

I understand this log is used for backups, but so far we don't have plans to implement backup because we are still on testing phases. Also we won't be the one performing it, so we have to wait for now.

Thanks


texts:
I have been looking at sample questions on logging for 70-463 exam. For the below, the answers given do not make sense to me.

The first answer given does not even say how to implement logging. I would have selected C as the answer.

The second answer suggests using dtexecui, which is for legacy only? Again I would use the 'Create an OnError event handler' (F) as the answer.

Am I wrong?

Thanks!


texts:
I have SQL Server 2005 which is installed on server machine.

I was working fine on SQL server before I change the server machine IP. I changed server machine IP to host the Web Service on it.

But now when I enter the Login and Password information SQL Server displays this error message:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I also tried to remove Server machine IP, Still I can't able to access the SQL Server.
My all database stucked in it. Or can I recover my SQL server database?


texts: SQL DATEDIFF coercion differences between databases on same SQL instance?

texts:
I have a data coercion mystery. I am seeing two different behaviors from DATEDIFF for the same query, and I cannot understand why.

This is an extract of the relevant part of the query in question, with fixed values. The first value represents "today" in our query, and was set up with the same data type with an explicit CAST. The other value being passed in is from C# DateTime.MinValue. I am aware that it is below the valid SQL DATETIME range, but I am interested in the differences here:

-- dates in ISO 8601 literal format to avoid ambiguity
SELECT DATEDIFF(dd,CAST('2014-03-24' AS SmallDateTime),'0001-01-01')

We have two different databases on the same SQL server instance.

Server one returns an error about the date range of the '0001-01-01':

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Server two returns a value that looks ballpark correct:

-735315

The problematic date is almost certainly '0001-01-01' and it fails as I expect for a datetime, as it is below the minimum SQL datetime of Jan 1, 1753.
(https://msdn.microsoft.com/en-us/library/ms187819.aspx)

According to the MSDN page for datediff (https://msdn.microsoft.com/en-US/library/ms189794(v=SQL.105).aspx), it can accept the following values:

 startdate is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset

Cast results for each of these are identical between servers, and are listed here:

SELECT CAST('0001-01-01' As time) -- works: 00:00:00.0000000
SELECT CAST('0001-01-01' As date) -- works: 0001-01-01
SELECT CAST('0001-01-01' As smalldatetime) -- error: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As DateTime) -- error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As datetime2) -- works: 0001-01-01 00:00:00.0000000
SELECT CAST('0001-01-01' As datetimeoffset) -- works: 0001-01-01 00:00:00.0000000 +00:00

The error explicitly calls out a failed DateTime conversion, so that appears to be the coercion of choice on database one.

It seems database two uses a different coercion that succeeds and does the datediff math correctly.

Because both of these databases are on the same SQL instance, I am ruling out instance settings.

Here are a few database settings we thought to check, and they appear identical between the two databases as well (checked in SQL Server Management Studio):

Database Collation (should be per server, but included for clarity):

(database) > Right Click > Properties > General > Maintenance > Collation
Database one: SQL_Latin1_General_CP1_CI_AS
Database two: SQL_Latin1_General_CP1_CI_AS

Date Correlation Optimization Enabled:

(database) > Right Click > Properties > Options > Misc. > Date Correlation Optimization Enabled
Database one: False
Database two: False

Two Digit Year Cutoff:

(database) > Right Click > Properties > Options > Containment > Two Digit Year Cutoff
Database one: 2049
Database two: 2049

User options date format

DBCC USEROPTIONS
Database one, dateformat: mdy
Database two, dateformat: mdy
(other settings appear identical)

I'm happy to provide other settings, or test query results, let me know what you'd like to see.

Why are the two databases behaving differently for this identical query? Why does it appear that the coercion chosen is different?


texts: How to calculate a total by comparing current and next row

texts:
I have a table with some rows:

I have a few questions to accomplish the task altogether. Is it possible to calculate total of transmitted and received based on checked_date and ip and connect_time using mysql query? If yes, can I use logical operators to calculate total of transmitted and received? To get correct total transmitted and received I need to use a formula like:

if connect_time[i] > connect_time[j]
then total += transmitted[i]+transmitted[j]
else total += ABS(transmitted[i]-transmitted[j])
end

How to get it done with this formula, does anyone have an idea? P.S. i = current row and j = next row.


texts: More efficient to do SELECT and compare in Java or DELETE and INSERT

texts:
I am hitting a REST API to get data from a service. I transform this data and store it in a database. I will have to do this on some interval, 15 minutes, and then make sure this database has latest information.
I am doing this in a Java program. I am wondering if it would be better, after I have queried all data, to do:

SELECT statements and compare vs transformed data and do UPDATEs (DELETE all associated records to what was changed and INSERT new)

OR

DELETE ALL and INSERT ALL every time.

Option 1 has potential to be a lot less transactions, guaranteed SELECT on all records because we are comparing, but potentially not a lot of UPDATEs since I don't expect data to be changing much. But it has downside of doing comparisons on all records to detect a change

I am planning on doing this using Spring Boot, JPA layer and possibly postgres


texts: Veryfing integrity of custom order column with pure SQL

texts:
I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper elements of structure both tables have sort column (of type integer). Both structure and entities can mix on a given level, so sort numbers are in effect "dispersed" between the two tables, but required to be continous (so 1,2,3,4 and not 1,4,5,9).

The problem is that some app most probably screwed that order, to be not continous and I'd like to fix it. I can create UNION query to inspect order at certain level, but I have to check numbers visually to determine if they are in fact continous (which is error prone). So the question is: can I, using pure SQL, determine where the order is not continous? I guess I could get redundant order numbers (eg. 1,2,2,3) by some wicked GROUP BY. Not sure how to catch "holes" (eg. 1,2,4,5);

EDIT: As requested, below you can find a simplified example of the problem:

CREATE TABLE structure (
  structure_id INTEGER,
  structure_name TEXT,
  structure_parent INTEGER,
  structure_sort INTEGER
);

CREATE TABLE entity (
  entity_id INTEGER,
  entity_name TEXT,
  entity_structure INTEGER,
  entity_sort INTEGER
);

INSERT INTO structure VALUES (1, 'root', null, 0);
INSERT INTO structure VALUES (2, 's1', 1, 1);
INSERT INTO structure VALUES (3, 's2', 1, 3);
INSERT INTO structure VALUES (4, 's3', 1, 7);

INSERT INTO entity VALUES (1, 'e1', 1, 2);
INSERT INTO entity VALUES (2, 'e2', 1, 2);
INSERT INTO entity VALUES (3, 'e3', 1, 5);
INSERT INTO entity VALUES (4, 'e4', 1, 8);

Sequences, referential integrity and some meaningless columns skiped for brewity. Here is how you can see all the flaws in order numbers:

SELECT
  structure_id as "item id",
  structure_name as "item name",
  'structure' as "item type",
  structure_sort as "item sort"
FROM
  structure
WHERE
  structure_parent = 1
UNION
SELECT
  entity_id as "item id",
  entity_name as "item name",
  'entity' as "item type",
  entity_sort as "item sort"
FROM
  entity
WHERE
  entity_structure = 1
ORDER BY
  4;


texts: Unnest ids from arrays based on priority with no duplication

texts:
Here is the sample schema for my database:
Priority  | Productive | UnProductive | Neutral |
-------------------------------------------------
  High    |   [1, 2]   |      []      |  [4, 5] |
  Medium  |   [3, 4]   |    [5, 7]    |   [2]   |
  Low     |     [1]    |    [2, 6]    |    []   |

NOTE: These numbers are actually UUIDs in my database and no numbers will be repeated in the same row.
I want to unnest the category from productive, unproductive and neutral such that:

Not any from 1 to 7 numbers are repeated and
If the number is already captured by High Priority it will not be captured by Medium and same goes for Low.

Expected output:
Productive: [1, 2, 3]  # no 4 because it have been captured by neutral High priority
UnProductive: [7, 6]  # no 5 and 2 because it have been captured before
Neutral: [4, 5]  # no 2 because it is caputed before


texts: Upgrading to SQL Server 2012 from SQL Server 2008

texts:
I have upgraded my SQL Server 2008 R2 Enterprise edition to SQL Server 2012. Even after upgrade I can see & use SQL Server 2008 R2 instance without any issue. Did my upgrade went fine?

I can open default instance both from SQL Server 2008 and 2012. Also I have updated the compatibility level of all databases to 110.

After in-place upgrade

Edit - version

After running

SELECT @@Version

SQL Server 2012

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

SQL Server 2008 R2

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


texts: I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.


texts: ORA-01654: unable to extend index  by 128 in tablespace

texts:
I am not a DBA but have to resolve this issue.

There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO tablespaces

Last week I got the exception

  ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>.

I got the tablespace size increased considerably. But still I am getting this error.

I deleted a lot of data thinking that would free-up some space. But that’s of no use.

Will shrinking the tablespace or re-sizing the datafiles of the permanent tablespace resolve this issue?

Also, the statistics hasn’t been gathered since March. Will gathering the latest statistics help me in any way?


texts: SQLite fails to delete the foreign keys with the cascade delete

texts:
I have a simple SQLite database with a foreign key on a master table. This table is a relational table to help mapping Table A to Table B.

CREATE TABLE IF NOT EXISTS "PERSON"
(
 "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 "NAME" TEXT NOT NULL,
 "SURNMAE" TEXT NOT NULL,
 "LAST_MOD" TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS "DEPARTMENT"
(
 "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 "DEPNAME" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "PERSON_DEPARTMENT"
(
 "PER_ID" INTEGER NOT NULL,
 "DEP_ID" INTEGER NOT NULL
               FOREIGN KEY (PER_ID)
                  REFERENCES PERSON (PER_ID)
                     ON UPDATE CASCADE
                     ON DELETE CASCADE
                  FOREIGN KEY (DEP_ID)
                  REFERENCES DEPARTMENT (DEP_ID)
                     ON UPDATE CASCADE
                     ON DELETE CASCADE
);

Now so long I delete a couple of records using the visual interface, rows in the related table are deleted too as well as enforced for not being orphan.

However, if I do a truncate via a DELETE FROM PERSON, all the records in the related table remain in place. I can't understand why.
Any hint?


texts:
Made a mistake with mysql restore on the production site instead of the staging one. Can I use mysql general log to restore? It seems general log is all I have. I dont seem to have a binlog with MariaDB's Aria storage engine.
What should I do now? Anyone has any experience using general log to recover the database?


texts: Pause postgres process already in progress

texts:
A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has limited RAM so both our processes are running extremely slowly. My colleague's query (the creation of a materialized view) typically takes 4 hours to complete but has now been running most of the day.

Since my colleague's query is more important than mine and needs to be completed by a certain time; is there a way I can pause my query thereby increasing the available RAM and allowing my colleague's process to complete?

My query is a large 'update' to a table and ideally I don't want to kill it a have to start all over again. Is there anyway I can reduce or temporarily suspend the resources consumed by my process?

Postgres 9.4/Redhat 6.8


texts: Does osql.exe lock tables while exporting?

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

I am using SQL Server Workgroup Edition (10.52.4000.0).


texts: Which accented characters are considered the same?

texts:
In MySQL, certain characters are considered the same when performing searches, e.g. 'n' and 'ñ'; unless you specify BINARY in the condition.

Is there a list somewhere of the characters that are considered the same?


texts: Over the weekend, I was participating in a DR test. On the database side, that meant restoring backups from the production system to the DR system. What struck me as odd about the performance of the restores was that it seemed to read the backup file quickly enough (i.e. would report 100%), but would then spend a lot of time before actually reporting the restore complete (i.e. x pages restored to file n, y pages restored to log file). Once I copied the backup files to be local to the DR system, everything went quickly. What explains this behavior?


texts: Configure Postgresql column capitalization with being able to query using different capitalization

texts:
I have an existing database that we need to replace from MySQL to PostgreSQL. The problem being that we currently have two applications that use the database (one to create and one to view the data) that use different capitalization.

As the viewing application is in php it will grab the data using the following:

$row["Data"]

But the column "Data" doesn't exist because PostgreSQL converts it to lowercase to there is only the column "data".

A solution to this would be to rewrite the database and use "Data" in the queries and make the queries case-sensitive. The only problem is that this would break the creating application as it uses the lowercase variant.

So the question is, if there is some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?


texts: Indexes with the same object_id but different names

texts:
When I run

SELECT
  count(object_id) AS count,
  object_id,
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count

I will get a list of indexes having the same object_id, but not necessarily with the same name.

Should not all indexes with the same object_id have the same name?


texts: SSAS 2016 and XMLA: Problem retreiving "MDSCHEMA_ACTIONS" Rowsets under specific scenario

texts:
I'm developing a Web client that uses XMLA over HTTP calls to communicate with a SSAS 2016 Cube.

When my client's user clicks on a cell in a pivot grid that is based on this cube, I would like to show him a list of available SSAS "Actions" to perform.

To do so, my code uses a XMLA "DISCOVER" command with "MDSCHEMA_ACTIONS" Request Type, while restricting on the specific cell path.

I do manage to get results when I restrict on one measure and one dimension attribute member (Scenario A) - I get a rowset with one action ("the Action")

But, if I add another dimension attribute member (Scenario B), I get en empty rowset.

Naturally, I thought that this is due to the way the DBA has configured the Actions for the cells, but to my surprise, when I used MS Excel Pivot Table and queried the actions for the exact same cell (Scenario B), I did got the same Action (as wished).

I would like to use your experience to find some clues as to what might cause the different results between the two clients, given that the only apparent difference is adding the second dimension attribute.

Here are the Coordinates for both scenarios:

Scenario A (Returned an Action):

(
[BusinessDate].[CURRENTWEEK].&[1],
[Measures].[Total Payment Amount]
)

Scenario B (Failed to return an Action):

(
[BusinessDate].[CURRENTWEEK].&[1],
[Owners].[Owner Id].&[34876],
[Measures].[Total Payment Amount]
)

And here is the XMLA wrapper for BOTH Scenarios:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
 <SOAP-ENV:Body>
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
   <RequestType>MDSCHEMA_ACTIONS</RequestType>
   <Restrictions>
    <RestrictionList>
     <CATALOG_NAME>my_catalog</CATALOG_NAME>
     <CUBE_NAME>my_cube_name</CUBE_NAME>
     <COORDINATE_TYPE>6</COORDINATE_TYPE>
     <COORDINATE>COORDINATE-GOES-HERE</COORDINATE>
    </RestrictionList>
   </Restrictions>
   <Properties>
    <PropertyList>
     <Format>Tabular</Format>
    </PropertyList>
   </Properties>
  </Discover>
 </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Appreciate any help!

PS I've tried to sniff with wireshark how exactly the Excel request looks like, but it looks like encoded ASCII that I couldnt understand.


texts: Reason to not use nullable number in Oracle?

texts:
Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel value); the reason is that no number column in their Oracle database supports null values, on the recommendation of their (now former) Oracle dev. This company also writes the vast majority of their code in VB6 (slowly transitioning to VB.NET, which is another topic for another day...).  Out of pure curiosity, is there any valid reason for this recommendation? I can't think of any on my side.

--- edit

Thanks for the feedback all. I posed the same question on CodeProject.com (link) and received very similar feedback. It appears the only time one could possibly begin to justify this practice is related to foreign keys, and I can state that they use no foreign keys anywhere in the system. The developer that made this determination (I used to work at that company) has significantly more experience than I, so I wanted to make sure there wasn't a valid reason for this before derision ensues.


texts:
I am executiong query, that produces plan:

Statistics IO:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 128, logical reads 5952, physical reads 576, read-ahead reads 6080, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 9, logical reads 90450, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, Some questions

1. Why Statistics IO show higher reads than Profiler?.

As to KB314648 it is OK if if Profiler reports higher numbers than Statistics IO. But Profiler shows  92283 reads at  described query, same execution. Does that mean that Profiler does not count workfile/worktable reads?

2. What is difference between "Worktable" and "Workfile"

I have found that:

work files could be used to store temporary results for hash joins and hash aggregates.
work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

Is there some physical difference between them?

3. Why there is "Worktable" in this particular case?

Why do i have worktable at all, if there is 0 logical reads? Its included in statistics IO just because there might be need for it (in case of bad estimates)?

Description found on technet seems vague..

4. What "Physical reads" on Workfile means?

Does that mean that there was not enough memory granted to query, so data had to be written to disk while query executes (yellow warning on Hash Match)?
Can i assume that whenever I see worktable/workfile in Statistics IO with physical reads, there has been not enough memory granted for query and some intermediate results of query had to bee written to tempdb disk?
And whenever i see only logical reads, RAM is used?

5. One "workfile" means exactly one table that is used for one purpose?

If there is more that one workfile/worktable then i cannot know to what operation it is used?


texts:
We have a database, where we insert data into table whenever we receive an order from our services.

We use auto_increment to generate the ID and assign the order with the same.
This is usually same everywhere.

But the problem is, since this information is passed to third party vendors, they are able to trace, how many orders we are receiving per day. By sending a request in the early morning and sending another request in midnight and count the difference in the ID.

This is giving insight information of our services. Since it's bit complex system, we can't do complex operation that can randomise this.

My initial idea is to generate auto_increment numbers in bit random fashion way.

1,2,3,4,5,67,68,69,70,71,133,134,135,136,137,189,190,191,192,193,194

I guess it should be simple, as Mysql runs out of cache, generate next sequence by appending some random number.

How to achieve this without impacting the performance?


texts:
CDC has been set up for one of our databases in our test environment but it's not capturing any of the changes. I've made sure the capture job is running. I've disabled CDCs for the database, enabled them again and added the tables back, still not working.

I'm not seeing any errors (checked the job and sys.dm_cdc_errors), any suggestions on what to check next?

This database is set up in an availability group on a SQL 2012 server.

Thanks,
Tim


texts: Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of data size?

texts:
I have two different production systems where the total time for an ETL (Extract, Transform, Load) reporting process (Power BI Report Server) takes around 47 to 60 minutes whether the extract phase pulls data for 15 minutes, 1 hour, 12 hours, or 24 hours. Yes, there are vastly different amounts of rows for those ranges.
Is this normal?
I had expected that the total time for ETL when there is NO data would be .... well... a small amount of total time.  But I must confess that I know nothing about the internals of the T and L phases within a report server.
Can someone shed light on whether the T and L phases are typically fixed in duration (up to a point)? I suspect that this process has not been examined or tuned.

UPDATE
In regards to the T&L phases, I have a theory but no proof.
In the extract phase for these two systems, the total time for the queries remains about the same. 6 minutes regardless of how much data is being pulled. Why? Because a handful of the queries use clustered index scans. I've documented this problem in other questions.
My theory/fear for the T&L phases - is that something similar is happening; a clustered index scan such that it doesn't matter (to a point) how much data is being processed.
But as I mentioned, I have no knowledge (yet) of the inner workings of a Power BI report server's Transform and Load phase. I am making simple observations and curious why the total time does not change when no data is pulled.


texts: Ordering a parent table by SUM() of colums of two of its child tables

texts:
I know that title might sound confusing, but i will try to explain my problem as much as i can.

I have four tables:

companies{
id,
name,
address
}

stores{
id,
name,
address,
company_id
}

invoices{
id,
total,
date_time,
store_id
}

receipts{
id,
total,
date_time,
store_id
}

As you can see, store is linked to a company and both invoices and receipts are linked to a store.
I am having a problem writing a query which will return a list of stores by a company name, ordered by their turnover.

For example:

SELECT s.name as storename, SUM(i.total) as turnover
FROM companies c
  INNER JOIN stores s on s.company_id = c.id
  INNER JOIN invoices i ON i.store_id = s.id
WHERE c.name = 'Com1'
AND QUARTER(i.date_time) = 4
AND YEAR(i.date_time) = 2019
GROUP BY s.name
ORDER BY turnover DESC

I know how to order a store by a sum(invoices.total) or by sum(receipts.total), but i struggle to find a way to combine the two. I wish for end result to be something like this.

+-----------+----------+
| store_name| turnover |
+-----------+----------+
|    store1 | 1231.43  |
|    store2 | 2342.34  |
|    store3 | 4323.21  |
+-----------+----------+

Where turnover will be the SUM of SUM(invoices.total) and SUM(receipts.total)


texts:
Rather than merging rows I need to 'expand' rows, here's an example to explain:

|Material|count|
|Rock    | 3   |
|Gold    | 2   |
|Silver  | 1   |
|Wood    | 2   |

I need one row per material e.g. translated to this

|Material|count|
|Rock    | 1   |
|Rock    | 1   |
|Rock    | 1   |
|Gold    | 1   |
|Gold    | 1   |
|Silver  | 1   |
|Wood    | 1   |
|Wood    | 1   |

Is there an sql solution for this type of problem?


texts: Postgresql 9.2.6 ignoring archive_command

texts:
I'm attempting to set up a 3-server chain by leveraging WAL-E.  I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.

My replication from a -> b is working perfectly through WAL-E.  I specify the S3 bucket and access information and a uploads to S3, and b pulls down segments it may need if streaming falls behind.

I'm having an issue trying to get b to upload segments to S3 for use by server c.  I've updated my postgresql.conf on b to be:

wal_level = hot_standby
checkpoint_segments = 10
archive_mode = on
archive_command = '/etc/postgresql/9.2/main/local_backup_script.sh "%p" "%f"'
archive_timeout = 3600
max_wal_senders = 5
wal_keep_segments = 4096
hot_standby = on

My local_backup_script.sh is as follows:

#!/bin/bash

echoerr() { echo "$@" 1>&2; }

FAIL=0

`envdir /etc/wal-e.d/env-rep-a /usr/local/bin/wal-e wal-push $1` & pid_1=$!

echoerr "Spawned replication process $pid_1"

wait $pid_1 || let "FAIL+=1"

if [ "$FAIL" == "0" ];
then
echoerr "Replication success $1 $2"
else
echoerr "Replication failed $1 $2"
fi

exit "$FAIL"

I am using a different S3 bucket from b -> c so they don't step on each other.  I've also verified that wal-e upload works to this bucket by manually calling the script from the pg_xlog directory.

But postgresql NEVER executes archive_command, and I'm not at a loss as to why.  I even just tried to print something to STDERR (so it goes to the log), nothing.

Few other points:
1) /var/lib/postgresql/9.2/archive directory is empty (on b)
2) the script permissions have been set properly (owned by postgres and chmod +x)

Anyone have any suggestions on what may be wrong?


texts:
oracle9i/windows2003,java application.

oracle crash, alert log file contains sdh_ora_4636.trc which contains:

*** 2012-05-05 13:53:32.000
*** SESSION ID:(100.50539) 2012-05-05 13:53:32.000
FATAL ERROR IN TWO-TASK SERVER: error = 12571
*** 2012-05-05 13:53:32.000
ksedmp: internal or fatal error
Current SQL statement for this session:
SELECT
   ID, CATEGORY, RESTOID, RESTAROID, CREATETIME, SNMPORT, UPDATETIME,
   ALIAS,SERIALID, ISOFFLINE, VERSION, SNMPREAD, DESCRIPTION, NAME, SNMPWRITE,
   MAXALARMSEVERITY, PHYSICALADDRESS, DN, IDENTIFIER, IPADDRESS, VENDORID, CITYID,
   REGIONID, MEMODELID, MEMODELNAME, SECTOR, PRESTATUS, STATUS, BASESTATION, HOTPOINTID, ACID
FROM MANAGEDELEMENT WHERE (NAME = :1)

I checked the SQL; it runs quickly.

Does anyone have an idea as to why this is happening? Would upgrading my database solve the problem?


texts: MS SQL Server Incremental(differential) backup interrupted due to Full backup

texts:
I have a manual full backup happening every night, post which I am taking differential backup after every hour but after  three to four differential backups a full backup happened through third party automated backup happen, which is not in my control.

Can any please suggest the best approach to be followed if I don't want to stop the automated backup taken by third party and differential backup not to be failed because of this?.


texts: SQL Server Developer Edition Downgrading to Older Developer Edition

texts:
I have seen answers for questions regarding downgrading from different editions (e.g. - Enterprise to Standard), but is anyone familiar with downgrade policies for SQL Server Developer editions to OLDER developer editions?

We currently have a need for 2008 R2 developer editions, which are no longer available unless purchasing via reseller.

We found a reseller, but our company's planned upgrade path includes 2012 in the next 6 to 12 months. It does not make sense for us to purchase 2008 R2 dev edition if we can purchase 2012 or 2014 at retail prices, downgrade for the short term, then upgrade once our enterprise does.

Has anyone done this before? I'd prefer an answer here versus the reseller as their answer is going to be sales-centric (to be polite) versus a more straight forward answer from someone who may have done this.


texts:
On the Oracle Database Appliance, the default deployment only gives you a single control file.

I find this a little puzzling. The single control file causes a policy violation in the automatically configured Enterprise Manager DB Console and Oracle's recommendation is still, as far as I can tell, that you should always have at least two control files on separate drives and file systems.  Personally, I've always had three copies, just to be on the safe side.

The ODA is configured with ASM and does have a good bit of storage redundancy using triple mirrored drives. Is it OK to run with a single control file in this configuration?

Adding a second control file to the same disk group might not make much sense, would multiplexing the control files to the SSD disk group or perhaps the OS drives of each node make more sense?


texts:
I want a PL/SQL procedure to give me progress updates as it runs. However, DBMS_OUTPUT seems only to give me the output when the whole procedure is done.

Is there a way to make it give me updates during runtime?

Thanks!


texts: SSMS 2016 Status bar -no option to see session id

texts:
I am using SSMS 2016 version.

Microsoft SQL Server Management Studio                      13.0.16106.4
Microsoft Analysis Services Client Tools                    13.0.1700.441
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

In previous version I was able to see session id at the status bar or tab text.
I could navigate to Tools-->Options-->XML-->Editor Tab and Status Bar and turn it on of off.  I do not see the same in SSMS 2016.

Anybody know if this option is removed or relocated under other option.


texts: Does INFORMATION_SCHEMA always contain up-to-date metadata?

texts:
This query prints the size (in terms of data and indexes) of the tables in mydatabase:

SELECT table_name "Table name",
round(((data_length)/1024/1024),2) "Data size",
round(((index_length)/1024/1024),2) "Index size"
FROM information_schema.TABLES
WHERE table_schema="mydatabase" AND data_length>1000000
order by table_name INTO OUTFILE '/tmp/mydatabase_values'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Sizes are printed in Mb, and only tables larger than 1Mb are considered. Tables are listed in alphabetical order and the output is saved into a CSV file.

Running this query at different points in time showed - obviously - different results as data changed in the database.  However, just in the last few weeks the query produced identical results.  Does it mean that the database hasn't changed much in size (as you can see, rounding is done at the 10Kb boundary) or is there something I am really missing?  The question might sound absurd, but do the views in INFORMATION_SCHEMA always hold up-to-date metadata?

Note: no, I am not reading each time the same CSV file by mistake.

Edit: all tables are InnoDB, and innodb_file_per_table=1.


texts:
I'm using Oracle 11g databse. For logical backup, we are exporting database on daily basis using below command  and dump size is almost 12GB. , but now I'm facing an issue with my expdp.

expdp 'sys/sys#123@Test as sysdba' DUMPFILE=Backup_04042017.dmp LOGFILE=BakupLog_04042017_1603.log FULL=y consistent=y

Export gets completed successfully, but two tables are not getting exported. The following error is shown in the log.

ORA-31693: Table data object "Owner"."Table_1" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

As read from many blogs, I've set undo_retention value from default to 36000 and set the retention for its corresponding table using below command.

alter table OWNER.TABLE modify lob(DESCRIPTION_HTML_) (retention);

It doesn't work as expected. So, I've set PCTVERSION to 40% using below command.

ALTER TABLE OWNER.TABLE MODIFY LOB (DESCRIPTION_HTML_) (PCTVERSION 40);

My bad, that too didn't work.
Also I tried to export the single table which also given me the same error.

Can I set PCTVERSION more than 40%? Does it inversely affect my production database?

Following are the results that I got related to undo size where my optimal undo_retention is showing lower value than undo_retention. Moreover, needed_undo_size is greater than actual_undo_size.

also,

Do I need to increase undo_size? How it should be calculated based on my database configuration?


texts: While trying to unlock the HR user, I am getting an error saying, “user HR does not exist”.


texts:
I was testing a logon trigger to restrict connections to certain IPs. It looks like the trigger was not correct. Now I can't log in to the server even as SA.

Is there a way to fix this?


texts: Should Query Tuning be Proactive or Reactive?

texts:
As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server).  I make the best possible design prior to and during development.

But, just like any other software developer, there is added functionality, bugs, and just change of requirements that demands altered/created database objects.

My question is, should query tuning be proactive or reactive?  In other words, a few weeks after some heavy code/database modification, should I just set aside a day to check out query performance and tune based off of that?  Even if it seems to be running okay?

Or should I just be aware that less-than-average performance should be a database check and going back to the proverbial chalkboard?

Query tuning can take up a lot of time, and depending on the initial database design it could be minimal benefit.  I'm curious as to the accepted modus operandi.


texts: SQL equivalent of the "relocate all files to folder" option?

texts:
When restoring database using the GUI, there's an option "relocate all files to folder" which is very convenient.

I wonder if there's the same thing if I'm restoring db using T-sql?
To me, finding out logical file names then put into the move clause seems to be too tedious and unnecessary.


texts:
I am trying to daisy chain ids through 3 tables. I need to insert a line, take the inserted id and insert that into the next insert, which then takes that new id and inserts it into the final table. My current code looks like:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS '
BEGIN

        INSERT INTO transaction_info(tenant_id)
        VALUES ($1);

        INSERT INTO entry(transaction_info_id)
        VALUES(NEW.id);

        INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
        VALUES ($2,$3,$4,$5,NEW.id);

        COMMIT;
END;
'

but I've tried a bunch of other configs.

This code returns ERROR: missing FROM-clause entry for table "new".

I've tried using the RETURNING clause but then I get ERROR: query has no destination for result data. It seems to me that if I use RETURNING then the sequence stop there? Something like that.

I've also tried using the WITH clause but I REALLY don't understand how that works yet. I can't figure out from the documentation and I'm having trouble understanding a lot of the questions/answers about the WITH clause.

Everything I can find seems to be aimed at gathering SERIALS but my ids are UUID so I can't use the lastval() type clauses I believe.

I'm very new at this so I apologize if this has already been answered and I just don't understand it but I've been searching and trying to figure this out for 2 days now and I'm really hitting a brick wall. I only have a couple of hours each night to learn and code so it would mean a lot if someone could point me in the right direction.