MongoDB Indexing - What happens if I don't index all necessary fields? Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

Those 2, should I index them? If I don't, will I still benefit from indexing the first 3?

Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

  3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

  Those 2, should I index them?

If you have an index on three fields supporting your common equality/sort/range queries, this index is still a viable candidate for a query including additional fields. There will be some performance cost for fetching documents to find values that aren't included in the index, but this may be acceptable depending on your queries and data distribution.

The details of your common queries will also influence whether an index on five fields can effectively support lookups on three. If your common queries include different combinations of equality, range, and sort fields you will likely need multiple indexes.

One situation to be wary of is having multiple candidate indexes that might support a given query shape, but no ideal index. For example: if you have an index on the three common fields and a separate index including the two less common fields, the outcome of planning for a query involving all five fields may not be deterministic.

  If I don't, will I still benefit from indexing the first 3?

If you don't create an index on any of the fields used in a query, the only possible query plan will be a collection scan (i.e. read all documents in the collection to find matching values). That's generally a situation to avoid unless your query is likely to fetch most of a collection. An index on a subset of fields is likely to be more useful than no index.

The overall benefits can only be qualified by comparing the execution stats given your actual queries and representative data. An index on all fields might be more efficient if the additional fields are selective and there would be relatively few index lookups compared to the number of documents to scan. However, additional fields will also increase the size of the index data which might be detrimental if these fields aren't frequently queried.

For a better comparison I would try creating both indexes in a test environment with some representative data and then review the explain results for candidate plans for your common queries:

db.mycollection.find(...).explain('allPlansExecution')

If you have many candidate indexes you may find it easier to use an index hint to force a specific index to be selected in your test environment. For production environments you should only add required indexes.

Another consideration would be matching indexes to actual usage in your deployment. You could add indexes based on slow or inefficient queries as observed in your deployment (or potentially identify unused indexes to remove). For a start on log-based analysis, see Understanding IXSCAN and COLLSCAN in MongoDB logs on Server Fault.


How to handle schema changes in source systems when running an ETL? How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.

I would like to answer this question on the same abstraction level it was asked.

Using conceptual object model of Informatica

Table represented by Source object is used in a Mapping, which enumerates all required Source columns (Ports). When the Mapping is executed by Session (as a part of a Workflow), the Mapping will throw runtime error about missing source Port.

Using the ETL subsystem model of Kimball

Take a look at The 38 Subsysems of ETL by Kimball. Whatever ETL tools you use, you will have following subsystems which play a role in adressing your "variating source" issue:

Extract Subsystem should catch runtime errors. SELECT * is almost never a good idea. If data source changed in a way that caused some columns/tables to disappear, the error / warning should be triggered - and you do need to log/track all errors. See next one.
Error Event Tracking. - aka Logging system. It's crucial, but sometimes ignored by in-house ETL solutions. All the components of ETL should be able to log errors/warnings in uniform way, on different levels of severity. And the messages should be propagated to the responsible ETL manager person. Bare minimum: flat, syslog-like logfiles plus some script to classify & email errors.
Job Scheduler and/or Workflow Monitor. Obviously you will run automated and/or manual ETL jobs, and you should be able to get notified about errors - as well as see current and historical job results including status, rowcounts, warnings of recent jobs.
Problem Escalation.. You need to have an effective path of problem escalation and it's not a technical, but organizational issue. Technically, it's often enough to send an email titled "EDW workflow 001 failed with FATAL error (log attached)". The key is a responsible person and a business process which will push this up.


Migrating SQL database to a new SQL server Thanks for viewing my question. I currently have a database on SQL Server 2005, which is being migrated to SQL Server 2008. Each SQL Server is named differently in SSMS. The old is named GARDB1, and the new GARDB2008. GARDB2008 is in production with everything running smoothly besides this is the last database I need to move over. Now, obviously both servers have different IP addresses, but I can add the old IP to the new server after migration.

My problem is in connecting too MANY different reports we have connected to the database after migration. The connection strings do not include the IP address, only the name GARDB1. There is also a website attached to this database.

How can I migrate this database to the new server while keeping everything connected? It is okay to have downtime while doing this. I tried creating a new alias in SQL configuration manager, but this did not help. What am I missing?

Thanks for any help!

Josh

You can use CNAME in DNS. When you're done with GARDB1, rename it to something else (GARDB1_OLD). Create a CNAME in the DNS server called GARDB1 which should point to GARDB2008. It should solve your problem because GARDB1 will resolve to GARDB2008 now. Ideally what you need to have is a generic CNAME that you can just re-point to a new DB server each time you are migrating. Perhaps next time you plan to migrate, you can implement it. Or you could go with what @SeanGallardy suggested.


How to uninstall Oracle 11G software that are installed in Windows 8? How to uninstall Oracle 11g software that are installed in Windows 8?
I have installed oracle 11g version in local machine.

Oracle has a tool called Deinstall that will remove an Oracle database as well as the Oracle software. Either GUI or command line. Check here on how to use it.

https://docs.oracle.com/database/122/HPCLI/oracle-deinstallation-tool-deinstall.htm


mysql_query() is NOT passing the command to the MySQL server using the C API I have this question asked but after a deeper investigation I realized that hanging is not the issue. After reading this thread I decided to ask a new question.

For example I pass this string to the mysql_query() function:

SELECT 1

When I run several thousand of this command via mysql_query() then the command is NOT submitted to the MySQL server!

I checked the MySQL log file and I couldn't believe it but the command wasn't there and therefore the mysql_query() does never return because it doesn't receive any answer from the server.

MySql is writing the command into the log file BEFORE it gets executed. I tried it with SELECT SLEEP(10).

Note:

I know, it does mean that much but it was working for 3 years without any issue on SLES 11.3/XenServer 6.5. Note: This is an MCVE where it doesn't work as well!

Since I moved it to SLES 12.3 and XenServer 7.4 the problems started.

The C++ code is very simple - I have checked it for 3 weeks before I wrote this question. The point is that this issue starts after ~10k queries. The VM has still ~3 GB RAM free.

Note regarding an MCVE:

I created an MCVE which works for more than 500k executions of the MySQL command.

When I insert this MCVE code into my project and comment my existing main() in order to execute the main function from the MCVE then after few thousand [or even only hundred] of executions suddenly the MySQL command is again not submitted to the MySQL server.

To be more specific: Only static objects of my project are initialized before the embedded MCVE runs.

This must be something very strange regarding these static initializations because no other line of code is running.

Question:

Any idea what is going on? Especially the fact that the command is apparently [why should I doubt the MySQL Server log file] not submitted from the C API client to the MySQL server is really disturbing me.

Technical data:

SLES 12.3 running on XenServer 7.4 as VM
Compiled with gcc 7.3 using -std=c++17 and almost every warning option.
Using the default SLES 12.3 glibc 2.22 with all SLES updates installed.
Connecting to MySQL Community Server 5.7.21
C API client 5.7.21

User Gerard H Pille gave me the correct tip - see the chat.

I had to remove the MYSQL_OPT_RECONNECT setting and now it works. The program runs now for more than 2 months without any issue1.

It were these 2 lines of code according to the MCVE:

bool autoReconnectMySQL = true;
mysql_options( mysqlConnection, MYSQL_OPT_RECONNECT, &autoReconnectMySQL );

Why the setting is causing this issue I cannot answer.

New test results:
MySQL C API 8.0.11 doesn't have this issue anymore.

1 Now, it runs for more than 1 year without any problem.


Certifying New Versions of SQL Server I manage development teams that support a legacy app with a SQLServer backend.  The app is deployed on-premise so our customers maybe run any version SQLServer (minimum 2008).

We have an on-going challenge of certifying that our app runs on new versions of SQLServer or any new service pack.  It's very time consuming for our QA to run a full regression of our suite of products so I'm wondering if anyone faces the same challenge and has found another way to certify new releases of SQLServer as they come out.

We've though of turning on profiler and then hammering the app to capture SQL and just replay that on a new SQLServer instance with the new version. Is there a better way?

BTW, we use a lot of SQL features such as triggers, procs, views, defaults etc.

Thx

Answers originally left as comments

I think you identified it in your question, regression testing. You maybe able to thin down the testing to just corner cases of the functionality. However if you are looking at more than “does it run” and wanting to test “how well does this run” a full test maybe your best option. – Aaron

To me, this is less of a question about the database, and more of an issue with your regression testing. You may want to explore ways to make your regression testing less labor intensive. – AMtwo

Also there aren’t that many things that work in 2008 and break in newer versions; the exceptions are limited to lists documented on Microsoft’s site, like this one for SQL Server 2016. So outside of those items, you should generally expect that moving to a new version should just work. Performance testing is something completely different, of course, and there aren’t any magic shortcuts aside from testing. Baselines are a good start. – Aaron Bertrand


Is it possible to wrap aggregate functions in Postgres? Postgres' string_agg(expr, delimiter) function is great.  But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is what I want 9 times out of 10.  With a non-aggregate function, I could simply do this:

create or replace function public.string_agg(text)
returns text
as $$
    select string_agg($1, ', ');
$$ language sql
immutable;

But since string_agg is an aggregate function, this doesn't work. It complains that the field I'm passing to it needs to be in the GROUP BY clause, which tells me that the query engine doesn't understand that my wrapper is an aggregate function.

