Optimize finding newest x rows from category The query for this problem is fairly simple and has been answered many times before, I however have problems with optimizing it.

Say I have a table with the columns timestamp(bigint), category(int), and value(int). I need to efficiently find the top x rows sorted by timestamp descending where category matches.

I currently do this via the following query:

SELECT   *
FROM     table
WHERE    category=315
AND      timestamp > 1488983069581
ORDER BY timestamp DESC
LIMIT 10

To speed up the query I use an index:

ADD INDEX `fetch` (`category` ASC, `timestamp` ASC);

This speeds up my query significantly.

Sadly this is still pretty slow. My table has a few million entries containing about 300 different categories and a lot of entries per category. ~100 fetches take about a second on my above average home desktop. It is of course a fairly heavy operation on some millions of rows, but I would not expect it to take over a second. It is not the overhead of the requests as a smaller table returns instantly.

I wonder if I just underestimate the time to fetch or that I do something wrong. From a computer science standpoint this is a very easy query where a lot of preprocessing can be applied (on insertion). For example, using a hash table for the category leading into a descending sorted list would give pretty much constant search time. My MySQL knowledge is limited though, advice would be very much appreciated.

Version: 10.1.16-MariaDB (32-bit)

I would say I have about 1GB RAM available for MySQL. If 1GB is very little, I could probably add more, up to 4GB. The thing is I would like to save some for the application that uses the queries.

innodb_buffer_pool_size 16MB.

Create table

delimiter $$

CREATE TABLE `data_f32` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category` int(10) unsigned NOT NULL,
  `timestamp` bigint(20) unsigned NOT NULL,
  `value` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `data_f32_foreign_data_id_idx` (`category`),
  KEY `fetch` (`category`,`timestamp`) USING BTREE,
  CONSTRAINT `data_f32_foreign_category` FOREIGN KEY (`category`) REFERENCES `config_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13648145 DEFAULT CHARSET=latin1$$

Execution plan

id | select_type | table    | type  | possible_keys                        | key    | key_len | ref      | rows  | Extra
1  | SIMPLE      | data_f32 | range | "data_f32_foreign_category_idx,fetch" | fetch  | 12      | NULL     | 94642 | "Using where"

DB info

Community Wiki answer compiled from comments on the question

You should probably check the InnoDB settings. You may have given very little RAM for MySQL to use. How much RAM do you have, and what's the setting of innodb_buffer_pool_size?

It's usually set up around 70-80% of RAM in servers. Or more if your RAM is larger (say, more than 64GB).

Your index is good, yes. You might get better performance if you make that index  (data_id, timestamp) the PRIMARY KEY (and demote the (id) to a simple index). That would probably improve performance of the query (to a few milliseconds) at the expense however of slightly slower writes.

The UNIQUE (id) index is redundant. You can remove it (but that is irrelevant to the issue). I would probably remove the (data_id) index as well. The fetch index covers that as well. - ypercubeᵀᴹ

A tiny (16M) buffer pool cache leads to a lot of I/O. - Rick James


PostgreSQL: SET-ing from psql arguments Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution:

SET statement_timeout TO '1min';

This is great, but sometimes I want to relax the restriction for a specific script.

Is there a way to set the timeout as a psql argument? I've tried psql --set statement_timeout=50, unsuccessfully.
Is there a way to set it from a Python script which uses psycopg2?

You can use the environment variable PGOPTIONS.  Either set it permanently or just when you call psql, e.g.,

PGOPTIONS='--statement-timeout=1min' psql ...

This works for any libpq client, including psycopg.

Of course you could also just put the SET statement in the  psqlscript.


Do indexes activate immediately? After you create an index for a table, should we see performance improve immediately? Does the application using it need to re-connect or should it just stay running as normal?

After an index is created on a table, it can be used immediately by queries. But that doesn't mean the index is going to be used or that the index is going to improve performance immediately. Validate that the index is being used by the query(ies) you were targeting and compare performance differences.

The first time the index is used, the database may have to read the index pages from disk into memory, so it might take a little longer than on subsequent runs when the index may already be in memory.


Postgres Compare with Date in Where Clause I have a table with ~200 million rows that has a date column. I want to perform the following query.

select my_date from my_table
where my_date > to_date('2020-10-01', 'YYYY-MM-DD');

As the query is executing, on each my_date in each row, the date value in the database will be compared with my string date cast into a date object. Is postgres smart enough to realize that this transformation from string to date object needs only to be performed once or will it execute with each row?

Postgres will do the string to date conversion just once, because to_date is a STABLE function and the argument is a constant.

But if you’re worried about this, use the date literal syntax: date '2020-10-01'.

See https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT


PIVOT, Row_Number() returning multiple rows I'm trying to pivot a list of emails onto a single row, but for some reason my primary email address is getting a row of its own, so Im ending up with two rows per contact.

CREATE TABLE #tmp (
ID VARCHAR(20)
, EMAIL VARCHAR(50)
, isPrimary VARCHAR(1)
)

INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'ABoyd@Energize.com', '1')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'BSykes@Energize.com', '0')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'TC6@test.com', '0')

How would I amend the script below to return only one row containing all 3 email addresses rather than two rows?

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL, isPrimary
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

Any help would be appreciated, thanks.

Try removing isPrimary from your SELECT where you're calculating the ROW_NUMBER().

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

| ID                   | 1                  | 2                   | 3            |
|----------------------|--------------------|---------------------|--------------|
| B4100358183Q!ZH.ETES | ABoyd@Energize.com | BSykes@Energize.com | TC6@test.com |


SSMS connections not using KERBEROS over VPN - why? I have a very simple setup. SQL Server X (2012) and SQL Server Y (2016). X has a linked server to Y using the setting "Connections will: Be made using the login's current security context". SPNs are set up, and internal users on our domain can query the linked server. I can see their connections authenticating through KERBEROS if I run this query:

select session_id,net_transport,client_net_address,auth_scheme
from sys.dm_exec_connections WHERE net_Transport = 'TCP'

However, a few external users connect via VPN. They then shift+right click on SSMS and "run as a different user" and use their account located on our domain. They can connect to the instances separately, but hit the 'double-hop' issue like below when trying to query the linked server:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I run the above query, I can see their connections are using NTLM authentication, not KERBEROS, so that explains why credentials are not passed through. Why are being authenticated with NTLM? Is this a configuration issue with the VPN? Maybe a limitation to a VPN?

**** UPDATE ****

Further testing has shown that if a user connects to a machine on our network via RDP and leaves that connection open, connections from their local SSMS then seem to use KERBEROS. When the user logs off of the RDP session, new connections from SSMS then start using NTLM again. In other words, they are doing NOTHING inside the RDP session, but as long as that's open, connections from SSMS use KERBEROS. (Maybe a coincidence, but that's the best I've got now)

Assuming the client has SSMS 2012 installed, you might have one of them try running the following from a command-prompt as a test:

  %SystemRoot%\System32\runas.exe /netonly /user:DOMAIN\Username "%ProgramFiles(X86)%\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe"

If they have some other version of SSMS, you'd need to replace the "110" with the appropriate version number, such as 120 for SSMS 2014 or 130 for SSMS 2016, etc.

If that does work, and you are so-inclined, you could use my answer on StackOverflow to create a small User Interface for your users that could prompt them for the domain\username\password then launch SSMS using netonly authentication.  This would essentially mean they wouldn't need to run the "runas" command manually.


access all_mview or user_mviews from app code I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?

by default all users can read the user_mviews and all_mviews views since the SELECT right is granted to public:

SQL> SELECT grantee, privilege, table_name
  2    FROM dba_tab_privs
  3   WHERE table_name IN ('USER_MVIEWS', 'ALL_MVIEWS');

GRANTEE  PRIVILEGE  TABLE_NAME
-------- ---------- ------------
PUBLIC   SELECT     USER_MVIEWS
PUBLIC   SELECT     ALL_MVIEWS

If for some reason this grant has been removed, you would need to regrant the rights to either public or a specific user (with a DBA account):

GRANT SELECT ON user_mviews TO <user>;

Edit: By definition, USER_MVIEWS describes materialized views owned by the current user while ALL_MVIEWS describes the materialized views accessible to the current user (the user needs to be granted SELECT on the mview either directly or through a role).


Creating trigger to manipulate language This is the table that I have. Effective date is a user input, then I need to use a trigger that produces Print depending on the language the user inputs (English or French only).

For English, I used DATE_FORMAT to achieve the output as shown, but I am completely lost on what I should do if the user chooses French. The desired output would be 23 Mai 2016.

I was thinking of storing all the months in a separate table and then calling that table depending on the month component of the date provided but I'm still not sure if that will work. Any help is appreciated.

╔══════════╤════════════════╤═════════════╗
║ Language │ Effective Date │ Print       ║
╠══════════╪════════════════╪═════════════╣
║ English  │ 23/05/2016     │ 23 May 2016 ║
╟──────────┼────────────────┼─────────────╢
║ French   │ 23/05/2016     │             ║
╚══════════╧════════════════╧═════════════╝

Edit:

Trigger Code:

DELIMITER |

CREATE TRIGGER printdate
BEFORE INSERT ON `h&a` FOR EACH ROW
BEGIN
  IF NEW.language = "English"
  THEN
  SET NEW.print = DATE_FORMAT(NEW.`effective date`, '%d %M %Y')
  END IF;
  END|

 DELIMITER ;

