How do I rank the total score in order of positions How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position

CREATE TABLE `scores` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` int(11) default NULL,
  `sessions_id` varchar(11) default NULL,
  `term_id` int(11) default NULL,
  `ass1` int(11) default NULL,
  `test1` int(11) default NULL,
  `proj1` int(11) default NULL,
  `ass2` int(11) default NULL,
  `test2` int(11) default NULL,
  `proj2` int(11) default NULL,
  `exam` int(11) default NULL,
  `class_id` int(11) default NULL,
  `subject_id` int(11) default NULL,
  `total_score` int(11) default NULL,
  `class_average` decimal(6,2) default NULL,
  `highest_score` int(11) default NULL,
  `lowest_score` int(11) default NULL,
  `position_in_subject` int(11) default NULL,
  `grade` varchar(2) default NULL,
  `teacher_remark` varchar(50) default NULL,
  `date_created` varchar(20) default NULL,
  `employee_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

and here is my query

INSERT INTO `scores` VALUES ('1', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('2', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('3', '1', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('8', '5', '2014/2015', '2', '5', '6', '6', '6', '6', '6', '6', '1', '7', '41', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('9', '5', '2014/2015', '2', '5', '4', '5', '4', '5', '4', '34', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('10', '5', '2014/2015', '2', '5', '4', '4', '5', '5', '4', '5', '1', '9', '32', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('15', '8', '2014/2015', '2', '6', '6', '6', '6', '7', '7', '7', '1', '7', '45', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('16', '8', '2014/2015', '2', '4', '3', '4', '3', '4', '3', '40', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('17', '8', '2014/2015', '2', '4', '5', '4', '4', '5', '4', '23', '1', '9', '49', '35.00', '63', '22', null, '', null, '2015-04-05', '1');

-- suggestion include a student with the same total so that calculating the rank can be QA in a proper way

INSERT INTO `scores` VALUES ('4', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('5', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('6', '2', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');

I hate giving complete solutions but here you go:

START TRANSACTION;
create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc
FROM
    scores
GROUP BY student_id);
select student_id,  tot_sc,
FIND_IN_SET(
tot_sc
, (SELECT
    GROUP_CONCAT(
    distinct tot_sc
        ORDER BY tot_sc DESC) AS score
FROM
    total_sc_tmp)) as rank from total_sc_tmp;
  COMMIT;

reorder, use @row and avoid the tmp table if you want.
Happy Easter to all of you with constructive comments


Dynamic Joining Across Databases I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is updated every time certain data from the client databases are updated. The 'Updates' table is setup somewhat like this:

DbName     CustomerId
ClientA    1
ClientB    1

I would like to join this data to a table in the database denoted in the 'DbName' column and end up with something like this:

DbName     CustomerId     CustomerName
ClientA    1              Bob Jones
ClientB    1              John Smith

What is the cleanest and most efficient solution for this problem?

In order to help you with the dynamic SQL, I've put together a small proof-of-concept:

USE Test; /* this could be TempDB */

CREATE TABLE DatabaseNames /* this is the table that exists in your
                                  "separate" database */
(
    DatabaseID INT NOT NULL CONSTRAINT PK_DatabaseNames_DatabaseID
                PRIMARY KEY CLUSTERED
    , ClientName NVARCHAR(255) NULL
    , TargetTableName NVARCHAR(255) NULL
    , SomeValue NVARCHAR(255) NULL
);

INSERT INTO DatabaseNames (DatabaseID, ClientName, TargetTableName, SomeValue)
SELECT d.database_id, d.name, 'TestTable', 'Whatever'
FROM sys.databases d
WHERE d.database_id>4;

DECLARE @cmd NVARCHAR(max);
DECLARE @sep NVARCHAR(max);

SET @cmd = '';
SET @sep = '';

SELECT @cmd = @cmd + @sep + 'SELECT * FROM TestDB.dbo.DatabaseNames DN
         INNER JOIN ' + QUOTENAME(DB_NAME(DatabaseID)) + '.dbo.' +
         QUOTENAME(TargetTableName) + ' CN ON DN.SomeValue = CN.SomeValue;'
        , @sep = CHAR(13) + CHAR(10)
FROM DatabaseNames;

SELECT @cmd; /* this will display the generated dynamic SQL */

/* uncomment this to execute the dynamic SQL
EXEC sp_executesql @cmd;
*/


Recursive multiplication How can I get ASSY_EXT_QTY to be the quantity of all the path?
IE:
LVL   QTY   ASSY_EXT_QTY
1     1     1
2     3     3 1*3
3     2     6 1*3*2
...

I thought making a path but for the quantities and then somehow multiply them but this seems far reached. This is the code:
select
  LEVEL,
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
  CASE
    LEVEL
    WHEN 1 THEN COMPONENT_QTY
    ELSE (PRIOR COMPONENT_QTY) * COMPONENT_QTY <--- RIGHT NOW I'M DOING THIS BUT NOT ENOUGH AS WORKS IN THE CURRENT SUB-LEVEL ONLY.
  END ASSY_EXT_QTY
from
  (
    select
...
    FROM
...
    WHERE
...
  ) START WITH FATHER_ITEM_NUMBER = :ASSY CONNECT BY PRIOR CHILD_ITEM_ID = STR_ITEM_ID
) bom

I'd be just SQL, no functions or something like that as I'm not allowed by the environment.
Thanks!

ODCI
Personally, I'd probably write my own aggregate function so that the SQL looks like
select
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
  my_mult_agg( ) over (order by level) ASSY_EXT_QTY
from ( .. ) bom

(There still might be a pure SQL way)
Example:
Modified from the example in the documentation found here
create or replace
type my_mult_agg_impl as object
(
  mult_buffer NUMBER, -- highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl)
    return number,
  member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl,
    value IN number) return number,
  member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl,
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN my_mult_agg_impl,
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl,
    ctx2 IN my_mult_agg_impl) return number
);
/

create or replace type body my_mult_agg_impl is
static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl)
return number is
begin
  sctx := my_mult_agg_impl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
  mult_buffer := mult_buffer * value;

  return ODCIConst.Success;
end;
member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
  mult_buffer := mult_buffer / value;

  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN my_mult_agg_impl,
    returnValue OUT number, flags IN number) return number is
begin
  returnValue := mult_buffer;
  return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, ctx2 IN my_mult_agg_impl) return number is
begin
  self.mult_buffer := self.mult_buffer * ctx2.mult_buffer;

  return ODCIConst.Success;
end;
end;
/

CREATE or replace FUNCTION my_mult_agg (input NUMBER) RETURN NUMBER
AGGREGATE USING my_mult_agg_impl;
/

select level,
 my_mult_agg( level ) over (order by level) as MM
from dual
connect by level <= 10
order by level;

result:


PostgreSQL: access right problems I have a "production" user and database on which I want to give right access on all the users belonging to a production group.

Unfortunately, I don't succeed, keeping the access denied error.

Can you have a look at the example script below and tell me what I missed (production user/database is toto and production group is toto_group)?

Some comments:

The use case is: The data loading is already in production. Developpers (titi, etc.) now only have to read the data, and we want them to read it from the production database in order to avoid having to duplicate everything.
I've initially tried on Linux, but I did the test script on windows. Same results.
I'm using postgresql 9.5
I have also tried to grant the access to the tables instead of the schema. Same results.

Thanks a lot.

\c postgres postgres

DROP DATABASE IF EXISTS toto;
DROP TABLESPACE IF EXISTS toto_ts;
DROP ROLE IF EXISTS toto;
DROP DATABASE IF EXISTS titi;
DROP TABLESPACE IF EXISTS titi_ts;
DROP ROLE IF EXISTS titi;
DROP ROLE IF EXISTS toto_group;

CREATE ROLE toto_group NOLOGIN;

CREATE ROLE toto WITH PASSWORD 'toto' LOGIN;
CREATE TABLESPACE toto_ts OWNER toto LOCATION 'd:/pg_ts/toto';
CREATE DATABASE toto TABLESPACE=toto_ts TEMPLATE=template0 OWNER=toto;
GRANT toto_group TO toto;

CREATE ROLE titi WITH PASSWORD 'titi' LOGIN;
CREATE TABLESPACE titi_ts OWNER titi LOCATION 'd:/pg_ts/titi';
CREATE DATABASE titi TABLESPACE=titi_ts TEMPLATE=template0 OWNER=titi;
GRANT toto_group TO titi;

COMMIT;

\c toto

DROP SCHEMA public;
CREATE SCHEMA s_test;
SET SCHEMA 's_test';
GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group;
CREATE TABLE t_test (id INTEGER);

COMMIT;

\c titi

DROP SCHEMA public;
CREATE SCHEMA s_test;
SET SCHEMA 's_test';
GRANT SELECT ON ALL TABLES IN SCHEMA s_test TO toto_group;
CREATE TABLE t_test (id INTEGER);

COMMIT;

You should also grant access to the database and SCHEMA itself:

GRANT CONNECT ON DATABASE toto TO toto_group;
GRANT USAGE ON SCHEMA s_test TO toto_group;


Why is this join cardinality estimate so large? I am experiencing what I think is an impossibly high cardinality estimate for the following query:

SELECT dm.PRIMARY_ID
FROM
(
    SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
    LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
    LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst ON dm.PRIMARY_ID = lst.JOIN_ID;

The estimated plan is here. I am working on a statistics copy of the tables so I cannot include an actual plan. However, I don't think that it's very relevant for this problem.

SQL Server estimates that 481577 rows will be returned from the "dm" derived table. It then estimates that 4528030000 rows will be returned after the join to X_LAST_TABLE is performed, but JOIN_ID is the primary key of X_LAST_TIME. I would expect a join cardinality estimate between 0 and 481577 rows. Instead, the row estimate appears to be 10% of the number of rows I would get when cross joining the outer and the inner tables. The math for this works out with rounding: 481577*94025*0.1 = 45280277425 which is rounded to 4528030000.

I am primarily looking for a root cause for this behavior. I am interested in simple workarounds as well, but please do not suggest changing the data model or using temp tables. This query is a simplification of logic within a view. I know that doing COALESCE on a few columns and joining on them isn't a good practice. Part of the goal of this question is to figure out if I need to recommend that the data model be redesigned.

I am testing on Microsoft SQL Server 2014 with the legacy cardinality estimator enabled. TF 4199 and others are on. I can give a full list of trace flags if that ends up being relevant.

Here is the most relevant table definition:

CREATE TABLE X_LAST_TABLE (
JOIN_ID NUMERIC(18, 0) NOT NULL
    CONSTRAINT PK_X_LAST_TABLE PRIMARY KEY CLUSTERED (JOIN_ID ASC)
);

I also scripted out all of the table creation scripts along with their statistics if anyone wants to reproduce the issue on one of their servers.

To add a few of my observations, using TF 2312 fixes the estimate, but that is not an option for me. TF 2301 does not fix the estimate. Removing one of the tables fixes the estimate. Bizarrely, changing the join order of X_DETAIL_LINK also fixes the estimate. By changing the join order I mean rewriting the query and not forcing the join order with a hint. Here is an estimated query plan when just changing the order of the joins.

I know that doing COALESCE on a few columns and joining on them isn't a good practice.

Generating good cardinality and distribution estimates is hard enough when the schema is 3NF+ (with keys and constraints) and the query is relational and primarily SPJG (selection-projection-join-group by). The CE model is built on those principles. The more unusual or non-relational features there are in a query, the closer one gets to the boundaries of what the cardinality and selectivity framework can handle. Go too far and CE will give up and guess.
Most of the MCVE example is simple SPJ (no G), albeit with predominantly outer equijoins (modelled as inner join plus anti-semijoin) rather than the simpler inner equijoin (or semijoin). All the relations have keys, though no foreign keys or other constraints. All but one of the joins are one-to-many, which is good.
The exception is the many-to-many outer join between X_DETAIL_1 and X_DETAIL_LINK. The only function of this join in the MCVE is to potentially duplicate rows in X_DETAIL_1. This is an unusual sort of a thing.
Simple equality predicates (selections) and scalar operators are also better. For example attribute compare-equal attribute/constant normally works well in the model. It is relatively "easy" to modify histograms and frequency statistics to reflect the application of such predicates.
COALESCE is built on CASE, which is in turn implemented internally as IIF (and this was true well before IIF appeared in the Transact-SQL language). The CE models IIF as a UNION with two mutually-exclusive children, each consisting of a project on a selection on the input relation. Each of the listed components has model support, so combining them is relatively straightforward. Even so, the more one layers abstractions, the less accurate the end result tends to be - a reason why larger execution plans tend to be less stable and reliable.
ISNULL, on the other hand, is intrinsic to the engine. It is not built up using any more basic components. Applying the effect of ISNULL to a histogram, for example, is as simple as replacing the step for NULL values (and compacting as necessary). It is still relatively opaque, as scalar operators go, and so best avoided where possible. Nevertheless, it is generally speaking more optimizer-friendly (less optimizer-unfriendly) than a CASE-based alternate.
The CE (70 and 120+) is very complex, even by SQL Server standards. It is not a case of applying simple logic (with a secret formula) to each operator. The CE knows about keys and functional dependencies; it knows how to estimate using frequencies, multivariate statistics, and histograms; and there is an absolute ton of special cases, refinements, checks & balances, and supporting structures. It often estimates e.g. joins in multiple ways (frequency, histogram) and decides on an outcome or adjustment based on the differences between the two.
One last basic thing to cover: Initial cardinality estimation runs for every operation in the query tree, from the bottom up. Selectivity and cardinality is derived for leaf operators first (base relations). Modified histograms and density/frequency information is derived for parent operators. The further up the tree we go, the lower the quality of estimates tends to be as errors tend to accumulate.
This single initial comprehensive estimation provides a starting point, and occurs well before any consideration is given to a final execution plan (it happens a way before even the trivial plan compilation stage). The query tree at this point tends to reflect the written form of the query fairly closely (though with subqueries removed, and simplifications applied etc.)
Immediately after initial estimation, SQL Server performs heuristic join reordering, which loosely speaking tries to reorder the tree to place smaller tables and high-selectivity joins first. It also tries to position inner joins before outer joins and cross products. Its capabilities are not extensive; its efforts are not exhaustive; and it does not consider physical costs (since they do not exist yet - only statistical information and metadata information are present). Heuristic reorder is most successful on simple inner equijoin trees. It exists to provide a "better" starting point for cost-based optimization.

Why is this join cardinality estimate so large?

The MCVE has an "unusual" mostly-redundant many-to-many join, and an equi join with COALESCE in the predicate. The operator tree also has an inner join last, which heuristic join reorder was unable to move up the tree to a more preferred position. Leaving aside all the scalars and projections, the join tree is:
LogOp_Join [ Card=4.52803e+009 ]
    LogOp_LeftOuterJoin [ Card=481577 ]
        LogOp_LeftOuterJoin [ Card=481577 ]
            LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_Get TBL: X_DRIVING_TABLE(alias TBL: dt) [ Card=481577 ]
                LogOp_Get TBL: X_DETAIL_1(alias TBL: d1) [ Card=70 ]
                LogOp_Get TBL: X_DETAIL_LINK(alias TBL: lnk) [ Card=47 ]
            LogOp_Get TBL: X_DETAIL_2(alias TBL: d2) X_DETAIL_2 [ Card=119 ]
        LogOp_Get TBL: X_DETAIL_3(alias TBL: d3) X_DETAIL_3 [ Card=281 ]
    LogOp_Get TBL: X_LAST_TABLE(alias TBL: lst) X_LAST_TABLE [ Card=94025 ]

Note the faulty final estimate is already in place. It is printed as Card=4.52803e+009 and stored internally as the double precision floating point value 4.5280277425e+9 (4528027742.5 in decimal).
The derived table in the original query has been removed, and projections normalized. A SQL representation of the tree on which initial cardinality and selectivity estimation was performed is:
SELECT
    PRIMARY_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN X_DETAIL_1 d1
    ON dt.ID = d1.ID
LEFT OUTER JOIN X_DETAIL_LINK lnk
    ON d1.LINK_ID = lnk.LINK_ID
LEFT OUTER JOIN X_DETAIL_2 d2
    ON dt.ID = d2.ID
LEFT OUTER JOIN X_DETAIL_3 d3
    ON dt.ID = d3.ID
INNER JOIN X_LAST_TABLE lst
    ON lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)

(As a aside, the repeated COALESCE is also present in the final plan - once in the final Compute Scalar, and once on the inner side of the inner join).
Notice the final join. This inner join is (by definition) the cartesian product of X_LAST_TABLE and the preceding join output, with a selection (join predicate) of lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) applied. The cardinality of the cartesian product is simply 481577 * 94025 = 45280277425.
To that, we need to determine and apply the selectivity of the predicate. The combination of the opaque expanded COALESCE tree (in terms of UNION and IIF, remember) together with the impact on key information, derived histograms and frequencies of the earlier "unusual" mostly-redundant many-to-many outer join combined means the CE is unable to derive an acceptable estimate in any of the normal ways.
As a result, it enters the Guess Logic. The guess logic is moderately complex, with layers of "educated" guess and "not-so educated" guess algorithms tried. If no better basis for a guess is found, the model uses a guess of last resort, which for an equality comparison is: sqllang!x_Selectivity_Equal = fixed 0.1 selectivity (10% guess):

-- the moment of doom
movsd xmm0,mmword ptr [sqllang!x_Selectivity_Equal

The result is 0.1 selectivity on the cartesian product: 481577 * 94025 * 0.1 = 4528027742.5 (~4.52803e+009) as mentioned before.
Rewrites
When the problematic join is commented out, a better estimate is produced because the fixed-selectivity "guess of last resort" is avoided (key information is retained by the 1-M joins). The quality of the estimate is still low-confidence, because a COALESCE join predicate is not at all CE-friendly. The revised estimate does at least look more reasonable to humans, I suppose.
When the query is written with the outer join to X_DETAIL_LINK placed last, heuristic reorder is able to swap it with the final inner join to X_LAST_TABLE. Putting the inner join right next to the problem outer join gives the limited abilities of early reorder the opportunity to improve the final estimate, since the effects of the mostly-redundant "unusual" many-to-many outer join come after the tricky selectivity estimation for COALESCE. Again, the estimates are little better than fixed guesses, and probably would not stand up to determined cross-examination in a court of law.
Reordering a mixture of inner and outer joins is hard and time-consuming (even stage 2 full optimization only attempts a limited subset of theoretical moves).
The nested ISNULL suggested in Max Vernon's answer manages to avoid the bail-out fixed guess, but the final estimate is an improbable zero rows (uplifted to one row for decency). This might as well be a fixed guess of 1 row, for all the statistical basis the calculation has.

I would expect a join cardinality estimate between 0 and 481577 rows.

This is a reasonable expectation, even if one accepts that cardinality estimation can occur at different times (during cost-based optimization) on physically different, but logically and semantically identical subtrees - with the final plan being a sort of stitched-together best of the best (per memo group). The lack of a plan-wide consistency guarantee does not mean that an individual join should be able to flout respectability, I get that.
On the other hand, if we end up at the guess of last resort, hope is already lost, so why bother. We tried all the tricks we knew, and gave up. If nothing else, the wild final estimate is a great warning sign that not everything went well inside the CE during the compilation and optimization of this query.
When I tried the MCVE, the 120+ CE produced a zero (= 1) row final estimate (like the nested ISNULL) for the original query, which is just as unacceptable to my way of thinking.
The real solution probably involves a design change, to allow simple equi-joins without COALESCE or ISNULL, and ideally foreign keys & other constraints useful for query compilation.


Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30 Problem/Issue : Getting Error :  [Err] 1615 - Prepared statement needs to be re-prepared

I have a Stored Procedure which contains a Prepared Statement and a view

DROP PROCEDURE IF EXISTS `sampleProc`;

DELIMITER ;;
CREATE DEFINER = `root`@`localhost` PROCEDURE `sampleProc`()
BEGIN

SET @select = "SELECT * FROM `viewSample` ";

  PREPARE stmt FROM @select ;
  EXECUTE stmt ;
  DEALLOCATE PREPARE stmt ;

END  ;;
DELIMITER ;

Following call sometimes gives error CALL sampleProc();

Possible work around / solutions

It appears that the best solution is to increase the value of table_definition_cache , but it seems that it is not working , as it had already been increased from 1400 (default) to 16384. table_open_cache is also been increased to 32162

Variable_name               Value
table_definition_cache      16384
table_open_cache            32162
table_open_cache_instances  4

This seems to be an on-going issue
Views are messy to handle with Dynamic SQL
Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it.
Another bug report, Prepared-Statement fails when MySQL-Server under load, states that error 1615 is not a bug when the underlying tables are busy. (Really ?)
While there is some merit to increasing the table cache size (See MySql error when working with a mysql view), it does not always work (See General error: 1615 Prepared statement needs to be re-prepared (selecting mysql view))
ALTERNATIVES
Over a year ago, someone mentioned this in the MySQL Forum (MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared”).
Someone came up with the simple idea of not using the view in the prepared statement but using the SQL of view in a subquery instead. Another idea would be to create the SQL used by the view and execute it in your client code.
These would seems to be better workarounds that just bumping up the table cache size.


How to update two records with two different values with two 'where' conditions at once? In T-SQL, how to have this:

DECLARE @amount decimal(18,2) = 1000
UPDATE account
    SET balance = balance +  @amount WHERE accountID= 1;
UPDATE account
    SET balance = balance -  @amount WHERE accountID= 2;

in one update query to make sure eg. no one is changing account currency between two updates? When a row with ID = 1 is locked I want the row with ID = 2 also to be locked. Isolation level is read commited. Something like:

   DECLARE @amount decimal(18,2) = 1000
   UPDATE account
        SET balance = balance +  @amount WHERE accountID= 1;
    AND
        SET balance = balance -  @amount WHERE accountID= 2;

UPDATE account
    SET balance = balance +  CASE WHEN accountID = 1 THEN amount
                                               WHEN accountID = 2 THEN amount * -1
                                               ELSE 0
                                               END
WHERE accountID IN (1,2)

The * is the multiply operator, that means amount * -1 = - amount - dat-nguyen


Streaming Replication failing from standby with connection issue I'm setting up streaming replication between a primary and standby server.

On the standby server, when I start up the instance I see that replication isn't taking place. Looking at the log, I see the following:

2017-02-02 20:37:03 UTC [45618-1] LOG:  database system was shut dow
n in recovery at 2017-02-02 20:36:50 UTC
2017-02-02 20:37:03 UTC [45618-2] LOG:  entering standby mode
cp: cannot stat '/etc/replarchive/000000010000000000000005': No such file or directory
2017-02-02 20:37:03 UTC [45618-3] LOG:  invalid record length at 0/5000098
2017-02-02 20:37:03 UTC [45623-1] FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL on
        FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL off

That's a pretty obvious error message, could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.5", user "replication", SSL on.

But on the primary server, pg_hba.conf indeed has the following entry:

host    all     replication 10.0.0.5/32     md5

I can't seem to wrap my head around why the standby server is failing a connection with that in place.

So I tried to connect from the standby server to the primary server via psql:

# this is on the standby server (10.0.0.5) connecting to the primary server (10.0.0.4)
psql myrepl replication -h10.0.0.4

Putting in my password when psql prompts me to, I successfully connect to the myrepl database on the primary server.

What am I missing here? Why is recovery on the standby not able to connect to the primary server when it appears obvious that the connection is indeed possible?

Thank you in advance!

On the primary server, in pg_hba.conf replace your entry with the below:

host    all  all  10.0.0.5/32  trust

Now reload the conf (for example, doing pg_ctl reload or doing a SELECT pg_reload_conf(); in the DB itself).

On the standby, check recovery.conf: it should contain the following:

standby_mode='on'
primary_conninfo=' host=10.0.0.5'
restore_command = 'cp /var/lib/postgresql/9.1/main/archive/%f %p'
recovery_target_timeline = 'latest'

Now restart the slave machine.

And if you are still unable to connect, check replication user attributes -  replication should be set there.  Use \du to check this when using psql.


Table with identity column in merge replication I have a table in SQL Server 2005 and I have an IDENTITY(1,1) property set for an ID column.  Records are inserted in a table at a local server as well as a remote server.  I am using Merge Replication to match the data from local server and remote server.

What happens if I insert the same ID at the local server and the remote server?

Please suggest what am I doing wrong here.

When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur.

With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology.

With manual, you will need to manually assign "islands" of ranges for nodes using DBCC CHECKIDENT.

Have a look at Replicate Identity Columns.


Access control on Azure SQL Data warehouse Access Control setup on Tables inside dbo schema.

We are Denying select permission on some tables inside dbo schema as these tables have some critical data.

Issue: Even when Select query fails on the object. A user having create view grants can create a view on the same object.

Sample queries:

Select * from dbo.TABLE1 -- Fails as user does not have access.

Create view USER.VIEW1 as (Select * from dbo.TABLE1) -- this goes through.

How can I restrict this from happening?

I haven't tested this on Azure, but on my local SQL Server 2016, I cannot reproduce what you're seeing.  Perhaps the target user has more rights than you think?  Do they have membership in a role that is giving them access?

Anyway, the testbed I used to test this is:

CREATE TABLE dbo.SomeTable
(
    SomeTable_id int NOT NULL
        CONSTRAINT SomeTable_pk
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON [PRIMARY];

CREATE USER UserA WITHOUT LOGIN;
DENY SELECT ON dbo.SomeTable TO UserA;
GRANT CREATE VIEW TO UserA;
GRANT CREATE SCHEMA TO UserA;

EXECUTE AS USER = N'UserA'; --anything past this point is executed using UserA's permissions

--fails with SELECT permission denied
SELECT *
FROM dbo.SomeTable;
GO

--this works due to the GRANT CREATE SCHEMA statement above
CREATE SCHEMA UserA;
GO

--this works due to the GRANT CREATE VIEW statement above
CREATE VIEW UserA.SomeTableView
AS
SELECT *
FROM dbo.SomeTable;
GO

--this fails since the user who created the view doesn't have rights to the underlying table.
SELECT *
FROM UserA.SomeTableView;
DROP VIEW UserA.SomeTableView;

--revert back to "me" instead of UserA
REVERT

UserA is able to create the view, but they cannot actually select from it.  The error returned is:

  Msg 229, Level 14, State 5, Line 27
  The SELECT permission was denied on the object 'SomeTable', database 'MaxTest', schema 'dbo'.


How to compare all rows in a table and extract data using triggers I have two tables, one table stores the GPS data of various users and another table stores the idle time of the users based upon the GPS latitude and longitude values and timestamp difference.

Table T1 contents:

id  uid  latitude   longitude    received_time          imno
435 25  13.041868   80.198868   2014-08-07 15:53:00     352944054874253
436 25  13.041862   80.198868   2014-08-07 15:54:00     352944054874253
437 25  13.041869   80.198868   2014-08-07 15:55:00     352944054874253
438 25  13.041887   80.198871   2014-08-07 15:55:38     352944054874253
439 25  13.041887   80.198871   2014-08-07 15:55:42     352944054874253
440 25  13.041887   80.198871   2014-08-07 15:55:47     352944054874253
441 25  13.041887   80.198871   2014-08-07 15:55:52     352944054874253
442 25  13.041887   80.198871   2014-08-07 15:56:00     352944054874253

I have to compare rows having the same latitude and longitude values.

If previous row's latitude and longitude is the same as the next row's latitude and longitude, then calculate timestamp difference until the next row's latitude and longitude values aren't equal.

For example, from ID 438 to 442 the latitude and longitude values are the same so I have to calculate:

timediff = 442 received - 438 received time (in minutes)

and store it in table T2 like:

id  uid     latitude     longitude      idle_time               imno
1    25      13.041887   80.198871    2014-08-07 00:01:38    352944054874253

start_time                        end_time
2014-08-07 15:55:38         2014-08-07 15:56:00

How can I solve this?

Supposing id is primary_key and auto increment column for both t1 and t2, I think this is what you need:

CREATE TRIGGER trig1
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
  IF @latitude = NEW.latitude AND @longitude = NEW.longitude THEN
     SET @end_time = NEW.received_time;
     SET @iddle_time = TIMEDIFF(NEW.received_time, @start_time);
     UPDATE t2 SET iddle_time = @iddle_time, end_time = @end_time
               WHERE id = @last_id_t2;
  ELSE

     INSERT INTO t2 VALUES (NULL, NEW.uid, NEW.latitude, NEW.longitude,
                            NULL, NEW.imno, NEW.received_time, NEW.received_time);
     SET @last_id_t2 = LAST_INSERT_ID();
     SET @latitude = NEW.latitude; SET @longitude = NEW.longitude;
     SET @iddle_time = NULL;
     SET @start_time = NEW.received_time; SET @end_time = NEW.received_time;
  END IF;
END;

You can see the whole scheme here : http://sqlfiddle.com/#!2/a70ac/1


AlwaysOn Async read takes more time when compared to that of Sync secondary AlwaysOn Async read takes more time when compared to that of Sync secondary (by a small fraction). Is this expected behaviour? I was under the impression that ASYNC must be faster than the SYNC.

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one.

To list a few:

Replica is under too much load
Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ 2.4 GHz
Stats and/or indexes were updated and haven't yet been fully redone on the async
Geographical network latency


Definition of approach to store temporarily unknown data What structure would be more reasonable to store temporarily unknown data? Initially we do not know some aspects of the data we are working with, but such aspects will be filled in at some point.
Problem description
I am developing a database for product refurbishment, in which I have to store data such as customer, model, serial number, etc. (this part is working well so far).
Said database has to handle shipment of products (incoming - outgoing). There is no problem for the outgoing part because we already have the data, the problem we are facing is with the incoming aspect, since some customers send products as a bulk or lot, and we only receive a summary of the shipment (i.e., model and quantity), other customers do send the complete information of the products.
Alternatives I have considered
So far, I have a Shipment table, and a linking table that stores the relationship between Products and Shipments, the options that I have come up with are the following:

Option A. Insert products in the Product table with NULL marks (except Model & Customer) and relate them to a Shipment; later, when we receive the products we fill in the columns that were marked as NULL. This option will allow the products to be treated as the rest and will add minimal changes to the system.

Option B. Have a separate table that stores the ProductSummaries to be received, and then when the shipment is received they are properly stored in the Product table. This option will add more tables and complexity, but I will not have to deal with NULLable columns.

Questions

Which of my two described approaches will be more suitable?

Is there any other design or structure that I can implement or follow?

Option A is similar to what BI developers have dealt with in terms of incomplete data. In our case it's called a late arriving dimension and accommodates the fact that we often times get incomplete data, that is nevertheless important for immediate storage and analysis.

You can implement some form of the following: Instead of leaving NULLs create a default record in related tables so you can make inserts immediately with as much information as you have and later execute minimal updates without breaking your referential integrity design. Product would have a default or unknown row, and this would be used whenever you have no idea what product is coming in. Shipment maintains an FK to Products and once you know the product you can update Shipment with a new FK to Products. You may wish to add date columns to Shipment so you have insight into when these updates occur.

If you have incomplete product information, but enough to tell you've never seen it before (e.g. UPC but no color) do not use the default/unknown row. Insert a new product as you've described using defaults in the Product table, but generate a new PK. Later when your information arrives you should only need to update the product table - since that record has already been created and referenced elsewhere, you can perform minimal updates when your information finally arrives.


SQL Server 2008 R2 - Weekly / Monthly Statistics I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server.  For instance, how many times they were connected, which table was most popular or when was the most traffic, etc.

I cannot find anything about this weekly / monthly user statistic for SQL Server 2008 R2.

Do you know how to do that?

SQL Server doesn't track things like table access and concurrency.

You could do this kind of thing yourself if you do some or all of these things:

audit logins
force all data access via stored procedures (and perform custom logging)
add triggers to capture insert/update/delete activity if you can't force data access through stored procedures
capture concurrency for activity by taking snapshots of DMVs (e.g. sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests)
server-side trace / extended events for anything not captured above


Normalization/normal forms - May a field describe an other field? Like this:

CREATE TABLE persons(
    id serial8 NOT NULL PRIMARY KEY,
    name varchar,
    -- A lot of other fields
    date_of_birth timestamp with time zone,
    date_of_birth_precision varchar(16),
    CHECK (date_of_birth_precision IN ('Years','Months','Days','Hours','Minutes'))
);

date_of_birth_precision describes the precision of date_of_birth.

I wonder if it violates this rule (because I don't fully understand the rule):

Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every superkey of R.

I disagree with the other answers on this one and do not believe this violates 3NF.  This is because date_of_birth_precision does not imply date_of_birth nor does date_of_birth imply date_of_birth_precision.
It's important to note the definition of a functional dependency:

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection  is a function, i.e. Y is a function of X.1 In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set.

So let's ask ourselves, for a given value of date_of_birth_precision, does that imply a date_of_birth?  Certainly not.  For a given value of date_of_birth, does that imply a date_of_birth_precision?  It doesn't, otherwise you wouldn't even be asking this question.  Since neither implies the other, there is no functional dependency between the two.  Therefore, this does not break 3NF because 3NF only qualifies the rules for functional dependencies; it says nothing about attributes which are not functionally dependent on each other.
I find the alternate definition of 3NF much easier to comprehend, personally:

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

X contains A (that is, X → A is trivial functional dependency), or
X is a superkey, or
Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key)


Can I minimize maintenance of duplicated Windows + SQL Auth logins? In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP.  As a result, each physical user has two logical logins for each instance and two logical users for each database on each instance.

Is there any way to link two logins, so I can (e.g.) grant privs to both simultaneously?

This is a small thing, but seeing repetition like this triggers my "there's got to be a better way" sense:

CREATE LOGIN Bob
CREATE LOGIN Domain\Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Domain\Bob
USE Db;  CREATE USER Bob FOR LOGIN Bob
USE Db;  CREATE USER Domain\Bob FOR LOGIN Domain\Bob

Am I overlooking a feature that would reduce this duplication?

We'll eventually replace domain logins with AD groups, which will help.

I would suggest moving all clients to the domain and removing the SQL logins as the AD route is much more secure. If this is not an option then I would probably remove the AD logins and have RDP users use SQL logins as having two logins per user doesn't make much sense.

If for some reason you decide you have to go the two login route I would write a stored procedure to replicate the settings from AD users to your SQL logins. This could be triggered with a DDL trigger. This way you would only have to update one set of users.

I would also be careful in using AD groups if you are not the only person responsible for the administration of the domain. It's not clear from the database who has the permissions specified for the group as users be dropped in and out of the AD group without your knowledge.


How to get last and first occurrence of items into separated columns? I do have the fallowing table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1

I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9
Ray     5         NULL
Charles 1         NULL

How can I write a query to get the last and first occurrence of values grouped by name and ordered by id, like this?

Test:

SELECT
  t_min_max.name,
  t_min.value AS value_1st,
  CASE WHEN min_id <> max_id THEN t_min.value END AS value_last
FROM (
  SELECT name, min(id) AS min_id, max(id) AS max_id
  FROM table
  GROUP BY name
) AS t_min_max
JOIN table AS t_min
  ON t_min_max.min_id = t_min.id
JOIN table AS t_max
  ON t_min_max.max_id = t_max.id;

With complex queries, you should always work your way from the inside out,

SELECT name, min(id) AS min_id, max(id) AS max_id
FROM table
GROUP BY name;

You can see here that we're grabbing the range of IDs for each user,

id name     value
1  Daniel   3      <- this is a min(id)
2  Daniel   7
3  Daniel   2      <- this is a max(id)
4  Carol    9      <- this is a min(id)
5  Carol    4
6  Carol    9      <- this is a max(id)
7  Ray      5      <- this is both min(id) and max(id)
8  Charles  1      <- this is both min(id) and max(id)

After we do that we have a table like,

         min(id) | max(id)
Daniel | 1       | 3
Carol  | 4       | 6
Ray    | 7       | 7
Charles| 8       | 8

We then self-join that back to the original table in order to get the values.


Guaranteeing a FK relationship through multiple tables I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is also associated with a 'recordLabel'. When an 'album' is created, it is associated with an 'artist' and a 'manager', but both the artist and the manager need to be associated with the same recordLabel. How can I guarantee that relationship with the current table setup, or do I need to redesign the tables?

The relationship, as presented in the question, could be enforced with a simple trigger.  This example relies on SIGNAL from MySQL 5.5 and later, to throw an exception and prevent the insertion of invalid data.

DELIMITER $$
DROP TRIGGER IF EXISTS albums_bi $$
CREATE TRIGGER albums_bi BEFORE INSERT ON albums FOR EACH ROW
BEGIN

  IF (SELECT record_label_id FROM artist a WHERE a.id = NEW.artist_id) !=
     (SELECT record_label_id FROM manager m WHERE m.id = NEW.manager_id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'artist and manager must be from the same label';
  END IF;

END $$
DELIMITER ;

That's the simple version.  You'd also need a BEFORE UPDATE trigger to accomplish the same thing, preventing records from being subsequently changed to be invalid.

I've used the word "simple" twice, now, and we all know that word doesn't really belong in discussion of databases...

What about the case where an artist or a manager changes record labels?  Are the albums no longer valid?  Since this is presumably historical data over time, and presumably artists and/or managers may have moved from one label to another, it doesn't seem to make a lot of sense to have quite such a static model.

To model this data accurately, it almost seems like you'd need some kind of temporal tables that reflected the period of time in the real world when the facts stated in the database were true.

For example, an "artist_recording_label_contract" table for the periods of time during which an artist had a contractual relationship with a recording label... with something like artist_recording_label_contract_id (surrogate pk identifying the relationship between an artist and a recording label for one specific window of time), artist_id, record_label_id, start_date, end_date ... then another similar table with the same information for managers... and each album would reference the respective record in each of these time window tables when the artist and manager worked for the same label.

Then your trigger would have to validate that the artist and the manager's contract windows were not only with the same recording label, but also during an overlapping window of time... which might also need to overlap with a date in the album table if you're tracking when albums were produced.

And you'd need to potentially prevent data in the contract window tables from being updated in such a way that would invalidate any associated albums or cause windows to improperly overlap with each other, such as a manager working for the same label "from 1971 to 1976" and "from 1975 to 1980."

(So much for simple.)


postmaster.opts in postgresql 9.3? I am using PostgreSQL 9.3, but the book I am reading "Beginning Databases with PostgreSQL From Novice to Professional" is using PostgreSQL 8. It mentions there is a postmaster.opts file which passes the default flags to the postmaster executable. For example, it allows you to set the -D flag (t set location to data directory) and an -i flag (to enable network connections) and so forth. Well, I am running Ubuntu 14.04 and I cannot find this file anywhere. Has it disappeared in PostgreSQL 9 and if so, what has replaced it?

I have recently installed postgresql 9.4 from source

[pol@localhost inst]$ ./bin/psql --version
psql (PostgreSQL) 9.4.0
[pol@localhost inst]$

My install directory structure is as follows

/home/pol/Downloads/software/postgres/inst
[pol@localhost inst]$ ll
total 128
drwxrwxr-x.  2 pol pol  4096 Jan 29 05:42 bin
drwx------. 18 pol pol  4096 May  1 18:03 data
drwxrwxr-x.  6 pol pol  4096 Jan 29 05:42 include
-rw-r--r--.  1 pol pol 75392 Jan 29 05:44 INSTALL
-rw-rw-r--.  1 pol pol   158 Jan 29 05:45 install.txt
drwxrwxr-x.  4 pol pol  4096 Jan 29 05:42 lib
-rw-------.  1 pol pol 14718 May  2 17:47 logfile
-rw-rw-r--.  1 pol pol  2981 Jan 30 07:49 logfile.txt
-rw-rw-r--.  1 pol pol  5996 Apr  2 22:32 mytestdb.sql
drwxrwxr-x.  6 pol pol  4096 Jan 29 05:42 share

Then I searched for files name xxxx.opts

[pol@localhost inst]$ find . -name *.opts
./data/postmaster.opts

So, it exists in 9.4 - it appears from Googling that it generally lives in the data directory. I have never edited it manually.

[pol@localhost inst]$ more ./data/postmaster.opts
/home/pol/Downloads/software/postgres/inst/bin/postgres "-D" "./data"
[pol@localhost inst]$

So, it appears that it still has a function in the system.


Breaking out of a recursive query in Postgres 11 I'm having some trouble getting the correct data out of a tree structure, where I only want the first nodes in a branch matching criteria to be the result of the query.

I have this table of domains:

create table domains(
    id bigint,
    domain_name varchar,
    parent_id bigint,
    valid boolean
)

insert into domains values
    (1, 'example.com', null, false),
    (2, 'a.example.com', 1, true),
    (3, 'b.example.com', 1, false),
    (4, 'c.b.example.com', 3, true),
    (5, 'd.a.example.com', 2, true)

This gives this tree, with the valid domains in green:

Based on this, I want a.example.com and c.b.example.com to be returned as valid domains. Had the top level domain example.com been valid, only that should be returned. ("valid" is just a flag determined elsewhere.)

I have a recursive query that fetches the whole domain tree:

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT * FROM valid_domains

...but then my stumbling block is figuring out how to filter on valid domains and break that branch when I hit one that's valid, without returning any valid domains below it.

I've looked at window functions, but haven't been able to mesh that with the recursive query thus far. Am I taking the wrong approach by making this recursive in the first place?

WITH RECURSIVE cte AS (
  SELECT id, domain_name, valid
  FROM domains
  WHERE parent_id IS NULL
UNION ALL
  SELECT domains.id, domains.domain_name, domains.valid
  FROM domains
  JOIN cte ON domains.parent_id = cte.id
  WHERE NOT cte.valid -- stop recursion when valid node reached
)

SELECT id, domain_name
FROM cte
WHERE valid

fiddle


Create log file only for my executed queries I have been using the following to set up the log file:

log_statement='all',

log_collector='on',

log_destination='on',

log_filename='filename'

After that, for testing I just executed a query then I have seen the log file in tools>serverstatus. Unfortunately it has a lot of anonymous queries including the query which I ran.

How can I filter out the queries which were executed along with my query (anonymous queries)? Is there any possibility to log the affected table information like:

which table got affected?
at what time was the query executed?
what fields got affected?

And how can I get these details using queries?

The log_statement in postgresql.conf applies to all users and all databases, but if it's not what you want, you may provide per-database and per-user values, with:

ALTER USER someuser SET log_statement='all';

and/or

ALTER DATABASE dbname SET log_statement='all';

and leave log_statement to 'none' in the global configuration file postgresql.conf.

Also when you're superuser, you can enable logging anytime just for your session with:

SET log_statement='all';

As to what is logged, it can be configured with log_line_prefix but it doesn't go as far as logging tables and columns names. To log which columns are affected by UPDATEs you'd need to do that in triggers in every table, as in tablelog


MS Access: Issue with Autonumbers in the export I have a question that might be trivial to solve, but I have spent some time on it and didn't manage to solve it.

I am working on a database in MS Access, in which I keep track of the specifications of mechanical parts such as valves, pipes, and instruments. These components are grouped in tables, each one containing a class of components.
The single components can be described by parameters such as diameter, media used, and other fields. These values are fixed (e.g. the media will always be water, air, hot gas etc.), so they are stored in auxiliary tables, that are called once the user is filling the form using a combo box.

So far, everything good: the user sees the media in the combo box and I can select the same combo box from the view in the database table.
Once I export the file in Excel, I have a problem: the file is not reporting the "Description" field in the Auxiliary table - such as "water" - but its Autonumber.
How can I solve the problem? I would like to have an exported file that is showing the information displayed in the auxiliary table, and not the AutoNumber connected to it.
I have tried to solve the issue in this way:

The existing Relationship was between the Autonumber, as Primary key, and the field in the table, as foreign key. I deleted the relationship, set the "description" field as Primary key and made a new Relationship between the description in the auxiliary table and the foreign key.
The problem in this case is that the new fields are stored correctly, but the old fields remain as numbers. I wouldn't like to reinsert manually all the values, if it is possible to avoid it.

How can I convert all the information that is currently stored as number in its correspondant value in the auxiliary table?

Thank you very much in advance!

You will need to write a query or report that joins between the tables so you can select the description.

The Image below shows the MS Access query designer. It displays both tables and the join between them. Below that it shows the fields I want to return in my query.

You can also view the SQL Query, in this example it would look like this:

SELECT Inventory.Item_id, Inventory.Item_name, Media.Description
FROM Inventory INNER JOIN Media ON Inventory.Media = Media.Media_id;

The query result look like this:


Table Partitioning Using Inheritance and Triggers I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I have created! I wrote the following SQL code:

DROP TABLE IF EXISTS title_basics_1874to1899;
CREATE TABLE title_basics_1874to1899 (
    CHECK (
        CAST(startyear AS INT) >= 1874
        AND
        CAST(startyear AS INT) <= 1899
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1900to1949;
CREATE TABLE title_basics_1900to1949 (
    CHECK (
        CAST(startyear AS INT) >= 1900
        AND
        CAST(startyear AS INT) <= 1949
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_1950to1999;
CREATE TABLE title_basics_1950to1999 (
    CHECK (
        CAST(startyear AS INT) >= 1950
        AND
        CAST(startyear AS INT) <= 1999
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_2000to2115;
CREATE TABLE title_basics_2000to2115 (
    CHECK (
        CAST(startyear AS INT) >= 2000
        AND
        CAST(startyear AS INT) <= 2115
    )
) INHERITS (title_basics);

DROP TABLE IF EXISTS title_basics_null;
CREATE TABLE title_basics_null (
    CHECK (
        startyear IS NULL
    )
) INHERITS (title_basics);

-- INDEXES
DROP INDEX IF EXISTS title_basics_1874to1899_index;
CREATE INDEX title_basics_1874to1899_index ON
title_basics_1874to1899 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1900to1949_index;
CREATE INDEX title_basics_1900to1949_index ON
title_basics_1900to1949 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_1950to1999_index;
CREATE INDEX title_basics_1950to1999_index ON
title_basics_1950to1999 USING BTREE(startyear);

DROP INDEX IF EXISTS title_basics_2000to2115_index;
CREATE INDEX title_basics_2000to2115_index ON
title_basics_2000to2115 USING BTREE(startyear);

-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION title_basics_partitioner()
RETURNS TRIGGER AS $$
    BEGIN
        IF ( CAST(NEW.startyear AS INT) >= 1874 AND CAST(NEW.startyear AS INT) <= 1899 ) THEN
            INSERT INTO title_basics_1874to1899 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 1900 AND CAST(NEW.startyear AS INT) <= 1949 ) THEN
            INSERT INTO title_basics_1900to1949 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 1950 AND CAST(NEW.startyear AS INT) <= 1999 ) THEN
            INSERT INTO title_basics_1950to1999 VALUES (NEW.*);
        ELSIF ( CAST(NEW.startyear AS INT) >= 2000 AND CAST(NEW.startyear AS INT) <= 2115 ) THEN
            INSERT INTO title_basics_2000to2115 VALUES (NEW.*);
        ELSIF (NEW.startyear IS NULL) THEN
            INSERT INTO title_basics_null VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Out of range year value.';
        END IF;
        RETURN NULL;
    END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS insert_title_basics_trigger on title_basics;
CREATE TRIGGER insert_title_basics_trigger
BEFORE INSERT ON title_basics
FOR EACH ROW EXECUTE PROCEDURE title_basics_partitioner();

Also, my parent table which is called title_basics has the following data types (SQL code below):

CREATE TABLE title_basics (
    t_soundex VARCHAR(7),
    startYear INTEGER,
    isAdult VARCHAR(1),
    genres VARCHAR(150),
    endYear INTEGER,
    primaryTitle VARCHAR(500),
    tconst BIGSERIAL PRIMARY KEY NOT NULL,
    runtimeMinutes VARCHAR(10),
    titleType VARCHAR(15),
    originalTitle VARCHAR(500)
);

My goal is to have the data from the parent table (title_basics) move over to the children table based on the trigger, this must be achieved through a trigger or a rule. My code was based on this documentation: https://www.postgresql.org/docs/12/ddl-partitioning.html

PostgreSQL Version: 11

The trigger will never move rows that are already in the table when you create the trigger, if that is what you mean.

Apart from that, your trigger works just fine.

INSERT INTO title_basics VALUES ('dsadas', 2020, 'T', 'dsadsa', 2021, 'tit', 42, 'min', 'type', 'orig');
INSERT 0 0

No row was inserted, because the trigger function returned NULL.

SELECT * FROM title_basics;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
 dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

Sure, that returns a row, because of the table inheritance.

But the row is actually here:

SELECT * FROM title_basics_2000to2115 ;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
 dsadas    |      2020 | T       | dsadsa |    2021 | tit          |     42 | min            | type      | orig
(1 row)

If you select only from the parent table itself, you get nothing:

SELECT * FROM ONLY title_basics;

 t_soundex | startyear | isadult | genres | endyear | primarytitle | tconst | runtimeminutes | titletype | originaltitle
-----------+-----------+---------+--------+---------+--------------+--------+----------------+-----------+---------------
(0 rows)

Note: You should really use declarative partitioning instead on v11. It is so much simpler and better.


How can I set a constant number as the lowest number in a column? Here is my query?

UPDATE users
SET reputation = reputation - (CASE WHEN id = 29 THEN 2
                                    WHEN id = 30 AND NOT 0 THEN 15
                                    ELSE 0
                               END)
WHERE id IN (30, 29);

Sometimes it throws this error:

  #1690 - BIGINT UNSIGNED value is out of range in '(spy.users.reputation - (case when (spy.users.id = 29) then 2 when ((spy.users.id = 30) and (not(0))) then 15 else 0 end))'

As you see in the error message, reputation column is UNSIGNED, and I need to keep it 1 at least .. something exactly like stackoverflow: if a user has 1 rep and he gets a downvote, his rep will still remain 1. Well how can I fix that update query to prevent failing in such a case?

Noted that using greatest(): doesn't make any different. I mean this won't work either:

UPDATE users
    SET reputation = GREATEST(reputation -
                               (CASE WHEN id = 29 THEN 2 WHEN id = 30 AND NOT 0 THEN 15
                                     ELSE 0 END), 0)
WHERE id IN (30, 29);

SET reputation =
    GREATEST(1,
        CAST(reputation AS SIGNED) - ( ... )
            )


VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals' Good day,
We are currently facing an issue with SSIS deployment from Visual Studio 2019 to SQL Server 2019. Using the Visual Studio "Integration Services Deployment Wizard" to deploy even a blank project, results in the following error: The SELECT permission was denied on the object 'server_principals', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Screenshot:
I use my AD account (Windows Authentication) to deploy from VS 2019. My AD account has db_owner access across the server and databases. We have also attempted to deploy the dtsx file from SSMS 2019 itself which also results in the same error
I am struggling to find any online resources which specifically make reference to the SELECT issue on "Server_principals"
Has anyone perhaps experienced something like this or can anyone please advise where the issue could lie or where to troubleshoot next?
Thanks in advance

Thanks Mustaccio, reading through that did give me an idea of where to look for the solution. Adding the SELECT permission for sys.server_principals on database master for database role "Public", seems to have resolved the issue


in SQL databases does an update rewrite all columns in the row or only those in the SET tuple? i was wondering how a sql update works in two scenarios:

table with 3 columns: a, b, c

say the data value for a has to be changed.

1: update on a row setting all columns a, b, c (where b and c have not changed from what is stored)

2: update on a row setting only the a column value (because b and c have not changed)

is there any performance difference between these? im having a hard time finding information about how they actually work behind the scenes. from the few articles i found my understanding is that in an update the db will:

load the row (all columns) into memory
set the values from the set tuple (in order)
write the updated row back

This is going to vary between databases, but there are things that we can say are generally (but certainly not always) true of common databases such as SQL Server, Postgres, etc.

Drive IO generally works in blocks, usually these days of 4Kbyte or 0.5K, and to write any of a block the drive will write the whole block. Database engines organise their structures into pages for various reasons including to mach the IO subsystem. In MS SQL Server each page is 8K, done databases allow you to choose on a per DB or per server basis. As with the underlying drives writing to a page will result in rewriting the whole page (which may be multiple disk blocks in size) not just the part constituting the updated row(s) or the individual columns in the row(s). So the same amount of IO will be done in your two examples (SET a & SET a, b, c).

In fact more than just that page will be affected: an entry will be made in the transaction log first, and relevant index pages may need to be updated too. The MVCC model used (or supported as an option) by some database systems as extra details to the question, but that detail may be getting a bit deeper than the intended scope of your question.

Another point that does seem relevant is that this IO will happen even if nothing really changes: if you are seeing column a to 42 and it is already 42 the writes will still happen. Further more, any relevant triggers will still fire, constraints will be checked, etc. This is why you sometimes see statements like UPDATE someTable SET aColumn = @aValue WHERE aColumn <> @aValue, to avoid excess activity (if doing this in your code, be sure to be careful to account for NULLs if they might be present, which my example does not for the sake of being concise, also be aware that there are circumstances where you might want triggers to fire even if an update otherwise effectively does nothing).

It is worth reiterating before finishing this answer, that none of this is true for all databases, or all IO more generally, but is only commonly true.


write to SQL server from another server I have 2 Windows 2012 virtual machines set up in Virtualbox - one called "AppServer" where I'll be installing applications, and another called "DatabaseServer" - where the application databases will be stored, using SQL Server 2008.

I'm running some install files on the application server to create a corresponding database on the database server, but it is just hanging, most likely due to permissions.

The VMs are connected using the Internal Network function of Virtualbox, and they can successfully ping each other.

How do i set up permissions on the database server to allow accounts from the application server to create databases, etc. on it?

Unfortunately using application pool identities between two different servers is not possible. App Pool identities are located on their host machines only - SQL server will be unaware of the application pool accounts - so you cannot add them explicitly to logon to the SQL server. They do however run as the Local Machine account over the network - in a domain environment you could give the application server's machine account permissions to the SQL server.

If you have a domain they could be joined to you could run the application pools under a domain identity; granting the domain account access to the SQL server.

This is my preferred method for a dev environment: Enable the "Local Account Token Filter Policy" using group policy. Cross create your accounts on both servers with same username/passwords. Run the application pools under the created accounts; you can grant the same account access on the SQL server; since the account exists on both servers you shouldn't have any issues.


Postgres: flatten Key-Value (K-V) store with jsonb_agg I have two tables: bar and foo (one to many respectively).

The foo table has an internalId and an external reference. The issue here is that the external reference can be duplicated.

I want my query result to have two columns: bar_id and mappings (where bar_id is not duplicated) where the key is the duplicate id and the values list are all the internal ids it is related to.
So far I have been able to aggregate the duplicate external ids to the internal id but not grouped those one more level
i.e
with data:

('100001', 1, '1a'),
('100001', 2, '1a'),
('100001', 3, '1b')

I can get the result:

100001  {"1a": [1, 2]},
100001  {"1b": [3]}

But I want:

100001  {"1a": [1, 2], "1b": [3]}

Fiddle here: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0

You need a two step aggregation:

select bar_id,
       jsonb_object_agg(externalidentifier, internals) as mappings
from (
  SELECT foos.bar_id,
         externalidentifier,
         jsonb_agg(internalIdentifier) as internals
  FROM foos
  GROUP BY bar_id, externalidentifier
) t
group by bar_id;

Your updated fiddle: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0


Does a killed query continue rolling back after closing and exiting out of SSMS? I have a query that took about 6 hours to run, and I decided to cancel it.

Now, the query appears to still be running somewhere in the background because when I try to expand the database and access tables, its stuck on 'expanding.'

Can a query still be closing (i.e. rolling back) long after you exit out of SSMS? Its been almost 24 hours since I closed the query.

I also have temp tables still in the tempdb that I used in the original query, which are still there, even after I exit out of SSMS. Could this indicate the rollback is still in progress?

Closing the connection to SQL Server, whether that is a connection utilizing SSMS or some other application causes the rollback of any currently active transaction(s) created by that connection.  This includes any implicit transactions, and operations such as RESTORE DATABASE, etc.  See this answer for more details about what can cause a transaction to be rolled back.  Be aware that rollback may take an extended amount of time to complete, and will continue whether or not the application that caused the rollback is connected or not.  Rollback will continue even if you restart SQL Server - in fact, restarting SQL Server will likely result in the database being marked as "in recovery" while rollback continues.

If SSMS is actually still running (perhaps the user interface is not visible, but the process is visible in Task Manager), the query might be still running, but I highly doubt that is the case.

Most likely you have some other connection holding a lock on some objects in the database in question, or a rollback is continuuing.

You can check the progress of transactions that are being rolled back by looking at system DMVs, such as:

SELECT des.session_id
    , des.host_name
    , der.command
    , der.percent_complete
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
WHERE der.command = 'ROLLBACK';


Changing data source on Access front end I feel bad asking this, as I am only using an Access front end temporarily, but I have been asked to migrate an access 2007 db to mysql with a php front end.  I have moved the data to mysql and am working on the ~400 forms, reports, etc, but want the users to have access to the system while I am developing the new one.

I have little experience with access and although I have been able to connect to the mysql tables, the forms don't seem to update their data sources.

Is there a way to update the entire access front end to the new tables?  Or am I missing something really simple and straightforward.  I've googled extensively and I've read all the "similar questions" presented on the page where I am typing this and can't an answer which seems to apply.

Thanks for any help or links offered.

You can convert your physical tables in the Access database to the linked tables. The linked table is just this: a link. The actual data is located elsewhere. For this to work you will need to create a connection to the MySQL database supported by Access. Access supports OLEDB and ODBC connections. Download a MySQL OLEDB or ODBC driver, create a DSN (if you chose the ODBC path). Open your Access database and click on the External Data toolbar. Select "Create Link" and then create links to all your data that you have exported to MySQL. The linked tables will be created alongside your existing tables. You can then try deleting the physical tables one-by-one and renaming the matching linked tables, so that the names are the same. You can test the application step-by-steps after each table.

BTW, you could use Access to export data via the driver as well. It would export it together with the indices


Display Monthly Attendance Report in MySql I am doing a School Management System in php using Mysql DB. I am stuck in my Project.
Please anybody suggest what I am doing wrong.

I have two tables in my database; one is to store Students records another one is to store their attendance day wise

Now I want to display a report of all students in particular class for the current month whether they were present or absent. But I am capturing only Absent students details only in attendance table.

I have written sql query to display the result here is:

SELECT tab.class, attend, DATE, ta.rollno, ta.StdNm
FROM tbl_absentees tab, tbl_admission ta
WHERE ta.Cls = class
  AND ta.rollno = tab.rollno
  AND class =22
  AND attend =  'A'
  AND DATE =  '2013-06-07';

The result is:

Class Attend RollNo StudentName

But I want to display in 31 days table manner by taking only Date in attendance table
if attend=A display A for absent Days otherwise Display 'P' for remaining days

How can I do this in mysql? Can anyone suggest/give me an idea to achieve this.

Sorry for misclarification in my question. Actually I want to display an attendance report for a particular month where data comes from two tables:

the first table consists of StudentName, RollNo, Class
the second table consists of Date, Status, RollNo, Class

Now I want to display report like this.

This type of rotation of data from columns to rows is known as a PIVOT. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression to get the result.

My first suggestion would be to determine if you have a calendar table or a table that contains all of the dates that you want to display.  If not, then I would suggest creating one similar to the following:

CREATE TABLE calendar (`Date` datetime) ;

INSERT INTO calendar (`Date`)
VALUES
    ('2013-06-01 00:00:00'),
    ('2013-06-02 00:00:00'),
    ('2013-06-03 00:00:00'),
    ('2013-06-04 00:00:00'),
    ('2013-06-05 00:00:00'),
    ('2013-06-06 00:00:00'),
    ('2013-06-07 00:00:00'),
    ('2013-06-08 00:00:00'),
    ('2013-06-09 00:00:00'),
    ('2013-06-10 00:00:00');

This will make it possible for you to generate a list of all of the dates that you want to display.

Second, you will need to generate the list of each student and each date.  You can do this by using a CROSS JOIN between your tbl_admission and the calendar table:

select c.date, a.studentname, a.rollno, a.class
from calendar c
cross join tbl_admission a;

See Demo.  Once you have this list, then you can use a LEFT JOIN to your existing tbl_absentees table to get the result:

select
  ca.studentname,
  ca.rollno,
  ca.class,
  max(case when ca.date = '2013-06-01' then coalesce(p.status, 'P') end) `2013-06-01`,
  max(case when ca.date = '2013-06-02' then coalesce(p.status, 'P') end) `2013-06-02`,
  max(case when ca.date = '2013-06-03' then coalesce(p.status, 'P') end) `2013-06-03`,
  max(case when ca.date = '2013-06-04' then coalesce(p.status, 'P') end) `2013-06-04`,
  max(case when ca.date = '2013-06-05' then coalesce(p.status, 'P') end) `2013-06-05`,
  max(case when ca.date = '2013-06-06' then coalesce(p.status, 'P') end) `2013-06-06`,
  max(case when ca.date = '2013-06-07' then coalesce(p.status, 'P') end) `2013-06-07`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-08`,
  max(case when ca.date = '2013-06-08' then coalesce(p.status, 'P') end) `2013-06-09`,
  max(case when ca.date = '2013-06-10' then coalesce(p.status, 'P') end) `2013-06-10`
from
(
  select c.date, a.studentname, a.rollno, a.class
  from calendar c
  cross join tbl_admission a
) ca
left join tbl_absentees p
  on ca.rollno = p.rollno
  and ca.date = p.date
group by ca.studentname, ca.rollno, ca.class
order by ca.rollno;

See SQL Fiddle with Demo.  Of course for your request, you most likely want to query the data based on a date range so you will not want to hard-code the values.  If that is the case, then you will need to look at using a prepared statement to generate dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN ca.date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN coalesce(p.status, ''P'') END) AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='2013-06-01'
  and date <= '2013-06-05';

SET @sql
  = CONCAT('SELECT ca.studentname,
              ca.rollno,
              ca.class, ', @sql, '
            from
            (
              select c.date, a.studentname, a.rollno, a.class
              from calendar c
              cross join tbl_admission a
            ) ca
            left join tbl_absentees p
              on ca.rollno = p.rollno
              and ca.date = p.date
            where ca.date>=''2013-06-01''
              and ca.date <= ''2013-06-05''
            group by ca.studentname, ca.rollno, ca.class
            order by ca.rollno');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both of these queries will give a result similar to:

| STUDENTNAME | ROLLNO | CLASS | 2013-06-01 | 2013-06-02 | 2013-06-03 | 2013-06-04 | 2013-06-05 | 2013-06-06 | 2013-06-07 | 2013-06-08 | 2013-06-09 | 2013-06-10 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       Naren |      1 |    22 |          A |          A |          A |          A |          P |          P |          P |          P |          P |          P |
|       Srinu |      2 |    22 |          P |          P |          P |          P |          P |          P |          P |          P |          P |          P |
|        Blah |      3 |    22 |          A |          P |          P |          P |          P |          P |          P |          P |          P |          P |


Log Shipping Fails with CONTINUE_AFTER_ERROR DR plan for primary database in a private hosted cloud environment. The primary is a hosted physical server. The secondary is on another network, same domain. Version is SQL Server 2008R2 (10.50.2550.0) Enterprise. Compatibility=2008.

Log Shipping runs fine for a few restores, then I come back the next day, and I see "A previous RESTORE WITH CONTINUE_AFTER_ERROR operation left the database in a potentially damaged state." And the restore job continues to fail. No further restores go through.

I checked the primary db with DBCC CHECKDB and there are no errors reported. I reconfigured LS twice, and the last time, added a 5-minute delay for the restore job. Same thing happens every time. It runs fine for a few restores, but then fails.

I could really use some help knowing where to look for the issue.

Graeme
PS: This question was previously posted on SQL Server Central, with no responses. At the request of an expert, I am moving it here. Thanks again.

Do a FULL backup of the primary DB (now or whenever), copy that backup file over to the secondary server, then restore that to the secondary DB in standby mode.

Afterwards, see if your LS jobs run successfully on secondary once you get enough fresh logs to apply to it.


Payment methods conceptual and logical model I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have relationships - but then they could not be modelled as entities.
I tried asking this on stackoverflow but got no feedback for couple of days now.

Three possible methods of payment:

a ticket paid on leave,
prepaid card with cash credit,
prepaid card with "time credit",

Price of ticket depends on time:

1-2hrs = $0,
3hrs = $2,
4hrs = $4,
afterwards x hrs = $(x+1), but max. $20 for 24hrs (... easiest to put these to 24 rows, right?).

A ticket (a) may be allowed a 20% discount (ie. for shopping in the mall).
Cash credit card uses same prices as tickets but with 40% discount.
Cash credit card can be reloaded.
Time card is paid once and allows parking while valid.

The problem is I don't know how to put those highlighted relations to the logical db model and whether event to put them there. Is it ok-practice to have isolated tables in the design?

In a transactional system you will want to record the values that applied to the transaction at the time it happened.

It is a mistake to try to use relationships to connect a transaction to a date-depenendent lookup data.  The reason for this is that changes to the date-dependent data that happen after the transaction will restate what should have happened to the transaction.  This will cause confusion and errors.

The way to handle this type of situation is to denormalize the actual value applied to the payment transaction.  Your model doesn't reflect this, but if you had named categories for your discounts and timespan rates, you could keep a relationship from the transaction to the category name while still denormalizing the actual (time sensitive) rate/percent.

EDIT: What should the logical model look like?

In your logical model, the issue is that you need multiple discount (or price) rates because they change over time - or just because there may be multiple rules, for example one price for regular customers and one for special customers, etc.

What is missing is the notion of a rate table.  This is like a header which then has rate details attached to it.  What you have in your model now is really just the rate detail.  You need to insert the rate table header in between the transaction and the rate detail.  In your logical model, the relationship is between the transaction and the rate table header.  This is OK because the rate table is more stable than the details which change over time.

In your physical model you still need to denormalize make a point in time snapshot of the actual rate used into your transaction because that is the only reliable way to make sure that editing the rates won't restate history.


How to pass variable to PL/pgSQL code from the command line? I am running a psql script from the command line with variables something like:

psql ...... -v now_utc=$NOW_UTC

Then I want to use this in my script like:

$$
DECLARE
   _now_date timestamp := :now_utc;
BEGIN
  -- do something
END
$$

But I got an error like:

syntax error at or near ':'

The script is fine once I change from :now_utc to now() and it works like a charm.

The question is how I can pass a variable from the command line to PL/pgSQL code?

The manual for psql:

  Variable interpolation will not be performed within quoted SQL literals and identifiers.

The body of a DO statement (or function) is a quoted literal. Dollar-quoted in this case, but all the same:

$$
DECLARE
 _now_date timestamp := :now_utc;
BEGIN
  --
END
$$

Insert text with single quotes in PostgreSQL

To enable SQL interpolation, do it outside the string and then concatenate - which gets tedious quickly ...

One way to make it simpler is to let Postgres do the string processing and then execute the result using \gexec. Call from psql:

SELECT format($$
DO
$do$
DECLARE
   _now_date timestamp := %L;
BEGIN
  RAISE NOTICE '%%', _now_date;
END
$do$;
$$, :'now_utc')\gexec

NOTICE:  2019-06-14 00:41:53.040879
DO

(% has a special meaning inside format(), using %% to get a single % in the string.)

Also note the syntax :'now_utc' to get a quoted string.

How can I quote a named argument passed in to psql?
How to concatenate psql variables?

To just use the current UTC timestamp, you don't need all this complication:

DO
$do$
DECLARE
   _now_date timestamp := now() AT TIME ZONE 'UTC';
BEGIN
  RAISE NOTICE '%', _now_date;
END
$do$;

DO statements are not meant to take parameters. It's simpler to create a (temporary) function and pass value(s) as function parameter(s) in the call:

CREATE FUNCTION pg_temp.foo(_now_date timestamp)
  RETURNS void AS
$func$
BEGIN
  RAISE NOTICE '%', _now_date;
END
$func$  LANGUAGE plpgsql;

Call with SQL interpolation in psql:

SELECT pg_temp.foo(:'now_utc');

Finally, for passing values, you can also (ab)use a Postgres "customized option" (as session variable):

SET myvars.now_date TO :'now_utc';

DO
$do$
BEGIN
  RAISE NOTICE '%', current_setting('myvars.now_date', true);
END
$do$;

Note that the the value is stored as text and you may need to cast.

Details:

Declaring variable for a whole script

But then you might skip the psql variable and set the option in Postgres directly ...


How can I limit SQL Server Agent's simultaneous jobs number? I'm looking for a setting (other than schedules shifting) which could limit the number of jobs running simultaneously.
I have 844 jobs in my SQL Agent (SQL Server 2017 Enterprise). Most of them needs few minutes to complete, there are about 190 jobs that needs half of hour. All of them are scheduled to run every hour.
If they run all together, I can't fit total duration time below 60 minutes.
Is there any setting that could queue 744 jobs until one from those 844 finishes and SQL Agent would start the next job waiting in the queue?

This is best done outside of SQL Agent.  Honestly, at 800+ jobs running every hour you should have moved to a different method long before now.

My preference for this would be to create a single master SSIS package that reads in a list of jobs that need to be run and then starts up the first X in the list, then sleeps until the number of running jobs is below threshold, then starts up the next X in the list.  You could randomize the list or sort it, as you choose.  I've done something similar to this in my environment where I needed to make sure that certain classes of packages (client imports) were done one at a time.

The less dynamic approach would be to just break the list of jobs that need to be run into however many simultaneous you want running.  Each of those would have a list of steps that call the actual job that needs running.  It takes less fiddling about with timing, sleeps and querying active jobs but is less dynamic (for example, changing the number of max simultaneous threads will take work.

But in any case, I would leave the jobs as they are now, just remove/disable their schedules.  Then whatever solution you want can just call the sp_start_job procedure.  This lets you still easily run any job you want out of cycle and you see independent reports of success/failure.


SQL Server 2012: flatten hierachy to dynamic number of columns In MS SQL Server SQL 2012, I have a hierarchy table with a dynamic number of levels. How can I transform the following table:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))

INSERT INTO #Hierarchy VALUES (1, NULL, 'Revenue')
INSERT INTO #Hierarchy VALUES (2, NULL, 'Costs')
INSERT INTO #Hierarchy VALUES (3, 1, 'Sales')
INSERT INTO #Hierarchy VALUES (4, 1, 'Interests')
INSERT INTO #Hierarchy VALUES (5, 2, 'COGS')
INSERT INTO #Hierarchy VALUES (6, 2, 'Expenses')
INSERT INTO #Hierarchy VALUES (7, 6, 'Salaries')
INSERT INTO #Hierarchy VALUES (8, 6, 'Other')

to get such a table:

Regards,
Przemek

I can't come up with anything better than building out a Dynamic SQL Statement for what you're trying to do.  This is neither easy to read nor efficient in execution, but it should do what you want and will work with any number of parent/child levels.  I'm also sure there are other ways to assemble this statement, so feel free to adjust this to your liking.

DB Fiddle for the following:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))
INSERT INTO #Hierarchy VALUES (1,NULL,'Revenue')
INSERT INTO #Hierarchy VALUES (2,NULL,'Costs')
INSERT INTO #Hierarchy VALUES (3,1,'Sales')
INSERT INTO #Hierarchy VALUES (4,1,'Interests')
INSERT INTO #Hierarchy VALUES (5,2,'COGS')
INSERT INTO #Hierarchy VALUES (6,2,'Expenses')
INSERT INTO #Hierarchy VALUES (7,6,'Salaries')
INSERT INTO #Hierarchy VALUES (8,6,'Other')

DECLARE @maxLevels INT

-- Retrieve Max Number of Levels From Hierarchy
;WITH depthCheck as
(
  SELECT H.AccountId, H.ParentId, Name, 0 AS Level
  FROM #Hierarchy H
  WHERE H.ParentId IS NULL

  UNION ALL

  SELECT Hc.AccountId, Hc.ParentId, Hc.Name, dc.Level + 1 AS Level
  FROM #Hierarchy Hc
  INNER JOIN depthCheck dC
  ON Hc.ParentId = dC.AccountId
 )
-- Add 1 Level because your column labels start with 1 instead of 0
SELECT @maxLevels = MAX(Level) + 1 From depthCheck

-- Declare some variables needed to assemble the Dynamic SQL Statement
DECLARE @qLevel INT = @maxLevels, @iLevel INT = @maxLevels, @sqlSelectStmt VARCHAR(MAX) = '', @sqlFromStmt VARCHAR(MAX) = ''

-- This nested While Loop builds out both custom SELECT and FROM clauses
WHILE @qLevel > 0
BEGIN

    -- If Root level, the format will be different
    IF @qLevel = 1
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', /\Name/\ AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + '#Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + @sqlFromStmt
    END
    -- If not a Root Level, COALESCE statements will be required for the SELECT clause and JOINS will be required in the FROM clause
    ELSE
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', COALESCE(/\Name/\) AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + 'LEFT JOIN #Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9)
                         + 'ON L' + CAST(@qLevel - 1 AS VARCHAR(20)) + '.AccountId = L' + CAST(@qLevel AS VARCHAR(20)) + '.ParentId' + CHAR(13) + CHAR(10) + @sqlFromStmt
    END

    -- Build out the cascading COALESCE statment
    WHILE @iLevel > 0
    BEGIN
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\Name/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.Name, /\Name/\')

        IF @iLevel = 1
            SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\Name/\', '')

        SET @iLevel = @iLevel - 1
    END

    -- Decriment @qLevel and Reset @iLevel counters
    SET @qLevel = @qLevel - 1
    SET @iLevel = @qLevel
END

-- Include the AccId Column
SET @sqlSelectStmt = CHAR(9) + '  COALESCE(/\ID/\) AS AccId' + CHAR(13) + CHAR(10) + @sqlSelectStmt

-- Build out the cascading COALESCE statment for the AccId column
SET @iLevel = @maxLevels
WHILE @iLevel > 0
BEGIN
    SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\ID/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.AccountId, /\ID/\')

    IF @iLevel = 1
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\ID/\', '')

    SET @iLevel = @iLevel - 1
END

--PRINT @sqlSelectStmt
--PRINT @sqlFromStmt

-- Assemble final Dynamic SQL Statement
DECLARE @finalSQL VARCHAR(MAX)
SET @finalSQL = 'SELECT' + CHAR(13) + CHAR(10) + @sqlSelectStmt
              + 'FROM' + CHAR(13) + CHAR(10) + @sqlFromStmt
              + 'WHERE' + CHAR(9) + 'L1.ParentId IS NULL'

-- Print for good measure
PRINT @finalSQL

-- Execute the assembled statement
EXEC(@finalSQL)

DROP TABLE #Hierarchy


Optimized table design I am building an advertisements system for websites ( a plugin for a framework ). I am not quite sure how I should set up the table in terms of indexes.

Basically there are two options for billing - by impressions and by clicks, so I guess the table fields ( initially, without customization ) would be

this may not be valid code, just a representation

`id` int(10) unsigned AUTO_INCREMENT,
`headline` VARCHAR(100),
`text` VARCHAR(255),
`url` VARCHAR(255),
`impressions` int(10) unsigned NULL,
`clicks` int(10) unsigned NULL,
`threshold` int(10) unsigned

Where threshold will be the amount of impressions/clicks paid for, and respectivelly impressions or clicks will hold the same amount initially, and will then decrease as the advertisement gets attention accordingly.

I'm having my doubts about this design because one of the impressions or clicks columns has to be empty at all times, and this just doesn't seem right. Also my select queries will look a little messy

SELECT (fields) FROM adverts WHERE ( clicks > 0 OR impressions > 0)

And then from the selected advertisements I have to reduce impressions

UPDATE adverts SET impressions = impressions - 1 WHERE id IN (selected previously)

But if the advertisement is paid as clicks, setting NULL to NULL - 1 doesn't make much sense, but seems to work, surprisingly for me.

Also what I'm more concerned about are indexes. Having in mind that most probably there will be an audience selection option, where advertisers will be able to chose their audience by, mainly, age and location. I'd like to ask, how are indexes used in such situations where there are going to be such complex queries and multiple possible columns to validate the record from ( clicks and impressions )? Indexing either of them is pointless because the driver can only use one index and is still going to have to go through all other records. This makes me think that I'm using the wrong schema, is there a better way?

I'll get a answer started for you on the multiple column index questions.

Multiple Column Indexes

CREATE INDEX myindex ON table (clicks, impressions);

  MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Example

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

  The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookups in the following queries:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

  However, the name index is not used for lookups in the following queries:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Your Query

This query would not be used in lookups on the index.

SELECT (fields) FROM adverts WHERE clicks > 0 OR impressions > 0;

But this one would.

SELECT (fields) FROM adverts WHERE clicks > 0;

This is why I suggested campaign flags because you can combine the flags in your index to indicate if clicks, impressions OR both are present.

CREATE INDEX myindex ON table (campaign_flag);

To query

SELECT (fields) FROM adverts WHERE campaign_flag = 'Search';  --Clicks Exist
SELECT (fields) FROM adverts WHERE campaign_flag = 'Display'; --Clicks/Impressions Exist

Campaign Flag / Campaign Type Suggestion

If this is for digital marketing, campaign flags or campaign types could be used to further filter down to the clicks and impressions you are really needing.

Let's say there are 1,000 records in the table. If you query looking for both clicks and impressions > 0, then you will search all 1,000 records for a result.

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND (clicks > 0 OR impressions > 0);

Now let's add campaign_flag field to the table and divide our data up into segments. 500 records are marked as 'Display' and 500 are marked 'Search'. This is a much better approach to index on as opposed to the full 1,000.

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Search' --Searching for just Clicks
AND clicks > 0;

Compensation Models

Compensation models go hand-and-hand with campaign flags or types. Both Search and Display could have a range of different models now and in time. Knowing you have 500 Display records in the previous example, you could also have 250 that are CPC and the other CPM

SELECT
   (fields)
FROM adverts
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND cmodel = 'CPM' --Further filtering based on type of model
AND clicks > 0;

http://www.basimos.com/web-marketing/internet-marketing-glossary-c.htm


Data model on online exam system I am trying to implement online examination system.

There are two types of question, either fill in the blanks, or multiple choice questions (MCQs).

Right now, I modeled tables as:

questions (
    qid INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT,
    answer VARCHAR(20),
    mcq TINYINT(1)
);

options (
    qid INT,
    options VARCHAR(20)
);

Options will be stored as:

{
    (1,option1),
    (1,option2),
    (1,option3),
    (1,option4)
}

MCQ is a 1-bit flag to flag whether it is MCQ or not, if yes, then it fetches options from options table.

For different courses in any institute, should I maintain  different table for different courses, or add course field in question table itself?
Is it okay to store answer in questions table, or should I maintain a separate answers table ?

answers (
    qid INT,
    course VARCHAR(10),
    answer
);

For future additions etc. it is better to have a course table and refer to it in your questions table. This gives you flexibility when modifying your structure.
Again, for future additions you had better to have a separate answer table.

Your design can be like this:

Since it is a one-to-many relationship, there will be a course_id field in the question table.