texts: PostgreSQL Trigger to Update Inserted Row With Data From Another Table

texts:
I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the sensor data as they are inserted into SENSORS. Each row of sensor data will be updated with the corresponding label name from LABELS. Unable to get the row update to work. Sample shown below:
Sample Dataset
CREATE TABLE sensor (
   datavalue integer,
   created timestamp,
   label_id integer,
   label_name text);

CREATE TABLE labels (
   id integer,
   label_id integer,
   label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

Trigger Function
CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$func$
BEGIN
UPDATE sensor
    SET label_name = b.label_name
    from labels b
    where new.label_id = b.label_id;
     RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

Test row insertion
Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

I have a very large number of rows flowing in, and would like to update each row as it is inserted.
Any assistance greatly appreciated. Thanks!


texts: Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

texts:
We have a program that transfers cobol data (index files) to a database.

Until now we transferred the data to an oracle database.

In the beginning of the program I logged in with user sys and then I altered the current schema to ink. I did this in this way, because ink has no privileges to create a table.

Then I created a table and the table owner was automatically ink, not sys.

We recently have switched databases from Oracle to Postgres.

Here I log in as "postgres", then I switch to the "ink" schema with:

set search_path to 'ink';

Then I create a table and the table owner automatically is postgres, not ink.
So this behavior is reverse.

What do I have to do in order to get the same behaviour as with the oracle database:

In my scenario the table owner should automatically be ink, not postgres.

It would be nice if I would not have to write

ALTER TABLE ... OWNER TO ink;

after each create table command.


texts: Optimize simple query using ORDER BY date and text

texts:
I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records

The data types of the table are:

fcv_id = serial
fcv_fecha_comprobante = timestamp without time zone
fcv_numero_comprobante = varchar(60)

The query is:

SELECT fcv_id, fcv_fecha_comprobante FROM factura_venta
ORDER BY fcv_fecha_comprobante, fcv_numero_comprobante

This query takes about 5 seconds, but if I take out the "order by" the query takes only 0.499 seconds

The problem I have is that I need to run this query in the shortest time possible,
so I search on google what can I do and create a composite index with the following query

CREATE INDEX factura_venta_orden ON factura_venta
USING btree (fcv_fecha_comprobante ASC NULLS LAST
           , fcv_numero_comprobante ASC NULLS LAST);
ALTER TABLE factura_venta CLUSTER ON factura_venta_orden;

But the query is taking the same time or even more.

I'm using Postgres 9.0.13, here is the EXPLAIN with 73436 rows

Sort  (cost=11714.03..11897.62 rows=73436 width=27) (actual time=1260.759..1579.853 rows=73436 loops=1)
  Sort Key: fcv_fecha_comprobante, fcv_numero_comprobante
  Sort Method:  external merge  Disk: 2928kB
  ->  Seq Scan on factura_venta  (cost=0.00..4018.36 rows=73436 width=27) (actual time=0.363..162.558 rows=73436 loops=1)
Total runtime: 1694.882 ms

Postgres is running on a Phenon II 1055T (3 cores) With 8 GB Ram and 500 GB disk.

How I can optimize this query?


texts: How to use relationships to extract data via select

texts:
Disclaimer: Novice in SQL

I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys in the major table. For the sake of the argument, the tables below have been simplified down to the problem.

The database operations insert into the did_table and fid_table. These are generally very infrequent. When an event occurs, the details are captured in the used_for_lookup table. This table is then used to do SELECTS on. The columns fid and did in the used_for_lookup table are references into the fid_table and did_table.

A simplified version with data:

sqlite> select * from used_for_lookup;
id          datetime    fid         did
----------  ----------  ----------  ----------
1           2014-10-10  1           1
sqlite> select * from did_table;
id          other_ints  did_important_string
----------  ----------  -----------------------------------------------------
1           1           this needs to go into the query result from did table
sqlite> select * from fid_table;
id          other_ints  fid_important_string
----------  ----------  -----------------------------------------------------
1           1           this needs to go into the query result from fid table

The most general query is that we query used_for_lookup table between 2 date events on the datatime column.

When we do a select, like so:

sqlite> select * from used_for_lookup where datetime between '2014-10-09' and '2014-10-11';
id          datetime    fid         did
----------  ----------  ----------  ----------
1           2014-10-10  1           1

we need to "convert" fid and did column values to the fid_important_string and did_important_string values for when we report back the answer.

They are foreign keys which helps with data integrity, but I'm stumped on how do you now use the column lookups to get the strings out of other tables into one answer?

Thanks.


texts: Are parallel queries with mysqlnd actually possible?

texts:
So I have been playing with asynchronous database requests in PHP using mysqlnd in an attempt to achieve parallel database requests and cut down total execution time i.e. rather than:

total time = query 1 time + query 2 time + query 3 time + query 4 time

instead:

total time = max(query 1 time, query 2 time, query 3 time, query 4 time)

My thinking is that using MYSQLI_ASYNC makes the query non blocking allowing you to kick of a number of queries right after another with a new connection, and as MySQL handles queries in parallel natively, thus achieve execution of parallel database requests.  I am however seeing absolutely no improvement in execution time whatsoever and instead am seeing the same execution time as if the queries are run one after the other.

Where am I going wrong and is this actually possible?  (note I am a developer not a sysadmin)


texts: SQL Server 2012 Express with Reporting Services

texts:
Everywhere I search for SQL Server 2012 Express with Advanced Services, I ended up with this link, http://www.microsoft.com/en-us/download/details.aspx?id=35579, and every time I install it, I do not see where I can add reporting services as a feature.

Is there another installation file that includes Reporting Services, or do I actually have it now but cannot tell it's installed? I went to Control Panel -> Administrative Tools -> Services already and do not see it listed, which tells me that it's not installed?

Update: I've followed Kin's instructions, but just cannot advance further.  Here's where I am now. In Reporting Services Configuration Manager, the Connect button is grayed out, and when I click on Find, I get error message of "Unable to connect to the Reporting Services..." See below.

When I go to the SQL Server Configuration Manager, I do not see Reporting Services as a service listed to start of stop. See below.

So, in my mind, perhaps I don't have the Reporting Services installed, so I try to install the features, but there's no Reporting Services listed as an available feature to add. See below. I know I downloaded the .exe file that includes everything available to download, including the Reporting Services. What should my next steps be to troubleshoot further? I'm tempted to uninstall and reinstall, but I'm not confident that I can uninstall properly. There seems to be so many files related to SQL Server 2012, and I'm not sure how that would affect Visual Studio I currently have installed. By the way, I'm on a local desktop installation with Windows 7.


texts: sqlagent job - using system folder path with CmdExec

texts:
I'm trying to setup a SQL Server agent job of type CmdExec for running an executable inside %programdata%\my company folder.

If I use %programdata%\my company\myexec.exe, the job fails because it apparently can't find the file.

But it works with the absolute path:

C:\ProgramData\my company\myexec.exe

How can I run the job successfully using the special folder alias?


texts: Specify windows account for SSRS data source in SSDT project

texts:
In the properties of a data source in SSRS, I can specify a Windows domain account to use, like this:

This data source was originally deployed from a SSDT project and I need these settings to be reflected in that project - otherwise the data source will no longer work correctly the next time that the project is deployed.

Unfortunately SSDT does not seem to provide a way of doing this.  If I specify credentials here it automatically assumes SQL Server authentication (which is of no use to me).

Am I missing something, or am I once again the victim of Microsoft's inability to be consistent?


texts:
I have been pulling the total stock within the first 6 months. How can I pull the total stock within the last 6 months in the same query?

Also I want the result of the query as shown below:

  Stock Code | the first 6 months   |  the second 6 months   etc.

My SQL query:

 SELECT TOP (100) PERCENT STOK_KODU,
    COALESCE (SUM(CASE WHEN kod1 = 'G' THEN kod2 ELSE 0 END), 0)
   - COALESCE (SUM(CASE WHEN kod1 = 'C' THEN kod2 ELSE 0 END), 0) AS StokToplam1
FROM s_hareket WHERE (STOK_KODU BETWEEN '01001' AND '75000')
and (tarih BETWEEN '2011-01-01' and '2011-06-30')
GROUP BY STOK_KODU
ORDER BY STOK_KODU


texts: Decrease Insert and Update performance on bigger database

texts:
Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future workload. I did it in Jmeter application. Put all procedures in one transaction controller:

GetSomeDataFromNonPartitioneTable1
GetSomeDataFromNonPartitioneTable2
InsertNewTransaction (insert new row in two partitioned tables)
GetOneRecordFromPartitionedTable
UpdatePartitionedTable

At the beggining of the test (0 rows in paritioned tables) got performance around 1300 operations per second ("one operation" means above set of procedures running one after the other). I tried same test (with same number of virtual users) when got around 4 000 000 of records in partitioned tables (4 000 000 per table). Get performance around 1050 operations per second. Going deeper I have got average response times:
1 test (0 rows in partitoned table)

GetSomeDataFromNonPartitioneTable1 - 22 ms
GetSomeDataFromNonPartitioneTable2 - 22 ms
InsertNewTransaction (insert new row in two partitioned tables) - 160 ms
GetOneRecordFromPartitionedTable - 22 ms
UpdatePartitionedTables- 136 ms

2 test (around 4 000 000 rows in partitoned tables)

GetSomeDataFromNonPartitioneTable1 - 25 ms
GetSomeDataFromNonPartitioneTable2 - 25 ms
InsertNewTransaction (insert new row in two partitioned tables) - 217 ms
GetOneRecordFromPartitionedTable - 23 ms
UpdatePartitionedTables - 177 ms

My questions:

Why bigger table decrease performance so much ?
What can I do to avoid it ? Bigger partitioned table got 2 non clustered indexes and cluster index, and smaller got only cluster index.


texts:
When i typed the command "backup database" it mentioned

"RMAN-06169: could not read file header for datafile 5 error reason 4..."etc

and then i checked the STATUS of the datafile 5

(look at the picture)

so i tried to recover ,it didn't work.

it told me that "ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/app/oracle/oradata/abc/abc.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/abc/abc.dbf'
(datafile 7 is the same situation.)"

It reminded me that i once delected the document-abc under the graphic interface,which meant that i didn't use the command to delect the file.

so i tried to alter the datafile 5 and datafile 7..  it didn't work also.

how to alter the datafile 5 and 7? and backup database?
thanks very much~！


texts: SQL Server Debugger Permissions without Granting sysadmin to Devs

texts:
BOL documentation says sysadmin permission is required to run debugger.

I'm 90% certain there is no workaround to this requirement, but thought I would ask just in case someone found a way to grant Debugger permission without granting sysadmin permission.

What do people do when you have a team of developers needing to step through a complicated cursor loop with variables, etc to debug some aspect of that?

Most shops don't allow developers to have sysadmin permission even on development servers, and many wouldn't allow devs to keep a copy of enterprise data on their local machine with their own developer sql server edition e.g. due to PII and data security reasons.

Not sure why the debugger would be set up this way.

So, I'm curious how other people handle the requests for Debugger permission in a similar scenario.

What do you do in your environment?


texts: Setting stack-size ulimit for mongodb to reduce connection overhead

texts: I am using MongDB 2.4.9 and working on performance tunung. With reference to the ulimit settings for mongodb, in my linux environment (ubuntu 12.04) the default setting for ulimit -s is 8192. MongoDB does not specify any default value for ulimit -s (stack-size). However this case study suggests to set stack-size to 1024. How does the default stack-size 8291 impacts mongodb performance. Does changing this value to 1024 help to reduce connection per overhead in order to improve performance?


texts: Increasing column from VARCHAR(255) in master/slave setup

texts:
We have replication set up on some MyISAM servers, in a master/save scenario, MySQL v5.1.

One of the columns is currently declared as VARCHAR(255). We want to increase this to VARCHAR(512).

We have one master and 4 slaves. What is the best scenario to make the change.

If we do this:

ALTER TABLE item MODIFY url VARCHAR(512)

should that be ok, as long as we update the slaves first and then the master, or may it complain and it's best to do them all at once? Should I stop the slaves?

I've tried the update on a dev machine which takes about 10 mins, but it doesn't have replication set there. So looking for any flaws in my thinking.

Or looking further, does ALTER TABLE get replicated, and that's all I would need to do on the master?


texts: Nested Loop Output Estimate Low when expanding dataset

texts:
I have a stored procedure that is taking some header and details information of a tactic, that goes on to expand the data from a date range (Start/End) to the day level. This is then used further on in the procedure to calculate some day-level weightings. As I've been performance tuning, I've noticed one specific issue that I can't seem to solve.

If I have a "Tactic" that covers a year's worth of dates and the tactic item has 10,000 combinations that all need to expand out to the day level, I seem to get a low row estimate on the output of the nested loop. Investigating my plan, I believe it seems to be estimating the correct number of iterations at the nested loop, but at the output and subsequent inserts seem to be low. Is there something that I'm missing to gain better cardinality estimates?

Here's an extract of the behaviour:

DROP TABLE IF EXISTS dbo.Calendar
DROP TABLE IF EXISTS dbo.Tactic
DROP TABLE IF EXISTS dbo.TacticItems

CREATE TABLE dbo.Calendar (
    ddate   DATE NOT NULL
  , Day_Idx INT
  , PRIMARY KEY (Day_Idx)
)

CREATE TABLE dbo.Tactic (
  TacticId      INT NOT NULL PRIMARY KEY
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
)

CREATE TABLE dbo.TacticItems (
  TacticId      INT NOT NULL
, ItemKey_A     INT NOT NULL
, ItemKey_B     INT NOT NULL
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
, PRIMARY KEY (TacticId, ItemKey_A, ItemKey_B)

)

/*
  Prime Dates For the Year
*/

INSERT INTO dbo.Calendar (ddate, Day_Idx)
SELECT  ddate = DATEADD(DAY, A.Increment, A.Base_Date)
      , Day_Idx = CONVERT(INT,CONVERT(CHAR(8),DATEADD(DAY, A.Increment, A.Base_Date),(112)))
FROM    (
          SELECT TOP 366
                  Base_Date = CONVERT(DATE, '2019-12-31')
                , Increment = ROW_NUMBER() OVER (ORDER BY Object_Id)
          FROM    sys.objects
        ) A

INSERT INTO dbo.Tactic
        (TacticId , From_Day_Idx, To_Day_idx)
VALUES  (29       , 20200101    , 20200630)

INSERT INTO dbo.TacticItems
            (
                TacticId
              , ItemKey_A
              , ItemKey_B
              , From_Day_Idx
              , To_Day_idx
            )
SELECT      1
          , A.ItemKey_A
          , B.ItemKey_B
          , 20200101
          , 20201231
FROM    (SELECT TOP 63 ItemKey_A = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) A
CROSS JOIN (SELECT TOP 162 ItemKey_B = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) B

DROP TABLE IF EXISTS #Results
SELECT      *
INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

Here is the plan that i'm getting:

And the nested loop input that seems to be correct:


texts: Get all records from inner joined tables if second table does not have data

texts:
I follow below syntax by which I achieve the result. but it takes time

IF NOT EXISTS(select * from #tblxyz)
BEGIN
INSERT INTO #tblxyz select NULL
END

Select a.*
        FROM
            tblabc a
            INNER JOIN #tblxyz b ON a.ID = ISNULL(b.ID,a.id)

If second joined table has some rows, I only want to return those joined rows only. Only if the second table has no rows, I want to return al lrows from the first table.


texts:
This SQL returns a table with everything unique, which is what I want.  However, if there are two (or more) event numbers, I need it to return the line with the highest rev_num for that eventnum.

I have read about the max() function, but I'm not having any success with it.

select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'

Here's what the output looks like:

FINAL ANSWER:

First off, both answers helped immensely. The final query looks like this:

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.completion_remarks
    , d.weather_code, rev_num
from mv_outage_duration a
    inner join mv_outhist_transformers b
        on a.restore_operation_id = b.phase_restored_operation_id
    inner join mv_aeven d
        on d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID
    inner join mv_we_outage_premise e
        on e.xfmr = b.xfmr_name
where e.we_premise = '995184'
  and rev_num = (select max(rev_num)
                   from mv_aeven d
                  where d.restore_operation_id = b.PHASE_RESTORED_OPERATION_ID)

Max did a great job of explaining why I should restructure my SQL in a way that uses the inner joins. Marty was correct in that I needed a subquery on the where clause, just needed to point to the right columns for the data I needed.  This could have been my fault as I'm not sure I explained the data structure well enough.

Thank you both for your time and efforts!


texts:
I am a little confused right now. Can someone answer me with a simple answer to this question.

I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
The user's default schema is [user_test] and almost all objects from the [database_test] are created under schema [user_test]. In the database exist 3 more objects with schema [dbo]

The user has rights to both schemas: [dbo] and [user_test] without explicitly specifying them in select statements in front of objects reference.

What is the first schema checked by SQL when an object is called by user [user_test] ?

I mean, If [user_test] issues a select for an object from dbo schema (without schema in front), will SQL first check in it's default schema specified and then in dbo?

thanks,


texts: Get multiple column and row values in a sigle row

texts:
I have a table structure as:

KEY     language_id        language_value
------------------------------------------
SELECT      1               Select Lang 1
SELECT      2               Select lang 2
GET         2               get lang 2

I want a result like

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   Select Lang 1         Select lang 2
GET                            get lang 2

If Search for a key "GET"

KEY     language_val_1        language_val_2
----------------------------------------------
SELECT   get lang 1

Thanks


texts: SQL Mirroring - Application not pointing to new Primary

texts:
Im stuck with a connection string issue

this is my connection string.

<add name="APP01" connectionString="Server=10.169.0.2; Failover Partner=10.166.0.2; Initial Catalog=DB2017; Integrated Security=True" providerName="System.Data.SqlClient" />

If I do sql server mirroring failover, the application is not connecting to new primary server. Still all the connections goes to old primary.


texts: Simple MySQL query randomly takes forever

texts:
I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will take "forever".

It feels like a similar problem to this: https://stackoverflow.com/questions/976739/mysql-query-randomly-takes-forever

At seemingly random times while the MySQL query is running the load on my server spikes from an average of 0.5 up to 8 and my entire server slowly grinds to a near halt. The query does eventually complete but sometimes it runs for over 10 minutes -- on a "normal" occasion the query completes in 0.2 seconds.

This is the query:

SELECT * FROM email_messages
WHERE time_created <= "2013-04-22 10:40:00" AND is_sent = 0 AND is_cancelled = 0
LIMIT 6

time_created is (should be) indexed - its purpose is to create a 10 minute delay before sending so emails can be cancelled if necessary.

I've isolated it to this query because my server trucks along nicely without the load average spikes when this cronjob is turned off.

My server is a VPS with 2GB of RAM. The database isn't huge (750mb in total) but the email_messages table being selected from is by far the largest at around 400mb.

Through research, I thought it was a MySQL query cache problem because query cache was turned on. But turning off query cache has not resolved the issue.

I turned off query_cache by setting query_cache_size = 0.

Any ideas at all as to what could be going on here?

[CONCLUSION]

I did an experiment by including a condition in the query using my primary key so that only the last 1000 table entries where being search through.

... AND id > 131000 ...

Running the cronjob with this condition resolved the issue, and further investigation in the mysql console with EXPLAIN revealed that the index on time_created  was not created as I had expected.

I now need to read up on how to use HeidiSQL to properly create indexes because HeidiSQL is telling me there is an index key on time_created but EXPLAIN says otherwise. This may be a trap for young players!


texts: Resources for SQL Server developer migrating to Oracle

texts:
What resources can you recommend for a SQL Server developer wanting to learn Oracle basics?

I am looking for a comprehensive whitepaper or blog post describing differences between these systems and answering questions like How to create an IDENTITY column? or What data type is equivalent of float?.


texts: Regular data transfer from SQL Server to MySQL

texts:
Our company uses an application with a SQL Server database where we store all of our Project Item data.
Our company's Portal Website uses a MySQL database that holds other Project information (location, start date, billing agent etc...)
These two databases are currently only "connected" via a read only ASP.NET API on SQL Server.

We want to expand on what the SQL Server database currently offers us, however we don't want to make any changes to the SQL Server database considering it is also updated by the company that maintains the application that uses it.

For example, the SQL Server database doesn't currently have any change tracking and doesn't send notifications to relevant parties when key data is changed.

Our current thinking is, we create a lightweight duplicate of the SQL Server database on our Portal's MySQL database (only taking over the information we need) and periodically take over changes via our ASP.NET API, which should allow us to extend the database to safely implement change tracking, increase automation with change notifications and some other nice stuff.

As many of you have probably picked up, I'm pretty new to all this myself so any and all guidance is appreciated. My questions are:

Does this sound like an efficient / effective way to get what we want?
What are the advantages / disadvantages of our current trajectory?
What better / alternative ways are there of reaching our goals?
Any other questions I should be asking?

I believe the SQL Server version is 2012.


texts:
I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data.

I tried re indexing, it's good the database has de-fragmented. But the DB size is not reduced.

I am not prepared to use shrink database. I have already tried compression and DBCC UpdateUsage but no space gain. Please advise me any other ways to reduce the database size.

Our client will not allow us to take the DB Offline. It might take 5 hr to shrink the database and then re indexing will take 3 hrs, so in total we would need 8 hr down time. I am looking for a solution without downtime.


texts: Query is slow with JDBC parameters, fast with concatenated SQL

texts:
I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.

I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.

I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver (mssql-jdbc-7.0.0.jre8.jar).

My slow-running code looks like this:

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "
            [big join here across multiple tables]
            "WHERE FOO.ID_= :fooId " +
            " AND BAZ.NAME_ LIKE :likeParameter ";
    Query query = getSession().createSQLQuery(sql)
            .setParameter("fooId", fooId)
            .setParameter("likeParameter", likeParameter);
    return query.list();

It runs quickly if I change it to this:

String sql = " SELECT DISTINCT FOO.BAR_ID_ "
            [big join here across multiple tables]
            " WHERE FOO.ID_= " + fooId  +
            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";
    Query query = getSession().createSQLQuery(sql);
    return query.list();

The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?

UPDATE: here are the query plans:

Fast query with in-line data: https://www.brentozar.com/pastetheplan/?id=SkkH0NiU4

Slow query with parameters: https://www.brentozar.com/pastetheplan/?id=SkBxhQoLE


texts: Find rows in which the column doesn't contain a "space"

texts:
I'm using Postgres 9.5.  I want to search for rows in which my name column does not contain a space.  I'm a little murky on how to define a space to you, though. I thought it would just be the space bar on my keyboard, so I ran:

.... where name not like '% %';

but then I got some results like this:

 | JASON FALKNER

That sure looks like a space to me, but there are probably some other things going on. Is there a better way I can scan for rows in which my name column doesn't contain a space?

Using a regexp, not (name ~ '\s') still returned columns that looked like they had a space.

Using:

select cast(name as bytea) ... where name not like like '% %';

returned:

\x4a41534f4ec2a0424c414b45

However, I'm still a little unclear how I use that data to figure out how to screen spaces from my results.

I tried where not (name ~ '[[:space:]]')' and it's returning "JASON BLAKE" with the same byte sequence above, \x4a41534f4ec2a0424c414b45.


texts: Availability Group log_reuse_wait = Replication

texts:
We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center.

We did a large index rebuild on the primary which obviously needs to replicate to the secondaries. I believe this replication has now finished. When I run:

select redo_queue_size, log_send_queue_size
from sys.dm_hadr_database_replica_states
where database_id = 28

I get:

redo_queue_size |log_send_queue_size
0           | 0
0           | 0
NULL        | NULL

Which suggests to me that the replication has finished. However, when I run

select log_reuse_wait, log_reuse_wait_desc from sys.databases where database_id = 28

I get:

log_reuse_wait  | log_reuse_wait_desc
6               | REPLICATION

I found this query:

select *
from sys.dm_hadr_database_replica_cluster_states
where database_name = DB_NAME()

which shows the state of the replication which again suggests that everything is replicated, but it can't be truncated for some reason:

is_failover_ready   | is_pending_secondary_suspend  | recovery_lsn              | truncation_lsn
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001
1                   | 0                             | 4294967295429496729500001 | 793156000000001600001

The Transaction Log backup jobs are running and backing up as expected, but the % of the log file used continues to grow due to normal traffic. How do I get SQL to reuse the transaction log file?


texts: MySQL hangs when I query specific tables after truncate

texts:
I've observed this a couple of times now.
I truncated a table, and now anytime I do any SQL operation on that table, MYSQL client will stop responding. I can open a new client and query any other table. If I truncate another table while this is going on, it will also become unresponsive. It seems to take a very long time to "fix" itself.

Eventually, it does, and I can truncate the tables and run any other query operation against them.

Does anyone know why this would happen?


texts: Performance/Health checking queries for a user with Read permissions

texts:
I am a software developer that only had read access to our prod database (of course).

Occasionally we have what looks like performance issues on our server when my DBAs are not available.

Is there any kind of query I can run (with Data Reader permissions only) that could tell me anything about the current health of my SQL Server?

If there was some kind of edge permission that did not allow for changing data or schema that would help, I could probably get permissions to that.  (For example, I was given permissions to monitor replication in our production environment.)


texts: SQL Server 2008R2 - Why is my index not used

texts:
I have a table defined in the following way:

CREATE TABLE [dbo].[MyTable]
(
   [MyTable_ID] [int] IDENTITY(1,1) NOT NULL,
   [COLUMN_WITH_DATA] [varchar](128) NOT NULL,
   [COLUMN_A] [varchar](128) NULL,
   [COLUMN_B] [varchar](128) NULL,
   [COLUMN_C] [bit] NOT NULL
)

And an index created like that:

CREATE INDEX [MyTable_Index_ABC] ON [dbo].[MyTable]
(
    [COLUMN_A],
    [COLUMN_B],
    [COLUMN_C]
)
ON [PRIMARY]

And I run the following query:

SELECT TOP 1 [MyTable].*
    FROM [MyTable](UPDLOCK)
    WHERE
        [MyTable].[COLUMN_A] IS NULL
    AND [MyTable].[COLUMN_B] IS NULL
    AND [MyTable].[COLUMN_C] = 0

The goal behind this is to get COLUMN_WITH_DATA vaule of first not yet used record, and then update its' COLUMN_A and COLUMN_B with not null values, to mark it as consumed.

I have a couple of millions of records in MyTable, and about half of them has both:
COULMN_A and COLIMN_B that are not NULL (indicating the data was already consumed).

In this situation the query runs really slow, and the execution plan shows that the index:

MyTable_Index_ABC

is not used unless I use a query hint, in which case the query runs much faster.
On the other hand if I update all not yet consumed rows, that is having:

COLUMN_A IS NULL AND COLUMN_B IS NULL

so that COLUMN_A and COLUMN_B contain empty string: '' instead of NULL, the index is used and the query runs much faster again.

Two questions would be:

why null values make my index discarded
is it possible to instruct the database to always use the index without having to use query hints on possibly multiple different queries
??

Thanks,


texts:
I have a problem with one of our shards. For some reason I'm getting the next usage:

  15:38:42 up 4 days, 10:28,  1 user,  load average: 3.67, 3.50, 3.56

In the mean time all other shards are ok. Their average load approx:

  16:49:58 up 11 days, 56 min,  1 user,  load average: 0.03, 0.03, 0.05

I have 3config, 2 query and 7 shards (all of them are primary replicaset. don't ask why).

I have 2 databases: 20Gb and 30gb. All servers are the same in their configuration. Also, in each database only 1 collection. In MMS control panel I see:

Maybe a problem here?
How I can investigate this issue and fix it?

As I'm new with MongoDB I have used the next article to configure my shards:

https://stackoverflow.com/questions/19672302/how-to-programatically-pre-split-a-guid-based-shard-key-with-mongodb

Please check 1st accepted answer. Or how can I sheck my shard key myself?


texts:
One of our developers created a view whereby one of the select columns was wrapped in an RTRIM function. That same column on the underlying table has a non-clustered index on it.

Now queries using this view and a where statement on this column are failing to use the index due to the RTRIM. I need the view's output to be consistent as unfortunately this developer is no longer with us and I can't read his code.

What are some options for optimisation? I've got one, but I'm hoping there is better.


texts: Setting NLS params in logon trigger don't work in oracle 11g

texts:
We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session parameters are no longer persisting. To explain, I've pasted a session that shows the NLS_DATE_FORMAT not being used properly.

This is the trigger we're using:

create or replace
TRIGGER schmea.nls_tr
AFTER logon ON DATABASE
BEGIN
   execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'' NLS_TERRITORY = ''AMERICA''';
END;

The formats above are not default, so they appear to change on login.

SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS

NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF

SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';
select count(*) from TABLE where start_date > '2012-06-10 00:00:00'
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P024, instance
[domain.com]:[instance] (1)
ORA-01861: literal does not match format string

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';

  COUNT(*)
----------
      4901

SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS

NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF

Please help. I've been tearing my hair out for 13 hours, 7 minutes and 4 seconds. Any ideas?

Thank you.


texts: How to preserve sqlplus session in shell script

texts:
I am trying to insert queries from a sql file in shell script using sqlplus.
Now, some queries can fail because of incorrect schema.
So I want to commit all transactions only if all queries succeed.
The main problem is that, I would like to execute all queries irrespective of any error and rollback all queries if there was at least one error.

The idea behind executing all queries is to find the queries which would error out and create schema fix for the same.

I am using this command:

cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba

But this will auto-commit once the sqlplus exits.
I cannot use set exitcommit off since I am using 11.1.

Is there any way to do this in shell script?


texts: Mysql - Grouping and getting the Max(col1) row variables

texts:
I have this table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `col1` VARCHAR(30) NULL DEFAULT NULL,
    `col2` VARCHAR(30) NULL DEFAULT NULL,
    `col3` VARCHAR(30) NULL DEFAULT NULL,
    `riskLevel` VARCHAR(30) NULL DEFAULT NULL
    PRIMARY KEY (`id`)
)COLLATE='utf8_general_ci' ENGINE=InnoDB;

What i want to do is group this table by col1, and get the row with the highest riskLevel for each col1 group values.

When using this query:

SELECT id,col1,col2,col3, MAX(riskLevel)
FROM table1
GROUP BY col1

You get the max riskLevel, but the other values are random, not fittings to the correct row.

I know one solution:

SELECT id,col1,col2,col3, riskLvel
FROM table1 JOIN
(SELECT col1, MAX(riskLevel) as riskLevel
FROM table1
GROUP BY col1) x ON x.col1 = table1.col1 AND x.riskLevel = table1.riskLevel

This solution doesn't seem optimal to me.

Is there another way of getting the described behavior?


texts: How to grant access to IP range in Postgres?

texts:
I would like to configure my Postgres DB to be accessed by any IP 10.8.101.*. I know I have to create a rule in pg_hba.conf but I'm not sure what it should look like. My LAN IP is currently 10.8.101.128 so I thought this would work:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             10.8.101.0/128         md5

But I'm getting an error could not load pg_hba.conf. Does anyone know how to do this properly?


texts: Is partial synchronisation possible between two databases on different servers?

texts:
Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups.
My question is whether I could add additional objects to the secondary instance - in particular a materialised (indexed) view - without needing that view to synchronise back to the primary.
Is this possible?
("Why?", I hear you ask? Because I don't want the performance hit of maintaining the view to impact the primary instance. It would be find for the secondary to use async-commit mode, if needed.)


texts: Is it possible to exclude a database from WAL and basebackup?

texts:
I'm planning to add a GIS database to an existing PostgreSQL 12 cluster with multiple databases, streaming (hot standby) replication and WAL archiving.
Once created the new database will never be changed, and is easily recreated from the source data. However it would be very large (100s of GB).
The server is currently backed-up using wal-e (periodic base backups plus wal archiving) and has to use physical replication due to use of large objects.
Is there any way to exclude the new database from this process, so that the backups are not significantly slowed and inflated by the new data? It would still need to work for a replication though, making it an even more complex problem.
Is this something that can be done perhaps with tablespaces, which would then be manually copied for initial replications instead of using pg_basebackup?


texts: Linked server to remote sql server failed

texts:
I have tried to create linked server to the remote sql server but when it failed with following error message.

  The test connection to the linked server failed.

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. (Microsoft SQL Server, Error: 18452)

I have followed the instructions from How do I specify a linked server to a remote database over tcp/ip?.

My config settings are

EXEC master.dbo.sp_addlinkedserver
@server     = N'TESTSERVER',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI',
@datasrc    = N'172.x.x.x';
-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'TESTSERVER',
@useself     = N'False',
@locallogin  = N'Local_user_name',
@rmtuser     = N'Remote_user_name',
@rmtpassword = N'Remote_password';

I am able to connect the remote server instance. But it failed when I used that as a linked server in my Local instance.

Please help me resolve the error.

Thanks.

  Update

I am able to connect by changing the setting from Be made using the login’s current security context to  Be made using this security context

Please let me know why the latter works.


texts: THROW not including name of calling procedure unless custom error-message is specified

texts:
I am experiencing some behavior with THROW that I can't understand. Consider the following stored procedure:

CREATE PROCEDURE usp_division_err AS
SET NOCOUNT ON;
BEGIN TRY
    EXEC('select 1/0')
END TRY
BEGIN CATCH
    THROW;
END CATCH

When the procedure is executed, the following error is raised:

  Msg 8134, Level 16, State 1, Line 1
  Divide by zero error encountered.

Note, no information about in which procedure the error was raised is included. That's because the erroneous, dynamic SQL is executed in another scope, and that is fine. However, change the CATCH-block to look like this

BEGIN CATCH
    THROW 50000, 'An error occurred.', 1;
END CATCH

and the execution of the procedure will raise this error instead:

  Msg 50000, Level 16, State 1, Procedure usp_division_err, Line 7 [Batch Start Line 0]
  An error occurred.

The error is still encountered while executing the dynamic SQL, but when I manually specify the error number and error message (the first and second parameter of THROW), the procedure-name of the executing procedure somehow appears.

Why does the procedure-name appear in the second error-message but not the first?


texts: When SQL Server uses Multi Page Allocations

texts:
What are the consumers of MPA (multi page allocations) in SQL Server?

I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans can use MPA as they can exceed 8 KB.

There is a blog here that suggests use of MPA but it refers to stored procedures (one with 500 parameters). In the attached screenshot I see an execution plan using around 11 MB;  does this use MPA? Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

My confusion is what actually uses MPA (multi page allocator).  The example in the link I posted shows a complex execution plan which will require contiguous allocation over 8 KB.

An answer below suggests that there are many things like linked server or extended stored procedures that can use MPA. Which for some reason I am not able to agree. For example Extended Stored procedures can be C++ code using HeapAlloc which SQL server has little control over to manage its memory (it has to be at Windows OS level).

Extended stored procedures even in SQL 2012 still consumes memory outside buffer pool so it has to nothing to do with multi page allocation. The same applies to Linked servers especially if you use third party providers (e.g. ORACLE).


texts: Select count(*), where 2 rows with different values exists

texts:
I have following table structure and data:

+-----------+-------+
|type       | value |
+-----------+-------+
|first_name | john  |
+-----------+-------+
|last_name  | doe   |
+-----------+-------+
|last_name  | smith |
+-----------+-------+

I need to get count (in my case it's 1), if table contain data John Doe. Query like:

SELECT COUNT(*) FROM tablename WHERE (type='first_name' AND value='john') AND (type='last_name' AND value='doe')

returns zero count. Don't understand, how to create this combined query.


texts: How to identify overflow rows in DB2 LUW

texts:
Situation: DB2 LUW database with several tables reporting overflow.

We are attempting to determine the cause of the overflow. The tables in question do NOT have varchar columns. Million+ rows are inserted per month. Updates to rows are mostly limited to changing a single column from a value of 0 to 1.  Tables are compressed. Clustered index.

Is anyone aware of a DB2 for LUW utility that can be used to identify the specific rows which have overflowed? We are hoping that by identifying the rows we can uncover the cause of the overflow.

Currently there are two causes being entertained - the combination of compression/update or the presence of the clustered index.


texts:
Given the following tables:

CREATE TABLE `longdescs` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` mediumint(9) NOT NULL,
  `who` mediumint(9) NOT NULL,
  `bug_when` datetime NOT NULL,
  `work_time` decimal(7,2) NOT NULL DEFAULT '0.00',
  `thetext` mediumtext NOT NULL,
  `isprivate` tinyint(4) NOT NULL DEFAULT '0',
  `already_wrapped` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `extra_data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `longdescs_bug_id_idx` (`bug_id`),
  KEY `longdescs_who_idx` (`who`,`bug_id`),
  KEY `longdescs_bug_when_idx` (`bug_when`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `fk_longdescs_bug_id_bugs_bug_id` FOREIGN KEY (`bug_id`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_longdescs_who_profiles_userid` FOREIGN KEY (`who`) REFERENCES `profiles` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=82727 DEFAULT CHARSET=utf8

CREATE TABLE `trace` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `comment_id` mediumint(9) NOT NULL,
  `type` varchar(255) NOT NULL,
  `short_hash` char(22) DEFAULT NULL,
  `stack_hash` char(22) DEFAULT NULL,
  `trace_text` mediumtext NOT NULL,
  `quality` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trace_comment_id_idx` (`comment_id`),
  KEY `trace_short_hash_idx` (`short_hash`),
  KEY `trace_stack_hash_idx` (`stack_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

ALTER TABLE trace ADD CONSTRAINT `fk_trace_comment_id_longdescs_comment_id` FOREIGN KEY (`comment_id`) REFERENCES `longdescs`(`comment_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)

MariaDB [bugs4]> show warnings;

| Level  | Code | Message |
| Warning |  150 | Create table 'bugs4/#sql-4bb_6f1d' with foreign key     constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'bugs4.#sql-4bb_6f1d' (errno: 150)                                                                                |

Any idea why I get that error? FYI I looked it up and it appears that it was a bug but not anymore.

http://bugs.mysql.com/bug.php?id=16290


texts: Posgresql - deadlock on non related queries

texts:
I am using Postgresql 11.
I have found an error similar to following in my logs:

org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 332091 waits for ShareLock on transaction 99896257; blocked by process 332093.
Process 332093 waits for ShareLock on transaction 99923910; blocked by process 332091.
  Hint: See server log for query details.
  Where: while locking tuple (884208,33) in relation "tab_1"
SQL statement "SELECT 1 FROM ONLY "sch"."tab_1" x WHERE "tab_1_key" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "INSERT INTO sch.tab_2 (col1, col2, ..., coln)
   SELECT
      col1,
      col2,
      ...
      coln
   FROM
      sch.tab_3
   LEFT JOIN sch.tab_4
      ON tab_4.id = tab_3.id
   LEFT JOIN sch.tab_5
      ON tab_5.id = tab_3.id

So basically the first query looks like some DB internal query, nothing from my code.
And it seems like it is making deadlock with my query. But they are not related by any table.
Any ideas about how to track the reason for the deadlock in such a case?


texts: query two point values from a table then insert a new record after creating a line from the two points

texts:
I am running postgres: 9.5.19

I have already point values as separate records in a table. I want to create a new record which represents a line geom derived from the two points above. I have lots of point records but I want to be able to select by id each point to link together.

I am trying this syntax:

insert into locations (geom,type,label,user)
select st_makeline(a.geom,b.geom) as line from (select * from locations where id = 144) as a, (select * from locations where id = 145) as b,
'Link','test','ADMIN';

and get this error:

  ERROR:  syntax error at or near "'Link'"
  LINE 1: ...a, (select * from locations where id = 145) as b, 'Link','te...

Can someone point me to a working syntax?


texts: Migrate a remote oracle database (11g) into a docker oracle XE database (11g)

texts:
I am brand new to Oracle and I am trying to migrate an existing VM that has deployment scripts run on it every release to use a docker container instead. Then I can run the release scripts and tag a new container each release (to be used for our integration testing).

Unfortunately it seems like no matter what I try (exp, expdp, generating ddl from datagrip, generating ddl from data studio, expdp in toad) it is impossible to fully copy a remote database into the XE database without a ton of errors in the packages or key constraint errors.

What am I missing?

Here's what I've done so far:

expdp user/pass@XE directory=dp_dir FULL=YES dumpfile=8_17.dmp logfile=8_17.log network_link=XE_BUILD content=ALL TRANSPORT_FULL_CHECK=YES

This creates a dump file that seems like it should be valid.

Then I do this;
impdp importer/importer@XE directory=dp_dir full=Y dumpfile=8_17.dmp logfile=imp_full.log

This also completes with a relatively small number of errors. However I constantly get key constraint errors and broken packages that I don't experience when using the remote database.

I've tried rebuilding all package and things like that, still no luck.


texts: Can this be accomplished with Oracles Fine Grained Auditing?

texts:
We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same oracle account to log in and we need to audit WHO, using the users username on the application, did the action on this table.  What I was thinking is that we can just pass the users id in as part of the select statement ( select 'bob', col1, col2...) and that could possibly be accessed with Oracles Fine Grained Auditing.

Does anyone have any experience in doing something like this or if it is even possible.  If not, any ideas on how we can accomplish this?

Thanks!

Forgot to mention that this is in Oracle 10G


texts: Can I install SQL Server 2012 Express edition on a same machine with 2012 SQL Server Standard version

texts:
As I would like to say that we are going to downgrade the SQL Server Standard 2012 SP1 edition with SQL Server Express 2012 SP4 in one Production Server.

For the time being I want to keep the SQL Server Standard 2012 SP1 in that server and I want to install the SQL Server Express 2012 SP4. Until unless the SQL Server Express 2012 SP4 will be not come to production. Till time I want to keep the SQL Server 2012 Standard SP1.

  My Question is that at the same time can I install the SQL Server
  Express 2012 SP4 along with the SQL Server Standard 2012 SP1 in the
  same Server.

Note : Suppose that as the Running SQL Server 2012 Standard SP1 instance name is ABC. Can I install the same instance name (ABC) of SQL Server 2012 Express SP4 or it will be different.


texts: Where can I find documentation on SQL Server internals?

texts:
I know, well I've been told, Microsoft has a per-dev doc system under MSDN. Specifically, I'm looking for docs on the internal format of the WAL log. Is this documented anywhere? Certainly third parties must have access to this?

And, moreover, is there anyway to know what exactly is documented with MSDN without first paying for it?

What other source of documentation is there on the SQL Server internals?


texts:
I'm looking for a tool that could browse and visualize data from db like Kibana does.

I'd prefer a native Posgres tool to any that transfers data from a db somewhere else. I know that rivers for Elasticsearch did the latter and it's now deprecated.

Thank you in advance.


texts: How large will the Transaction Log backup be?

texts:
I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got way out of hand in the past year.

Now I want to fix the situation by first doing a full backup from SQL Server and then doing a transaction log backup. There is not much space on the server, so I'd like to know in advance how big the transaction log backup will be, approximately. I understand that the transaction log will only be truncated after backup, so I'm afraid the backup will be 90 GB as well and clogg the server.

So far I can't find any information on backup sizes, can someone explain what will happen size-wise? Thanks in advance!

Edit:
SQL Server version is Standard Edition, and the database recovery model is full.


texts: Easily grant access to list of users after database is created

texts:
I have a couple of databases that are occasionally deleted and recreated. This happens when the schema changes.

I have a group of developers that need access to those databases. In order to grant these developers access to those databases, I can either:

Use a shared login, and grant that login access to each database using SSMS. This is bad security, but fairly quick. I don't want to do this except that it saves me a lot of time.
Use individual logins, and grant each individual login access to each database using SSMS. This is good security, but costs me time every time I have to recreate the databases. I have to select each user's login, and edit the user's User Mapping.

How can I easily grant a group of users access to a database that I've dropped and recreated?

Versions

Microsoft SQL Server Management Studio 12.0.2569.0
SqlServer 12.0.4100.1

Scope

There are about six developers.
There are two SqlServer servers.
Each server has two databases to which developers are granted access.
Databases are deleted/recreated once or twice a month.


texts: Why are partial PostgreSQL HASH indices not smaller than full indices?

texts:
I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial.

Now I'm wondering why a partial HASH index isn't smaller than a full hash index:

CREATE INDEX full_hash    ON mytable USING HASH(my_id); # 256 MB
CREATE INDEX partial_hash ON mytable USING HASH(my_id) WHERE my_ID IS NOT NULL; # 256 MB

CREATE INDEX full_btree    ON mytable (my_id); # 537 MB
CREATE INDEX partial_btree ON mytable (my_id) WHERE my_ID IS NOT NULL; # 32 MB

Both hash indices take exactly the same amount of space (as shown in pgHero). However, when using standard BTREE indices, the partial index takes only 5% of the space of the full index.

Are partial HASH indices not supported in PostgreSQL 10?


texts: Amazon RDS non superuser create function in C

texts:
I am asked to restore a Rails application to a postgresql RDS instance. There are many functions in C but looks like only superuser can create functions in C and I could not get superuser access on RDS instance, so I am kind of stuck.

any suggestions ?


texts: Approximation of table size for MySQL cluster

texts:
I want to make some approximation of table size for MySQL cluster.
Please read my reasoning and tell me, if I am right or am I making mistake somewhere.

I have simple table with PK (int) and additional column (int).
In MySQL int = 4 bytes, so one row is about 12 bytes (4*2 for columns and 4 for PK index).
If I have for example 1'000'000 rows this will be 12'000'000 bytes ~= 11.5MB.
Am I right in this calculations? Can I store such table with so little space?


texts: Why does Postgres run SORT on many rows, before GROUP BY?

texts:
I'm using Postgres 9.4, with a recently analyzed database. These are my tables:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
-----------------+----------------------+-----------
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
Indexes:
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_chem_id_vc" btree (chemical_id varchar_pattern_ops)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

               Table "public.frontend_pct"
      Column       |          Type           | Modifiers
-------------------+-------------------------+-----------
 code              | character varying(3)    | not null
 name              | character varying(200)  |
 org_type          | character varying(9)    | not null
Indexes:
    "frontend_pct_pkey" PRIMARY KEY, btree (code)
    "frontend_pct_code_1df55e2c36c298b2_like" btree (code varchar_pattern_ops)

This is my query:

EXPAIN (ANALYSE, BUFFERS)
SELECT pr.pct_id AS row_id, pc.name AS row_name,
       pr.processing_date AS date, SUM(pr.cost) AS actual_cost,
       SUM(pr.items) AS items
FROM vw_chemical_summary_by_ccg pr
JOIN frontend_pct pc
  ON pr.pct_id=pc.code AND pc.org_type='CCG'
GROUP BY row_id, row_name, date
ORDER BY date, row_id;

The results of the analyse shows a very slow sort on 5 million rows, BEFORE running the GroupAggregate.

The result of the GroupAggregate is just 5,000 rows. So wouldn't it make more sense to aggregate first, then sort?

Explain here: http://explain.depesz.com/s/IS1

Any other suggestions for speeding up the query would also be very welcome.


texts: How mysql database performance can impact by different types of disk[hard disks]

texts: I have a ques regarding mysql server performance which how related to server's different types of disk. I mean how disk impacting Mysql Server performance and which type of disk is more convenient for good performance. Please suggest the best way to decide which type is disk is best like ssd,nvme etc


texts: Getting distinct list of records based on the MAX of a column

texts:
I have a status table (tblTestActionStatus) which has three columns

ID_TestAction:  references some test action
ID_Status:   a look up table reference for the variety of possible statuses for the action
StatusDateTime:  a datetime field which logs the exact time a status update was made for the test action.

So while a test action is happening, periodic status updates are made.

I am interested to know the current status which would be the last status update of a test action as of the moment I run the query.
For example, test action # 100 has gotten 4 status changes so far.  The first was 1, then 2, then 3, and most recently 4.  And test action # 101 has gotten 3 status updates so far.

So I would like to write a query that returns for me all the columns in the table but for only the most recent StatusDateTimes.

I attached a pic which shows the table contents and the rows I would like to see coming back from the query highlighted.


texts:
Is it possible to Apply DB2 9.5 logs files from Production site to DR site? Or do I have to do Backup/Restore and rollforward ...?

I copied log files from production site to DR and issued the following command:

rollforward db DBNAME to end of logs and stop overflow log path (/home/db2inst1/logs) noretrieve

Got the error:

  DB is not in Rollforward Pending


texts:
My MS Access Application uses SQL Database as backend.
The permission to SQL database is granted with Windows Authentication mode to a specific user group.
Most of users in the group can get access to SQL database but there are some users reported to fail for login in the database.
The error message:
Connection failed
SQLstate: '28000'
SQL Server Error: 18456
Login failed for user Domain\UserID

I spent sometime searching for the solution but I could not find one with the same problem.
Could anyone give me an advice on this?
Many thanks.


texts: Can we use proxysql 2.x for load balancing multiple Galera clusters?

texts:
We have recently introduced proxysql 2.x for loadbalancing a galera cluster in production environment. It is working great so far.
I have been tasked to separate staging cluster which is currently in same production instance.
Can I use the same proxysql instance to load balance staging cluster to avoid redundancy ?
I am bit hesitant at the same time considering proxysql 2.x does not really know anything about the cluster.
Has anyone of you used the same proxysql 2.x instance for multiple clusters? Are there any risks?


texts:
ASE documents the process to export-import keys when we fully encrypt the database. Once imported, a create query with the same credential on a target allows load from the source database.
But in the case a column of a table of a db is encrypted using keys, the load of the database doesn't directly work. Importing of the keys also isn't straightforward as before since the key is now referred as dbname.dbo.keyname which in the previous case is master.dbo.keyname
Is there any smoother way to do this other than decrypting the column of creating a database first on target with the same name before importing the keys?


texts:
I'm trying to encrypt a column with AlwaysEncrypted and I get the following error.

  Could not deploy package.

  Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72012: The object [AlwaysEncryptedDemo_log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72015: The table [dbo].[tmp_ms_xx_t1] is being dropped, data loss could occur.

  Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.

  Error SQL72045: Script execution error.

The executed script:

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[tmp_ms_xx_t1])
    RAISERROR (N'Rows were detected. The schema update is terminating' +
          ' because data loss might occur.', 16, 127)
        WITH NOWAIT;

This happens both in SSMS and using the generated PowerShell. The table schema is simply one column, nvarchar(10). We're only testing this out.

Can someone help me get past this error please?


texts:
I try to connect to Azure SQL with MS JDBC driver:

import java.sql.*;

public class ExampleSQLJDBC {

    public static void main(String[] args) {

        // Setting.
        String connectionUrl = "jdbc:sqlserver://SERVER.database.windows.net:1433;database=DATABASE;encrypt=true;trustServerCertificate=true;";
        String user = "USER@SERVER";
        String pass = "PASSWORD";

        // Declare the JDBC object.
        Connection conn = null;

        try {
            // Establish the connection.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(connectionUrl, user, pass);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

But I am getting:

com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Broken pipe ClientConnectionId:742ba7b5-43bb-441b-8e8d-bbb9dc5fa8ce".
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2226)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1756)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at run.ExampleSQLJDBC.main(ExampleSQLJDBC.java:21)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Interestingly, I can connect to the database from the same computer with Navicat. Hence, the problem should not be in the setting of firewalls.

Metadata:

Server: Azure V12
Driver: 6.0 (sqljdbc42.jar)
JRE: 1.8.0_72-b15 (from Oracle)
_JAVA_OPTIONS: -Djsse.enableCBCProtection=false
security.provider.1: sun.security.provider.Sun
OS: OS X 10.11.5

How can I connect to Azure SQL from Java?


texts: What is the correct way to rename/swap a table in production

texts:
I have two tables, tblCode and tblCodeAlternative

tblCode has about 4100 rows and is used in production.

tblCodeAlternative has about 4200 rows and is in the production database but not used anywhere.

What I need to do is swap the contents of the above database so tblCode becomes tblCodeAlternative and tblCodeAlternative becomes tblCode.

So really what I want to do is swap the contents of the two tables. I have tried to rename tblCode to tblCodeTemp, then rename tblCodeAlternative to tblCode and lastly rename tblCodeTemp to tblCodeAlternative.

However, for some reason this makes some of the application fail. The database itself is not producing any errors, in fact the renaming is surprisingly quick.

I'm not entirely sure why parts of the application is failing. If I revert the renaming then it all works again.

To rule out any mistakes on the database side, is it OK to rename a table the way I have done, or should it be done in another more robust way? I realise that for a few seconds there is no table called tblCode which is used in the application.


texts: Is possible do an online snapshot backup , without archive logs at Oracle Database?

texts:
Using Oracle Database Standard Edition 11r2 .

I'm looking a way to backup the database with zero downtime , using a snapshot of datafiles (LVM snapshots) and without need backup de archive logs.

All options found so far , we will need save fews archives logs.

My reason for this requirement is today our databases are put offline to copy our datafiles. There are procedures and documentation about backup and the restore . I want to minimize any change here at backup and restore process , all looking for minimize point of failures , human error which could be invalidate all backup.
So , I need to exclude the archive logs from the equation , do not want they involved.

I think something like :

flush all data from database to disk (checkpoint)
lock any write at the database datafiles (how?)
create the snapshot
release the database
mount the snapshot
copy all data of the snapshot
destroy the snapshot.

And if need to restore , just restore de data files and open the database.


texts: Database mirroring stuck in suspended mode

texts:
We have an issue with database mirroring on one of the databases on our sql instance where mirroring is suspended for almost 15 hours now.

DB is 200 GB on SQL server 2008R2 Sp3+cu with latest patched done for meltdown.

so far we have done below

Rebuild the endpoints--> no luck
Resume the mirroring with partner resume --> no luck
DBCC CHECKDB shows 0 errors found on either side.

Nothing found from error logs on Principal. However on secondary when we resume error logs seems to have stack dump logged every time.

Any help on how to resume the suspended mirror apart from rebuilding mirroring from scratch?


texts: MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica

texts:
We have a Source Database of about 1TB, and a Replica on another server (similar architecture).
We have setup Master Slave replication and delay between Master and Slave is always growing since.
Master server is "hyghly" used by cron which insert/update a lot of data, but after some researches, I have to admit I'm a bit stuck.
Could you help me diagnose the cause of the problem? Many thanks for any hint
Master / Slave server specs: 24 cores / 256 GB DDR4 / NVMe
htop says CPU usage:  6% / RAM usage: ~50% / IO: up: ~0,23 Mb/s, down: ~3,5 Mb/s
MySQL variables : https://justpaste.it/mysql_variables
Mysql > show replica status \G result:
             Replica_IO_State: Waiting for master to send event  <- most of the times
                  Source_Host: XX.XX.XX.XX (Source private IP)
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.001720
          Read_Source_Log_Pos: 1024908194                        <- increasing normally
               Relay_Log_File: Linux2-relay-bin.000296
                Relay_Log_Pos: 915186810                         <- increasing normally
        Relay_Source_Log_File: binlog.001676
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 915186601                         <- incrising normally
              Relay_Log_Space: 48272449293                       <- incrising normally
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 56693                             <- increasing slowly
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: ece58e3c-5ac0-11eb-ab4b-00505601ddac
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: waiting for handler commit        <- most of the times
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:


texts: Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?

texts:
In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good idea, because I could block the table for a long period of time. So, I cancelled the process, maybe tried just VACUUM (not full) or let AUTOVACUUM do later whatever it can do.

The question is: if I stop a VACUUM or AUTOVACUUM "mid-way", is all the processing already done lost?

For instance, if VACUUM already found 1 M dead rows and I stop it, is all this information lost? Does VACUUM work in a fully transactional manner ("all or nothing", like a very good number of PostgreSQL processes)?

If VACUUM can be safely interrupted without all the work being lost, is there any way to make vacuum work incrementally? [Work for 100 ms, stop, wait 10 ms to allow for non-blocking the rest of the world... and so on]. I know you can do part of this by tuning autovacuum parameters, but I am thinking along the lines of being able to control this programmatically, to be able to do it at certain times / under certain conditions.

NOTE: Stop / cancel / kill the process means in this context:

If using pgAdmin, press the "Cancel Query" button.
If working programmatically, call pg_cancel_backend().

I assume that both are equivalent. I have not used any shell/system-level kill command.


texts:
I was reading this:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

Step 2 says "Install the certificate in your operating system", but I am able to connect with psql without doing anything, and when I do, it says SSL connection. Without the servers certificate, how can it do this? Where are public certificates to be kept for a client to check? I've seen things talking about ~/.postgresql, others saying with the rest in /etc/ssl, but I don't see Amazon's anywhere on my system.


texts: Vehicle spare parts inventory datadase model

texts:
I am modeling a database to be used for vehicle spare parts inventory management and I am stuck...

Some spare parts can be broken down from a kit into component parts.  I would like to create a model that supports this, but do not know how to build it.

Spare parts can be issued in sets or pieces.  The database should keep information about how many complete sets we have and how many have been broken into pieces.  For instance, one piston ring set will have 6 different individual piston rings.

Can you point me in the right direction?


texts: How to display data from table 1 separately, and combined with matching rows from table 2

texts:
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.

For my results, I want every row in t1 to appear without any t2 data; then, in the following output rows, I want to see the combined data from the t1 row and any matching t2 rows. Is that possible in MS SQL? How can I do it?

Table t1:   Table t2:
+----+----+  +----+----+----+
| i1 | x  |  | i2 | c1 | c2 |
+----+----+  +----+----+----+
| 1  | a  |  | 1  | 1  |    |
| 2  | b  |  | 2  | 2  | g  |
| 3  | c  |  | 3  | 3  | h  |
| 4  | d  |  | 4  | 3  | i  |
+----+----+  +----+----+----+

Result
+----+----+----+---+----+----+
| i  | i1 | i2 | x  |c1 | c2 |
+----+----+----+---+----+----+
| 1  |  1 |NULL| a |NULL|NULL|<-- `t1` First Row
| 2  |  1 | 1  | a | 1  |NULL|
| 3  |  2 |NULL| b |NULL|NULL|<-- `t1` Second Row
| 4  |  2 | 2  | b | 2  | g  |
| 5  |  3 |NULL| c |NULL|NULL|<-- `t1` Third Row
| 6  |  3 | 3  | c | 3  | h  |
| 7  |  3 | 4  | c | 3  | i  |
| 8  |  4 |NULL| d |NULL|NULL|<-- `t1` Fourth Row
+----+----+----+---+----+----+

NOTE: column i in the output is simply a row number.


texts: How to export all tables with user in Oracle 10g Enterprise edition?

texts: I want to take backup of all my tables and the user too in Oracle 10g Enterprise edition and then I want to import it to some other computer. The user is not system I have created another user and all my tables are in that user.


texts: expdp full export not exporting all schemas

texts:
On Oracle 12c, fairly new database with APEX installed. I do a

 expdp \"/ as sysdba\"  directory=dp_dir full=y dumpfile=full.dmp logfile=full.log

And I am getting just a few tables from SYS and SYSTEM exported:

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.882 KB      24 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.523 KB      14 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.945 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.914 KB       1 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.320 KB       1 rows
. . exported "SYSTEM"."REDO_DB"                          25.58 KB       1 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.671 KB       4 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.21 KB      22 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                       10.14 KB       3 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.976 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

Yet I know I have tables and other objects in other schemas:

AtlasProd:SYS@extern01> select owner,
  2  decode(segment_type, 'TABLE', 'TABLE', '-other-') as segment_type,
  3  count(*)
  4  from dba_segments
  5  group by owner, decode(segment_type, 'TABLE', 'TABLE', '-other-')
  6  order by 1, 2;

OWNER                          SEGMENT_TYPE         COUNT(*)
------------------------------ ------------------ ----------
APEX_050000                    -other-                   646
APEX_050000                    TABLE                     155
AUDSYS                         -other-                     6
DBSNMP                         -other-                     4
DBSNMP                         TABLE                       2
GSMADMIN_INTERNAL              -other-                    15
GSMADMIN_INTERNAL              TABLE                       6
OUTLN                          -other-                     6
OUTLN                          TABLE                       3
SYS                            -other-                  1987
SYS                            TABLE                    1059
SYSTEM                         -other-                   321
SYSTEM                         TABLE                     124
XDB                            -other-                   655
XDB                            TABLE                      49

15 rows selected.

I see a workaround at Export pump all schemas except Sytem related schemas but I know I've used full=y before successfully.


texts: Oracle Error 12560 generating big sqlnet.log file

texts:
I have a Windows 2008 server which have two versions of Oracle: 11.2.0 and 12.1.0

A huge log file is being generated on the following path:

C:\Users\theUser\Oracle\oradiag_theUser\diag\clients\user_theUser\host_3095494057_80\trace\sqlnet.log

This file is being written every second with the following error log:

            Fatal NI connect error 12560, connecting to:
             (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=G:\application\application_process.exe)(HOST=server_name)(USER=user_name)))

              VERSION INFORMATION:
                TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
                Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
                Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
              Time: 18-MAR-2016 13:06:35
              Tracing not turned on.
              Tns error struct:
                ns main err code: 12560

            TNS-12560: TNS:protocol adapter error
                ns secondary err code: 0
                nt main err code: 530

            TNS-00530: Protocol adapter error
                nt secondary err code: 126
                nt OS err code: 0

So at that point the file get so big that my server run out of space. I'm trying to determine why this happens, I have been investigating a lot and I suspect that is an ORACLE_HOME issue, but still can't prove this.

Someone experienced something like this before?


texts: With MySQL Replication, what level of resilience is possible?

texts:
I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing my system, so I'm not here looking for "howto" administrative advice, rather I want to understand the achievable semantics.

So, to simplify my requirements, I have an application that cannot function unless it can read a database. There are two scenarios: first, the common usage, many requests a minute, read the database return an answer.  Second, less freaquently, update the database with new data. It's acceptble for the updates to be a few minutes delayed.

So my first thought:  Master->Slave

Now the reader can use either Master or Slave, if we lose the Master for a while the reader can work against the Slave.

Sounds simple enough. But ... what about more drastic problems, how manual is recovery? How long does it take? What data is lost?

Take this scenario:  Master->Slave. We know that the slave is potantially a little out of synch with the Master. Now suppose we lose the master in a way that means it won't be back any time soon.

Now presumably we need the Slave to become the writable Master, and we'll need a new Slave.

Specific Questions:

How much time and effort to make the Slave the Master - I failed to find docs on what to do. I'm guessing that this is pretty easy. Can we make this take-over seamless to client apps? Adjust DNS routing or some such?
If we can't now get at the old Master's logs, then we have to accept that some updates to the master will never make it to the new Master, we do have data loss?
How much effort to create the new Slave? My guess here is that this is not difficult but potentially takes time. I was trying to imagine reducing this overhead by having two Slaves, and adjusting the replication so that when Slave 1 becomes the new Master, Slave 2 now becomes the slave of that new Master. However given the potential delays in replaction I don't see that ensuring complete consistency is very easy.


texts: Adding additional condition significantly degrades performance

texts:
Have the following query:

SELECT * FROM table_A
INNER JOIN table_B ON /* conditions */
INNER JOIN table_C ON /* conditions */
INNER JOIN table_D ON /* conditions */
WHERE
  table_B.state = 'active' AND
  table_B.completed = 'f' AND
  table_B.end_date IS NOT NULL AND
  /* tons of other conditions and subqueries*/
  ORDER BY table_A.id

It takes 250'000 ms. If execute this query without table_B.state = 'active' condition, it takes less than a second.

explain analyze shows me that plan for both queries is the same (it
uses mostly sequential scan except some subqueries).
table_B contains only 10168rows.
state has character varying(255) type
I tried to increase effective_cache_size, work_mem, shared_buffers to 512MB, 32MB, 128MB respectively but it didn't help.
I use PostgreSQL 9.1.9.

I really can't believe that string comparison costs so much and think that it's not a reason. Any suggestions?

Below you can see part of explain analyze output for both queries. For 250.000 ms:

http://explain.depesz.com/s/RJs

Sort  (cost=5966.80..5966.80 rows=1 width=37) (actual time=250432.738..250432.747 rows=95 loops=1)
  Sort Key: table_A.id
  Sort Method: quicksort  Memory: 32kB
  ->  Nested Loop  (cost=0.00..5966.79 rows=1 width=37) (actual time=685.612..250432.564 rows=95 loops=1)
        ->  Nested Loop  (cost=0.00..5965.76 rows=1 width=41) (actual time=685.594..250431.181 rows=95 loops=1)
              Join Filter: some conditions
              ->  Nested Loop  (cost=0.00..342.66 rows=114 width=20) (actual time=0.056..10.305 rows=1575 loops=1)
                    Join Filter: some conditions
                    ->  Seq Scan on table_B  (cost=0.00..321.10 rows=454 width=24) (actual time=0.036..6.091 rows=1618 loops=1)
                          Filter: ((NOT completed) AND (end_date IS NOT NULL) AND ((state)::text = 'active'::text))

for 768 ms:

http://explain.depesz.com/s/f8K

Sort  (cost=33164.70..33164.72 rows=8 width=3223) (actual time=701.824..701.833 rows=112 loops=1)
  Sort Key: table_A.id
  Sort Method: quicksort  Memory: 142kB
  ->  Nested Loop  (cost=0.00..33164.58 rows=8 width=3223) (actual time=682.709..701.456 rows=112 loops=1)
        ->  Nested Loop  (cost=0.00..33161.36 rows=8 width=3204) (actual time=682.691..701.098 rows=112 loops=1)
              Join Filter: some conditions
              ->  Nested Loop  (cost=0.00..422.31 rows=697 width=377) (actual time=0.047..3.951 rows=2009 loops=1)
                    Join Filter: some conditions
                    ->  Seq Scan on table_B  (cost=0.00..295.68 rows=2789 width=168) (actual time=0.011..1.897 rows=2052 loops=1)
                          Filter: ((NOT completed) AND (end_date IS NOT NULL))

UPDATE: I think Postgres incorrectly choose execution plan for queries. As you can see estimation of query execution significantly differs from real execution. Also this queries are performed with different performance on different Postgres versions. For example, for 9.1.13 it takes 1.6s for both queries. For 9.3.4 they both are performed for 280s. Also it's possible to increase performance if turn off enable_nestloop, enable_hashjoin (so query will be performed with another plans). So finally i decided to simplify query.


texts: What should be the schema of the database if the parent table is being referenced in more than one child table?

texts:
I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on Category.
How should I design the database for such a situation?
As of now, I have created these tables as below:
Category:

CategoryId
CategoryName
HasSubCategory

SubCategory:

SubCategoryId
CategoryId
SubCategoryName

Product:

ProductId
CategoryId
SubCategoryId
ProductName

Now, while creating a Product, if the selected Category's HasSubCategory is true then SubCategory would be selected by the user. Now consider a situation that the user selects a Category whose HasSubCategory is true, and based on the selected Category, SubCategories will be loaded and after selecting a SubCategory, the user submits the Product record. Now, what if the user modifies the SubCategory record which was selected in the Product, and changes its dependent Category. In this case, I need to update the Product record as well and change its Category as per the new Category selected in the SubCategory record.
Is there any better approach to handle such a situation or I shall manage like this only?


texts: PostgreSQL performance degradation over time on a write intensive db

texts:
I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the query is 2ms then next day it got to 15ms then day after 30ms.

By query I refer here to a combination of either:

insert a row into table 2, select a row from table 2, select a row in table 3, update a row in table 3, commit
insert a row into table 1, select a row in table 3, update a row in table 3, commit

I wonder what might be the reason of that or which settings from the configuration file should I consider setting and how? I observed the problem on Ubuntu machine where database was set and the primary keys were not added. On the other hand on Win which I develop on it was not observed (it was running constantly on average 3ms per query for 7 days).

I noticed that in the new database (on Ubuntu) there were no primary keys on any table, as oppose to the one I develop on. Could the lack of primary keys have the negative impact on this sort of query?

I thought I will ask this question in the mean time as I am moving my whole db from my development machine to the test one.

On development I used PostgreSQL 8.4 (CPU: Intel i7 740QM, RAM: 6GB), on test there is PostgreSQL 9.1 (CPU: Intel i3-2100, RAM: 3.8GB).

UPDATE: autovacuum related parameters:

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

UPDATE2:
It appears that the problem occurs on the development machine as well, but I remember it running fine before. Never the less I did some more testing and did run EXPLAIN ANALYZE on the query, which I takes the most time, and it is an update (I also seen selects take a while on the table as well) presented below:

EXPLAIN ANALYZE UPDATE ais_track SET latest_dynamic = '2012-09-10 22:22:22.222' WHERE mmsi = 123456789 AND ais_system = 1;

The below results are on Win as restore still is in progress on the Ubuntu, and I got this at first:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=1.090..2.460 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 8.681 ms

Then on 2nd repeat and further repeats of the same update query for a few times I get something of this form:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=0.699..1.797 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.850 ms

After a hundred repeats or so it got to over 2ms.

EXPLAIN ANALYZE for Select:

EXPLAIN ANALYZE SELECT * FROM ais_track WHERE mmsi = 123456789 AND ais_system = 1

1st run:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=1.283..2.522 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 2.560 ms

After a hundred or so runs:

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=0.027..1.357 rows=1 loops=1)
  Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.382 ms

The table used in query:

CREATE TABLE ais_track
(
  ais_system integer NOT NULL,
  mmsi integer NOT NULL,
  ext_id integer,
  latest_dynamic timestamp without time zone,
  latest_static timestamp without time zone,
  "name" character varying,
  CONSTRAINT pk_track PRIMARY KEY (mmsi, ais_system)
)

And two indexes:

CREATE INDEX ais_track_mmsi
  ON ais_track
  USING btree
  (mmsi);

CREATE INDEX ais_track_sys
  ON ais_track
  USING btree
  (ais_system);

NOTE: The table size is 11000 and it doesn't change.


texts:
When is it good to use SELECT DISTINCT .. and when not?

Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some problems (not selective enough, bad joins, incorrect logic and so on).

Do you have similar experience or is it just me?


texts: Active Directory to authenticate user access to Postgres DB through ODBC driver. Is this possible?

texts:
I need for a user to logon to a Postgres DB through either MS Excel or Power BI, without entering any credentials, using solely their Active Directory login. I'm mainly a BI Developer and not usually part of the infrastructure side of things, but I've been tasked with finding out if this is at least feasible.

Am I right in thinking that this would be a pretty normal task within Microsoft infrastructure?

User signs on with AD to domain/their machine
AD grants access to something like SQL server (I take through some native MS protocol, not ODBC)
Excel(MS Query)/PowerBI from the users desktop rides that connection to allow the tool(s) seamless access to data as defined by their AD access rights.

The aim is to remove any additional credential entry by the user into either of the tools, because there'd be a high user turnover and user access to data sets must be stringently managed.

I imagine there would be back and forth between the AD admins and the DBA to facilitate user creation and access control at both ends, and I know postgres itself is AD/LDAP compliant, so some kind of access is possible. I'm just not sure if point 3, the seamless connection is possible.