texts: dividing quatity INT value into unique separate rows for each item

texts:
I am working on a project where we are selling tickets. How do I create multiple rows from a quantity value. Currently it looks like this

line_item_id | order_id | product_id | quantity
------------------------------------------------
      1      |     1    |      6     |    5
------------------------------------------------

I wish for my end result to look like this into a separate table

ticket_id    | customer_id | product_id | quantity
------------------------------------------------
      1      |     2       |      6     |    1
------------------------------------------------
      2      |     2       |      6     |    1
------------------------------------------------
      3      |     2       |      6     |    1
------------------------------------------------
      4      |     2       |      6     |    1
------------------------------------------------
      5      |     2       |      6     |    1
------------------------------------------------

I am using a stored procedure but every time I call it it does what I want but it also duplicates the previous data into the table as well as adding the new tickets.

Also here is the stored procedure. Maybe I'm doing something wrong

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_item_import`()
BEGIN
    # Declare variables to read records from the cursor
    DECLARE parent_id_val INT(10) UNSIGNED;
    DECLARE item_type_val VARCHAR(255);
    DECLARE quantity_val INT(11);

    # Declare variables for cursor and loop control
    DECLARE no_more_rows BOOLEAN;
    DECLARE item_qty INT DEFAULT 0;

    # Declare the cursor
    DECLARE item_cur CURSOR FOR
        SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            JOIN merchandise m ON m.MerchID = oi.MerchID
                WHERE o.customer_id = 2 AND
                MerchCatID = '7' OR MerchCatID = '8';

    # Declare handlers for exceptions
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    # Open the cursor and loop through results
    OPEN item_cur;

    input_loop: LOOP

        FETCH item_cur
        INTO parent_id_val, item_type_val, quantity_val;

        # Break out of the loop if there were no records or all have been processed
        IF no_more_rows THEN
            CLOSE item_cur;
            LEAVE input_loop;
        END IF;

        SET item_qty = 0;

        qty_loop: LOOP

            INSERT INTO items_new
                (itemID, itemDesc)
            SELECT
                parent_id_val, item_type_val;

            SET item_qty = item_qty + 1;

            IF item_qty >= quantity_val THEN
                LEAVE qty_loop;
            END IF;

        END LOOP qty_loop;

    END LOOP input_loop;
END$$
DELIMITER ;


texts: Writing audit trail funtionality in postgres, can I use pl/V8 instead of pl/pgSQL?

texts:
I'm using postgres 9.4 on Amazon RDS.

This the fast and loose "application developer" definition of an audit trail.
The audit trail isn't for security nor is it a legal requirement - I don't even plan on putting it in a different schema.
It's purely for visibility of "who did what, when" so I can answer questions from the business; where our generic for "created by / last updated by" columns won't quite cut it.

The application is a standard multi-tier JVM-based line-of-business app.  It's never going to need to hit any kind of real scale.
Our requirements could probably be satisfied by writing interceptor-style functionality in the application tier.  Been there, done that, wasn't very hard - but it has its downsides and I want to do it in the database this time.
Initially, there's not even any plans to build any user-level query functionality, when we need to figure out what happened to some piece of data we'll do it by hand - maybe even offline.

I'm thinking triggers that dump the changes into a jsonb column.  Basically port https://wiki.postgresql.org/wiki/Audit_trigger_91plus to pl/V8 with jsonb, plus my own unique brand of weirdness thrown in for flavour.
I know pg_audit is coming, but it won't make it in time for what I'm doing.  Besides, it appears that it will have a bunch of complexity to satisfy a host of requirements that I'm not interested in.

My understanding is that RDS offers pl/V8 by default, so would it be a terrible idea to do this in V8?
I'm looking here for concrete reasons that would make doing this in V8 objectively inferior to pgSQL.


texts: MySQL: execute an "UPDATE" only if an specific row exists

texts:
I want to execute an UPDATE clause only in the case a specific row exists. Specifically, if a row in gallery_image exists with a specified position, I want to update all rows with a position higher than that value, flipping the sign of the position value.

So this is my try:

IF SELECT * FROM gallery_image WHERE position = 'X'
UPDATE gallery_image SET position = position * -1

but I get an error.

EDIT: After the first comment after my question I have tried this:

update gallery_image if ( EXISTS ( select * from gallery_image position = 8 )) set position = position * -1

but it says

  An alias was found before. (near "(" at position 24)"


texts: mysql join many to one table and display each 'many' record as an additional column

texts:
I have a many to one table.

For example:

user_id, name
1, chris
2, john

and

user_id, item
1, apple,
1, banana,
2, orange

How could I run a query that returns something like this:

user_id, item_1, item_2, item_3
1, apple, banana, null
2, orange, null, null

Would I need to do a sub query for each item_n column?


texts: How to use internal variables in Report Builder Textboxes

texts:
I have a query in my dataset that is calculating a date range:

DECLARE @StartDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 1, 0)
DECLARE @EndDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)

I've setup the parameters as an internal value with no prompt.

I'm trying to use these variables/parameters in a report title:

"Resource Activity Between Two Statuses - " & Parameters!StartDate.Value & " - " & Parameters!EndDate.Value

...and I'm getting an error.  Unsure if I'm stepping outside of the design of what SSRS can do, or if I'm going about it the wrong way, but the end result should be obvious - I'm wanting to show the date range in the title of the report.

  The report parameter ‘StartDate’ has a DefaultValue or a ValidValue that depends on the report parameter “StartDate”. Forward dependencies are not valid.


texts: PostgreSQL's record type returning null?

texts:
I don't understand why this result is possible. Can someone explain why the first assert returns true and the second false? generate_token() is a function that generates a random bytea and inserts it into a certain table, then the inserted row is returned. The function itself is working fine. Running through a .pgsql script.
DO $$
DECLARE
    test record;
BEGIN

select * into test from generate_token('test');

--returns true
assert test.token IS NOT NULL;
--returns false, which breaks.
assert test IS NOT NULL;
END;$$;


texts: Should I believe Query Store runtime stats?

texts:
There is this weird behavior of Query Store runtime stats that I observe at many of my servers with different scenarios that makes me not to believe these stats. Or am I wrong in something?

E.g. trivial query like this with a given query plan:

Stats (sys.query_store_runtime_stats) reports absolutely insane number of max_logical_io_reads at some accidental interval:

But table has only 27 pages allocated altogether!

I am experiencing this phenomenon with different queries cross many environments. And it's ruining my regressed query analysis.

Queries with different numbers of reads don't have different plans.

The heap table shown only updates and inserts rarely. It's just coincident I used a heap as an example. I experience this with clustered tables too.


texts: Database index creation multiple criterias

texts:
Context:
Designing a B-tree database.
I have a table with say 12 columns (A,B,C..). Those columns can be varchar or int in any order (in case of multiple tables).
When running a query any of those columns can be constrained to a value, but not necessarily all.
And I cannot statistically predict which ones will be set. The values in columns are random unpredictable/cause fragmentation.
If the dataset is big enough (multiple gigas and up) to be willing to avoid linear scanning, what would be the best indexing strategy here?
Strategies:
1.should I create a table for each possibilities (in fact creating a composite index per table) and update it on alteration? (carefully updating other related tables as well)
2.should I keep one table but multiple composite indexes, with a null option in the tree to jump to the next tree? (this has my preference)
3.?
Clarifications:
Insert/update are not time constrained. Search has to be as fast as possible.
Again, not interested in sql queries, just your opinion/experience.
Thanks


texts: How can I generate a row_number without using a window function?

texts:
In PostgreSQL, how do you generate a row number:

WITHOUT a Window Function (like row_number())
WITHOUT a Temp Table
Only using a single SELECT

Here is some sample data to play with,

CREATE TEMP TABLE foo AS
SELECT * FROM ( VALUES
  ('wgates', 'Gates', 'William' ),
  ('wgrant', 'Grant', 'Wallace' ),
  ('jjones', 'Jones', 'John' ),
  ('psmith', 'Smith', 'Paul' )
) AS t(name_id, last_name, first_name);

The desired output would be:

 row_number │ name_id │ last_name │ first_name
────────────┼─────────┼───────────┼────────────
          1 │ wgates  │ Gates     │ William
          2 │ wgrant  │ Grant     │ Wallace
          3 │ jjones  │ Jones     │ John
          4 │ psmith  │ Smith     │ Paul

Some of these methods can get tricky. Please explain your answers. I can also imagine two categories of answers that work:

data with a UNIQUE or PRIMARY KEY (we can still use name_id here)
nothing UNIQUE at all.

All features are on the table for the most recent version of PostgreSQL.

Ultimately, I need a unique key on a table that has no ID so I can update it against a cross-join of itself. I am also asking out of simple curiosity.


texts: Group by function not working correctly in SQL server CTE

texts:
This is SQL Server CTE,

;with grp (Sdate,TransactionType,tot)
as

(select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type],
    (count ([Transaction_type])) as [Total Count]
    from TBL_AGENT_TRANSACTIONS
    where cast(CallStartTime as DATE)>='2015-04-27' and cast(CallEndTime as Date)<='2015-04-27'  and Transaction_type in ('Debit_Card')
    group by CallStartTime, Transaction_type)

select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount from grp group by TransactionType,grp.Sdate,grp.tot

Here I want to group totalcount based on Sdate and Transaction Type.
But it is not working correctly. I have 3 records with same Sdate, Transaction Type and I need to show totalcount as 3 as a single row. But i'm getting two rows as below.

Can any one help me to fix this minor issue?

Update:

I got the exact problem,

In my table 'TBL_AGENT_TRANSACTIONS', data type of the column 'Transaction_type' is varchar but I need to find the sum of that Transaction_type based on the Sdate and Transaction_type. If I put sum(Transaction_type) in the first select query, it's showing datatype covertion failed error so I'm finding the count(Transaction_type) in the first select query and getting sum of that field in the second select query. I guess my problem is in this process only.


texts: Load From S3 MySQL Error Code 2013 -- AWS Aurora

texts:
I am attempting to load S3 files to an Aurora MySQL DB Cluster using the below AWS documentation:

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html

LOAD DATA FROM S3 PREFIX '://'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Everything works fine and looks good in MySQL when I run a test on 1-2 csv files in my S3 bucket, but when I try loading 2,000+ csv files from my S3 bucket, after exactly 7,200 seconds the query fails and I get the error "MySQL Error 2013 Lost connection to MySQL server during query" (this has happened multiple times now).

MySQL settings are set as such:

I am using MySQL Workbench, MySQL Version 8.0.15, and engine InnoDB.

Any ideas why this keeps happening? Any guidance or help would be extremely appreciated!!


texts:
I have a Table with 3 rows. Player, Time, Server.

Now, I want 2 things and I don't know how.

For Server there are 3 worths available. 1, 2 and 3. Server and Player are a Primary Key together. User is a VARCHAR, Server and Time are Strings. I use 1,2 and 3 to make i easier to understand.

1st I want to sort the table by time and get the position of a specific player, but only from 1 Server.

Example:

User Time Server
2    10   1
1    50   1
2    60   3
1    20   2
1    100  3
2    70   2

Now, want to get which position the user 2 has

If I sort by time and server = 1

user 2 would be number 2
user 1 number 1

If server = 2

user 2 would be 1
user 1 would be 2

Then I want to get the User position, if I sort by the sum of the time of a user.
In this case, user 1 would be 1 (Sum of time: 170) and user 2 would be 2 (Sum of time:100).

I hope you can help me!
BTW, I want to use this in Java ;)

~java4ever/Robin


texts: How can I count the total number of document indexed by my oracle.text index?

texts:
I know I can use ctx_query.count_hits to count the number of document matching a query,
and similarly, I can use

count(*) where CONTAINS(...)

But these are only options if I actually have a query. Those functions throw an exception if I pass an empty string as a query (thanks oracle...). So,

  Is there a way to count the total number of document indexed by my
  oracle.text index?


texts: Using an enum as part of an index definition

texts:
When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that.
So my question is what are the subtle issues when an ENUM is part of an index definition? Is it an antipattern or bad idea?


texts: Uncertainty about number format in MySQL

texts:
In Oracle Database, fields like this:

      example number(6,4) not null;

Indicate that I'm reserving 6 digits for this column, 4 that are being used from the point to the right and 2 for the point to the left.

Is this the same in MySQL?

Thanks!!! I know this is basic but I have some time working on databases and always this question...


texts: Range Optimizer in Mysql: What is an Index dive during Query Optimization?

texts:
On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer).

Quoting his point,

  For as long as there have been a range access method in MySQL, the number of rows in a range has been estimated by diving down the index to find the start and end of the range and use these to count the number of rows between them. This technique is accurate and is, therefore, a good basis to make the best possible execution plan (QEP) for the query.

I have tested the same and they were accurate in my tests too.
I have a few questions,

What does it exactly mean, "to find the start and end of the range"? Does this mean
getting the start page and end page for range in the B-tree.
If my first question is Yes, then how this technique will be accurate,i.e.,
 what about the pages.
in the middle of the range.


texts: Does a Levenshtein distance involve some computation for each single row?

texts:
Let's say I have, in a SQL DB, a table with 2 columns: id integer, description text and 100,000 rows. The column description has always < 20 characters.

If I want to:

SELECT * FROM mytable WHERE levenshtein_dist(description, "hello world") < 7

does this require to compute the distance for each of the 100,000 rows (heavy computation!) for any implementation of Levenshtein string similarity distance, because this is inherent to the method

or

is there some clever implementation or clever technique (involving an index maybe?) that can avoid to have to loop over each single row of the table?

Context: I wanted to know if MySQL (or even sqlite) had an efficient mean to find string similarity. FTS (Full text search) is one but unfortunately (it's for sqlite here, but it might be the same for MySql?):

  SQLite's FTS engine is based on tokens - keywords that the search engine tries to match.
  A variety of tokenizers are available, but they are relatively simple. The "simple" tokenizer simply splits up each word and lowercases it: for example, in the string "The quick brown fox jumps over the lazy dog", the word "jumps" would match, but not "jump". The "porter" tokenizer is a bit more advanced, stripping the conjugations of words, so that "jumps" and "jumping" would match, but a typo like "jmups" would not.

The latter (the fact that "jmups" cannot be found as similar to "jumps") makes it unpractical for my use case, sadly.


texts: Design of small tables: clustered index or heap

texts:
I'm trying to design a database schema to hold an entity that has a couple of one-to-many associated collections.

The challenge here is that the related objects have few properties, and so the tables are "small" (at least in terms of amount of columns). This makes it difficult to make a decision on two fronts:

Do I have a primary key or not
Do I create a clustered index, or make it a heap + have a covering non-clustered index

This is my situation (note - actual entity is replaced with a generic example):

-----------------------             --------------------------
|         User        |             |      UserSituation     |
-----------------------         =>  --------------------------
| + UserId [NOT NULL] |             | + UserId [NOT NULL]    |
-----------------------             | + StartDate [NOT NULL] |
                                    | + EndDate [NULL]       |
                                    | + IsActive [NOT NULL]  |
                                    --------------------------

                                    --------------------------
                                    |      UserCategory      |
                                =>  --------------------------
                                    | + UserId [NOT NULL]    |
                                    | + StartDate [NOT NULL] |
                                    | + EndDate [NULL]       |
                                    | + Category [NOT NULL]  |
                                    --------------------------

Again, I've generalised the example, please don't try to give me advice to re-design the database structure by trying to derive any business meaning behind the fields.

To design the tables, I've reasoned about the following, which is valid for both tables:

A primary key cannot exist because it needs to cover all columns and EndDate is nullable. Still, if that was not the case, would it make sense to have a primary key which is so wide?
A covering non-clustered index can use the RID to lookup table data. This will make leaf nodes smaller, because the alternative is having "UserId + Uniquefier" in all entries. How much does that matter?
A clustered index on UserId -> this means a Uniquefier will be created, so I can't seem to decide whether that would be good or bad.
In any case I have a UNIQUE NONCLUSTERED index over all columns.

I've read Kimberly Tripp's articles on the relevant topics but also stumbled across Marcus Winand's blog post about clustered indexes. He has a rather opposing vision about clustered index than the majority of people, but he seems to make sense all the same.

Other notes:

The data in the tables will not be updated, only deleted/inserted because my app treats the collection entries as immutable.
I'm using SQL Server, and need to support 2008 R2 and up.
The sub-tables will not really be queried, mostly joined to the main entity.
Having read a substantial amount of information on indexes and table design, both here and on blogs, I can't decide on this particular scenario.


texts:
We are migrating to SQL server 2012 to PDW.
Any SQL script for Comparing the data between SQL server 2012 and Parallel dataware house or any tools to compare.


texts: How to Create Live connection between Amazon Redshift and Amazon RDS (MySQL)

texts:
I have a transaction table in MySQL which get updated almost every second. The table has about 20 million records and counting. I have been running queries directly on this table in my app development which has been having serious impact on performance especially when I have to call some queries using aggregate functions.

I want to connect the MYSQL to Amazon Redshift.

The problem is I want my connection to Redshift to be live. ie. Get Redshift updated with every insert in my tales in mysql.

How do I create the live connection to Amazon Redshift?
Is there any other way I can manage data without using Amazon Redshift?


texts: Error on import of mysqldump file - Illegal double value found during parsing

texts:
I am currently getting the following error while trying to import / restore a mysqldump file:

ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308'
value found during parsing

I looked at various places online and could not find anything that was similar.

Some background:

I am trying to create a new replication box and the mysqldump is new from another replica box. Both boxes are:

  CentOS 5.7 with MySQL 5.0.77-4

Used the following to create the mysqldump:

$ mysqldump --lock-all-tables --all-databases | gzip -9 > mysqldump.sql.gz

The new replication box has nothing on there right now and all the databases / tables are supposed to be created by mysqldump. Any insights?

Thanks!


texts: Use stored procedure to get minimum value from column and decrement it and other values

texts:
I'm trying to understand how the following algorithm could be completed in SQL 2008 R2.

I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders are assigned a value, a sequence number, to indicate the order that they will be completed. As they are completed, the order is removed. This is all well and good, except, you suddenly end up with the lowest value being over 5660. Additionally, there are different machines for different orders, with different order assignments for each machine.

For example, for one machine,

Order No.

1

2

3

4

5

Then, orders 1, 2, 3, and 4 are completed, and new orders are added on, so you have:

5

6

7

8

What I need is the following.

Once I am at the point where the lowest number in the table is NOT 1 (even if it is only 2), take that minimum value and subtract it from every number so that everything is lowered proportionately.
Based on our previous example, the output would be:

5 -> 1

6 -> 2

7 -> 3

8 -> 4

I understand the process that needs to happen, I am just unsure of how to translate that into SQL, considering that there are multiple machines.

I know that I could easily do this with a cursor, but I would prefer not to. Ideally, some type of nested statement would be best.

For example, some type of statement (pseudo code, not correct at all)

UPDATE xTable
SET xNumber = (xNumber - (SELECT MIN(xNumber) FROM xTable))
WHERE xMachine = xTable.xMachine (??)

Any type of insight that you could offer would be appreciated.


texts: Permission Denied on shared object (.so) file postgresql

texts:
I'm trying to import a C-Language function to my postgresql database.

I have my shared object inside my home directory but I can't use it on my database.

CREATE OR REPLACE FUNCTION add_one(integer) RETURNS integer AS '/home/pedro/.../.../example.so', 'add_one' LANGUAGE C STRICT;

And this is my error message:

ERROR:  could not access file "/home/pedro/.../.../example.so": Permission denied

I think is probably my OS blocking access to this file but I don't want to give permission to all users.

tl;dr: I'm looking for a elegant solution to import my C-Language Function, as a Shared Object file, inside my postgresql database.

Thanks in advance!


texts: Availability Group and Transfer SQL Server Objects SSIS task

texts:
We have two db instances on Always On Availability Group.

And I need to transfer data from selection of tables to the primary DB.

I have created SQL Agent Job for SSIS Transfer SQL Server Objects for set of tables.

It was working very well except that after some number of executions the job crashes with non informative communicate: "...The package execution failed.  The step failed." Once the target server crashed, and also the AG configuration had to be readded. And second time the target DB is freezed on "Synchronizing" state and all subsequent runs fail.

Is this recommended configuration?
How can I trouble shoot it? I dodn't find anything in event viewer nor in Management Studio. Perhaps I'm looking in the wrong place?

Thank you for help.


texts: Can I query a tab-delimited file from SSMS?

texts:
Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere?

I know you can BULK INSERT from a tab-delimited file using something like:

BULK INSERT SomeTable
FROM 'MyFile.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

however that requires you know the columns in advance and create a table to hold the data.

I also know you can query some other file types such as CSV or Excel without defining the columns in advance using OPENROWSET and the Excel drivers, such as:

-- Query CSV
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=\\Server\Folder\;HDR=Yes;',
    'SELECT * FROM MyFile.csv')

-- Query Excel
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
    'Excel 8.0;Database=MyFile.xls',
    'SELECT * FROM [Sheet1$]')

Also, if I change the registry key Format under HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text from CSVDelimited to TabDelimited on the SQL Server, the CSV query above will correctly read a tab-delimited text file, however it will no longer read a comma-delimited text file so I don't think I want to leave it like that.

Attempting to use Format=TabDelimited in the OPENROWSET does not work either

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=\\Server\Folder\;HDR=Yes;Format=TabDelimited',
    'SELECT * FROM MyFile.txt')

I have made some attempts to copy the Text registry keys from both the Engines and ISAM Formats keys to something custom that defaults to TabDelimited, however it is still reading files with CSVFormat instead of TabDelimited format so I must be missing something here.

Is there a way to query a tab-delimited file to view its contents without having to create a table and BULK INSERT it?

I am using SQL Server 2005


texts:
I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is:

select * from Table_Name
order by random()
limit 10;

Another faster method is:

select * from Table_Name
WHERE random() <= 0.01
order by random()
limit 10;

(Although that 0.01 depends on the table size and the sample size; this is just an example.)

In both of these queries a random number is generated for each row and sorted based on those random generated numbers. Then in the sorted numbers the first 10 are selected as the final result, so I think these should be sampling without replacement.

Now what I want to do is to somehow turn this sampling methods into sampling with replacement. How is that possible? Or is there any other random sampling method with replacement in PostgreSQL?

I have to say that I do have an idea how this might be possible but I don't know how to implement it in Postgres. Here is my idea:

If instead of generating one random value we generate S random values where S is the sample size, then order all of the random generated values, it will be sampling with replacement. (I don't know if I am right.)
At this point I don't mind about the performance of the query.


texts: There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?


texts: select table and replace column fields by value from multiple tables in mysql

texts:
I am not more familiar with MySQL.i have been working in mssql. currently, we have a requirement to select table column fields replaced by values from multiple tables.
I tried out with some queries but not getting the proper result since it has some inner options.
please refer to the fiddle.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=15c6f66c363ab16a3437a3911c266bde

Please advise me on this

Thanks in advance

EDIT:
The code (and text) from dbfille:

1:

CREATE TABLE TableData_400
 (id INT,
 created_date DATETIME,
 sample_1 TEXT,
 sample_2 TEXT,
 sample_3 TEXT,
 sample_4_1 TINYINT,
 sample_4_2 TINYINT,
 sample_4_3 TINYINT
 );
INSERT INTO TableData_400 VALUES
('1','2019-05-17 06:19:13','aaa', 'bbb','ccc',0,0,1);

select * from TableData_400

2:

DROP table TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id INT,
 sample_title TEXT
 );

3:

INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4','checkbox');

select * from TableData_elements where Dataid=400

4:

CREATE TABLE TableData_elements_options
 (Dataid INT,
 sample_id INT,
 option_id INT,
 optionss TEXT
                   );
 INSERT INTO TableData_elements_options VALUES
('400',4,1,'First'),
('400',4,2,'second'),
('400',4,3,'Third');

select * from TableData_elements_options where Dataid=400

expected result like

select datas from TableData_400 like the following way ,

 id  created_date            fname Sname Lname first second third
 1    2019-05-17 06:19:13    aaa    bbb   ccc   0     0       1

5:

SELECT  CONCAT( 'SELECT ', GROUP_CONCAT(CONCAT('sample_', sample_id, ' ', sample_title)),
               ' FROM TableData_400'
               )
INTO @sql FROM TableData_elements;
SELECT @sql;

PREPARE myStatement FROM @sql;
EXECUTE myStatement ;
DROP PREPARE myStatement;


texts: Saving SSIS package from server to local computer

texts:
Right now I'm able to upload an SSIS package from my local computer to SQL Server with my C# program:

Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();

string pkg = @"C:\Temp\myPackage.dtsx";
Package loadedPkg = app.LoadPackage(pkg, null);

app.SaveToSqlServerAs(loadedPkg, null, "myNewPackage", "myServer", null, null);

This works great. Now I want to do the opposite of this, save an SSIS package from the SQL Server to my local computer. I'm using the Microsoft documentation here: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application?view=sqlserver-2017

It doesn't seem to have any methods to save to a computer. I see the "load" methods, but I haven't been able to get those to work. I think they just prep a package to be uploaded to the server, like in my previous example. Is this possible through the same "Application" class?


texts: If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just looking to avoid needing their password to be them, so I can test their privileges at their user. In postgres I could simply allow an ident authentication on the system root user, to bypass password auth. The reason I need this is to be able to reproduce a users problem by being them, not being them will not allow an accurate reproduction. I can of course ask for their password, but that takes more time that bypassing it.


texts: Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

texts:
TLDR: The CTXCAT index is supposed to be transactional, but Before Insert triggers that modify the indexed column don't seem to induce an update.

I'm running on Oracle 9i.
(Actually, I'm running on 12c, but building for 9i.)

I have a table like so:

------------------------------------------
| Names                                  |
|----------------------------------------|
| first_name | last_name | compound_name |
|----------------------------------------|
| Joe        | Smith     | JOE SMITH     |
| ...        | ...       | ...           |
------------------------------------------

I'm using Oracle Text (available on 9i) with a CTXCAT index on the compound_name field.  CTXCAT is a transactional index, meaning that it updates any time there is a change to whatever it's indexing.  This is in contrast to a CONTEXT index, which requires a manual or periodic sync command issued to it, at which point the index updates.

Index creation code: (forgive the capitalization)

BEGIN
  ctx_ddl.create_preference   ('MY_WL', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute       ('MY_WL', 'substring_index',  'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_index',     'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_min_length',  1);
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_max_length',  7);
END;

begin
  CTX_DDL.CREATE_INDEX_SET('MY_IS');
  CTX_DDL.ADD_INDEX('MY_IS', 'is_active');
end;

-- Creating the ctxcat index uses the owner's default tablespace.
ALTER USER test DEFAULT TABLESPACE misc;

CREATE INDEX test.names_idx on test.names (name_compound)
              indextype is ctxsys.ctxcat
              parameters ('WORDLIST MY_WL
                           INDEX SET MY_IS');

I never make changes directly to the compound_name column.  Instead, I have a Before Insert Or Update trigger, that composes that value out of first_name and last_name.  The trigger looks like this:

create or replace trigger test.names_compound_trg
              before
                insert or
                update of first_name, last_name
              ON test.names
                 FOR EACH ROW
   begin
      select (
        UPPER(
              NVL2(:new.first_name,   :new.first_name || ' ',   null) ||
              NVL2(:new.last_name,    :new.last_name,           null)
        )
      ) INTO :new.compound_name FROM sys.dual;
   end;
/

You can see that any change to the first_name or last_name fields updates the compound_name field with a capitalized, concatenated string.  The trigger capitalizes it because Oracle 9i does not support case-insensitive indexes.  To get a case-insensitive search, I uppercase the compound_name and in my query I uppercase the search value.

Now, the problem:

Executing this SQL command does not cause my index to update:

update test.names set first_name = 'george', last_name = null where ...;

But executing this SQL command will:

update test.names set
  first_name = 'george',
  last_name = null,
  compound_name = 'george'
where ...;

The test.names_compound_trg trigger works properly and populates the compound_name field alright.  But it seems as though, even though CTXCAT is supposed to be transactional, it's not smart enough to detect that I've manipulated the :new.compound_name value in my trigger.  In the tests I've done so far, only a direct manipulation of that field with an update or insert query will trigger an update to the index.

Any solutions to this?  I would like to more or less keep the set up I have, with the compound_name field being populated by triggers rather than in my DAO layer, but I need to force the CTXCAT index to update itself whenever that field changes, even if the trigger is changing it.

Alternative methods of handling this are welcome as well, but I'd prefer a simple tweak or annotation or something to just get this working.

Thanks!


texts: Improve performance of transactional replication

texts:
I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & inserts 1000's 15000 plus rows in each database(more than 100) on the server. And this job runs every n minutes. It eventually causes locking issues and slows down replication.

The main objective of doing replication is to able to scale horizontally and also improve performance.
Following is a quick overview of test environment

2 database server
Each server has 100 unique databases & 5 shared database(which are replicated)
Any suggestion how to get around the locking issues?

Thanks


texts: Postgres SQL CPU and RAM optimisation to improve decryption performance with pg_crypto

texts:
I am currently working on an application that requires column-based decryption for a few thousand of rows on a regular basis. The queries are decrypted using pgp_sym_decrypt, where several columns are decrypted for each select.
For a few thousand records, the queries are unfortunately quite slow and I found out that the CPU and the RAM were not quite used. top gives 6.6% CPU usage and 14 GB RAM available out of 16 GB. Therefore, a "standard query" takes around 30s to proceed, while an acceptable performance would be rather around 5 seconds.
I tried changing a few parameters in postgres.conf, but I didn't get any performance improvement. The version of Postgres is 10.6.
Is there a possibility to increase the hardware usage of Postgres to make the decryption faster?
EDIT: Here is the result of the EXPLAIN ANALYZE:
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Sort  (cost=5.34..5.35 rows=1 width=403) (actual time=28390.586..28391.014 rows=4999 loops=1)
         Sort Key: (pgp_sym_decrypt("namaApp_nprole".name, 'nada123456'::text))
         ->  Nested Loop  (cost=0.84..5.33 rows=1 width=403) (actual time=7.291..28372.724 rows=4999 loops=1)
               ->  Index Scan using "namaApp_npaccount_bank_id_7de78b3c" on "nadaApp_npaccount"  (cost=0.42..2.64 rows=1 width=4) (actual time=0.060..15.340
rows=4999 loops=1)
                     Index Cond: (bank_id = 1)
               ->  Index Scan using "namaApp_nprole_account_id_2ad7bcd8" on "nadaApp_nprole"  (cost=0.42..2.64 rows=1 width=884) (actual time=0.008..0.011 ro
ws=1 loops=4999)
                     Index Cond: (account_id = "namaApp_npaccount".id)
 Planning time: 1.733 ms
 Execution time: 28400.053 ms```


