Find rows where integer sequence contains a given subsequence Problem

Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL.

I have a table representing sequences of integers. The definition is:

CREATE TABLE sequences
(
  id serial NOT NULL,
  title character varying(255) NOT NULL,
  date date NOT NULL,
  sequence integer[] NOT NULL,
  CONSTRAINT "PRIM_KEY_SEQUENCES" PRIMARY KEY (id)
);

My goal is to find rows using a given subsequence. That is to say, the rows where the sequence field is a sequence that contains the given subsequence (in my case, the sequence is ordered).

Example

Suppose the table contains following data:

+----+-------+------------+-------------------------------+
| id | title |    date    |           sequence            |
+----+-------+------------+-------------------------------+
|  1 | BG703 | 2004-12-24 | {1,3,17,25,377,424,242,1234}  |
|  2 | BG256 | 2005-05-11 | {5,7,12,742,225,547,2142,223} |
|  3 | BD404 | 2004-10-13 | {3,4,12,5698,526}             |
|  4 | BK956 | 2004-08-17 | {12,4,3,17,25,377,456,25}     |
+----+-------+------------+-------------------------------+

So if the given subsequence is {12, 742, 225, 547}, I want to find the row 2.

Similarly, if the given subsequence is {3, 17, 25, 377}, I want to find the row 1 and the row 4.

Finally, if the given subsequence is {12, 4, 3, 25, 377}, then there are no rows returned.

Investigations

First, I am not completely certain that represent sequences with an array data type is wise. Although this seems appropriate to the situation; I fear it makes more complicated handling. Perhaps it is better to represent the sequences differently, using a model of relations with another table.

In the same way, I think about expand the sequences using the unnest array function and then add my search criteria. Nevertheless, the number of terms in the sequence being variable I do not see how to do that.

I know it is also possible to cut my sequence in subsequence using the subarray function of intarray module but I do not see how it benefit me for my search.

Constraints

Even if at the moment my model is still being developed, the table is intended to be composed of many sequences, between 50,000 and 300,000 rows. So I have a strong performance constraint.

In my example I used relatively small integers. In practice, it is possible that these integers become much larger, up to overflow bigint. In such a situation, I think the best is to store numbers as strings (since it is not necessary to perform these sequences of mathematical operations). However, opting for this solution, this makes it impossible to use the intarray module, mentioned above.

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator.

Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script).

Datum
_int_sequence_contained(PG_FUNCTION_ARGS)
{
    return DirectFunctionCall2(_int_contains_sequence,
                               PG_GETARG_DATUM(1),
                               PG_GETARG_DATUM(0));
}

Datum
_int_contains_sequence(PG_FUNCTION_ARGS)
{
    ArrayType  *a = PG_GETARG_ARRAYTYPE_P(0);
    ArrayType  *b = PG_GETARG_ARRAYTYPE_P(1);
    int         na, nb;
    int32      *pa, *pb;
    int         i, j;

    na = ArrayGetNItems(ARR_NDIM(a), ARR_DIMS(a));
    nb = ArrayGetNItems(ARR_NDIM(b), ARR_DIMS(b));
    pa = (int32 *) ARR_DATA_PTR(a);
    pb = (int32 *) ARR_DATA_PTR(b);

    /* The naive searching algorithm. Replace it with a better one if your arrays are quite large. */
    for (i = 0; i <= na - nb; ++i)
    {
        for (j = 0; j < nb; ++j)
            if (pa[i + j] != pb[j])
                break;

        if (j == nb)
            PG_RETURN_BOOL(true);
    }

    PG_RETURN_BOOL(false);
}

CREATE FUNCTION _int_contains_sequence(_int4, _int4)
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE FUNCTION _int_sequence_contained(_int4, _int4)
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR @@> (
  LEFTARG = _int4,
  RIGHTARG = _int4,
  PROCEDURE = _int_contains_sequence,
  COMMUTATOR = '<@@',
  RESTRICT = contsel,
  JOIN = contjoinsel
);

CREATE OPERATOR <@@ (
  LEFTARG = _int4,
  RIGHTARG = _int4,
  PROCEDURE = _int_sequence_contained,
  COMMUTATOR = '@@>',
  RESTRICT = contsel,
  JOIN = contjoinsel
);

Now you can filter rows like this.

SELECT * FROM sequences WHERE sequence @@> '{12, 742, 225, 547}'

I have conducted a little experiment to find how much faster this solution is.

CREATE TEMPORARY TABLE sequences AS
SELECT array_agg((random() * 10)::int4) AS sequence, g1 AS id
FROM generate_series(1, 100000) g1
  CROSS JOIN generate_series(1, 30) g2
GROUP BY g1;

EXPLAIN ANALYZE SELECT * FROM sequences
WHERE        translate(cast(sequence as text), '{}',',,')
 LIKE '%' || translate(cast('{1,2,3,4}'as text), '{}',',,') || '%'

"Seq Scan on sequences  (cost=0.00..7869.42 rows=28 width=36) (actual time=2.487..334.318 rows=251 loops=1)"
"  Filter: (translate((sequence)::text, '{}'::text, ',,'::text) ~~ '%,1,2,3,4,%'::text)"
"  Rows Removed by Filter: 99749"
"Planning time: 0.104 ms"
"Execution time: 334.365 ms"

EXPLAIN ANALYZE SELECT * FROM sequences WHERE sequence @@> '{1,2,3,4}'

"Seq Scan on sequences  (cost=0.00..5752.01 rows=282 width=36) (actual time=0.178..20.792 rows=251 loops=1)"
"  Filter: (sequence @@> '{1,2,3,4}'::integer[])"
"  Rows Removed by Filter: 99749"
"Planning time: 0.091 ms"
"Execution time: 20.859 ms"

So, it is about 16 times faster. If it is not enough, you can add support for GIN or GiST indexes, but this will be much more difficult task.


How to disable SSMS table designer timeout? How do I disable the Table Designer timeout in SQL Server Management Studio?

Background

This KB Article seems to indicate that if you stop overriding the Designer timeout:

  To resolve this behavior, use one of the following methods:

  Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio.

it is supposed to revert to the Execution timeout:

But rather than timing out never it times out after 30 seconds:

I've also tried setting the table designer to the maximum value of 65,535. Unfortunately that just causes the timeout to happen after 18 hours, rather than 30 seconds.

How do I disable the table designer timeout in SQL Server Management Studio?

SQL Server Management Studio: 9.00.1399.00

Pre-emptive snarky comment: what kind of table change are you making that needs more than 18 hours to complete? I'm changing a column in a 130 GB table from text to nvarchar(max).

"...i'm changing a column in a 130 GB table from text to nvarchar(max)..."

Hi If you change a column and you expect that it can take considerable amount of time - I would recommend writing a Query.

Depending on you situation it could be as simple as "ALTER COLUMN" or as complex as "Creating temp table, coping all data, dropping table, creating table with new structure, copying data from temp table".

In any situation, having a script will allow you to run it without any timeout probably in you maintenance window (if you have one).

Regards,


Random selection with a "complex" subset structure This is my first question in the whole StackExchange world.
I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question.

In my application I have to make a sequence of random selection in my data.
The structure is something like this (see db<>fiddle):

4 task (e.g. Eat, Sleep, ...)
10 mood (e.g. Calm, Excited, Happy, ...)
10 place (e.g. Bedroom, Batchroom, ...)

only a few combinations between these items make sense. I decided to use a link table to summarise all suitable combinations using foreign keys from the other tables (see db<>fiddle above). I want that only the combinations present in the link table can be chosen.

The steps to perform are the following:

Pick 1 task randomly (e.g. Eat)
Retrieve all combinations that contain that task (e.g. all combination about Eat)
Pick 1 mood randomly
Retrieve all combinations from step 2 list that contain step 3 mood
Pick 1 place randomly
Find the randomly selected combination

REMARKS
Every random pick (for each level) should be performed from a list that contains "unique" items. For example if a list with more than one combination with the same mood si accepted obviously the most numerous mood are more likely to be picked up.

All this procedure is my idea to avoid that "numerosity" of valid combination for different task can influence the probability of being selected. For example, if the selection is made simply choosing one row in link table and I got 20 combination for Eat task and only 5 for Sleep task it is more probable that an Eat task will be selected.

NOTE: I'm using MariaDB 10.3 (x64)

Real question
I'm a complete beginner with DBs (as you surely noticed).
Does it make sense to perform these kind of operations "Database-side"?
I think it would be much more easier to do something like this using the application that in the end uses these data from my DB. On the contrary I started considering the idea of performing this selection entirely with database with performance purpose.

About random selection of a row I've read: Data Sampling: Techniques for Efficiently Finding a Random Row that is a rewrite of Rick James' blog Fetching Random Rows from a Table
 and several question here on DBA.

I'm trying to write my query to perform operations in a smart way. The code is unfinished, maybe 30-40% of the final mission. Having no experience with MySQL I'm discovering new features everyday so I restarted from scratch several time. At this stage I prefer that focus goes on the question above. Obviously some advice regarding best strategy for writing this kind of queries are welcome.

create sql security invoker view filtered_table as
select *
from link_table
join (
    select task_id as id
    from task
    order by rand()
    limit 1
) as random
on link_table.task_id = random.id;
select * from filtered_table;

set @min := (
    select min(subset_id)
    from filtered_table
);
set @max := (
    select max(subset_id)
    from filtered_table
);
select @min, @max;

PS. sorry for my English, I usually eat Pasta and Pizza so... ¯_(ツ)_/¯

