texts: How can I be specific about which CDB I want to connect to wrapping a PDB?

texts:
I'm quite new to Oracle. I created a CDB like cdb1 and then a PDB within like pdb1. Now I can connect to my PDB using something like:

sqlplus user/password@server:port/pdb1

But can't I have two PDBs with the same name in different CDBs? If that happened, sqlplus wouldn't know which PDB I wanted to connect to. Does the listener have some robust way to map the PDB passed in the connection to an actual PDB within a CDB on the machine where the listener is running?

EDIT: I found out that when I write pdb1 in the sqlplus connection identifier, that is actually not the name of the PDB, but the name of the service, which just happens to be the same as the name of the PDB.

As far as I understand, I can map the service name to PDB name using select name, pdb from v$services;.

It seems to map services to CDBs I need to type lsnrctl service. And then I get output like:

Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

So I guess the instance cdb1 is referring to the CDB cdb1? Or does it also just happen to have the same name?

Then again, surely the service stating that it has 1 instance, means it could have 2 instances and these could point to separate CDBs, which brings me back to my original question of how to differentiate different PDBs with the same name.


texts:
I'm trying to use Query Store on SQL Server on Amazon RDS, and not having any luck. The query store works for a few hours after each failover, but then stops working. For example, our last manual failover was June 13 @ 5am. When I query the records in the query store, the last record there is from June 13 @ 10am. When I try to clear the query store, the records aren't removed. Same happens if I try to turn query store off for the databases.

SELECT  RecordCount = COUNT(*),
        LastDate = MAX(last_execution_time)
FROM    sys.query_store_runtime_stats;
GO
ALTER DATABASE [foo] SET QUERY_STORE CLEAR;
GO
SELECT  RecordCount = COUNT(*),
        LastDate = MAX(last_execution_time)
FROM    sys.query_store_runtime_stats;

... returns ...

RecordCount LastDate
----------- ----------------------------------
9025        2017-06-13 10:13:16.0500000 +00:00

(1 row(s) affected)

RecordCount LastDate
----------- ----------------------------------
9025        2017-06-13 10:13:16.0500000 +00:00

(1 row(s) affected)

I'm hoping that I'm just missing an RDS specific method for dealing with the query store, but have found nothing of use yet. Any suggestions?


texts:
In SSMS I have this

:SETVAR DBSrvrInst MyDatabase
:connect  ADMIN:$(DBSrvrInst)

In the Messages tab I get

  Connecting to ADMIN:$(DBSrvrInst)...

The variable isn't being replaced.
But if I do this and remove the constant

:SETVAR DBSrvrInst MyDatabase
:connect  $(DBSrvrInst)

In the Messages tab I get

  Connecting to MyDatabase...

The variable is replaced correctly.
How do I get this to work

:SETVAR DBSrvrInst MyDatabase
:connect  ADMIN:$(DBSrvrInst)

I would even be ok with using the port but that doesn't work either

:SETVAR DBSrvrInst MyDatabase
:connect  $(DBSrvrInst),1434

Any suggestions are greatly appreciated.


texts: I need to run pairs of packages from one job. First packages needs to run linearly. Second ones in parallel. I want to use configuration table for those packages, and use SSIS foreach container to run those pairs according to configurations. So I need somehow to cause this container not to wait for a second package to finish and run the next pair. One way that I know is to use SQL jobs that can be fired and be forgotten, but that means, that I need to create a job for each pair, but there might be a hundreds of them, so I want to avoid that.


texts: Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

texts:
If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a function which deletes 100,000 records from a main table and all of its associated data from ancillary tables, the initial delete takes about an hour.

If I wait 10 minutes before running the function, it runs in about 16 - 20 seconds.  I run this function 120 times in separate transactions in this script, then rebuild my constraints and indexes at the end (postgres was hanging in the middle if I try to keep them all in the same transaction and even at 500000 per transaction sometimes resulted in a performance hit).

Is there anything which postgres does following index/constraint changes that could account for the need to wait on running my deletes?


texts: What is a good script or free/trial tool to inventory/replicate all settings in master db?

texts:
Context: CU10 failed, reporting a corrupt master db when applied to SQL Server 2012 SP3 CU5.  We built an empty new master on that server and will restore the backup of the CU5 master into a new CU5 instance on a different server. Now we need to replicate all settings from the restored CU5 master into the newly built CU10 master.

We cannot restore the backup of the CU5 master onto the CU10 instance of course, because the backup is from an older version.  We are not concerned about master encryption key as we have not used SSRS, SSL or encryption on this server.


texts: Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

texts:
Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration.

Unfortunately, I couldn't get the example to work for event JOB_OVER_MAX_DUR in Oracle 10gR2 on one particular box.

What I've tried is:

-- create log table
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
  (message IN sys.scheduler$_event_info) as
begin
--
  insert into job_output values (systimestamp,'sniper job started for '
   ||message.event_type||' from '
   ||'"'||message.object_owner||'"."'||message.object_name ||'"');
  commit;
end;
/

-- create a sniper program
begin
  dbms_scheduler.create_program (
      program_name => 'sniper_prog',
      program_action=> 'sniper_proc',
      program_type => 'stored_procedure',
      number_of_arguments => 1,
      enabled => FALSE) ;
--
  dbms_scheduler.define_metadata_argument ('sniper_prog','event_message',1);
  dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to log any job that has
-- raised an event
begin
  dbms_scheduler.create_job('sniper_job',
    program_name=>'sniper_prog',
    event_condition =>
      'tab.user_data.event_type = ''JOB_OVER_MAX_DUR'' OR tab.user_data.event_type = ''JOB_SUCCEEDED''',
    queue_spec =>'sys.scheduler$_event_queue,myagent',
    enabled=>true);
end;
/

-- create job to test the sniper job
begin
  dbms_scheduler.create_job
    ( 'first_job', job_action =>
        'insert into job_output values(systimestamp, ''first job begins'');
         commit; dbms_lock.sleep(120);
         insert into job_output values(systimestamp, ''first job ends'');',
      job_type => 'plsql_block',
      enabled => false ) ;
  -- set max runtime
  dbms_scheduler.set_attribute
    ( 'first_job' , 'max_run_duration' , interval '60' second);
  -- set all events to be raised (for debugging)
  dbms_scheduler.set_attribute(
    name      => 'first_job',
    attribute => 'raise_events',
    value     => dbms_scheduler.job_all_events);
  -- start the job
  dbms_scheduler.enable('first_job');
end;
/

For some reason the event JOB_OVER_MAX_DUR is either not raised or the sniper_job doesn't receive it. Only JOB_SUCCEEDED is received.

My log table looks like:

SELECT *
  FROM job_output
ORDER BY 1 DESC;

25.07.2012 10:39:11,475879 +02:00   sniper job started for JOB_SUCCEEDED from "SCOTT"."FIRST_JOB"
25.07.2012 10:39:10,155557 +02:00   first job ends
25.07.2012 10:37:10,142660 +02:00   first job begins

So no event JOB_OVER_MAX_DUR was received.

Any ideas what I am doing wrong or which init parameters I have to check/set or how to debug these events?

Edit: I'm interested why this isn't working in 10g. This example is working fine in 11g but not in 10g and I don't see any reason why.

Edit 2: The example is working fine at a different 10g box, but on the intended one it's not.  I've checked all the %scheduler% dictionary views, but none of them gives details on which events have been raised. Any ideas/hints how to debug scheduler events? In this case I have DBA rights and OS server level access to debug.


texts:
In order to save licensing costs I'm evaluating whether I can get away with Standard Edition for a VLDB. I'll outline the scenario:

Here, "VLDB" means 5TB.
The size is expected to be stable over time.
Almost all data would be in a single table.
The query and DML pattern is a key-value type of scenario. There are only single-row accesses by primary key.

I wonder what I would need Enterprise Edition for. I would not derive any benefits from partitioning because I don't want to switch partitions or place them on different storage. Data compression could save us maybe 50% but we use cheap storage. The saving would be insignificant compared to the expensive license.

For HA I don't see why I couldn't go with the deprecated but still working Mirroring. That's not ideal but Enterprise Edition is a high price to pay for unclear benefit.

Still, it makes me nervous to use Standard Edition here because it is not what's generally recommended and it seems vaguely risky. Looking for some advice on this situation. Thanks!


texts:
I want to know what happens when a drop database command is canceled using ^C.

Does it partially removes the data or completely?


texts: Exec Stored Procedure on particular day of week

texts:
I have a procedure which should execute on specific day i.e. only on Monday of the week.

How to call it from another procedure passing variable or parametre ?
without using SQL server agent

declare @wday as nvarchar
set @wday = (select DATENAME(dw,getdate()))

select @wday as day1


texts: Percent decrease in value each month with SQL

texts: I'm trying to make a field in MS SQL that will serve as a target field, and I would like for it to decrease automatically each month. For instance, starting in April with a target 99.4%, May would decrease to 98.8 and June would be 98.3? Is there an easy way yo do this with SQL that I'm not aware of? I've gone through and manually entered them in with a CASE STATEMENT but I would like for them to not be hard coded.


texts: How to recursively update a same string?

texts:
I have a table with a column containing names like this:

id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan Bourdillion Traherne

I have a second table with a list of first names, like this

id, first_name
1, Emily
2, John
3, George
4, Suzan
5, Paul
6, Alan
7, Mary
8, Mick
9, Bourdillion
10, Jim
11, Cole

And I want to remove the first names in the first table, in order to obtain this:

id, employee
1, Mr. Thornton
2, Mr. Mckenzie
3, Mr. McDoughal
4, Ms. Flemming
5, Mr. Traherne

No matter how many first names, I would like to remove them all without calling several times my first_names table, and I wonder if this would be possible without using a loop in a function.

I have tried a query like this :

WITH RECURSIVE name AS (   SELECT REPLACE(t1.employee, t2.first_name, '') sec_name
                        FROM  t1, t2
                        WHERE position(t2.first_name in t1.employee) > 0 )