I looked at the docs for user-defined aggregates (http://www.postgresql.org/docs/9.1/static/xaggr.html and http://www.postgresql.org/docs/9.1/static/sql-createaggregate.html) but it's not obvious to me how I could define my new string_agg(text) using the existing string_agg(text, text).  It seems I would have to define my new function from scratch, which is more complexity than I want to take on, and I'm also not entirely confident I could mirror the built-in string_agg's behavior perfectly.

So is there some technique for creating a simple wrapper than I'm not seeing?  Or am I going to have to Do More Work?

For all I know, you cannot wrap an aggregate function. You have to write your own aggregate function.

If you write your own function, you may run into incompatibilies when upgrading or porting to another database. So, personally, I wouldn't bother and just add the delimiter to string_agg() on every call. It really isn't that much of a pain.

That said, it's not that complicated to write your own aggregate function, either:

A custom final function:

CREATE FUNCTION f_str_agg_final(anyarray)
    RETURNS text LANGUAGE SQL AS
$func$SELECT array_to_string($1, ', ')$func$;

The aggregate:

CREATE AGGREGATE f_str_agg (anyelement) (
    SFUNC     = array_append
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
   ,FINALFUNC = f_str_agg_final
);

Call:

SELECT f_str_agg (kat) from tbl;

Works for almost any type - except for array types. You would have to modify your aggregate for this. More in this closely related answer on SO.


ERROR: insufficient columns in PRIMARY KEY constraint definition I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration.
Table DDL:
CREATE TABLE c_account_p
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);

When I try the create table, it's throwing below error:
ERROR:  insufficient columns in the PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
SQL state: 0A000

Is it mandatory/necessary that the partition column should be a primary key? cause if I  include load_dttm as PK then its working fine.db<>fiddle
If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY.
Could some please help me to understand this scenario?
Thanks.

Yes, this is required.  There is no concept of an index over all
partitions in PostgreSQL.  The requirement of having the partition key
a subset of the primary key allows us to implement primary keys by
just having individual unique indexes on each partition.
There's mention in section 5.10.2.3. "Unique constraints on
partitioned tables must include all the partition key columns. This
limitation exists because PostgreSQL can only enforce uniqueness in
each partition individually.
CREATE TABLE c_account_p
    (
        billing_account_guid character varying(40)  NOT NULL,
        ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
        load_dttm timestamp(6) without time zone NOT NULL,
        ban integer NOT NULL,
        CONSTRAINT billing_account_pkey  PRIMARY KEY (billing_account_guid,ban,load_dttm)
    ) PARTITION by RANGE(load_dttm);


Before and After update trigger on same table I have a table with 4 columns qty, qtydiff, price and value.

I want a after update trigger that fires when qty row values change (more than one rows).

The Before trigger should read and store the old values from qty and After trigger should subtract old value from new value on qty column, get the difference value in qtydiff and multiply that with price column and update the result in the value column.

The code below is just a concept code that I have not tested yet, I am not sure it would work or not but is there a way to get before and after update to work in the same trigger?

 CREATE TRIGGER [dbo].[Price_Modified]
       ON [dbo].[STOCK]
       BEFORE UPDATE ON STOCK
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            SELECT qty
            FROM STOCK
        END
AFTER UPDATE
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            UPDATE Stock
            SET value = price * qtydiff
            FROM STOCK
            WHERE qtydiff = oldvalue - newvalue
        END
    END

There is no BEFORE trigger in SQL Server. An INSTEAD OF trigger can be used to provide similar functionality but the trigger code would need to perform the UPDATE.

However, an AFTER trigger can be used here by using the INSERTED (new) and DELETED (old) virtual tables to get the values needed for the calculation. The example below assumes a primary key column named StockID with a value that cannot be changed.

CREATE TRIGGER [dbo].[Price_Modified]
    ON [dbo].[STOCK]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Qty)
BEGIN
    UPDATE s
    SET value = new.price * (new.Qty - old.Qty)
    FROM STOCK AS s
    JOIN inserted AS new ON new.StockID = s.StockID
    JOIN deleted AS old ON old.StockID = s.StockID
    WHERE new.Qty <> old.Qty;
END;

GO


How to reuse code in Oracle Script? I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist.

declare
 tableExists number;
begin

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE';

if tableExists>0 then
    execute immediate 'drop  table MY_TEST_TABLE';
end if;

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE2';

if tableExists>0 then
execute immediate 'drop table MY_TEST_TABLE2';
end if;

--Here I create both tables: MY_TEST_TABLE and MY_TEST_TABLE2

My question is:
Is there a way to avoid repeating the code for checking if the table exists? Is there a way that I could call that code as a method passing the table name as parameter?

I believe I could create/declare a function and then use that. But that kind of function is not temporal. I know this is not like C or C++. But I was wondering if there was a way I could have a sort of "private method" for this script to test the existence of my tables and that way I wouldn't have to repeat the code, but the method wouldn't remain after my script was finished.

What is the correct way, if there is any, to achieve what I want?

You can always nest blocks in PL/SQL

DECLARE
  <<define your other local variables>>

  PROCEDURE drop_if_exists( p_table_name IN VARCHAR2 )
  AS
    l_num_tables pls_integer;
  BEGIN
    SELECT COUNT(*)
      INTO l_num_tables
      FROM user_tables
     WHERE table_name = p_table_name;

    IF( l_num_tables = 1 )
    THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
    END IF;
  END drop_if_exists;
BEGIN
  drop_if_exists( 'MY_TEST_TABLE' );
  drop_if_exists( 'MY_TEST_TABLE' );

  <<more code here>>
END;


Does a unique clustered index give array-like lookup performance? Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row.

id | PlanetName
---+-----------
 0 | Mercury
 1 | Venus
 2 | Neptune

Suppose that I am primarily interested in optimizing queries that look up rows by the unique index. For example:

SELECT PlanetName FROM Planets WHERE id = 2

If I were programming in another language and I had the data loaded up into an array, then it would be very quick to perform these lookups by pointer arithmetic. I would like to be able to get the same sort of performance when I carry out the queries in MySQL.

My thinking is:

a clustered index means the data is stored sequentially (i.e., very similarly to how a primitive array is stored in memory)
a unique integer index ensures that SQL does not have to worry about duplicate rows

So if I create a unique clustered index for id, does that mean that MySQL will carry out a pointer arithmetic-type lookup?

Yes and no.

An "array" in a programming language does some trivial address arithmetic to quickly locate the Nth entry in the array.

In MySQL, most indexes are built as B+Trees.  (See Wikipedia)  This structure is more complex than an array, but still the best available.  WHERE id=2 requires drilling down a "tree" of nodes to locate the item with "2" in the "id" column.

A BTree has several advantages over a simple array.  And these advantages are necessary for the generality of database operations.

