texts: Oracle: What should be better, alter table CACHE or INDEXES?

texts:
I have a database (700 tables / 400GB) running on a VM with 64GB of RAM on a physical server with 384GB of RAM, the database has some indexes on some table fields, but it hasn't a good performance.

That physical server was reserved to run databases only, so I could allocate as memory as necessary on it to get a good performance.

I've been reading about tuning and realized that I could put all non-securefile (blob) tables on memory using the CACHE or KEEP POOL parameter, or create a lot of indexes.

What should I do to make queries run faster on it? CACHE or INDEXES?


texts: I want SQL query for select multiple columns into one

texts:
I have one single table and in that table there are three columns company1, company2, company3.
Below is my table data :
Company1    Company2    Company3
ABC
Test1       Test3   Test5
Test2       Test4   Test6
testing     testing2

And I want to combine these columns into single column with serial number like below :
SrNo    CompanyName
1       ABC
2       Test1
3       Test2
4       testing
5       Test3
6       Test4
7       testing2
8       Test5
9       Test6


texts: Select returns users locked or a default value for null rows

texts:
I'm don't have much knowlegde on Oracle database and used some questions from here to obtain this query:

select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then

(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code'))

else 'none'

end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp;

With this query, I want to retrieve users locked on which date and what causes the lock, if there's no users, returns only 'none' instead of "no rows selected". For users locked it works, but the else statement don't. Can someone explain me why? I need this to fix my zabbix database monitoring with ODBC.


texts: Project a time range in multiple rows with Postgres


texts: Execute a stored procedure via a Linked Server from SQL Server 2008 R2 to SQL Server 2014

texts:
We are in the planning stages for an upgrade from SQL Server 2008 R2 to SQL Server 2014.

Our system executes stored procedures against different databases on different servers.

If we upgrade one server to SQL Server 2014, but leave the remaining servers on SQL Server 2008 R2, would we lose that ability?


texts:
We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept them alive, but they received no WAL files for a little over two days. The backlog on the two primaries approached 3500 before we got connectivity back again.

When I look at the logs in ~postgres/data/pg_log on the two secondaries, I can see in the current log that the first WAL segment to be processed when we got back online is the numerically consecutive very next one after the last one that made it into the the log file from the day of the start of the outage. It looks like we didn't miss anything, and the secondaries were caught up in less that a day.

But I'd like to verify this. What would most effectively assuage my paranoia would be to break connectivity again so the primaries would begin to buffer up WAL files, promote the secondary machines to primary in isolation, run enough strictly read-only  queries to satisfy myself of the integrity of our backup data, and then put the secondaries back in continuous recovery mode and finally restore connectivity.

Is that doable? Or would the promotion and processing of queries, even read-only ones, alter the secondary database enough that it could not resume continuous recovery afterwards?

I realize that this would not be an issue with streaming replication, but 8.4 does not have that capability.


texts: Finding optimal indices, different query competing indices

texts:
My schema:

create TABLE "logs" (
    "id" serial not null default nextval('logs_id_seq'::regclass),
    "request" varchar(1024),
    "token" varchar(512) default NULL,
    "was_batch_request" bool not null default false,
    "created" timestamp,
    PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "idx_logs_token_created" ON "logs" ("token", "created");
CREATE UNIQUE INDEX "idx_logs_created" ON "logs" ("created");

I've around 5.000.000 entries with varying token and I've two queries I regularly perform against this table. Think of token usually having 200 to 500 chars. There are about 600 distinct token in this table.

Query 1:

SELECT
    TO_CHAR("logs".created, 'YYYYMMDDHH24') AS date
  , COUNT(*)                                AS num
FROM "public"."logs" AS "logs"
WHERE "logs"."created" BETWEEN NOW() - INTERVAL '24 hour' AND NOW()
GROUP BY TO_CHAR("logs"."created", 'YYYYMMDDHH24')
ORDER BY "date" DESC
LIMIT 24

For this query I've an index on created.

Query 2

SELECT count(*)
FROM logs
WHERE token = 'token_600_chars_long'
      AND
      created >= NOW() - INTERVAL '1 day';

For this query I created an index on token,created.

Some observations:

Query 1 takes around 2s
Query 2 takes around 1s

The issue with Query 1 .. unknown. It uses the created index; if the created index does not exist, it takes as long as 5s.

The issue I see with Query 2: it does not use the token,created index but the created index.

However, when I do the following changes, Query 2 uses the token,created index:

I drop created index (but this slows Query 1 down to 5s)
I change the interval to e.g. 4 days and then it starts using the token,created index and finishes in 100-200ms.

Explain for Query 1:

QUERY PLAN
Limit  (cost=75793.01..75793.07 rows=24 width=8) (actual time=2077.100..2077.103 rows=23 loops=1)
  ->  Sort  (cost=75793.01..75814.66 rows=8662 width=8) (actual time=2077.098..2077.099 rows=23 loops=1)
        Sort Key: (to_char(created, 'YYYYMMDDHH24'::text))
        Sort Method: quicksort  Memory: 26kB
        ->  HashAggregate  (cost=75442.85..75551.12 rows=8662 width=8) (actual time=2076.830..2076.979 rows=23 loops=1)
              Group Key: to_char(created, 'YYYYMMDDHH24'::text)
              ->  Index Only Scan using idx_logs_created on logs ""logs""  (cost=0.44..72326.16 rows=623337 width=8) (actual time=0.244..1804.994 rows=876114 loops=1)
                    Index Cond: ((created >= (now() - '24:00:00'::interval)) AND (created <= now()))
                    Heap Fetches: 876143
Planning time: 0.968 ms
Execution time: 2077.309 ms

Explain for Query 2:

QUERY PLAN
Aggregate  (cost=70856.83..70856.84 rows=1 width=0) (actual time=640.327..640.327 rows=1 loops=1)
  Output: count(*)
  ->  Index Scan using idx_logs_created on public.logs  (cost=0.44..70795.30 rows=24610 width=0) (actual time=0.096..637.190 rows=30863 loops=1)
        Output: id, request, token, was_batch_request, created
        Index Cond: (logs.created >= (now() - '1 day'::interval))
        Filter: ((logs.token)::text = 'DazToken'::text)
        Rows Removed by Filter: 843711
Planning time: 0.381 ms
Execution time: 640.417 ms

In MySQL I could force certain queries to use specific, to my knowledge PostgreSQL does not provide such a think.

Is it possible to speed up the queries with the current schema?


texts: Transactional Replication from SQL Server to Azure SQL

texts:
I'm attempting to setup transactional replication between an on-premises SQL Server and an Azure SQL database. Unfortunately, it looks like Azure SQL doesn't support SQL Server Replication, and Azure Data Sync seems like it's been abandoned (and even if not, it chokes on databases with over 200 tables).

What are some alternatives to setup continuous transactional replication between an on-premises SQL Server and an Azure SQL database? My goal is to only replicate a handful of tables, and I'd like it to happen in near-real-time (as opposed to on a schedule).


texts: On Elasticsearch, what is the required response for a directory URL of a HTTP URL repository?

texts:
The official document of Elasticsearch says we can use URL as a setting of Read-only repository. The document also says several protocols, including HTTP, is supported for the URL. (refer: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-snapshots.html#_read_only_url_repository )

I understand that the snapshots are stored in the repository as a directory structure which contains Lucene index and some meta data.

Given above conditions, I think GET request to a directory URL of a HTTP URL repository need to return a list of files/directories inside, in a certain format, for the snapshot API to restore a snapshot. At least, I thought some sort of ls operation on the directory containing Lucene indices were needed.

Questions:

Is my understanding correct?
If so, what is the required format for the directory URL? (What is the required response for HTTP request to the directory which makes snapshot API's restore work?)


texts: Recommended Postgres settings to speed up indexing on a 100GB database?

texts:
I have just installed Postgres 9.1 on a server with 32GB of RAM and 320GB solid-state disk.

I will be using this server to serve both my site and my database (i.e. it's not just a database server). The database is 500m rows, approximately 120GB of data.

Once the data is loaded, the database needs to be optimised for SELECT queries - the data is fairly static in nature, and only changes once every three months. 99% of the queries made to the database will be reads.

I'm wondering what would be good Postgres settings to start out with. I've set:

effective_cache_size 8GB
shared_buffers 4GB

in the Postgres config file, but I haven't touched any of the other defaults.

With the settings above, I'm finding it very slow (more than an hour and still running) to create an index on one field in my table, and I want to create many of them.

Are there other settings I could look at to make index creation faster? Or can I safely increase these settings?


texts: How to script statistics in Sql Server? (using T-SQL)

texts:
there is a very similar question here:

Create SQL Script for Statistics

But that is not what I want.

I want to get back exactly what I have put in.
For example:

CREATE STATISTICS [_MM_STATS__745366020_7_1_4_5_2_3]
ON [dbo].[ProductShipTax]
([TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo])

I would like a query that would allow me to get the exact script above so that I could for example, apply it to a different database.

would this be possible?


texts: mongodb “WriteConcernFailed” on a replica set

texts:
I have a replica set of 4 members. 1 primary, 1 arbiter, 1 secondary, and another one that should be secondary, but have issues and is not connected. (in a "connecting..." state).

When the unconnected member is part of the replica set, i get errors in the other members:

from the server mongo log:

[LogicalSessionCacheRefresh] Failed to refresh session cache: WriteConcernFailed: waiting for replication timed out; Error details: { wtimeout: true }

command config.$cmd command: update { update: "system.sessions", ordered: false, allowImplicitCollectionCreation: false, writeConcern: { w: "majority", wtimeout: 15000 }, $db: "config" } numYi elds:0 reslen:782 locks:{ Global: { acquireCount: { r: 208, w: 208 } }, Database: { acquireCount: { w: 208 } }, Collection: { acquireCount: { w: 208 } } } protocol:op_msg 15030ms

the write concern settings are: w : 1.0, and timeout 0.
but, "writeConcernMajorityJournalDefault" is set to true

When i remove that member from the replica, the errors disappear.

I dont understand, the write concern is set to w:1, so how do i get a timeout?
Even if it was "majority", my secondary and primary are up, so i have 2/3 servers up, why would i get a timeout?

I understand that i get a timeout error because one of the members is down, i just not really understand why.

thanks!


texts:
I'm new to database design. This database is for an upholstery cleaning store. I have some doubts related to model billing part. This store accepts payments using money, credit/debt card, bank transfer, invoices and PayPal. So many options.
I've accessed databaseanswers and currently analyzing 3 models:

Generic Model;
Future of Payments;
Invoices.

In generic model, it fits perfectly for card payment, but not with bank transfer and PayPal. I didn't understand why table Customer_Payment_Methods has one to many relationship to Payments instead of a Payment can have (one to many) many payment methods.
Future of payments pretty much the same as before adding payment channels.
Invoice model is just for... invoices.

I noticed that a customer has an account which can have many payments. But how can I solve for money and paypal parts? Separate tables?


texts: MySQL - view select rooms not in booking range

texts:
I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.

view:

USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ROOMINFO.ID,# ROOMINFO.`Type`, ROOMINFO.Max,
    #GROUP_CONCAT(DISTINCT BOOKROOM.Ref) as Bookings,
    GROUP_CONCAT(DISTINCT BOOK.Ref) as BookRef, ##
    GROUP_CONCAT(DISTINCT BOOK.Start_Date) as StartDate,
    GROUP_CONCAT(DISTINCT BOOK.End_Date) as EndDate
FROM ROOMINFO
LEFT JOIN BOOKROOM ON ROOMINFO.ID = BOOKROOM.ID
INNER JOIN BOOK ON ROOMINFO.ID AND BOOK.Ref = BOOKROOM.Ref ##
GROUP BY ROOMINFO.ID, ROOMINFO.`Type`, ROOMINFO.Max;

A room can have multiple bookings (which don't overlap) within the current bookings logged.

The problem I'm currently having is the select query to specify a date range, I cannot wrap my head around it.

I want to get returned from the query the list of rooms which don't have a booking for the specified date range.

I'm currently trying the following 3 selects for testing:

#1
SELECT * FROM RoomAvalability;
#2
SELECT ID, `Type`, Max FROM RoomAvalability WHERE NOT StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';
#3
SELECT ID, `Type`, Max FROM RoomAvalability WHERE StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';

1 returns all rooms

2 also returns all rooms

3 returns no rooms

1 & 2 results:

ID, type max
1   2   4
2   2   4
3   2   4
4   1   2
5   1   2
6   1   2
7   1   2
8   1   2
9   1   2
10  1   2

Shouldn't number 2 be returning the 8 rooms instead of 10, as it's the negative of number 3?

number 3 provides no results meaning there isn't a booking within this range, but it should provide 2 results?

The other thing I am thinking: should I be starting from the booking and link the room instead?

structure and values

CREATE SCHEMA CATTERY2;
USE CATTERY2;
#BOOK
CREATE TABLE BOOK(
           Ref INT NOT NULL AUTO_INCREMENT,
           Start_Date DATE NOT NULL,
           End_Date DATE NOT NULL,
           PRIMARY KEY(Ref));
#ROOM
CREATE TABLE ROOMINFO(
          ID INT NOT NULL AUTO_INCREMENT,
          `Type` VARCHAR(10) NOT NULL,
          Max TINYINT NOT NULL,
          PRIMARY KEY(ID));
#BOOKROOM
CREATE TABLE BOOKROOM(
           Ref INT NOT NULL,
           ID INT NOT NULL,
           FOREIGN KEY (Ref) REFERENCES BOOK(Ref),
           FOREIGN KEY (ID) REFERENCES ROOMINFO(ID));

INSERT INTO BOOK(Start_Date, End_Date) VALUES
("2019-11-22", "2019-11-25"),
("2019-11-24", "2019-11-28"),
("2019-12-01", "2019-12-02"),
("2019-12-01", "2019-12-06"),
("2019-12-02", "2019-12-03"),
("2019-12-04", "2019-12-10"),
("2019-12-04", "2019-12-10"),
("2019-12-05", "2019-12-13"),
("2019-12-16", "2019-12-19"),
("2019-12-26", "2019-12-28"),
("2019-12-26", "2020-01-01"),
("2019-12-28", "2020-01-02"),
("2019-12-31", "2020-01-05"),
("2020-01-03", "2020-01-08"),
("2020-01-05", "2020-01-11"),
("2020-01-06", "2020-01-09"),
("2020-01-06", "2020-01-11"),
("2020-01-08", "2020-01-18"),
("2020-01-11", "2020-01-15"),
("2020-01-15", "2020-01-17"),
("2020-01-15", "2020-01-18");

INSERT INTO ROOMINFO (ID, `Type`,Max) VALUES
(1, "Family", 4),
(2, "Family", 4),
(3, "Family", 4),
(4, "Dual", 2),
(5, "Dual", 2),
(6, "Dual", 2),
(7, "Dual", 2),
(8, "Dual", 2),
(9, "Dual", 2),
(10, "Dual", 2);

INSERT INTO BOOKROOM( Ref, ID ) VALUES
(1, 4),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7),
(7, 3),
(8, 2),
(9, 1),
(10, 8),
(11, 3),
(12, 9),
(13, 2),
(14, 10),
(15, 4),
(16, 5),
(17, 6),
(18, 7),
(19, 2),
(20, 1),
(21, 10);


texts: Oracle 12c Installation in Windows 7 error INS-30131

texts:
I am trying to install Oracle 12c in a Windows 7 x64 computer and after a while I get this error:

SEVERE: [FATAL] [INS-30131] Initial setup required for the execution of installer validations failed.
   CAUSE: Failed to access the temporary location.
   ACTION: Ensure that the current user has required permissions to access the temporary location.
*ADDITIONAL INFORMATION:*
- Framework setup check failed on all the nodes
- Cause: Cause Of Problem Not Available
- Action: User Action Not Available
Summary of the failed nodes
maddy-pc
- Version of exectask could not be retrieved from node "XXXXXX"
- Cause: Cause Of Problem Not Available
- Action: User Action Not Available

Does anybody have an idea how to solve this problem?


texts: MySQL using up all RAM and SWAP in group replication settings

texts:
I am using MySQL 8.0.18 in a group replication (multi-master) infrastructure. There are currently 4 master servers connected to each other and everything seems to be working fine but over the course of around 2 weeks all mysqld process on all servers uses up all the swap. ram usage stays at 800Mb as I suspect innodb_log_buffer_size is doing its job.

Server Specs:

Debian 10
1Gb RAM
3GB SWAP
20GB ssd

Mysql.cnf:

max_connections=20
innodb_log_buffer_size=800M
key_buffer_size=50M
super_read_only=off
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1
loose-group_replication_unreachable_majority_timeout=80
loose-group-replication-autorejoin-tries=2015
loose-group-replication-transaction-size-limit=0

Related OS config:

vm.swappiness=5

How do I make mysql stop using so much swap?


texts: Detect if the family record is incomplete to avoid partial deletion

texts:
MS SQL SERVER and ORACLE.

I have a table Acct table that has acct_id and parent_acct_id attribute.
Parent_acct_id on children account points to acct_id on parent account. If no parent, then parent_acct_id = null.
Family only has two levels, no grandparent/grandchildren.

Now I want to delete some accounts. It has a temp(acct_id, save) table to store the data that need to be deleted.

I want to avoid partial deletion so I need a query to detect if the temp table has the entire family or just a part of family.

For example,

I already have a query to populate 'yes' value from parent to children or from children to parent. I only need a query to mark children as 'yes' if parent doesn't exist or mark parent as 'yes' when missing children.

I only came up with two separate queries but I want to optimize to only use 1 query.

--parent in the temp table but children are not
UPDATE MAIN SET save = 'yes'
FROM  temp_table MAIN
WHERE save IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE acct.parent_acct_id= MAIN.acct_id
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.acct_id = temp.acct_id))

--children are in the temp table but parent is not
UPDATE MAIN SET SAVE =   'yes'
FROM temp_table MAIN
WHERE SAVE IS NULL AND EXISTS
    (SELECT 1
    FROM acct
    WHERE MAIN.acct_ID = acct.acct_ID
    AND acct.parent_acct_id IS NOT NULL
    AND NOT EXISTS
        (SELECT 1 FROM temp_table temp WHERE acct.parent_acct_id = temp.acct_id))

Any thoughts appreciated!


texts: Install MariaDB 10 Generic Binaries + TokuDB

texts:
I used to install MariaDB from Generic Binaries on a Debian Wheezy 64bits:

mariadb-10.0.13-linux-x86_64.tar.gz
(from downloads.mariadb.org)

I would like to use TokuDB as Storage Engine.

However, TokuDB is available in "glibc_214" generic linux package as well as .deb packages, but it is NOT available in "non glibc_214 generic linux package"...

I can't use "mariadb-10.0.13-linux-glibc_214-x86_64.tar.gz" because Debian Wheezy glibc is 2.13:

$~: ldd --version
ldd (Debian EGLIBC 2.13-38+deb7u4) 2.13

Why is TokuDB not available in standard 64bits version of Generic Binaries?

Is it safe to copy ha_tokudb.so from an debian wheezy apt install (/usr/lib/mysql/plugin/ha_tokudb.so) to a generic linux "manual" installation (in ./lib/plugin)?

If I do so, I can enable TokuDB and all seems to work (I hope so anyway...):

(none)=# INSTALL SONAME 'ha_tokudb';
Query OK, 0 rows affected (0.30 sec)
(none)=# SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
...
| TokuDB             | YES     | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
...
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)


texts: PL/SQL: best way to count elements in an array?

texts:
Given this:

DECLARE
  TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY;
  V_COUNT INTEGER;

I would like to do:

BEGIN
  -- ... some code filling the MY_ARRAY array

  -- obviously COUNT_ELEMENTS() does not exists, this is what I'm looking for :-)
  V_COUNT := COUNT_ELEMENTS(MY_ARRAY);

  DBMS_OUTPUT.PUT_LINE('My array containts ' || V_COUNT || ' elements.');
END;

Is there something better than creating a procedure doing a basic loop incrementing a counter? Maybe a PL/SQL native function already does this COUNT_ELEMENTS()?


texts:
I know this question has been asked probably a million times, so please forgive me asking it again. I am not a DBA and have very little knowledge of SQL and Derby, but need some kind of a solution as I am the only one responsible for getting this done:

Recursive query in Apache Derby 10.10
There is a table, storing basically a tree:

+----+-----------+-----+
| id | parent_id | ... |
+----------------------+
| 0  | null      | ... |
| 5  | 0         | ... |
 ......................
| N  | K         | ... |
+----+-----------+-----+

I need to make it look like this:

+----+-----------+-----+
| id | parent_id | ... |
+----------------------+
| 0  | null      | ... |
| 1  | 0         | ... |
| 15 | 1         | ... |
| 16 | 1         | ... |
| 2  | 0         | ... |
| 10 | 2         | ... |
| 11 | 2         | ... |
 ......................
| N  | K         | ... |
+----+-----------+-----+

Or a way to traverse and enumerate the tree in place.

Thank you.


texts: How do I get information about how an index set was created in Oracle?

texts: A long time ago I created a CTXCAT index with an INDEX SET using ctx_ddl.create_index_set and ctx_ddl.add_index but forgot to save the DDL anywhere and now I need to recreate this index on another server and I want to make sure the indexes are created in the same manner.  How do I get what columns are included in an index set?  I know the name of the INDEX SET, I just am trying to figure out what columns are included in it.


texts: Can the account name of 'distributor_admin' be changed?

texts:
'distributor_admin' is a SQL-auth account that is used for replication. Out of the box it is granted sysadmin and currently it looks like that can not be dialed back to db_owner (see related Does distributor_admin need sysadmin? ).

According to some sources (pg4 of PDF) distributor_admin is one of the top eight SQL usernames used in brute force attacks. It is considered a best practice to rename the 'sa' account can the 'distributor_admin'be renamed also?

I looked around and don't find anyone suggesting this alternative. I found a few posts that implied the possibility of the 'distributor_admin' being hard coded into the replication process.

There is not a lot of recent documentation about the distributor_admin account with replication.


texts: Does Oracle 11g support CREATE ASSERTION?

texts:
When I tried to write the following statement:

CREATE ASSERTION ir_collaboration
CHECK(NOT EXISTS(
  SELECT ngo, id_employee, COUNT(*)
  FROM collaboration
  GROUP BY ngo, id_employee
  HAVING COUNT(*) >= 2
  )
);

I got

  Error SQL: ORA-00901: invalid CREATE command

Do I get this error message because it is valid in SQL-92 standard but Oracle 11g does not support it?


texts: SQL Server 2012 defrag job runs interactively, but not as a job - why?

texts:
I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 (Standard 64-bit). When I execute the procedure I use all of the default settings, the only parameter I provide is the @database, which I use to pick out the active databases which need maintenance - for example:

USE MASTER
GO
EXEC dba_indexDefrag_sp @DATABASE='MyDb'

If I run this from SQL Management Studio, on the server, the procedure executes to completion, doing its work and recording its activity, taking about an hour and a half. If, however, I create a SQL Agent job for it, using the same login account (my own) to run the job then I see the job execute successfully BUT I see that the job completed in 0 seconds and the job history indicates an issue:

Job Name        Weekly Index Defragment
Step Name       Defragment MyDb Db
Duration        00:00:00
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: dbo. The user does not have permission to perform this action. (Line Number: 442) [SQLSTATE 01000] (Message 0).  The step succeeded.

The problematic line 442 is an INSERT, as follows:

        INSERT INTO dbo.dba_indexDefragStatus
        (
              databaseID
            , databaseName
            , objectID
            , indexID
            , partitionNumber
            , fragmentation
            , page_count
            , range_scan_count
            , scanDate
        )
        SELECT
              ps.database_id AS 'databaseID'
            , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
            , ps.[object_id] AS 'objectID'
            , ps.index_id AS 'indexID'
            , ps.partition_number AS 'partitionNumber'
            , SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
            , SUM(ps.page_count) AS 'page_count'
            , os.range_scan_count
            , GETDATE() AS 'scanDate'
        FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL, @scanMode) AS ps
        JOIN sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), NULL , NULL) AS os
            ON ps.database_id = os.database_id
            AND ps.[object_id] = os.[object_id]
            AND ps.index_id = os.index_id
            AND ps.partition_number = os.partition_number
        WHERE avg_fragmentation_in_percent >= @minFragmentation
            AND ps.index_id > 0 -- ignore heaps
            AND ps.page_count > @minPageCount
            AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
        GROUP BY ps.database_id
            , QUOTENAME(DB_NAME(ps.database_id))
            , ps.[object_id]
            , ps.index_id
            , ps.partition_number
            , os.range_scan_count
        OPTION (MAXDOP 2);