SELECT sec_name FROM name ;

But I get as many output as there are first names in the input, like :

Id, sec_name
1, John Thornton
1, Cole Thornton
2, Paul Mckenzie
2, George Mckenzie
...

My Postgres version is 9.6 .

Any help will be much appreciated !


texts:
I do try to create a dump of a table, that is stored inside of my database.
The table is called

webapp_product

In order to create the dump, I do use following commands:

docker-compose exec db1 pg_dump -F tar -d db -U user -t webapp_product > /home/backup.tar

Then, on the other machine:

sudo mv backup.tar ../pgdata
docker-compose exec db1 sh
pg_restore -Ft -C -U user -d db /var/lib/postgresql/data/pgdata/backup.tar

Unfortunately, it always ends up with the same scenario. I do receive following error log:

pg_restore: error: could not read from input file: end of file

I have also tried the other way. After moving the backup file, I have been trying to use:

cat /var/lib/postgresql/data/pgdata/backup.sql | psql -d db -U user

After invoking that command, I have been receiving an error saying that I am unable to create duplicates (IDs of the records were the same.)
In order to go further, I have tried to turn off all of the constrains, but without any expected effects.

@Update1

The backup.tar file consists of a following lines:

-- NOTE:
--
-- File paths need to be edited. Search for $$PATH$$ and
-- replace it with the path to the directory containing
-- the extracted data files.
--
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1 (Debian 12.1-1.pgdg100+1)
-- Dumped by pg_dump version 12.1 (Debian 12.1-1.pgdg100+1)

Can it affect the process of restoring the data?

and has a following ending:

--
-- PostgreSQL database dump complete
--


texts: Why if I add one more condition on WHERE clause, then using an index fails?

texts:
Here is my query:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = 29
AND question.amount IS NULL
AND answer.id not in (
  select post_id
  from votes
  where table_code = 15
  group by post_id
  having sum(value) < 0)

And this is the EXPLAIN result of it:

As you see, all tables are using an index. Ok, now I need to add one more condition on the WHERE clause of outer query. This is that condition:

... AND from_unixtime(answer.date_time) BETWEEN (now() - INTERVAL 1 year) AND (now() - INTERVAL 1 hour)

Well, after adding that new condition, this is the EXPLAIN result of it:

See? answer table doesn't use any index anymore. Why? And what index do I need to make query above faster and more efficient?


texts: How do I switch master with slave and vice versa on MySQL?

texts:
I have two instances of MySQL, one master and one slave.

How can I switch them to make the slave the master and vice-versa while avoiding downtime?


texts: Is it okay to rename the MySQL data files (FRM) manually?

texts:
Recently I realized the case sensitivity issues in MySQL. I wanted to convert my upper case table names to lower case using this solution but I forgot to do Step 1, and completed the remaining steps. Now I couldn't access my tables that are in upper case. Renaming the tables using

mysql>  rename table BOOK to book;
ERROR 1017 (HY000): Can't find file: './las_data_3_dt2014/book.frm' (errno: 2)

returns error.

I'm unable to use mysqldump on my database.

mysqldump: Got error: 1146: Table 'las_data_3_dt2014.adjustment_type' doesn't exist when using LOCK TABLES

I want to know if it's okay to rename the upper case .FRM files to corresponding lower case files, so that I can run the rename commands and access the data again.


texts:
Though SQL is more affiliated with table like operations and not so much with recursive, say we would like to implement the linked (or double-linked) list concept (like we have for instance in C).
Is there any way to do this efficiently, considering that we can have items moving around from any place into any place on a linked list?
Some solution using CLR?
Or is it really something that should never be brought to SQL Server?

Note that this question evolved also into a linked lists VS trees discussion

Though I pinned SQL Server, this is an academic like question, so a solution in any other is also good, even if we just get to the conclusion that this is something that should never be brought to the database.


texts: how to install SQL 2000 in windows server 2016 OS, Is it possible ,please give me a solution.


texts: Restart of rownumber based on count in other table in oracle sql

texts:
I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) while table2 presently has 80,000 rows (row count varies from day to day).

TABLE1 :- NAME_VALUES
NAME_VAL
--------
TOM
DICK
HARRY

TABLE2 :- CUS_TABLE

CUS_ID
---------
401795480
201134211
137643082
876450821
777290153
111035791
579865552

I want to have something like below as output

401795480 TOM
201134211 DICK
137643082 HARRY
876450821 DICK
777290153 HARRY
111035791 TOM
579865552 DICK

My idea was to assign rownumber for each table. For table 2 I would like to restart the rownumber once the maximum count of table 1 is reached, something like below, but I am unable to figure out how can I perform this

Table1
    NAME_VAL   TABLE1_RN
    ---------------------
    TOM          1
    DICK         2
    HARRY        3

Table2
    CUS_ID    TABLE2_RN
    --------------------
    401795480 1
    201134211 2
    137643082 3
    876450821 1
    777290153 2
    111035791 3
    579865552 1

Now I have a key and can easily map to fetch the details I require.

Please suggest if any method can satisfy my requirement.


texts: How do I add extra columns to crosstab()?

texts:
I am using PostgreSQL 9.6 and have following schema

CREATE TABLE activitylog (
    sessionid text NOT NULL,
    studentid integer NOT NULL,
    activity text NOT NULL,
    created timestamp without time zone DEFAULT now() NOT NULL
);

The schema is used to log visitors activity. Each visit is assigned a session id which is unique for the visit. Sessions ends when user logs out. Every user can log in and log out multiple times.

INSERT INTO activitylog VALUES
  ( 'abc', 1, 'logged in'   , '2017-08-12T02:39:05.753088Z' ),
  ( 'abc', 1, 'page visited', '2017-08-12T02:49:05.753088Z' ),
  ( 'abc', 1, 'logged out'  , '2017-08-12T02:59:05.753088Z' ),
  ( 'def', 1, 'logged in'   , '2017-08-12T03:09:05.753088Z' ),
  ( 'def', 1, 'page visited', '2017-08-12T03:19:05.753088Z' ),
  ( 'def', 1, 'logged out'  , '2017-08-12T03:29:05.753088Z' ),
  ( 'ghi', 2, 'logged in'   , '2017-08-12T03:39:05.753088Z' ),
  ( 'ghi', 2, 'page visited', '2017-08-12T03:59:05.753088Z' ),
  ( 'ghi', 2, 'logged out'  , '2017-08-12T03:59:05.753088Z' );

Required Result

I want to select data from the table such that I get result in following format

|  sessionid  |  studentid  |          loggedin           |          loggedout          |      interval      |
| ----------- | ----------- |       --------------        |         -----------         | ------------------ |
|     abc     |      1      | 2017-08-12T02:39:05.753088Z | 2017-08-12T02:59:05.753088Z |       20min        |
|     def     |      1      | 2017-08-12T03:09:05.753088Z | 2017-08-12T03:29:05.753088Z |       20min        |
|     ghi     |      2      | 2017-08-12T03:39:05.753088Z | 2017-08-12T03:59:05.753088Z |       20min        |

Basically,

Pivot table
Add studentid column
Calculate interval

What I have tried so far

I have successfuly piovoted the table

SELECT * FROM crosstab(
$$
select sessionid, activity, created from activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$
)
AS sessioninfo (sessionid text, loggedin timestamp, loggedout   timestamp);

I tried to add studentid extra column using this query but it didn't give me desired result

SELECT * FROM crosstab(
$$
select sessionid, studentid, activity, created from
activitylog
where activity = 'logged in' OR activity = 'logged out'
ORDER  BY 1, 2
$$,
$$
VALUES (1), (0)
$$
)
AS sessioninfo (sessionid text, studentid INTEGER,
loggedin timestamp, loggedout   timestamp);

But this places null in logged in and logged out timestamps.

To sum it up

What is the purpose of VALUES with crosstab()? To be honest I am not completely sure what purpose second parameter of crosstab() serves and why in almost all examples it is VALUES.
How do I add studentid to result?
How do I calculate interval between two columns that are result of crosstab()?


texts: MySQL: Skip INSERT / UPDATE statement that triggers error but show them in output

texts:
I have a million of rows of customers to be imported via SQL dump. Customer data will be inserted into main table customer and child table customer_address

The dump contains the following SQL statements:

File: customer-dump.sql

BEGIN TRANSACTION;
INSERT INTO customer (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO customer_address (customer_id, city, country) VALUES (LAST_INSERT_ID(), 'New York', 'USA');
COMMIT;

BEGIN TRANSACTION;
INSERT INTO customer (firstname, lastname) VALUES ('Lorem', 'Ipsum');
INSERT INTO customer_address (customer_id, city, country) VALUES (LAST_INSERT_ID(), 'Chicago', 'USA');
COMMIT;

# So on for N no of customers

I am importing via CLI as:

mysql -u [user] -p [database] < customer-dump.sql > /tmp/customer-import-error.log

My Queries:

How to continue the script execution even if there is a failure in some rows?
How to show/log errors for failed rows?
Any advice on such massive import?


texts: I am not able to get the result when joining two tables with where clause with OR condition

texts:
I have two tables projects and project_user_cross. I want to get the projects if the user id exists in either of the two tables

Here is the projects table

Id | project_manager_id | name | description | created_at | updated_at | progress
1  | 1                  | p 1  | lorem ipsum | 2019-9-16  |            | 0

Here is the project_user_cross table (empty)

Id | project_id  | user_id

Here is the query I tried:

SELECT *
FROM projects p
INNER JOIN project_user_cross c ON c.project_id = p.id
WHERE p.project_manager_id =1 OR c.user_id =1

Since project_manager_id = 1 exists I want the query to return the projects but its returning nothing.


texts: Why does Extended Events server_principal_name return NULL sometimes?

texts:
I have an extended event session setup. This is the definition.

CREATE EVENT SESSION [Successful-Logins] ON SERVER
ADD EVENT sqlserver.connectivity_ring_buffer_recorded (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_principal_name,
    sqlserver.session_id, sqlserver.sql_text ) ),