Here is a way to get the name of the month in French, then you can use CONCAT to construct the date however you want:

SET @month=month("23/05/2016");
SELECT CASE @month
    WHEN 1 THEN "janvier"
    WHEN 2 THEN "février"
    WHEN 3 THEN "mars"
    WHEN 4 THEN "avril"
    WHEN 5 THEN "mai"
    WHEN 6 THEN "juin"
    WHEN 7 THEN "juillet"
    WHEN 8 THEN "août"
    WHEN 9 THEN "septembre"
    WHEN 10 THEN "octobre"
    WHEN 11 THEN "novembre"
    WHEN 12 THEN "décembre"
END INTO @nom_de_mois;
SELECT @nom_de_mois;

Working example:

mysql> SET @month=month("2016-05-23");
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CASE @month
    ->     WHEN 1 THEN "janvier"
    ->     WHEN 2 THEN "février"
    ->     WHEN 3 THEN "mars"
    ->     WHEN 4 THEN "avril"
    ->     WHEN 5 THEN "mai"
    ->     WHEN 6 THEN "juin"
    ->     WHEN 7 THEN "juillet"
    ->     WHEN 8 THEN "août"
    ->     WHEN 9 THEN "septembre"
    ->     WHEN 10 THEN "octobre"
    ->     WHEN 11 THEN "novembre"
    ->     WHEN 12 THEN "décembre"
    -> END INTO @nom_de_mois;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @nom_de_mois;
+--------------+
| @nom_de_mois |
+--------------+
| mai          |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(date_format("2016-05-23", "%d"), " ", @nom_de_mois, " ", date_format("2016-05-23", "%Y")) AS date_in_French;
+----------------+
| date_in_French |
+----------------+
| 23 mai 2016    |
+----------------+
1 row in set (0.00 sec)


Based on your default database encoding, character_set_database=latin1, you should force your client to use latin1.  Your client is currently in utf8 and the database appears to be in latin1.  This assumes that your table is also using latin1.

To make sure your data is stored correctly in MySQL, you can force the mysql client to use latin1.

1 Force the Client to use latin1

mysql -u root -p --default-character-set=latin1 nameofthedatabase

Then run your SELECT statement to verify the character formatting

select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10

2 Backup the database using latin1

You can also dump the database using the same option

mysqldump -u root -p --default-character-set=latin1 nameofthedatabase > nameofthedatabase.sql

Then you could view the dump file nameofthedatabase.sql using a standard text editor like textpad or gedit to see if the characters are encoded correctly.

Collation is a set of rules that determine how rows are sorted and how they are compared.  Collation does not affect the encoding which is the problem you are currently having.

If both options still give you weird symbols, the import file may not have been correctly formatted in the first place.


How can I know the progress when importing data into MySQL using LOAD DATA INFILE? I have a 1 billion record stored in csv file. I'm using LOAD DATA INFILE to import the record to the table.
create table weibo_bind
(
    column1 bigint
        primary key,
    column2 bigint
);

And my csv file is column1,column2.
LOAD DATA INFILE '/path/to/demo.csv' IGNORE INTO TABLE weibo_bind FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

But how can I debug or how can I check the progress of importing? Because it takes a lot of time to import.
Or is there any way to speed up?
I'm using
mysql  Ver 8.0.22, Innodb

Sort the incoming file by the PRIMARY KEY; this will speed up the LOAD.  Don't have any secondary keys during the load.
There is no way (that I know of) to watch the progress in 8.0.
The .ibd file size is not a reliable indication -- you can't easily predict how big it will eventually be.  Datatypes, data values, PK, other indexes, etc, impact the size.
Also, that file will grow in bursts.
Having just two BIGINTs (8 bytes each), the table might be about 40-60 bytes per row.  The smaller range might be about right if the data is in PK order; the larger if not.
I hope the query does not timeout.
The process is probably I/O-bound, indicating that nothing you could do would speed it up.


SQL Server AlwaysOn ReadOnly Routing to Secondary I configured SQL Server AlwaysOn to redirect Read-Only to Secondary and tried to check with ApplicationIntent=ReadOnly setting in SSMS by connecting to the Listener. But my connection is always going to Primary only as revealed by

SELECT @@SERVERNAME

Does anyone know what is wrong with the settings or reason for this?
I checked altering the settings but didn't work.

Setup Details:

  SQL Server 2017
  AG Properties
  Using Admin account to test the routing.

Does anyone know what is wrong with the settings or reason for this? I checked altering the settings but didn't work.

You may verify the read-only routing configuration based on this post

Once all set (upon validation): If you're using SQL Login, try to mention default database that is part of AG, then your connection must be re-direct to secondary replica.

or mention following parameter from SSMS connection. This is how usually every application connects.

ApplicationIntent=READONLY; Initial Catalog=YourDatabase


Is there a term for a table that has an "effective date" column? I work with a system which uses a lot of tables whose primary keys include

a foreign key to another table and
a column indicating the date that record became/becomes "active".

Example tables:

employee                    (primary key is employee_id)
    employee_id    INT
    hire_date      DATE

employee_salary             (primary key is employee_id, start_date)
    employee_id    INT      (foreign keyed to employee.employee_id)
    start_date     DATE
    salary         TINYINT

When viewing an employee in our application UI, we would display all the information from employee and whichever employee_salary record had a start date most recent before the date we are interested in.

In my working group, we would refer to employee as a "base" table and employee_salary as a "repeating" table, since we consider the salary to merely be an aspect of the "base" entity, the employee.  We also say that the employee_salary table uses an "effective date".

But these don't seem to be terms in widespread use.  Are there standard terms for

tables that represent an entity, and
tables that represent a changing aspect of one of those entities?

Temporal Table or History Table are probably the terms you are looking for. Often you will see a nullable "end date" column in the table as well.


PostgreSQL UPSERT issue with NULL values I'm having an issue with using the new UPSERT feature in Postgres 9.5

I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable.
Below I have created a smaller version of the issue i'm having, specifically with NULL values.

CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);

Running this query works as needed (First insert, then subsequent inserts simply increment the count):

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun',1,'test value','ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value';

However if I run this query, 1 row is inserted each time rather than incrementing the count for the initial row:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun',1,null,'ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = null;

This is my issue. I need to simply increment the count value and not create multiple identical rows with null values.

Attempting to add a partial unique index:

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);

However, this yields the same results, either multiple null rows being inserted or this error message when trying to insert:

  ERROR:  there is no unique or exclusion constraint matching the ON
  CONFLICT specification

I already attempted to add extra details on the partial index such as WHERE test_field is not null OR identifier is not null. However, when inserting I get the constraint error message.

Clarify ON CONFLICT DO UPDATE behavior

Consider the manual here:

  For each individual row proposed for insertion, either the insertion
  proceeds, or, if an arbiter constraint or index specified by
  conflict_target is violated, the alternative conflict_action is taken.

Bold emphasis mine. So you do not have to repeat predicates for columns included in the unique index in the WHERE clause to the UPDATE (the conflict_action):

INSERT INTO test_upsert AS tu
       (name   , status, test_field  , identifier, count)
VALUES ('shaun', 1     , 'test value', 'ident'   , 1)
ON CONFLICT (name, status, test_field) DO UPDATE
SET count = tu.count + 1;
WHERE tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value'

The unique violation already establishes what your added WHERE clause would enforce redundantly.

Clarify partial index

Add a WHERE clause to make it an actual partial index like you mentioned yourself (but with inverted logic):

CREATE UNIQUE INDEX test_upsert_partial_idx
ON public.test_upsert (name, status)
WHERE test_field IS NULL;  -- not: "is not null"

To use this partial index in your UPSERT you need a matching conflict_target like @ypercube demonstrates:

ON CONFLICT (name, status) WHERE test_field IS NULL

Now the above partial index is inferred. However, as the manual also notes:

  [...] a non-partial unique index (a unique index without a predicate) will
  be inferred (and thus used by ON CONFLICT) if such an index satisfying
  every other criteria is available.

If you have an additional (or only) index on just (name, status) it will (also) be used. An index on (name, status, test_field) would explicitly not be inferred. This doesn't explain your problem, but may have added to the confusion while testing.

Solution

AIUI, none of the above solves your problem, yet. With the partial index, only special cases with matching NULL values would be caught. And other duplicate rows would either be inserted if you have no other matching unique indexes / constraints, or raise an exception if you do. I suppose that's not what you want. You write:

  The composite key is made up of 20 columns, 10 of which can be nullable.

What exactly do you consider a duplicate? Postgres (according to the SQL standard) does not consider two NULL values to be equal. The manual:

  In general, a unique constraint is violated if there is more than one
  row in the table where the values of all of the columns included in
  the constraint are equal. However, two null values are never
  considered equal in this comparison. That means even in the presence
  of a unique constraint it is possible to store duplicate rows that
  contain a null value in at least one of the constrained columns. This
  behavior conforms to the SQL standard, but we have heard that other
  SQL databases might not follow this rule. So be careful when
  developing applications that are intended to be portable.

Related:

Allow null in unique column

I assume you want NULL values in all 10 nullable columns to be considered equal. It is elegant & practical to cover a single nullable column with an additional partial index like demonstrated here:

PostgreSQL multi-column unique constraint and NULL values