I don't understand why it fails as a SQL Agent job yet succeeds 'interactively'. Is there something about that INSERT statement which needs special attention? I would appreciate some help to allow me to resolve this problem. Thanks.


texts:
We have two DNS servers which replicate entried between them and we recently had a DNS issue where some of the listener addresses were removed from one of the servers only.

At that point some of our connections to AlwaysOn started to fail.  My question is, if he SQL server is configured with both DNS servers then shouldn't it have found the entry in the DNS server that still had the entry, or is this something specific to how SQL works?


texts: How can I exclude rows from two tables whose values are encompassed by each other?

texts:
I have the following two tables, Table A and Table B.
What I'm trying to do is return a result set where:

If the start position (only) in Table B is between the start and end position of any row in Table A, then I don't want that row from Table B to be in the result set.

If any of the start and end positions in Table A are between any of the start and end positions of any row in Table B, then I don't want that row from Table A.

Here is Table A:

RID
StartPos
EndPos

7
45
77

7
118
130

7
197
212

7
218
235

Here is Table B:

RID
StartPos
EndPos

7
83
87

7
121
132

7
175
179

7
183
191

7
195
214

7
221
237

Here is my desired result set. You can see that 197 and 212 from Table A fall between the row in Table B that has 195 and 214, so that row from Table A isn't in the result set. You can also see that the start position 221 in Table B is between a range in Table A (218, 235), so that row from Table B isn't in the result set either.