ADD EVENT sqlserver.login ( SET collect_options_text = ( 0 )
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) ),
ADD EVENT sqlserver.logout (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) )
ADD TARGET package0.event_file ( SET filename = N'Successful-Logins' ,
                                  max_file_size = ( 10 ) ,
                                  max_rollover_files = ( 0 ) )
WITH ( MAX_MEMORY = 4096 KB ,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
        MAX_DISPATCH_LATENCY = 30 SECONDS ,
        MAX_EVENT_SIZE = 0 KB ,
        MEMORY_PARTITION_MODE = NONE ,
        TRACK_CAUSALITY = ON ,
        STARTUP_STATE = OFF )
GO

My intention is to query sys.fn_xe_file_target_read_file regularly with a filter on server_principal_name. Unfortunately, this field is not always populated. From the same client app and client host, I have some events where server_principal_name is NULL and other where it is populated.

I'm using this query for testing.

SELECT  --TOP 10
        CONVERT(VARCHAR(19),DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')),120) AS [timestamp] ,
        xevents.event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        DB_NAME(xevents.event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS [database_id],
        xevents.event_data.value('(event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(max)') AS [server_principal_name],
        xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client_app_name],
        xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname]
FROM sys.fn_xe_file_target_read_file
            ('C:\Program Files\Microsoft SQL Server\MSSQL11.INSTANCENAME\MSSQL\Log\Successful-Logins*.xel',
            NULL,null, null) f
CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
ORDER BY DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')) DESC


texts: Trying to understand TID definition from PostgreSQL documentation

texts:
I am trying to understand what is TID and how it works.
I found 2 definitions of TID in the documentation:

1) from https://www.postgresql.org/docs/11/datatype-oid.html

  A final identifier type used by the system is tid, or tuple identifier
  (row identifier). This is the data type of the system column ctid. A
  tuple ID is a pair (block number, tuple index within block) that
  identifies the physical location of the row within its table.

2) from https://www.postgresql.org/docs/11/storage-page-layout.html

  In fact, every pointer to an item (ItemPointer, also known as CTID)
  created by PostgreSQL consists of a page number and the index of an
  item identifier.

I understand the second definition and it's clear for me, but I struggle with the first definition and the terms it uses.

What are block and tuple index within block? How do they match with pages and the second definition?

Please help and clarify, which definition is correct and how should I understand the terms from the first definition.


texts: Can't map invalid columns based on column names in a .dtsx-Package

texts:
I have the following problem. I currently have to modify a .dtsx-Package which was originally created by a coworker who is not arounf anymore. This package contains some logic but above all it does the following:

The Package contains some reference to xsd-files which provide a structure.
Some XML-files are fetched and read based on the structure provided with the xsd-files.
The process is supposed to write to several ADO destinations witch hold tables according to the data in the xsd-files.
After the package retrieves and reads the xsd + xml files a merge join is applied and the data is supposed to be written to the database.

The whole thing looks something like this:

So the rest of the package is huge and it works fine. But for this one component I have had trouble for several days now. Here everything works fine, except for the Merge-Join node. normally if I open the editor I am always able to map the column names according to their names. In this case this very same window looks as follows:

As you can see there are no mapping possible as this dialog doesn't show any column names, so every time I try it nothing happens.

Any ideas? Note the very same procedure works throughout the entire package, it is only in this one task where it behaves kinda strange.


texts: set session - custom variable to store user id

texts:
I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this:

set session "myapp.user" = '12345';
...
SELECT current_setting('myapp.user');

and it seems to work - I thought that "myapp.user" should be declared in .conf file but it seems that I can create session variables on the fly (I didn't change .conf file at all).

Is there any disadvantage of doing like this?


texts: Postgres: Explicit lock with transactions

texts:
I am a beginner in SQL world and have some doubts regarding transaction/locking:

In postgres, do transaction also ensures locking of rows(preferable)/tables until it is completed?
Also, if there are two transactions through different queries, will second one wait for the first one to complete i.e. will it be serial or concurrent?

I think both points infers same but I am not sure so posting both.


texts: Adding slave to existing master-master config throws foreign key constraint failed error

texts:
We have two MySQL servers running in master - master configuration.
Now we have to add a slave to the existing configuration, but upon adding the third database server and starting slave on it, it throws:

Cannot add or update a child row. foreign key constraint fails

We have tried taking a mysqldump from the master with --master-data and restored it. After that, we started the slave on the slave DB. But even then, it says the same error. We have also tried XtraBackup. But that also throws the same error. Are we missing something?
mysqldump command:
mysqldump --master-data -uroot -p dbname > dbname.sql

We have also tried it this way: percona xtrabackup
In both cases, upon getting the foreign key error, we tried to dump and restore individual tables referenced by the foreign keys manually from the master to the slave. Upon doing this, the replication starts and seems to work normally with 0 seconds behind master for a few minutes, after which another foreign key error shows up, stopping the replication.

auto_increment_increment is set to 2 in both masters.
auto_increment_offset is set to 1 in Master 1 and 2 in Master 2.
We have mostly innoDB tables and some MyISAM tables.
MySQL version 5.5.30.

There is a follow up question: After adding a slave to Master - Master configuration, slave syncs from only one master


texts: Find rows that have the same values in another column

texts:
I have the following table.

User  Permission
John  1
Mary  1
Anne  1
Joe   2
Marie 2
Nick  3

I want to select and group users with the same permission number. Is this actually possible? I have millions of different permission values.

Expected output:

(John, Mary, Anne), (Joe, Marie), (Nick)


texts: SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

texts:
I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database.

Part of the optimisation requires persisting Query Plan XML to a table, then indexing it so that I can easily pull out the Warnings such as PlanAffectingConvert and fix these issues.

However, when I try to create a selective XML index on the table, like so:

CREATE SELECTIVE XML INDEX sxi_queryplan
ON dbo.Table(query_plan)
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
FOR
(
    pathQueryWarnings = '/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/Warnings' AS XQuery 'node()'
)

...I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:
There is an error in XML document (1, 1762).
Instance validation error: 'SelectiveXML' is not a valid value for IndexKindType.

The index is still created though, however whenever I then try to query the table, using the below, I get the same error as above.

WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  query_plan FROM dbo.Table WHERE query_plan.exist('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/Warnings') = 0

I've spent all morning trying to find the answer to this, but had no luck finding anythign about the Error Messages.

Without the index on the table, the above query works fine, suggesting the XML is not actually invalid. With it, I get the errors...

Any ideas?


texts:
I have a table containing range table like this.

DayFrom Dayto Frequent
60      46    3     = interval 15 days (15/3 = Every 5 days send email for 3 times)
45      37    3     = interval 9 days (9/3 = Every 3 days send email for 3 time)
36      31    6     = interval 6 days (email will sent everyday)

I will sent email based on that table. The case is, there will be an Event/Promotion that start on 1 June 2016. I want to get the date based on that range becoming 1 row. When 2 April = GETDATE() the email will send, the email will send again on 7 April and so on until the range on Range table is finished.

a)  46-60:
1st: 2, April
2nd: 7, April
3rd: 12, April

b)  37-45:
1st: 17, April
2nd: 20, April
3rd: 23, April

c)  31-36:
1st: 26, April
2nd: 27, April
3rd: 28, April
4th: 29, April
5Th: 30, April
6th: 1, Mei