Database tables are designed to handle an arbitrarily large number of items.  Arrays are limited to what can fit in RAM.  This effectively prevents the address arithmetic of arrays, and forces some other implementation.
You can DELETE * FROM t WHERE id=2.  (Arrays don't allow holes; BTrees do.)  Note that it prevents the use of "address arithmetic" to locate a record.
A BTree index can use a string for lookup -- WHERE name = 'Venus'.  And this works as easily and almost as fast as using numbers.
Since BTrees are "blocks"; they get scattered around and they could become empty.  This leads to the overhead of maintenance of the tree.  Don't worry about this; BTrees are still very fast, on average.
"Clustering" in this context implies that ids 1,2,3,... are "consecutive" and "adjacent" in some sense.  (Or "1,3,...", if id=2 has been deleted.)  In practice, there are about 100 consecutive values sitting in one B+Tree block.  This allows for "range" to be very efficient.  Example:  WHERE name BETWEEN 'Mars' AND 'Venus' would be store alphabetically.
When a 'range' query runs off the end of a B+Tree block, the next block is linked from it.  (This is the "+".)
Technically, array lookup is O(1) and a BTree lookup is O(logN).  But the log is not that big a number -- A BTree for a million rows is about 3 levels deep; for a trillion rows, only about 6 levels deep.  That is, looking up a row in a trillion-row table is only about twice as slow as in a million-row table.
MySQL (InnoDB specifically) requires a unique PRIMARY KEY and clusters it with the data.
Hence, a lookup by the PK (WHERE id=2, if id is the PK) is one drill-down of one BTree to get to the entire row.
A secondary index (not the PK) is implemented as a B+Tree with the key column(s) plus the column(s) of the PK.
Hence, lookup by a secondary key (SELECT * FROM t WHERE name='Venus' with INDEX(name)) is a little more complex.  First drill down the name index to find the id, then drill down the PK+data BTree to find the entire row.
Prevention of duplicates -- This happens when INSERTing or UPDATEing a row.  Effectively, it looks up the row via the PRIMARY KEY (if given -- cf AUTO_INCREMENT) and via every UNIQUE index.  If any of those are a match, you get an error (unless doing INSERT IGNORE).  Otherwise, the PK's BTree and the Unique BTrees are poised to take the new/modified row.  Dealing with potential dups is not free, but cheap.
For a BETWEEN, the first row is accessed (O(logN)), then each subsequent row is O(1).

So, yes, an array lookup takes nanoseconds, which is faster than a database lookup, which takes microseconds (if cached in RAM), or milliseconds (if I/O is needed).  That is the price you have to pay for unlimited size and many more features.


Postgresql not using GIN trigram index when performing non-ASCII LIKE query? Steps to reproduce

Create database

CREATE DATABASE citiesdb
  WITH OWNER = citiesowner
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       CONNECTION LIMIT = -1;

After creating database you can just run code from sql fiddle from the answer by Erwin https://dba.stackexchange.com/a/63202/37108 (http://sqlfiddle.com/#!12/270e2/1) or read additional info at the end of the question.

Run LIKE query with only ASCII characters

EXPLAIN ANALYZE SELECT * FROM city WHERE other_names_lower like '%ele%';

"Bitmap Heap Scan on city  (cost=16.10..64.02 rows=13 width=147) (actual time=0.642..3.303 rows=513 loops=1)"
"  Recheck Cond: (other_names_lower ~~ '%ele%'::text)"
"  ->  Bitmap Index Scan on other_names_lower_trgm_gin  (cost=0.00..16.10 rows=13 width=0) (actual time=0.486..0.486 rows=513 loops=1)"
"        Index Cond: (other_names_lower ~~ '%ele%'::text)"
"Total runtime: 3.439 ms"

Run LIKE query with non-ASCII characters

explain analyze SELECT * FROM city WHERE (other_names_lower like '%желез%')

"Seq Scan on city  (cost=0.00..1693.53 rows=5 width=134) (actual time=33.498..58.688 rows=9 loops=1)"
"  Filter: (other_names_lower ~~ '%желез%'::text)"
"  Rows Removed by Filter: 46673"
"Total runtime: 58.753 ms"

Question

When searching for non-ascii text the engine is using sequential scan instead of GIN trigram index. Why is it doing that and what are alternative ways to construct the index, query or database to speed up the lookup?

Additional info

PostgreSQL 9.2; Windows 8 64-bit.

Part of table definition ([...] are other columns).

CREATE TABLE city ([...] other_names_lower text [...]) WITH ( OIDS=FALSE );

Column other_names_lower contains different names for cities. Rows contain Chinese, Polish, Russian and other character ranges.

Index creation code

CREATE EXTENSION pg_trgm;

CREATE INDEX other_names_lower_trgm_gin
  ON city
  USING gin
  (other_names_lower gin_trgm_ops);

Other settings - query suggested by Daniel Vérité in the comment

select name, source, setting from pg_settings where source <> 'default' and source <> 'override';

"application_name";"client";"pgAdmin III - Narz??dzie Zapytania"
"bytea_output";"session";"escape"
"client_encoding";"session";"UNICODE"
"client_min_messages";"session";"notice"
"DateStyle";"session";"ISO, YMD"
"default_text_search_config";"configuration file";"pg_catalog.simple"
"enable_seqscan";"session";"on"
"lc_messages";"configuration file";"en_US.UTF-8"
"lc_monetary";"configuration file";"Polish_Poland.1250"
"lc_numeric";"configuration file";"Polish_Poland.1250"
"lc_time";"configuration file";"Polish_Poland.1250"
"listen_addresses";"configuration file";"*"
"log_destination";"configuration file";"stderr"
"log_line_prefix";"configuration file";"%t "
"log_statement";"configuration file";"all"
"log_timezone";"configuration file";"Europe/Sarajevo"
"logging_collector";"configuration file";"on"
"max_connections";"configuration file";"100"
"max_stack_depth";"environment variable";"2048"
"port";"configuration file";"5432"
"shared_buffers";"configuration file";"4096"
"TimeZone";"configuration file";"Europe/Sarajevo"

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected.

SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C".

There is probably something wrong in your setup that is not in your question.
 Run (takes some time for big tables and an exclusive lock!):

VACUUM FULL ANALYZE city;

And try again ...


How to trigger an event when row column crosses now()? Is it somehow possible in postgres 12 to trigger an event when a table with an tsrange column crosses now(), or someone insert something that where the tsrange is prior to now()?
Triggers, as far i know, only triggers when someone insert or updates a row.
Triggering when someone insert a row which has passed now, seem to doable
With trigger that triggers on all inserts, but i am not sure how to trigger an event, when a given timestamp in that row crosses now..
And can both trigger scenarios be combined into one?

You'd have to schedule a program to run regularly and process the table.
Usually it is better to implement such logic in the SELECT statements.


Are read replicas heavy reading in affect masterDB? We have a very heavy load  in Read side with large DB tables in 2 hours per day that affect to Db so as solutions I have created read replica for it.

If application fetches large heavy data on read replica, is MasterDB affect with that havy load or it will work as it is.

From your tags, I guess its for AWS RDS Aurora. Generally, Aurora's design differs from native databases. It has a distributed storage system.

Of course, if you huge select will affect the master when you have MyISAM in place. But Aurora completely designed with InnoDB. So it won't.

Not sure whether your replica and master are in the same AZ. Its a good practice to use different AZ in Aurora.

Due to its PIOPS, your massive select load will not affect your master anyway. If you have a predictable load on a particular time, use Aurora autoscaling to share the loads between multiple read replica.


Sql Server Enterprise 2008 R2 Partition Switch with Xml Index Ok, sql db partitioning and switching doesn't seem to be a highly discussed topic, but it is something I'm implementing.  I've had success in all areas of partition functions, partition schema, file group assignments, etc.  Up to this point I haven't had any issues with partition switching until a pesky xml field was found to be killing performance.  So I attempted to add a simple primary xml partition, but now all the partition switching is failing. Below is a mock of my table structure in play.

CREATE TABLE [MySchema].[DataTable](
    [PartitionKey] [varchar](12) NOT NULL,
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DataTabe_Id] DEFAULT NewId(),
    [ClientId] [smallint] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [ResponseValue] [nvarchar](255) NULL,
    [ResponseValueXml] xml NULL,
 CONSTRAINT [PK_DataTable] PRIMARY KEY CLUSTERED
(
    [PartitionKey] ASC
    , [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  =ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_MySchema(PartitionKey)
) ON PS_MySchema(PartitionKey)

Some notes on this is that the partition key is a complex / computed key based on clientId, year, and another value.  The partition schema is built on a partition function that groups clients + dates, which are then put into seperate file groups (by year) - makes sliding window method easy to manage.

The general flow of things is to create a work table in the same file group, use alter table to switch the desired partition out of DataTable to the work table.  Do some stuff against the work table, and switch it back when done.  This works great... Until I add a primary xml index on ResponseValueXml.  The alter table / switch requires that only the source of the switch be allowed to have an xml index.  So, it appears I'm allowed to switch it to the work table (so long as the work table doesn't have an xml index). But I can't seem to switch it back.  If I used the DataTable as the source, it errors because the target cannot be empty.  If I use DataTable as the target, it errors about the xml index only allowed on a source table.

What am I missing here?  How am I supposed to manage this index when I'm not allowed to switch them?
Any guidance is appreciated.

This is straight from BOL:  Transferring Data Efficiently by Using Partition Switching

XML columns must have the same schema. Any xml columns must by typed to the same XML schema collection.
No XML indexes allowed on the target table. There can be no XML indexes on the target table.

It appears that you need to ensure both XML columns are typed against the same schema and drop the XML index from the target partition, if applicable.
I have never personally performed a partition switch when an XML index is involved, so there could be more to it than this.
Hope this helps,
Matt


Query to find users with passwords same as their username in Oracle I'm writing an Oracle audit script where I need to find users in a database who have set their passwords to be the same as their username, e.g. dbaadmin/dbaadmin. The query would need to get the list of users in the database & then check if their passwords is same as their respective usernames.

Can anyone help me out with this query?

PS: I'm a Database newbie.

I don't think this is possible for current versions of Oracle. What I understand happens is that Oracle generates an instance-specific salt and adds this to the passwords being hashed using the SHA1 algorithm, the result of which is stored as the hashed password. See for example http://marcel.vandewaters.nl/oracle/security/password-hashes for information about this algorithm.

This means that password hashes are installation specific, and I don't think it's possible to generate the expected hash value for a given username/password combination.

What you need to do is as @elfcheg suggests to add a password_verify_function on all profiles, or to a single profile, and assign this profile to all users.


Mariadb master master replication supported by HAPROXY for automatic failover We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to promote slave in case of failure.

Now we want to implement HA PROXY for automatic fail over. In case Master A goes down application should switch automatically to Master B.

Now my question is that if Master A goes down and applications start serving from Master B. But when Master A comes online and is not in sync with Master B what will HA PROXY do, will applications get connected to Master A instantly or HA Proxy will check synch status and let Master A synch up with Master B and then allow applications to connect with Master A.

What parameters need to be added in order to achieve this.

HA PROXY Configuration:

With your current configuration, if Server A goes down, HAProxy will switch to Server B. But as soon as Server A is back up and running on port 3306, HAProxy will switch back to Server A, which is not what you want.

The best way to handle this use case is to use the stick-table parameter.

With a stick table based on the destination IP address, HAProxy will redirect connections to server B even when server A is operational again.

It will only switch back to server A when you restart the HAProxy service or if server B fails or if you disable it manually.

Here is an example of a configuration:

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    option mysql-check user haproxy_check
    balance source
    stick-table type ip size 1 nopurge
    stick on dst
    server MASTER_A X.X.X.X:3306 check
    server MASTER_B X.X.X.X:3306 check backup

You will find more information about this on this HAProxy blog post and in HAProxy documentation (see stick-table, stick on and peers options)


How do I do a join and exclude certain records? Table STUDENT has columns ID, LASTNAME, FIRSTNAME

Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID

Students can have many teachers. Teachers can add students to their class.

I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.

What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.

SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID  ???

This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?

I'm coding in PHP and using DQL, but any help on just the query part would be great.

Hope that makes sense. I confuse myself.

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher.  In fact you could just make the the PK and drop ID.

This is TSQL so it may be wrong for mysql

SELECT S.ID
     , S.LASTNAME
     , S.FIRSTNAME
  FROM STUDENTS S
  LEFT JOIN TEACHERS T
        ON T.STUDENT_ID = S.ID
       AND T.TEACHER_ID = $teacherID
 WHERE T.TEACHER_ID is null


What are the Oracle Dataguard Implications of Migrating to a PDB? If I have an OLTP database with two physical standby instances (one remote) what are the implications of migrating the primary DB to being an Oracle 12c PDB? i.e. Does the process of migrating to being a PDB send a sensible EOR record via Dataguard that will leave the data files on the physical standbys in a state that can be picked up from or would it require a complete backup of the whole CDB with the new PDB plugged in on the primary to be restored to each standby before Dataguard could be resumed?

The Primary DB would already be a 12c non PDB.

The notes about this in Oracle 12.1.0.2 state that it is not required to move/copy the files from the primary during the plugin. The note also states that in this release the copy of the standby files of the source can be postponed using the standbys=none clause. This makes it possible to plugin a large primary database in a primary container database and perform redo apply/recovery later, when all files are copied into to correct file structure.

seeHandling of Standby Databases during plugin note 1576755.1

My conclusion is that in 12.1.0.2 the standby files need to be copied but this is not perse during the plugin of the primary database so it does not cause any extra downtime.

Still I would like to see this tested before I would do this on production.


SSRS schedule failure intimation I am scheduling a report using SQL Server Reporting Services Subscription option.
Suppose my rdl file is corrupted or my stored procedure has got some problem.
Then the report won’t generate in its next turn.
What can I have to get the intimation that the report does not generated properly?

Can I configure to get a mail on report generation failure

SSRS report subscriptions generate job entries in SQL Agent.  Find the corresponding agent job and set email alerts through it's properties for failure.


How to create Parent record on Child record creation and assign Parent ID to Child record in MySQL I have 2 tables - [stakeholders] and [organizations].

FK from [organizations.stakeholder_id] to [stakeholders.id].

I want to create [stakeholder] record automatically when new [organization] is created AND to assign newly created [stakeholder.id] back to [organizations.stakeholder_id] in the same transaction.

Is it possible and how?

No it's not possible to create a child record without creating the parent record, but you can always(even in programming languages) insert into the parent table first and then insert into the child table.

insert into parent_table(id) values(1);
insert into child_table(childid,name,id) values(1,"x",1);

For example in any programming language if you have the id number that you want to add to your database you can use it in both insert statements.


Can I sync an existing database using a .dmp file? I have two oracle databases (production and lab). When I created the lab database I imported a .dmp file from production to copy the data. Now that the database has been running for a while the data is out of sync and I'd like to import again. What's the easiest way to do this?

I tried using imp again but it shows errors now since the tables are already there. I could delete the data files and re import I suppose.

Edit: This is what I've tried:

$ imp user/password file=MyExportFile.dmp

following statement failed because the object already exists:
CREATE TABLE ...
...
Import terminated successfully with warnings.

Next time, instead of "deleting everything", you can tell imp to ignore table creation error and still load the data by specifying the IGNORE=y option:

imp user/password file=MyExportFile.dmp IGNORE=y

There are few other considerations that you should make when importing into an existing schema, as outlined in the manual. Specifically, you may need to disable referential integrity constraints if they exists, otherwise some data may be rejected.


Mysql database connector table or keys This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables.

What would be best way to connect them? I have a connector table with the IDs of the Authors and ID of the titles so you can left join them, is there a better way to do this? i looked at having an extra column in the title table which would allow the author ID but this would be problematic for multiple titles to a single author.

Does anyone have any suggestions of a better way of doing this ( instead of a connector table)?

any help would be greatly appreciated.

Thanks

James

A connector table is the standard way to solve this sort of problem.

As you say in your question, if you have a TitleID column in the Authors table it restricts you to one Title per Author, unless you add a Title2ID, Title3ID, etc columns, which for someone like Stephen King might mean quite a few columns.....

Also, by having the  connector table you can deal with cases where a Title has more than one Author.


how to take postgres server backup skipping some of databases? I have PostgreSQL server version 9.5. Contains 20 Databases.

I want take server backup skipping of some 10 databases.

Am using pg_dumpall, but it was taking full server backup.

thanks,

Instead of using pg_dumpall, just use pg_dump and specify which databases you want to dump.  One possible way to do it is:

for db in <space-separated list of database names>
do
  pg_dump -U <user> -h <host> ${db} > ${db}.dmp
done


How to identify cross-database queries in SQL Server In preparation for a migration project we are trying to identify any queries that select data across databases.

This can be of the form:

SELECT fieldA, fieldB
FROM dbo.TableA a
JOIN DatabaseB.dbo.TableB b ON b.ID = a.ID

or

DECLARE @resultFromDatabaseB VARCHAR(50)

SELECT @resultFromDatabaseB = b.ID
FROM DatabaseB.dbo.TableB

SELECT fieldA, fieldB
FROM dbo.TableA a
WHERE a.ID = @resultFromDatabaseB

or any other variation (including INSERTS/UPDATES/etc...)

Unfortunately the majority of queries are not stored procedures so I cannot do static analysis of the SQL text, my current plan calls for recording the cached query text and doing static analysis on them.

Are there any alternatives to this approach?  ie: use of extended events/trace/audit?

I would set up an Extended Events session to capture lock acquired events and stream the events with the API.

First of all the session:

CREATE EVENT SESSION crossdb ON SERVER
ADD EVENT sqlserver.lock_acquired(
    SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(
        sqlserver.request_id,sqlserver.session_id,
        sqlserver.sql_text,sqlserver.tsql_frame
    )
    WHERE (
            [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[not_equal_uint64]([database_id], (32767))  -- resourcedb
        AND [package0].[greater_than_uint64]([database_id], (4)) -- user database
        AND [package0].[greater_than_equal_int64]([object_id], (245575913)) -- user object
        AND (
               [mode] = (1) -- SCH-S
            OR [mode] = (6) -- IS
            OR [mode] = (8) -- IX
            OR [mode] = (3) -- S
            OR [mode] = (5) -- X
        )
        AND [database_name] <> N'distribution'
    )
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO

Then, you could group the events on transaction_id and tsql_frame. The groups that touch more than one database_id are the ones that issue cross-database statements. You can then extract the statement from the sql_text action, using the offsetStart and offsetEnd from tsql_frame.

I have a similar example on my blog here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/


Extract part of a string in Postgres I have a question how to extract a substring.

Here an example string:

{{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale},{transfer-to,10,kb,Kai Büsing},{conn-to,66,ConTeach Zentrale,ConTeach Zentrale {disc-to,,,},{rel-from,66,ConTeach Zentrale,ConTeach Zentrale}}

The goal is to extract '66,ConTeach Zentrale,ConTeach Zentrale' between '{conn-to,' and '},'

Any ideas?

You can use the function substring() with a regular expression. But you need to define your requirements exactly. Like markp commented, there is ambiguity with multiple matches.

SELECT substring($string FROM '{conn-to,(.*?)},')

The part between parentheses is returned. This returns the first match, and the non-greedy quantifier *? prefers the shortest match.


Getting table definitions on the command line in MySQL I am new to MySQL. I am trying to get table definitions from the database server (4.0.26).

I tried below steps to get information, running cmd as an administrator:

c:\> cd mysql\bin\
c:\mysql\bin>mysql -u username -p
password: password

mysql>show database

    -> (nothing comes up)

After closing and opening, same steps till password:

mysql>use database
Database changed

mysql>show tables

     -> (nothing comes up)

I need to check two table definitions. What am I missing?

you need to add semicolon at the end of your command.

     show databases;

     show tables;


Near identical MySQL deployments behaving very different - High CPU Usage problem So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text stored in MySQL tables.
Four of them have no problems what so ever. One is struggling a LOT under the same or somewhat less load than the other four.
I cannot understand why this is.
Things I've done so far:

Checked slow queries. All queries uses indexes and are in the realm of 0.0008 Sec execution time i.e. very fast
I've noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT, so much so, I've turned off the UPDATE's that were there, for this instance. Bear in mind that these UPDATE's doesn't cause a blip on the other servers.
Tried to eliminate external factors i.e. noisy neighbours (moved host) etc.

Worth noticing is that the machines are deployed the same i.e. a vanilla Debian 10 installation with a LEMP stack, nothing out of the ordinary at all.
Still, the problem persists. I can see the load of the machine struggling to keep under 1.00.
The other machines are in the 0.10 - 0.20 range all the time.
Looking at CPU for the MySQL process on this machine (with 2 CPU cores as the other machines have as well) it is quite often above 100%. The other machines are never - EVER - over 60% for the MySQL process.
So, any help is much appreciated.
Please do let me know if you need me to run a command that you need to see the output from in order to help.
Thanks.
EDIT  Spelling and clarifications

This sounds like a configuration issue. INSERT/UPDATE statements result in writes, so you should look at the write path. Check the following:

Is write caching on the disks enabled?
Are the disks configured the same way (RAID geometry, file system, mount options, controller)?
Are your InnoDB transaction logs the same size and configuration (innodb_log_% options)?
Are your disk flushing options configured the same (sync_%, innodb_flush_%)?


Product_ID in my Invoice table I'm doing a uni assignment.
I have

a stock table where stock is assigned an ID (product_ID).
a sales table where the stock is purchased.
an invoice table where sales are processed.

I want the invoice entry to contain many product_id's and their quantities.
I currently have "Invoice_id, Customer_id, Staff_id, Product_id, Quantity, Price, Total Cost".
Doing it this way I can only have one product_id per entry. I want there to be multiple entries under one invoice_ID.
Am I thinking of this the right way? When you get an invoice it says the product on it and how many of them you bought.

For what you're trying to accomplish, you'll want to normalize your data into two Invoice related tables. One for header information, e.g. Invoice_Id, Invoice_Date, Customer_Id, TotalCost; and one for the invoice line information, e.g. InvoiceLine_Id, Invoice_Id, Product_id, Quantity, Price.
The InvoiceLine table will have one row per product that was purchased on this invoice. It will have a unique InvoiceLine_Id per row (key field), and the InvoiceId field in it can be repeated across rows because it will be a many-to-one foreign key relationship to the InvoiceHeader table.


Incremental Database/Data Change Restore I have vendor production backup that needs to be restored onsite daily. The most frustrating part is that their installation is on SQL 2008 but the DB mode is SQL 2000. sigh!!!

The database in our site will primarily be readonly. The vendor runs any required updates/data changes to the DB on their site during the day and will send the backups to us on a daily basis. It is a smaller bak file now but will grow over time. The goal is to restore incremental updates only.

Possible workaround that I could think of are the following:

Keep the DB in our side in read only mode (after full backup is restored) and request the vendor to give us the diff backups every day. Of course if they take a full backup , we are back to square one.
Partition the database and request the vendor to give the partition group backups. This may be difficult because not all tables may be qualified for partition.
Replication could be an option but the vendor is not ready for this.
Bulk insert for individual objects could be an option but again the vendor is not ready for it.
CDC feature could possibly help here but again not in SQL 2000.

If you can share any other ideas that you could think of, that would be very helpful. (I agree the first step should be to asking them to upgrade)

Thanks for all your help.

editted based on feedback in comments

If you could stay readonly than a good alternative to replication
could be log shipping. The vendor would create transaction log
backups. And send those backup to you. You then apply those
backups.You can't restore differential only, you would still need to
reply a full first. Another benefit of (delayed) logshipping is that, since the source is at the vendor (where you have no control over what they do with it, you might want to build in some buffer (in time) that when they make a huge mistake, you simply pause the logshipping redo, so you still have a good copy)
For restoring filegroups/files you need transaction log restores as well. It can work but it highly depends on the growth rate vs the transaction log rate if I would prefer this above option 1, simply log shipping. Furhtermore, partitioning your database and table just for the sole purpose of solving your replica problem wouldn't be the route I would pick first but I have some questions for you later.
Replication sounds like a very good option if you think the restoring of transaction log backups becomes to much work.
What prohibits sql 2000 from doing Bulk Inserts? However, if you think logshipping is tedious, keeping track of updating every single object sounds like a potentially more troublesome strategy.
I have no experience with cdc. somebody else has to help out on that.

Couple of questions for you:

How big is the initial database size?
What will be the daily, monthly growth rate?
What will be  the daily growth rate of the transaction log?
is it possible to have both locations in the same domain?
Is it possible to build a secure connection between the two locations?
What is the bandwidth between the to locations? what is the latency?

If we have some numbers, it's easier to provide you with a better advise.


Datawarehouse - Dimension, Fact, or Degenerative dimension I am building a warehouse for a billing system that bills a number of services/utilities on the same invoice, as such I am having real trouble conforming to a recognised design method for the warehouse. I think I still very much have an OLTP hat on.

As an example a single invoice could be produced containing charges for electricity, gas and mobile phones.

All three components of the bill, themselves have two components, fixed charges, and usage based charges. Even simplified to just a mobile phone it still means to store the level of detail required for the business I need 2 fact tables:

FactMobileFixedCharge
FactMobileCalls

Each of these go onto a single invoice, in addition to this there are transactions at an invoice level too, so I now need a further fact table:

FactInvoiceTransaction

The basic schema for this simple example is as follows:

All the reading I have done approaches the InvoiceLineItem example to resolve the issue of an invoice appearing like a fact and a dimension, where the facts of the invoice are essentially propogated to the child table, causing some redundancy but conforming to standard practises.

In the production billing system itself there are 15 tables that contain items that appear on an invoice, a summary table that contains invoice level charges and a summary of the charges in the other 15 tables by category, and the invoice table itself.

I don't feel that my scenario matches this though, because the invoice has a number of different things that also need to be reported on (Invoicing address, marketing material on invoice, payment method, invoice format, email, online or paper billing, balance brought forward, payments since the last invoice etc). While this is acceptable redundancy if I had one or two tables it seems like a lot for 15 fact tables.

In addition to being a lot of redundancy when there are multiple tables, I would also still need to link FactMobileCall and FactMobileCharge to get records for the same invoice, and I don't beleive a degenerate dimension is a good way to link the two.

Basically this all points towards Invoice being a dimension, especially as there are no measurements on an invoice level, however it still feels like it should be a fact as there will only ever be new records on each import.

Which leads to my questions:

Am I correct in thinking that an Invoice in this scenario should be considered a fact?
If so is it acceptable to have further facts referencing this fact, or is there a better way to approach this?
I can't believe I am the only person who has encountered a situation where the InvoiceLineItem examples don't really seem to fit. Is there a recognised way of dealing with this?

For further information, my warehouse schema currently looks likes this (where I have conceded to Invoice being a fact like dimension):

This is how I would approach it myself. Remember to think of your Facts as "actions" or "verbs" and your dimensions as the the descriptors of your facts.

So your invoice and invoice line items are both facts. One approach to dealing with this is what you mentioned, making the fact table to the granularity of the invoice level line item. This will lead to redundancy of certain things like you mentioned. This if fine for things as an billing address dimension and certain invoice level facts such as a tax could even be broken down to the line level item and it could then aggregate back up to the invoice level amount.

This approach can break down if you have invoice level facts that don't break down nicely. For example, are customers allowed to have more than one payment method? Could there be more than one type of marketing material attached to the invoice? Does the marketing material correlate to specific line items?

Asking these questions can definitely lead you to creating multiple facts such as FactInvoiceLineItem, FactInvoice, FactPayments, etc. that you end up tying together with something like invoice number. Doing this will give you more flexibility to analyze specific facts better.

If doing this approach and you are building cubes off the fact tables, I would recommend using views to load your cubes. This way you could have a high level view for the invoice that joined your various fact tables and had measures on there such as number of line items, number of payments, etc.

All that to say, I believe going that route is much better than splitting out your fact tables that have the same granularity.

Feel free to respond with more details if you need more help hashing it out.


Unable to revoke permissions for 'NT AUTHORITY\SYSTEM' on SQL Server 2016 I have tried everything I know to do and spent several hours Googling without success. I want to revoke the SHUTDOWN and CONTROL SERVER permissions for the login NT AUTHORITY\SYSTEM.

When I run this

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

...the output is:

Things I have tried:

Deleting the Login entirely,
Executing the following (with the different permissions listed):

USE master;
REVOKE SHUTDOWN FROM "NT AUTHORITY\SYSTEM";
GO

Even after I delete the login, and execute the fn_my_permissions SP, it still lists the permissions from the image above.

Any help I could get on this would be great.

This should revoke the permissions

REVOKE CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
REVOKE SHUTDOWN TO [NT AUTHORITY\SYSTEM]

Tests

use [master]
GO
GRANT CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
GO
use [master]
GO
GRANT SHUTDOWN TO [NT AUTHORITY\SYSTEM]
GO

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

Result

... (34 Results)

REVOKE CONTROL SERVER TO [NT AUTHORITY\SYSTEM]
REVOKE SHUTDOWN TO [NT AUTHORITY\SYSTEM]

EXECUTE AS LOGIN = 'NT AUTHORITY\SYSTEM'
SELECT * FROM fn_my_permissions(NULL,NULL)
REVERT

Result

Extra info

If you are ever unsure about the syntax, an easy one (in my opinion), is selecting what you need inside of the GUI and then scripting it out using the 'script' button.

For example in this answer, I added the grants for the CONTROL SERVER and SHUTDOWN to the login NT AUTHORITY\SYSTEM , Scripted it out, and then removed them in the GUI and scripted them out again to get the revoke statements.
I am against executing stuff through the GUI though, since it is easy to overlook things, and it has proven to be buggy.


Connect to PgAdmin III via localhost I just want to login for first time, from pgAdmin III via localhost on openSuSE 13.1 64x.
My pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection:

local   all   all   trust

That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation:

  The host is the IP address of the machine to contact, or the fully
  qualified domain name. On Unix based systems, the address field may be
  left blank to use the default PostgreSQL Unix Domain Socket on the
  local machine, or be set to an alternate path containing a PostgreSQL
  socket. If a path is entered, it must begin with a “/”. The port
  number may also be specified.

Bold emphasis mine.

More about connecting without password in this related answer on SO:
Run batch file with psql command without password


Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.

Even though the guilty records have been removed or corrected, scanning still reveal the records in the .ibd file, as well as the ibdata1 and ib_logfiles.

I have optimized the affected table and so the .ibd file is clean now; but I still have the issue of the ibdata1 and log files.

Doing a full mysqldump of all databases is a last resort as the data set if quite large.  Since we are already using per table tablespaces, can I simply stop mysql, move the ib files and then restart and reset the files in that manner?

You cannot (or, you can, but with pain & risk).

For the ib_logfile[01] - you needn't worry: these are cyclic files, so your info will eventually be overwritten (and if you want to rush it, why don't you invoke a gazillion UPDATE foo SET bar=1 WHERE id=1 on some dummy foo table?

But the ibdata file cannot just be dropped.
Nevertheless you should (no guarantees held) be able to overwrite the file at those particular positions where you find your sensitive data. If you can identify all of them - overwrite them with spaces/whatever (say these are credit card numbers -- overwrite with "0"s).

I wrote on how you can use a replicate to convert all your tables to MyISAM (Assuming no foreign keys), restart, throw away your ibdata file, convert all back to innodb, get on with your life. It's a lengthy process as well. See: Getting rid of huge ibdata file, no dump required


Restoring a database using Postgres data files instead of .sql files I backed up my Ubuntu 14.04 system using rsync to a USB stick, but apparently it wasn't formatted for booting. The original disk has been erased and I put a fresh install of Ubuntu on it. In migrating my data I forgot to dump one of my postgres databases to SQL, and I need to migrate it to my new install of Ubuntu.

I'm considering formatting a bootable disk and then attempting to rsync my old system to that for disk so I can boot and dump my database to SQL. But that's a discussion for a different forum.

I'm wondering if it's possible to use the data files from my old system to restore my database (i.e. copy /var/lib/postgresql/9.3/main) from my old install to my new install? Has anyone ever done this? I did try just copying the data file but it caused Postgres to break beyond repair. I had to delete it entirely and reinstall.

I know it's possible with MySQL, but I'm less familiar with Postgres.

Thanks!

Rsync is a supported backup method for postgresql.
However it has some caveats that you need to be aware of. Namely the database should be offline when the sync is done.

So if your database was offline when you synced, you could restore it jsut fine.


Trigger before update a fields in a table I want to know how can I code a trigger that verify a fields before updating it. For example I want to prevent a date update, let say, not to modify records with date before '2020-01-01'.

Thanks in advance.

The answer will vary depending upon the RDBMS you are using. Also DBAs & developers have different preferences.

Personally i would choose to apply this in a stored procedure rather than a trigger. Triggers dont scale well and can give unpredictable results with bulk updates.


Is "--one-database" safe to use for importing from a dump? If I make a full dump using mysqldump --all-databases > dump.sql and then I want to import just a single database, I can use mysql --one-database db1 < dump.sql.

According to the documentation, This option is rudimentary and should be used with care because statement filtering is based only on USE statements.  Here is an example of how this could be dangerous:

mysql --one-database db1

DELETE FROM db2.t2;
USE db2;
DROP TABLE db1.t1;
CREATE TABLE db1.t1 (i INT);
USE db1;
INSERT INTO t1 (i) VALUES(1);
CREATE TABLE db2.t1 (j INT);

But this scenario seems very unlikely because I have never found a dump to contain statements that specify a database.table syntax.  So the question is, if I make a full dump as written above, is there any risk of using the --one-database option to restore?

The only risk is time and missing out on trigger stored procedures.

Normally, mysqldumps are done in alphabetical order.

When loading a dump, expect databases aardvark, alligator and antelope to load early. Any database like zoo and zebra would require time because --one-database does not force a mysqldump to load the specified database. The entire dump must be read until the specified database is in use.

For example, mysql --one-database zebra will read the entire mysqldump but start loading when it gets to the line the dump that says

USE zebra

Then, all INSERT INTO commands are processed until the EOF or until it see another line like

USE zimbabwe

Some developers I know would wait until the database they chose is loaded. Then, hit Ctrl-C. I would not risk that. You should make sure that all triggers and stored procedures are loaded as well.

If you cannot wait and want to Ctrl-C, login to mysql while the dump is still loading and make sure all tables, views, triggers, and stored procedures have been loaded.


SQL Query to look for nulls from two different tables while adding in a new column that states what table its from I need help with a query that I am trying to make. I have two tables, Orders and Process and I am trying to find instances where the OrderName and ProcessTitle in each table is null and I want to add in a column in the query to state what table it is in.

Orders Table

OrderName                   OrderNumber        OrderStatus
-------------------------------------------------------------
 xx                                   1          Active
 xx                                   2          Active
 bbb                                  3          Active
 abc                                  4          Active
 NULL                                 5          Inactive

Process Table

 ProcessTitle                   ProcessNumber   ProcessStatus
-------------------------------------------------------------
 abc                                  1          Active
 abc                                  1          Active
 db                                   2          Active
 BG                                   3          Inactive
 BG                                   3          Inactive
 Null                                 4          Active
 Null                                 4          Inactive

Here is what I have so far. I queried both tables to show where the OrderName
and ProcessTitle to display where there are nulls by using the union function.
I am still new to SQL and am still learning but I wasn't sure how to search
this question. I feel like I should be using a case statement to achive this but how
would I reference both tables? Thank you in advance.

Select OrderName,
       OrderNumber,
       OrderStatus
From Orders
where OrderName is null
UNION
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus
where ProcessTitle is null

Is there a reason you can't simply hardcode a literal of the name of the SourceTable in each select?

--Demo setup
Declare @OrdersTable table (
  OrderName VARCHAR(4),
  OrderNumber INTEGER,
  OrderStatus VARCHAR(8)
);

INSERT INTO @OrdersTable
  (OrderName, OrderNumber, OrderStatus)
VALUES
  ('xx', '1', 'Active'),
  ('xx', '2', 'Active'),
  ('bbb', '3', 'Active'),
  ('abc', '4', 'Active'),
  (NULL, '5', 'Inactive');

Declare @ProcessTable table (
  ProcessTitle VARCHAR(4),
  ProcessNumber INTEGER,
  ProcessStatus VARCHAR(8)
);

INSERT INTO @ProcessTable
  (ProcessTitle, ProcessNumber, ProcessStatus)
VALUES
  ('abc', '1', 'Active'),
  ('abc', '1', 'Active'),
  ('db', '2', 'Active'),
  ('BG', '3', 'Inactive'),
  ('BG', '3', 'Inactive'),
  (Null, '4', 'Active'),
  (Null, '4', 'Inactive');

--Solution
Select OrderName,
       OrderNumber,
       OrderStatus,
       'OrdersTable' as SourceTable
From @OrdersTable
where OrderName is null
UNION ALL
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus,
       'ProcessTable' as SourceTable
From @ProcessTable
where ProcessTitle is null

| OrderName | OrderNumber | OrderStatus | SourceTable  |
|-----------|-------------|-------------|--------------|
| NULL      | 4           | Active      | ProcessTable |
| NULL      | 4           | Inactive    | ProcessTable |
| NULL      | 5           | Inactive    | OrdersTable  |


How to create a Relation between a table and a many-to-many relation table I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:-

Skill that have predefined levels assigned to it, such as junior, senior, manager.
Staff will be assigned to certain skill’s level.

For example inside ym system i will have such an info; staff A is connected to a skill named “Sql server 20082” through Senior level.

Currently I have set the relationship between the tables as follow:-

But I have this concern, currently I am storing the relation between Skill & level though a many-to-many table (Skilllevel), and I am storing the relation between staff and skill though a many-to-many table (SkillLevelStaff). But on the skilllevelstaff table I am storing the same relation skillId+LevelID , which already defined inside the  SkillLevel table.
So  can anyone advice on this please? should i for example create another system generated ID for the SkillLevel table and setit as the PK instead of the SkillID+LevelID?
Thanks

In general I see nothing wrong with a many-to-many-to-many table where you have all three IDs.  In this case however, you have additional columns on the SkillLevel table meaning that the Skill+Level pair has it's own unique properties.  Given that I would go ahead and create an additional SkillLevelID column and use it in your SkillLevelStaff table.

Whether or not you use the SkillLevelID as the primary key is a bit trickier.  Honestly in my opinion, in the end it doesn't matter.  You will need at least a unique key on the SkillLevelID if you are using foreign keys and you will want at least a unique key on the SkillID + LevelID pair in order to enforce the business rule that the combination is unique.  It won't really matter which one is the primary key.  The clustered index on the other hand will matter.  If you do most of your queries joined to the SkillLevelStaff table then it might be a good idea to use the surrogate key (SkillLevelID) and it will certainly avoid fragmentation.  On the other hand something like SkillLevel probably doesn't get updated frequently so the amount of fragmentation caused by using the natural key (SkillID + LevelID) is probably going to be minimal. It will however be better if the majority of your queries use SkillID or SkillID + LevelID as part of the WHERE clause (or joining to those tables).  You will probably want to do some experimentation there.


DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error message indicates that DB2 is autogenerating the id 1 and trying to insert, which causes an error since that primary key already exists.

This is the error message:

  One or more values in the INSERT statement, UPDATE statement, or
  foreign key update caused by a DELETE statement are not valid because
  the primary key, unique constraint or unique index identified by "1"
  constrains table "PROJECTMGMT.TIMESHEET" from having duplicate
  values for the index key.

This is my primary key:

"TIMESHEET_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
   START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647
   NO CYCLE CACHE 20 NO ORDER
),

To try to troubleshoot the issue, I changed the TIMESHEET_ID column to GENERATED BY DEFAULT so that I could manually enter a primary key value and the query worked.

I am fairly new to DB2, and this has me stumped. I haven't been able to find any fix for this.

Determine the largest current ID value:

select max(TIMESHEET_ID) from PROJECTMGMT.TIMESHEET

then reset the identity value:

alter table PROJECTMGMT.TIMESHEET
  alter column TIMESHEET_ID restart with <whatever the max value is + 1>


Why can a select statement acquire more than 1 Sch-S lock on one table? I have a repeating deadlock in my production environment. I cannot reproduce it in staging.

Script 1 is a scheduled data import procedure.

-- Create and populate new_TableImported

if object_id('old_TableImported') is not null drop table old_TableImported
set transaction isolation level serializable;
set xact_abort on;
begin try
    begin tran
        if object_id('TableImported') is not null exec sp_rename 'TableImported', 'old_TableImported'
        exec sp_rename 'new_TableImported', 'TableImported'
    commit
end try
begin catch
    if (xact_state() <> 0) rollback tran;
    throw;
end catch
set transaction isolation level read committed;
if object_id('old_TableImported') is not null drop table old_TableImported

Script 2 is a scheduled data export procedure: just select from a view.

CREATE VIEW [export].[vSomeDataNeededOutside]
AS
select
    t.*,
    e.SomeField
from dbo.OneOfMyTables t
    left join (
        select ...
        from TableImported
        group by ...
    ) e on ...
where ...

According to data collected with SQL Server Profiler.

Select (2) starts. Sch-S lock is obtained on TableImported.
Data import (1) runs up to sp_rename block. Sch-M lock is requested, and the query waits.
(2) for some reason requests additional Sch-S lock on TableImported.
Deadlock. (1) waits (2) to release the first Sch-S lock, (2) waits (1) to acquire the second Sch-S lock.

What is going on? Why do select try to obtain the second lock of the same type on the same table?

Your query is creating more than one lock because your derived table is getting executed more than once.

Why the sub-query is being executed more than once is another issue?

Check your execution plan.   its quite possible the Estimated Number of executions on TableImported  matches closely to the Estimated Number of rows for OneOfMyTables
There is some discussion around this here

If you could change the the left join to an inner join, it would only scan (or seek) TableImported once.

As for the difference between Staging and prod... or a solution ¯\_(ツ)_/¯  (sorry)


Postgresql streaming replication - choose databases to sync I have been reading on the Postgresql replication streaming (as described here) and was wondering if it is possible to choose which databases are synced, or if it has to be the entire DB server.  E.g. if I have database1, database2, and database3 in my master server, can I have my slave server sync only database1 and database2?  And further, could I have a second slave server sync a different subset of databases, like only database3?

I am using postgresql version 9.6.

No, traditional streaming replication cannot do that.

To replicate per database, you would have to use logical replication instead.  But this is not available until v10.  (Certain pieces of it were available back to 9.4, but using it required some very deep expertise until v10 when a user-friendly front-end was added)


Search for current month data in postgresql 9.2 I am trying to get the ticket data in a 9.2 database only from the current month.

The field called data_cadastro is DATETIME.

 id_ticket |    data_cadastro
-----------+---------------------
      2521 | 2017-10-31 08:11:48

how should I do ?

This is what you need:

SELECT *
FROM ticketdata
WHERE data_cadastro >= date_trunc('month', CURRENT_DATE);


Index Reorganize fails I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error.

Executing the query "ALTER INDEX [PK_abc] ON " failed with the
following error: "A severe error occurred on the current command.  The
results, if any, should be discarded.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

I have checked DBCC check db and no errors reported. Can this be due to low Transaction Log space as reorg don't consume space specially on data disk or I need to look at other direction. Any guidance would be greatly appreciated.

We can't say why, since you posted the SSIS error message. A maintenance plan is an SSIS package. SQL Server returned an error message to SSIS, and SSIS show you something else. You need to hunt down the SQL Server error message. Check the maintenance plan report file, assuming you have such. And the maintenance plan history table as well.


Best way to quickly and effectively drop a database and delete all files? If I set a Microsoft SQL Server database to offline and drop it, it will be deleted, but the log and data files will remain. If I do it in online mode, there is a chance that another connection will stop me from dropping it. In a dev environment, or for a database that I want to periodically blow away and recreate on production, what's the best sequence of T-SQL commands to blow away a database, along with its data files and log files, that will work as long as I have sa privileges and no one is maliciously trying to stop me from dropping the database?

Unless you are in the context of the database, you can't ensure you are the only one using it. Also, don't use batch separators. Run it all as a single batch.

Use AlwaysEncryptedSample;

ALTER DATABASE AlwaysEncryptedSample SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Use master;

DROP DATABASE AlwaysEncryptedSample;


How to use trigger in VoltDB As I know, trigger isn't supported in VoltDB. If anyone has updated news about trigger on VoltDB for nowadays, would you please let me know.

So my question: is it possible to implement a trigger-style action in VoltDB?

What I need is like following:

Once I commit an operation in VoltDB, like inserting a tuple, updating a tuple or deleting a tuple, a C function or executable binary could be executed, and the old/updated tuple value could be passed to the function.

Thanks in advance!

VoltDB has very limited support for triggers in DDL, the way some traditional SQL databases do. You can tell it to run a SQL statement when a table reaches a certain row count, which is often used to truncate older data.

Within stored procedures, there is tremendous support for very flexible logic. You can perform arbitrary database operations when certain conditions are met.

VoltDB does not have an official way to call a C function or executable with data. Perhaps you could expand on what you're trying to accomplish? If this is part of a change-data-capture scenario, you might want to take a look at VoltDB's Export technology, which is a push-based system for moving data to a downstream system in a reliable way.

https://voltdb.com/products/fast-data-ingest-and-export
https://docs.voltdb.com/UsingVoltDB/ChapExport.php


NoSQL DBs and 2-way synchronization I've been told that MongoDb does not, but do any NoSQL DBs support this functionality?

You can get the 2-way Sync feature using Couchbase Mobile (client-side) solution and on the backend using Couchbase Server

Can check out an example on this blog post


How do I get TABLE OF TOURNAMENT POSITIONS according to score table Buen dia Comunidad.

Good morning Community.

I am having doubts about how to obtain the POSITION CHART of the following SCORE table in MYSQL.

1: This is my table where the user records the escort of each sport played.

2: Now this is where I have doubts, how do I get the POSITION CHART according to the attached model where the following is calculated: PJ = Matches played PG = Matches Won PP = Lost Matches PE = Draws Matched GF = Goals For GC = Goals Against DFG = Total Goal Difference = where the winning team obtains 3 Points.

.

I wait for your kind support

I could only make the amount of encounter that 1 team has made

select category, report, gender, Team1, count (*) from score where Team1 = 'red' and category = 'CAT 3'

Looking the task and sample data and ignoring sample output...

For MySQL 8+ :

WITH cte AS (
SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
FROM score
UNION ALL
SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
FROM score
)
SELECT UPPER(equipo1) EQUIPOS,
       COUNT(*) PJ,
       SUM(score1>score3) PG,
       SUM(score1<score3) PP,
       SUM(score1=score3) PE,
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM cte
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

... and for MySQL 5+ :

SELECT UPPER(equipo1) EQUIPOS,
       COUNT(*) PJ,
       SUM(score1>score3) PG,
       SUM(score1<score3) PP,
       SUM(score1=score3) PE,
       SUM(score1) GF,
       SUM(score3) GC,
       SUM(score1-score3) DfG,
       SUM(3*SIGN(score1-score3)) TOTAL
FROM ( SELECT categoria,ndeporte,equipo1,score1,equipo3,score3
       FROM score
       UNION ALL
       SELECT categoria,ndeporte,equipo3,score3,equipo1,score1
       FROM score) cte
WHERE categoria = 'CAT 3'
  AND ndeporte = 'Futbol'
  GROUP BY UPPER(equipo1);

If you need the data for all categoria and/or ndeporte then add them to output fields list and GROUP BY clause, and remove from WHERE clause (or edit if you need the result for some values only).

fiddle


How to document Oracle database? I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the best practice for documenting Oracle database?

In Oracle, you would use the COMMENT command to:

  [...] add to the data dictionary a comment about a table or table
  column, view, materialized view, operator, indextype, mining model, or
  edition.

Most tools (PL/SQL Developer, Toad...) will display these comments in appropriate fields when you browse the database schema.

The comments can be queried directly with the *COMMENTS dictionary views, such as ALL_TAB_COMMENTS.


Postgres: Why does pg_stat_get_dead_tuples return 0 when there is still wasted space? Using Postgres 11.1, I have been trying to determine/estimate the amount of 'wasted' space in a table that would be recovered by a VACUUM FULL. My plan was to use pg_stat_get_live_tuples (L), pg_stat_get_dead_tuples (D), and pg_total_relation_size (S), then estimate the wasted space as (D / (L+D)) * S.

However, this does not appear to work. While investigating why not, I did the following setup:

CREATE TABLE sam_silly(txt TEXT);
INSERT INTO sam_silly VALUES('one');
INSERT INTO sam_silly VALUES('two');
INSERT INTO sam_silly VALUES('three');
INSERT INTO sam_silly VALUES('four');
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;

Then I did an initial count of the space according to these functions. The results were as expected (there are a million i.e. 2^20 rows):

select pg_total_relation_size(c.oid) AS size, pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead
FROM pg_class c where relname='sam_silly';

47308800, 1048576, 0

I then deleted half the table:

delete from sam_silly where txt like 'one%' or txt like 'three%';

Immediately repeating the query above gave the results I would expect:

47308800, 524288, 524288

Half the data is alive, half dead.

Then I ran 'analyze' and this is where things got weird:

47316992, 524288, 0

Space went up slightly but now there are no dead rows!

I tried 'vacuum' too:

47316992, 524264, 0

Not much change.

Finally, after VACUUM FULL it behaves as I would expect again:

23519232, 524264, 0

Now there are no dead tuples but also the space has been recovered.

The real database has autovacuum on, so it will probably vacuum the tables I'm interested in. But it looks like vacuuming causes it to set the number of dead tuples to 0 even though there is still a lot of 'wasted' space.

Is there a way to find out/estimate how much space is 'wasted' in this situation, when pg_stat_get_dead_tuples is returning zero even though it has not reclaimed the space used by the previously-reported dead tuples?

Exact version in case relevant: 'PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit'

ANALYZE does not remove dead tuples.  It looks like autovac kicked in and removed the dead tuples around the same time as you were doing the ANALYZE, and that is why the dead tuple count fell to zero.  If you are careful to prevent that from happening, you will find that an ANALYZE does not reset the dead tuple count.

The space freed up by the autovacuum is available for internal reuse, and can be seen using the pg_freespacemap extension.

select avail,count(*) from pg_freespace('sam_silly') group by 1 order by 1;
 avail | count
-------+-------
 3,648 |    22
 3,680 | 1,232
 3,712 | 1,153
 3,744 | 1,749
 3,776 | 1,233
 3,808 |   360
 3,840 |    20
 4,384 |     1
(8 rows)

Every page of this table is about half full and available for internal re-use.  This is only bloat if you do not intend to re-use that space in the near future for new tuples (including updates of old tuples, which create new versions of the old tuples).

The purpose of the dead tuple count is to let the autovac launcher know when it might be advisable to launch an autovac.  Pursuant to that goal, it records the number of tuples which are dead but not yet vacuumed away.  And when they are vacuumed away, the vacuum resets that count downward.  It is not there to show you bloat, which is why it does a poor job when forced into that purpose.

If you want to query for bloat, you can check out the suggestions at https://wiki.postgresql.org/wiki/Show_database_bloat


SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this? In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id), it goes nuts and takes a minute or so. This is longer than the app timeout, so the app fails for the user.

In the "goes nuts" cases, the returned result set is correctly empty, as it is in many but not all the other cases.

Luckily this is reproducible both in the production and development environments.

The developer says removing "TOP 1" from the query, then making sure the app consumes the extra rows of the result set, clears up the performance problem.

The query planner suggests no indexes when TOP 1 is present. (in dev).

Changing the query and fixing the app is in progress.  Rollout takes a while.

My question: Is there any DBA-accessible way to tune or tweak the production SQL Server instance to overcome this problem before the app change with the new query rolls out?

SELECT TOP 1
       subscription_id
  FROM subscription AS sub
  JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id
  JOIN person_group AS apg ON apg.person_id = bi.person_id
  JOIN pplan ON pplan.plan_id = sub.plan_id
  JOIN product ON product.product_id = [plan].product_id
  JOIN product_attribute ON product_attribute.product_id = product.product_id
 WHERE apg.group_id = @Id
   AND apg.start_date < GETDATE()
   AND (apg.end_date IS NULL OR apg.end_date > GETDATE())
   AND (sub.end_date IS NULL OR sub.end_date > GETDATE())
   AND product_attribute.attribute_type = 'special feature'
   AND product_attribute.attribute_data = '1'
 ORDER BY sub.start_date ASC;

If you can't change the query you can use a plan guide.

Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this).

If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (HASH JOIN, MERGE JOIN) if inappropriate nested loops is the problem. You might need to resort to the USE PLAN N'...' hint.

Once you know the hint(s) required you can apply them using the information here.


Stored Procedure Consolidation I recently started working on a project using stored procedures from data vs. ORM's

I haven't seen this project's procedure layout before and was wondering if it's ok / what would be better.

Code would similar to this

procedure getCarsBy

@color
@inProduction
@queryType

case queryType = 1
    Select from Cars where color = @color
case queryType = 2
    Select from Cars where inProduction = @inProduction

Most the procedures have big case blocks for each where clause configuration.

I'd like to break things out of the case statements because it doesn't seem proper to duplicate the select piece all over the place but I'm not sure if dynamic sql or many procedures would be better.

For example getCarsByColor, getCarsByInProduction ect.

Thoughts?

Your question may be a candidate for closing as "Primarily opinion-based" but yes, I'd follow your proposed pattern, to a point. What I tend toward is identifying the most frequent parameter combinations and coding discrete procedures for them.

It becomes unfeasible with a large number of combinations of parameters to code and maintain for each, although you could code-gen them. Instead, deal with the lesser combinations with dynamic SQL, or static SQL with OPTION RECOMPILE:

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (c.Country = @country OR @country IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)

  The effect of all the @x IS NULL clauses is that if that input
  parameter is NULL, then that AND-condition is always true. Thus, the
  only conditions that are in effect are those where the search
  parameter has a non-NULL value.

  As far as maintainability goes, it's difficult to think of a better
  solution for the search conditions at hand. It's compact, easy to read
  and to extend. And performance? Very good as long as you include the
  query hint OPTION (RECOMPILE). This hint forces the query to be
  recompiled each time, in which case SQL Server will use the actual
  variable values as if they were constants.

Query and quote courtesy of Erland Sommarskog's reference article on the topic, Dynamic Search Conditions.

By coding specifically for the common cases you reduce the overhead of RECOMPILE incurred on the less frequent cases. That said, I confess I'm getting good value from more liberal application of RECOMPILE recently. In the era of more cores & hyper-threading, the recompilation overhead can (but certainly not always!) be preferable to one-size-fits-all execution plans.


DB Fiddle: https://www.db-fiddle.com/f/6i2dwPvg4PqTfxyCTQZoho/0
And your query could look like
with recursive myCTE (root_id, id, parent_name, parent_id) as (
    # the most basic level, we need to remember the root id and name
    select id as root_id,
           id,
           name as parent_name,
           parent_id
    from myTable
    # you can control which output you need here or at the end (modifying the `from myCTE`
    where parent_id is null
    # or with that
    # where id = 2

    union all

    # combine that in recursive way
    select mC.root_id,
           mT.id,
           mC.parent_name,
           mT.parent_id
    from myTable mT
    inner join myCTE mC on mT.parent_id = mC.id
)
select root_id, parent_name, group_concat(id)
from myCTE
where id <> root_id
group by root_id, parent_name;

It uses the recursive CTE statements (see https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive) which allows to gather such hierarchical rows together. At the end, the GROUP_CONCAT is used to combine together strings by groups.


Simple filesystem cube I'm trying to understand cubes so thought I would create a simple datamart to represent my filesystem.

So my question is, if I wanted to create a simple datamart to represent my filesystem, how would it look?

I'm just thinking one fact for the files, and a few dimensions for file extension, maybe a date dimension and few others.

eg
factFiles
dimExtensions

Some guidance/suggestions would be appreciated.

Personally when i got started out with SSAS the best thing i found to do was run through the examples using Adventureworks. The tutorials are here: http://msdn.microsoft.com/en-us/library/ms170208(v=sql.110).aspx You may need to use the dropdown to select the version of sql server you intend to use.

The pre-reqs take you through getting sample data installed. They're actually very good tutorials to follow along with. I went through them when 2005 was the latest version so can't necessarily vouch for the more recent editions but i'm sure they're of equal quality.

Thanks,

Simon


Display Data Using Pivot Is a pivot the best way for me to display this data?  I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this

id  uuid    cycle   run walk
1   mn12    1   1   0
2   cr23    0   0   1
3   bb11    1   0   0

This is the syntax I attempted, but it gives me an error of

  Incorrect syntax near the keyword 'For'

What should I alter in order to have my data display as desired?

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)
create table #bigponyhorses
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,uuid int
  ,empid varchar(10)
)
Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')
Insert Into #bigponyhorses VALUES ('1','mn12'), ('2','mn12'), ('3', 'cr23'), ('1', 'bb11')