RID
StartPos
EndPos

7
45
77

7
83
87

7
118
130

7
175
179

7
183
191

7
195
214

7
218
235

In sum, I want to exclude where:

TableA start pos and end pos are between any of table B's start pos and end pos.
TableB start pos is between any of table A's start pos and end pos.

Also, there are more than one RIDs in the table...
I thought (and still think) I could get this done using a FULL join (or maybe using APPLY), but I'm getting a little stuck, which is why I'm asking you all for some help on it!
Here is the T-SQL to generate the two tables:
CREATE TABLE #TableA(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 45, 77)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 118, 130)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 197, 212)
INSERT #TableA (RID, StartPos, EndPos) VALUES (7, 218, 235)

CREATE TABLE #TableB(
    RID int NULL,
    StartPos int NULL,
    EndPos int NULL
)

INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 83, 87)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 121, 132)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 175, 179)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 183, 191)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 195, 214)
INSERT #TableB (RID, StartPos, EndPos) VALUES (7, 221, 237)

SELECT *
FROM #TableA

SELECT *
FROM #TableB


texts:
I'm trying to use the command restore database until cancel, but I'm getting the following erro:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "scn, sequence, time"
RMAN-01007: at line 1 column 24 file: standard input

Is this command deprecated?

My Oracle database version is the 11g R2


texts: Where do you go to request new features in SSMS?

texts:
I'd like to ask Microsoft:

To color code the pages in SSMS based on the server they are connected to
Make it go ding when the query results are returned.

Question: How do you ask for those features?


texts: In PostgreSQL, if crashes during VACUUM, will it recover from a checkpoint that is before the frozen transaction ID of VACUUM?

texts:
When VACUUM starts, it will calculate a frozen transaction ID and freeze all tuples older than it. Will this frozen TXID be younger than the latest checkpoint?
If this is the case, then when the system crashes after VACUUM has deleted some dead tuples and the system recovers from this checkpoint, these tuples will be available to some transactions again during the replaying of WAL.
Is my understanding of this process correct?


texts: How to store object with property of generic type in DB

texts:
In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be values that are strings, integers, timestamps, IP-Adresses, etc. It is also possible that I need to add another data type later but this should happen not very frequently.
New parameters will be inserted frequently. Values of existing parameters will probably be updated several different times. Parameters and values will be read very frequently but only for displaying them to the user, so without any special analytical operation.

For the later code (which will be C++) I was thinking of a parameter base class from which subclasses for each data type will be derived. StringParameter, TimestampParameter, etc.

I was now wondering how to store parameters in an Oracle 10g database. Ideas so far are