I'm just a newbie to select like in this case.. Usually i'm just query for normal case :(
I've tried the following query. But the frequent is not work.

select h.DayFrom ,h.DayTo ,h.Frequent ,
       ( h.DayFrom - h.DayTo + 1 ) AS AlarmDay ,
       (( h.DayFrom - h.DayTo + 1 ) / h.Frequent ) AS ReminderTimes ,
       ( F.PromotionFrom - ( DATEDIFF(d, GETDATE(), F.PromotionFrom) ) ) AS ReminderDate  from PF F INNER JOIN PendingApprovalDetail H ON F.SBU = H.SBU AND H.DocumentType = 'PF'
AND DATEDIFF(d,GETDATE(),F.PromotionFrom) BETWEEN H.DayTo and H.DayFrom


texts: How to aggregate pairs of id values into groups

texts:
I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example below to illustrate the problem.

id1 id2
--- ---
1    2
1    3
3    4
3    5
6    7

I want to group together ids so that all linked ids are grouped together even if there is not a direct link between 2 id values.

id group_id
-- ----
1   1
2   1
3   1
4   1
5   1
6   2
7   2

I would like to achieve this in sql without using cursors if possible.

All advice gratefully received.

Update: I thought it would be useful to add a bit more background on the data this represents. The data represents records for businesses sourced from different data sources

The main table looks like this

id business_name postal_address business_classification x y group_id

The main table can contain duplicates / similar records which need to be associated. The table with id1 and id2 columns is a result of de-duplication using fuzzy string matching (levenshtein, word matching etc) and spatial proximity matching. I think that this table is actually an undirected graph containing many disconnected sub graphs e.g. group_id 1 and 2.I want to join all ids in the same network and update the group_id in the main table.

I think that the question is now how to identify all disconnected sub graphs on a graph using SQL server Find All Disconnected Sub Graphs - Java


texts:
I need to write query that finds the average for each classname, total average for each student, I already wrote two separate query for each one of those, but how to combine to one query?

My 1st query:

select distinct  StudentFirstName,StudentLastName, ClassName, avg(Grade) as 'average for this subject'
from tests
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName,ClassName;

My 2nd query:

select distinct  StudentFirstName,StudentLastName, avg(Grade) as 'total average'
from tests
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName;

Students table:

  pk-INT           VARCHAR             VARCHAR
+-----------+------------------+-----------------+
| StudentID | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
|         1 | agam             | rafaeli         |
|         2 | amir             | aizinger        |
|         3 | avi              | caspi           |
|         4 | avia             | wolf            |
|         5 | ben              | moskovich       |
|         6 | chen             | segalovich      |
|         7 | dana             | levy            |
|         8 | daniel           | marcus          |
|         9 | daphna           | chwarts         |
|        10 | david            | cohen           |
+-----------+------------------+-----------------+

Tests table:

  PK-VARCHR    PK-VARCHR     PK&FK-INT   INT
+------------+------------+-----------+-------+
| TestDate   | ClassName  | StudentID | Grade |
+------------+------------+-----------+-------+
| 2017-07-01 | Algebra    |         1 |    88 |
| 2017-08-02 | Algo       |         1 |    97 |
| 2017-09-01 | Algebra    |         1 |    80 |
| 2017-09-01 | Algebra    |         1 |    97 |
| 2017-09-01 | Set-theory |         1 |    85 |
| 2017-09-04 | Calcules   |         1 |    86 |
| 2016-05-03 | Set-theory |         2 |    84 |
| 2016-07-02 | Calcules   |         2 |    89 |
| 2016-07-04 | Algo       |         2 |    83 |
| 2016-07-05 | Algebra    |         2 |    79 |
| 2016-06-03 | Algebra    |         3 |    99 |
| 2016-07-02 | Algo       |         3 |    97 |
| 2016-07-03 | Calcules   |         3 |    96 |
| 2016-09-03 | Set-theory |         3 |    95 |
| 2016-06-03 | Algebra    |         4 |    78 |
+------------+------------+-----------+-------+


texts: Service mongod fails after adding security.keyFile in config file

texts:
My mongo.conf:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile

# network interfaces
net:
  port: 27017
  bindIp: [127.0.0.1, 172.24.16.25]  # Listen to local interface only, comment to listen on all interfaces.

security:
  authorization: enabled
  keyFile: '~/rsKeyFile'

Issue:
When i perform service mongod restart after adding keyFile in mongo.conf as shown above

keyFile: '~/rsKeyFile'

My service fails when i check service mongod status it says:

"/etc/mongod.conf" 47L, 851C written
[root@788978-APP06 ~]# service mongod restart
Redirecting to /bin/systemctl restart  mongod.service
[root@788978-APP06 ~]# service mongod status
Redirecting to /bin/systemctl status  mongod.service
â mongod.service - High-performance, schema-free document-oriented database
   Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Thu 2017-05-18 06:19:43 PDT; 2s ago
     Docs: https://docs.mongodb.org/manual
  Process: 24066 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=1/FAILURE)
  Process: 24064 ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb (code=exited, status=0/SUCCESS)
  Process: 24060 ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb (code=exited, status=0/SUCCESS)
  Process: 24056 ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb (code=exited, status=0/SUCCESS)
 Main PID: 24066 (code=exited, status=1/FAILURE)

May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Starting High-performance, schema-free document-oriented database...
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Started High-performance, schema-free document-oriented database.
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: about to fork child process, waiting until server is ready for connections.
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: forked process: 24069
May 18 06:19:43 788978-APP06.dgsworld.com mongod[24066]: ERROR: child process failed, exited with error number 1
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: mongod.service: main process exited, code=exited, status=1/FAILURE
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: Unit mongod.service entered failed state.
May 18 06:19:43 788978-APP06.dgsworld.com systemd[1]: mongod.service failed.

I have 777 permissions on my rsKeyFile but still its not working, when i comment key fine in mongo.conf like given below and try restarting mongod service it works perfectly fine and show this Active: active (running) since Thu 2017-05-18 06:23:51 PDT; 2s ago status.

#keyFile: '~/rsKeyFile'

I am following this https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

Please help, thanks in advance!!


texts: What is the recommended my.cnf settings for 32 GB Server

texts:
I am just moving my website to a new server and ı want to know what is the recommended settings for 32 GB server?

Mysql tunning for my old server (16 GB)

https://pastecode.xyz/view/577e824e


texts: Functioning of Oracle's Server result Cache

texts:
I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] :

  PL/SQL function code can include a request to cache its results. Upon invocation of
  this function, the system checks the cache. If the cache contains the result from a
  previous function call with the same parameter values, then the system returns the
  cached result to the invoker and does not reexecute the function body. If the cache
  does not contain the result, then the system executes the function body and adds the
  result (for these parameter values) to the cache before returning control to the invoker

My doubt:

After a PL/SQL function is executed, its result set is stored in cache.
Then a DDL statement changes the table/index/anything that the function uses...

If I run the same PL/SQL function now, will I get the same result or updated result?


texts: Reason for using write barrier before unlocking the buffer header in Postgresql

texts:
I was reading the source code for buffer management in Postgres. In buffer management, there is a macro to unlock the buffer header
#define UnlockBufHdr(desc, s)   \
    do {    \
        pg_write_barrier(); \
        pg_atomic_write_u32(&(desc)->state, (s) & (~BM_LOCKED)); \
    } while (0)

This code clears the BM_LOCKED flag in state variable present inside buffer header atomically.
I know that pg_write_barrier() blocks the compiler from reordering loads and stores around the barrier and also ensures that the stores issued prior to the barrier are completed before
any stores issued after the barrier. This is used to remove the inconsistencies raised in SMP systems due to out of order execution.
But I am unable to see how reordering memory accesses here can be a problem. It will be of great help if someone can give me the reason for using a write barrier before the unlocking operation.


texts:
Recently inherited a new SQL server and after some Blitz (Thanks Brent and team) checks have noticed that some of the DB's are set to Torn_Page or None for the page verify option.  I want to change this to Check_Sum.

Company policy is raise a change for everything which I have done, I was going to change this early morning as I am sure the impact is very low.  I understand it will flush the proc cache but will it have any other impact on a live system, should it be an out of hours change?


texts: How to make search faster in mysql DB with > 2m records, only numbers

texts:
I have a table with sim card data with over 2m-3m records.

The design is a column sim_number = varchar(11) with data like number (0123456891)

When the table is under 1m records it's still fast but when over 2m it takes more time to search

Search by:

LIKE '%6789%' take about under 1s
LIKE '%567891%' take about 2s

Search a full number like (WHERE sim_number = '0123456891')

With the number as the last record take about 2-3s

My VPS has 2GB RAM

Can this performance be improved?

I tried to use full text search with this table but it's not working because there is no word in my search.


texts: Which characters are considered "word" characters

texts:
Fulltext searchin with InnoDB in MySQL only includes certain characters referred to in the docs as "word characters" and mentioned here:

  For the built-in full-text parser, you can change the set of characters that are considered word characters in several ways, as described in the following...

My question is, for a given character set, how can I determine which characters are considered "word" characters?  My table is utf_general_ci. I did a bit of trial and error and found for instance, that "¬" is not treated as a word character but "Ω" is. I'm looking for either a clearly defined reference or a tool of some support so that I can find this out without doing trial and error.


texts: ORA-01427:single-row subquery returns more than one row

texts:
I wrote in PL/SQL Developer:

select * from lc_history2

and I got that error:

ORA-01427: single-row subquery returns more than one row

Can someone help?  It's necessary.


texts:
When we use an argument length with numerical data types, as far as I know this specifies the display width.
I tried the following:

mysql> create table boolean_test (var1 boolean, var2 tinyint);
Query OK, 0 rows affected (0.10 sec)

mysql> show create table boolean_test;
+--------------+-------------------------
| Table        | Create Table
+--------------+-------------------------
| boolean_test | CREATE TABLE `boolean_test` (
  `var1` tinyint(1) DEFAULT NULL,
  `var2` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+---------------------------
1 row in set (0.00 sec)

First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.

mysql> insert into boolean_test values(101,112);
Query OK, 1 row affected (0.03 sec)

mysql> select * from boolean_test;
+------+------+
| var1 | var2 |
+------+------+
|   10 |  112 |
|  101 |  112 |
+------+------+
2 rows in set (0.00 sec)

I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1).
Shouldn't I see a 1 for var1? I.e. just 1 display digits?


texts: Why does pre-sorted index not help me in window function?

texts:
I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
  %(max_providers_to_target_per_territory)s as max_providers,
  case
    when call_point_rank = 1 then
        rank()
        over (partition by team_id, territory_name, call_point_rank order by office_tam desc, account_id, address_key, tam desc, id)
    else NULL
    end as cum_provider_count
from
(
  select *,
    -- We order by `account_id, address_key`, and then by `id` as a tie breaker for
    -- deterministic results.
      rank()
      over (partition by team_id, territory_name, id order by office_tam desc, account_id, address_key,
        tam desc, id ) as call_point_rank
  from
    providers_tam_with_offices_tmp
) a

As an experiment, I tried adding this index to providers_tam_with_offices_tmp.
create index providers_tam_with_offices_tmp_idx on providers_tam_with_offices_tmp
(team_id, territory_name, id, office_tam desc, account_id, address_key,
        tam desc, id );

But the query plan shows that this doesn't eliminate the need to sort the table first before calculating the first window function:

While the utility of this might be questionable, I am interested in the general case of why the optimiser wouldn't spot that there's already an index with exactly the sorting required.
Or have I misunderstood how window functions required the data to be organised?