But this gets out of hand quickly for more nullable columns. You'd need a partial index for every distinct combination of nullable columns. For just 2 of those that's 3 partial indexes for (a), (b) and (a,b). The number is growing exponentially with 2^n - 1. For your 10 nullable columns, to cover all possible combinations of NULL values, you'd already need 1023 partial indexes. No go.

The simple solution: replace NULL values and define involved columns NOT NULL, and everything would work just fine with a simple UNIQUE constraint.

If that's not an option I suggest an expression index with COALESCE to replace NULL in the index:

CREATE UNIQUE INDEX test_upsert_solution_idx
    ON test_upsert (name, status, COALESCE(test_field, ''));

The empty string ('') is an obvious candidate for character types, but you can use any legal value that either never appears or can be folded with NULL according to your definition of "unique".

Then use this statement:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun', 1, null        , 'ident', 11)  -- works with
     , ('bob'  , 2, 'test value', 'ident', 22)  -- and without NULL
ON     CONFLICT (name, status, COALESCE(test_field, '')) DO UPDATE  -- match expr. index
SET    count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

Like @ypercube I assume you actually want to add count to the existing count. Since the column can be NULL, adding NULL would set the column NULL. If you define count NOT NULL, you can simplify.

Another idea would be to just drop the conflict_target from the statement to cover all unique violations. Then you could define various unique indexes for a more sophisticated definition of what's supposed to be "unique". But that won't fly with ON CONFLICT DO UPDATE. The manual once more:

  For ON CONFLICT DO NOTHING, it is optional to specify a
  conflict_target; when omitted, conflicts with all usable constraints
  (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a
  conflict_target must be provided.


Summ up between events SELECT
  A.date as timestamp,
  A.driveNumber as 'Drive Number',
  SUM(2 * 6374.612 * asin(sqrt(POW(sin(radians((B.latitude - A.latitude) / 2)),2) + cos(radians(A.latitude)) * cos(radians(B.latitude)) * POW(sin(radians((B.longitude - A.longitude) / 2)),2)))) as KmDriven
FROM teslafiData A
JOIN teslafiData B ON (A.ta_id = B.ta_id + 1)
WHERE A.latitude <> '' and A.longitude <> '' and B.latitude <> '' and B.longitude <> ''
GROUP By A.driveNumber
ORDER BY KmDriven DESC

This gives me KmDriven for each driving event. Each drive has its own driveNumber.

Now I need to get km drive between each charging event. Between charges there might be several driving events. Each charge is listed by chargeNumber and while charging the driveNumberis 0 and chargeNumber is counting up for each new charge.

Data Example in the DB:

ta_id    latitude      longitude  driveNumber   chargeNumber
1        47.xxx        8.xxx      1             0
2        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
3        47.xxx        8.xxx      1             0
...
80       47.xxx        8.xxx      2             0
81       47.xxx        8.xxx      2             0
82       47.xxx        8.xxx      2             0
...
151      47.xxx        8.xxx      2             0
152      47.xxx        8.xxx      0             1
153      47.xxx        8.xxx      0             1
154      47.xxx        8.xxx      0             1
...
256      47.xxx        8.xxx      0             1
257      47.xxx        8.xxx      3             0
258      47.xxx        8.xxx      3             0
259      47.xxx        8.xxx      3             0

EDIT:

I now managed to get the ta_id when a charging session ends and when it starts again. Therefore I need now to figure out how to calculate the distance between those two events

SELECT ta_id, chargeNumber
FROM (
SELECT ta_id, chargeNumber,
       IF ( @prev_ver <> chargeNumber,
           IF (@prev_ver := chargeNumber, 1, 1),
           IF (@prev_ver := chargeNumber, 0, 0)) AS IsDifferent
FROM teslafiData
CROSS JOIN (SELECT @prev_ver := '-1') AS var
WHERE battery_range <> 0 and latitude <> '' and longitude <> '' and latitude <> '' and longitude <> ''
ORDER BY ta_id ) A
WHERE A.IsDifferent = 1
ORDER BY ta_id ASC

Don't use '' to indicate missing lat/lng -- use NULL instead.  Meanwhile keep lat/lng in, say FLOAT.  See: http://mysql.rjweb.org/doc.php/latlng#representation_choices

If you are using MySQL 8.0 or Mariadb 10, use LEAD(1) and OVER ( PARTITION BY chargeNumber ORDER BY tag_id ) to get the calculate drive distances and the first lat-lng per charge.  In the absence of that windowing function, it would be better to do the arithmetic in your application.

Use LEAD(1) to find the distance for each distance.
SUM() with GROUP BY chargeNumber to get the total distance for each charge.

Be aware that the drive-distance falsely assumes a straight line.


Building a query to copy user security settings to another user I am trying to write a query to look at a current users security settings, and then copy those settings over to another user. My data is as follows:

 Logon_user  Logon_level   Form
 ABOND        1            COST
 ABOND        0            PRODUCTION
 ADOND        0            ACCOUNTING
 ATEMPLE      0            COST
 ATEMPLE      0            PRODUCTION
 ATEMPLE      0            ACCOUNTING

I would like to look at the settings for ABOND and copy the logon_level values to ATEMPLE.

You could try something like:

CREATE TABLE dbo.MyTable
(
    Logon_User VARCHAR(255)
    , Logon_Level INT
    , Form VARCHAR(255)
);

INSERT INTO dbo.MyTable VALUES
('ABOND', 1, 'COST')
, ('ABOND', 0, 'PRODUCTION')
, ('ABOND', 0, 'ACCOUNTING')
, ('ATEMPLE', 0, 'COST')
, ('ATEMPLE', 0, 'PRODUCTION')
, ('ATEMPLE', 0, 'ACCOUNTING');

UPDATE MyTable
SET Logon_Level = T.Logon_Level
FROM MyTable
    INNER JOIN MyTable T ON MyTable.Form = T.Form
WHERE MyTable.Form = T.Form
    AND T.Logon_User = 'ABOND'
    AND MyTable.Logon_User = 'ATEMPLE';

SELECT *
FROM dbo.MyTable;


MSSQL Right / Left Trim by Character How can I trim last (or first) characters from a string in SQL server?
Given a string like '~~abc~~', the output will be '~~abc'.

For example, in Postgresql, SELECT trim(trailing '~' FROM '~~abc~~') returns '~~abc'.

There should be more efficient methods if you are doing this for a lot of data, but one way to do this could be by using LEFT(), RIGHT() and PATINDEX()

DECLARE @string varchar(255) = '~~abc~~';

SELECT RIGHT(@string,LEN(@String)-PATINDEX('%[^~]%',@string)+1); -- Remove leading ~
SELECT LEFT(@string,LEN(@String)-PATINDEX('%[^~]%',REVERSE(@string))+1);-- Remove trailing ~

Results

(No column name)
abc~~

(No column name)
~~abc


Getting the top count of records from a table for each month and user I have a table where translations are stored along with the user_id and when the translation was added.
I want to create a query that shows the 'Translator of the month' (the person who did the most translations in that month).

I have a query to get the top translator of all translations:

SELECT user_id, COUNT(user_id)
FROM gp_translations
WHERE (STATUS='current' OR STATUS='old')
GROUP BY user_id
ORDER BY COUNT(user_id)

and I have a query that shows the year/month:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

but when combining both I fail miserably. The query runs endlessly:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`, (
SELECT distinct user_id
FROM gp_translations t2
WHERE (STATUS='current' OR STATUS='old') AND     YEAR(t2.date_added)=YEAR(t1.date_added)
AND MONTH(t2.date_added)=MONTH(t1.date_added)
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 1)
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

The table gp_translations has the following structure:

 CREATE TABLE `gp_translations` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`original_id` INT(10) NULL DEFAULT NULL,
`translation_set_id` INT(10) NULL DEFAULT NULL,
`translation_0` TEXT NOT NULL,
`translation_1` TEXT NULL,
`translation_2` TEXT NULL,
`translation_3` TEXT NULL,
`translation_4` TEXT NULL,
`translation_5` TEXT NULL,
`user_id` INT(10) NULL DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
`date_added` DATETIME NULL DEFAULT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`warnings` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `original_id` (`original_id`),
INDEX `user_id` (`user_id`),
INDEX `translation_set_id` (`translation_set_id`),
INDEX `translation_set_id_status` (`translation_set_id`, `status`),
INDEX `date_added` (`date_added`),
INDEX `warnings` (`warnings`(1))
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=342587;

I know I am doing something seriously wrong here. But I can't find the way to properly merge both with decent speed. Any help is appreciated. MySQL version is 5.5.34

Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):

SELECT a.*, c.*
FROM
(
   SELECT yr,mn, MAX(translations_per_month) as max_user_translations
   FROM
   (
     SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id,
     COUNT(1) as translations_per_month
     FROM `gp_translations` t1
     INNER JOIN gp_users u ON
      (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
     GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
    )b
    GROUP BY yr,mn
)a
INNER JOIN
(
   SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
   COUNT(1) as translations_per_month
   FROM `gp_translations` t1
   INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
   GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

Also, I made a guess that status column is in gb_users table ; if not, the query needs to be modified a bit.


Transaction log backup I have database of 800GB average but when I run a backup job (of system and user databases) the transaction log backup reports a failure: There is not enough space on the disk.

When I make a transaction log backup the size of this would decrease or do I need a shrink (after or before run a backup)?

Shrinking is bad for performance and is a waste of time since it's just going to grow again. You need to add more disk space to accommodate the backups that you need. If you don't care about point-in-time recovery for the database, which it sounds like may be the case since there isn't enough storage even for a LOG backup, switch your recovery model to SIMPLE. With SIMPLE, you won't need LOG backups. But which recovery model you need is dependent upon your business needs of the database.


Convert a row to a nested JSON document containing all relations In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the foreign keys of the NEW record in the JSON object in a nested fashion.

For example:

before:

employee = {
    "id": 1,
    "name": "myname",
    "department": 2,
    "phone_no": "123456789"
}

after:

employee = {
    "id": 1,
    "name": "myname",
    "department": {
        "id": 2,
        "name": "IT"
    },
    "phone_no": "123456789"
}

What is the best and most generic way to accomplish this without prior knowledge about the schema of the NEW record? I need to keep this trigger function as generic as possible because I plan to use it on all tables. One level of depth in following foreign keys is currently enough for me. Also to simplify, I can assume that all foreign keys should be a single column.

As I understand, I need to loop over all columns in the NEW record, find out if the column is a foreign key using information_schema or pg_catalog, find the foreign key details like to which column on which table it points to, then perform a dynamic SQL SELECT (because I presume table and column names would be strings, not SQL identifiers) over the target table for the target record, convert the record to JSON and finally assign it to the appropriate key of the top-level row JSON object.

I'm yet trying to write the actual working code for this, for which I welcome any help or directions. And there might be simpler solutions to this problem, which I would like to know about.

Your guess is pretty close, you'll need dynamic SQL.

But this should be considerably faster and more elegant than looping through all columns in the NEW record etc.:

CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk()
  RETURNS trigger AS
$func$
DECLARE
   _sql text;
   _jsonb_row jsonb;
BEGIN
   SELECT 'SELECT to_jsonb($1) || '
       || string_agg(
            format('(SELECT jsonb_build_object(%1$L, t.*)
                     FROM %2$s t WHERE %3$I = $1.%1$I)'
                 , a.attname                 -- %1$L, %1$I
                 , c.confrelid::regclass     -- %2$s
                 , f.attname)                -- %3$I
            , ' || ')
   FROM   pg_constraint c
   JOIN   pg_attribute  a ON a.attrelid = c.conrelid
   JOIN   pg_attribute  f ON f.attrelid = c.confrelid
   WHERE  c.conrelid = TG_RELID
   AND    c.contype  = 'f'               -- to select only FK constraints
   AND    a.attnum   = c.conkey[1]       -- assuming only single-col FKs!
   AND    f.attnum   = c.confkey[1]
   INTO   _sql;

   IF FOUND THEN                         -- FKs found
      EXECUTE _sql USING NEW INTO _jsonb_row;
   ELSE                                  -- no FKs found, plain conversion
      _jsonb_row := to_jsonb(NEW);
   END IF;

   RAISE NOTICE '%', _jsonb_row;         -- do something with it ...
   RETURN NEW;                           -- proceed with org. row
END
$func$  LANGUAGE plpgsql;

Example trigger using above function:

CREATE TRIGGER upd_bef_jsonb_row_with_fk
  BEFORE UPDATE ON tbl
  FOR EACH ROW EXECUTE PROCEDURE trg_jsonb_row_with_fk();

This builds subqueries for all FKs from the Postgres catalog tables and executes the SQL command dynamically. For simplicity, I include all user columns from corresponding rows in lookup tables (t.*).

Nitpick: jsonb_build_object(%1$L, t.*), not jsonb_build_object(%1$L, t).
Seems like adding noise, but it avoids a corner case problem: This is supposed to work for any input table, and one might contain a column named t. Then the name t in above expression would resolve to the column instead of the table alias (the whole row). Using t.* removes this ambiguity as it can only resolve to the whole row. (Parentheses would be required to refer to a composite type column, like (t).*). Read the manual here and here.

Since it uses the column name of original FK columns as key name for extended objects, plain concatenation with || does what you need: it replaces the existing simple value with the jsonb object.

Further reading:

Retrieving all PK and FK
How to access NEW or OLD field given only the field's name?


Mirrored DB on Restoring state after removing Mirroing also We done Mirroring of MS SQL data base to other system without witness on synchronize mode. When I Remove mirroring(not pausing , not fail over) by manually My Mirrored Database still continuing in Restoring state

That is normal. When you setup a database mirroring the mirror database is on no recovery/restoring mode and copies and run transaction executed on primary server. Then when you remove the database mirroring you just stopped those transaction to be transferred and nothing is happened to bring it back to recovery mode. If you want your secondary (mirror) database to be active you have to execute.

      RESTORE DATABASE YourDatabaseName  WITH RECOVERY


DB model strategy to override field value We have to store some dynamic configuration in JSON format in DB. There would be some default configuration, and it can be overriden per account. Can someone help me with the strategy to store such kind for data.

I have come up with following. If the accountConfig is null then look at defaultConfig.

DefaultConfig table

id, name,   config
1   name1   {x:12, y: 13}
2   name2   {y:10, z:100}

AccountConfig table

id, accountId, defaultConfigId, accountConfig, active
1   1000       1                null           false
2   1000       2                {y:11, z:111}   true

Should I have inheritance, or embedded entities? or anything else?

You can create a view like:

create view all_config as
select y.id, x.accountId, y.config
from accounts x
cross join defaultconfig y
where not exists (
    select 1 from accountconfig z
    where x.accountid = z.accountid
      and y.id = z.defaultconfigid
)
union all
select a.id, a.accountid, a.defaultconfig
from accountconfig a

and select from there to get the current config for an account. Note that you should only store information that is changed in AccountConfig. Don't use null as a merker. In your example, id 1 in AccountConfig should be removed. The view will provide this info from the default table.


How to preseve Execute permissions for MySQL procedure with alter procedure (Drop/Create) In MySQL you can not alter procedure rather you have to perform drop/create procedure to alter the body of the procedure. The problem is doing drop/create also removes the execute permissions given on that selected procedure.

Is there any way of giving permanent execute permissions that remains even after drop/create procedure.

A sample code to verify is as below

  delimiter //
  create  procedure t1 ()
  select 1;
  //

  grant execute on procedure t1 to test1@'%';

call t1() works well, but if you run the above code after doing drop procedure first it gives permissions error.

Any help is much appreciated

Best is to grant execute at the DB level:

grant execute on db.* to test1@'10.%';  (don't use %, too risk, use ip subnet)
This way you don't need to grant permission in SP.


Performance/Health checking queries for a user with Read permissions I am a software developer that only had read access to our prod database (of course).

Occasionally we have what looks like performance issues on our server when my DBAs are not available.

Is there any kind of query I can run (with Data Reader permissions only) that could tell me anything about the current health of my SQL Server?

If there was some kind of edge permission that did not allow for changing data or schema that would help, I could probably get permissions to that.  (For example, I was given permissions to monitor replication in our production environment.)

Have your DBAs create

Adam Machanic's - sp_whoisactive
Brent's - sp_AskBrent

and grant execute permission to you.

That should be a good starting point to find out why your sql server is slow.

Note: Brent has already mentioned on How to Grant Permissions to Non-DBAs. You can follow that method to ask your DBA to grant permissions to you.


Why does requesting the "estimated execution plan" for sp_delete_job throw an error? I'm attempting to look at the estimated execution plan for the following T-SQL statement:
EXEC msdb.dbo.sp_delete_job @job_id = '3A015189-F4EB-439B-9CA0-27AFB74719D8'
    , @originating_server = '(LOCAL)'
    , @delete_history = 0
    , @delete_unused_schedule = 0;

When I do that, SQL Server throws the following error:

Msg 213, Level 16, State 1, Procedure sp_delete_all_msx_jobs, Line 25
Column name or number of supplied values does not match table definition.

The definition of sp_delete_all_msx_jobs contains the following pertinent lines starting at line 25 (wrapped for readability):
INSERT INTO #temp_jobs_to_delete
SELECT sjv.job_id,
    CASE sjs.server_id WHEN 0 THEN 1 ELSE 0 END,
    sjv.owner_sid
FROM msdb.dbo.sysjobs_view sjv
    LEFT OUTER JOIN msdb.dbo.sysjobservers sjs
        ON (sjv.job_id = sjs.job_id)
WHERE (ISNULL(sjs.server_id, 0) = 0)
    AND (sjv.originating_server = @msx_server)

Line 14 of that stored proc shows the definition of #temp_jobs_to_delete is:
CREATE TABLE #temp_jobs_to_delete
(
    job_id UNIQUEIDENTIFIER NOT NULL
    , job_is_cached INT NOT NULL
    , owner_sid VARBINARY(85) NOT NULL
)

That looks valid to me.  Why is "Display Estimated Execution Plan" returning an error?
I can run this on SQL Server 2008R2, and 2012; both return the error.  I'm using SSMS 2012.

Because Microsoft hasn't learned to create unique object names. If you look at the definition for sp_delete_job, you will find this around line 115, a slightly different version of #temp_jobs_to_delete:

CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
                                   job_is_cached INT NOT NULL)

Now, consider that SQL Server may take a slightly different path when generating estimated execution plans for a set of nested procedure calls than if it simply ran the code. It likely does some kind of object-level collection that recognizes the same #table is used and assumes that it must adhere to the same schema (even if logically the two #temp tables would never cross paths).

Now, it is possible that this code will actually break when you try to delete a job and the properties of that job lead to sp_delete_all_msx_jobs to be called, which I suspect in your case did not happen on actual execution (nor in mine). I did not have the energy to set up MSX to evaluate. But remember some of the other things that happen during parsing that couldn't possibly be a problem on actual execution:

IF (1 = 1)
  CREATE TABLE #x(i INT);
ELSE
  CREATE TABLE #x(d DATETIME);

Illogical parsing error:

  Msg 2714, Level 16, State 1
  There is already an object named '#x' in the database.


I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created? I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the first database down. If I set sga_target >=10G it gives below error on startup:

  ORA-27104: system-defined limits for shared memory was misconfigured

While free -m shows that there is enough memeory available though:

             total       used       free     shared    buffers     cached
Mem:         72419      34490      37928          0        618      28159
-/+ buffers/cache:       5711      66707
Swap:         2047          0       2047

Do I need to increase swap space? Any pointer in this regard is highly appreciated.

Also for 70 GB memory, for creating many instances, what would be best value for swap space - is there some way to caluculate this? My objective is to have at least two instances each with sga_target=20G and will keep only one instance up at a time. If I am missing any concept here?

Output of ipcs -im is as below

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

Your kernel parameters need modifying.

Edit /etc/sysctl.conf and ensure the following lines are present:

kernel.shmall = 18350080
kernel.shmmax = 75161927680
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

Then reboot the machine.


SQL Server 2014 SP1 Setup Freezes Removing Temporary Files On Windows 10 x64 I want to install SQL Server 2014 Enterprise Edition with Service Pack 1 on Windows 10. But when I try, the installation stops every time at the part the screenshot below shows, and I wait for hours. Finally, I have to cancel the setup process.

I did some research into the issue, then installed Entity Framework 3.5 and Windows PowerShell 2.0**. I needed no pre-requisites.

Please note that I also could not install Cumulative Update for Windows 10 for x64-based Systems (KB3081438). This update fails to install on my computer the same thing every time. Maybe this problem can be resolved by this update; because I heard that this update is also related to Entity Framework?

What is the reason for this problem? What should I do to solve it?

You are installing SQL Server 2014 Enterprise Edition on Windows 10 (a client OS), which is not supported. If you were to take this case to Microsoft they would say you are trying an unsupported scenario. Although your installation won't fail as such if you are trying to install Enterprise Edition on a client OS, what is the point in wasting such a costly license on a client OS which has so many limitations, compared with a server OS?

The Log you posted is incomplete. You can use this link to locate all the log files regarding installation, then post it on on shared drive for analysis. Please share the link with us so that we can study the logs.


Delete backups from old incarnation in rman I did a refresh on a non-production database and these are the incarnations:

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

1       1       ORCL     1355555557       PARENT  1          20-DEC-11

2       2       ORCL     1355555557       CURRENT 12         17-JUL-14

How do I delete the backups from the old incarnation since they're not needed anymore?
Any help would be greatly appreciated.
Thanks.

I couldn't find any clause to the REPORT or LIST commands which would allow you to specify the incarnation of interest.

You could however correlate your latest incarnation reset time with backup completion time to identify the backups completed before your current incarnation began.

First, let's set the environment variables so that RMAN output also includes time.

$ NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS'
$ export NLS_DATE_FORMAT
$ export NLS_TIMESTAMP_FORMAT

Now let's examine which incarnations we have in RMAN repository.

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- ---     ---------- ----------
1       1       OCP      2737702586       PARENT  1          25.08.2013 05:18:32
2       2       OCP      2737702586       PARENT  635002     05.03.2014 10:49:33
3       3       OCP      2737702586       PARENT  750347     13.03.2014 18:08:15
4       4       OCP      2737702586       PARENT  750727     18.03.2014 11:37:57
5       5       OCP      2737702586       CURRENT 1137478    05.08.2014 08:19:38

It's shown in the output that our current incarnation's reset time is 5 August 2014 8:19 AM (05.08.2014 08:19:38).

I have some backups completed before and after the current incarnation began.

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        05.08.2014 08:21:56 1       1       YES        TAG20140805T082051
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
31      B  A  A DISK        05.08.2014 08:21:59 1       1       YES        TAG20140805T082159
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
33      B  A  A DISK        05.08.2014 08:20:49 1       1       YES        TAG20140805T082048
34      B  F  A DISK        05.08.2014 08:20:51 1       1       YES        TAG20140805T082051
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036
36      B  A  A DISK        05.08.2014 08:20:48 1       1       YES        TAG20140805T082048

Now we can list backups which were completed before the current incarnation began.

RMAN> list backup summary
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
29      B  F  A DISK        05.08.2014 08:12:43 1       1       YES        TAG20140805T081243
30      B  F  A DISK        05.08.2014 08:10:36 1       1       YES        TAG20140805T081036
32      B  F  A DISK        05.08.2014 08:13:48 1       1       YES        TAG20140805T081243
35      B  F  A DISK        05.08.2014 08:11:51 1       1       YES        TAG20140805T081036

As you can see, only backups with completion time before the current incarnation's reset time are shown in the output.

After you narrowed down the backups to the ones whose completion time is before your current incarnation's reset time using COMPLETED subclause of the LIST command, you can specify the same condition to the DELETE command.

RMAN> delete backup
2> completed before
3> "to_date('05.08.2014 08:19:38', 'dd.mm.yyyy hh24:mi:ss')";

There are also AFTER and BETWEEN specifiers to the COMPLETED subclause. See the section Listing Selected Backups and Copies in Database Backup and Recovery User's Guide. An example of BETWEEN specifier usage:

RMAN> list copy of datafile 2 completed between '10-DEC-2002' and '17-DEC-2002';

UPDATE

You can switch between incarnations using RESET DATABASE TO INCARNATION n command (check the scenarios of usage), but you'll always end up with the same output from the LIST and REPORT commands with respect to backup sets, no matter which incarnation you switch to.

You can search Database Backup and Recovery Reference for the word "incarnation", to see when you can specify the incarnation and to which RMAN commands.


Is it possible to encapsulate a table in MySQL? I am new to Databases. I want the following:

UPDATEs in a database to happen only through a stored procedure
The user does not have GRANT UPDATE
The user has GRANT EXECUTE ON PROCEDURE

I tried it this way but figured out that the user needed to have a UPDATE permission.

Is there any other way in which this can be achieved?

In MySQL Stored Procedures, you have the concept of SQL SECURITY.

It can be either DEFINER or INVOKER

When you call a Stored Procedure that has DEFINER for SQL SECURITY, the caller is allowed to have the same grants as the DEFINER for the duration of the call. The GRANT EXECUTE for the specified Stored Procedure is necessary.
When you call a Stored Procedure that has INVOKER for SQL SECURITY, the caller is expected to have the needed grants. If any of the needed grants are missing, the call will fail at the earliest point where the needed grant was missing.

For more information, please read the MySQL Documentation on Access Control for Stored Programs and Views

To see the SQL SECURITY for the procedure or function named mydb.myproc, run this:

SELECT security_type FROM information_schema.routines
WHERE routine_schema='mydb' AND routine_name='myproc';


Oracle Dynamic Sampling The optimizer will attempt to use default database statistics in preference to dynamic statistics. In some cases, oracle will auto use dynamic statistics.

parallel execution
missing statistics
stale statistics
insufficient statistics
have sql plan directives

My question is if a SQL statement with highly selective filters on column that has missing index statistics, will it benefit from dynamic sampling?

The amount of benefit from dynamic sampling will depend on the degree of cardinality estimate errors and how the query plan changes after you correct those errors. It is possible that your query will benefit and a good next step for you is to add a DYNAMIC_SAMPLING hint in your query and to observe how the EXPLAIN PLAN changes. However, a better solution would be to just fix your statistics. All queries will benefit from that instead of just the ones that you tune by hand.

In the workloads that I've observed, the queries that benefit the most from dynamic sampling go the other way: the optimizer estimates that very few rows will be returned from a large table which leads to nested loop joins and index use throughout the plan. If the optimizer knew that the large table returned many rows it would pick a better plan. Suppose the following query returns 100% of the data in X_TABLE:

SELECT COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

On my machine Oracle uses a cardinality estimate of 5% of the rows from the table. This is a default cardinality estimate used in some cases when the optimizer doesn't have enough information. If I add a dynamic sampling hint then the estimate is fixed:

SELECT /*+ DYNAMIC_SAMPLING(X_TABLE 10) */ COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

My rule of thumb is to use a sampling level of 10 for small tables and to use something less than 10 for larger tables. If you use 10 then Oracle will read all blocks from the table before generating an EXPLAIN PLAN.


How to create a table in access and automatically fills it with information from a query? Is it possible if I create a table that will lookup for a value filtered from a query (consult), in this case is duplicated "Name"s from a Employees registring table and automaticly fills up that number of registers, then the only thing that I need to change is the values of other fields I made in this new automatic-table?

Sounds likes you would be best served by using another Query, not a Table.  In Access you can use a Query much like a Table, even use a saved Query as a source in another Query.  If you are careful you can create an Editable Query, in which changes you make to the Query results directly affect the Base Table.  It can be tricky to do so when you have more then one Table/Query involved, as it sounds like you do, but it is a possibility.

You can read more about Editable Queries here


Difference between Archive destination & db_recovery_file_dest What is exact difference between Archive destination & db_recovery_file_dest on Oracle Database?

SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      C:\app\Azar\fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            C:\app\Azar\Arch
Oldest online log sequence     320
Current log sequence           322

Archive log destination define the place where to store archived redo log files(once online redo log file is filled it can be stored as archived log files). You can have multiple archive log destinations.

In other hand, DB_RECOVERY_FILE_DEST specifies the default location for the fast recovery area. The fast recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

You can use DB_RECOVERY_FILE_DEST(which is default if you enable archive log mode in your database without specifying archive log destination explicitly), which is fast recovery area, to store archived redo log files or you can store them in separate locations.

Documentation:

DB_RECOVERY_FILE_DEST

Managing Archived Redo Log Files


Delete from table causes the PostgreSQL database to enter recovery mode It's becoming common to see this problem in the development environment.
My application is not in production yet, but I'm worried this problem could happen.

As I develop new features, I commonly clear some tables when necessary.
I started to get into this problem with one specific table I try to clean.
I delete everything from other tables that could have foreign constraints, but when I try to delete from that specific table, pgAdmin loses the connection and the database enters in recovery mode.

First I get a warming saying:

  "The application has lost the database connection".

If I try several times to connect and execute again, then I get a message

  "FATAL: the database system is in recovery mode".

One thing that worked was to execute an update setting some field to it's own value on all rows (i.e update table set field = field).
After this I could delete all rows.

I'm actually using c# and EntityFramework 6 with the DevArt.PostgreSql plugin to control the database creation, but I generated the table script from pgAdmin to help understand the table structure. I also added one other table that has a foreign key to the table in question.

The application domain, code and database structure is written in pt-BR, so I tried to translate the table and fields names to help.
It's an application to control order requests and charges.

Table (request_event):

CREATE TABLE public.request_event
(
    id integer NOT NULL DEFAULT nextval('request_event_id_seq'::regclass),
    id_request integer NOT NULL,
    type integer NOT NULL,
    date timestamp without time zone NOT NULL,
    time_informed boolean NOT NULL,
    id_user integer,
    message text COLLATE pg_catalog."default" NOT NULL,
    visible boolean NOT NULL,
    occurrence timestamp without time zone NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone,
    CONSTRAINT request_event_pkey PRIMARY KEY (id),
    CONSTRAINT "FK_request_event_request_id_request" FOREIGN KEY (id_request) REFERENCES public.request (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "FK_request_event_user_id_user" FOREIGN KEY (id_user) REFERENCES public.user (id_person) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.request_event OWNER to postgres;

CREATE INDEX "IX_request_event_id" ON public.request_event USING btree (id) TABLESPACE pg_default;
CREATE INDEX "IX_request_event_id_request" ON public.request_event USING btree (id_request) TABLESPACE pg_default;
CREATE INDEX "IX_request_event_id_user" ON public.request_event USING btree (id_user) TABLESPACE pg_default;

Table (charge_event):

CREATE TABLE public.charge_event
(
    id integer NOT NULL,
    id_charge integer NOT NULL,
    CONSTRAINT charge_event_pkey PRIMARY KEY (id),
    CONSTRAINT "FK_charge_event_charge_id_charge" FOREIGN KEY (id_charge) REFERENCES public.charge (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "FK_charge_event_request_event_id" FOREIGN KEY (id) REFERENCES public.request_event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.charge_event OWNER to postgres;
CREATE INDEX "IX_charge_event_id" ON public.charge_event USING btree (id) TABLESPACE pg_default;
CREATE INDEX "IX_charge_event_id_charge" ON public.charge_event USING btree (id_charge) TABLESPACE pg_default;

Delete SQL (fail):

DELETE FROM charge_event;
DELETE FROM request_event;

Delete SQL (worked):

DELETE FROM charge_event;
UPDATE request_event SET type = type;
DELETE FROM request_event;

As suggested by @a_horse_with_no_name, there is one bugfix related to crashes with DELETE statements here and here.

I waited for the problem to occur again. After I updated to version 11.1, the delete statement worked as expected.


How do I design this Draft Table and select from it? I've asked the same Question before and deleted it on a different subject, This one has the same design in it

This is my Query: https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/1 with the Table Design.

I'm trying to display the drafts content using the provided Query:

SELECT t.title, p.content, p.version
FROM drafts d
INNER JOIN titles t ON t.id = d.tp_id AND d.t = 1
INNER JOIN posts p ON p.id = d.tp_id AND d.t = 2
WHERE user_id = 1

I'm trying to use INNER JOIN to prevent the not-existing titles and posts from appearing, But I got no results at all,

The result I expect is:

title     content    version
TheTitle  null       null
TheTitle  Content1   1
TheTitle  Content2   2
TheTitle  Content3   3
TheTitle  Content4   4
TheTitle  Content5   5
TheTitle  Content6   6
TheTitle  Content7   7

The first row is from the table titles which has nothing to do with content, version, And all the rows after it is from the table posts,

Also the table posts is linked with the table titles using the column tid, That How I'm supposed to get the title value in rows > 1

Is there a better approach to make this draft? And How do I select the results from it as intended?

Another Queries I tried:

https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/4

With ts (u,t) as (
SELECT d.user_id, t.title t
FROM drafts d
 JOIN titles t ON t.id = d.tp_id AND d.t = 1),
ps (u,pc,pv) as (
SELECT d.user_id u, p.content pc, p.version pv
FROM drafts d
Left JOIN posts p ON p.id = d.tp_id AND d.t = 2)
select ts.t, ps.pc, ps.pv from ts
Left outer join ps on ts.u = ps.u /*
Union
select ts.t, ps.pc, ps.pv from ps
Left outer join ts on ts.u = ps.u */
Where ts.u = 1
and ps.u = 1
Order by ts.t, ps.pc, ps.pv

See [ https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/2 ]


mysql MATCH relavancy score I thought this would be an easy question to google for; but I couldn't not find it.

Given the following query:

SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel
FROM (`phppos_items`)
WHERE MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) AND `deleted` = 0
ORDER BY `rel` DESC LIMIT 100

Is the HIGHER match score more relevant? Could someone point me to the right mysql manual page that explains this?

Based on simple observation a higher score does seem better.

MySQL Documentation Under Natural Language Full-Text Searches

Paragraph 1

  By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). *For each row in the table, **MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list***.

Paragraph 3

  When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are nonnegative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

When doing Boolean Searches, MATCH() will return 0 or 1.

NOTE TO MyISAM USERS

You can use the myisam_ftdump utility against the MyISAM table to get a complete listing of words and their frequency.

NOTE TO InnoDB USERS

There is a wonderful explanation of how InnoDB calculates Relevance in Boolean Fulltext Searching under the subheading Relevancy Rankings for InnoDB Boolean Mode Search in the MySQL Documentation (Boolean Full-Text Searches)


Will a delete then insert within a transaction cascade? I had a question about what the expected and actual behaviour for the following scenario is.

The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an id column which is reference by several other tables using foreign keys. If I set the foreign key ON DELETE action to CASCADE, obviously this will delete rows in the other tables if I issue a single DELETE FROM [table1] command on this table. However, what would happen if I were to delete all the rows, and then re-insert the same rows with the same IDs under the same transaction? Will this trigger the cascade mid-transaction, or will the foreign-key reconciliation happen once I've called commit?

Obviously, I'm thinking SQL Server here, but I'm wondering if this behaviour is consistent across other DBs as well.

In the event that the cascade is triggered even in the middle of the transaction, what would be the best way to go about managing foreign keys and relationships with a table which is completely cleared and re-loaded every day?

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.


Adding a user to MySQL with 'name'@'%' fails with ERROR 1396 I just tried to add a new user to MySQL using

CREATE USER 'name'@'%' IDENTIFIED BY '...'

However, it fails with the following error:

ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

Adding the user just for localhost works fine:

CREATE USER 'name'@'localhost' IDENTIFIED BY '...'

I have no clue what could be the problem. I'd be grateful for any ideas.

(I'm using mysql  Ver 14.14 Distrib 5.1.66.)

According to the docs if you ommit the @'hostname' (that is CREATE USER 'name') MySQL will interpret it as it had a @'%'. The error message you provided suggests that there is already a user 'name'@'%' in the system:

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE USER 'name'@'%' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'
mysql> CREATE USER 'name' IDENTIFIED BY 'test';
ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

If you delete the user and still get the message, try running FLUSH PRIVILEGES.

Also see this SO question for additional info.


How to write the following trigger I am writing a trigger that needs to raise an exception if a condition doesn't hold. That condition that should result in a raise is that the current row being modified has a field enrolled that is null, and the primary key of the row being modified does not appear in one of 6 other tables.

(I know that this is a terrible design, but this is what I've inherited, and I don't have much experience with the more advanced features of our DBMS, postgresql).

I am comfortable with my trigger, but I am having trouble writing the procedure that the trigger should execute: This is what I have so far, which admittedly is not much:

CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'User not enrolled plan'
  USING HINT = 'Ensure user is enrolled in a plan'
END;
$$ LANGUAGE plpgsql;

What I am trying to achieve in the stored procedure is something like:

enrolled_in_plan = OLD.automatic_payments || !(OLD.id in one of 6 tables)

if <> enrolled_in_plan
  raise...

end

Raising an exception is not something you do very often. However, if that's your case...

Your trigger function should probably look similar to:

CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
    IF old.enrolled is NULL THEN
        IF not
               (old.id in (select id from table1) or
                old.id in (select id from table2) or
                old.id in (select id from table3) or
                old.id in (select id from table4) or
                old.id in (select id from table5) or
                old.id in (select id from table6)    )
        THEN
            RAISE EXCEPTION 'User not enrolled plan'
            USING HINT = 'Ensure user is enrolled in a plan' ;
        END IF ;
    END IF ;

    -- If we didn't raise any exception, return the new row "as is"
    RETURN new ;
END;
$$ LANGUAGE plpgsql;

I am not sure when this trigger should be fired. I assume it should be fired BEFORE an UPDATE. I don't think it makes sense to fire it before/after an INSERT; and don't know at all if it is sensible to trigger also BEFORE DELETE.

If you only need to check this condition when UPDATING, you would have

CREATE TRIGGER check_enrolled
   BEFORE UPDATE
   ON your_table FOR EACH ROW
   EXECUTE PROCEDURE ensure_consistent_enrolled();


Using WITH together with CREATE TEMPORARY TABLE AS SELECT ... in Postgres? I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE).

e.g. something along the lines of

WITH a AS (
SELECT 1 foo, 2 bar
), b AS (
SELECT 4 bar, 5 baz
)
CREATE TEMPORARY TABLE foo AS
SELECT * from a JOIN b ON (a.bar=b.bar)

If I comment out the CREATE TEMPORARY TABLE line, this works. How to create a temporary table using the query result of the SELECT, without rewriting the temporary views into a single query?

The WITH goes after the CREATE TABLE

CREATE TEMPORARY TABLE foo
AS
WITH a AS (
SELECT 1 foo, 2 bar
), b AS (
SELECT 4 bar, 5 baz
)
SELECT * from a JOIN b ON (a.bar=b.bar)

Note the above will result in "ERROR: column "bar" specified more than once" - but I assume you are using better (distinct) column names in the real query.


CREATE TABLE AS vs SELECT INTO PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both?

  CREATE TABLE AS -- define a new table from the results of a query

  CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names).

  CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

And, then.

  SELECT INTO -- define a new table from the results of a query

  SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Without the explanation, always use CREATE TABLE AS without exception. At the bottom of each under NOTES this is cleared up,

Notes for SELECT INTO,

  CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Notes for CREATE TABLE AS,

  This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.

Also in the Compatibility section of the docs  of SELECT INTO it goes even further,

  The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECPG (see Chapter 34) and PL/pgSQL (see Chapter 41). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code.

So we have,

PostgreSQL thinks it's confusing because SELECT INTO does other stuff in contexts only available in PL/pgSQL, and ECPG.
CREATE TABLE supports more functionality (I assume they're referring to WITH OIDS, and TABLESPACE, IF NOT EXISTS).
SELECT INTO for table creation is "deprecated".

As a side note, the syntax for a CTAS with a CTE may look a bit weird., and SELECT INTO may also be some kind of hold over QUEL's RETRIEVE INTO. QUEL was the predecessor to SQL, that the predecessor to PostgreSQL (INGRES) used.


Reset the sequence of a serial PK with dynamic SQL How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this?

My goal is to do this within a DO/BEGIN block for performance reasons plus it'll help me understand plpgsql by doing something useful so I'm not going to mix this in to PHP unless I really have to.

DO $$
 DECLARE PKEY VARCHAR;
BEGIN

SELECT pg_attribute.attname INTO PKEY
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'parts1'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;

--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

  END;
$$ LANGUAGE plpgsql;

General advice

You mentioned it yourself, you just started using Postgres. Yet, you're tackling extremely advanced tasks right away, juggling system catalogs and operating with advanced dynamic SQL to automate things.

While your objectives seem reasonable, you still need to start at the basics. There is just too much to explain here. Start with (relevant parts of) the excellent manual. I provided a couple of deep links further down.

Answer

To get name(s) (and data type(s)) of columns involved in the primary key, rather use this simpler query:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary;

I updated the Postgres Wiki page where your original query seems to originate from.

However, this can return multiple rows, while you only assign a single value. Assuming you have established that we are dealing with a serial type (single column) primary key. Else use similar techniques as laid out in my previous answer to make sure.

Then use the dedicated system information function pg_get_serial_sequence() to determine the name of the used sequence, like demonstrated in my previous answer.
Per documentation:

  get name of the sequence that a serial, smallserial or bigserial column uses

DO
$do$
BEGIN
   EXECUTE (
   SELECT format($$SELECT setval('%s'::regclass, max(%I)) FROM %s$$
                 , pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
                 , a.attname
                 , a.attrelid::regclass
                )
   FROM   pg_index i
   JOIN   pg_attribute a ON a.attrelid = i.indrelid
                        AND a.attnum = i.indkey[0]
   WHERE  i.indrelid = 'tbl'::regclass
   AND    i.indisprimary
   );
END
$do$ LANGUAGE plpgsql;

This builds and executes a query of the form:

SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

Explain / Advise

This is advanced stuff and not really suitable for beginners. Messing with system catalogs can go south quickly if you don't know exactly what you are doing.
Stick to legal all-lower case identifiers in Postgres and plpgsql to make your life easier. But never rely on it in dynamic SQL, where you also need to defend against SQL injection at all times.
Making heavy use of format() to build the query string conveniently & safely.
Inside a plpgsql function you cannot call SELECT without assigning the result. You would use PERFORM instead. Details in the manual.
I removed that completely, since I reduced everything to a single EXECUTE.
Since the whole operation only makes sense for a single-column  primary key, I simplified the JOIN condition to a.attnum = i.indkey[0]
Note that pg_index.indkey has the special (internal) type int2vector. Unlike Postgres arrays its index starts with 0, not 1.
Make it a habbit to use dollar-quotes with a token around plpgsql code (including DO statements). This allows to nest simple dollar-quotes like I do in my example. Details:

Insert varchar with single quotes in PostgreSQL

You only need a single SELECT here:

SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

instead of:

SELECT setval('tbl_tbl_id_seq'::regclass, (SELECT max(tbl_id) FROM tbl));

SQL Fiddle demonstrating a few things.


SQL Server Checklists Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming before they happen (that is the plan)...

So far, I have made a start collecting scripts for the following (no order):

Daily

Check system uptime (just in case I need to check anything as a DBA)
Check the last backup
Check the transaction log backups
Check the status of SQL Jobs
Check the average CPU usage for the last 24 hours (or 1140 mins)

Weekly

Check MSDB backup history
Check to see when the last time CheckDB was run
Check index fragmentation
Check index stats (reads vs writes etc)
Check for IO bottlenecks

Monthly

Check missing indexes
Check indexes that are no longer used

Any other suggestions? (I am new to DBA so any help/advice is always welcome)

Only variation I'd suggest on your check list is to replace the word BACKUP with RESTORE. Checking that backups complete is a good start, what really matters is whether or not you can restore from them. Alert on a backup failure, automate a random sampling of restores so you know your backups are good.

The next step on from a daily/weekly/monthly check list is history. A check on x/y/z performance counters is meaningless without a baseline to compare today with yesterday. Without understanding the today vs yesterday, it's impossible to predict next month.


ORA-01034: ORACLE not available in oracle 11g I installed oracle 11g on ubuntu 14.04 and i started lister and the message is as below.

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-OCT-2014 14:20:02

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/TSDC/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TSDC)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                10-OCT-2014 14:20:02
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/TSDC/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TSDC)(PORT=1521)))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Then I started the server by oracle-xe start and it was started successfully.

When I was trying to connect to sql*plus following error was getting..

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Please suggest me what to do with this?

Run sqlplus / as sysdba then issue command startup; to start the database.

Also on Linux with this error, you can check first if Oracle background processes are up with ps aux | grep pmon. Pmon is process monitor, or check some other background process. If they are not running then the database is not running.


Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl What could be the best approach ?

Till now could figure out the following (Oracle)

Calling exp.exe and imp.exe utilities in Oracle client using Java and inturn call it from Perl.
Calling exp.exe and imp.exe utilities in Oracle client directly from Perl.
Explore more of DBI, DBD modules.
Search for some commands and execute them by connecting to respective databases

New to Perl and database(DB2, SQL Server).

Backing up in DB2 is pretty simple. You use the DB2 Backup Database command (command line). You can invoke this via scripting language if you wish (I highly recommend it). We run AIX at our shop, so we use ksh scripts that are called by cron jobs to make sure we run backups of the database.

Restores we do "manually". We still have them scripted, but we invoke them only when needed.


mysqldump: Error: 'Can't create/write to file Can't create temporary files on /var/tmp.

mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces

On my OS.

 id mysql
uid=60(mysql) gid=105(mysql) groups=105(mysql)

ls -l /var/
drwxrwxrwt  2 root root  72 oct 24 16:43 tmp

What can I do?

Perhaps you can change the owner with

chown -R mysql:mysql /var/tmp

Check to see if there is a [mysqldump] header in /etc/my.cnf. It may look like this:

[mysqldump]
tmpdir=/var/tmp

or perhaps under the [mysqld] header

[mysqldump]
tmpdir=/var/tmp

Try commenting it out and trying mysqldump again.


Get the id_user_app if not exists in id_user_rq I'm building a application and have this table for users relationships. I need to build some queries like for example:

Who invited me as a friend?
Who I invited to be my friend?
Who I accept as friend?

And need some help because for me are complex queries :(. Also I don't know if open trhead for each of the question or leave all them here in this topic, what's the best?

For the first question: I have this table structure: http://www.dropmocks.com/mBjNvF and need to get the id_user_app if not exists in id_user_rq as the title said. I try this query:

SELECT DISTINCT A.*, p.*, u.email FROM default_relations_users A
    LEFT JOIN default_relations_users B ON (A.id_user_rq = B.id_user_ap
    AND A.id_user_ap = B.id_user_rq AND A.id_user_rq = 1)
    LEFT JOIN default_users u ON (u.id = A.id_user_rq)
    LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
    GROUP BY A.id_user_rq

But it's not working, any advice or help?

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq)
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_ap IS NULL
              GROUP BY A.id_user_rq

I changed A and B to be subqueries using 1 for id_user_rq and id_user_ap

If you want the opposite, just reverse A and B subqueries to something like this:

SELECT DISTINCT A.*, p.*, u.email FROM
              (SELECT * FROM default_relation_users WHERE id_user_ap = 1) A
              LEFT JOIN
              (SELECT * FROM default_relation_users WHERE id_user_rq = 1) B
              ON (A.id_user_rq=B.id_user_ap AND B.id_user_rq=A.id_user_ap)
              LEFT JOIN default_users u ON (u.id = A.id_user_rq)
              LEFT JOIN default_profiles p ON (p.user_id = A.id_user_rq)
              WHERE B.id_user_rq IS NULL
              GROUP BY A.id_user_rq


Converting Cursor based filtering logic to CTE based I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition where I have to get the top 10 values for each iteration in cursor which I am not able to do with set based logic.

Assuming the cursor based logic being:

SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

    FETCH NEXT FROM @mycurse INTO @SomeVariable

    IF @@FETCH_STATUS <> 0
        Print 'Error in the Cursor Fetch Statement'

    WHILE @@FETCH_STATUS = 0

    BEGIN

INSERT INTO @TOP10
    SELECT Value1,Value2,Value3,Value4
        FROM (
                select Value1,Value1,Value3,Value4 from BaseTable where SomeVariable=@SomeVariable
        )

        FETCH NEXT FROM @mycurse INTO @SomeVariable

    END

CLOSE @mycurse

And the CTE based soltion looks like this:

;With myCTE(value)
as
(
    SELECT value FROM sometable
)
INSERT INTO @TOP10
    SELECT Value1,Value2,Value3,Value4
    FROM
      (select
          Value1, Value1, Value3, Value4
       from BaseTable BT, myCTE MC
       where BT.SomeVariable = MC.SomeVariable)

As you see both work fine and return identical result, with CTE version using significantly less time. But the logic of having top 10 values makes it go wrong, it returns only 10 values where it is supposed to return 20 values.

SET @mycurse = CURSOR FOR
SELECT value FROM sometable

OPEN @mycurse

    FETCH NEXT FROM @mycurse INTO @SomeVariable

    IF @@FETCH_STATUS <> 0
        Print 'Error in the Cursor Fetch Statement'

    WHILE @@FETCH_STATUS = 0

    BEGIN

INSERT INTO @TOP10
    SELECT top 10 Value1,Value2,Value3,Value4
    FROM
        (select
             Value1, Value1, Value3, Value4
         from BaseTable
         where SomeVariable = @SomeVariable)

        FETCH NEXT FROM @CUR_Liability INTO @VarLiability

    END

CLOSE @CUR_Liability

So please provide me with a solution for this scenario, it does not necessarily need to be CTE based my goal is to reduce run time. Also I can update with sample result set if this is not clear enough. Thanks in advance.

Update : I have managed to cook up a solution using Row_Number() function but I am still curious about other options.

If I understand correctly, and you want to insert 10 values of the 2nd table for every iteration of the cursor (or cte), you can use this:

; WITH myCTE (value) AS
    ( SELECT value FROM sometable )
INSERT INTO @TOP10
    SELECT t.Value1, t.Value2, t.Value3, t.Value4
    FROM myCTE AS mc
      CROSS APPLY
        ( SELECT TOP (10)
              bt.Value1, bt.Value2, bt.Value3, bt.Value4
          FROM BaseTable AS bt
          WHERE bt.SomeVariable = mc.value
          ORDER BY <SomeColumns>                     -- needs something here
        ) AS t ;

You also need an ORDER BY in the subquery with TOP to be consistent. Without order by, the database engine is free to return any 10 rows that match the conditions.

It could also be done using ROW_NUMBER():

; WITH myCTE (Value1, Value2, Value3, Value4) AS
    ( SELECT bt.Value1, bt.Value2, bt.Value3, bt.Value4,
             Rn = ROW_NUMBER() OVER (PARTITION BY bt.SomeVariable
                                     ORDER BY <SomeColumns>)
      FROM sometable AS s
        JOIN BaseTable AS bt
          ON bt.SomeVariable = s.value
    )
INSERT INTO @TOP10
    SELECT Value1, Value2, Value3, Value4
    FROM myCTE
    WHERE Rn <= 10 ;


How To Restrict a Column To Only A Default Value When I add a "default value" expression to a column, if I do not specify a value for that column during an insert, the column automatically gets its default value. But if a value is supplied during insert, that value is used. How do you restrict a column to only have a default value so that an insert statement cannot write an arbitrary value in that column?

EDIT:
The default value I have in mind is NEWID()

For this sort of specific requirement, I would use an INSTEAD OF trigger set on the table. Here's a simple example showing how this would work.

-- I chose this table structure (nullable elements) so no error would be thrown on insert,
--  and because no values are expected from the user for these columns
CREATE TABLE Bob (
    dataElement varchar(200) not null,
    LastUpdate datetime,
    UnchangeableID UniqueIdentifier
    )
GO

CREATE TRIGGER tr_MakeItHard1 ON Bob
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Bob (DataElement, LastUpdate, UnchangeableID)
    SELECT dataElement, GETDATE(), NewID()
    FROM inserted
END

GO
CREATE TRIGGER tr_MakeItHard2 on Bob
INSTEAD OF UPDATE
AS
BEGIN
    -- Here, you can either choose to allow manual updates of the LastUpdate value
    -- from the insert, or only update the data here. I chose to only allow updates
    -- here.
    Update Bob
    SET dataElement = i.dataElement,
        LastUpdate = GetDate()
    FROM inserted i
END
GO

DECLARE @uid uniqueidentifier

SELECT @uid = NewID()

INSERT INTO BOB values ('Data Element Insert', '20100101', @uid)

SELECT b.*,
    @uid as Generated
FROM Bob b

DECLARE @uid uniqueidentifier
SELECT @uid = NewID()

UPDATE Bob
SET dataElement = 'Data Element Update',
    LastUpdate = '20120101',
    UnchangeableID = @uid

SELECT b.*,
    @uid as Generated
FROM Bob b

/*
Results of the queries -- the top row is from the INSERT, the row below is from the UPDATE

dataElement             LastUpdate              UnchangeableID                          Generated
Data Element Insert     2017-01-05 08:59:12.187 7829000B-5BC8-4488-A006-DCB2933C5C95    3670E5AA-FBD4-49C0-88CF-8E2DE9F89BFC
Data Element Update     2017-01-05 09:04:57.527 7829000B-5BC8-4488-A006-DCB2933C5C95    86E9617D-F238-4E46-BE8F-85A318853593

*/


pg_dump does not honor -n I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4).

well, here's the command:

  pg_dump -f file.sql -F plain --inserts -n schema database

Ok. My command finished up really nice, but, it's not dumping everything to a schema called schema (in the example above). It's loading everything into public.

EDIT:

To make the question clearer:

The dump works fine. Upon inspection, it's dumping my schema.table, for example, to public.table. I want that to restored to schema, not public.

Any tips on solving this?

Thanks

When dumping a schema with the mentioned command, as postgres user:

pg_dump -f file.sql -F plain --inserts -n schemaname database

it produces a file whose basic structure looks like:

-- various SET commands
CREATE SCHEMA schemaname;

ALTER SCHEMA schemaname OWNER TO ownername;

SET search_path = schemaname, pg_catalog;

CREATE TABLE tablename(...) ...

ALTER TABLE schemaname.tablename OWNER TO ownername;

COPY tablename (columns...) FROM stdin;
data...
\.

The fact that search_path is set to the newly created schema implies that the CREATE TABLE and COPY statements that follow will happen in that schema and not in the public schema.

Or SET search_path would have to fail, which shouldn't happen even if the creation of the schema just above in the script failed. If schemaname does not exist, the next schema tried would be pg_catalog and CREATE TABLE would refuse to write into it, and after that there's no next schema in the search path, and in particular not public.

It's hard to imagine what mishappening could cause the above script to end up creating and populating anything in the public schema.


Upgrading to SSMS with SQL Web Edition turned off Code Outlining : How do I get it back? I upgraded to SQL Server 2008 R2 Web from Express with Advanced Services recently and code outlining went away. How do I get my collapsible code back?

Thanks

Apply the latest service packs was the answer. Applied SP1 and cumulative updates from MS. Should have known, Windows, the service pack wonder.