The steps to perform are the following:

  Pick 1 task randomly (e.g. Eat)
  Retrieve all combinations that contain that task (e.g. all    combination about Eat)
  Pick 1 mood randomly from the list of combination retrieved in step 2
  Retrieve all combinations from step 2 list that contain step 3 mood
  Pick 1 place randomly from remained combination
  Find the randomly selected combination

WITH
cte1 AS (SELECT *
         FROM task
         ORDER BY RAND() LIMIT 1),
cte2 AS (SELECT mood.*
         FROM link_table
         JOIN mood USING (mood_id)
         JOIN cte1 USING (task_id)
         ORDER BY RAND() LIMIT 1)
SELECT cte1.*, cte2.*, place.*
FROM link_table
JOIN cte1 USING (task_id)
JOIN cte2 USING (mood_id)
JOIN place USING (place_id)
ORDER BY RAND() LIMIT 1

fiddle

PS. It is possible that the query will return no record.

PPS. There is more simple alternative:

WITH cte AS (SELECT *
             FROM link_table
             ORDER BY RAND() LIMIT 1)
SELECT task.*, mood.*, place.*
FROM cte
JOIN task USING (task_id)
JOIN mood USING (mood_id)
JOIN place USING (place_id)

fiddle

This variant will never return zero records if link_table is not empty.

But! the chance for each separate record to be returned differs for 1st and 2nd queries...


Confused about maximum connections in PostgreSQL I am building a web application and I using PostgreSQL as the database management system.

The following page details the maximum connections setting:

https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html.

I read about tuning on the following page

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

and it mentions

  Generally, PostgreSQL on good hardware can support a few hundred connections.

Question

I am confused about what defines a "connection". Many web sites have thousands or more users and it seems if it can only support a few hundred connections at once, then significant horizontal scaling would be required.  Are connections very short?...Meaning, if a user loads a page and it queries the database, does it open and close a connection before and after?

In a web application you use a connection pool that establishes a fixed amount of database connections that are then shared between all sessions in your web applications.

Even when a web application has thousands of concurrent users, it might only need as much as 50 database connections (if the connection pooling is done right).

  Meaning, if a user loads a page and it queries the database, does it open and close a connection before and after?

No, you shouldn't do that. Opening and closing connections is a relatively expensive operation. The connection pool will typically keep the connections open - at least for a defined amount of time. Your application takes connections from the pool and returns them to the pool.


reducing migration time of a large size postgresql database We have a postgresql 9.4 DB server that handles 2 DBs, each ~800GB. No replication is implemented yet.

We reduced pg_dump time with using -j & -Fd options to about 2 hours.

The problem is, we need to separate DBs, so we setup a fresh new server with Postgresql 9.6, but pg_restore takes a very long time (~2 days). I tried parallel pg_restore using -j (4, 8, 16, although VM has a 4 core CPU and 16GB of RAM) and tuned some parameters in posotgresql.conf like shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers and so forth, but they didn't have any significant effect on reducing time of restore.

Even, I tried to restore data and schema separately, but same results.
I think the problem is we have many tables with 15M records that all have index and indices will increase restore time but I'm not sure of that

We thought about replication, so we do not have any significant downtime (after sync is complete, we can stop old/master server and change replica/slave to acts as a master server), but as version of postgresql servers is not same, it didn't work out! Updating main server postgresql version to 9.6 and then start replication will take such a time!

Any advice and suggestions will be greatly appreciated!

Bit of a FAQ.

pg_upgrade with --link (but understand that you can't easily go back);
Logical streaming replication with pglogical
Trigger-based logical replication with Londiste etc

Search for zero or low downtime PostgreSQL major version upgrade.


Optimizing a query so MySQL will use the indexes for the joins Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL.

Wrote a query but it's very slow.  Here's the query:

  SELECT Table1.*, (long list of fields for each Table2, Table3, Table4) FROM Table1
  JOIN Table2 on creator = Table2.objectId JOIN Tabl3 on Table1.location
  = Table3.objectId JOIN Table4 on Table1.campaign = Table4.objectId WHERE Table1.campaign = 'some_object_id'

Here's the explain:

+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                         | key         | key_len | ref                                    | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | Table4         | const  | PRIMARY                               | PRIMARY     | 12      | const                                  |    1 | NULL        |
|  1 | SIMPLE      | Table2            | ALL    | PRIMARY                               | NULL        | NULL    | NULL                                   |  270 | NULL        |
|  1 | SIMPLE      | Table1 | ref    | location_idx,campaign_idx,creator_idx | creator_idx | 13      | parseObjects.Table2.objectId            |    1 | Using where |
|  1 | SIMPLE      | Table3 | eq_ref | PRIMARY                               | PRIMARY     | 12      | parseObjects.Table1.location |    1 | NULL        |
+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
4 rows in set (0.29 sec)

Table2, Table3, and Table4 all have objectId as their primary key.

Here's the profile

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000287 |
| checking permissions | 0.000011 |
| checking permissions | 0.000008 |
| checking permissions | 0.000007 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000036 |
| init                 | 0.000113 |
| System lock          | 0.000016 |
| optimizing           | 0.000023 |
| statistics           | 0.000123 |
| preparing            | 0.000030 |
| executing            | 0.000009 |
| Sending data         | 5.481633 |
| end                  | 0.000023 |
| query end            | 0.000014 |
| closing tables       | 0.000027 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000013 |
+----------------------+----------+
18 rows in set, 1 warning (0.13 sec)

This query is taking over 5.0 seconds on average, and I'm using a very small data size for testing: no table has more than 400 rows.

Here are my notices:

Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
Note that 400*270=108000, which is not a tiny number of scanned rows :)


Trying to support unicode in MySQL database I am doing a project in which I have to save tweets into my MySQL database.  The problem is some tweets contains smilies and other symbols which I am not able to save - it shows error like below:

  Incorrect string value: '\xF0\x9F\x98\x82. ...' for column 'text' at row 1

I was trying to convert to utf8mb4 using this link tutorial, and in step 5, I have changed my.ini file in my C:\ProgramData\MySQL\MySQL Server 5.6 directory. But when I do

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

in my MySQL console, I am not getting the desired result, meaning my character set is not changed to utf8mb4 - it is still utf8.

Please guide me how to resolve this, as I am new to this. I have attached an image to the step 5 (Modify connection, client, and server character sets) of the above tutorial.

You need utf8mb4 instead of utf8 to get  and a number of other emoji.

You need SET NAMES utf8mb4 after connecting.

You also need to make sure SHOW CREATE TABLE says utf8mb4.

Your "this link tutorial" seems to be broken.  I would like to see what is says.  (And perhaps find an error in it.)

More discussion of utf8mb4.


Right Full Outer Join Query I came across 1 query which is

Select * from R Natural Outer Join S

Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}.

To implement this I created 2 relations named R and S.

create table R
(
A number(5),
B number(5)
)

create table S
(
B number(5),
C number(5)
)

And I inserted the provided tuples in it.

Now while implementing this I came to know that "Natural Outer Join" is not supported by the Database tool that I am using (Oracle) so I used the following query

select *
from R
natural full outer join S

output

2 1 5
2 1 5
2 1 5
2 1 5
4 3 6
7   10

Now coming to my question

Is "Natural outer join" same as "Natural full outer join"?
How the matching of records are being done here?

Because there is no primary key defined in any of the tables I think it should do cross join and display 16 records which is not the case.

It would be very helpful if anyone can explain this behavior to me.

A quick check on Wikipedia doesn't mentioned if an "outer join" implies left, right or full when this important bit is omitted.

Practically,

"outer join" by iself isn't supported. You normally require LEFT, RIGHT or FULL
"natural" means "join on column with the same names"

This means

"Natural outer join" won't be recognised
"Natural full outer join" is "full outer join" with "natural" matching

Indexs/keys don't matter in this case and make no difference.

The result you get is correct for the standard

select *
from
   R
   full outer join
   S ON R.B = S.B

or

select *
from
   R
   full outer join
   S USING (B)

Note: not all RDBMS support all syntax:

SQL Server doesn't support NATURAL (a good thing)
MySQL doesn't support FULL OUTER JOIN (can be worked around)

Natural joins are dangerous anyway (SO links)

https://stackoverflow.com/questions/4826613/natural-join-in-sql-server/4826659#4826659
https://stackoverflow.com/questions/3277580/sql-server-lack-of-natural-join-x-join-y-usingfield
https://stackoverflow.com/questions/3063107/is-natural-join-any-better-than-select-from-where-in-terms-of-performance


xSQL trace file error I am trying to use this programme that goes by the name xSQL Profiler but the problem is that it keeps giving me a error where it says:

Trace mietrak did not start. Please check if the Trace File Path You specified for each Sql Server exists. Also check if you can connect to the Sql Server and have ALTER TRACE permissions.

This error pops up when i try to play my trace.

So what i did is the following:

I checked if the data path was correct and if it existed and it did
I logged in as a sa on the SQL management studio and clicked on the security tab then the log in tab then the login for the machine i was using witch was production\production-server and under securable i granted it access to Alter trace
after my second step failed i decided to let the program log in as a sa by choosing the SQL server authenticated mode and it still gave me the same error
I reinstalled the program twice and followed all steps precise to no victory
I then tried another profiler and it worked perfectly just to test if there is maybe some kind of SQL related issue

So now you are thinking why am i not just using the Profiler that works and that is because it does not offer all of the Features that xSQL does.

So please if any one has a solution or knows of a free profiler that is as good and offers the same features as xSQL please help.

Note that the location where the trace files are temporarily stored should be accessible from the server where the SQL Server instance you are tracing is running – SQL Server generates the trace and should be able to store the trace data in that location. xSQL Profiler will then grab those files from there and load them into the central repository.
If you still have trouble download and install the latest build from our site and then contact us at the support email so that we can help you.