;WITH data AS
(
  select *
  FROM
  (
      Select
      [rs].[ID],
      [bph].[uuid]
      [rs].[event1],
      CASE when [bph].[uuid] is not null then '1' else '0' end As [Exists]
      from #robschneider rs
      OUTER APPLY (SELECT *
                   FROM #bigponyhorses
                   WHERE [rs].[uuid] = [uuid]) [bph]
  ) src
  pivot
  (
      [Exists]
      For [planname] IN ('cycle', 'run', 'walk')
  ) piv
)
SELECT
[rs].[ID],
[rs].[event1],
'cycle', 'run', 'walk'
FROM data

I couldn't include [Intranet].[dbo].[ResourceDevelopementCompletedCategories] in my test query below but hopefully I have provided enough code for you to understand the syntax you require.

create table #robschneider
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,event1 varchar(100)
  ,uuid int
)

Insert Into #robschneider Values ('cycle', '1'), ('run', '2'), ('walk', '3')

SELECT  ID
        ,uuid
        ,COALESCE(cycle, 0) AS [Cycle]
        ,COALESCE(run, 0) AS [Run]
        ,COALESCE(Walk, 0) AS [Walk]
FROM
(
    Select
    [rs].[ID],
    [rs].[uuid],
    [rs].[event1],
    CASE when [rs].[uuid] is not null then '1' else '0' end As [Exists]
    from #robschneider rs
) src
pivot
(
    MAX([Exists]) For [event1] IN ([cycle], [run], [walk])
) piv

UPDATE
I have added the COALESCE statements to add the 0 values when event1 doesn't exist.