Single table, string column: A single database table for all parameters where the column type for the value is VARCHAR2. The code can then transform the values to the correct data type by using functions like atoi. Drawback is I would need an extra type column and transforming values back and forth with atoi and itoa is not very safe and neither good maintainable (e.g. if the data type of one parameter changes from int to long).
Single table, raw column: A single database table for all parameters where the column type for the value is RAW. The code then just stores and retrieves the property "as-is" in binary. Drawback is I would need an extra type column and changing the data type of a parameter would break backward compatibility (or one would need to perform a database upgrade which converts all old values to the new ones.
Multiple tables, one for each data type: A basic parameter table for all common properties and several extra tables only for the value so that the column can be of the correct data type. Drawback: Not all data types from my domain can be mapped to an oracle data type. Additionally I would need a lot of new tables and joins. Also introducing new parameters would need a change in the database schema.

For which option would you go and why? Is there another solution I have not yet thought of?


texts: Is accelerated database recovery available in SQL Server 2019?

texts:
SQL Server 2019 CTP introduced a new feature – accelerated database recovery. But then something strange started to happen:

If we look at doc page about this feature, we would see no mention of
SQL Server 2019 – there’s Azure DB only.
If we look at sys.databases
description, we would see no mention of
is_accelerated_database_recovery_on column, while in fact, it exists
Same thing about accelerated_database_recovery database option.
However, feature matrix shows ADR available for Standard and Enterprise SQL Server editions.

On the one hand, enabling ADR could benefit with faster transaction rollback and lesser startup time.

On the other hand, feature seems undocumented. Therefore, it’s behavior is unpredictable. If something goes wrong with transaction log, I could literally lose some data.

So, does anybody knows: can I rely on ADR using SQL Server 2019? Is it postponed due to some instability or it is just a glitch in documentation?


texts: SQL Server Transaction Rollback: does log shrink or grow?

texts:
When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size?

Do all the entries generated during and related to the transaction get deleted again (in both DB and LOG) or does the undo result in the same number of entries in the log, just undoing what was done before, growing the LOG file further?

Since deleting might be difficult in case of overlapping operations, I tend towards the latter, still I wonder and could not find out.


texts: Created RDS db-instance Replica successfully, now how to connect it with server

texts:
I had created AWS RDS, MySQL db-instance successfully with the help of Github Pages Documentation. Thanks a lot 18F/OpenFEC Project.

Now, Instead of Route 53 configuration for network connectivity, I want to configure directly through Godaddy DNS Manager. Because already a subdomain is configured for a production server with RDS db-instance endpoints.

I can create another subdomain at DNS Manager with Replica db-instance's end points. But, how to achieve connection on both db-instance as well as replica-db-instance.

Please guide me, to manage traffic of db query without updating that production app.


texts: Should indexes automatically be placed into their own bufferpool?

texts:
We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.

Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG data each into their own tablespace. (Reason is better control over disk, maintenance, backups, etc.)

Tablespaces have to be associated to bufferpools. Right now most of our tables and indexes fit into 4K bufferpools and tablespaces.

Normally with our scripts we set up a 4K bufferpool BP4K. We then set up at least two 4K tablespaces (as we dont' have a lot of LOBs): TS_DAT_4K for data and TS_IND_4K for indexes.
By default we have just been assigning both of these tablespaces to bufferpool BP4K.

What I have wondered: Since indexes are in their own tablespace, should they also be given their own bufferpool?

My reason for wondering this is based on optimization. If indexes have their own bufferpool, then they are more likely to remain in memory (rather than being pushed out of memory due to table records being read in). This would allow for faster scanning of indexes to find the records in the tables. And since indexes would no longer share the same bufferpool with the tables, more of the tables can now remain in memory for logical retrieval versus a physical read. So my thought is that this would reduce physical I/O and thus help with the performance/maintenance of the database.

I also can't help but wondering if this is just pre-optimization, which is 99% of the time a bad thing to do, introducing extra overhead, etc. (especially since we haven't identified that we need separate bufferpools yet based on current performance. Of course the application under development isn't in production yet either and does still need database tuning....)

Thoughts on this? Is this a best-practice? or just pre-optimization and over-thinking?


texts: Can I permanently lock a MySQL table to prevent inserts

texts: I have a complicated situation where I would like to prevent inserts into a MySQL table, but keep it open for edits/updates to existing data. Can this be done? I've searched around here, but could not find a similar situation.


texts:
CREATE OR REPLACE VIEW organizedevent AS

    SELECT o.organizerID AS "Organizer ID",
           o.organizerName AS "Name",
           o.contact AS "Contact",
           COUNT(e.eventID) AS "Organized Event"
    FROM   Organizer o ,Event e
    WHERE  o.organizerID=e.organizerID
    GROUP BY o.organizerID,o.organizerName,o.contact,eventID
    HAVING COUNT(e.eventID) >=1
    ORDER BY o.organizerID ASC;

I need how to write the join table statement to get the answer as I want the answer like ORG1058 have count 2 and ORG1059 have count 6.


texts: How to store one-byte integer in PostgreSQL?

texts:
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value and I want it to be stored in a one-byte data type.

Is there an extension or a way to use one-byte integer data type in PostgreSQL?
How many bytes is NUMERIC(1,0)?


texts: Automate update columns in a view with trigger function in plpgsql

texts:
Im attempting to update several columns in a view very similar to this post. The difference is that Im combining in some cases up to 3 tables, and for each update on the view I would like the trigger to do an INSTEAD OF UPDATE on the appropriate table.

From the Documentation I've figured out how to update columns by hand, but with some views having up to 20 or 30 columns this will get tedious. I would like to automate the process similar to the previously mentioned post.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE poi
(
  poi_id SERIAL PRIMARY KEY,
  col_a integer DEFAULT 0,
  col_b integer DEFAULT 0,
  col_c integer DEFAULT 0
 )
/

CREATE TABLE points
(
  poi_id integer REFERENCES poi(poi_id),
  col_d varchar(50),
  col_e varchar(50),
  col_f varchar(5)
 )
/

-- Insert some example values
INSERT INTO poi (col_a, col_c) VALUES(1,1)
/
INSERT INTO points (poi_id, col_d, col_e, col_f)
VALUES(1, 'col d description', 'col e description', 'name')
/

CREATE VIEW poi_view as
SELECT poi.poi_id,
poi.col_a,
poi.col_b,
points.col_d,
points.col_e
FROM poi
JOIN points
ON poi.poi_id=points.poi_id
/

-- Create Instead of Trigger
CREATE OR REPLACE FUNCTION poi_view_update()
RETURNS TRIGGER as
$$
BEGIN
  IF (OLD.col_a != NEW.col_a) THEN
    UPDATE poi SET col_a = NEW.col_a
    WHERE poi_id = OLD.poi_id;
    IF NOT FOUND THEN RETURN NULL; END IF;
    RETURN NEW;
  END IF;
  RAISE NOTICE 'No update performed';
  RETURN NEW;
  END;
$$
LANGUAGE plpgsql
/

CREATE TRIGGER poi_update_trigger
INSTEAD OF UPDATE ON poi_view FOR EACH ROW EXECUTE PROCEDURE poi_view_update()
/

Query 1:

select * from poi

Results:

| poi_id | col_a | col_b | col_c |
|--------|-------|-------|-------|
|      1 |     1 |     0 |     1 |

Query 2:

select * from points

Results:

| poi_id |             col_d |             col_e | col_f |
|--------|-------------------|-------------------|-------|
|      1 | col d description | col e description |  name |

Query 3:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     1 |     0 | col d description | col e description |

Query 4:

UPDATE poi_view SET col_a = 0

Results:

Query 5:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     0 |     0 | col d description | col e description |

UPDATE 3/7/18

In attempting to answer my own question, Ive decided to approach it as plainly as possible, by creating the appropriate variables, building a query, then simply using the EXECUTE command. So far I have

DECLARE
   v_schema  text := quote_ident(TG_TABLE_SCHEMA);
   v_name text := quote_ident(TG_TABLE_NAME);
   column_name text;
   t_schema text := 'my_schema';
   t_name information_schema.tables.table_name%TYPE;
 BEGIN
    column_name = ???
    t_name = SELECT DISTINCT table_name
    FROM information_schema.view_column_usage
    WHERE view_schema = v_schema
    AND table_schema = t_schema
    AND column_name = column_name;

My current issue is how do I find the column name from the original table the column came from?


texts:
We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at some instance the commits with exact same timestamp gets slow.

This is extracted from MySQL slow query logs

There isn't anyother query in the slow query log at the same time. Any pointers on how it can be further investigated?

We are using MySQL 5.7.16 on Amazon RDS.


texts: Sizing MySQL Database Servers for Drupal

texts:
What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc?

We're going to go with either the community edition or the enterprise edition, and use it to run Drupal for a www and other top-level sites. OS is RHEL 6.x, on VMWare. We'll also probably run a caching tool, like memCache or Varnish. At the moment we are looking at two load balanced web servers that connect to one or more MySQL servers. There is talk of possibly using a master-slave or multi-master configuration. DB engine is going to be InnoDB.

We're looking at about 6000 pages of content to start, and grow quickly. Our normal throughput is about 40Mbps (observed over one hour), although we've seen it as high as 70Mbps occasionally.

Thanks,

KM


texts: Suitable NoSQL DB design for a fleet of IoT devices

texts:
This is my use case:

10,000 IoT devices
Each IoT device sends its environmental data (temperature, pressure, etc.) every 30 seconds
Data of each device will be kept for 24 hours

The main requirement is to retrieve the latest data of a specific device (from device_id).
Retrieve of the data will be step-wise. Meaning, at one time, only 100 records will be read. Then if the user wants, he will request the read the previous 100 records, and so on.

Also, it is possible that multiple users try to retrieve data of multiple devices at the same time.

My concerns are:

How to structure the DB (I am specifically looking at AWS's DynamoDB)
Creating a table for each device would be elegant, however seems creating 10,000 tables is not recommended
If I put all the 10,000 devices in one huge table, reads from this would be very inefficient.

Could someone advice on what would be the best design strategy for this case?

Thank you.


texts: Any instance where DMV can cause blocking?

texts:
So we had some slow performance with timeouts this morning.  When looking for the cause, our blocking query (uses DMVs) showed up as the blocking query.  I think this was just a fluke but the job we use to get the blocking details was running 4 to 5 times slower than normal.  I believe this is just a side effect of whatever was causing the timeouts.

So my question is whether querying the DMVs could actually cause blocking for queries on other tables?  Doesn't seem logical.

Using:

dm_exec_sql_text
dm_os_waiting_tasks
dm_tran_locks
dm_exec_sessions
dm_exec_requests


texts: SQL Return Count of Answers with 0's for Choices from another table

texts:
My question went dead after one unhelpful answer on SO here

I need this to be optimized because it will be called often. It is going into a stored procedure.

I am given @slide (tblILDSlide.id) and @sessionid (tblILDSession.id)

I need my results to look like this:

--lets say students had answered a(3),b(10),c(0),d(5) in a survey (test)
 |Answer Counts
0|3
1|10
2|0 --this needs to show as a zero and not be skipped
3|5

Since questions will each have different numbers of possible answers I need to have the rows seeded from the @@rowcount of Choice for the QID that is here:

**dbo.tblSurveyAnswerChoice
 QID (int, not null)
 Sequence (int, null)
 Choice (nvarchar(100), null) --this column will vary and is for choices
 --like true/false, Yes, No, Red Blue,
 --or even Completely Agree,Somewhat Agree...,Completely Disagree

To get the QID I have to use @slide and:

**dbo.tblILDSlide
 id (PK, int, not null)
 slide_type(FK, int, not null)
 question_id(FK, int, null)
 media_url(nvarchar(100), null)

I need a row for every choice (with zeros for choices with no responses). The responses are kept here:

 --there can be more than one response per question
 --like for multiple-multiple choice (check all that apply)
**dbo.tblResponses
 response_id(FK, int, not null)
 answer_sequence (int, null) --accepts 1,2,3 etc for choice
 other (nvarchar(50), null) --for text answers

To get them I have to use response_id which I can get by using @sessionid AND @slide here:

**dbo.tblSlideResponseInfo
 id (PK, int, not null)
 user_id(nvarchar(50), not null)
 slide_id (FK, int, not null)
 response_date (datetime, not null)
 session_id (FK, int, not null)

Copied from the post at stackoverflow:  My problem is when there is a possible choice with no answers I get no count. So I know I need to grab the possible choices which are available [in the other table ...]  I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that, but I can't figure it out.


texts: Help - Error : Could not connect to master of shard replica set

texts:
I have a shard, using mongo v2.4.11. It has to mongos, 3 config, and 8 replica set (2 replica for each set, means 16 node for shard)

It is keep having exception as per below.

Caused by: com.mongodb.MongoException: can't connect to new replica set master [shardA01:27017], err: couldn't connect to server shardA01:27017

I try to look for anyone who might has similar issue, but i found nothing. This happens about 1 of 1000 request.

All node of shard is hosted using aws virtual servers.

What i have done is
- check CPU and memory but everything is fine, not even > 10% of usage
- set master priority to one of the node.

Anyone know what the problem is? Or how to further investigate the issue?


texts: MySQL (or MariaDB) multi-master without MMM

texts:
I've been reading blogs on MySQL multi-master setup.  The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut down the master, promote the slave to master, point the configs to the old slave, and do our work.  The new master would then become the slave (or we would fail back).

I'm trying to design a master-master setup.  I will not be using it in the performance sense of writing to both of them primarially, however I just want the secondary master to be ready as a backup and avoid the whole deal of promoting the slave to master (just point the configs to the secondary master).

I've seen some blogs describing this setup, but they didn't look too professional.  Basically your replica set is set up like a traditional master-slave, however Server 1 is both a master and slave of Server 2 and Server 2 is a master and slave of Server 1.

Are there any issues or caveats with this setup?  Is it recommended?  Will this allow me to direct writes to the secondary master, take down the primary, bring it back up, have it catch back up, then point my configs back to the original primary master?


texts: Getting the value from a column during a trigger

texts:
After a test completes (on a SQL 2008R2 system), the system updates a row in the TestRun table to include an end time. I am creating a trigger that will take action after that update, and do some post-test analysis for me.

Right now it's very simple.

BEGIN

    SET NOCOUNT ON;
    DECLARE @errCount Int
    @errCount = exec USF_NumofErrors @RunID = '124'

END
GO

However I would like to have that integer replaced with a variable that is the RunID of the row updated (which is causing the trigger to fire).

How can I do that?


texts: How to get the list of foreign key together with the index name on that foreign key?

texts:
I am using this query to list all the foreign key, but I also need to have the index name on that foreign key.
select a.*
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as a
where a.CONSTRAINT_TYPE = 'FOREIGN KEY';

How to get a list of the foreign key together with the index name on that foreign key?


texts:
I'm the developer, not the DBA. I don't know much about setting up databases but i'm concern of the setup at my current work.

The server is installed on desktop PC hardware(i7, 16GB RAM, 1 TB SATA disk) running windows server 2003(yup that's correct)

The system that's running on it has 25 users(will most likely increase to 30 soon) with (2 selects, 2 updates, 1 insert) at least twice every minute. Table isn't that big yet either, just reaching 2 million rows with 21 columns. I've checked every query and my indexes works fine.


texts: SQL Server sp_addrolemember in stored procedure for different database

texts:
I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database.

Use statements are not allowed in stored procedures, and both ALTER ROLE (it's a SQL Server 2012 box) and sp_addrolemember don't take a target database parameter.

Ideas?


texts: nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role

texts:
I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are:

pid
datid
datname

Other columns are constantly null. When query is run from superuser account there are values of course.
Based on pg_stat_progress_vacuum view definition I noticed that the problematic part is usage of pg_stat_get_progress_info function with VACUUM parameter (view names params returned by this function).
User has pg_monitor role assigned.
What am I missing?
Result of select * from pg_stat_progress_vacuum; from monitoring user
[
  {
    "pid": 33079,
    "datid": 16410,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  },
  {
    "pid": 38625,
    "datid": 121786502,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  }
]

Result from superuser for the same query
postgres=# select * from pg_stat_progress_vacuum;
  pid  |   datid   |     datname      |   relid   |       phase       | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-------+-----------+------------------+-----------+-------------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 38625 | 121786502 | <database name> | 121786624 | vacuuming indexes |         7091104 |           7091104 |                  0 |                  0 |       178956970 |        73659774
(1 row)

A couple of seconds passed from one query execution to the other, so I assume that just autovacuum has finished thus the difference in row count.
Postgres version is 12.2


texts: Help defining the correct relationship between two tables

texts:
I am working on the implementation of a point of sale system for restaurants, in my question I need to know the relationship that exists between the entities tables and orders, based on the following conditions

A customer can be served at a table.
A client can request to join several tables in consequence the order of this client can have several tables.

The current idea is that I should create a table called for example order_table with a many-to-many relationship between tables and orders or it should be a one-to-many relationship between orders and order_table.

Based on the conditions that I mention what you think would be the best solution to this case.

Thank you very much for your time


texts: How to delete a Database -Error throws the action cant be completed

texts:
Am trying to delete the WSS_UsageApplication database in  my sql server 2012 SP2 and this is one of the databases associated with SharePoint.
since its consuming huge db space - 62 GB!! of my hard disk and my application is very very slow.
How to delete this database?
 when i tried to delete this from Data folder, am unable to delete the same.
it says the error :

  the action cant be completed because the file is open in sql server.

[![unabel2delete-DB][1]][1]


texts: Fetch data from same table using two group by clauses in mysql

texts:
I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month.

In my table, user_type is individual and group (values I,G) with 10 entries.

4 are individual and 6 are group
2 individuals are registered in July and 2 are in August
4 group members registered in July and 2 in August.

So my output should be the following:

count of individual register in month of July ?
count of group register in month of July ?
count of individual register in month of August ?
count of group register in month of August ?

Sample table Data

usertable

+----+--------+-----------+---------------------+
| id | Name   | user_type | joined_date         |
+----+--------+-----------+---------------------+
| 1  | john   |  I        | 2014-07-16 00:00:00 |
| 2  | james  |  G        | 2014-07-11 00:00:00 |
| 3  | george |  I        | 2014-08-11 00:00:00 |
| 4  | aby    |  I        | 2014-07-11 00:00:00 |
| 5  | padma  |  G        | 2014-08-11 00:00:00 |
| 6  | Mick   |  G        | 2014-08-16 00:00:00 |
| 7  | Bony   |  G        | 2014-07-21 00:00:00 |
| 8  | Sebas  |  I        | 2014-08-11 00:00:00 |
| 9  | danie  |  G        | 2014-07-11 00:00:00 |
| 10 | davi   |  G        | 2014-07-01 00:00:00 |
+----+--------+-----------+---------------------+

expected result

+------------------+-------------+--------+
| Individual_Count | Group_Count | Month  |
+------------------+-------------+--------+
| 2                | 4           | July   |
| 2                | 2           | August |
+------------------+-------------+--------+

I need to populate This date in a Bar chart. I am bit confuse for this query for last days.Please help me if possible.
Thanks in advance


texts: dockerized postgresql listing no clusters

texts:
I'm upgrading an existing dockerized postgres:9.6 to 10. There are plenty of good Q/As on DBA and SO that go the path of pg_upgradecluster, but it is not working for me.

When I start a vanilla pg:10 instance, whether I mount the old volume or not, pg_lsclusters is empty.

root@server:# docker run -it -d --name pg10 postgres:10
...
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
...
2018-07-15 05:11:52.569 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-07-15 05:11:52.635 UTC [68] LOG:  database system was shut down at 2018-07-15 05:11:52 UTC
2018-07-15 05:11:52.651 UTC [1] LOG:  database system is ready to accept connections

Connections work:

root@server:# docker exec -it pg10 psql -h localhost -U postgres postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgres=#

But clusters are not found:

root@server:# docker exec -it pg10 su postgres -c pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file

(Nothing given.) If that will work, then I'm intending to mount the old db volume as another cluster (via steps listed in https://stackoverflow.com/a/47233300/3358272).

I don't understand enough about the db layout to know why the default postgresql image does not produce its default database as a cluster that can be seen with this utility. Help?

Edit: the current installation lists them just fine, though it's an instance of sameersbn/postgresql, not the pg-central version:

root@server:# docker-compose exec postgresql pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

The library image defaults to creating a new database under /var/lib/postgresql/data (no version, no main).


texts:
I have these tables to which I am doing a query to get data from all the tables
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e8d847e118b372071e42c685e9e5ad72
In the sample, there is not a lot of data but I am looking for a way to paginate the data results of the UNION ALL query.
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    ARC.TITLE,
    ARC.EXID,
    ARC.CREATED_AT,
    ARC.FROM_DATE,
    ARC.TO_DATE,
    null as EMBED_CODE,
    ARC.FILE_NAME,
    ARC.FRAMES,
    ARC.URL,
    ARC.PUBLIC,
    ARC.STATUS,
    ARC.TYPE,
    null as EXTRA
from
    PUBLIC.ARCHIVES as ARC
left join USERS U on
    ARC.REQUESTED_BY = U.ID
left join CAMERAS C on
    ARC.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    TL.TITLE,
    TL.EXID,
    TL.INSERTED_AT,
    TL.FROM_DATETIME,
    TL.TO_DATETIME,
    null as EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    null as PUBLIC,
    TL.STATUS,
    null as type,
    TL.EXTRA
from
    PUBLIC.TIMELAPSES as TL
left join USERS U on
    TL.USER_ID = U.ID
left join CAMERAS C on
    TL.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    COMP.EXID,
    COMP.NAME,
    COMP.INSERTED_AT,
    COMP.BEFORE_DATE,
    COMP.AFTER_DATE,
    COMP.EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    COMP.PUBLIC,
    COMP.STATUS,
    null as type,
    null as EXTRA
from
    PUBLIC.COMPARES as COMP
left join USERS U on
    COMP.REQUESTED_BY = U.ID
left join CAMERAS C on
    COMP.CAMERA_ID = C.ID;


texts: Postgres: understanding amount of WAL files/size generated

texts:
I'm constantly caught by surprised how certain operations generate me huge amount of WAL files.

I want these WAL files for point in time recovery (I also perform a nightly full dump in addition) so the basic functionality provided is wanted and I don't want to change that (i.e. I'm not searching for a way to turning WAL archives off, etc.)

Using Postgres 9.5 with these settings:

wal_level = archive
checkpoint_timeout = 20min
max_wal_size = 1GB
min_wal_size = 80MB
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

I had run this statement today:

WITH table2_only_names AS (
    SELECT id , name FROM table2
)
UPDATE table1
SET table2_name = table2_only_names.name
FROM table2_only_names
WHERE table1.table2_id = table2_only_names.id;

Table1

CREATE TABLE public.table1 (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('table1_id_seq'::regclass),
  table2_id BIGINT,
  table3_id BIGINT,
  positive_count INTEGER NOT NULL DEFAULT 0,
  neutral_count INTEGER NOT NULL DEFAULT 0,
  negative_count INTEGER NOT NULL DEFAULT 0,
  is_blocked BOOLEAN NOT NULL DEFAULT false,
  blocker_id BIGINT,
  group BIGINT,
  created TIMESTAMP WITH TIME ZONE,
  modified TIMESTAMP WITH TIME ZONE,
  table4_id BIGINT,
  name CHARACTER VARYING(255)
);
CREATE UNIQUE INDEX idx1 ON table1 USING BTREE (table3_id, table2_id);
CREATE INDEX idx2 ON table1 USING BTREE (table3_id);
CREATE INDEX idx3 ON table1 USING BTREE (table2_id);
CREATE INDEX idx4 ON table1 USING BTREE (group);
CREATE INDEX idx5 ON table1 USING BTREE (blocker_id);
CREATE INDEX idx6 ON table1 USING BTREE (table4_id);

15 Mio rows, table size ~3,4GB, index size 7GB

Table2

CREATE TABLE public.table2 (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('table2_id_seq'::regclass),
  name CHARACTER VARYING(255),
);

10 Mio rows, table size ~2GB, index size 3,4GB

The runtime was about 55 minutes (not complaining here).

The amount of generated WAL files was unexpectedly huge. The aforementioned WAL archives are on a dedicated partition with has 100GB and at the point the query started, around 30GB were free.

It was not enough as after 15-20 minutes disk space was <10GB and I started to delete "older" WAL archives. I had to do this constantly up to the point were I was pretty sure I already had to delete WAL archive files generated by this very statement.

The tables in question were not used by any other process during that time but "normal" operations of other tables continued.

I've marked the time where I started the query and where it ended. You can clearly see where I deleted the WAL archive files :-)

To me, why so many are generated is a mystery and it currently is a problem because it's hardly foreseeable how much is needed and when operation strike which need one.

What am I missing to better understand how much space is needed?
Are these things avoidable?
Am I doing something wrong?


texts:
I am trying to insert 5-star ratings by James Cameron for all movies in the database. Review date must be leaving as NULL.
Here are my tables

My way is:

INSERT INTO rating(stars)
SELECT rating.stars FROM rating
INNER JOIN movie
ON rating.mID = movie.mID
WHERE rating.stars='5'
AND movie.director='James Cameron'
AND rating.ratingDate IS NULL;

Why does my query doesn't work as expected? It just returned my empty set.


texts: How would I structure embedded fields such that 2 collections can access them?

texts:
Sorry for the vague title had trouble trying to word it.

Essentially I'm making a Discord bot and I'm making a ranking system for it. I have Users and Guilds collections and I'm wondering how I'd establish ranks for each user per guild. A user can be in multiple guilds and obviously guilds can have multiple users.

The schema(s) of Guilds and Users as of current doesn't matter much but the data I'm trying to store for each user in each guild is as follows:

{
    user_id: int32
    guild_id: int32
    background: string
    level: int32
    points: int32
}

Currently the way I have been storing the data is in it's own collection called GuildUsers but this feels wrong as they are small documents and there's repeated data in this case kind of like a sql many to many relationship.

I'd like to embed these documents into either Users or Guilds although am having trouble deciding which one as I still need to have aggregations for the highest level user(s) globally (across all guilds) and highest user(s) for a particular guild.

Currently all I need to do to get the top 10 in a server is:

db.guild_users.find({_id:guild_id}, options={sort: [('level', -1), ('points', -1)], limit: 10})

And for top 10 overall just omit the filter. But how would I go about this if it was embedded and what would be the best collection to embed the data in?


texts: SSAS Dimensions Hierarchy causes deployment errors

texts:
I've set up a Data Source Connection to the Adventure Works DW database using the service's credentials.

I'm creating a dimension using the DimDate table.  To simplify things, I'm using only the Calendar Quarter, Calendar Year, Day Number of Month, English Month Name, and the Date Key columns.

Each of these attributes have been set to AttributeHierarchyVisible = False.

I dragged the Calendar Year into Hierarchies followed by Calendar Quarter, English Month Name, Day Number of Month.

I set the attribute Relationships to:

Date Key -> Day Number Of Month -> English Month Name-> Calendar Quarter -> Calendar Year

All relationships are set to rigid.

When I try to deploy this setup, I get the following errors:

  Error  2   Internal error: The operation terminated unsuccessfully.        0   0
  Error   3   Server: The current operation was cancelled because another operation in the transaction failed.        0   0

I also receive this warning:

  Warning    1   Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimDate', Column: 'CalendarQuarter', Value: '2'. The attribute is 'Calendar Quarter'.      0   0

I can't figure out what is going on, or why this is erroring.  The book I'm using gave me the steps listed above and Google has failed me thus far (a very scary thing).

Any help you guys can give to help me figure out what I'm doing wrong would be greatly appreciated.


texts: How can I add timestamp into my table when I use Write-DbaDbTableData?

texts:
I am trying to use one table to store the SQL server disk space checking data by dbatools:
$allservers_PRD | Get-DbaDiskSpace | Write-DbaDbTableData -SqlInstance MySQL2019 -Database SQL_DBA_TASK -Table SQL_DISK_SPACE -AutoCreateTable

How can I add timestamps (what is the checking time) into my table rows as addition column when I use it?
Many thanks,


texts: Assign number incrementally, reset when value changes


texts:
I'm trying to merge data from multiple tables in different databases on the same server however the result is incorrect (i.e. the number of rows returned is more than what it should be). Here's the code:

merge into factOrders fo
using (
    select CustomerKey
        ,ProductKey
        ,CategoryKey
        ,dt1.TimeKey as [OrderDateKey]
        ,dt2.TimeKey as [RequiredDateKey]
        ,dt3.TimeKey as [ShippedDateKey]
        ,o.OrderID
        ,Freight
        ,od.UnitPrice as [UnitPrice]
        ,od.Quantity as [Quantity]
        ,od.Discount as [Discount]
        ,od.UnitPrice * od.Quantity * (1 - Discount) as [TotalPrice]
        ,sh.CompanyName as [ShipperName]
        ,sh.Phone as [ShipperPhone]
    from db1.dbo.Orders o
        ,db1.dbo.[Order Details] od
        ,db1.dbo.Shippers sh
        ,db1.dbo.Products p
        ,dimCustomers dc
        ,dimProducts dp
        ,dimCategories dct
        ,dimTime dt1
        ,dimTime dt2
        ,dimTime dt3
    where o.OrderID = od.OrderID
        and dc.CustomerID = o.CustomerID
        and dp.ProductID = od.ProductID
        and dct.CategoryID = p.CategoryID
        and sh.ShipperID = o.ShipVia
        and dt1.date = o.OrderDate
        and dt2.date = o.RequiredDate
        and dt3.date = o.ShippedDate
    ) o
    on (
            fo.ProductKey = o.ProductKey
            and fo.CustomerKey = o.CustomerKey
            and fo.CategoryKey = o.CategoryKey
            and fo.OrderDateKey = o.OrderDateKey
            )
when matched
    then
        update
        set......
when not matched
    then
        insert.....
        values (......)

I can tell the issue is somewhere in the WHERE condition and I've tried multiple options but not getting the correct result.


texts: How do I extract a repeating string pattern from multiple rows?

texts:
I have the following data:

|  ID  |                 BadCol                        |
+------+-----------------------------------------------+
|  1   | All of R489804 R489805 R489806 R489807 R489808|
|  2   | SP fr R308999                                 |
|  3   | R308777, R104621, R708931                     |

I'm only interested in the R###### pattern (which will always be an R followed by 6 numerical characters). I can retrieve the location of first occurrence using the following function:

PATINDEX('R[0-9][0-9][0-9][0-9][0-9][0-9]', BadCol)

Ideally I'd like to pivot the pattern into it's own row like so:

|  ID  | GoodCol |
+------+---------+
|  1   | R489804 |
|  1   | R489805 |
|  1   | R489806 |
|  1   | R489807 |
|  1   | R489808 |
|  2   | R308999 |
|  3   | R308777 |
|  3   | R104621 |
|  3   | R708931 |

I've seen some great resources on how to do this when the data is delimited the same way, such as here. Unfortunately the data in this column is delimited differently from row to row. I've also explored a recursive CTE like what is detailed here, but unfortunately both of these methodologies use CHARINDEX() which allows an optional start_location parameter. PATINDEX() unfortunately does not take a start_location parameter.


texts: What permissions do I need to grant on service accounts on SQL root path when relocating?

texts:
During a Win2012 with SQL Server 2012 two-node cluster installation, I accidentally set the system root to be the mount point volume hosting folders for the various attached LUNs for the system and user DBs.

I relocated them post-installation to the volume I have for the System DBs and made the corrections to the startup switches, registry for the full-text service, and SQL Server Agent error log. However, I'm still getting:

  error code 5: Access Denied errors on service startup

This only goes away when I grant the SQL Server service account local Admin privileges. I tried granting NTFS Full Control privileges on the mount point to the service account but no luck.

Is there another permission set I should apply and to which accounts?


texts: How to Find Missing number in Sequence using mysql?

texts:
SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
   FROM wp_blogs AS a, wp_blogs AS b
   WHERE a.blog_id < b.blog_id
   GROUP BY a.blog_id
   HAVING start < MIN(b.blog_id);

It's working for me thank you for this post and i need brief explanation about this query


texts: Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?

texts:
I have a function that returns a table. This function SELECTS DB1.dbo.table1

user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the security context does not allow access to DB1

Can a user have permissions to read only functions but not tables?


texts: Querying from system.schema_keyspaces generates code=2200 [Invalid query]

texts:
I'm following Cassandra docs to learn CQL but when I type the query
SELECT * from system.schema_keyspaces; the system generates
InvalidRequest: code=2200 [Invalid query] message= unconfigured table system.schema_keyspaces. Although I'm able to execute another quires like create, update..etc.


texts:
I can't find pg_resetxlog under /usr/bin.

postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1)

cd /usr/bin
postgres@node-1:/usr/bin$ ./pg_resetxlog
-su: ./pg_resetxlog: No such file or directory


texts:
I have to use Sybase's SQL Anywhere for a project so it has been put on a VM. Memory consumption is particularly high for a not overly taxed database and it's putting a lot of pressure on the VM.

The process is dbsrv12.exe and it's consuming >3GB of memory. Is there a way I can limit that to something more sensible and suitable for a VM?


texts: DBCC SHRINKFILE works with file_id but not with logical name

texts:
I am trying to shrink a database file but am running into errors.

Using the file_id from sys.database_files works, but using the logical file name yields an error.

The logical file name is the same in both statements, so that can't be the problem. Also, the database that is being connected to, is the same. The following works as expected:

declare @fileId as int = (select file_id from sys.database_files where name = 'XY')
DBCC SHRINKFILE (@fileId, 0, TRUNCATEONLY)

However the following...

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

...will result in error 8985:

  Msg 8985, Level 16, State 1, Line 1
  Could not locate file 'XY' for database '<my database>' in sys.database_files. The file either does not exist, or was dropped.


texts: EC2 - How to correctly back up PostgreSQL data?

texts:
Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data directory, and one volume to store WAL files from PostgreSQL.

(1) The volume with WAL files will also have a base backup of the data directory, which is copied over after doing a pg_start_backup(). Then it will store the continuous archive output from PostgreSQL (WAL files). To snapshot this volume, is there any point in issuing a sync and freezing the filesystem (using xfs_freeze if it's XFS or dmsetup if it's EXT4)? Or can I just take a live snapshot? WAL files will be shipping at a rate of one per minute. Is it possible that a snapshot could be initiated while a single WAL file is being copied over and resulting in corrupt data?

(2) The volume that contains the live PostgreSQL data directory will also be backed up for good measure (daily). Before doing a snapshot of this volume, I issue a pg_dump and the resulting SQL file is kept in the data directory. Is there any point in taking precautions to ensure the actual database data is consistent? Would it be correct to assume that taking a live snapshot will properly (a) backup config files (postgresql.conf, pg_hba.conf, pg_ident.conf) and (b) backup the SQL dump file. Backing up those two things, sql dump file and config files, would be the main point of snapshotting this volume. The DB is not very big so I don't mind the fact that data files will bloat this snapshot. And in that case, I can just do a live snapshot -- correct?

(2a) Would it just be better to keep the data directory on the root volume, and have a backup script that copies the sql dump file as well as config files onto another volume, and snapshot that volume once the copy is done?

(3) As for the volume with code on it, again is there any point in sync'ing and freezing the filesystem? Or can just a live snapshot be taken? This data should be fairly "static".

(4) Is this a solid backup scheme? The root volume is not backed up on a regular basis since I will just keep a machine image after it is set up and configured.

Thanks


texts: SP_whoisactive capture on Alwayson Secondary Replica issue

texts:
I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column.
This happens when I run the code below as a sql job. However, when I have my SSMS connection set to use APPLICATIONINTENT = READONLY and execute it manually it stores the execution plan for the queries running on the server.

How do we set APPLICATIONINTENT = READONLY  for SQL Agent jobs running on secondary readonly replicas. Thanks!

EXEC dbo.sp_WhoIsActive
        @get_plans = 1,
        @destination_table = 'dbo.WIA_Output'

error message="The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."


texts:
Quote from MariaDB Documents

Description: Limit in bytes of the InnoDB FULLTEXT index query result cache per fulltext query. The latter stages of the full-text search are handled in memory, and limiting this prevents excess memory usage. If the limit is exceeded, the query returns an error.
Default: 2000000000

I want to know if for example both queries run FTS query in parallel, MySQL Allocate about 1907MB to per query (if query wants) ?
Because we have to many FTS query and all are complex query which lead to growing use of RAM.
Update 1
This is one our query:
select id,title,content,time,c_count,agency_name
    from news join agency use(id)
    where time >= '2016-10-01 00:00:00'
      and time <  '2016-11-01 00:00:00'
      and match("content") against('+("Hello" "Hi" "Hey")+("World" "All" "guys")'
            in boolean mode)
    order by c_count desc


texts: How to join on a column that includes the ID but has additional strings (Redshift)

texts:
I have 2 tables with the following 2 columns that I would like to use as the key for my join: a column that has the ID of the call and I have another one which is the link to the call, containing the call id, which I would like to use as the key for tmy join
Example:
Call ID: 65452346546
link: https://app.srug.io/id=65452346546
Is there a way to join the 2 tables or do I have to do some data preprocessing before.
I tried to split it, but this didnt quite work, because the new ID column is empty:
select *,
SPLIT_PART(reverse("link"), 'id', 11) as "ID"
from "Call Table"

Any advice?


texts: What syntax does PostgreSQL require when all identifiers in a query are dynamic?

texts:
Example:
SELECT $1 FROM $2.$3 WHERE $1 = $4

Result:
ERROR:  syntax error at or near "$2"

Example:
SELECT "$1" FROM "$2"."$3" WHERE "$1" = "$4"

Result:
ERROR:  relation "$2.$3" does not exist

If you wonder why all the identifiers are parameterized, it's because I'm designing a database manager tool which dynamically allows me to edit any "cell" in any table.


texts: Why changing the order of network protocols does not make any sense?

texts:
All I want to do is to connect to SQL Server though Named Pipes without using an explicit way like NP:ServerName because mentioned approach works.  I want to connect through Named Pipes using just ServerName and give a SQL Server a chance to choose the right network protocol. So I have 3 VMs:

What I did step by step:

In Server Configurations of SQL Server A I enabled all network protocols.

I am also familiar with the Order column, however, changing it appears to have no affect. I swapped the order of TCP/IP and Named Pipes:

I tried to connect from SQL Server B into SQL Server A using Computer Name(WIN-VKHOKLJ3IJG) and I hoped that it will use Named Pipes protocol but unfortunately it chose TCP/IP as a connection protocol:

Could someone explain why TCP/IP was chosen instead of Named Pipes? According the order, SQL Server should choose Named Pipes but it rather preferred to use TCP/IP.


texts: PostgreSQL DECLARE can't understand integer

texts:
I'm trying to declare a very simple cursor where I define a variable and do a simple select:
DECLARE
    something integer;
BEGIN
    select * from artifact;
END;

I continually get this error:
Error: ERROR: syntax error at or near "integer"
I've also tried replacing integer with int or numeric (which doesn't make sense in this situation but I tried anyway) and haven't had any luck at all.
I'm using postgresql jdbc: org.postgresql:postgresql:42.2.7'


texts:
Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent users benchmark is improved by a factor of 8, at least.

However, on my testing environment at Amazon RDS, which is a clone of my production environment, absolutely nothing changes. My benchmark results from after and before setting it to 2 are virtually the same. The server was rebooted and the right value is there when querying for SHOW VARIABLES.

I expected improvement wouldn't be as much as with a local server, but I expected something at all, even if not worth the consequences. Any ideas on what might be wrong? Does Amazon RDS doesn't update it immediately somehow or loads default settings from somewhere else?


texts:
I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm unfamiliar with how to program this). The problem I'm seeing with this specific table is that when I start another population test, the SEQUENCE does not reset back to the first number I want (which is 1).

When I wish to re-run a new test I delete the table in question then run the following:

DROP SEQUENCE foo.fee;
GO

DROP SCHEMA foo;
GO

When I want to re-run the test I run the following SCHEMA & SEQUENCE commands, which are fired in the order below:

CREATE SCHEMA foo;
GO

CREATE SEQUENCE foo.fee
START WITH 1
INCREMENT BY 1
NO CYCLE
NO CACHE;

GO

I then create the table:

CREATE TABLE foo.sample_table_with_data
(order_number bigint PRIMARY KEY NOT NULL,
sample_column_one nvarchar(max) NULL,
sample_column_two nvarchar(max) NULL,
sample_column_three nvarchar(max) NULL)

GO

Once that is completed I run the following insert command 50,000 times:

INSERT INTO [foo].[sample_table_with_data]
(
    [order_number],
    [sample_column_one],
    [sample_column_two],
    [sample_column_three]
)
VALUES
(
    NEXT VALUE FOR foo.fee,
    'Blah',
    'Blah Blah',
    'Blah Blah Blah'
)

Now there is absolutely no problem with the data entering into the table.  The challenge I'm encountering is that when I delete the table, drop the schema and sequence then re-create the table, sequence, and schema the SEQUENCE picks up from the last number in the previous database incarnation and not reset back to one.

For example, if the last number in the sequence is say 634,534 the next sequence number in the new table is 634,535.

After deleting the table and dropping the schema and sequence I run the following to verify removal of the sequence and schema:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
GO

SELECT * FROM sys.sequences
GO

I'm stumped as to why this is happening.  Is there another command that I'm missing here that would help me localize what exactly is going on here?

I should note that this table belongs to a database with 7 other tables all running the SEQUENCE command correctly.

This is a SQL 2012 SP1 Enterprise Edition installation.


texts: How to restore a postgres database created via an older version of postgres?

texts:
I have a file db.backup, that I would like to restore to PostgreSQL version 11.

When I tried to install via Azure Data Studio I got an error:

  Restore: pg_restore: [archiver] unsupported version (1.13) in file header

Then I found that running pg_restore I can actually read the file:

pg_restore /..db.backup -U postgres > db-restore.sql

And I can see that the file is human-readable. However I would much rather just restore the database from the backup.

How can I do this? I tried installing an older version of PostgreSQL server, but I don't know how to install an older version of PSQL (yet). Would this help?

Is there some flag I can add to the restore command so that the restore works?

-- EDIT

The output of pg_restore --version, i.e. the version of pg_restore that gave me the error message mentioned above:

  pg_restore (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)


texts: AlwaysOn Geo Dispersed Automatic Failover

texts:
I'm looking to implement a "not quite" hub and spoke type architecture using AlwaysOn and wondered how the cluster quorum might play into this scenario if a remote site can't connect to the hub where we setup a file share witness or the other nodes in the underlying cluster. If the remote location was the primary for the AG and had a local secondary and also a remote secondary (Corp), would the AG automatically failover to the remote secondary even though there may not be an issue inside the remote location?

For example: The hub we will call corporate and the remote locations we will call stores, all on the same AD domain. I put all twelve stores (two servers each, a primary and a local secondary) and the corporate server (will be a central secondary to each of the remote primary servers) into the cluster together (not using Failover Clustering, just the backbone of AlwaysOn) and setup a file share witness. Now let's say that store 1 looses WAN connectivity back to corporate for a few hours. Since neither of the servers (primary or local secondary) can connect to any other store (node) and the file share witness can't be reached, will the Availability Group automatically failover if I have it set for Automatic Failover and Database Level Health Detection? Or will it understand that it has gotten separated from the cluster and simply do nothing?

I realize this is a bit of a complex use case when it comes to AlwaysOn, but thought I'd see if anyone had knowledge of how this scenario might work.


texts:
We currently have a db that the backup has been set to Simple that is being backed up every night (we have backups for last 7 days), is it possible to find records that were deleted in say June?

The db is fully functional so no corrupting or anything, we just want to be able to see if we can some how find deleted records in the current log file.
I have used apexsql log but that shows there is nothing, assuming because when a backup is done in simple mode, it pretty much discards the log.

Another question is, say i want to be able to have the ability to get deleted records in the future, should i be setting my backup to "full" and is that good enough or do i need also do bulk log backups?

I guess is there a simple way that its explained on what mode means what and what type of recover I get.  The ms article https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017 talks about this but It was not clear to me what needs to happen in diff scenarios when one needs to get specific deleted/changed records.


texts:
I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics (CPU, I/O, memory, etc.) but the query list is completely empty.

So, does anyone have any experience with pgtop specifically on RDS for PostgreSQL? I would like to know what queries are running, cpu usage, queries state, etc.


texts: What would cause extra records to be inserted during a INSERT trigger

texts:
The following trigger that joins Inserted table on itself creates multiple unwanted records:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

Where as this trigger that joins Inserted on Foo (the table that the trigger is linked to) creates one record.

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

The second trigger does exactly what I want, but I'm trying why the first trigger creates so many records. I would've expected Inserted and Foo to be identical when the trigger fires. Is there any kind of special property/behaviour that the Inserted table exhibits when joining on itself.

Apologies if there's not enough info, I've tried to reduce to the basic differences between the two triggers.

Update #1

I'm trying to backfill the table with records where the user account has transactions, but doesn't have the flag set.

Update #2

The inserts are carried out by a C# app. The data source is flat file where each line represents a single row in the table. I don't know if the app uses connection pooling or if this matters. The bulk inserts are done once a month. The C# app usually imports 8373 rows. The 1st trigger inserts 50 additional row, there's only one row that I want.


texts: In-Memory OLTP SQL Server 2016 - insufficient system memory

texts:
I am trying to place an In-Memory OLTP table,
Here is the memory estimate you will need:

But when the migration starts, the following error message is displayed:

The size of my table is approximately 22GB

I have a server with 64GB of memory and I allocated it for SQL 56GB, and to put the table in memory I only need 23GB

Can anybody help me?
Thanks.


texts: Select rows where value of second column is not present in first column

texts:
I have this table structure:

 id1 id2
 1   2
 1   3
 1   4
 2   1
 2   5

I need to build a query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4.
I tried this one:

SET @a=1;
SELECT DISTINCT x.id2
 FROM tt x, tt y
 WHERE x.id1=@a AND x.id1 != y.id2;

but it's not working.


texts: Issues installing SQL Server 2008 on Windows Server 2008 R2

texts:
I am trying to install SQL Server 2008 on Windows Server 2008 R2 Cluster. While installing the first node, I keep on getting the following error:

  TITLE: Microsoft SQL Server 2008 Setup
  The following error has occurred:
  Attempted to perform an unauthorized operation.
  Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

Has anyone seen this issue before? I have uninstalled it multiple times and tried re-installing it but same results.


texts:
I have 2 different query and i want to summarize their count in other query like this:

    select a,((select count(id) from foo) + (select count(id) from bar))
    as 'alpha' from beta;

Is there a way to do it on mysql?


texts: How can I make PQExecPrepared() not hang if the network connection has been lost?

texts: We're using prepared statements and have found that if, after successfully connecting and executing some statements, the network link the connection was usning goes away,  PQExecPrepared() hangs forever.  Is there a way to dictate from the client side, a maximum time to wait?  In comparison, if the Postgres server goes down, rather than the network, PQExecPrepared() returns immediately with an error.


texts: SQL 2012 Upgrade Advisor cannot detect SQL Server

texts:
I have an instance of SQL 2005 on a Windows 2003 server that I want to analyze using Upgrade Advisor. I can access SQL Server remotely via SSMS, so I assume necessary ports are open & user privileges enabled. However, Upgrade Advisor gives me the following error:

===================================

Upgrade Advisor could not detect SQL Server components on the server. Please check if current user has the privilege to access target server's registry, and if the Remote Registry service in target server is started.

===================================

Requested registry access is not allowed. (mscorlib)

Program Location:

at System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
   at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
   at Microsoft.Win32.RegistryKey.OpenSubKey(String name)
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo()
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.DetectComponents()
   at Microsoft.SqlServer.UpgradeAdvisor.FormDetectComponents.Detect()


texts: Linked Server Query Failures Due To Indexes

texts:
We are trying to query a 2008R2 database (ServerA) from 2012 (ServerB) using a linked server on ServerB that has security setup to use a SYSADMIN SQL Server Login on ServerA.

Example: When I run the query below

SELECT TOP 5 * FROM [ServerA].AdventureWorks2008R2.sales.currency

I receive this error

  An "OLE DB provider SQLNCLI11 could not map ordinals for one or more columns of object DBSCHEMA_TABLES" error is received when executing an SQL statement against a linked server in Microsoft SQL Server.

By itself, that is a cryptic error that I can find little about; however, that is not the really odd thing.

If I delete all of the indexes from ServerA.Adventurworks2008R2.sales.currency, it works exactly as expected. If I then add any type of index, whether UNIQUE or not, back to the table, I get the same error.

If I query a system table as shown below, it also works fine

SELECT TOP 5 * FROM [ServerA].AdventureWorks2008R2.sys.tables

The only viable workaround that I can find is to use OPENQUERY. Although that does work it is not preferred. Otherwise, I'm out of options.

Has anyone seen or heard of anything remotely similar to this?


texts:
My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely.

If for some reason the network connection between the two machines break, while a query was being processed, what will happen?

I suspect that MySQL will reject any garbage data but I don't want it to save that garbage data in my rows and I don't want MySQL to get stuck.


texts: how to disable and enable SSIS package using commandline?

texts:
I have a scenario where SSIS packages are deployed at the customer's site and they have some data issues.

Because of this we are developing new packages to fix the corrupted data.

When my new pacakge will be executed I want to disable the previous packages for the time when the fix is running on and then enable it again.

Are there any commands or scripts which I can execute from my new package to disable other packages and then enable them back afterwards?


texts: we have got performance experts analyzing our SQL server by running profiler traces in production. The profiler trace creates 20 GB of trace files everyday since 2 weeks. Is this a correct approach? Please suggest .


texts: Percona PMM Dashboards in Vanilla Grafana Crash

texts:
I have a vanilla non-PMM flavoured Grafana + Prometheus setup.
It seemed really silly to install a completely different appliance and setup to get Percona PMM Dashboards in my Grafana. So back in April 2019 I grabbed the dashboards from Percona's github and installed them. Aside from a few dead links or blanks, for the majority they all worked. I did some mild string transformations on the dashboard JSON files to fit them into our setup better (datasource name, dashboard name, tags).
Today I upgraded to the latest Promethehus + Grafana 7.0+ setup, which went great, and other non-percona dashboards are working great.
But when I went to import the latest Percona PMM dashboards, my troubles started.
First I ran into the fact they now use custom panels shipped with the pmm-app plugin, so I grabbed their plugin and installed the panels that way. Since I'm importing the dashboard JSON manually so I can do some light transforms mentioned above, I made sure to rebuild the plugin without the dashboards embedded in it, so only the panels are there. That seems to import OK, and the panels show up in grafana.
But some of the dashboards error out in a huge way, like CPU_Utilization_Details_Cores.json one the Overall CPU Utilization Details row starts showing IFrames with Not Found errors. After about 5 seconds the whole dashboard crashes with Dashboard not found errors and boots me out back to the home screen. It doesn't mention what dashboard are missing, and the javascript console shows 23 /api/dashboards/uid/undefined 404 errors.
Has anyone else managed to import the PMM dashboards into their grafana? What am I missing here?
Here's percona's github repo for the dashboard and pmm-app plugin: https://github.com/percona/grafana-dashboards


texts: SQL Server 2008: Measure tps / select statements per second for a specific table?

texts:
There is a discussion how to measure activity against an entire DB: https://stackoverflow.com/questions/5019593/sql-server-2008-transaction-per-second-for-specific-database

My question is narrower.

We are working on performance optimization of our application. To see how we are doing, we need to measure SELECT operations per second/minute/hour (prefer hour) on a specific table. (This way we can measure before and after implementing the optimization, to see how we are doing.)

What is the method or tool to do this?

Thank you!