How to select date of only today from datetime column in mysql I have table that have column of datetime that have value like 2017-01-13 17:01:56. Such values will have more than hundreds in a day like ordering in a restaurant. Now I want to select the orders that applied only today or date from datepicker. I don't know how to write the WHERE condition. My database is MySql and I do on c# with WPF.

Thanks.

Date format is YYYY-MM-DD

SET @dt = '2017-01-03 00:00:00';
SELECT * FROM some_table
WHERE datefield >= @dt AND datefield < (@dt + INTERVAL 1 DAY);


how can I divide log files for specific DB queries? Currently my db has a couple of triggers for some (not all) tables that get executed when a specific number of columns has been updated. I keep track if the triggers for those rows on a boolean column in the tables.

Id like to be able to log UPDATE and INSERT queries and the errors/warnings coming from them onto different files in my postgresql server as a singular log file can get quite big.

Ive thought of a couple of approaches to this problem and am looking for a better approach than these;

have the triggers (plpython3) create seperate log files when they only get called (not a lot but they insert millions of rows onto different tables.
write additional triggers that insert logging information onto another table (either by explicit means or a single JSON column)

thank you for your time and answers!

There is only a single PostgreSQL log file per database cluster, so you cannot have that out of the box.

Adding triggers that log to a file is an option, but a trigger (and the logging itself) can add significant performance overhead, certainly more than the PostgreSQL logging itself.

The route I would explore is this:

Try to perform the actions you want to log with a different database user. If that is not easy, be creative: perhaps you can execute SET ROLE/RESET ROLE commands at strategic points in debug mode to become a different user.
Enable logging for that user only:

ALTER ROLE myuser SET log_min_duration_statement = 0;

Then it will be easier to evaluate the log, because it will be smaller.


Security : SQL Server Object Access From One Schema to another schema Scenario:
I have denied permissions from table1 for USER1.
DENY SELECT,INSERT,UPDATE,DELETE ON OBJECT :: dbo.table1 TO USER1

Instead, I have allowed access to a view(vw_table1) which will selects from table1 and filter some data using where clause. The idea is, users won't be able to see entire data of the table. To support insert, update and delete, I have created instead of triggers.

The above scenario works fine when Table1 and VW_table1 are on same schema.

Now, I have a requirement to move the view only in a different schema (sec.vw_table1). After moving the view when I do "select * From sec.vw_table1" It gives access denied error when the view tries to access dbo.table1.

How can we achieve this in SQL Server? I don't want to give access to dbo.Table1 to User1. But should work when it is called from sec.vw_table1.

Any help would be highly appreciated!!

The concept you want to understand is called "ownership chaining"

As long as the owner of the object and database remain the same, SQL Server will not re-evaluate the permissions of a user after the 1st object in the chain.

In your first example you denied USER1 access to TABLE1 but allowed access to VW_TABLE1.  Since USER1 has access to VW_TABLE1 and the ownership chain has not been broken, they can see TABLE1 because permissions are not re-evaluated inside the VIEW.

In your second example I'm assuming the SEC schema must have a different owner which causes SQL Server to re-evaluate permissions when inside the VW_TABLE1 view.

In short, to fix this problem make sure all objects have the same owner


How to crosstab with a composite "row name" I have following table with id(intger), project (varchar), month (varchar), hours (integer)

CREATE TABLE foo (id,project,month,hours)
    AS VALUES
    ( 1 , 'A', 'aug', 1 ),
    ( 1 , 'A', 'sep', 3 ),
    ( 1 , 'B', 'aug', 2 ),
    ( 1 , 'B', 'sep', 5 ),
    ( 2 , 'A', 'aug', 2 ),
    ( 2 , 'A', 'sep', 4 );

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

crosstab doesn't permit composite keys. it requires rows provide a unique identifier for which to pivot them on. This means in your case (id,project) has to be serialized. You can do this with jsonb, or array or the like. Here I'm just using a text[].

SELECT id, project, aug, sep
FROM crosstab(
  $$
    SELECT ARRAY[id,project]::text[], id, project, month, hours
    FROM foo
    ORDER BY 1
  $$,
  $$
    SELECT DISTINCT month FROM foo ORDER BY 1
  $$
) AS c(rn text[], id int,project text,aug int,sep int);

 id | project | aug | sep
----+---------+-----+-----
  1 | A       |   1 |   3
  1 | B       |   2 |   5
  2 | A       |   2 |   4
(3 rows)

A couple of notes,

you should probably be storing month as a date.
you may also want to look at the MADlib pivot which does permit composite keys.

There are lots of other ways you can solve this problem too without using crosstab,

SELECT id, project, jsonb_object_agg( month, hours ) AS month_hours
FROM foo
GROUP BY id, project
ORDER BY 1,2;
 id | project |     month_hours
----+---------+----------------------
  1 | A       | {"aug": 1, "sep": 3}
  1 | B       | {"aug": 2, "sep": 5}
  2 | A       | {"aug": 2, "sep": 4}
(3 rows)


Which SQL standard is most broadly and completely supported? I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and energy on it ?

I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.

While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.

It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.

SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.


How to upgrade PostgreSQL from version 8.4 to 9.4? I want to upgrade my PostgreSQL from version 8.4 to 9.4.

The documentation is not very clear to me.

Will I lose my old databases if I do the upgrade?
How can I backup my old databases if I am to lose them after the upgrade?
How can I upgrade my psql?

My PostgreSQL is running on a CentOS 6.6 server.

This is how I solved my problem.

Upgrade Postgresql 8.4 to 9.4 in Centos

 1. Yum Install PG9.4
 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
 3. yum install pgdg-redhat94-9.4-1.noarch.rpm
 4. yum install postgresql94-server
 5. service postgresql-9.4 initdb
 6. chkconfig postgresql-9.4 on

Backup Data

 7. su - postgres

 8. pg_dumpall > dump.sql

Restore Data

 9. service postgresql stop

 10. service postgresql-9.4 start

 11. su - postgres

 12. psql < dump.sql

Config Network Access

vi /var/lib/pgsql/9.4/data/postgresql.conf

 1. listen_addresses = '*'
 2. port = 5432

/var/lib/pgsql/9.4/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident
host    all         all         130.51.79.0/24        md5
host    all         all         10.210.29.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               ident

Remove PG8.4

 1. yum remove postgresql
 2. ln -s /usr/pgsql-9.4/bin/psql /usr/local/bin/psql


Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure While looking at several examples of pl/python and pl/pgsql,  I have seen many - but not all - using volatile cost.

ie:

CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- code
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Searching for more information about volatile cost I have discovered (at first sight) that roughly at least 90% of web examples are using volatile cost 100, and sometimes somethimes volatile cost 1. (for rows it's 1000).

As I have understood, this indication helps the query plan optimizer to decide how to set priorities in short-circuit boolean operations.

Is it premature optimization if I start to give an estimate cost or rows for each of my stored procedures ? Should I only do it when I want to optimize certain query ? Is it an art for choosing the good value of cost ?

I know about the command explain, which I have have not learnt yet. Is this command helpful for estimating cost ?

VOLATILE is completely independent from COST.
The first defines function volatility and VOLATILE is the default, so it can be omitted. Alternatives are STABLE and IMMUTABLE.
The latter is only valid in combination with a COST parameter, like COST 500. The default is COST 100 which, again, can be omitted. It declares the cost per row of the result, which is used by the query planner to find the cheapest plan. If you don't know what you are doing exactly, just leave it at the default.
Read the very fine manual, starting at the chapter CREATE FUNCTION.


How can I import data from a NetCDF file into SQL Server? I'm being asked to investigate the feasibility of importing data from a NetCDF file into SQL server.

The file could be upwards of 300-400Mb and provided 4 times a day. However I'm only interested in a small percentage of the data. (Maybe as little as 3-5%). We estimate importing ~4000 rows on each run and accumulating the data.

From what I have read a NetCDF file is an XML file. Which may also include binary data.

What would be the best options for importing the data? Should I import the whole file into working table and then select what I want? Or would it be possible to parse/query the file using SQL XML features and only import the data i'm interested in.

I'd appreciate any recommendations from anyone who has first hand experience with NetCDF and what tools they used.

My solution was to create a SSIS Package, which has a C# script task which used Microsoft Scientific Data Set. (SDS)

The C# script then loops through the data and passes it back to SSIS, when then inserts it into SQL server.

Once I had the basics running I asked on other SE sites for help and my example code is there.

My data files contained Meterological data for all of New Zealand, but i was only interested in a small specific area. As the data was geospatial with reliable coordinates, my script could be more selective about what it returned, saving time and a lot of unnecesary I/O.

I am extremely grateful for the help people gave me. And I would like to be able to help others in the same way. If you have question DM me or add a comment here and I will try and help as best i can.


SQL join multiple "id" columns from a single row to another table Apologies if this has been asked but I've tried searching and haven't come across anything similar. I have a two tables in a MySQL database. Athletes and Teams

The athletes table contains the following columns:

athleteID
firstName
lastName

and the Teams table consists of:

teamID
athleteID_1
athleteID_2
athleteID_3
athleteID_4

Given a teamID, I would like to retrieve the firstName, lastName, and athleteID of all the athletes on the team.

Currently, I am doing five queries

SELECT athleteID_1, athleteID_2, athleteID_3, athleteID_4 FROM Teams WHERE teamID=3

Then using a for loop in PHP to run the following query four times (once for each athleteID:

SELECT firstName, lastName from Athletes where athleteID=$athlete_id_n

I would like to end up with a four arrays, each with two entries (firstName and lastName).

You could use a union. Fiddle

select * from (
  select t.teamID, a1.firstName, a1.LastName, 1 as AthleteNumber from teams t
  inner join athletes a1 on a1.athleteID = t.athleteID_1
  union
  select t.teamID, a2.firstName, a2.LastName, 2 as AthleteNumber from teams t
  inner join athletes a2 on a2.athleteID = t.athleteID_2
  union
  select t.teamID, a3.firstName, a3.LastName, 3 as AthleteNumber from teams t
  inner join athletes a3 on a3.athleteID = t.athleteID_3
  union
  select t.teamID, a4.firstName, a4.LastName, 4 as AthleteNumber from teams t
  inner join athletes a4 on a4.athleteID = t.athleteID_4
) s
order by teamID, AthleteNumber
;


Oracle Loop through values from one table and join with another table I have a table (table1) with fields package_id and contract_id.

Data in table 1 is like that 1 package_id can have 1 or more contract_id associated with it.

I have another table (table2) with contract_id and status. status can be ('active' or 'dead')

I need to find the package_id which doesn't have any contract_id associated with it which is 'ACTIVE' or in other words I need to find package_id which has all contract_id associated with it in 'DEAD' state.

Please help.

Two ways to do this would be with NOT EXISTS with an INNER JOIN or MINUS

INNER JOIN

SELECT *
FROM table1 t1
WHERE NOT EXISTS
(
SELECT t2.contract_id
FROM table2 t2
INNER JOIN table1 t3
ON t3.contract_id = t2.contract_id
WHERE t2.status = 'ACTIVE'
AND t3.package_id = t1.package_id
);

MINUS

SELECT *
FROM table1 t1
WHERE EXISTS
(
SELECT t2.contract_id
FROM table2 t2
WHERE t2.status = 'ACTIVE'
MINUS
SELECT t3.contract_id
FROM table1 t3
WHERE t3.package_id = t1.package_id
);

DB <> Fiddle


Query the Differential Changed Maps in SQL Server Differential backups use the Differential Changed Map pages to determine what pages to backup. I'd like to investigate a backup strategy involving differential backups. For that purpose I'd like to automatically track the amount of changed data over time for multiple databases.

I'd like to programmatically query the Differential Changed Map to determine how many pages are changed.

This seems to be possible with DBCC PAGE according to http://www.practicalsqldba.com/2013/07/sql-server-understanding-differential.html. Is there a cleaner or "more documented" way than this? It seems like this will be awkward and error-prone to implement.

SQL Server 2019 contains a new DMV sys.sys.dm_db_page_info() (docs).

  The function returns one row that contains the header information from the page.

This header information includes

diff_status         bit             Bit to indicate if diff status is changed
diff_status_desc    nvarchar(64)    Description of the diff status bit

The bit column is 0 for clean pages and 1 for dirty pages. The description is NOT CHANGED or CHANGED respectively.

Let's say we create a new table and add a row.

use Sandpit;
create table t(c int);
insert t(c) values(1);

We can find page ids in the traditional ways or using another DMV

select t.c, pr.*
from t
cross apply sys.fn_PageResCracker(%%physloc%%) as pr;

For me this happens to return file 1, page 240. Plugging this into the DMV we get

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

Which returns

diff_status diff_status_desc
----------- ----------------
1           CHANGED

A full backup should reset these flags and, sure enough, it does

backup database Sandpit to disk = N'C:\Dummy\Sandpit.bak'

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

diff_status diff_status_desc
----------- ----------------
0           NOT CHANGED


Dealing with non active transaction in log file and shrinking I have posted the first part of my question here & I don't want to flood my question with a loads of information and would force people to alter their answers for my each question.

I think I am lacking the knowledge about how logs works, so I would appreciate if anyone can redirect me to tutorial or book which can tells me deep about it. However, I am confused about

I understand that I get error because my log file was filled when SQL was either trying to run an active transaction or was trying to create checkpoints.

but I have large enough space for log file (i.e. 200 GB); so is there any way I can remove entries from log file for all transaction other than active transaction. Because at max my transaction will not exceed 10 GB at any instance.

It is recommend not to shrink log file
But then how can I ensure that my future transactions (i.e queries) will run smoothly? Does SQL Server shrink/flush/delete data from log by itself?
What is the best practice to deal with log file size?

I already have 200GB allocated for log file and I can ask for more. Isn't that enough? and if I keep increasing my drive size then some other day I will be on the same position where I am today.

To understand Transaction Log architecture you have few good articles

Understanding Logging and recovery in SQL Server
Transaction Log physical architecture

I would ask you to take some time and read these articles specially the first one.

  so is there any way I can remove entries from log file for all transaction other than active transaction. Because at max my transaction will not exceed 10 GB at any instance.

No you cannot. In simple recovery the space held by committed transaction is re-utilized in transaction log file. When transaction commits checkpoint truncates the logs and when log is truncated the space used by previous transaction will now be re-utilized by new one so log file will not have to grow instead use the space already there. The problem here is long running transaction  you must focus on it, this is holding log hostage and not allowing it to truncate there by increasing space.

  It is recommend not to shrink log file But then how can I ensure that my future transactions (i.e queries) will run smoothly? Does SQL Server shrink/flush/delete data from log by itself?

Yes its recommended not shrink log file. You need to make sure you do not run any transaction which does huge data manipulations. You can do whatever data manipulation you like but make sure amount of rows this manipulation is dealing is not too big. Log file just reacts to what you are running so you have control in it. Make sure after each data manipulation you manually run checkpoints

SQL Server will never shrink data and log files by itself for that you have DBCC SHRINKFILE command.

  What is the best practice to deal with log file size?

The article How to set Log file size will help you set optimum value. Basically you have to spend some time looking at transaction log growth and see how much it grows in a day or week. Currently as per your previous question the autogrowth and initial size of both data and log files are not good. You must refer to to this article to set correct autogrowth setting


Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled? I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it manually).

Here is what I am seeing...

but on the table itself...

Could this be why it's not happening automatically?

You are looking at the wrong place.
You have to check as below :

Using T-SQL ..
Use database_name
go
ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO;

Once done, you can check the status of the last populated datetime
-- script source : http://stackoverflow.com/a/10505496/1387418
-- Modified by Kin on Dec 14' 2015 to reflect the FTCatalogName
DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'AW2008FullTextCatalog' -- change here !

SELECT name as FTCatalogName,
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs

Insert some data ..
insert into HumanResources.JobCandidate
(BusinessEntityID, Resume, ModifiedDate)
values
    (4, NULL, DEFAULT)

Now you will see that the FT Catalog is updated.

In the logs (..\MSSQL\Log\SQLFT*)also, below will be the message...

2015-12-14 12:36:51.29 spid50s     Informational: Full-text Auto population completed for table or indexed view '[AdventureWorks2012].[HumanResources].[JobCandidate]' (table or indexed view ID '1589580701', database ID '5'). Number of documents processed: 1. Number of documents failed: 0. Number of documents that will be retried: 0.

From BOL :

By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
By default, SQL Server populates a new full-text index fully as soon as it is created. However, a full population can consume a significant amount of resources. Therefore, when creating a full-text index during peak periods, it is often a best practice to delay the full population until an off-peak time, particularly if the base table of an full-text index is large. However, the full-text catalog to which the index belongs is not usable until all of its full-text indexes are populated.

Also, refer to Improve the Performance of Full-Text Indexes


SQL Server Management Send Mail as Part of a Job I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, and SQL Server Agent will also send error emails to me, so my email profile seems to be set up correctly. The code:
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'myaccount@emailcom',
@subject = 'Bad Data Report',
@query = N' USE Database;
   DECLARE @yesterday date = getdate() - 1;  select * from Timecards WHERE Date = @yesterday AND (DailyHours > 24.1 OR DailyHours < 0 ) ',
@exclude_query_output = 1,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_attachment_filename = 'BadDataQuery.txt'
END

Following some advice from similar issues on this site, I tried looking through sysmail_allitems and sysmail_faileditems, but neither log any attempts to send. I also created a stored procedure that I had the job call as a roundabout way to send the email, but that also did not send an email nor leave an error. Any ideas what might be going on?

Here are some steps to troubleshoot the situation. After executing them you're probably be able to identify what's causing your problem. Execute one of them at a time to eliminate the possible problems:

Replace @attach_query_result_as_file = 1, for
@attach_query_result_as_file = 'Danchat',. The idea is to cause an
error to see if causes the job to fail or if it goes unnoticed too;
Clean the job step and leave only a simple mail command like this:
EXEC msdb.dbo.sp_send_dbmail    @profile_name = 'MailProfile', @recipients = 'myaccount@emailcom',     @subject = 'Test'; and try to
run the job. Don't use begin/end or anything else on the step, just a
simple code like that;
To find if the problem comes from the parameter of the @query
variable run the complete code of your question on the job, but use
this @query = N'SELECT 1;' instead of your original query. If the
email arrives, it means you only have to correct that internal query
of yours, if the email is not sent, you should start removing the
other options until the email is sent to find the culprit.
Try using this: @execute_query_database = 'YourDatabase',   @query = N'select * from Timecards WHERE Date >= ''20210418 00:00:00.000'' AND Date < ''20210419 00:00:00.000'' AND (DailyHours > 24.1 OR DailyHours < 0 )', assuming this interval contains any data. If not,
query your table for an interval that has at least one row to see if
it works properly.
Finally, if the mail is not sent only the moment you specify a table
on your query, it's possible the problem is related to privileges
(even though you are using an admin account - maybe some change in
context is happening). Try configuring an extended event session to
capture requests and you will be able to verify that.

Solution:
From chat discussion we were able to figure out the problem was in fact a permission problem and he was able to capture the actual user running the internal query following my last suggestion about an XE Session.


Minimizing downtime during MariaDB Replication Setup I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB Knowledge Base there's mention of taking a lock on all tables on the master (FLUSH TABLES WITH READ LOCK) while using mysqldump to transfer the Databases across to the slave. This step has me concerned a slight bit for the following reasons

We have a number of large databases (~20 DBs @ 40G) with some huge ones (~3 @ 100G).
It takes about ~40 minutes to transfer each DB across to the slave. In total, this entire process could take up to 20 hours (assuming good conditions).
We can't keep locks on the master for too long - as we disrupt business while the tables are locked.
Should anything fail in the process of copying the dumps across to the slave - we'll have taken the master down in vain and may need to attempt this again.

What would be a good way to go about setting up replication in this scenario with minimal downtime to production. I'm open to some out of the box thinking - is there a way to do this by breaking things down and doing them in stages so that we can verify each stage?

EDIT
I should add that we do take nightly dumps of the DBs as part of the backup strategy. Perhaps there's a way of using these to aid the process?

Use https://github.com/alestic/ec2-consistent-snapshot of your EBS volume using --mysql-master-status-file FILE option.

Create a new volume from that snapshot and attach to new instance or another instance you can use to copy to a remote location if needed.. The db server will only be locked for a couple seconds. I just performed this operation on a server with a 2TB server with millions of tables.

Start the slave db with data files from other server reset the slave and change your master with the correct log position.


When updated records get written to the real physical pages in mdf file? I'm a beginner in SQL Server, just some questions on transaction. My textbook says:

A checkpoint is a periodic operation
that forces all dirty pages for the database currently in use to be written to disk

I'm confused, does a checkpoint make all dirty pages written to log file .ldf?

Let's say we write some code:

BEGIN TRAN T1;
UPDATE table1
SET postcode = '9000'
WHERE CustomerID = 1;

...
...
COMMIT TRAN T1;

so by the time the 'commit' is executed, does the updated record in table1 still in cache and waiting for a checkpoint to make the updated record written in the log file?
After the updated record is written to the disk (log file), then how the updated record get written to the real physical pages in mdf file?

We can think of a data store as being a list of all the events that have ever happened - insert row 1; insert row 2; update row 2; delete row 1. To find a current value we simply replay all events over each other and where we end up is the answer. In current parlance this is known as event sourcing.
There are some problems with this, however. Performance is the obvious one as every time a value is required the whole log must be reprocessed.
Since history does not change once recorded an obvious optimization would be to take a snapshot at a point in time and summarize all events up to that point. Now queries can be answered from this summarization plus the much smaller set of events that have happened since the snapshot was calculated. We could even persist these summaries to avoid having to read the whole event stream on start-up.
Further, every event as it arrives can be written into this summary so no query need ever read the event stream. This, effectively, is how a database server works.
On a controlled shut-down these summaries can be persisted cleanly, ready to be read on start-up, and all values are consistent. But what of uncontrolled shutdowns, such as system crashes or loss of power? How can we handle transactions that were not yet commited when the outage occured? Some hardware is too small to hold all summaries in memory so some parts must be shuffled on and off disk. How to keep all parts consistent irrespective of their current location and transactional status?
There are several solutions to these problems. One is the Aries protocol which SQL Server follows. This separates the events from the summaries. The events go to a log file (ldf in SQL Server) and the summaries (i.e. tables) into a data file (mdf). Log records are written synchronously as SQL statements are processed. Tables are written occassionally and asynchronously as the system decides it needs to. Persisting tables to disk is called checkpointing. There is no direct relation between when a SQL statement is processed or a transaction committed and when a checkpoint is processed. Checkpoints are written by a background system task. Often we decide checkpoint frequency to meet the system's recovery time objective (RTO).
Importantly a table can be checkpointed even if it contains values written by a transaction which has not committed. Aries stores enough in the log to correctly identify and recover from this situation.
Under Aries the logical and physical states of the data are separate. Locking a row is a logical operation; the page holding that row may or may not be in memory. Committing a transaction is a logical operation; the pages touched by that transaction need not be persisted immediately, or may have been persisted already.
When data is changed the old and new values plus some metadata are written to the log file and flushed to disk. This write is synchronous. Once this is acknowledged there is a permanent record of the change which can be examined after a crash and used to bring the system back to a consistent state. Only after the log is acknowledged will the change be applied to the data page. This is why it is known as write-ahead logging.
The start and end of each transaction is also recorded in the log. Transactions can start because the client explicitly requested one (BEGIN TRANSACTION). If no explicit transaction exists the system will start its own to ensure the atomicity (the "A" in ACID) of each SQL statement. When a transaction commits only the log record recording this fact is guaranteed to be persisted. The data page holding the newly written value may either be in memory only or may have been checkpointed. It doesn't matter to Aries which is the case.
On recovery the system can read the log, find the last checkpoint, remove changes made by un-committed transactions and apply changes made by committed transactions.
For your specific example of updating a customer's postcode let's suppose there are two matching rows. The log file will end up looking a little like this (in my made-up pseudo-code).
lsn:12345; begin transaction 1;
lsn:24680; transaction 1; object: table1; row:654: column: postcode; old: '3000'; new: '9000'
lsn:35791; transaction 1; object: table1; row:357: column: postcode; old: '3002'; new: '9000'
lsn:45678; commit transaction 1;

Of course if you run a hex editor over an ldf it will look nothing like this, but it illustrates the point. "LSN" is the Log Sequence Number and is used by Aries to relate data pages to log events. Other users' log records will be interleaved with yours. I've indicated this by leaving large gaps between LSNs.
Table checkpoint log records can appear before, between or after what I've shown, or not at all. It doesn't matter to the protocol.
Aries protocol is simple in concept but has to accommodate many corner cases which makes it complex to implement. There are many on-line resources available explaining it. I like this one (YouTube) by a professor at Carnegie Mellon University and this write-up.
Recently SQL Server has introduced a new protocol called Accelerated Database Recovery. It can reduce recovery times from minutes to milliseconds.
Kudos to @DanGuzman for his work on this.


SQL Server 2017 Express edition Bulk load I am working on the SQL Server 2017. I need to import 20 text file into one table. Every text file has the same data type and column name. I have checked the data and they have in the same order also. I need to import in SQL Table and create a new column, the last column saying that

Row 1 to Row 150 data comes from "textfile-1"

, Row151 to Row300 data comes from "textfile-2"

, Row301 to Row400 data comes from "textfile-3"

We don't have any packages like SSIS.

Can we do it in Advance SQL Query? if so can someone please guide me

After creating the table you want to import the data to (TextFileImportTable) with a nullable column (FileName) on the end to store the file name, here's an example on how to import to it:
BULK
INSERT TextFileImportTable
FROM 'c:\textfile-1.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-1'
WHERE FileName IS NULL;

BULK
INSERT TextFileImportTable
FROM 'c:\textfile-2.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-2'
WHERE FileName IS NULL;

Rinse and repeat a new batch for each text file.
Note this answer assumes the columns are comma delimited and each row is new line delimited.  You can modify those attributes by changing the characters used in the FIELDTERMINATOR and ROWTERMINATOR properties, per this Microsoft Docs page.


Importing a SQL Dump File through XAMPP's Command Line A few days ago, I exported a .SQL dump file with all my databases, which is about 150MB. The thing is I cannot import it through phpMyAdmin since the file is way to big.

I remembered there was BigDump and I tried to use it, since it's ideal for such big imports as mine. The problem is that I need to set a database to import the dump file on, which is something I cannot do, since the dump file contains some databases I dumped, meaning that BigDump needs a database to be configured with so it can import the file.

I've been searching around and found out that one option could be using XAMPP's mysql.exe command line, which I already tried to, but since the import command requires a database to make the import, it makes it impossible.

Do you have any another idea or know how to make an import without the need to provide a database?

Regards and thanks in advance!

Depending on the tool and parameters used to create the .sql dump file of multiple databases, the file will normally have

CREATE DATABASE DBn...;

and

USE DBn;

statements that will allow your import to proceed without hiccups. For example, both the mysqldump command mysqldump command and phpMyAdmin's Export function for multiple database insert them.

Assuming you have exported with a sensible tool like those above, then you can import the database with a command line like this:

mysql -u username -p < dumpfile.sql

Your mysql username account needs to have appropriate privileges to, for example, create databases.

You can even run this command in a more familiar way by naming the startingDB database to use before running the commands in dumpfile.sql:

mysql -u username -p startingDB < dumpfile.sql

As the dumpfile will have a CREATE DATABASE and USE before any CREATE TABLE or INSERT statements, everything will work fine.


Interrupting query sends wrong thread ID In the MySQL command line client (Linux) when I kill a query by pressing CTRL+c this usually works fine.

mysql (slave) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 117920686" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (0.78 sec)

However when I do the same on my live master server (of all servers it must be this one...) it somehow sends the wrong thread ID.

mysql (master) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 770543254" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec) /* <- it obviously hasn't aborted at all */

The ID is way too low. Other threads have IDs like 26534760326. All servers are of the same version:

# yum list installed mysql*
Loaded plugins: dellsysid, rhnplugin, security, ulninfo
This system is receiving updates from RHN Classic or Red Hat Satellite.
Installed Packages
MySQL-python.x86_64                                             1.2.3-0.3.c1.1.el6                              @public_ol6_latest
mysql-community-client.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-common.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-libs.x86_64                                     5.6.27-2.el6                                    @mysql56-community
mysql-community-libs-compat.x86_64                              5.6.27-2.el6                                    @mysql56-community
mysql-community-release.noarch                                  el6-5                                           @/mysql-community-release-el6-5.noarch
mysql-community-server.x86_64                                   5.6.27-2.el6                                    @mysql56-community

Additional info:

The connection_id() function returns the right value however.

root@ods01:(none) > select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|     26542310314 |
+-----------------+
1 row in set (0.00 sec)

root@ods01:(none) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 772506538" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec)

Does anyone know how to fix this?

Write a bug report!  http://bugs.mysql.com.
  26542310314 MOD 2^32 = 772506538

That is, the 64-bit connection_id was chopped to 32 bits to send the KILL, thereby sending it to the wrong id.
Not many users get past 4 billion connections.  How long has your server been up?  I hope the code is not reconnecting for every query.
Change logs

----- 2014-09-25 5.7.5 Milestone 15 -- Bugs Fixed -- -----
Connection IDs are 32-bit unsigned integers, beginning at 1. When the server assigns connection IDs and reaches the top of the 32-bit range, it rolls the value over to begin at 1 again. It was possible that the server would assign a connection ID to a new thread while that ID was still in use by an existing thread, if the old thread was particularly long running. For such cases, reference to the ID becomes ambiguous. For example, it cannot reliably be determined for KILL connection_id which thread to kill, which could lead to undefined behavior. This behavior has been corrected so that in-use IDs are not reused.
Connection IDs are 32-bit unsigned integers, beginning at 1. When the server assigns connection IDs and reaches the top of the 32-bit range, it rolls the value over to begin at 1 again. It was possible that the server would assign a connection ID to a new thread while that ID was still in use by an existing thread, if the old thread was particularly long running. For such cases, reference to the ID becomes ambiguous. For example, it cannot reliably be determined for KILL connection_id which thread to kill, which could lead to undefined behavior. This behavior has been corrected so that in-use IDs are not reused.
----- 2012-12-11 5.6.9 Release Candidate -- Bugs Fixed -- Incompatible Change -----
Connection ID (thread ID) values greater than 32 bits can occur on
some systems (such as busy or long-running 64-bit systems), causing
these problems:
Connection IDs written to the general query log and slow query log
were incorrect. This was true for logging to both files and tables.
The
CONNECTION_ID()
function could return a value with a data type too small for values
larger than 32 bits.
The
mysql_thread_id()
and
mysql_kill()
C API functions did not handle ID values larger than 32 bits. This
could result in killing the wrong thread; for example, if you invoked
mysql_kill(mysql_thread_id()).
Connection IDs now are permitted to be 64-bit values when the server
supports them (when built with 64-bit data types), which has these
effects:
Connection IDs are logged correctly to the general query log and slow
query log.
Note This change involves a modification to the log tables, so after
upgrading to this release, you must run mysql_upgrade and restart the
server.
CONNECTION_ID()
returns a data type appropriate for values larger than 32 bits.
mysql_thread_id()
is unchanged; the client/server protocal has only 4 bytes for the ID
value. This function returns an incorrect (truncated) value for
connection IDs larger than 32 bits and should be avoided.
mysql_kill()
still cannot handle values larger than 32 bits, but to guard against
killing the wrong thread now returns an error in these cases:
If given an ID larger than 32 bits,
mysql_kill()
returns a
CR_INVALID_CONN_HANDLE
error.
After the server's internal thread ID counter reaches a value larger
than 32 bits, it returns an
ER_DATA_OUT_OF_RANGE
error for any
mysql_kill()
invocation and
mysql_kill()
fails.
To avoid problems with
mysql_thread_id()
and
mysql_kill(),
do not use them. To get the connection ID, execute a SELECT
CONNECTION_ID() query and retrieve the result. To kill a thread,
execute a KILL statement.
(Bug #19806, Bug #11745768, Bug #65715, Bug #14236124, Bug #44728, Bug #11753308)

(Small bug numbers can be found at bugs.mysql.com; large bug numbers are inaccessible - internal to Oracle.)
So, recommend you upgrade to 5.6.9+ or 5.7.5+.  Since you are on 5.6.27, there is a puzzle.  Maybe some detail in that lengthy Changelog says something useful.


Startup procedure for availability replica becoming primary Is there any way how to create a startup T-SQL script/procedure for starting availability replica (when state has changed from secondary to primary)?

I want to disable SSIS jobs specific for related availability groups.

Since you are talking about Availability Groups, there isn't an instance startup like you're thinking about as there is with failover cluster instances.  Because all replicas/instances are online (during normal operation of course) you can't leverage the same implementation of running a "startup procedure".

Off the top of my head, the way to do this would be one of a couple of ways.

First off, you could have a scheduled SQL Server Agent job that routinely checks to see if the local replica is currently the primary in the availability group.  And then disable whatever jobs you need to given the certain condition.  I don't recommend this method.

But my recommendation would be to have this in your existing SQL Server Agent jobs.  So for instance, in your jobs you would have logic that does the check to see if the local replica is currently primary, and if so do what the job currently does.  Something like this:

if (
    select role_desc
    from sys.dm_hadr_availability_replica_states
    where is_local = 1
) = 'primary'
    begin
        -- do your job's task
    end
else
    begin
        -- don't do anything (this 'else' block can be omitted)
        -- or if you want to handle accordingly, do so
    end

So in this example, no jobs are explicitly disabled.  They'll just do a quick conditional check to see if it should actually do something in a particular role. This is basically the way that backups are typically designed in an AG.


Delete data from a billion row table based on where clause I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field.

The table is about 800 GB in size.

What is the most efficient way to delete the data without causing adverse effects? The table is under heavy use, meaning lots of inserts and updates (which don't affect the rows to be deleted).

There is a maintenance window, which would be fastest, but I may not be able to get a big enough maintenance window. On the flip side, I can take my time with the delete, so there is no rush.

Figured a better way to do this

Insert into new_tbl select * from old_tbl where start_date < now -INTERVAL '2 years'
During downtime window:

insert into new_tbl select * from old_tbl where id not in (select id from new_tbl ) and id not in ( select id from old_tbl)

Rename old_tbl to old_tbl_drop and new_tbl to old_tbl.
Drop old_tbl_drop.


SSRS default date expression I have FROM and TO date parameters. I need to assign default values for them.

For example, if we run the report today (12 September), the default values for FROM and TO should be 10/01/2016 and 03/31/2017. as the current month (9) satisfies in the second condition below:

Based on the report run date (current date):

IF Month                       then From Date               and To Date
>=1 and <=3                    4/1/prior yr                 9/30/prior yr
>=4 and <= 9                   10/1/prior yr                3/31/curr yr
>=10 and <=12                  4/1/curr yr                  9/30/curr yr

Could you tell me the SSRS expression?

Ok, it seems I finally understood what you want.
Create a dataset date_parameters with the following query:
select case when month(getdate()) <= 3 then '4/1/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 9 then '10/1/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 12 then '4/1/' + cast (year(getdate()) as char(4))
       end as from_date,
       case when month(getdate()) <= 3 then '9/30/' + cast (year(getdate()) - 1 as char(4))
            when month(getdate()) <= 9 then '3/31/' + cast (year(getdate()) as char(4));
            when month(getdate()) <= 12 then '9/30/' + cast (year(getdate()) as char(4))
       end as to_date

Then in your default parameter values choose the option Get values from a query
Specify your date_parameters dataset and choose from_date for your FROM DATE parameter and the same dataset but to_date as value for TO DATE parameter

And here is the result:

P.S. Adjust case constants as you need (with padding zeros), only now I noticed that in the question you wrote 10/1/2017 but in the comment it became 10/01/2017. I used your constants from the question, i.e. without zeros


Postgres how to get column data types for a view I want to get meta information of a view in postgres. Specifically the column data types. Any suggestion for how to do that?

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'my_view';


ProcessID Owner with third party app I am having problems trying to audit a third party app with a SQLServer backend.

The app manages users and user access via a SQL table, using a single SQL login to access the databases.

I am trying to audit this by using the Host PID much like Windows Task Manager associates a PID and Owner for every process.

Here is what I have attempted and tried

I was able to figure out how to extract the app.exe PID.
I cannot figure out how to get the Windows Owner associated to that PID.
I have tried using xp_cmdshell to query the Windows tasklist and even wrote a .Net console app which gets called by SQL to collect the information but every time I try to extract the Owner it is blank.

Any thoughts on how I can get the Owner?

I cannot figure out how to get the Windows Owner associated to that PID.

You can get the owner using PowerShell:

# Identify the name of the remote computer you want to query
$computerName = 'SomeRemoteComputerName'
# Invoke the command on the remote system to get the information that is necessary
Invoke-Command -ComputerName $computerName -ScriptBlock {
    # Build a hashtable that associates process ids with owners
    $processOwners = @{}
    Get-WmiObject -Class Win32_Process | ForEach-Object {
        $processOwner = $_.GetOwner()
        # Combine the domain and user information together to get the process owner
        $processOwners[[int]$_.ProcessId] = $processOwner.Domain + '\' + $processOwner.User
    }
    # Now get all processes and add the owner information to them
    Get-Process | ForEach-Object {
        $processOwner = $null
        # If we have process owner information for the process, look up the owner in the table
        if ($processOwners.ContainsKey($_.Id)) {
            $processOwner = $processOwners[$_.Id]
        }
        # Add the owner information to the current process object
        Add-Member -InputObject $_ -MemberType NoteProperty -Name Owner -Value $processOwner
        # Return the current process object from the script block
        $_
    } | Select-Object Name,Owner,Description
}

script source


Always on without Listener IP I joined a new company this week where I noticed that one always on setup has listener ip configured, while the other doesn't. For the AG setup which doesn't have the listener in it, will the applications connect to the cluster ip address? Can i add the listener at this point of time? What are the HA options if Listener is not present?

First two screenshots show one AG setup without the listener

For the AG setup with the listner and the fileshare witness

Not having a listener IP means that you will get the functionality provided by a FCI (Failover Cluster Instance) rather than a fully fledged AG + FCI solution.  In effect you are asking what are the effects of using a FCI as a data source as opposed to an AG and how can I expand on it.

The FCI will require you to stop a service on one node with shared storage and wait for it to start on a different node after the resources are started on it.  This takes time.  By using this IP you will have to deal with this downtime every time you patch or fail over.

An AG has the service running at all times on all the nodes and you get to set much better priority, along with assigning read only node options to your clients connection strings.  This means faster failover, dynamic rerouting of applications with the READ_INTENT connection setting, and better management of your entire AG solution and cluster as a whole.

It is very simple to add or remove a listener so do not worry if you don't have one yet.

Edit to question:
Having a listener or not in SQL Server doesn't affect a FCI failover unless you do something with the dependencies with each other.  Outside of that a FCI will remain independent and operate on the instance level. The FCI will failover if there is a issue with the heartbeat or it's initiated, the AG solution with a listener will do so at the database level.  Thus you can have your application working off an AG listener which can work independently of the FCI depending on how the solution is designed.  We have some FCI/AG MCM's and experts here who can give you more in depth answers too.


Which version of xtrabackup to use with MySQL 5.5? I've just installed a copy of Percona xtrabackup onto my Debian (jessie) server, but when I run it it says that it is:

innobackupex version 2.3.5 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 45cda89)

But I am using MySQL 5.5. Is this the correct version of innobackupex to use with my databases, and if not which version should I use? The documentation isn't very clear.

I installed it using the following process:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

sudo pico /etc/apt/sources.list

then add the following two lines
    deb http://repo.percona.com/apt jessie main
    deb-src http://repo.percona.com/apt jessie main

apt-get update

apt-get install percona-xtrabackup

Percona XtraBackup 2.3 should be backward compatible, so you should be able to use PXB 2.3 with MySQL/Percona Server 5.5. There are some edge cases where you will need to use PXB 2.2 or even 2.1 but it is rarely needed if PXB 2.3 works.

PXB 2.4 is recommended to be used on MySQL/Percona Server 5.7.

In any case, you should always test your backups so backup using PXB 2.3 and restore it on another instance to see if everything works.


How do you migrate a database from SQL Server 2008 to MySQL? I'm just getting into MySQL, so forgive me if this is a basic question. For my application I need to migrate my database from SQL Server 2008 R2 to MySQL.

Is there any mechanism or technique or suggestion or tips or magic tricks   for migration?

You can use tools which are very efficient in migrating the data from one type of the database to other.

There are many types of tools some of them are freely available.I have used Talend which is very efficient in doing this.

It is also freely available.


EXECUTE denied, but only when I'm connected locally I created a stored procedure on a MSSQL server from my local machine via SSMS.  I see this SP in the object explorer when I expand the database 'Stored Procedures' node.  I am able to execute the SP in the normal manner.

However, if I RDP to the SQL Server, load SSMS on the server, and expand the 'Stored Procedures', I don't see the SP.  In fact, there are several stored procedures I don't see.  If I try to execute the stored procedure, I get an error message:

Msg 229, Level 14, State 5, Procedure SayHi, Line 1
The EXECUTE permission was denied on the object 'SayHi', database 'master', schema 'dbo'.

I have verified:

I am connecting to the correct server via RDP
I am connecting to the same server in both SSMS sessions
I am viewing the same database (master) in both SSMS sessions
I am logging in as the same user in both SSMS sessions

Also:

This is a procedure that I just created.  Even when executing on the server, the server knows the procedure exists - otherwise the error message would say "Could not find stored procedure".  That's why I'm confident I'm communicating with the correct server / database.  The other SP's that I'm unable to see have been around for a long time - some I created, others were already there.
I have 'Refresh'ed the tree (irrelevant to the EXECUTE denied response)
The SP is in the master database, if that makes any difference
My local SSMS is SSMS 2017, the server is SQL 2012 SP3
My colleague sees all of the SPs regardless of whether he is using SSMS on his own machine or while RDP into the server.
I have tried logging out of the server and restarting SSMS.

Any ideas why I can't see / execute the SP while logged in at the server, despite being logged in as the same user??

Your login is Windows login, so it can get its permissions from different Windows groups.

You loose some of them when logging locally, this usually happen when you launch SSMS not as administrator: you are still a member of BUILTIN\Administrators but UAC strips out your rights.

You should execute the following code in both cases: when you are logging remotely and locally, and check if in usage you see DENY ONLY when logging locally:

select distinct name, type, usage
from sys.login_token
where principal_id > 0

What to do next depends on what you see as the results.
If you see BUILTIN\Administrators DENY ONLY just run your SSMS as administrator.
If there is some other difference in the results, please post it here.


PostgreSQL 9.3, set database (or all) to read only mode Is any simple function/command for PostgreSQL 9.3 to set whole database to read only mode and then back to RW mode?
For example same as for MySQL:
FLUSH TABLES WITH READ LOCK;
LOCK TABLES customers READ;
UNLOCK TABLES;

or
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;
SET GLOBAL read_only = 0;
UNLOCK TABLES;

Thanks.

default_transaction_read_only  might work for your case.
The command below will make new sessions start their transactions in read-only mode:
ALTER DATABASE name_of_database SET default_transaction_read_only to ON;

and to go back to normal:
ALTER DATABASE name_of_database SET default_transaction_read_only to OFF;

This takes effect for new sessions, not for sessions that are already connected.
The effect can also be obtained at the user level (which might be more convenient in some cases):
ALTER USER name_of_user SET default_transaction_read_only to ON;

While in read-only mode, a session can always issue SET default_transaction_read_only to OFF; to get out of this mode, or start any individual transaction with BEGIN read write;. But in general programs don't do that, so that default_transaction_read_only is sufficient to deny them the ability to write.


Mysql setting a record as deleted or archive Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?

You basically have two choices.  You could use a view to do the filtering though this has some performance issues on MySQL, or you could use a trigger to copy deleted rows into an archive table.  I suspect that on MySQL, the trigger solution will be much better (on other DB's the tradeoffs may go other ways.


MySQL optimization question -- how to speed up a simple query I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records.

A simple query like

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE
      t.ticker='LAV05 Comdty'
   && t.whichprice='last'
   && t.date = '2005-09-01'

takes over 1.6 seconds to respond to a local query, which seems very slow. I would appreciate any suggestions for ways to speed this up.

Here is your original query

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE t.ticker='LAV05 Comdty'
AND t.whichprice='last'
t.date = '2005-09-01';

Since there is but one table in this query, all you need is a good index.

ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date);

This index should now be a permanent part of the table until you decide to remove it, which I don't think you want to do. To see the table and the indexes, just do this:

SHOW CREATE TABLE commoddb.tbcommodprices\G


SQL Server Schema Security I have a user U in a database where I want it to be able to create types and stored procs in a specified schema say UserSchema but not be able to alter schema dbo. So I created the schema and also applied the following grant statement to it:

GRANT  ALTER, Execute ON SCHEMA ::UserSchema to [U];

When I login as user U and I try create a type in UserSchema it fails with a permissions failure...

Any Ideas?

That's because you also need to grant the CREATE TYPE permission on the database to the particular principle(s).

Please see this BOL reference on CREATE TYPE:

  Requires CREATE TYPE permission in the current database and ALTER permission on schema_name.

grant create type
to [U];
go


SQL 2016 - NU/NC Index Missing From sys.partitions Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view?  There is a CI on the same table and there is a record for it in the view.  My understanding is that any NCI should have at least one record in sys.partitions.  Thanks.


Sync two SQL Server database Currently we have one SQL Server 2008 R2 database server in location X. All application users are in location X. All instances of the application can add data to database table and which may be requested by other users at the same time.

Now few of our users moved to location Y and they are facing lag in accessing data from location X database.

I'd like to create a replica of database X and put in location Y and keep them in sync. Updates in location Y would be available to location X database too and vis-versa. What is the best way to keep both databases in sync and reduce the access lag?

I am new to database management. Please feel free to suggest some articles or reading.

As you plan to update location Y, you should consider merge replication but it isn't simply a case of turning it on and you may need to make some changes to your DB or application.

You do have other options.

Reduce database chatter. Minimise the number of calls to the database to reduce the impact of latency. If you're talking between London and Hong Kong, you'll pick up 250ms of latency with each packet (round trip). Four packet would accumulate 1 second of latency, so batch your queries up and use stored procedures to reduce the overall size of your queries.
Split reads and writes. The reads could talk to the local DB (Y) and the writes sent back to the master (X). This could enable you to use AlwaysOn Availability Groups, Log Shipping or other forms of replication like Snapshot and Transactional.
WAN optimization. Most firewalls have a WAN optimiser option (usually a bolt on). This would compress the traffic, reducing the latency of larger payloads. Or look at Windows Server BranchCache.


SELECT multiple values from one table having matching record in another table in one row I have two tables as follows:

create table first (id int, name varchar(20));
create table second (id int, value char(1),fid int);

These tables have the following records:

insert into first values (1,'Ahmad'),(2,'Sami'),(3,'Khan');
insert into second values
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

I want to select those records from table second which have matching records in table first in a single row.

The desired output would be:

 id | name  | value | value | value
 ---+-------+-------+-------+-------
  1 | Ahmad |   a   |   b   |   d
  2 | Sami  |   c   |   e   |
  3 | Khan  |   f   |       |

The number of values for a single record of table first is not limited and there can be n number of values for a single record of table first.

n is unknown, but I need to repeat the column value for each value in table second which has matching record in table first. I need the whole table like above The desired result. I need only those rows referenced by table second.
The version of Postgres will be the latest one.

Actually, since

  there can be n number of values for a single record of table first.

the number of columns to return is not exactly arbitrary. There is a maximum of columns, and this has a clean solution - unless you have more columns than Postgres allows for a table:

  250 - 1600 depending on column types

BTW, typically, you would also have a third table listing all possible values of value, the whole of it implementing a classical many-to-many relationship.

How to implement a many-to-many relationship in PostgreSQL?

You can use CASE statements, or more elegantly, the crosstab() function of the additional module tablefunc. If you are unfamiliar with it, read basic instructions here first:

PostgreSQL Crosstab Query

Columns to the right of actual values are filled with NULL.
Assuming a maximum of 5 possible values and building on this setup:

CREATE TABLE first  (id int, name text);
CREATE TABLE second (id int, value "char", fid int);

INSERT INTO first  VALUES
  (1,'Ahmad')
, (2,'Sami')
, (3,'Khan')
, (4,'Nobody');  -- Added to demonstrate difference
INSERT INTO second VALUES
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

Either use crosstab(text) (1 parameter form) and join to table first another time:

SELECT id, f.name, value1, value2, value3, value4, value5
FROM   crosstab(
        'SELECT f.id, 1 AS dummy_category, s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
       ) ct (id int
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char")
JOIN first f USING (id);

Or use crosstab(text, text) (2 parameter form) and generate dummy categories for your values:

SELECT *
FROM   crosstab(
        'SELECT f.id, f.name
              , row_number() OVER (PARTITION BY f.id ORDER BY s.value) AS dummy_category
              , s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
      , ('SELECT generate_series(1,5)')
       ) ct (id int, name text
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char");

Result is the same either way:

 id | name  | value1 | value2 | value3 | value4 | value5
----+-------+--------+--------+--------+--------+--------
  1 | Ahmad | a      | b      | d      |        |
  2 | Sami  | c      | e      |        |        |
  3 | Khan  | f      |        |        |        |

If you want to include all rows from table first, make it a LEFT [OUTER] JOIN:

         ...
         LEFT JOIN second s ON s.fid = f.id
         ...

Then we get one additional result row for the above example:

  4 | Nobody |        |        |        |        |


Is there a way to see what locks a stored procedure obtains? I would like to run a stored procedure, and obtain an analysis of all the locks that it took while running. Is there a way to do that? If not automatically, then is there a resource that will explain to me how to figure it out statically?
I'm running Postgresql 9.4 but am open to solutions that only work in newer versions since we're planning to upgrade.

Stored procedures don't acquire locks. Queries do. But you can't really detect which query obtained a lock. You can only see which session/transaction is holding a lock.


Capturing datetime of change in SQL Server CDC So we've started exploring using change data capture on one of our production databases.  We'd like to know the datetime of each change.  Reading through walkthrough's and tutorials etc it seems that the standard approach is to use the LSN to relate to the cdc.lsn_time_mapping system table.  This approach works but is not very straightforward nor performant when talking about 100's of thousands of changes a day.

In a test environment I made the following adjustment to the change track tables.  I issued an ALTER TABLE statement to add a column to the end called [__ChangeDateTime] and made it's default value GetDate().  The approach seems to work, the change tracking still functions normally, the datetime's are being captured.  But mucking around with system tables makes me a little nervous.

If this isn't a system field that Microsoft added from the beginning they must of had their reasons.  Since they instead opted for the LSN to cdc.lsn_time_mapping approach am I setting myself up for problems by creating my own hack this way?

UPDATE:

Discovered during testing that GetDate() at times isn't precise enough for our needs - multiple changes sharing the same time.  Recommend using sysdatetime() and datetime2 to move the value out to the nanosecond.  Option for 2008+ only obviously.

Remember that CDC uses a log reader agent to populate the change table. Why is that important? By that mechanism, rows show up in the change tables asynchronously to the changes made in the base tables.

There are actually 3 different time points that can be recorded, in reverse chronological order:

The time the change was delivered to the change table (which is what you're recording).
The time the transaction that contained the change committed (using cdc.lsn_time_mapping).
The time with which you manually populate a column in the base table (using a default constraint, a trigger, etc.).

So the first thing is to be clear on what you want to record. Usually we would care about either #2 or #3.

If the LSN mapping mechanism (#2) isn't performing well enough for you, the only supported alternative is to add a column to the base table and populate it yourself (#3).

With regards to changing the internal tables, as a matter of policy, I think it's best to avoid hacking around with internals when there are supported alternatives. The very last thing you want is an important production system going down, needing to call Product Support, and being denied service because of something like this. Never mind the issues of it potentially breaking things (upgrades), or being broken because it was unexpected (turn CDC off, then on again, as mentioned in the other answer).


How can I determine if a table exists in the current search_path with PLPGSQL? I'm writing a setup script for an application that's an addon for another application, so I want to check if the tables for the other application exist.  If not, I want to give the user a useful error.  However, I don't know what schema will be holding the tables.

DO LANGUAGE plpgsql $$
BEGIN
    PERFORM 1
    FROM
        pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE
        n.nspname = current_setting('search_path')
        AND c.relname = 'foo'
        AND c.relkind = 'r'; -- not sure if I actually need this or not...

    IF NOT FOUND THEN
        RAISE 'This application depends on tables created by another application';
    END IF;
END;
$$;

However, current_setting('search_path') returns a TEXT containing "$user",public by default, which isn't terribly useful.

The only other thing I can think of is to try selecting from the table and catch the exception.  It would do the job, but I don't think it is very elegant and I've read that it is expensive to use (though maybe that would be ok in this scenario since I'm only running it once?).

Quick and dirty

In Postgres 9.4+ use

SELECT to_regclass('foo');

Returns NULL if the identifier is not found in the search path.
In Postgres 9.3 or older use a cast to regclass:

SELECT 'foo'::regclass;

This raises an exception, if the object is not found!

If 'foo' is found, the oid is returned in its text representation. That's just the table name, schema-qualified according to the current search path and double-quoted where necessary.

If the object is not found you can be sure it does not exist anywhere in the search path - or not at all for a schema-qualified name (schema.foo).

If it's found there are two shortcomings:

The search includes implicit schemas of the search_path, namely pg_catalog and pg_temp. But you may want to exclude temp and system tables for your purpose. (?)
A cast to regclass works for all objects in the system catalog pg_class: indexes, views, sequences etc. Not just tables. You seem to be looking for a regular table exclusively. However, you'll probably have problems with other objects of the same name, too. Details:

How to check if a table exists in a given schema

Slow and sure

We are back to your query, but don't use current_setting('search_path'), which returns the bare setting. Use the dedicated system information function current_schemas(). Per documentation:

  current_schemas(boolean) name[]
  names of schemas in search path, optionally including implicit schemas

"$user" in the search path is resolved smartly. If no schema with the name of SESSION_USER exists, the schema is not returned to begin with. Also, depending on what you want exactly, you can additionally output implicit schemas (pg_catalog and possibly pg_temp) - but I assume you don't want those for the case at hand, so use:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT  -- list can be empty
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = ANY(current_schemas(FALSE))
      AND    n.nspname NOT LIKE 'pg_%'  -- exclude system schemas!
      AND    c.relname = 'foo'
      AND    c.relkind = 'r')           -- you probably need this
   THEN
      RAISE 'This application depends on tables created by another application';
   END IF;
END
$do$;

SQL Fiddle, demonstrating all except for the last DO statement.
SQL Fiddle (JDBC) has problems with DO statements containing termination characters.


single query using OR operator vs multiple queries using equals operator Case 1 - Single query using OR operator in where clause:

select * from users where name='smith' or nick='smith' (using index_merge )

Case 2 - multiple queries using equals operator in where clause:

select * from users where name='smith'; (using single index);

select * from users where nick='smith'; (using single index);

Q: Is there any performance loss in Case 2?

It's a common optimization to use UNION in these cases (at least when you must use a single query):

select * from users where name='smith' /* using single index */
UNION
select * from users where nick='smith'; /* using single index */

In my experience, I don't like to rely on index_merge (union) because the performance is usually not as good as doing the explicit UNION trick like above.

But each case could be a little bit different, because the performance may depend on how many rows are matched by each condition.

@jynus is correct in his comment below that the issue is about a temporary table created for the UNION.

I also want to add that it's hard to make general rules for what type of query is faster. So much depends on how much data you're querying, the version of the software you use, your operating system and tuning, and how many queries per second are running concurrently.

The only reliable way to answer these sorts of questions is to test several solutions yourself, on your server, against your data.


Oracle Client vs Oracle Instant Client on Linux! to have the following setup on a Linux box:

SQLPlus
client library (oci.dll)!!!!
ORACLE_HOME is set for users

is Oracle Instant Client enough or I need to have the Oracle Client installed on the box.

If you only need to run SQLPlus, grab "Instant Client Package - Basic" and "Instant Client Package - SQL Plus: Additional libraries and executable for running SQL Plus with Instant Client" from http://www.oracle.com/technetwork/topics/linuxsoft-082809.html & install them.

oci.dll is a windows file, so I have no idea what could be achieved by installing that on Linux.

unzip the files into a location of your choice & set LD_LIBRARY_PATH and PATH to point to that directory. If you need any further help after that, edit your question.