texts: Stored Procedure imported from file, but tables have no data

texts:
Here is the Stored Procedure (import from file) that I'm having trouble:

DELIMITER //
CREATE PROCEDURE `books_database`()
BEGIN
  DECLARE n INT DEFAULT 0;
  WHILE n < 5 DO
    INSERT INTO books.science (Title,Author) VALUES ('Data Structures Using C','Aaron Tenenbaum');
    INSERT INTO books.science (Title,Author) VALUES ('C Programming Language','Brian W. Kernighan, Dennis Ritchie');
    INSERT INTO books.science (Title,Author) VALUES ('My Inventions','Nikolas Tesla');
    SET n = n + 1;
  END WHILE;
END //

When I import the SP file, it finishes without error. When I check database/table, there is no data inserted from the SP.

What is lacking or wrong with this Stored Procedure?


texts:
Can anyone suggest a windows based version control tool for PostgreSQL.

Thanks and Regards,
GP


texts: Improving Mysql updating query performance

texts:
We are trying to change some data from our Mysql database (v5.7.17) from being evaluated on-the-fly to a field in the database. The concrete field is the detectable_status and it will be calculated each night for the equipment (which inherits from detectable) in order to evaluate it. That's the query updating the field for a concrete client in our application:

UPDATE tdetectable det
JOIN tdetectable_equipment equipment ON det.id = equipment.id_detectable
JOIN torganization org ON det.id_organization = org.id
SET det.detectable_status = (CASE
    WHEN (equipment.setting_up_date IS NOT NULL
        AND equipment.expiration_periodicity_type IS NOT NULL AND
        fdate_periodicity(equipment.setting_up_date,
        equipment.expiration_periodicity_type, equipment.expiration_periodicity_value,1)
        < NOW()) = 1
        THEN 'EXPIRED'
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle incorrect_cycle
        WHERE equipment.id_detectable = incorrect_cycle.id_detectable
        AND incorrect_cycle.status = 'NO_OK'
        AND incorrect_cycle.active <> 0 AND incorrect_cycle.id IS NOT NULL))
        THEN 'INCORRECT'
    WHEN (EXISTS(
        SELECT *
        FROM trevision_cycle need_revision_cycle
        WHERE equipment.id_detectable = need_revision_cycle.id_detectable
        AND need_revision_cycle.next_revision_date IS NOT NULL
        AND need_revision_cycle.next_revision_date < NOW() = 1
        AND equipment.id_detectable = det.id
        AND need_revision_cycle.active <> 0))
        THEN 'NEEDS_REVISION'
    WHEN (equipment.setting_up_date IS NULL
        AND (equipment.expiration_periodicity_type IS NOT NULL OR EXISTS(
        SELECT *
        FROM trevision_cycle last_revision_cycle
        WHERE last_revision_cycle.id_detectable = equipment.id_detectable
        AND last_revision_cycle.last_revision_date IS NULL
        AND last_revision_cycle.active <> 0)))
        THEN 'INDETERMINATE'
    ELSE    'CORRECT'
    END)