texts:
I have a series of events (potentially hundreds) segregated into applications. Each one of these events has a dependency on others (except the first one, of course). I'm looking to map all of these dependencies in a table, but I'm not sure the best way to do so. One event can have multiple predecessors, or multiple successors, or both. There are also cases where A is a prereq for B, B is a prereq for C, but A is also a prereq for D, and C and D are both prereqs for E. (I can't assume that the dependency chain is linear.)

I already have a table with all of the events listed with some other details, and the event names are unique. My first thought was to have a "dependency" table that had an ID (PK) field, 'event' field containing the ID from the events table, and a "dependenton" field that also contained an ID from the events table. Obviously each event could have multiple entries if they are dependent on more than one other event. I believe this follows first normal form, but I'm trying to think if there is another design that would be more efficient or easier to work with.

Thank you.


texts:
SELECT SQL_CALC_FOUND_ROWS pn_posts.id
FROM   pn_posts
       LEFT JOIN pn_term_relationships
              ON ( pn_posts.id = pn_term_relationships.object_id )
       INNER JOIN pn_postmeta
               ON ( pn_posts.id = pn_postmeta.post_id )
       INNER JOIN pn_postmeta AS mt1
               ON ( pn_posts.id = mt1.post_id )
       LEFT JOIN pn_wc_product_meta_lookup wc_product_meta_lookup
              ON pn_posts.id = wc_product_meta_lookup.product_id
WHERE  1 = 1
       AND ( pn_posts.id NOT IN (SELECT object_id
                                 FROM   pn_term_relationships
                                 WHERE  term_taxonomy_id IN ( 8 ))
             AND pn_term_relationships.term_taxonomy_id IN ( 21 ) )
       AND ( pn_postmeta.meta_key = '_manage_stock'
             AND mt1.meta_key = '_stock_status' )
       AND pn_posts.post_type = 'product'
       AND ( pn_posts.post_status = 'publish' )
GROUP  BY pn_posts.id
ORDER  BY pn_postmeta.meta_value ASC,
          Field (mt1.meta_value, 'instock', '3days', 'outofstock'),
          wc_product_meta_lookup.min_price ASC
LIMIT  48, 24;


texts: How to sync two tables which are on different databases and servers using SSIS?

texts:
I am relatively new to SQL Server's tools. I am on the following situation:

On several stored procedures on a database D1 on server S1, I need to use data that is stored on a database D2 on server S2, on a table that is being constantly updated.

As far as I've read, I can use SQL Server Integration Services to automate this task and sync two tables between two different servers/databases.

What I want to do is to have a "mirror" table from S2/D2 on S1/D1. So my stored procedures could use that table transparently.

Could you give me some any guidance? Thanks so much.


texts: SQL 2008 Server - performance loss possibly connected with a very large table

texts:
We have SQL Server 2008 with 3 active databases running on it.

DB1 - cca 400 MB size
DB2 - cca 8 GB size
DB3 - cca 42 GB size - but the majority of records are not used at all

In DB2 we have a this table

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table by itsel occupies cca 8 GB and has 283 029 812 records.
Vast majority of records in this table are historical records and are accessed very seldom or never. But a small part of recent records is used quite a lot and every hour many new records are getting inserted in this table.

The problem is that we recently observe performance problems in DB3. Though performance of DB2 and PenData is OK.

My question is:

1.could the size of the table PenData be important factor for the overall server performance? How do these many unused table records affect memory allocated by the server?

2.Could I get significant performance gain on the server (in DB3) if I delete half of the records from the very large table PenData?

3.And are there any tools to monitor performance when I do not have permissions to access Activity monitor?

EDIT

I was quite terrified to see (using scripts provided in the answers) that the PenData table took 60-70% of whole SQL Server memory (which is relatively low cca 6 GB). I am not sure why, since this is the application I have programmed myself and I do not see any reason, why should so many rows from this table remain cached in memory. It had also been my mistake to run SELECT COUNT(*) FROM PenData before I had tried to see, how much of PenData remained cached in memory.

I have omitted one foreign key I have in this table, so I present it here:

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

I have deleted milions of records in batches of 100 000 records using SET RECORDCOUNT 100000 from PenData. Now it has 211 120 425 records. I have run DBCC SHRINKDATABASE (PenData, 20) - only after this has the memory consumption by PenData decreased significantly.

The performance and memory consumption by other databases got better.

But after one day the table PenData occupies again almost all the memory...

EDIT

I have changed one single SQL command in one single stored procedure and now everything is perfect, database SupervisionP takes only 184 MB in cache! See detail here

Index seek much slower with OR condition compared with separate SELECTs

Thanks for your help.


texts: SQL Server: Understanding log ship utility: sqllogship.exe

texts:
This is my first time using the stock SQL log shipping setup. The copy operation is failing.

I think I see the issue. I need to change the unc:

 '\\Win-2ooc0jplr5k\sql\logbackup\', Backup Destination Directory:
 '\\10.37.155.187\sql\logs\',

I need to change that first unc to an IP, because this environment is not resolving names. (No domain, etc). But when I look at the job, it has this:

sqllogship.exe" -Copy 34FCC5A2-8A2C-4581-9A58-04BC60E42DEF -server WIN-2OOC0JPLR5K

How do I modify the from/to unc paths?

(I am on SQL 2014, but I believe this topic spans several versions)


texts:
We are seeing restore taking more than 25 minutes for a simple 4 mb file .Restore progresses to 100%,but it wont update any status from there

When checked in error log ,we could see below error...

  Process 0:0:0 (0x12c8) Worker 0x000000CB5481A160 appears to be non-yielding on Scheduler 0. Thread creation time: 13223894899341. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 2%. System Idle 92%. Interval: 70083 ms.

We are able to match the worker address with session id of restore as shown in below screenshot

further we could see wait type of the spid for restore is 'PREEMPTIVE_OS_CREATEFILE'

Below are some more details

Version:

  Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)
      Jul 20 2019 21:42:29
      Copyright (c) Microsoft Corporation
      Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: )

windows details :

  windows serrver 2012 R2 standard

actual file sizes of this mdf,ndf ,ldf are 0.5,0.4 and 1 GB respectively.backup file is 4 MB

File speeds during restore are below 50 MS.

Any ideas on what may cause this behaviour ..?


texts: Find the missing numbers in a given range

texts:
Hi I have a table that has start and  end columns that has the values

Start   End
9       10
11      19
29      44
45      51
56      60

Now if I give the range like 15 to 60 I need to return the values that not being covered in the above range.

The output I am expecting is  - 20,21,22,23,24,25,26,27,28,52,53,54,55

How can I retrieve this?


texts: To use a cursor or something else in my while loop?

texts: From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select statement and pass a few columns from each row into another stored procedure for processing. The only way I see to do this is to create a cursor and run a while loop to use the cursor to grab each row so the data can be processed. So is using a cursor to step though each row from a select statement for processing the best way to do this or is there another way? In my case I have to make sure every single row is processed.


texts: I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don't know SQL's seed so I cant generate the same output using python. Is there a way to set SQL's seed? I wasn't able to find any information related to it.


texts: SQL Server 2016 Bad Query Plan locks up DB once a week

texts:
Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out a lot of queries.

UPDATE STATISTICS on all tables always fixes it immediately.

After the first time, I made it update all stats on all tables nightly (instead of weekly), but it still happened, (about 8 hours after the update stats runs, but not every day that it runs).

This last time, I enabled Query Store to see if I could find which specific query/query plan it was.  I think I was able to narrow it down to one:

After finding that query, I added a recommended index that was missing from this not-frequently-used query (but which does touch a lot of frequently used tables).

The bad query plan was doing an Index Scan (on a table with only 10k rows).
Other query plans that returned in milliseconds, used to do the same scan though.  The newest query plan, after creating the new index does only seeks.  But even without that index, 99% of the time, it was returning within a few milliseconds, but then, weekly, it'd take > 40 seconds.

Bad one that times out: http://brentozar.com/pastetheplan/?id=rymaWt56e
Previous plans that don't time out: http://brentozar.com/pastetheplan/?id=HyN7ftcpe
Newest plan with new index: http://brentozar.com/pastetheplan/?id=ryLuGKcag

This started happening after moving to SQL Server 2016 from 2012.

DBCC CHECKDB returns no errors.

Will the new index fix the problem, making it never choose the bad plan again?
Should I "force" the plan that works well now?
How do I make sure this doesn't happen to another query/plan?
Is this a symptom of a larger problem?

Indexes I just added:

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Full query text:

https://pastebin.com/Z5szPBfu (LINQ-generated, I can/should be able to optimize columns selected, but it should be irrelevant to this problem)


texts: Should I drop a foreign key if I use a soft delete?

texts:
"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have suffered deadlock issue when application is under high concurrent workload.

BTW: My application also validates existence of parent resource if child resource has a reference(foreign key) to it when creating child resource


texts:
I have configured trigram index for fuzzy search in Postgres. I inserted 8500000 rows to this table with 4 words text.

CREATE DATABASE people;
\connect people;
CREATE TABLE person
(
    id   bigserial NOT NULL,
    name text      NULL
);

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION pg_trgm;

CREATE INDEX name_trigram_idx ON person USING gin (name gin_trgm_ops);

INSERT INTO person (name)
SELECT CONCAT(g.name, ' ', g.name, ' ', g.name, ' ', (SELECT array_to_string(
                                                                     array(select substr(
                                                                                'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
                                                                                ((random() * (36 - 1) + 1)::integer),
                                                                                1)
                                                                           from generate_series(1, 10)), '')))
FROM generate_series(1, 8500000) AS g (name);

Now I do a fuzzy search:

SELECT * FROM person WHERE name % '1200000'

And have results:

   id    |                name
---------+------------------------------------
   12000 | 12000 12000 12000 CLXBHQVEKC
  120000 | 120000 120000 120000 CLXBHQVEKC
 1200000 | 1200000 1200000 1200000 CLXBHQVEKC
 1202000 | 1202000 1202000 1202000 CLXBHQVEKC
 1212000 | 1212000 1212000 1212000 CLXBHQVEKC
(5 rows)

It takes about 1 second. That's good but I would like to have it faster (300ms).

What's are possible ways for optimization the speed?
Here is explain-analyze request:

