error when creating a view with CTE When running the CTE below, it fetchs me the data I want.

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

However when I'm trying to create a view on it like below

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error

  ERROR:  The column « Site » has been specified more than once

  ********** Error **********

  ERROR: The column « Site » has been specified more than once
  WSQL State :42701

I've tried to put the view name before Site, namely a or d or "Axis_Reference_All" but none of it works.

Questions are the following:

Is CTE allowed with view creation on postgres? I guess no but it is unclear.
What would be the best way to create a view with my CTE? That CTE has been built that way because it is improving performance.

There are two possible solutions

Do a USING join like below

CREATE VIEW report_view
AS
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
    )",
     a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site"", "Reference_internal"", "Customer_code") IN
       ( SELECT "Site"",
                    "Internal_reference"", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip

However as I'm retrieving datas from 5 years ago, it takes more than 5 minutes if I'm using a USING.

The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.

CREATE VIEW report_view AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site", "Reference_internal", "Customer_code") IN
       ( SELECT "Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT "Period_date",
       a."Site",
       a."Customer_code",
       "Internal_reference",
       "InvoiceNumber",
       "Value_in_currency",
       "Value_in_EUR",
       "Value_Budget_in_EUR",
       "Selling_price_CUR",
       "Selling_price_EUR",
       "Currency_code",
       "Selling_quantity",
       "Variance_price_CUR",
       "Variance_price_EUR",
       "Variance_value_CUR",
       "Variance_value_EUR",
       "Selling_date",
       "Reference",
       "Reference_internal",
       "Reference_customer",
       "Reference_supplier",
       "Reference_description",
       "Reference_workshop",
       "Reference_line",
       "Reference_segment",
       "Reference_purchasingfamily",
       "Reference_motorapplication",
       "Reference_interco",
       "Reference_trading",
       "Cogs_rm",
       "Cogs_dl",
       "Cogs_voh",
       "Supplier_global",
       "Supplier_code",
       "Supplier_name",
       "Supplier_account_manager",
       "Supplier_interco",
       "Supplier_incoterm",
       "Supplier_incoterm_location",
       "Supplier_incoterm_via",
       "Supplier_continent",
       "Supplier_country",
       "Supplier_city",
       "Supplier_zipcode",
       "Purchasing_unit",
       "Purchasing_price",
       "Purchasing_currency",
       "Purchasing_payment_term_days",
       "Purchasing_payment_term_type",
       "Purchasing_consigned",
       "Purchasing_grossweight",
       "Purchasing_grosscube",
       "Purchasing_eco_order_qty",
       "Purchasing_pack_order_qty",
       "Purchasing_moq",
       "Purchasing_mov",
       "Purchasing_leadtime_days",
       "Customer_global",
       "Customer_name",
       "Customer_account_manager",
       "Customer_interco",
       "Customer_incoterm",
       "Customer_incoterm_location",
       "Customer_incoterm_via",
       "Customer_continent",
       "Customer_country",
       "Customer_city",
       "Customer_zipcode",
       "Selling_unit",
       "Selling_price",
       "Selling_currency",
       "Selling_payment_term_days",
       "Selling_payment_term_type",
       "Selling_consigned",
       "Selling_grossweight",
       "Selling_grosscube",
       "Selling_eco_order_qty",
       "Selling_pack_order_qty",
       "Selling_moq",
       "Selling_mov",
       "Selling_leadtime_days",
       "Reference_netweight",
       "Sc_storage_unit",
       "Sc_production_unit",
       "Sc_inventory_status",
       "Sc_inventory_price",
       "Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

This will give the required speed in term of data retrieval.

Cheers


Check constraint that exactly one in group has bit column set as true Let's say you have a table of addresses like the following:

dbo.Address
(
    AddressId INT PRIMARY KEY,
    CustomerId INT,
    IsPrimary BIT,
    ...
    more typical address fields
    ...
)

I want a check constraint to enforce that there is always exactly one primary address per customer that allows me to switch primary addresses for a customer or add a new address and make that one primary.

I know I could have a constraint that not more than one is primary (allowing none to be primary) and then set all of a customer's address to not primary before inserting/updating an address to be primary. But how would I enforce that if a customer has addresses, one of them must always have IsPrimary = 1?

You can define a filtered index on the table which will do this for you, there is no need to use a check constraint or an additional table for this.

CREATE UNIQUE INDEX IX_Address_UniquePrimaryAddress
ON dbo.Address(CustomerId)
WHERE IsPrimary = 1;

Here is a good link of filtered indexes that should help you:

https://msdn.microsoft.com/en-us/library/cc280372.aspx


Oracle Forms 6i on Windows 7 Given I have a system of Oracle Forms 6i (legacy software), how could I make it work on Windows 7. We have serious problems with apps like PLUS80.exe crashing ("Oracle SQL*Plus has stopped working").

Is it possible at all? We also consider downgrading to XP if problems persist..

Virtulization is an option if it won't work on Windows 7.


Optimizing the repeated use of the same if condition inside a VIEW? CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField) "uniqueFieldName")
    IF(table1.foo or table2.foo IN ('State1', 'State2'), 0, table3.uniqueField2) "uniqueFieldName2")
    --more of these if statements with the same condition ,but different field
FROM table1
--various joins with other tables
);