WHERE det.active and org.id_client = ?

The status is set for every equipment in the client based in different factors (expiration dates, revision dates...). The problem is the query is quite slow. Here it is my benchmark:

Affected rows: 131, Duration: 0,375 sec.

Affected rows: 4.208, Duration: 44,585 sec.

Affected rows: 2.036, Duration: 13,712 sec.

Affected rows: 13.288, Duration: 00:10:04

Also I want to clarify the content of the fdate_periodicity function, which takes a date and adds a periodicity to it (example: 2017-09-10 + 1 YEAR = 2018-09-10):

BEGIN
IF add_to THEN
    RETURN
        CAST((CASE periodicity_type
        WHEN 'DAY' THEN (input_date + INTERVAL periodicity_value DAY)
        WHEN 'WEEK' THEN (input_date + INTERVAL periodicity_value WEEK)
        WHEN 'MONTH' THEN (input_date + INTERVAL periodicity_value MONTH)
        WHEN 'YEAR' THEN (input_date + INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
ELSE
    RETURN  CAST((CASE periodicity_type
        WHEN 'DAY' THEN (input_date - INTERVAL periodicity_value DAY)
        WHEN 'WEEK' THEN (input_date - INTERVAL periodicity_value WEEK)
        WHEN 'MONTH' THEN (input_date - INTERVAL periodicity_value MONTH)
        WHEN 'YEAR' THEN (input_date - INTERVAL periodicity_value YEAR)
        ELSE NULL
        END
        ) AS DATETIME
        );
end if;
END

I understand MySql is doing quite a heavy work here, but are this execution times normal for a core-8 + 14Gb RAM machine? What could I do to improve it? Here it is the execution plan (explain):

Thanks in advanced!


texts: How to get MAX and MIN in GROUP BY query?

texts:
In this query:

SELECT col1, col2, col3
FROM table1
GROUP BY col1

each row includes the first value of col2 and col3 for each unique value of col1.

How to select MAX and MIN for each value of col1.

Something like:

SELECT col1, minimum of col2, maximum of col2, minimum of col3, maximum of col3


texts: Run PostgreSQL without admin / superuser

texts:
I'm trying to setup a secure append-only database with PostgreSQL or MySQL. My idea for that is to generate a database where no admin/superuser accounts exists and only users with limited privileges can insert data to the database. The lack of superuser accounts should be no problem as the database will be setup completely from scratch if any admin changes need to be made.

My idea was to generate the database with an admin user (with an encrypted hash) and then immediately delete the admin user (or lock him out by setting the password to blank).

Is that a suitable approach?


texts: Why the same execution plan causes LOB reads in some cases?

texts:
I have a spatial index and the following query:

SELECT DS2.[ID]
      ,DS2.[InstanceID]
      ,DS2.[Location].STDistance(@Location)
FROM [dbo].[DataSource01] DS1
INNER JOIN [dbo].[DataSource02] DS2 WITH (INDEX = [IX_DataSource02_Location])
    ON DS1.[ID] = DS2.[ID]
WHERE DS2.[Location].STDistance(@Location) < 144840.96; -- 90 miles in meters

where the @Location is the current user location. For two different users, I get two different execution times - 0 seconds vs 5 seconds and I want to understand what is causing this.

I get two identical execution plans:

everything seems the same, except the Filter operator - in the faster case 5 k rows are returned against 1.8 k rows for the the other case.

I have compare the reads of the two queries and it seems the second one is performing a lot of LOB reads:

I am wondering what could cause such LOB reads when the plan is the same and the data size and rows of each operator is the same (except the Filter one)?

There is another difference in the Clustered Index Seek(Spatial) operator - the Number of Executions of the faster query is 1142 and for the slower is 1180.

The Warnings of the Clustered Index Seek (Spatial) operator is Columns With No Statistics. The Column Reference are:

[extended_index_1523900896_384000].Attr
[extended_index_1523900896_384000].pk0
[extended_index_1523900896_384000].pk1

execution plan - slow query: http://dox.bg/files/dw?a=c133c2a7fb

execution plan - fast query: http://dox.bg/files/dw?a=24818510fb

The queries are executed on the same hardware.


texts: Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?

texts:
I have to find the total time taken for query to execute in SQL-SERVER 2016 when all the data pages should be in Disk only.
To accomplish that , I cleared all buffers in the SQL-SERVER using the following commands :

DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
DBCC FLUSHPROCINDB (@dbId)
GO

After this I again found that logical reads were there, then I got to know about Read-Ahead , so I disabled read-ahead by the trace flag

DBCC TRACEON(652,-1)
GO

and also the prefetch flag

DBCC TRACEON(8744,-1)
GO

Now still I have logical reads , but Physical reads are significantly higher than just clearing the buffer.
Is there any way to achieve 0 logical reads, or my understanding of logical read is wrong?

Please guide me through, I'am new to SQL-SERVER world


texts: Storing prices with price history and different specificity (global, user group, single user) in MySQL database

texts:
I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). There are also two different types of prices (product and delivery price). Now I am having trouble with this. I made it to work, but I somehow feel bad about it when I write queries (they tend to get long and complicated).

I designed two tables like this (they are same):

base_prices

| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |

delivery_prices

| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |

For a user specific price referent id would be a positive integer, matching users id.
For a group specific price referent id would be a negative integer, matching group id (I made group ids negative integers).
For global product price, referent id will be NULL.
This way, when I query price for a specific item and specific user and his group all I need to do is filter table by item_id and order results by referent_id column in descending order. This way, user specific are on top, group after and global are last.

Date ranges valid_from-valid_until cannot overlap for two rows.

Now a query to get current prices would look something like this:

SELECT
    *
FROM
    base_prices AS outer
WHERE
    id = (
        SELECT
            id
        FROM
            base_prices AS inner
        WHERE
                NOW() BETWEEN valid_from AND valid_until
            AND inner.item_id = outer.item_id
            AND (
                    inner.referent_id = # HERE GOES USER ID
                OR  inner.referent_id = # HERE GOES GROUP ID
                OR  inner.referent_id IS NULL
            )
        ORDER BY
                inner.referent_id DESC # FOR SPECIFICITY

Thing that bugs me is that I always have to write this kind of sub-query to get items, and this doesn't seem as a good design to me. When data starts to build up, this queries will cost much more. Also, I had to write a few triggers to keep data integrity during inserts and updates.

My opinion is that current prices and historical prices need to be separated, but I was in a hurry, so this is what came up. I was thinking about scheduling events during inserts based on valid_from column that will move data from historical prices table to (new) current prices table.

I am inexperienced, so this seems kind of weird to me. It might be OK, though, but I would like to see some suggestions and opinions.

If I left out something or there is not enough info, leave a comment.


texts:
I am untangling some legacy code and never in all my years have I seen this:

Select *

FROM GLAccounts
INNER JOIN GLCharts
    ON glaGLChartID = glcGLChartID
LEFT JOIN GLCategories
    ON glcGLCategoryID = gltGLCategoryID
INNER JOIN GLDepartments
    ON glaGLDepartmentID = gldGLDepartmentID
INNER JOIN GLDivisions
    ON glaGLDivisionID = glvGLDivisionID
        ,GLFiscalYearPeriods                --<this comma>
INNER JOIN GLFiscalYears
    ON glfGLFiscalYearID = glzGLFiscalYearID

ORDER BY glcGLCategoryID
    ,glcParentGLChartID
    ,glaGLChartID

the join works, but I don't find this use of a comma in any T-SQL syntax reference for the ON clause.

What's happening here?
TIA


texts: comparison operator in count() is not working

texts:
The query (SELECT 15 > 14 FROM Duel) Working in MySql, but when I tried in MS-SQL Server getting following error:

  SQL Error(102): Incorrect syntax near '>'

I have a requirement where I want to see if at least 1 record exist return 1 or else 0

SELECT count(emp.name) > 0
        FROM ****
        WHERE *** IN (***)

Check the db fiddle


texts: MySQL - Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE

texts:
I am working with MySQL 8.0 through MySQL Workbench on a Windows machine.

I am having an enormous amount of problems importing an excel file into a MySQL database. I think the problem is that my data contains blank spaces or periods . to denote the absence of values for some positions.

I have received the data in the form of an Excel spread-sheet. As I have read that using CSV format may sometimes cause problems, I have saved the spreadsheet as a tab-delimited text file. I have created a table in MySQL Workbench according to the fields of the spreadsheet and everything is ready for the import. My problem is upon typing the following code into the MySQL Workbench:

LOAD DATA INFILE 'The/path/to/my/file.txt'
INTO TABLE my_table
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

I receive errors about incorrect values.

Some of the positions of the data are not of the type that the database expects. For example, there is a column that expects a DATE. The issue is that in the data, some fields corresponding to the column are blank. I think what is happening is that when MySQL tries to import that blank value, the error is caused.

Other columns have either empty spaces (no values) or indeed a single period . to denote that there is no value for that position. Depending on the column, the database may expect type Date or Int. I have searched extensively for advice on the subject. The first answer on this page had looked hopeful, but after implementing a copy of the solution I received an error of the type:

  Error Code: 1366. Incorrect integer value: ''for column 'abs_2012' at row 1.

abs_2012 is of type Int and row one's value is an empty space. What is particularly strange is that abs_2012 is the last column of about 30 I haven't received any errors about the previous columns.

Does anyone have any idea at all?

Many thanks in advance


texts: Show data and disk use breakdown by table

texts:
I have a SQL Server 2008 R2 database being used by several deployed programs.

Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and distinguish logical space from disk space?

If I use SSMS (Management Studio), the storage properties shown for the database reads 167 MB with 3 MB "available" (about the right size, but I'm concerned about the 3 MB available - is this a limit to be concerned about, assuming I know I have enough disk space?)

I can drill into each table, but that takes forever to do.

I know I can write my own queries and test around, but I'd like to know if there's already an easy (built-in?) way to do this.


texts:
Will increasing a column (nvarchar) width necessarily drop the table?

In other words, can the width be changed in a production environment with active users?

I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.


texts:
This is the error message I get on my replica when there is a connection timeout for few seconds with the Primary. Can anyone tell me whats going on and why are the available storage being moved as this is an AAG where storage is not supposed to be shared and also here there is no failover its just the secondary replica missed heartbeats. Thanks

The failover error logs are mentioned below starting from the bottom

Cluster network 'Cluster Network 2' is operational (up). All available failover cluster nodes attached to the network can communicate using it.
Joiner tried to Create Security Context using Package='Kerberos/NTLM' with Context Requirement ='0' and Timeout ='40000' for the target = 'xxxxxx01'
The Cluster service successfully brought the clustered role 'Available Storage' offline.

Clustered role 'Available Storage' is moving to cluster node 'xxxx'.
Cluster has lost the UDP connection from local endpoint xxx.xx.xx.21:~xx43~ connected to remote endpoint xxx.xx.xx.20:~xx43~.
Cluster has missed two consecutive heartbeats for the local endpoint xxx.xx.xx.21:~xx43~ connected to remote endpoint xxx.xx.xx.20:~xx43~.


texts: Why do large tables slow down queries in databases?

texts:
Kind of a generic question; but I have noticed that large tables in certain CMS's can slow them down to a crawl. The solutions for this I've seen are to just truncate those tables.

Why do large tables slow down SQL queries so much?

How do Indexes help large tables?

How does disk speed factor into this?


texts: Sorting Result state taking very long time


texts: Reason of poor performance of ANSI joins in Oracle 9i

texts:
Please before flagging as duplicate, read the last paragraphs.

In an Oracle 9i database this query runs in 0.18 seconds:

select
    count(*)
from
    dba_synonyms s,dba_objects t
where
    s.TABLE_OWNER = t.OWNER(+) and
    s.TABLE_NAME = t.OBJECT_NAME(+) and
    s.DB_LINK is null and
    t.OWNER is null;

...but this one runs in an appalling 120 seconds!:

select
    count(*)
from
    dba_synonyms s left join dba_objects t
                   on ( s.TABLE_OWNER = t.OWNER and s.TABLE_NAME = t.OBJECT_NAME )
where
    s.DB_LINK is null and
    t.OWNER is null

Notice the only difference is using propietary Oracle join syntax vs ANSI join syntax.

This question is not a duplicate of this one because that other question is about a very complex query involving more than 9 tables, and the only answer points out that the queries are very different besides the syntax usage (mainly the order of the tables).

In my case it is a extremelly simple query, a mere join between two relations with no mayor complications of differences, including the order of tables.

Is this a bug in Oracle 9i?
What is the cause of such dramatic    difference in performance?


texts:
I have a Master DB.
I want to make duplicate DB of the Master DB but not all tables just specified.

I have those table names in comma separated string.
tbl1, tbl2, tbl7, tbl10 etc.

I'm trying to make a stored procedure passing it new DB name That creates the DB and replicate the given table to it. So far i have 2 thing, but i don't know how to loop through it. I'm new to DB so please guide how to achieve this.

(Note) I haven't found if an array can be used to loop through, the table names could also be stored in array variable, because the table names are hard coded.

The following is the sudo code i desire.

CREATE DATABASE %param_db%;

@tables = 'tbl1, tbl2, tbl7, tbl10';
loop through @tables as table_name
    CREATE TABLE %param_db.table_name% LIKE Master.%table_name%;
End loop


texts: How to enhance speed of UPDATE statement

texts:
Out of practice , some tips on indexes efficiency appreciated.
Creating test data ... changing consultant name on a large table using an update statement. one of my tables has 10 rows, second table has 4500 rows ...

Tips appreciated to reduce the process time...

currently getting this error message which I presume is a timeout ...

  Lookup Error - MySQL Database Error: Lock wait timeout exceeded; try
  restarting transaction

drop table if exists cons_temp;

create temporary table cons_temp
as select distinct consultant_id, consultant
from testdata_biactivity_rob;

update testdata_biactivityx a
inner join cons_temp b on b.consultant_id = a.consultant_id
set a.consultant = b.consultant;


texts: Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0

texts:
I'm unable to export my 8.0.1 MySQL database using MySQL Workbench 8.0.  When I go to perform the export via the "Administration" table, with all the default settings (I didn't change anything anywhere, not even Advanced settings) I run into this error:

09:10:26 Dumping account (all tables)
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump --defaults-file="/var/folders/m2/y4sdt3ks3_x206vw2gv37pgc0000gp/T/tmptSYqBY/extraparams.cnf"  --user=root --host=account.biblebowl.org --protocol=tcp --port=4488 --default-character-set=utf8 --no-create-info=TRUE --skip-triggers "account"
mysqldump: Couldn't execute '/*!80000 SET SESSION information_schema_stats_expiry=0 */': Unknown system variable 'information_schema_stats_expiry' (1193)

Operation failed with exitcode 2
09:10:26 Export of /Users/me/dumps/Dump20190207.sql has finished with 1 errors

I could downgrade to a previous version Workbench, but I'd prefer to get this one working and I imagine other folks are going to run into this as well.  How can I get around this error and properly export my database?


texts: Restore Oracle Database excluding some tablespaces

texts:
I want to restore a backup in cold full (without archivelog) made with RMAN in another server and I do not want to do a full restore but partial, that is to restore only some tablespaces ... I have read that during the backup it can be configured to exclude some tablespaces of this but in this case what I need is ... already with the full backup do a restore of the database excluding certain tablespaces.
Is it possible to do this? it's an Oracle 9i

Thank you


texts: 2 Linked Servers using MSDASQL with conflicting Provider settings

texts:
I have a linked server problem on a SQL Server 2012 database.

Linked Server 1 accesses a sql server 2000 database using sql server native client 10.0 and MSDASQL provider.

Linked Server 2 accesses a mysql database using mysql odbc 5.1 driver and MSDASQL provider.

If Level Zero Only is unchecked on the MSDASQL provider Linked Server 1 works but Linked Server 2 does not ("An invalid schema or catalog was specified for the provider “MSDASQL” for linked server").

If Level Zero Only is checked on MSDAQL provider Linked Server 1 does not work (error as above) but Linked Server 2 does.

Is there a way to provide the value of Level Zero Only on the provider string in the linked server settings? Or can anyone explain why this scenario is happening and how to fix it. Thank you!!


texts:
I have a table, containing TV series episodes:

 series:
    | series_id | season | episode | title
    |         1 |      1 |      18 | xxx
    |         1 |      2 |       3 | xxy
    |         3 |      1 |      13 | yyy
    |         3 |      2 |       5 | yyy

I want to get the latest episode from each show, so 2x03 from series 1, and 2x05 from series 3.

I have 3 ideas, none of them great:

Create a new integer column, calculated as season x 100 + episode, now I can sort easily by it, and quite fast, but I have to recalculate it every time if the season or episode column is updated.
Write a select that first finds the max(season) for each series, and use it in a second select. Easier to maintain, but I was not able to write the right sql statement yet.
Use a calculated aggregare:

  SELECT series_id, max( 100* season + episodes ) as latest FROM series GROUP BY series_id;

Which option is better? How should I write option 2?


texts: Transferring (backup/restore) from development to an online server

texts:
I have a PostgreSQL database that is on my computer owned by postgres user and another database on a server, owned by a different user let's say user_server

I'm using pgAdmin.

I create a backup on my computer using pgAdmin with the default settings (custom or tar).
I connect to database on the server
I execute a restore on the server database using the backup from my computer

After doing the operations, pgAdmin says that it fails, but the database looks ok. There are sometimes issues with sequences where the start number is lower than the max corresponding ID/PK.

I checked the logs and I have multiple errors, but all of them are similar to the following:

----
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3398; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--------

pg_restore: [archiver (db)] Error from TOC entry 217; 1259 87892 TABLE association postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association OWNER TO postgres;

----

pg_restore: [archiver (db)] Error from TOC entry 216; 1259 87890 SEQUENCE association_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  must be member of role "postgres"
    Command was: ALTER TABLE public.association_id_seq OWNER TO postgres;

Looks like I'm having an issue because they are owned by 2 different users.

Can this be done from pgAdmin, like a setting to ignore users or do I need to modify something on the server?


texts: How to prevent big performance impact during index creation

texts: I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is there a way to prevent or limit this? After all I am creating the index to increase performance. Or is this a onetime hit I have to endure for things to be better in the future?


texts: how to join table from different database with different Users of database?

texts:
I have Two database called.

Database1:

host: 127.0.0.1
user: root1
pass: root1
dbname: db1

Table 1: Category

ID    INT(11)          Primary Key
Name  VARCHAR(255)

Database2:

host: 127.0.0.2
user: root2
pass: root2
dbname: db2

Table 2: Product

ID    INT(11)          Primary Key
Name  VARCHAR(255)
CatID INT(11)          FK( Table1: Category: ID )

I am calling query like this;

SELECT *
FROM db2.product AS p
LEFT JOIN db1.category AS c ON p.CatID = c.ID

It will return error:

SELECT command denied to user 'root2'@'127.0.0.2' for table 'Product'
SELECT command denied to user 'root1'@'127.0.0.1' for table 'Category'

Can anyone help me How can I resolve this issue.


texts: Oracle Database 11g and TFS (2010) integration

texts:
I am currently in a team of 4 database developers, working with Oracle Database 11g Enterprise Edition. We use quite a bit of SQL/PLSQL and create many scripts. My colleagues send all their work to a specific person before they can be deployed in a production environment.

Instead of bombarding a colleague like this, we'd like to use a version control tool (Team Foundation Server 2010 in our case), pretty much as a code repository, where our work can be saved on a regular basis.

Since noone -- including me -- has any experience with TFS, I'd like to ask:

How can we best deploy TFS (or perhaps any other version control tool) in a development and test environment, as a way to manage our work? Any downsides/advantages, any specific points to take into consideration, any tips/pointers?

This is more of an organisational exercise, rather than a technical one. So my current focus is not the installation, etc. but to find the best way to utilize version control in a small team.

Thanks!

EDIT: Perhaps you can also answer the question by recommending several TFS-specific features that might be useful to (Oracle) DBAs and developers.


texts:
I have an event log that rarely ends up with duplicated events (due to "at least once" delivery). The logs are practically unique on a short time frame, but might repeat on a time scale of days / weeks. I'd like to create a view of some kind where N events with the same text that occurred within 10 seconds will be reduced down to 1. How can I do this in Postgres? Perhaps a window function, or a partiton?

This is what I currently have, which works fine for toy examples, but explodes for anything nontrivial in size:

select * from events where (
    select count(*)
    from events as nearby
    where
        nearby.data=events.data and
        nearby.time>events.time and
        nearby.time<events.time+10
) = 0;


texts:
I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:

SELECT Max(NumInvoicesState)
FROM
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState
   FROM VENDORS V
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) AS Alias

My idea is to first do a query to get a table with a list of vendors by state and the number of invoices for each vendor.

I thought of joining the inner-table to Vendors and then group by VendorState, but that does not seem to work.

I guess I could SELECT VendorName, VendorState then join it with Vendors on, say VendorState and then Group by Vendors. But then I get all the Vendors, not just the ones with the max.


texts: SQL Server: Unable to save import export package, but am on Standard Edition

texts:
I am confused.

Issue: SSIS ("SQL Server Import Export Wizard") should let me build a data import package and save it to a file, for easy re-running (converting to a task, etc.).

I want to save a package, but the tool always tells me "profile cannot be saved because your are not running SQL Standard Edition."

I know that the SSIS ("SQL Server Import Export Wizard") that, but I think I qualify.

Why is the save option not available?

I am connecting to an instance of standard (from connection properties in SSMS):

Microsoft SQL Server Standard Edition (64-bit)  (SQL 2005)

I have tried the SQL 2012 and 2014 client tools, and have the same experience on each.

How do I solve this?

(Is the tool detecting Express edition that I have locally?)


texts: How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

texts:
4 processors means the query runs in parallel.

Select /*+ parallel(4) */ T1.A
FROM T1,T2,T3
WHERE T1.A = T2.A
AND T1.B=T3.B
AND T1.C=T3.C

Tables T1,T2 and T3 have 4,8, and 16 partitions respectively.

Using nested loops,sort merge and hash join as available methods of join. How to create execution plan for above query? I am not able to understand when to apply sort merge in execution plan.


texts: Read inside node value of Xml using Sql Server

texts:
I want to read node values of xml in sql, my xml look like this

I just want to read value of order node that is 'Order 1'
and my code is

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM OPENXML (@idoc, '/ROOT/Customer/Order',0)
WITH (
    [Order]  varchar(100)
    );


texts: Get object script in SQL Server via T-SQL?

texts:
I need to get the creation script of an object in a SQL Server database. I need the exact same script that was used to create it. Like when you use SSMS to alter a view you already defined, and then the script is there, just the way you've defined it earlier.

I need to do this in T-SQL. Where does SQL Server store the scripts related to the database objects? I can't find the related view in system catalog views.

What I'm trying to find is something like:

select creation_script -- I know this column doesn't exist
from sys.objects
where name = 'CustomersView'


texts: Which collation should I use for biblical Hebrew?

texts: Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).


texts:
Total table_parent rows: 1318
Total table_child rows: 12 mil++

EXPLAIN
select SQL_CALC_FOUND_ROWS table_child.*
  , table_parent.name AS parent_name
from
  `table_child`
  inner join `table_parent` on `table_parent`.`id` = `table_child `.`parent_id`;

Output

| id  | select_type | table        | partitions | type | possible_keys | key   | key_len | ref                     | rows | filtered | Extra |
| --- | ----------- | ------------ | ---------- | ---- | ------------- | ----- | ------- | ----------------------- | ---- | -------- | ----- |
| 1   | SIMPLE      | table_parent | NULL       | ALL  | PRIMARY       | NULL  | NULL    | NULL                    | 1318 | 100.00   | NULL  |
| 1   | SIMPLE      | table_child  | p1,p2,p3   | ref  | idx_1,idx_2   | idx_2 | 5       | db_name.table_parent.id | 447  | 100.00   | NULL  |

The table_parent filter only in the join, can the rows further reduce?


texts: Why does mysqldump stop the slave SQL thread for the entire backup?

texts:
I realized recently that I was using --master-data to dump a backup from a replication slave when what I really wanted was --dump-slave. After switching to --dump-slave, I see that mysqldump will stop the slave SQL thread during the backup (which was clear from the documentation -- it wasn't a surprise).

I was wondering if anyone could explain why the slave's SQL thread needs to be stopped for the entire backup? I'm using --single-transaction and I'm expecting to get a consistent snapshot as of the time the backup is started. That should be possible even with transactions being processed by the SQL thread. Even if the slave SQL thread is completely held-up due to some lock that mysqldump has acquired, the slave should be allowed to continue to (try to) execute SQL DML statements during the backup.

I can also understand wanting to at least pause the slave SQL thread so that the get-stave-status the start-transaction operations could happen consistently, but as soon as the dump operation begins, the slave SQL thread could be re-started, right?

When using --master-data with a replication master, global locks are obtained a single time and then released, allowing the regular application-load to continue during the dump operation. Why can't this be done on the slave in the same way? Any information anyone could provide would be greatly helpful.


texts: MongoDB refuse to start, operation not permitted

texts:
When I try to run mongod --dbpath=./data I get the following error:

2017-09-20T21:47:55.720+0200 I CONTROL  [initandlisten] MongoDB starting : pid=20500 port=27017 dbpath=./data 64-bit host=debian
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] db version v3.2.11
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] git version: 009580ad490190ba33d1c6253ebd8d91808923e4
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2l  25 May 2017
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] allocator: tcmalloc
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] modules: none
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten] build environment:
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten]     distarch: x86_64
2017-09-20T21:47:55.721+0200 I CONTROL  [initandlisten]     target_arch: x86_64
2017-09-20T21:47:55.722+0200 I CONTROL  [initandlisten] options: { storage: { dbPath: "./data" } }
2017-09-20T21:47:55.781+0200 I -        [initandlisten] Detected data files in ./data created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2017-09-20T21:47:55.802+0200 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=1G,session_max=20000,eviction=(threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2017-09-20T21:47:55.894+0200 E STORAGE  [initandlisten] WiredTiger (1) [1505936875:894516][20500:0x7f357e7c1cc0], file:WiredTiger.wt, connection: ./data/WiredTiger.wt: handle-open: open: Operation not permitted
2017-09-20T21:47:55.895+0200 I -        [initandlisten] Assertion: 28595:1: Operation not permitted
2017-09-20T21:47:55.895+0200 I STORAGE  [initandlisten] exception in initAndListen: 28595 1: Operation not permitted, terminating
2017-09-20T21:47:55.895+0200 I CONTROL  [initandlisten] dbexit:  rc: 100

The contents and permissions of the ./data directory is as follows:

drwxrwx--- 1 root vboxsf  4096 Sep 20 05:11 .
drwxrwx--- 1 root vboxsf  4096 Sep 20 22:07 ..
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:25 collection-0--661121965341019305.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 collection-0--9187569579836644431.wt
-rwxrwx--- 1 root vboxsf 16384 Sep 19 05:24 collection-2--661121965341019305.wt
drwxrwx--- 1 root vboxsf     0 Sep 19 19:16 diagnostic.data
-rwxrwx--- 1 root vboxsf 16384 Sep 19 05:24 index-0-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 16384 Mar  1  2017 index-1-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 index-2-7518289071480072689.wt
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:25 index-3-7518289071480072689.wt
drwxrwx--- 1 root vboxsf     0 Sep 20 05:12 journal
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:24 _mdb_catalog.wt
-rwxrwx--- 1 root vboxsf     0 Sep 20 05:11 mongod.lock
-rwxrwx--- 1 root vboxsf 36864 Sep 19 05:26 sizeStorer.wt
-rwxrwx--- 1 root vboxsf    95 Feb 23  2017 storage.bson
-rwxrwx--- 1 root vboxsf    49 Feb 23  2017 WiredTiger
-rwxrwx--- 1 root vboxsf  4096 Sep 19 05:24 WiredTigerLAS.wt
-rwxrwx--- 1 root vboxsf    21 Sep 20 04:49 WiredTiger.lock
-rwxrwx--- 1 root vboxsf  1000 Sep 19 05:27 WiredTiger.turtle
-rwxrwx--- 1 root vboxsf 61440 Sep 20 05:12 WiredTiger.wt

My user is in the vboxsf group.

Does anyone know why I'm getting this error, or what I can do to fix this?

I should note that I'm running Debian inside Virtual Box on a Windows 8.1 host. The directory in question is a shared folder (hence the vboxsf group).


texts: Auto restart MySQL /etc/init.d/mysqld In linux AMI?

texts:
Currently I added a swap file in my linux ami 2 instance. AWS

It has 4GB Added swap ram file
But I want to make sure that mysql will start if it fails to allocate ram?
I can start mysql like this
sudo /etc/init.d/mysqld restart
But how can I make it auto restart using cron job in this type of ec2 instance?
UPDATE
Added line in my /etc/crontab


texts: Mysql Cluster distribute data only one node..why?

texts:
here are following setup

ndb_mgm -e show

Connected to Management Server at: us-srv1.site.vrack:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @192.168.0.2  (mysql-5.6.31 ndb-7.4.12, Nodegroup: 0, *)
id=4    @192.168.0.1  (mysql-5.6.31 ndb-7.4.12, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.1  (mysql-5.6.31 ndb-7.4.12)

[mysqld(API)]   1 node(s)
id=50   @192.168.0.1  (mysql-5.6.31 ndb-7.4.12)

and this command shows this

root@us-srv1:~# ndb_mgm -e "ALL REPORT MemoryUsage"
Connected to Management Server at: us-srv1.tagove.vrack:1186
Node 3: Data usage is 0%(5 32K pages of total 64000)
Node 3: Index usage is 0%(8 8K pages of total 65568)
Node 4: Data usage is 24%(15940 32K pages of total 64000)
Node 4: Index usage is 7%(4672 8K pages of total 65568)

its fresh mysql-cluster installation and after setup i've just imported database and only one node shows memory usages.

config.ini

[ndb_mgmd]
hostname=us-srv1.site.vrack
datadir=/var/lib/mysql-cluster
NodeId=1
[ndbd default]
NoOfReplicas=2
datadir=/var/lib/mysql-node-data/
DataMemory=2000M
IndexMemory=512M
[ndbd]
hostname=us-srv2.site.vrack
NodeId=3
[ndbd]
hostname=us-srv1.site.vrack
NodeId=4
[mysqld]
hostname=us-srv1.site.vrack
NodeId=50

Right now i am testing this on two physical machines, us-srv1 has mysql api


texts: How do I reindex my SQL Server Express database to free up space when it's already full?

texts:
Being the naive guy that I am, I let my DB get full (10GB is the max for SQL Server Express), and have now decided to drop columns to reclaim space. I'm dropping 10 columns from a ~50M row table to save space, but even after dropping them, I keep getting "Error 1011: Could not allocate a new page for database ...".

I cannot shrink the database, I cannot reindex the database, I cannot rebuild indexes, I can't add a filegroup, or anything like that. I have 97% fragmentation (even after defragging my hard drive), and I feel like I'm running out of options. Even attempting to run an ALTER INDEX .. REBUILD WITH (SORT_IN_TEMPDB = ON) gives the same error message.

What are my options now? Should I just migrate to a different DB (MYSQL, PostgreSQL)?


texts:
I am learning about mongodb. If I create a bulk write is this transaction all or nothing? I have a scenario where my users can delete who they are following. For this to happen I need to delete from user A who they are following and from User B the follower. For consistency I need these to occur as a all or nothing because I wouldn't want only 1 of the 2 operations to succeed as this would cause bad data. Reading the docs:

https://docs.mongodb.org/manual/core/bulk-write-operations/

I could not find the answer.


texts: What format is this export of election data in?

texts:
I have a series of files I've downloaded from the Texas Ethics Commission (a misnamed . You can see an example of their layout in the ReadMe.txt. I've made an etl library which processes the ReadMe.txt to generate the SQL DDL to create this schema, and load it from the CSVs. The thing is, I think this is a standardized format. I imagine it's backed by something like a PICK (which is a BASIC database) or something COBOL-esque -- having worked with them before, and that this is something like a MARC, or ANSI, or ISO standard.

I'd like to potentially abstract out my ETL script to benefit others who use this format.

Some identifying features of the format are that it supports

Arrays and internal one-to-many relations on the record
at least types BigDecimal, Long, Date, String
the export is labeled "Flat File Architecture Record Listing"

Arrays

For example here you see this,

    Array                                                                                4050
    loanGuarantorLoanPersent[5/ROW_MAJOR]         CsvPublicExportLoanGuarantorLoanPersent     810 Guarantors for the loan (maximum 5)
 46    guarantorPersentTypeCd                     String                                       30 Type of guarantor name data - INDIVIDUAL or ENTITY
 47    guarantorNameOrganization                  String                                      100 For ENTITY, the guarantor organization name
 48    guarantorNameLast                          String                                      100 For INDIVIDUAL, the guarantor last name

That defines a structure called a loanGuarantorLoanPersent and essentially declares that there are five of them. So the export CSV will have something like

guarantorPersentTypeCd1,guarantorNameOrganization1,guarantorNameLast1,guarantorPersentTypeCd2,guarantorNameOrganization2,guarantorNameLast2,guarantorPersentTypeCd3,guarantorNameOrganization3,guarantorNameLast3...

You can see an example of this data here,

https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_CF_CSV/ReadMe.txt
https://github.com/EvanCarroll/db-Texas-Ethics-Commission/blob/master/data/TEC_LA_CSV/LobbyLAR-ReadMe.txt


texts: Postgres Transaction OOM on 100k DDL statements

texts:
We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't acquire more memory (increasing from 10MB to 2.2GB usage on 3GB ram), OOM killer hits it with 9 which results in Postgres being gone to recovery mode.
BEGIN;

CREATE SCHEMA schema_1;
-- create table stmts - 714
-- alter table add pkey stmts - 714
-- alter table add constraint fkey stmts - 34
-- alter table add unique constraint stmts - 2
-- alter table alter column set default stmts - 9161
-- alter table alter column set not null stmts - 2405
-- alter table add check constraint stmts - 4
-- create unique index stmts - 224
-- create index stmts - 213

CREATE SCHEMA schema_2;
-- same ddl statements as schema_1 upto schema_7
-- ...
-- ...
-- ...
CREATE SCHEMA schema_7;

COMMIT

Including the create schema statement, approximately 94304 DDL statements were meant to be executed.
As per Transactional DDL in PostgreSQL

Like several of its commercial competitors, one of the more advanced features of PostgreSQL is its ability to perform transactional DDL via its Write-Ahead Log design. This design supports backing out even large changes to DDL, such as table creation. You can't recover from an add/drop on a database or tablespace, but all other catalog operations are reversible.

We have even imported approximately 35GB of data into PostgreSQL in a single transaction without any problem, but why does the Postgres connection requires huge memory when executing thousands of DDL statements in single transaction?
We can temporarily resolve it by increasing the RAM or allocating swap, but we can say that the number of schema creation in a single transaction can increase up to 50 - 60 (Approx 1M DDL statements) which would require 100+ Gigs of RAM or swap which isn't feasible right now.
PostgreSQL version: 9.6.10
Is there any reason why executing lots of DDL statements requires more memory while dml statements does not? Isn't both handles transactions by writing to underlying WAL? So why, for DLL it's different?
Reason for Single Transaction
We sync the entire database of customers from Customer Premise (SQL Server) to cloud (PostgreSQL). All customers have different no of databases. Process is, entire data will be generated as CSV from SQL Server and import into PostgreSQL using Temp Tables, COPY and ON CONFLICT DO UPDATE. During this process, we treat each customer as a single database in PG and individual DB in customer's SQL Server as schemas in customer's PG DB.
So based on the CSV data, we will create the schemas dynamically and import data into it. As per our application design, the data in PG should be strictly consistent at any point of time and there shouldn't be any partial schema / tables / data. So we had to achieve this in a single transaction. Also we incrementally sync from customer to cloud DB every 3 minutes. So the schema creation can happen either in first sync or incremental sync. But the probability of creating so many schemas in first sync itself is very high.
Update 1
Commenting the ALTER TABLE ALTER COLUMN statements greatly reduced the memory usage as it now takes only 300MB atmost. Have to merge those into the CREATE TABLE statements itself.
Will ask the core problem in PG Hackers mailing list.


texts: SQL Server columnstore metadata (pushdown) filter

texts:
AFAIK columnstore indexes store min/max values for segments (1 million rows), so this information can be used to prune not needed data.
This feature seems to work with equality predicates, but not with more complex cases like joins or subquery filters.
In SQL Server 2014, is it possible to use columnstore metadata filtering with joins or subqueries?

Below is my test case:

--create table generate_series with 67_108_864 rows (runs about 5 mins)

set nocount on;

create table generate_series(c bigint);
insert into generate_series select 1;

declare @i int = 26;
while @i != 0
begin
    insert into generate_series WITH (TABLOCK)
    select
        (select count(*) from generate_series)
        + row_number() over (order by (select null)) as c
    from generate_series;

    set @i = @i - 1;
end;

create nonclustered columnstore index cstore on generate_series(c);

--Test queries

-- 0 seconds (very fast)
select * from generate_series where c = 100;

-- 8 seconds (slow)
select 100 as c into #tmp;
select * from generate_series where c in (select c from #tmp);


texts: How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted?

texts:
I have a website that uses ASP.NET membership and so there are multiple aspnet_... tables in our database.

We are creating a service for the users that allow them to essentially create laboratory testing orders for an employer. An employer can have multiple users that can order tests.

If someone effectively loses access to the service (they get fired, quit, retire, or get their permissions revoked by their employer) the laboratory testing orders that they created should remain in the system and so should the user's user name. This way when someone views a list of all the laboratory orders, they know what user ordered what testing and for who.

Right now, when a user loses access, we "physically" remove their user data row from the aspnet_users table and the aspnet_membership table. This removes their username, email, profile, password etc... We can't do this if we create a relationship between the aspnet_users table and the table that holds the laboratory orders. If we do, we either need to:

cascade the deletion (which will remove the orders that a user created)
set the value in the table that holds the laboratory orders to either "NULL" or some sort of default value. If we do that, we lose all of the user's information, such as their username etc... which we want to maintain.

If we opt to change to a "soft-delete" approach and just mark a user as not being allowed to log in (not approved), but keeping their user row in the aspnet_users and aspnet_membership` tables, is there any issue that might arise from this that I can't see? Security issues? Performance issues?

I generally think leaving user data in a system when the user cannot access the system is just... wrong. It leaves a bad taste in my mouth.

What is the best way to handle storing data like this that must be "persisted" even if the "parent" row is removed? Should I not use a relationship and just copy the user data to another table? If I do that there's a chance for inconsistent data and updates would be difficult. I can easily see it growing into a monster and impossible to maintain. There's also the chance of conflicts if a username is reused in the future.

Any advice would be very much welcomed.


texts: connection to Informix still active despite closing?

texts:
I have a small C# utility connecting (via the  IBM Data Server Provider for .NET) to a Informix DBS (version 11.7) and sending one or two queries to several databases. Even though I close each connection after the query is done, a call to onstat -u still lists my computer, with the flags Y and P (Waiting on a condition, Preparing/prepared, respectively) being set, one line for each query:

address          flags   sessid   user     tty      wait         tout locks nreads   nwrites
56413170         Y--P--- 704      user      MYWS  59dce6e8         0    1     64       0

The connections disappear when I end the program, or after a few minutes. I would expect my client not to show up immediately after closing the connection.

Is this a normal occurrence, or a sign that I might be doing something incorrectly?


texts: Combine a generated series with each row from a select

texts:
The below select is returning an array of all the projects owned by all my users.

WITH user_projects AS
(SELECT
  u.id as user_id
  ,COALESCE(array_agg(DISTINCT pa.project_id), '{0}'::integer[]) as project_ids
FROM users u
LEFT JOIN project_assignments pa on pa.user_creator_id = u.id
GROUP BY u.id
)

user: 1, project_ids: {1, 2}
user: 2, project_ids: {3, 4}

Using that select I would like to proceed a report, which will display for each user a 12 month summary.

SELECT
  to_char(pk.created_at,'Mon') as mon
 ,extract(year from pk.created_at) as yyyy
 ,up.user_id
 ,COALESCE(count(distinct pk.keyword_id), 0) as total_keywords
FROM user_projects up
LEFT JOIN project_keywords pk on pk.project_id = ANY(up.project_ids::int[])
GROUP BY up.user_id, up.project_ids, 1, 2

Output:

 mon: sept, yyyy: 2014, user_id: 1, total_keywords: 10,
 mon: , yyyy: , user_id: 2, total_keywords: 0,

The select from above is doing what I need but is not displaying all 12 months, only months for which it can find data in project_keywords and for users which don't have any data into project_keywords it will display 0 and the month and year columns will be empty.

So how can I produce a report for each user displaying the last 12 months since now(), and where no data is available fill the month and year columns?

I believe in order to do that I need to have a generate_series which will return the last 12 months so I did this:

(select created_at from generate_series(now()::date - INTERVAL '1 year',now()::date,'1 month') as created_at)

But now I'm stuck and I don't know how to combine all of this.

The expected output should be

For user 1:

mon: sept, yyyy: 2013, user_id: 1, total_keywords: 10,
mon: oct, yyyy: 2013, user_id: 1, total_keywords: 11
...
mon: aug, yyyy: 2014, user_id: 1, total_keywords: 0
mon: sept, yyyy: 2014, user_id: 1, total_keywords: 2

For user 2:

mon: sept, yyyy: 2013, user_id: 2, total_keywords: 10,
mon: oct, yyyy: 2013, user_id: 2, total_keywords: 0,
...
mon: aug, yyyy: 2014, user_id: 2, total_keywords: 0,
mon: sept, yyyy: 2014, user_id: 2, total_keywords: 0

http://sqlfiddle.com/#!15/8c911/6

Using PostgreSQL 9.3.


texts: "user does not exist " when calling DBMS_LOGMNR.START_LOGMNR

texts:
I have a CDB user called "MYADMIN" and I'm trying to make it connect to log miner.
-- enable calling admin username on CDB
ALTER SESSION set "_ORACLE_SCRIPT"=true
/
-- create unique table space for admin
CREATE TABLESPACE myadmints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON
/
-- create admin user on CDB
CREATE USER myadmin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE myadmints QUOTA UNLIMITED ON myadmints ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to myadmin
GRANT CREATE SESSION TO myadmin
GRANT CREATE TABLE TO myadmin
GRANT EXECUTE_CATALOG_ROLE TO myadmin
GRANT EXECUTE ON DBMS_LOGMNR TO myadmin
GRANT SELECT ON V_$DATABASE TO myadmin
GRANT SELECT ON V_$LOGMNR_CONTENTS TO myadmin
GRANT SELECT ON V_$ARCHIVED_LOG TO myadmin
GRANT SELECT ON V_$LOG TO myadmin
GRANT SELECT ON V_$LOGFILE TO myadmin
GRANT RESOURCE, CONNECT TO myadmin

I've selected one line from 'v$archived_log' and trying to load the file.
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/ARC000011_1061542581',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>3083464, EndScn=>3388245, Options=>DBMS_LOGMNR.DICT_FROM_ONLINECATALOG+DBMS_LOGMNR.NO_ROW_ID_IN_STMT);
END;

I can run it from the sys as sysdba user, but when I run it from my "myadmin" user I'm getting:
Error report -
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 3
01435. 00000 - "user does not exist"
*Cause:
*Action:

The error is about the START_LOGMNR line when I remove it there is no error.
Which privilege I'm missing?


texts: Using LIKE to search case insensitive over language specific characters (PostgreSQL)

texts:
Using a PostgreSQL database (Collation C, Encoding UTF8) we store data from various languages. This means we also get the special characters for free (e.g. Å, å, ...)

Within an application we now want to perform a case-insensitive search.  Meaning if the user types 'Å' in the search box, the query must also take into account 'å'.
However, using the lower(string) or upper(string) method completely ignores this values when casting.  And as such, no matching records are found...

Is there any way around this?
Using regex it seems one could use \p{L} to compare values, but I need it at the query level so I guess that's a no-go.

On a side-note: we are using Hibernate to create the query.


texts: create insertion rule on view returning id

texts:
I have a table private.products and a view public.products representing that table. And I want to create entries in private.products while inserting into view, it all works right, except that I also what to return the inserted id:

CREATE RULE insert_product
AS ON INSERT TO public.products DO INSTEAD
INSERT INTO private.products (name) VALUES (new.name) RETURNING products.id;
ERROR:  RETURNING list has too few entries

What am I doing wrong?


texts:
I can't seem to get this to work the way I need it to. I have two tables Assignment and Event.

Assignment
StartDate     EndDate       Equipment
4/1/2016      4/23/2016     1001
4/3/2016      4/10/2016     1002
3/30/2016     4/20/2016     1003

Event
StartDate     EndDate       Equipment    Event
4/2/2016      4/3/2016      1001         maintenance
4/8/2016      4/10/2016     1001         maintenance
4/4/2016      4/5/2016      1002         maintenance

I need to join on the equipment and have an output that divides the Assignment using the events. For this sample data I would have an output that looks something like this:

StartDate     EndDate       Equipment    EventStatus
4/1/2016      4/2/2016      1001         Active
4/2/2016      4/3/2016      1001         maintenance
4/3/2016      4/8/2016      1001         Active
4/8/2016      4/10/2016     1001         maintenance
4/10/2016     4/23/2016     1001         Active
4/3/2016      4/4/2016      1002         Active
4/4/2016      4/5/2016      1002         maintenance
4/5/2016      4/10/2016     1002         Active
3/30/2016     4/20/2016     1003         Active

So for Equipment number 1001; it was put into an assignment then had maintenance twice. That maintenance split the assignment into 5 parts like so:

---------------------------------------------------------------------
|  Active  |maintenance|  Active  |maintenance|       Active        |
---------------------------------------------------------------------

Each of those parts must have its own row with the start and end date for that section. How can I write a query to make this work?


texts: PostgreSQL autovacuum ignoring "inactive" tables resulting in "transaction id wraparound"

texts:
I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables that haven't been written to in a long time.

For example, I have a table that I can tell hasn't been written to since '2018-09-19 10:30:43.625069+00' thanks to the only inserts to this table always setting an updated_at column (current datetime is '2018-10-04 23:58:25.545881+00'), has n_dead_tup = 0, and has age(relfrozenxid) > 10000000.

I have autovacuum_freeze_max_age = 10000000 so this should mean this table will get autovacuumed, but instead I see it get past this point and then autovacuum doesn't get triggered for the table (it's currently at age(relfrozenxid) > 70000000 and autovacuum still hasn't triggered.

I currently have autovacuum set to be maximally aggressive and yet this is still happening. Here are the autovacuum settings:

track_counts = on
autovacuum = on
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_cost_limit = 10000
autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 1
autovacuum_freeze_max_age = 10000000
vacuum_freeze_min_age = 1000000

(yes, I realize autovacuum_vacuum_cost_limit doesn't matter because autovacuum_vacuum_cost_delay is 0)

Currently I'm forced to periodically manually trigger vacuums for these tables to keep things from getting out of hand. This feels like a hack, though.

Is there a way to get autovacuum to vacuum these tables, or equivalently a reason why autovacuum is ignoring these tables?

ETA

In case anyone comes here wanting to see my script for dealing with this, here it is:

#!/bin/bash -e
while true; do
  sleep 60 #just in case there's nothing sleep so we don't run this too hard; at the start to prevent rapid process bouncing when postgres is offline
  psql -d warehouse -t -c "select nspname || '.' || relname from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace where age(relfrozenxid) > 100000000 and relfrozenxid != 0 order by age(relfrozenxid) desc;" | sed '/^$/d' | parallel -j10 -q bash -c 'echo -n "autovacuum {} to prevent wraparound: " ; psql -d warehouse -c "vacuum freeze {}"'
  echo -n "vacuum postgres database: "
  psql -d postgres -c "vacuum freeze" #just to be safe
done

You'll probably want to up superuser_reserved_connections by 10 or whatever number you specify to parallel using the -j flag to make sure you can still access the database if regular connections are exhausted without shutting this down.


texts: Fetch ids(Primary Key) from table where ids not available in table within range

texts:
I have table named Student. It have primary key student_id which is auto increment and not null.

Now Student contains around 5000 records.

After passed out from university, we need to delete specific student record from Student and related child table as well.

It is working good.

I need to get those numbers who are not in student_id range.

For ex.

I have 5000 records. So assume range is 1-5000. In Student table, there may be data with primary key :

student_id :

1
2
4
5
6
7
9
12
15
etc...

So I want deleted ids, like : 3, 8, 10, 11, 13, 14, etc... And need this result in some text file.

Finally I have 2 questions :

Need query to get those deleted ids
Can I export result in some file after fired query ?