EXPLAIN (ANALYZE) SELECT * FROM person WHERE name % '1200000'
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on person  (cost=201.87..24804.13 rows=8499 width=42) (actual time=45.135..972.583 rows=5 loops=1)
   Recheck Cond: (name % '1200000'::text)
   Rows Removed by Index Recheck: 147980
   Heap Blocks: exact=25286
   ->  Bitmap Index Scan on name_trigram_idx  (cost=0.00..199.74 rows=8499 width=0) (actual time=40.833..40.840 rows=147985 loops=1)
         Index Cond: (name % '1200000'::text)
 Planning Time: 1.538 ms
 Execution Time: 973.046 ms
(8 rows)

And a link to my test repo.

Generally speaking, I would like to have a typo-proof search for a text with several words which has about 9 million rows and it shouldn't respond longer than 300ms.

P.S. for this experiment I'm running Postgres in docker with 6 CPUs, 8GB memory and 1GB of swap.


texts: Oracle Enterprise Manager Cloud Control 13.4 - Package Deployment Stuck

texts:
We upgraded our Cloud Control from 13.3 to 13.4.
I passed as well this step with "Simple Installation" choice, using SYSMAN.
I have a user named as CCUSER1.
When I want to skip this page with "Simple Installation" choice, using CCUSER1.

It stucks on like this:

Sometimes it finish with "Succeeded" state but when I refresh the page or to display "SQL Monitoring" page, the same page (Package Deployment) appears.
How can I solve this?
Regards,


texts: MySQL: Why is the `length` (byte length) of a string in a `varchar` field shorter than the actual string?

texts:
I'm sure there's a really obvious answer but:

Why is the length (byte length) of a string in a varchar field shorter than the actual string?

e.g.

CREATE TABLE `test1` (
  `testdata` varchar(4) DEFAULT NULL
) CHARSET=latin1;

insert into test1 (`testdata`) VALUES('ÂÂÂÂ');

select
    testdata,
    length(testdata),
    char_length(testdata),
    length('ÂÂÂÂ'),
    char_length('ÂÂÂÂ')
from test1

**result:**
testdata: ÂÂÂÂ
length: 4
char_length: 4
length: 8
char_length: 4


texts: Per Team - Count Number of Losses in Last N Results

texts:
I have the following tables:

teams:
-----------------------
| team_id | team_name |
-----------------------

team_results:
----------------------------------------------------------
| team_results_id | team_id | result_date | won (1 OR 0) |
----------------------------------------------------------

For each team, I want to look at the last N results and count the number of losses.

So far, I have the following query:

SELECT *, COUNT(*)
FROM
(
    SELECT teams.team_id, won
    FROM teams
    INNER JOIN
    (
        SELECT team_id, won
        FROM team_results
        ORDER BY result_date DESC
    ) AS team_results ON teams.team_id = team_results.team_id
) AS a
WHERE won = 0
GROUP BY team_id;

This counts the total number of losses per team, but I've been unable to limit the count to the last N results for each team. I've tried placing a limit in the nested subquery without success.


texts:
I have a table defined as:

CREATE TABLE [dbo].[IPS_INTEGERS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [network_start_integer] [bigint] NULL,
    [network_last_integer] [bigint] NULL,
    [connection_type] [varchar](50) NULL
)

It has about 360k registers and querying it repeatedly many times by second.
I need the following query optimized; currently it completes in 8 ms and I'd like to improve that.

SELECT connection_type
FROM   IPS_INTEGERS
WHERE  network_start_integer <= @IP
AND    network_last_integer >= @IP

The @IP value has the C# System.Int64 data type.

I have tried different indexing approaches but none giving the the expected optimization.

Currently using the following index:

CREATE NONCLUSTERED INDEX [IPS_include_connection] ON [dbo].[IPS_INTEGERS]
(
    [network_start_integer] ASC,
    [network_last_integer] ASC
) INCLUDE ([connection_type])

Here's the link to the estimated query plan.

Any suggestions?


texts: How to organize a development environment using Oracle XE 11g?

texts:
I started developing small applications using Oracle XE 10g/11g using APEX. They are intented to be run on desktop computers that acts as a server in small offices. Oracle XE is well suited for those scenarios.

The problem I am facing is in my developing environment where I am not sure how to set up. I have each different system organized as a set of schemas in Oracle XE and that is working fairly good. My problem is the following:

I need to make some serious customization to one of those systems for a new client, but I need to keep a copy of the system as it is right now (support/maintenance) because I have other clients which are using it.

I would like some advice in how to organize this development environment because I'll be facing this problem more than once.

Thanks


texts:
My query is below:
WITH CTE AS(
         SELECT Id,
                SaleTime
         FROM   tbl_sales_log
         WHERE  saletime > ’2019 -06 -01 00:00:00’
                AND saletime < ’2019 -06 -30 23:59:59’
                AND centerid IN (708, 805, 432, 403)
                AND itemid IN (3, 6)
         ORDER BY
                saletime DESC limit 1000 offset 0
     )

SELECT Id,
       col1,
       col2,
       col3,
       col4,
       col5,
       col6,
       col7,
       col8,
       saletime
FROM   my_table_6 R
WHERE  R.id = ANY(
           SELECT Id
           FROM   CTE limit 1000
       )
       AND R.saletime > ’2019 -06 -01 00:00:00’
       AND R.saletime < ’2019 -06 -30 23:59:59’
ORDER BY
       R.saletime DESC limit 1000;

Explain (analyze,buffers) is below:

my_table_6 has 35 million records. It has a btree-index for the column Id.
When I select just 1000 records it takes almost 30 seconds.
How can I optimize it?
Note: PostgreSQL 12 server is running on Windows.
CTE query executed within 500 millisecond, but querying from my_table_6 takes more time. I have checked this many times.


texts: Filegroup with multiple files: odd behaviour when moving stuff in

texts:
I am currently migrating databases from SQL Server 2008 (SP4) to SQL Server 2017 (CU3). A significant change is that all filegroups have two data files after migration. To accomplish this task I restore a backup, add the new filegroup with two equally sized files as well as the same autogrowth settings and transfer the data by using the following syntax:

CREATE UNIQUE CLUSTERED INDEX <PK of the table> ..... WITH (DROP_EXISTING = ON ,...) ON <new Filegroup>

Unfortunately I also have to move some LOBs then things get a little more complicated:

Add a partition scheme and partition function (basically with the same target)
CREATE the clustered index (WITH DROP_EXISTING=ON) on the new partition scheme
CREATE the clustered index (WITH DROP_EXISTING=ON) on the new filegroup
DROP the partition scheme and function

This technique is described by Kimberly Tripp here and goes back to Brad Hoff.

Thank you for staying tuned and now to my question:

How much free space is needed in a filegroup by rebuilding an index like this?

I'll give you an example:

I have one LOB table which is 220GB in size (according to total pages in sys.allocation_units divided by 128 divided by 1024).
Presized the two files in my new (empty) filegroup (to host just that table) by 220/2 =  110 GB each with filegrowth = 0.
Tried to transfer the table using the technique described above but got error message

  Could not allocate space for object 'xyz'.'xyz_pk' in database 'abc' because the 'def' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Increased the filesize to 112 GB each...tried again and got the same error message
Finally turned on autogrowth for the files and the process finished successfully

however each file then was 220 GB in size leaving 50% of free space within each file.

Here is an output of the suggested diagnostic query

The file size adds up to 227,22 GB.

Up to now I do not know any other remedy as to perform DBCC SHRINKFILE to get rid of the ridiculous high amount of free space. However that's not something I am particulary proud about...it takes ages potentially leaves corruption etc.etc.

Can you help me understanding why SQL Server allocates so much free space while afterwards happily filling my two files proportionally?

I'll try to prepare a demo afterwards...sorry but I am short on time now and perhaps some of you experts do already know the reason for this.

Thanks in advance for your help

Martin


texts: Backing up SQL Server 2008 database in different recovery model

texts:
We currently have a production environment with 10 SQL Server 2008 databases in Full recovery mode (for obvious reasons). We back these databases up fully at a frequent interval, but due to the Full recovery mode this results in a larger than advertised file size. For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.

We also have a dev environment that we would like to restore the production data to at a weekly interval. However, the dev environments have much less space and cannot handle the full 60+GB backup - they need a Simple recovery mode backup.

Right now, we have a manual process of taking the Full recovery production backup, restoring it on a large enough server, setting the recovery model to Simple, shrinking the database and log, and backing up in Simple mode. Then we restore this new Simple backup to the dev environment. Obviously this is not a very easy solution to automate, and just feels wrong.

I'd imagine there is an easier way to automate this same process and backup the production databases into Simple recovery mode without adding more space to the dev environments.

Any help would be appreciated.


texts: How do I save functions to individual files in PostgreSQL?

texts:
I maintain a legacy application that uses a PostgreSQL database.

The application is heavily dependent on stored procedures (aka functions).

I want to save these functions to files named after the function name so I can then use a VCS (version control system).

I know that I can save the code with the ALTER FUNCTION using PgAdmin but this only allows me to save one function at a time.

I am looking for a way to save all the functions automatically. Is there any way to script this task?


texts:
I'm working with SQL Server 2012 Enterprise Edition SP2 on a Windows 7 32bits.

I have this code inside a stored procedure.

Begin try
    -- EXEC CLR Stored Procedure
    exec dbo.MyStoredProcedureCLR @baseUrl, @orderNumber
end try
begin catch
    Declare @message nvarchar(max);

    set @message = ERROR_MESSAGE()
    EXEC master..xp_logevent 60000, @message, informational
end catch

MyStoredProcedureCLR is a SQL CLR Stored Procedure that make a PUT to a RESTful web service.

Sometimes, I get a 404 Not Found exception, and I want to catch it and log it. This is the message I get:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNewCodesICODECLR":
System.Net.WebException: Error en el servidor remoto: (404) No se encontró.
System.Net.WebException:
   en System.Net.HttpWebRequest.GetResponse()
   en StoredProcedures.GetNewCodesICODECLR(SqlString baseUrl, SqlString orderNumber, SqlString lineCode, Int64 quantity, Int64 codesPrinted, Int64 codesCleared)