I was wondering if following principles of DRY (don't repeat yourself) there would be a means of storing the same boolean condition being use in the IF statements for reuse to clean up the code and possibly make the query run better.

I have tried setting a boolean variable

set booleanIfCondition = table1.foo or table2.foo IN ('State1', 'State2');

However I get syntax errors whether I place it before the if statement or try to sneak in somewhere after the select.

Test

CREATE OR REPLACE VIEW VIEW_NAME AS (
SELECT
    table0.fieldBlah "FieldBlah",
    --more fields
    IF(@tmp := (table1.foo or table2.foo IN ('State1', 'State2')), 0, table3.uniqueField) "uniqueFieldName",
    IF(@tmp, 0, table3.uniqueField2) "uniqueFieldName2",
    --more of these if statements with the same condition ,but different field
FROM table1
--various joins with other tables
);


In PostgreSQL, is there a type-safe first() aggregate function? Full question re-write

I'm looking for a First() aggregate function.

Here I found something that almost works:

CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

The problem is that when a varchar(n) column passes through the first() function, it's converted into simple varchar (without a size). Trying to return the query in a function as RETURNS SETOF anyelement, I get the following error:

  ERROR: structure of query does not match function result type Estado
  de SQL:42804 Detalhe:Returned type character varying does not match
  expected type character varying(40) in column 2. Contexto:PL/pgSQL
  function vsr_table_at_time(anyelement,timestamp without time zone)
  line 31 at RETURN QUERY

In the same wiki page there is a link to a C Version of the function that would replace the above. I don't know how to install it, but I wonder if this version could solve my problem.

Meanwhile, is there a way I can change the above function so it returns the exact same type of the input column?

DISTINCT ON()

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

  SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget ORDER BY

Also, while I didn't bold it then I will now

  Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;


Turn off ANSI_WARNINGS for heterogenous queries We have a Stored Procedure with many linked-server queries. When executing it displays a Warning:

  Warning: Null value is eliminated by an aggregate or other SET
  operation.

To suppress this, I  tried :

  SET ANSI_WARNINGS OFF

But that threw this error :

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
  to be set for the connection. This ensures consistent query semantics.
  Enable these options and then reissue your query.

All articles I looked at mention setting ANSI_WARNINGS to ON to get it to work.
But I Absolutely do not want to see those Warnings, because I'm sending query results as attachment in sp_send_dbmail which has that Warning message right at the top of attachment.

Is it possible to suppress the warnings for heterogenous queries ?

This should remove your warning.

select name, count(employeeId) from employees group by name
select * from RemoteServer..departments


How can I use LEAD LAG-like functions on multiple columns simultaneously Suppose I have the following table:

start_data | end_date
----------------------
t1         | Null
Null       | t2
t3         | Null
Null       | t4
t5         | Null

The output should be like this:

start_data | end_date
----------------------
t1         | t2
t3         | t4
t5         | Null

notes:

the type of ti is date
ti < ti+1 holds true for all values.

pseudo code:
 for every record i
    xi <--- start_date
    yi <--- end_date
    zi <---- min(yi) && xi>yi
    (xi,zi) should be the output for this record i

Is it solvable using native SQL?

I guess one could order by coalesce(start_data, end_date)

WITH cte AS (
SELECT COALESCE(start_data, end_date) dt,
       ROW_NUMBER() OVER (ORDER BY COALESCE(start_data, end_date)) rn
FROM datatable
)
SELECT MIN(dt) start_data, MAX(dt) end_date
FROM cte
GROUP BY ROUND((rn-1)/2)

This query is a model. The final text is dependent by DBMS.

Maybe you will need to replace MIN/MAX with CASE WHEN rn%2 = [0|1]


Automatically reload DB nightly Nightly we dump our Production DB and load it into our Staging and Dev environments.

A bash script is taking care of that and the steps to achieve this are the following:

download the dump from the cloud
drop the active connections to the DB
revoke any connections to the DB
drop the DB
reload (recreate) the DB

This is the query I use for dropping the active conns:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$DB' AND pid <> pg_backend_pid();

And the one for revoking incoming conns:

REVOKE CONNECT ON DATABASE $DB FROM PUBLIC;

The problem is that almost every night, the script fails with the following: ERROR:  database "my_db" is being accessed by other users

And I'm not sure why is this happening since I reject the existing and then revoke any further connections.

Is it possible that the execution from dropping the connections to dropping the DB is too fast, which doesn't give enough time for some connections to finish getting dropped? I am adding a 2 sec delay after the REVOKE to test this out tonight, but I was wondering if there is a less hackish, and more sophisticated way of solving this.

In a same context, I was prefering to change the password of the applicative login, during the drop/restoration process.
Is it possible for you?

  Make a query to list active connection from pg_stat_activity table to identify the culprit(s).

Hope this helps,
Thomas


Do i need "CHECK CONSTRAINT" when i am using "WITH CHECK ADD CONSTRAINT" I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows:

ALTER TABLE [dbo].[ConsoleServer]  WITH CHECK ADD  CONSTRAINT [FK_ConsoleServer_RackUnits] FOREIGN KEY([RackUnitID])
REFERENCES [dbo].[RackUnits] ([UnitID])

GO
ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

I have these 2 questions:-

I know that the first line is responsible to create a FK between two DB tables. but what is the purpose of the following :

ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

I mean since I have specified to add the constraint with check, why do i need to enable the constraint using check constraint ?
Second question: In general, why does the DB script has the word GO. Now if I remove it the script will be executed well on the destination DB, so why it is included in the script prior to any statement?

The answer to both of your questions is really the same.  You are using a script generation tool.  It does what it's told to do.  Typically the second command is only needed if you are doing a WITH NOCHECK on the first one.  However it's included because the settings for the script generation component of SSMS told it to include it.

Here is a post I did on modifying those settings.

As far as the GO command.  This is a batch separator and is useful in breaking up a script both visually and for the computer.  It ends the scope of variables and affects how the parser looks at your script.  Again it's included because that is what the tool was told to do.


triggers or else between 2 postgres databases hosted on a different datacenter I'm trying to create a trigger inside of a Postgres RDS database hosted in Frankfurt that we will call "Frankfurt". That trigger will poll tables in a distant Postgres RDS database hosted in Seoul, let's call that database "Seoul".

The Seoul database will never have delete nor update. It will only have insert.

The aim of that trigger is for each insert into the Seoul database to copy the rows from Seoul that are not in Frankfurt, to the Frankfurt database.

CREATE OR REPLACE FUNCTION my_dml_function() RETURNS TRIGGER AS $employees$
    BEGIN
        --
        --
        IF (TG_OP = 'INSERT') THEN
            perform dblink_connect('dbname=database_seoul user=postgres password=ExtraPassword');
            INSERT INTO all_employees
            SELECT *
            FROM dblink('foreign_server',$RDS$
                SELECT *
                FROM employees
                WHERE employee_id NOT IN  (SELECT employee_id FROM all_employees)
            $RDS$) AS t1;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER my_dml_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW EXECUTE PROCEDURE my_dml_function();

I'm not sure if it is the best way.

I don't think this is a good idea, you should read the documentation on Streaming Synchronous Replication instead which guarantees consistency and is a ton easier to configure.

Even if you're not going to use that, you should consider postgres_fdw instead of the older dblink.


Can't read config files from pgAdminIII on local Kubuntu machine My database starts fine, I can connect to it from pgAdmin and run queries.  However when I go to Tools->Server Configuration->postgresql.conf I get an error.  The error states

could not stat file "postgresql.conf": No such file or directory
CONTEXT: SQL function "pg_file_length" statement 1.
STATEMENT: SELECT pg_file_read('postgresql.conf', 0, pg_file_length('postgresql.conf'))

I tried to run that statement as its own query and came up with no such function.  I googled around and found that I need adminpack so I installed that.  Now when I run the same query I get `could not open file "postgresql.conf" for reading: No such file or directory.

The file does indeed exist at /etc/postgresql/9.4/main/ so I put the full path in the command and it tells me that absolute paths aren't allowed.  I made a symlink in my data directory /var/lib/postgresql/9.4/main of the postgresql.conf file.  Doing that allows me to open postgresql.conf from pgamin.  However, when I save the file I notice that I get a new postgresql.conf in my data directory and that it renames the symlink to postgresql.conf.bak which means when I have conflicting postgresql.conf files between the /etc/.../main and the /var/.../main directories.

One thought is to move all the .conf files from the /etc/ directory to the /var/ and then put symlinks in the /etc/ but I figure I should just ask rather than screw around.

pg_hba and postgresql.conf should stay under

/etc/postgresql/[pg_version]/main/

then you should link them to /var/lib.. with:

#:ln -s /etc/postgresql/[pg_version]/main/pg_hba.conf
/var/lib/postgresql/[pg_version]/main/pg_hba.conf


How to mimic a full outer join using mysql views? I have all kinds of views in mysql with employee data.  I have tried to use methods like this -https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but they simply don't work for views.

I end up getting more of a LEFT JOIN as a result.  Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.

Simulation of FULL OUTER JOIN:

( SELECT ... FROM a LEFT JOIN b ON ... ) -- Intersection, plus rest of b
UNION ALL  -- There will be no overlap; ALL is faster
( SELECT ... FROM b LEFT JOIN a ON ... WHERE a.id IS NULL ) -- Just rest of a

(The link given is less efficient because it gets the Intersection twice, then dedups by doing UNION DISTINCT.)

If you need a FULL OUTER JOIN between more than 2 tables, then it gets messier.  (But you did not ask for that.)


Compare a date and a timestamp with time zone with now() in the same query? I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other.

When I try and query the server with timestamp with time zone by casting the timestamp as a date:

...
WHERE
    (
        status_code = '30000'
        OR status_code = '30005'
    )
AND CAST(expiration AS DATE) > now()

It works, but using the same query on the servers where expiration is already a date fails:

  [Err] ERROR:  invalid input syntax for type date: "No End Date"

Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.

This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration > now()

PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).

The error message tells a different story. You are actually trying to input a date with invalid syntax.

I wrote a detailed answer about handling of timestamps with or without time zone in PostgreSQL recently - if that should the issue:

Ignoring timezones altogether in Rails and PostgreSQL

As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:

...
AND   expiration::timestamptz > now()

If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially in a CASE construct:

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END > now()

The manual about the special value infinity.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

If the format of the time stamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:

to_date('07/08/2013', 'DD/MM/YYYY')

How to convert “string” to “timestamp without time zone”

Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE 'UTC' -- desired time zone here

If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')


File share witness vs 3rd Node for WSFC + AG with only 2 nodes? I am migrating from Mirroring to AGs on SQL Server 2014 / Windows 2012 R2. I will have a 2 node cluster. I need this cluster to be online at all times for a production environment.

I am reading Microsoft documentation for Availability Groups but I cannot find anything along lines of "Best Practices" for having a 2 node AG/WSFC, except that not having a witness is NOT "Best Practice". I am simply exploding with questions.

If I implement a File Share Witness

What is the best place to put my (file share) witness? Is putting it on a DC a good idea or a security risk?

Why does it have to be Windows Server as opposed to (say) a linux/NAS?

How do I prevent someone from simply deleting the file share because they don't know what it is or what its for, while not locking myself/all sysadmin out of it?

If I don't Implement a File Share Witness

Could I just remove dynamic quorum and exclude witness altogether? Is that even possible?

Is it better to just add another SQL node to my cluster, if I have one, to act as a 3rd voting party but not include any of its databases? Is that perhaps more durable?

In a 2 node Availability Group (or mirror) a 3rd node is important if you want automatic HA. If your secondary cannot connect to the primary, the secondary has to work out if it can come online as the primary, or if the error is actually with the secondary. Without a 3rd party, this is impossible.

The File Share Witness will be simpler than adding another Windows node just to work as a witness. It can be anywhere, what is important is that both nodes will be able to see the witness if the other node goes offline. You might need to think extra hard if you have a virtual environment, you need to consider if the physical node goes offline will your fileshare still be available.

Additional thoughts for multi data centers

If the 2 nodes are in different data centers (you mentioned DR, so I'm assuming) then having a file share in the cloud may be helpful (you can use Azure Blob Storage as a witness). This means if one of your primary data center goes offline, SQL will automatically failover to the secondary. If you host the fileshare at your secondary and the secondary goes offline, then your primary server will go offline as well (can't contact the secondary or the file share so assumes that it is no longer primary).


Do synonyms affect remote query performance I've got a task where I need to migrate an existing database to a new server.

The database in question has stored procedures that query another database on the same server, this second database is not going to be moved.

I would love to use a synonym to point to the other database on the linked server as this will make it really easy to set up and maintain, however I want to know if the synonym is likely to cause performance problems with the execution plan, or if it's only the cross server querying that is the problem.

A synonym is just a pointer and gets simplified away prior to optimization. If you have this:

CREATE SYNONYM dbo.foo FOR OtherServer.db.dbo.blat;

Then these two will optimize the same, producing identical execution plans (both having a Remote Query operator that points at blat):

SELECT bar FROM dbo.foo;

SELECT bar FROM OtherServer.db.dbo.blat;

You can test this quite easily.


Find tables with modified data after a specified datetime I am performing few inserts and updates on my database using tool provided by app supplier. I want to compare the updated database and backup after running the tool.

But my database size is around 250GB, that takes long time to compare. Rather than full database compare I want to compare the tables which are modified after running the tool.

How can I get all the table names those are modified after specified date like 2015-04-14?

Can someone help me with this please?

You can use something like

    SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

you can add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year, whatever. Something like

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

Although this is no substitute for true database auditing that captures who modified the table, what was changed, and what the table looked like prior to the change.

To get the former, you'll need to invest some time in DDL triggers or some money into a third party tool.


Fact table for sales product - oracle I have sales fact table (which has quantity - price measures) and dimensions (product - customer -time)
my table schema:

I need a query to get best selling product; then show list of amount paid for buying this product in each year and month like this example

Year    Month        AmountOfMoney
====    =====        =============
-       -            14040.00
1988    -            130.00
1998    March        130.00

I do not use Oracle, but using a STD syntax it should work:

/* aggregated sales by year-month */
SELECT
    T.CALENDAR_YEAR AS Year,
    T.CALENDAR_MONTH_DESC AS Month,
    SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
    SALES S
JOIN
    TIMES T
    ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = (SELECT PROD_ID
                   FROM (
                         SELECT
                            S.PROD_ID,
                            SUM(S.AMOUNT_SOLD) AMOUNT
                         FROM
                            SALES S
                         GROUP BY S.PROD_ID
                         ORDER BY SUM(S.AMOUNT_SOLD) DESC
                        ) BS
                   WHERE ROWNUM = 1
                  )
GROUP BY
    T.CALENDAR_YEAR,
    T.CALENDAR_MONTH_DESC
ORDER BY
    T.CALENDAR_YEAR,
    T.CALENDAR_MONTH_DESC;

db<>fiddle here


Changing db ownership using a user which can only see the sa At first, I would like to thank the community for creating such a resourceful forum. I've been using StackOverflow for years, and recently, using dba on SE too and you're great. Most of my questions have been asked for someone, or some comment have led me to an answer, so thank you very much!

After that being said, I have a problem which I don't think it's usual. The case is: I have been asked to create one database which will be a central db for a new system. This db will have the info that the company I work for need (like payment info, contacts, client's emails and stuff).

After that, each new customer will be given a clean db to work (the one they will use with our software). This db should have only one user, and the user can only see and connect to that specific database. This db should be partially contained and the user can't have any link to any existing instance login.
Also, for some reasons, the process of creating a db should be done over a procedure, to be triggered by some event, after the new client finishes his registration on our website. And the user that run this procedure (which I'll be calling "CREATOR") must not have any rights besides "db creation".

These steps I already covered, creating a user which only have permission to run the procedure and to create a database.

The problem is that this CREATOR user is the db_owner of the databases it creates. I would like to change the ownership to the sa (or any other login, really), but I can't because the CREATOR can't see anyone besides itself and the sa. I know that this happened because I didn't allow it to see other logins.

Then, it comes the question. How can I change the ownership of all created dbs to sa (the one I can see) without breaking those rules or how can I grant CREATOR the permission to see (and not to drop or create) other users and change the db owner of each db he creates?

Thanks in Advance!

May not be pretty, but you could use a trigger on database creation that executes as a privileged user of your choice:

CREATE TRIGGER [database_setup]

    ON ALL SERVER
    WITH EXECUTE AS 'privuser'
    FOR CREATE_DATABASE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @db nvarchar(100)
    DECLARE @sql nvarchar(MAX)
    DECLARE @data XML
    SET @data = EVENTDATA()

    SELECT @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(100)');
    SELECT @sql = ('ALTER AUTHORIZATION ON DATABASE::' + @db + ' TO sa;');
    sp_executesql @sql;
    END
GO

ENABLE TRIGGER [database_setup] ON ALL SERVER
GO


How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)? Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof)

I need to delete (.bak files older, than, say, 7 days in remote file share.

Before inserting the command into SqlServerAgent job, I tried to execute it from Windows command line:

using forfiles :
  forfiles /P "\\RemoteHostName\sharedFolderName" /s /m *.bak /D -7 /C "cmd /c del @path"

gives the error:
"ERROR: UNC paths (\machine\share) are not supported"
using Windows PowerShell:

dir \\RemoteHostName\sharedFolderName |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del

The latter deletes the required remote files when used from Windows PowerShell command prompt but when executed in/from an SqlServerAgent job - fails

The history of such SqlServerAgent jobs shows, quoting:

The job succeeded. The job was invoked by User domain\domainAccountName. The last step to run was step 1

Executed as user: localHostName\sqlbackup. The job script encountered the following errors. These errors did not stop script: A job step received an error at line in a PowerShellScript. The corresponding line is 'dir "\\RemoteHostName\sharedFolderName\" |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\RemoteHostName\sharedFolderName' because it does not exist. ' Process Exit Code 0. The step succeeded.

Note the green check-marks on failed job and job steps

Again, zoomed-in the text output of the result:

Also, when executed this SqlServerAgent job step shows: "processing", then "Success"

though it is persistingly failing to delete required files.

The question(s):

How to make the job and step, containing errors,  to show "Failed" instead of of "Succeeded"?
Why does the purging of older files using Windows PowerShell commands succeedes from command line prompt and fails from within SqlServerAgent job&step?

2a. How succeed with removing of older remote files on schedule from SQL Server 2012?

PS
I am using SQL Server Agent Job proxy according to this answer using local Windows user accounts on remote/target and local/source hosts with the same/equal names sqlbackup and the passwords

The same story with scheduling in Windows Task Scheduler - the job and steps are "succeeding" without deleting anything though copying to remote location (out of description here) by previous steps are successful. And I am interested to centralize all in SQL Server SqlServerAgent job (but not outside)

In order to make PowerShell errors cause a SQL Agent step to fail you have to make an adjustment to the $erroractionpreference setting. By default this value is set to Continue so the steps will show successful because no error is raised up for SQL Server Agent to detect an issue in the actual PowerShell command occurred.

You can put this at the top of your script that should fix that:
$erroractionpreference="stop".
This will allow the error PowerShell is returning to trickle up to SQL Server Agent process.


Why doesn't this query use an index spool? I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap:

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

And force a nested loop join with MAXDOP 1:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

This is a rather unfriendly action to take towards SQL Server. Nested loop joins often aren't a good choice when both tables don't have any relevant indexes. Here's the plan:

The query takes 13 seconds on my machine with 100000000 rows fetched from the table spool. However, I don't see why the query has to be slow. The query optimizer has the ability to create indexes on the fly through index spools. This query seems like it would be a perfect candidate for an index spool.

The following query returns the same results as the first one, has an index spool, and finishes in less than a second:

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

This query also has an index spool and finishes in less than a second:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Why doesn't the original query have an index spool? Is there any set of documented or undocumented hints or trace flags that will give it an index spool? I did find this related question, but it doesn't fully answer my question and I can't get the mysterious trace flag to work for this query.

As you know, the optimizer's search is not exhaustive. It tries things that make sense in context, and which frequently pay dividends on real queries. Forcing a loop join between two single-column unindexed heap tables is not such a scenario. That said, here are some details:

SQL Server likes to transform applies to joins early, because it knows more tricks with joins. Later on, it may explore converting the join back to an apply. The difference between the two being correlated parameters (outer references). Applies make sense when there is a suitable index on the inner side. Your example has no indexes, so the optimizer is not persuaded to explore translation to an apply.

A simple (non-apply) join has the join predicate on the join operator instead of outer references. The spool optimization for a non-apply is typically a lazy table spool, since there is no predicate on the inner side, only at the join.

The optimizer does not consider building an index on the fly to enable an apply; rather the sequence of events is usually the reverse: transform to apply because a good index exists.

You can sometimes encourage an apply rather than a join by using APPLY syntax in your query. The undocumented trace flag 9114 can assist in this by dissuading the optimizer from translating a logical apply to a join up front. For example:

SELECT *
FROM dbo.X_1000 AS a
CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b
OPTION (QUERYTRACEON 9114);

An index spool is favoured for apply because the outer reference means the selection is applied on the inner side of the join. You will often see this via SelToIndexOnTheFly but other paths exist. See my article The Eager Index Spool and The Optimizer.


Is it possible to change the color of relationship lines in MySQL Workbench diagrams? I have MySQL Workbench Community Edition 8.0.15 installed on my Mac OS 10.14.3. I'm currently working on making an ERD with Workbench's EER Diagram.

My problem is that whenever I export my diagram, the lines showing the relationships between tables are not visible because the background is white, and the lines are white.

It is visible on the editor because the background is black in workbench, but not when exported.

Is there any way to change the color of these lines? Or at least the background color of the diagram when it is exported so that the lines would at least be visible?

I found a solution to help. Close Workbench. Change the theme of your mac in clear mode. Restarts Workbench and saves again in pdf.


Oracle can't drop index with . in name? I am trying to drop an index that has a dot(.) in its name and can't seem to do it (I'm logged in as the SYSTEM user, which owns the index).  Any ideas?

SQL> select index_name,owner,status from all_indexes where owner<&gttable_owner;

INDEX_NAME                     OWNER                STATUS
------------------------------ -------------------- --------------------
IX_isbn_send_h.D_STAT          SYSTEM               VALID

SQL> drop index IX_isbn_send_h.D_STAT;
drop index IX_isbn_send_h.D_STAT
                          *
ERROR at line 1:
ORA-01418: specified index does not exist

Use this one:

DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT";

Note, when you use quotes, then names are case-sensitive in Oracle.


Creating a SQL Agent Job using Dynamic SQL I'm currently putting together Agent Jobs for Database backups, using the Ola Hallengren scripts. I've put together a script that creates an Agent Job to perform FULL backups and it's working perfectly.

Now, I want to make the script Dynamic. Why? Each environment will have a different Certificate for encrypting the backups and the Cleanup times may vary.

This is what I have thus far;

DECLARE @SQLCertificate NVARCHAR(50)
SET @SQLCertificate = N'00132'

EXECUTE ('
IF NOT EXISTS (SELECT [Name] FROM [dbo].[sysjobs] WHERE [Name] = N''Backups_Encrypted_FULL'')
BEGIN
    EXECUTE sp_add_job
        @job_name = N''Backups_Encrypted_FULL'',
        @enabled = 1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @owner_login_name = N''DBA_Maintenance'',
        @description = N''Performs a FULL backup of all User Databases'',
        @category_name = N''[Database Backups]''

    EXECUTE sp_add_jobstep
        @step_id=1,
        @job_name = N''Backups_Encrypted_FULL'',
        @step_name = N''Execute T-SQL'',
        @subsystem = N''TSQL'',
        @command = N''EXECUTE [dbo].[DatabaseBackup]
    @Databases = N''USER_DATABASES'',
    @Directory = N''C:\SQLBackup'',
    @DirectoryStructure = N''{DatabaseName}'',
    @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
    @BackupType = ''FULL'',
    @Description = N''Full_Backup'',
    @CheckSum = ''Y'',
    @Verify = ''Y'',
    @Compress = ''Y'',
    @CleanupTime = ''0'',
    @CleanupMode = ''AFTER_BACKUP'',
    @Encrypt = ''Y'',
    @EncryptionAlgorithm = ''AES_256'',
    @ServerCertificate = '''+ @SQLCertificate +''',
    @LogToTable = ''Y'''',
        @database_name = N''DBA.Maintenance''

    EXECUTE sp_add_jobschedule
        @job_name = N''Backups_Encrypted_FULL'',
        @name = N''Saturday Night'',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_end_date=99991231,
        @active_start_time=220000,
        @active_end_time=235959

    EXECUTE sp_add_jobserver
        @job_name = N''Backups_Encrypted_FULL'',
        @server_name = N''(Local)''
END ')

GO

Running it however, gives the following error;

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'USER_DATABASES'.

Anyone have any thoughts on this?

The first problem is that you are enclosing escaped string delimiters inside an already-escaped string:
----------------vv this opens a string
    @command = N''EXECUTE [dbo].[DatabaseBackup]
@Databases = N''USER_DATABASES'',
--------------^^ this tries to close it

This is similar to the following:
EXEC sys.sp_executesql N'PRINT ''foo''bar'';';

Which yields:

Msg 102, Level 15, State 1
Incorrect syntax near 'bar'.
Msg 105, Level 15, State 1
Unclosed quotation mark after the character string ';'.

To get around it, you need to double up the second layer of single quotes again.
EXEC sys.sp_executesql N'PRINT ''foo''''bar'';';

Which prints:

foo'bar

I don't think I have to tell you that putting four, eight, sixteen, and so on gets really ugly, really quickly.
A better solution
In the comment above, Dan was off on the reason for the error, but absolutely right on the question of why dynamic SQL in the first place. It's simply not necessary here - you can just use a local variable for the command, and concatenate or replace the certificate name.
DECLARE @SQLCertificate nvarchar(50) = N'00132';

DECLARE @cmd nvarchar(max) = N'EXECUTE [dbo].[DatabaseBackup]
          @Databases = N''USER_DATABASES'',
          @Directory = N''C:\SQLBackup'',
          @DirectoryStructure = N''{DatabaseName}'',
          @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
          @BackupType = ''FULL'',
          @Description = N''Full_Backup'',
          @CheckSum = ''Y'',
          @Verify = ''Y'',
          @Compress = ''Y'',
          @CleanupTime = ''0'',
          @CleanupMode = ''AFTER_BACKUP'',
          @Encrypt = ''Y'',
          @EncryptionAlgorithm = ''AES_256'',
          @ServerCertificate = ''$cert$'',
          @LogToTable = ''Y'';';

SET @cmd = REPLACE(@cmd, N'$cert$', @SQLCertificate);

IF NOT EXISTS (SELECT 1 FROM [dbo].[sysjobs] WHERE [Name] = N'Backups_Encrypted_FULL')
BEGIN
    EXEC dbo.sp_add_job
        @job_name = N'Backups_Encrypted_FULL',
        @enabled = 1,
        @notify_level_eventlog = 2,
        @notify_level_email = 0,
        @notify_level_netsend = 0,
        @notify_level_page = 0,
        @delete_level = 0,
        @owner_login_name = N'DBA_Maintenance',
        @description = N'Performs a FULL backup of all User Databases',
        @category_name = N'[Database Backups]';

    EXEC dbo.sp_add_jobstep
        @step_id = 1,
        @job_name = N'Backups_Encrypted_FULL',
        @step_name = N'Execute T-SQL',
        @subsystem = N'TSQL',
        @command = @cmd,
        @database_name = N'DBA.Maintenance';

    EXEC dbo.sp_add_jobschedule
        @job_name = N'Backups_Encrypted_FULL',
        @name = N'Saturday Night',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval = 0,
        @freq_relative_interval = 0,
        @freq_recurrence_factor = 1,
        @active_end_date = 99991231,
        @active_start_time = 220000,
        @active_end_time = 235959;

    EXEC dbo.sp_add_jobserver
        @job_name = N'Backups_Encrypted_FULL',
        @server_name = N'(Local)';
END


Why does data get corrupted in "large" text files - Reasons to use database I am just starting to learn about databases and using mysql.  I read that databases are more reliable than having large ascii text files and that ascii text files are more prone to corruption as they get bigger.

Can someone explain why this is the case and how databases safeguard against this in a high level?  Thank you.

I can only speak to MS SQL Server not MySQL I'm afraid.  SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here.  With a large text file any time you make a chance you re-write the entire file.  With a SQL Server when you make changes you only write down the PAGES that have changed.  This means that instead of writing megabytes an gigabytes or even terabytes you are writing down information in 8K chunks.  Less writing means less chance of causing corruption.

So what happens once you have some corruption?  Well in a text file you won't realize that the corruption exists until you happen to be looking at the corrupted portion of the file or the file will no longer open.  This means that over time more and more corruption will build up over time.  In SQL Server each page has a CHECKSUM built in.  This CHECKSUM is checked under several conditions.  Every time that page is written, when backups are taken with the WITH CHECKSUM option and when DBCC CHECKDB is run.  As a side note DBCC CHECKDB checks for a number of different possibilities of corruption not just the CHECKSUM.  Because of all this if you (or your DBA) is careful they will find the corruption more quickly.

Which leads us to repairing the corruption.  In a text file if your network admin is taking proper backups then you can hopefully restore your file back to before the corruption.  If you caught the corruption early enough.  If you didn't there may not be a backup old enough.  Also your only option is to restore the entire file.  This means any changes made to your file since the corruption occurred will be lost.  Databases provide a number of restoration methods.  If you are taking regular backups you can of course just restore the database.  This isn't much different than your text file.  However at least in SQL Server there are also options to restore parts of a database even down to the PAGE level.  And in fact with certain types of high availability there is even the option of automatically fixing a page as soon as corruption occurs.

Here is some additional reading for you.

SQL Skills postings on page checksums.

Understanding Pages and Extents

DBCC CHECKDB and database integrity

Sorry this is MS SQL Server and not MySQL but I imagine some of the principals are the same or at least very similar.


Postgres ignoring wal_level setting in conf I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for wal_level in the configuration file.

I have set in the config file:

wal_level = hot_standby
max_wal_senders = 5

However when I went to start Postgres I ran into the error:

  WAL streaming (max_wal_senders > 0) requires wal_level "archive", "hot_standby", or "logical"

If I set max_wal_senders = 0, the server starts fine, but running "show all" in psql shows that wal_level = minimal.

 -bash-4.2$ grep wal_level postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical

-bash-4.2$ psql -c 'show "wal_level";'
 wal_level
-----------
 minimal

Any idea why it would be ignoring the configuration value?

Nothing of note in the Postgres error log:

Oct 13 11:03:53 postgres[12324]: [3-1] LOG:  database system was shut down at 2016-10-13 10:55:35 UTC
Oct 13 11:03:53 postgres[12324]: [4-1] LOG:  MultiXact member wraparound protections are now enabled
Oct 13 11:03:53 postgres[12328]: [3-1] LOG:  autovacuum launcher started
Oct 13 11:03:53 postgres[12321]: [3-1] LOG:  database system is ready to accept connections

Just the one wal_level config line and archive_mode is enabled and configured to rsync archive files to the slave. I've uploaded the config here: http://pastebin.com/Vi3vByc1

I've checked and it's definitely loading the correct config file.

-bash-4.2$ psql -c 'show config_file;'
             config_file
--------------------------------------
 /data/pgsql/9.5/data/postgresql.conf
(1 row)

-bash-4.2$ grep wal_level /data/pgsql/9.5/data/postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical

Take a look at the postgresql.auto.conf file - it's populated if you changed it using alter system.
Also, make sure to restart the instance and not just reload it as this kind of change requires restart.


Check if table exists on a linked server I've had a search, but can't find a post relating to this instance specifically.

We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a table exists on the LINKED production server. For any given database/table combination on the server from which I am executing the SQL I would use an

IF OBJECT_ID(etc) IS NULL etc.

call, however this does not work when I reference the LINKED server. e.g.

IF OBJECT_ID('ZPRODSERVER.DM_Database.dbo.MyTable', 'U') IS NULL PRINT 'YES' ELSE PRINT 'NO'

returns "YES", even though I know this table exists, as when I select top 1 * from it I get table results. If I use:

IF EXISTS(select top 1 * from ZPRODSERVER.DM_Database.dbo.MyTable) PRINT 'YES' ELSE PRINT 'NO'

then I get "YES" returned, HOWEVER if the table doesn't exist, I don't get NO and instead I get an error message:

Msg 7314, Level 16, State 1, Line 90
The OLE DB provider "SQLNCLI11" for linked server "ZPRODSERVER" does not contain the table ""DM_Database"."dbo"."MyTable"". The table either does not exist or the current user does not have permissions on that table.

Is there a consistent method that I can use to determine if a table on a different server exists without incorrect results or an error message?

Thanks!

You can query the Information_Schema views on the linked server:

if exists(
        select  *
        from    [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES
        where   table_name = 'Table_Name'
                and table_schema = 'Table_Schema'
)
print 'Table Found'


PostgreSQL/ Linux, way to have DNS forward to a port/cluster? Aws linux(centos) ec2
Host: mydb. 10.10.10.10.
3 clusters: ports 5432, 5433, 5434

What I'd like to see is:
db2.mydb or mydb/db2 connect/fwd to cluster2 (5433).

Anything Linux or psql come to mind? In mssql I'd have named instances as an option

To clarify the needs a little better:
These new instances (clusters) will be used by various people. QA as a team or individually, Dev as a team or individually. There could be a need for 3, 10 or 30 clusters at any given time. Some clusters will remain active for weeks and others for hours.

A friendly name is purely for the human element.
I (as dba) would rather see a list of 20 names than 20 port numbers. But I could create a data driven page to translate for me, just need to track the mapping in a table.
The clusters will be used by people and in connection strings for applications.
The friendly name is self documenting, so there isn't any worry about confusion as to what port 6221 is in a file. Last week port 6221 was a dev sandbox, today it's a special hotfix branch.

In AWS the option exists to just have 1 linux server instance per cluster. Likely even easier to maintain in the long term, but AWS charges by the hour for each server, so it seems like a nightmare to keep turning off/on these linux server instances... or you look at a way higher cost having 30 vs 1 server running.

On a local virtual env I would just have 1 Linux, 1 Cluster, 1 DB each. All default port and I can map dns to that servers IP no problem.

Did any of this information help clarify? Anything else I can add to help?

The PostgreSQL protocol cannot support hostname based virtual servers, because the client doesn't send the hostname of the server in the initial connection packet like HTTP does in the Host header.

Your only option is to add additional Elastic IP addresses. You will need to be able to tell which traffic came from which IP. Because EC2 instances don't have their public IPs directly associated with their network interfaces you can't do this the normal Linux way of just having multiple IP addresses for an interface. Instead you must create multiple interfaces and bind one IP to each interface.

You can then have PostgreSQL instances listen on port 5432 on each interface by setting listen_addresses to the interface's private internal IP address, not the public address.

You must also change the unix_socket_directories setting of each server so they put their listening sockets in a different directory, or set it to empty to disable unix socket connections. Otherwise they'll all try to grab the same socket filename in /tmp or /var/run/pgsql (depending on how Pg was compiled) and all but one will fail to start with an error.

Personally I would be using Amazon VPC for this task. It gives you much finer control over interface and address management than EC2 Classic does. It's also quite easy to script the addition/removal of interfaces via the EC2 APIs.

A handy advantage of using IPs for this is that you can discard the IP when you discard the cluster, and add a new different IP when you bring up a new cluster. So you shouldn't have to deal with the port-reuse confusion problem you outline in your question.

It's also worth noting that you could use a proxy like PgBouncer to listen on the different IPs and forward connections to Pg running on a single node on different ports, if you needed to do that for some reason.


Validate id sequence consistency in records in Redshift I have a backend that log event to Redshift, for each event it generates a unique id. The id is a sequential number.

I have something like (events table):

+-------------------------+------+
| created_at              |  id  |
+-------------------------+------+
| 2017-06-30 09:20:47 UTC | 100  |
| 2017-06-30 09:18:31 UTC | 101  |
| 2017-06-30 09:16:19 UTC | 102  |
| 2017-06-30 09:12:08 UTC | 103  |
| 2017-06-30 09:11:59 UTC | 104  |
| 2017-06-30 09:11:15 UTC | 105  |
| 2017-06-30 07:03:41 UTC | 106  |
+-------------------------+------+

I have task than run every hour that move few records to an other table (deactivated_events).

I would like to verify that for the last 3 hours I don't have any missing record using id sequence.
First I thought about using generate_series but this is not present in Redshift. Other suggested to make a table with only the ids but that's still a pain to populate database with integer (here one example to generate 1 million

I'm wondering if the best is not use min max and count like this :

WITH merged_events AS
  (SELECT *
   FROM
     (SELECT id, created_at
      FROM events
      UNION
      SELECT id, created_at
      FROM deactivated_events
     )
   WHERE created_at > GETDATE() - INTERVAL '3 hours'
   ORDER BY id)
SELECT COUNT(*), (max(id) - min(id) + 1) AS diff
FROM merged_events;

PS: Bonus, how to found missing or duplicate records?

Let's imagine you have this setup:

INSERT INTO events
    (created_at, id)
VALUES
    ('2017-06-30 09:20:47 UTC', 100),
    -- ('2017-06-30 09:18:31 UTC', 101), -- Missing row
    ('2017-06-30 09:16:19 UTC', 102),
    ('2017-06-30 09:12:08 UTC', 103),
    ('2017-06-30 09:11:59 UTC', 104),
    ('2017-06-30 09:11:15 UTC', 105),
    ('2017-06-30 07:03:41 UTC', 106) ;

and ...

INSERT INTO deactivated_events
    (created_at, id)
VALUES
    ('2017-06-30 07:03:41 UTC', 97),
    ('2017-06-30 09:11:15 UTC', 98),
    ('2017-06-30 09:11:15 UTC', 99),
    ('2017-06-30 09:18:31 UTC', 100)     -- Repeated row
    ;

Provided that Redshift provides Window Functions, you can use a slight variation of your query to do two things:

Consider that there are repeated ids when you group by id and find the count to be > 1
Consider that there is a missing row (or more!) if the id of the previous row is not 1 + the current one. This is done by means of the LAG function.

This can be done with the following query

WITH merged_events AS
(
  SELECT
      id
  FROM
  (
      SELECT
          id
      FROM
          events
      UNION ALL  /* Must be UNION ALL, because we want to find repeated values */
      SELECT
          id
      FROM
          deactivated_events
  ) AS q0
  WHERE true -- In practice, created_at > GETDATE() - INTERVAL '3 hours'
)
SELECT
    id,
    count(id) > 1 AS repeated_event,
    ((lag(id) OVER(ORDER BY id)) /* previous id */ + 1) <> id AS previous_event_missing
FROM
    merged_events
GROUP BY
    id ;

This will produce:

 id | repeated_event | previous_event_missing
--: | :------------- | :---------------------
 97 | f              | null
 98 | f              | f
 99 | f              | f
100 | TRUE           | f
102 | f              | TRUE
103 | f              | f
104 | f              | f
105 | f              | f
106 | f              | f

You can check the whole setup (with PostgreSQL instead of RedShift) at
dbfiddle here

Caveats: your id might have gaps if it is actually from an IDENTITY column (or a SEQUENCE it Redshift happens to implement it, which I don't think is the case). So, you should first guarantee by some other means that your ids are originally consecutive...

References:

Sequence number generation function in AWS redshift which discusses different ways of getting sequences, and its tradeoffs.
How to find missing values in a sequence with SQL which shows different alternatives.


Count per week per column PostgreSQL Let's say I have a PostgreSQL table with 10 columns and 2 weeks (w1 w2) with 5 countries. Something that would look like this :

What I would like to do is a count per week and per column like this :

As count() counts only the non-NULL values, this is a simple GROUP BY query:

SELECT week, count(col1), ..., count(col10)
  FROM your_table
 GROUP BY week;


Full-text search on column for existence of a keyword from a table I have a table of keywords and I want to query another table and return rows where a column contains at least one keyword.

Is it possible query like that using full text indexes?

If not is there a more efficient way than the one below - dynamic SQL perhaps?

SELECT
    *

FROM
    dbo.Notes AS NotesContainingKeyword

WHERE
    EXISTS
    (
        SELECT
            *

        FROM
            dbo.Notes
            CROSS JOIN dbo.Keywords

        WHERE
            Notes.Note LIKE '%' + Keywords.Word '%'
            AND Notes.Id = NotesContainingKeyword.Id
    )

Concatenating the keywords with an OR(|) separator into a variable appears to work.

DECLARE
    @Keywords NVARCHAR(4000)

SELECT
    @Keywords = STUFF((
                    SELECT
                        '|' + Word

                    FROM
                        dbo.Keywords

                    FOR XML PATH('')
                ), 1, 1, '')

SELECT
    *

FROM
    dbo.Notes

WHERE
    CONTAINS(Note, @Keywords)


Cannot connect PHP to SQL Server in no way I installed SQL Server and it shows enabled in phpinfo(). But when setting up joomla on database config page when I choose SQL Server and try to connect the following error comes up

  Could not connect to the database. Connector returned number: Database sqlsrv_connect failed, Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 67 [code] => 67 [2] => [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [67]. [message] => [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [67]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired [message] => [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 67 [code] => 67 [2] => [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )

This is NOT 18456 (login failed). The error says

  Server is not found or not accessible

And it means that you should control that:

SQL Server is started and running
you use the correct instance name (so show us your connection
string), and if the instance is named, SQL Server Browser also should
be running
TCP\IP is enabled

You cannot find anything in error log because the server was not reached at all.


Query optimization when multiplying by zero I'm on SQL Server and I have a query like this:

select * from MyTable where a * expensive_function() > 0

I noticed the server always runs expensive_function(), even for rows where a = 0 (and also if I replace a with a literal 0).

Is there a clear reason for that?

Do other servers optimize queries like this?

Note: This query is trivial, and could be changed to avoid running the function. My question isn't about optimization of this query, but to know if/why/which servers would optimize (or not) queries like this.

Change your query to -

select  *

from    MyTable

where   case
            when    a <> 0
            then    case
                        when    a * expensive_function() > 0
                        then    1
                    end
        end = 1

As for the questions -
An optimizer does what optimizer does.
It is an application with features that are supported and features that are not supported as any other application.
No application is being created right away with all possible features and no development team has unlimited resources and motivation to develop any possible feature including those with insignificant business value.

For @a_vlad

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

declare @x int = 0;
select 1 where dbo.myfunction(@x) = 1 and 1/@x = 1

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

For @Myberg

create function dbo.myfunction (@i int) returns int as begin return 0 end
GO

create table #t (i int);
insert into #t (i) values (0);

select  1

from   (select  *
        from    #t
        where   dbo.myfunction(i) = 1
        ) t

where   1/i = 1

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


Why does Management Studio add the second alter table "CHECK CONSTRAINT" for a foreign key constraint ALTER TABLE [dbo].[REMINDER_EMAIL]  WITH CHECK ADD  CONSTRAINT
  [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] )
  REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] )

ALTER TABLE [dbo].[REMINDER_EMAIL] CHECK CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID]

With foreign key and check constraints, specifying CHECK or NOCHECK enables or disables it.  It's just explicit DDL that is generated by the GUI that, in your case, enables the foreign key constraint:

  { CHECK | NOCHECK } CONSTRAINT
  Specifies that constraint_name is
  enabled or disabled. This option can only be used with FOREIGN KEY and
  CHECK constraints. When NOCHECK is specified, the constraint is
  disabled and future inserts or updates to the column are not validated
  against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
  constraints cannot be disabled.

Reference: BOL ALTER TABLE

It is redundant and unnecessary, but I just take it as "verbose". If I was to defend it forcefully, I'd say it leaves the script reader without any question of functionality.


Oracle Triggers - Before Update and Before Insert - Separate or Combined? I'm running Oracle DB 9i.

I have a table with various constraints to ensure data integrity.  In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that necessary data goes into the table, in some cases allowing the application layer to omit the information from their queries, and in some cases forcing that it be present.

The actions that I need to do on my BEFORE INSERT are different than my BEFORE UPDATE.

My question is this:

Can I have one common trigger that uses IF / THEN, or should I make separate triggers?

For example:

-- Scenario 1
   CREATE OR REPLACE TRIGGER   my_trg
              BEFORE INSERT OR UPDATE ON my_table
                 FOR EACH ROW
   BEGIN
      IF UPDATING THEN
         IF :NEW.my_col IS NULL THEN
            raise_application_error(...);
         END IF;
         IF :NEW.my_col2 IS NULL THEN
            :NEW.my_col2 := (...);
         END IF;
      END IF;
      IF INSERTING THEN
         IF :NEW.my_col3 IS NULL THEN
            SELECT my_seq.NEXTVAL INTO :NEW.my_col3 FROM DUAL;
         END IF;
         IF :NEW.my_col4 IS NULL THEN
            (...)
         END IF;
      END IF;
   END;
/

Or:

-- Scenario 2
   CREATE OR REPLACE TRIGGER   my_trg1
              BEFORE INSERT ON my_table
                 FOR EACH ROW
   BEGIN
      IF :NEW.my_col3 IS NULL THEN
         SELECT my_seq.NEXTVAL INTO :NEW.my_col3 FROM DUAL;
      END IF;
      IF :NEW.my_col4 IS NULL THEN
         (...)
      END IF;
   END;
/

CREATE OR REPLACE TRIGGER   my_trg2
              BEFORE UPDATE ON my_table
                 FOR EACH ROW
   BEGIN
      IF :NEW.my_col IS NULL THEN
         raise_application_error(...);
      END IF;
      IF :NEW.my_col2 IS NULL THEN
         :NEW.my_col2 := (...);
      END IF;
   END;
/

Currently, these two triggers are combined.  Should I separate them out?

The actions you're performing on the data is technically different between the insert/update portions, but what about the business logic difference? IMO, your best bet is to contain a functional unit of work inside of a trigger, so that there is no discrepancy in what that trigger does. In my experience, Developers/Business Analysts often tend to think in transaction workflows.

I have found this to be especially helpful when I or others have to troubleshoot the consequences of user data corruption as it relates to triggers being fired - the less triggers you have to try to piece together the logic for, the easier it is to see how data was manipulated.


Where can I find database tuning exercises for learning? As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.

I'm looking for a database with a bunch of tables, data, queries, triggers, SPs and so on, in which there are deliberate performance problems, and hopefully a series of exercises in which you attempt to identify and resolve the problems. Ideally this will be on MS SQL server.

Anyone know of this kind of thing?

Paul Randal provides some sample DBs that are actually corrupt: http://www.sqlskills.com/BLOGS/PAUL/post/Conference-corruption-demo-scripts-and-example-corrupt-databases.aspx

Good exercise to go through working with DBCC commands and such on SQL Server.


Resolving a performance issue with BETWEEN join - eager spool This query (anonymized) takes about 2 minutes to execute.

SELECT
    ly.Col1
    ,sr.Col2
    ,sr.Col3
    ,sr.Col4
INTO TempDb..TempLYT
FROM Tempdb..T1 ly
JOIN TempDb..T2 sr on sr.[DateTimeCol] BETWEEN ly.DateTimeStart and ly.DateTimeEnd
WHERE sr.Col5 = 1 OR sr.Col5 = 2

Are there some alternatives that would help with this query?

After applying the index suggested by Paul White, the query plan looks like this:

Ensure you have an index on T1 like:

CREATE INDEX i ON T1 (dtstart, dtend) INCLUDE (col1);

You might reverse the key columns depending on which is more selective with your data set.

The optimizer tends to introduce an eager spool for one of the inequalities and leave the second one in a Filter. Repro:

CREATE TABLE #T1 (col1 integer, dtstart datetime, dtend datetime);
CREATE TABLE #T2 (col2 integer, col3 integer, col4 integer, col5 integer, dtcol datetime);

UPDATE STATISTICS #T1 WITH ROWCOUNT = 5000;
UPDATE STATISTICS #T2 WITH ROWCOUNT = 100000;

SELECT
    T1.col1,
    T2.col2,
    T2.col3,
    T2.col4
FROM #T1 AS T1
JOIN #T2 AS T2 ON T2.dtcol BETWEEN T1.dtstart AND T1.dtend
WHERE T2.col5 IN (1, 2)
OPTION (QUERYTRACEON 8690); -- No perf spool

With the index:

Naturally, the seek is only for the first index column. The second will be a residual predicate.

Generally, interval queries can be hard to optimize. Dejan Sarka and Itzik Ben-Gan have some good coverage on the topic:

Interval Queries in SQL Server Part 4 - Dejan
Interval Queries in SQL Server - Itzik


Altering the location of Oracle-Suggested Backup On one database, the Oracle-Suggested Backup scheduled from Enterprise Manager always ends up in the recovery area, despite RMAN configuration showing that device type disk format points elsewhere.

As far as I can see, the scheduled backup job is simply:

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA_OEM_LEVEL_0';
backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
}

Asking RMAN to show all reveals that device type disk is indeed configured to store elsewhere:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/s01/backup/PROD11/PROD11_%U';

If I run the script manually,  the backupset is placed at the above location, when the script is run from the job scheduler the backupset goes to the RECO group on ASM,

Why might Oracle still choose to dump the backupset to the db_recovery_file_dest?

Ultimately, how can I change the backup destination?

I cannot say for sure why RMAN behaves in this way, but as a workaround you can edit the resulting script within the same workflow in EM before the job is created:

 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U';

This will ensure the backups are placed in the destination you specified regardless of RMAN settings in control file saved with CONFIGURE command.


Mysql Alter Tables took 5 hours to add 1 column Had an issue recently where to add a column on a table with 1 million rows took 5 hours. I added a column with default value of 1. This was done during the night so there was not much activity on the database but it did have many locks once this started.

Looking at the server stats CPU, memory and hard drive space were all well below half of their maximums. So I'm not sure why this took so long the table in question only has 7 columns including the new one. Data types are int(11), datetime or varchar(45).

I'm using MySQL Cluster 7.3.3, with 4 data nodes all of which are in the same datacenter.

Can anyone advise why it would take 5 hours to simply add a column?

Table structure:

CREATE TABLE ddi_t (
    id int(11) NOT NULL AUTO_INCREMENT,
    ddi varchar(45) NOT NULL,
    is_deleted tinyint(1) NOT NULL DEFAULT '0',
    created_time datetime NOT NULL,
    module_group_id int(11) DEFAULT NULL,
    created_user_id int(11) DEFAULT NULL,
    supplier_id int(11) DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY uq_ddi_1 (ddi),
    KEY fk_ddi_1_idx (module_group_id)
) ENGINE=ndbcluster AUTO_INCREMENT=935352 DEFAULT CHARSET=latin1

In my experience with MySQL Cluster, I found pt-online-schema-change as a very handy tool for appling live schema changes.

It works this way:

it creates a new real table B (with temporary purposes) as clone
of the table A that you want to change
then applies the schema change to table B. The operation is fast cause the table is empty and has no traffic on it.
it creates a trigger so that new records inserted in table A will be copied into table B
then slowly copies records from A to B in small chunks of data
them with atomic RENAME TABLE operations B becomes A

I'm not sure if it works with all kind of schema changes.

If the table is big, it will take a long time, but the table will remain available for read/write queries.

The mysql user that runs pt-online-schema-change must have particular privileges, but this is not clearly explained in the documentation (I've opened a bug ticket about this)


MySQL queries still slow after deleting bunch of records I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of table locks. I am a developer but no mysql/database expert. My host suggested that I either delete and/or change the two tables in question to innoDB. So as these tables had a lot of junk data I decided to delete a bunch of records. I would say that these two tables are about 25% of the size when this problem started. Problem is that, it still has not made any difference. So my questions:

Do I need to clear cache or optimize table in order to see the
effects? My host is still suggesting I change those tables to innoDB
which is fine but I'm not sure why deleting that many records have
not made a difference.
Also I have read that it is better to
recreate the table than just optimizing? If needed I can hire a
database admin to help me but I would like to at least try a few
things if this is something simple. Can someone please guide me
through this.

One more important thing to add, it is a legacy website running on php 5.4 and mysql 5.6

Rather than change a pair of tables to InnoDB, it would be better for every table in the database to be converted as InnoDB is a much better storage engine. That said, MyISAM is workable if you don't mind babysitting it every once in a while.
Do I need to clear cache or optimize table in order to see the effects?
⇢ The database engine will clear the cache on its own, but you can force this by restarting the server. Optimising the tables is certainly recommended for MyISAM tables if you haven't done so in a while. This will ensure that the data is in a consistent state and that the indexes are properly linked.
Also I have read that it is better to recreate the table than just optimizing?
⇢ It is sometimes faster to recreate than optimise. If you plan on tackling just the indexes, then DROP and CREATE are your friend. If you want to also check the integrity of the tables, then OPTIMIZE TABLE is what you're looking for. If your site doesn't get too much traffic, you could even look at dropping the index, optimising the table, then recreating the index.
Ideally this should be done for every table in the database, not just the two in question. Databases, like many other things, need the occasional bit of maintenance to remain reliable.


DB2 - Inserting a timestamp operation result in a timestamp field I got a some javascript Dojo code assembling a query from JSON variables and I have been trying to insert a timestamp in it.

This is my test table (schema.mdummy):

k and v are varchars
n is an integer
t is a timestamp

I found out these statements work fine:

INSERT INTO mschema.MDUMMY (k, v)
VALUES ((SELECT k FROM mschema.MDUMMY WHERE k LIKE 'xp3'), '33');

SELECT TIMESTAMP('1965-07-27', '15:30:00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1;

But this one does not work as expected.

INSERT INTO mschema.MDUMMY (k, n, t) VALUES (
 '70',
 70,
 (SELECT TIMESTAMP('1965-07-27-15.30.00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1)
);

Is it some error on the syntax or is it impossible to perform in DB2?

DB Server OS is the latest Fedora server.

Following sequence of statements worked fine in Db2 (for LUW)

create table mschema.MDUMMY (k varchar(10), n int, t timestamp)

INSERT INTO mschema.MDUMMY (k, n, t) VALUES (   '70',   70,   (SELECT
TIMESTAMP('1965-07-27-15.30.00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1)
);

SELECT * FROM mschema.MDUMMY

I just copied the second statement from your question.
This means you made some other mistake running it or you are maybe not running it on Db2 LUW - this is why it is always useful to specify the OS.


mysql myisamchk vs mysqlcheck We have a mysql database running version 5.0. Every month we shutdown the database for maintenance. We run a myisamchk on the tables to fix data fragmentation, and this takes down the system for up to an hour.

Will it be better to run optimize table daily via a cron so as to eliminate the downtime or is it necessary to do the myisamchk periodically ?.

I have a beautiful idea for you on this one...

If you can setup the following:

Circular Replication

Two DB Servers
Each Server is a Master to the Other
Each Server is a Slave to the Other

Use a DBVIP that will point to one DB Server
Confine all Reads/Writes to the DBVIP

You can perform this OPTIMIZE TABLE maintenance on the DB Server that does not have the DBVIP.

First, construct the script on the Slave (the DB Server that does not have the DBVIP) that will run OPTIMIZE TABLE on all your tables. Then, run it on the Slave:

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY (data_length+index_length)"
echo "SET sql_log_bin = 0;" > /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/OptimizeAllTables.sql
mysql ${MYSQL_CONN} < /root/OptimizeAllTables.sql

The first line of the script is SET sql_log_bin = 0;. This tells the session not to record OPTIMIZE TABLE in its binary logs. This prevents OPTIMIZE TABLE from running on the Master (the DB Server with the DBVIP). Once a month, you can just take down the DBVIP from one DB Server and bring up the DBVIP on the Other DB Server. When you do that, you must also setup the OPTIMIZE TABLE script to on the Other Machine. The key is to run this on the DB Server that does not have the DBVIP.

Give it a Try !!!


Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i Does Visual Studio 2017 Community (.NET Framework 4.6.2) support Oracle 8i?

I am trying to connect with .NET Framework 4.6.2 to a production Oracle 8i database.

I googled it, but I can not find anything which is related to this topic.
I checked also this site but that is for Windows 2008 R2.

I tried Oracle 19 and Oracle 18 and it works. But I can't find what the oldest version is that is still compatible with .NET Framework (like Oracle 8 or Oracle 9) .

I tried to connect using ODP.NET Managed Driver but it was unsuccessful.

I am an Oracle Developer and Web Developer for the organisation. I have been given the task to test it if it's possible to connect on our  (TEST) database which is Oracle 8i (I believe). Our main database is Oracle 10, but this data is restricted and I cannot manipulate it. This part is for our DBA.

I don't have an 8i database handy to test. If you care about your data it'd be wise to upgrade to a supported version of Oracle ASAP. The last version of Oracle client that can connect to Oracle 8i is an Oracle 10 client (and you need to be on Oracle 8.1.7 server-side).

If you can find an Oracle 10 client install (either full-fat or the instant client), then you might be able to get it to work. - Phil

The article referenced in the question is rather bad. Listed prerequisites are wrong/outdated (was even in 2015), it does not properly use the tnsnames.ora file, and does not consider 32/64-Bit topic.

See my answer to Does ASP.NET MVC5 support Oracle 8i on Stack Overflow. - wernfried-domscheit


How to save mongodb data sub data more properly in this case? I have a business collection.

The businesses are reviewed by users.

Each user can only review a business once. Future reviews will simply replace the former one.

This is how we store it:

 "Reviews" : {
    "gusyanto" : {
      "day" : "December 21, 2012, 8:08 am",
      "review" : "Tes review"
    },
    "sapi" : {
      "day" : "December 21, 2012, 8:18 am",
      "review" : "makanan ini sangat enak"
    }
  }

Alternatively we can also use this:

 "Reviews" : [{
      "userid" : "gusyanto",
      "day" : "December 21, 2012, 8:08 am",
      "review" : "Tes review"
    },
    {
      "userid" : "sapi",
      "day" : "December 21, 2012, 8:18 am",
      "review" : "makanan ini sangat enak"}
    ]
  }

So basically we want can store it as a dictionary where the key is the userid or we can store it as an array where userid is the field key. So the key in the second approach is static, namely userid.

We want to index the entries. For example, a user may want to know what businesses he has reviewed.

Which approach I should use?

AFAIK, MongoDB queries are more optimized for your second approach.

with the first approach you will probably have to do a query like:

db.businesses.find({'Reviews.gusyanto': {$exists: true}})

Which is less efficient than using:

db.businesses.find({'Reviews.userid': 'gusyanto'})


query for each row from another arbitrary query, and union results I'm trying to build a query that will take another query. Then, perform another query based on those results. Union the results so that there are no duplicates. All on the same table. I've seen some similar posts here and here, but the "input" query could be almost anything and the number of columns may change.

Here's a sample dataset:

  num  |  sub  |                      valid_time                      | col1 | col2  |  ...
-------+-------+------------------------------------------------------+------+-------+------
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   1   |   2   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   2   |   4   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...

Based on some of the examples I was seeing with JOINs, a subset of columns were typically picked out.  I'm not sure how to return all columns with a JOIN. The number of columns may change over time, and all the columns need to be returned.  I would rather not have to update this query if a new column is added.

This does not work (I think because I need to JOIN somehow), but it shows where my head is at.

with cte as (
    select *
    from table
    where col1 = 'X' and
    now() <@ valid_time
)
select * from cte
union
select * from table
    where table.num = cte.num and
    table.sub = cte.sub and
    now() <@ valid_time;

I mentioned earlier that the "input" query could be almost anything.  I plan to templatized similar to:

with cte as (%(query)s)
select * from cte
union
select * from table
    where table.num = cte.num and
    table.sub = cte.sub and
    %(query_time_part)s;

Finally based on the example query, I would expect something like so:

  num  |  sub  |                      valid_time                      | col1 | col2  |  ...
-------+-------+------------------------------------------------------+------+-------+------
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   1   |   1   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Y  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   X  |  ...  |  ...
   2   |   3   | ["2018-12-31 01:00:00-00","2019-12-31 01:00:00-00")  |   Z  |  ...  |  ...

How about:

with cte as (%(query))
select unique table.* from table join cte using (num,sub)
    where %(query_time_part);

I'm assuming here that "%(query_time_part)" is written such that it applies to the literal "table", while "%(query)" already contains the analogous code which applies to the "table" underlying "cte".

The rows that make up the cte should automatically meet the join condition to self-join, so should not need any special steps, like a UNION, for them to be emitted in their own right.  That is, the version of them that get emitted is the version that comes from "table", not from "cte", but since they are identical it doesn't matter.


sqlcmd: no rows affected When I run exec dbo.subprogram from SQL Server Management Studio, it executes and returns result as many rows affected.

But when I run from cmd
C:\Users\mssadmin>sqlcmd -S servername -d databasename -q "EXIT( exec dbo.subprogram)"
it returns as 0 rows affected.

What could be the problem?

If you really want to return the number of rows, You need to create a text file A.SQL:

exec dbo.subprogram;
:EXIT(select @@rowcount)

Because EXIT must be at the beginning of a line, so you can't use -Q. Then you call:

C:\> SQLCMD -S servername -d database -i A.SQL -o A.OUT

Then the returned values by EXIT will be in ERRORLEVEL... You can query with:

C:\> ECHO %ERRORLEVEL%


improve MySQL text searches What MySQL settings improve text searches?
Which resources (memory, cpus, disk: ssd) to add first to increase performance?

Setup

Amazon RDS

type: db.t2.medium (2 CPU; 4G mem)
MySQL 5.6.27

Application: Redmine 3.3.0
Response time with simple but very generic search (for example: 'the') with no other load: ~15 sec.
Not using  InnoDB FULLTEXT Indexes
Info on Redmine DB:
[--] Database: redmine
[--]  +-- TABLE: 81
[--]  +-- COLL : 3 (utf8_general_ci, utf8_unicode_ci, latin1_swedish_ci)
[--]  +-- ROWS : 5114804
[--]  +-- DATA : 1.3G(81.79%)
[--]  +-- INDEX: 288.5M(18.21%)
[--]  +-- TOTAL: 1.5G
[--]  +-- ENGIN : 1 (InnoDB)

MySQL Settings

binlog_cache_size: 32768
binlog_format: MIXED
bulk_insert_buffer_size: 64000000
default_storage_engine: InnoDB
explicit_defaults_for_timestamp: 1
gtid-mode: OFF
innodb_buffer_pool_size: {DBInstanceClassMemory*3/4}
innodb_file_per_table: 1
innodb_flush_method: O_DIRECT
innodb_ft_cache_size: 80000000
innodb_log_buffer_size: 8388608
innodb_log_file_size: 134217728
key_buffer_size: 16777216
local_infile: 1
log_output: TABLE
log_slave_updates: 1
master-info-repository: TABLE
max_binlog_size: 134217728
max_connections: {DBInstanceClassMemory/12582880}
max_heap_table_size: 512000000
performance_schema: 0
query_cache_limit: 512000
query_cache_size: 512000000
query_cache_type: 1
read_buffer_size: 262144
read_rnd_buffer_size: 524288
relay_log_info_repository: TABLE
relay_log_recovery: 1
sync_binlog: 1
table_open_cache_instances: 16
thread_stack: 262144
tmp_table_size: 512000000

query_cache_size: 512000000 plus query_cache_type: 1 is really bad for performance.

If the QC is on, then every modification to a table will cause a purge of all rows for that table in the QC.  The bigger the QC is, the longer it takes.  I recommend only 50M for the size.  Or turn the QC off.  All

"the" is a poor test case since it is (assuming English) so very common. Pick some word that occurs only a few times.  Or, maybe a word that occurs once, a word that occurs about 10 times, and a word that occurs 100 times.

max_heap_table_size: 512000000 and tmp_table_size: 512000000 are also bad because they are 12% of RAM; lower to 1% of RAM.  In complex queries, those limits come into play.  If there are several complex queries running at once, you could blow out RAM.  Swapping is really bad for performance.

If you can't permanently change those settings, complain to the provider.  If they won't listen, then change providers.  That's how bad I think the settings are.  No change to "memory, cpus, disk: ssd" can fix the query_cache_size problem.


Modeling discounts - Product specific and Customer specific discounts I have a reasonably simple database but have the following problem:

I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to be discounted, but also provide discounts to all purchases by a specific customer (I have a table that holds customers). Now when I think of it, I might even want for specific stores to have discounts or maybe discount specific products to specific customers or any combination of these.

Normally, the sane way to approach this would be to create a table with sales, and maybe another one with sale types. But I really can't wrap my head around it. How would it actually look like? Any help?

I think that my actual DB model isn't relevant, but I'll update the post with it if it's needed.

You should have a table for each class of thing you want to offer a discount on.  From your question that would be a table each for products, customers and stores.  Most likely you will have these tables in your design already. Each will have a column of an appropriate type to hold the discount. You don't say what you will offer but a percentage discount could be a TINYINT UNSIGNED since it can only be in the range 0 to 100 or a DECIMAL if you prefer to handle your math that way, and so forth if you'll offer fixed-amount discounts ("$10 off your purchase over $120. This month only!"). Name your column to match your rule e.g. DiscountPercent or DiscountAmount.  If it is important to track changes to discounts over time separate tables keyed by date will be required e.g. CustomerDiscount (CustomerID, StartDate, EndDate, DiscountPercent).

Be sure to work out with your business how these rules interact.  If I'm a 5% customer buying a 6% product do I get an 11% discount or a (0.95 * 0.94) price?  For a $100 item will I be charged $89.00 or $89.30? Can I even get more than one discount on a single purchase? Which?

If only a few combinations receive the discount you could have an intersection table keyed by, say, product and customer as you mention in your question. This works up to a couple of levels then gets very unwieldy. Then a general decision table will be your best bet. This will have a column for each of your decision variables (product, customer and store in your examples) and another for precedence/ sort order. You manually assign the precedence to match your rules and at run time pick the highest precedence which matches this sale's parameters.

An example:

Store      Product  Customer  Precedence  Discount%
Sao Palo                               1          2
Melbourne                              1          2
           Foo                         2          3
           Bar                         2          3
                    Chris              3          5
                    Sam                3          5
           Foo      Sam                4          7

Sam buys some Foo in Melbourne.  She gets 7% off since that's the row with the highest Precedence that matches all the criteria (or has a NULL). Anything else Sam buys has a 5% discount. Anyone else who buys Foo gets 3% off.

It is usual to copy relevant values to the Sales table for each transaction. This gives you
a) a complete audit history, which accountants like
b) flexibility to award non-standard discounts.


How to Export SQL Server Database To A File For Later Import To Different Computer I'm using the Sql Server 2012 Import and Export data (64) i'm not sure what options i should be picking to do what i want. I just want to be able to export all the data in the database to a file which i can then send to my other machine and then import the database.

The databases just contains tables.

I upvoted the comment by @Alex K. as this, I believe, is the easiest way of accomplish what you want. Backup the database, when asked copy the .bak file where you think is better for your needs, maybe even you can copy it already in a place where the other PC has access. Then do the opposite on the other SQL Server instance on the other PC, restore the database from the .bak previously saved.

Here is a link to a detailed description on how to backup the database using SSMS.

And here how to restore using the SSMS.


Partitioning on a computed column Using - Oracle 11g

I have created a table say X. of this table, a column say C3 is a computed column which will have a value based on incoming values for the other 2 columns say C1 and C2. I am using a BEFORE INSERT trigger for the same.

The table is partitioned by Range on the computed column C3 and this is a Date column.

Now, when I try to insert data into the computed column using a Trigger, the system dumps all data into the default partition rather than creating individual partitions. Instead, when I use the computation formula directly in my Insert query; the partitioning is fine and as expected.

Any idea?

Thanx

I sounds like you are doing partitioning based on a virtual column.

virtual_column_based_partitioning