But, when I get that exception, I get another exception in xp_logevent:

'Error executing extended stored procedure: Invalid Parameter Type'

How can I know if ERROR_MESSAGE() is a valid parameter? OR what's happening?

By the way, I'm running a that SQL CLR Stored Procedure inside a Service Broker Queue.


texts: How to reconstruct functional dependencies from 3NF decomposition?

texts:
I have the following decomposition for the relation R(A,B,C,D):

R1 = (A,B),
R2 = (B,C),
R3 = (A,D).

It is given that AD is the only candidate key for the original relation and that R is not in BCNF. The decomposition above preserves the functional dependencies as well as is lossless-join decomposition (that is R1 natural join R2 natural join  R3 = R).

What could be a set of functional dependencies for the decomposition?

3NF is always lossless decomposition as well as one that preserves functional dependencies. Therefore, I think the decomposition given is in 3NF. If we were to reverse engineer 3NF decomposition algorithm we could say that the original set of functional dependencies was {a→b, c→b, a→d} indeed R3 contains all of the attributes of the candidate key. But the problem is that AD is not the candidate key of the above set. In order to for AD to be the only candidate key I'd have to either add another dependency or add more attributes to the existing dependencies. In either case the ensuing 3NF decomposition would not result in one like in the OP.

Is there something I'm missing to find an appropriate dependency set?


texts: Should every job, SSIS project, SSRS have its own sql login

texts:
Apologies for asking an opinion type question, but I'd like to know what is best practice.

I have a bunch of sql servers and every job as well as every connection manager for every SSRS report & SSIS project all use one login, sysadmin of course. I see it as a problem for two reasons:

I would like every SSRS, SSIS etc to use their own logins with only the required permissions.
I also would like to know immediately what's causing blocking etc., rather
than seeing one sysadmin login every time and then checking to see
what's running the offending query.

My fellow DBA believes this is madness due to the number of logins required (hundreds per server), suggesting one login per database with the db_datareader role would be more appropriate. Can anyone share links to articles or offer advice?

Thanks


texts: Find ClientHost using ClientIP - SQL Server 2014

texts:
Recently we started getting alerts as below :

EventID 18456 - Login failed for user 'abc'. Reason: Could not find a login matching the name provided. [CLIENT: xx.xx.xx.xxx]
EventID 17828 -
The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xx.xx.xx.xxx]

Below are the steps I did so far to find the Hostname using the Client IP in above log (xx.xx.xx.xxx) :

nststat -a xx.xx.xx.xxx

This does not give me any information about Client Host name and says HostName not found .

ping -a xx.xx.xx.xxx

  This gives me Reply message for IP mentioned .

nslookup xx.xx.xx.xxx

  Domain cant find IP.

What else should I do to find out what is wrong with failed login and whose login it is and from which machine ?


texts:
Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.

Let's overview simple example.

Assuming there two tables:

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `sub_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `child` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `parent_id` INT NOT NULL,
  PRIMARY KEY (`id`));

With the following content:

INSERT INTO `items` (`parent`) VALUES ('Main item1');
INSERT INTO `items` (`parent`) VALUES ('Main item2');
INSERT INTO `items` (`parent`) VALUES ('Main item3');

INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item11', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item12', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item13', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item21', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item22', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item23', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item24', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item31', '3');

Now let's create query something like this:

SELECT i.*, si.child, si.parent_id FROM items as i
LEFT JOIN sub_items as si
ON i.id = si.parent_id;

The result will be:

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'1', 'Main item1', 'Child Item13', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'2', 'Main item2', 'Child Item23', '2'
'2', 'Main item2', 'Child Item24', '2'
'3', 'Main item3', 'Child Item31', '3'

Now the challenge is to limit join table rows (in this example limit is 2 rows):

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'3', 'Main item3', 'Child Item31', '3'

If we try to use query bellow to achieve desired outcome MySQL will throw error:

SELECT i.*, si.child, si.parent_id FROM items as i
LEFT JOIN (SELECT * FROM sub_items WHERE parent_id = i.id LIMIT 2) as si
ON i.id = si.parent_id;

Then what is alternative approach to get result described above with 2 (or more) limit rows for each left join subquery?


texts: get max value occurred within every 10 minute of a day

texts:
I have a table named pointdb containing columns:

SOLAR_ACTIVE_POWER_G_VAL0
LOAD_ACTIVE_POWER_G_VAL0
timestamp

It contains every second wise data. I wanted to display max of SOLAR_ACTIVE_POWER_G_VAL0 and max of LOAD_ACTIVE_POWER_G_VAL0 occurring within every 10 minute interval. For that I have written a query as follows:

Collapse | Copy Code

SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,max(LOAD_ACTIVE_POWER_G_VAL0) AS Load, (DATEPART(MINUTE, [timestamp]) % 10) AS [Minutes]
FROM pointdb WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014')
GROUP BY (DATEPART(MINUTE, [timestamp]) % 10)

But it is not giving right output.


texts: Why `update` and `delete` acquire EXCLUSIVE lock rather than ROW EXCLUSIVE lock in Greenplum?

texts: As we known, insert,update and delete commands automatically acquire ROW EXCLUSIVE lock in PostgreSQL. As a fork of PostgreSQL 8.2, why in Greenplum update and delete commands automatically acquire EXCLUSIVE lock, what's the reason for Greenplum doing so?


texts: Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?

texts:
I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter.
A simplified version would look something like this:
CREATE OR REPLACE FUNCTION test(order_column text)
    RETURNS TABLE(thing1 bigint,thing2 text, thing3 timestamp without time zone)
    LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
RETURN QUERY
    SELECT thing1, thing2::text, thing3 FROM some_table
    ORDER BY
        CASE WHEN order_column='id' THEN thing1
        ELSE thing3
        END
    DESC;

END;
$BODY$;

Unfortunately, thing1 is a bigint and thing3 is a timestamp, and when I try to run the function I get an error saying bigint and timestamp types can't be matched, which I interpret as saying that the types returned from the case need to be the same (or at least compatible). I can't cast them both to text, because the range of values don't sort correctly then.
I've tried returning the column numbers instead of the column names - this at least executes, but it ignores the column order (in the function or just executing as a simple statement). For example,
SELECT * FROM some_table ORDER BY 1;

works correctly but
SELECT * FROM some_table ORDER BY CASE WHEN TRUE THEN 1 ELSE 2 END;

does not order by column 1
My work-around would be to do
if column_order='first' then
  (masses of complex stuff)
  SELECT ... ORDER BY thing1
else
  (masses of complex stuff, duplicated)
  SELECT ... ORDER BY thing3
end if;

but that's horrible, and I'm really hoping there's some other way around this, and that I'm currently missing something.
Is there any way to do what I'm trying to do?


texts:
Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank.

Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file 2. File 2 is in the same file group that file1 was in. Result 2555 error:

  Cannot move all contents of file "file2" to other places to complete the emptyfile operation.

Restarted SQL, executed script from point 2, with same result.

I am ultimately trying to remove the file group that the file is a member of and the file group has only one remaining file in it. The file is has current allocated space of 1 MB and available free space of .81 MB


texts:
I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard.  I set everything up with IMMEDIATE start, but then the job first said it started executing and then said it is NOT RUNNING.  The job's log file was not created, so I think it stopped upon starting, perhaps due to a bad parameter.  How can I just delete this job altogether, so that I can start the wizard again without creating problems with a duplicate job?

I typed select * from SYS.DBA_DATAPUMP_JOBS; and found the job as the only one listed:  JOB_NAME='IMPORT_JOB_SQLDEV_46'

And I google searched on the topic, but the only articles were many years old and did not give instructions which seem to work in modern SQL Developer.  For example, I tried the steps in this link, but Ctrl-C does not seem to produce anything.


texts: insertion creates duplicate with same data

texts:
I am using MySQL with PHP. I fetch data from a third party using SOAP.

I have an issue with duplicate insertion of data. This occurs once or twice a day.

Say there are 10 records ready to enter into the system. I create a loop to insert the records one by one. Each has a reference number which should be unique. In my case it is not. The code looks like this:

for($i=0;$i<count($Result['return']);$i++)
{

//Here function is working and this function check the duplication

//here in function record are inserted
//In this function a query exists that checks the existing maximum record
//then associates with a new one
//Query that fetches the max ref Number
 SELECT MAX(distNumber)+1 as max_ref FROM orders
//then insert statement of an orders

}

The system inserts duplicate records and distNumber is the same for all duplicated orders.

My overall issue is my MAX query worked one time and other insertion query runs multiple times. That is why the system inserted the same order twice and distNumber is the same for both.

I want to save my system from duplication of records at run time.


texts: Postgresql numeric and decimal is automatically rounding off

texts:
CREATE TABLE IF NOT EXISTS ttable (
    tcol decimal(9,7)
);

insert into ttable(tcol) values(17.4604786);

the value is getting stored as 17.46

Happens the same if I use decimal/numeric type

I am using

PostgreSQL 11.2 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit.

Tool
SQL Workbench/J Build 124 (2018-08-20 22:43)
Java version: 1.8.0_211 (64 bit).

Connection info:
Product Name: PostgreSQL
Product Version: 11.2
Product Info: 11.2
Driver Name: PostgreSQL JDBC Driver
Driver Class: org.postgresql.Driver
Driver Version: 42.2.6
Isolation Level: READ COMMITTED
Workbench DBID: postgresql


texts: Deleted rows but database size is still large

texts:
I am using SQL Server 2008 Express which has maximum database size limit of 10 GB. Every month I hit the limit and the application can't store more data.

To overcome this problem we run a job every month which takes backup of the database for last 30 days and deletes the data for those 30 days from the respective tables. After delete also the size remains high.

Shrinking the database, truncating the tables, table clean up, Shrink the database and rebuild the index, delete the tables and recreate them. Every time shrinking the database is not good so kept as a last option.

Apart from this is there anything else that I can try?


texts: initialize specified but the data directory has files in it. Aborting when start MySQL in kubernetes

texts:
Now I am using this command to deploy a HA mysql cluster:
helm install hades-mysql-ha incubator/mysqlha -n hades-pro

when the pod started, shows this error:
2021-05-20 05:54:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian10 started.
2021-05-20 05:54:19+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-05-20 05:54:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian10 started.
2021-05-20 05:54:19+00:00 [Note] [Entrypoint]: Initializing database files
2021-05-20T05:54:19.866813Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-20T05:54:19.868861Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-05-20T05:54:19.868896Z 0 [ERROR] Aborting

I already clean the mount folder in the NAS and reinstall MySQL HA, and recreated PV, why still give this error tips? what should I do to fix it?


texts: Locking in Postgres for UPDATE / INSERT combination

texts:
I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.

The user needs to be able to redeem a coupon, which will insert a row into the log table and mark the coupon as used (by updating the used column to true).

Naturally, there's an obvious race condition/security issue here.

I've done similar things in the past in the world of mySQL. In that world, I'd lock both tables globally, do the logic safe in the knowledge that this could only happen once at a time, and then unlock the tables once I was done.

Is there a better way in Postgres to do this? In particular, I'm concerned that the lock is global, but doesn't have to be — I really only need to make sure no one else is trying to enter that particular code, so perhaps some row-level locking would work?


texts: How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

texts:
Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in copying the SPs to the Client DB.
Problem: All the Business Logic gets copied at the Client Side and results in proprietary issues.
Solutions Tried Earlier:

CREATE PROCEDURE Proc_Name WITH ENCRYPTION

This method results in encrypted and non-maintainable SP code. We cannot judge which version of code is running at the client side, hence cannot debug. Version control cannot be applied. Moreover, client cannot perform DB replication since encrypted SPs do not get replicated.

CREATE SYNONYM SchemaName.Proc_Name FOR LinkedServerDB.SchemaName.Proc_Name

This allows for creation of references (synonyms) at Client_DB which access the actual SPs residing on a Remote_Linked_Server_DB. In each SP call, entire data is accessed from Client_DB and transmitted to Remote_Linked_Server_DB where the calculations are done and the result is sent back. This results in acute performance issues. Also requires 24x7 internet connectivity to the remote linked server.

Requirement (Edited):

We are looking for a solution whereby the stored procedure code could be compiled (secured) and separated from the Client Database.
The compiled stored procedure code should be available at the client-end so that client does not require 24x7 connection to a remote location for accessing the stored procedures as in the case of using SYNONYMS.
I have a slight hint of using CLR-SQL in Visual Studio Database Projects, but I guess that would require re-writing all the stored procedures (which is nearly impossible since we have more than 100 SPs).


texts: Is it possible to use dates in OpenLDAP's ACL entries?  I'm looking to do something like to * by this/termDate < NOW none by * break


texts: Not getting distinct results using case query

texts:
This is the query I am running. I am getting the correct results except they come in on a different line for each corpstorenum,
so i am getting :

Corpstorenum,BuffetYN, deliverybeeryn, dineinbeeryn,saladbar
store1,n,n,n,y
store1,n,n,y,n
store1,n,y,n,n
store1,n,n,n,n

instead of:

store1 n,y,y,y

This is my code:

select distinct corpstorenum,
case when storenumbers.storenumber is not null and concept ='buffetAY' then 'Y' else 'n' end as BuffetYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and delivery#>0 then 'Y' else 'n' end as DeliveryBeerYN ,
    case when storenumbers.storenumber is not null and concept ='Beer' and dinein#>0 then 'Y' else 'n' end as DineInBeerYN,
    case when storenumbers.storenumber is not null and menusize='WithMeal' and dinein#>0 then 'Y'
else 'n' end as SaladBar
from storenumbers
    left join prod5219 on storenumbers.storenumber = [Prod5219].store


texts:
Im trying to drop some databases on a postgres 9.6 server, but when I try Im told I am unable to.

I was under the impression that the postgres user was like a superuser.

su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.6.8)
Type "help" for help.

postgres=> DROP DATABASE d;
ERROR:  must be owner of database d
postgres=>


texts: Can I pass in a frozen time as a setting into postgresql and not have clock drift?

texts:
I would like to freeze time on a connection level across multiple transactions. Is there a conventional way of achieving this similar to

SET search_path = 'foo';

Rather than me passing in the time and binding it on each statement myself? Ideally I would want to be able to use NOW() and write conventional SQL, but freeze that time across multiple transactions.

The reason for this is because I want to avoid clock drift on a single web request.


texts: Am I breaking something if I set an autoincrement PK to 0?

texts:
I have this table
CREATE TABLE `test_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;

Insert some records...
INSERT INTO `test_table` (`name`) VALUES ('a');
INSERT INTO `test_table` (`name`) VALUES ('b');
INSERT INTO `test_table` (`name`) VALUES ('c');

So I get records 1, 2 and 3 with values a, b and c respectively.
Now, I need to have a record with 0 as id and empty string as name. So I did this
INSERT INTO `test_table` (`id`, `name`) VALUES ('0', '');
UPDATE `test_table` SET `id`='0' WHERE `id`='4';
ALTER TABLE `test_table` AUTO_INCREMENT = 4 ;

Do I have a guarantee that everything will continue to work properly? Table references, auto increment sequence, etc...


texts: How do I simultaneously update unique keys in PostgreSQL?

texts:
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE
);

I have the data
 id | name | ordinal
----+------+---------
  1 | A    |       1
  2 | B    |       2
  3 | C    |       3

I would like to update it to
 id | name | ordinal
----+------+---------
  1 | A    |       3
  2 | B    |       2
  3 | C    |       1

Touching records as little as possible (i.e. don't rewrite the entire record set, don't kick off unnecessary triggers), what's the generally applicable approach to updating ordinal to be the target values?
A vanilla update just gives me constraint violations, even if it happens in a single statement.
And dropping and recreating the unique constraint is expensive and poor concurrency.
This seems like a common enough problem that there ought to be a good way to do this, that I just can't think of.


texts: MongoDB: Is it possible to search for two fields whose array sizes don't match?

texts:
I have a collection that looks something like the following (I have simplified it a lot). I need to query any record that doesn't have the same number of customersAccepted entries as customersInvited.

/* 1 */
{
    "_id" : ObjectId("5d7b8ab0f1e55a2e4b6ccfa2"),
    "title" : "Service AT19",
    "customersInvited" : [
        429409,
        429410,
        900
    ],
    "customersAccepted" : [
        429409,
        429410,
        900
    ],
    "groupId" : null,
    "deleted" : true,
    "deletedDate" : ISODate("2019-10-12T05:59:08.135Z"),
}

Is this possible with the $size operator? I'm familiar with it when looking for a fixed number, so my Plan B will be to add two separate fields called numAccepted and numInvited, then update that when the record is modified and do something like:

db.collection.find( { "numInvited": { $ne: "numAccepted" } } );

I'd really rather not maintain additional fields I don't need if it is possible to compare array size directly though. Any advice you can give on this would be greatly appreciated.


texts: Why this result in MySQL is different than Excel or Calc?

texts:
I've been very confused because this calculation gives different results.

Select (10/110*48576000)

The result is:  4415999.9558

But if I do it in Excel using = 10/110*48576000 the result is 4416000. Even if you count it in Calc, 10 divide by 110 and multiply by 48576000, the result is 4416000.

Can someone explain me this weird behaviour?

Note I'm using Mysql 5.5.46 in Windows Server 2008.


texts:
We have a database that needs 40% free in the log files at all times or we get an alert.
I cannot find a way to set growth when a limit is reached.

Of course we can set autogrowth to a certain limit but then we would have to go into it weekly to add 40% based on last growth.

Any ideas?
SQL 2012


texts: How to optimise PostgreSQL planning time

texts:
I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2

select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);

that is slower than I like for where it is used in my system

running explain analyse yields this

explain analyse select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.593 ms
 Execution time: 0.015 ms
(3 rows)

where I can see that the execution is dominated by the query planning time.

I have failed to find a way to reduce this (was trying to use a plpgsql function in the hopes it would cache the query plan) so what is a good way to optimise this?

I have found out that the slow part is the call to ST_Transform by running the following

explain analyse select ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633);
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.074 ms
 Execution time: 0.018 ms
(3 rows)

But have failed to get the optimiser to treat ST_Transform any better.


texts: Can I use result of one query inside other query in Cassandra

texts: I need to delete all rows with given clustering column value. I don't have partition key so I don't have complete primary key. To delete record from Cassandra table we need to provide specific partition key. Therefore first I need to select records to delete using this clustering column value and then deleting that record with the help of associated partition key values.


texts: Is fragmention limited to indexes in sql server?

texts:
As far as I can see with research the only thing that fragments is indexes no other objects in SQL can fragment.

I have to keep track of fragmentation within multiple databases, would it be correct to understand that I only check index fragmentation?
Can any other object fragment?
Does it need to be checked?


texts: No parallelism in Express Edition of SQL Server

texts:
I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property.
Is it that Express Edition never goes parallel? I cannot find anything about this in Microsoft documentation.


texts: Cassandra slow read performance after migrating to new AWS instance class

texts:
Cluster of 3 cassandra nodes as a dev environment. Used to run in AWS on i3.2xlarge; migrated as a test to i3en.2xlarge to evaluate for later migrating production cluster to.

When running cassandra-stress benchmarks for large row counts (e.g. 100M and 1B), I am seeing an increase in write performance of 80-90%, but a decrease in read performance of around 80%. Minimal or no external traffic to the cluster during these tests.

I have checked the keyspace created by cassandra-stress for excessive SSTables, and there doesn't seem to be any problem there. Running iperf and I/O benchmarks on the new i3en nodes shows a 30-120% increase in performance over the i3 depending on the benchmark.