Should the filtering column(s) always be in the keys / includes? I'm considering creating a filtered index in my copy of the Stack Overflow database.  Something like this, for example:

CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
    ON dbo.Users (DisplayName)
    WHERE Reputation > 400000;

Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?

Yes!

For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes

The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.

Key lookups when the query predicate doesn't match the filter expression

First of all, the documentation has this to say about including filter expression columns:

  A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.

Including the Reputation column in the index (key or includes) removes the need for this lookup.

See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.

Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index

Key lookups when the filtering column is included in the resultset

The documentation goes on to say this:

  A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).

Poor row estimates when using equality expressions

There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan).  Including the filtering column can prevent these more-accurate estimates from being discarded.

More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index

An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows

Key lookups when using IS NULL in the filtering expression

Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup.  See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?

As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.


Splitting different parts of a string I have an SQL Server database where there is a cell with a delimiter (\) separated string. An example of this string would be:

category_path
=============
RootCategory\Middle Category\Child Category\Child of child category
RootCategory\Middle Category\Other Child Category\

There are a lot of category paths like this. I would like to parse all of them into an other table:

category_name            parent_path
====================================
RootCategory             null
Middle Category          RootCategory
Child Category           RootCategory\Middle Category
Child of child category  RootCategory\Middle Category\Child Category
Other Child Category     RootCategory\Middle Category

I have left out the duplicates here, but the result could contain duplicates (I will need to cursor over the rows of this table later, and I can call distinct there).

There are a lot of examples around the net where a string is simply splitted. (Here for example) I was thinking about writing a query where I split the string, cursor over the results and accumlate the parent_path in each step, but that seems to be very suboptimal.

Is there a solution where I wouldn't have to declare so many cursors for a simple string?

Splitting a string with a recursive CTE is usually a bad choice but this is a case where I at least would consider a solution using that.

If you used a faster string split technique you would have to preserve the order of items and rebuild parent_category using for xml. That might still be faster than using a recursive CTE but here is the recursive stuff anyway.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  category_path nvarchar(max)
);

insert into YourTable values
('RootCategory\Middle Category\Child Category\Child of child category'),
('RootCategory\Middle Category\Other Child Category\');

Query 1:

with C as
(
  select left(category_path, charindex('\', category_path+'\') - 1) as category_name,
         stuff(category_path, 1, charindex('\', category_path+'\'), '') as category_path,
         cast(null as nvarchar(max)) as parent_path
  from YourTable
  union all
  select left(category_path, charindex('\', category_path+'\') - 1),
         stuff(category_path, 1, charindex('\', category_path+'\'), ''),
         coalesce(parent_path+'\', '')+category_name
  from C
  where category_path <> ''
)
select distinct
       category_name,
       parent_path
from C;

Results:

|           CATEGORY_NAME |                                 PARENT_PATH |
-------------------------------------------------------------------------
|          Child Category |                RootCategory\Middle Category |
| Child of child category | RootCategory\Middle Category\Child Category |
|         Middle Category |                                RootCategory |
|    Other Child Category |                RootCategory\Middle Category |
|            RootCategory |                                      (null) |


plpgsql - stored procedure Comma separated values in parameters Im writing a stored procedure to get database and table names in variables and then I'll push these values into IN clause.

CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100))
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
   tables_s varchar(100);

 BEGIN
 select '''' + REPLACE(tbls, ',', ''',''') + '''' into tables_s;
    FOR rec IN select table_schema, table_name from information_schema.tables where table_catalog in (db) and table_type='BASE TABLE' and table_schema not in ('pg_catalog','information_schema')  and table_name in (tables_s)
   LOOP
   select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
 $$;

If I call this procedure, its just showing CALL. No results.

But in my FOR LOOP, if I remove the and table_name in (tables_s), Im able to print all table names and schemas.

But something wrong with the multiple table names in the variable. Without that its working.

Can someone help me to figure it out what was wrong in my script?

The best solution is to not pass a single varchar parameter but an array:

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text[], p_tbls text[])
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name
               from information_schema.tables
               where table_catalog = any (p_catalogs)
               and table_type='BASE TABLE'
               and table_schema not in ('pg_catalog','information_schema')
               and table_name = any (p_tbls)
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;

Then you can call it e.g. like this:

call bhuvi(array['schema_1',schema_2'], array['table_1', 'table_2']);

If you can't change the calling code to deal with array, convert the comma separated strings to arrays

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text, p_tbls text)
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name
               from information_schema.tables
               where table_catalog = any (string_to_array(p_catalogs.','))
               and table_type='BASE TABLE'
               and table_schema not in ('pg_catalog','information_schema')
               and table_name = any (string_to_array(p_tbls, ','))
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;


Contradictory reports from MySQL that a binlog file exists Our replication recently stopped working.  I submitted a question but was unsuccessful in fixing the problem (Error 1236 - "Could not find first log file name in binary log index file").  Since the occurrence of the events described in the question, I've executed a RESET MASTER on the master, a RESET SLAVE on the slave, and created a new full dump, but am still getting the same results.

I thought I'd take a step back and try taking a different approach, attacking the specifics of the error message itself.

When I execute SHOW SLAVE STATUS on the slave, it reports that the master cannot "find first log file name":

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

The "first log file name" is /var/lib/mysql/mysql-bin.000001:

root@master [905 13:30:56 /var/lib/mysql]# cat mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004
/var/lib/mysql/mysql-bin.000005
/var/lib/mysql/mysql-bin.000006

MySQL itself seems to be aware that mysql-bin.000001 is the first binary log:

MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |      10421 |
| mysql-bin.000002 | 1073919628 |
| mysql-bin.000003 | 1074488806 |
| mysql-bin.000004 | 1073744707 |
| mysql-bin.000005 | 1074366770 |
| mysql-bin.000006 | 1069984818 |
+------------------+------------+
6 rows in set (0.00 sec)

mysqlbinlog shows that this file seems to be accessible and valid:

root@master [911 13:48:04 /var/lib/mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160526 12:24:14 server id 5  end_log_pos 248   Start: binlog v 4, server v 10.0.21-MariaDB-log created 160526 12:24:14 at startup

MySQL itself also seems to have access to this first log file:

MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000001' from 0 limit 4;
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc       |         5 |         248 | Server ver: 10.0.21-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000001 | 248 | Gtid_list         |         5 |         273 | []                                             |
| mysql-bin.000001 | 273 | Binlog_checkpoint |         5 |         312 | mysql-bin.000001                               |
| mysql-bin.000001 | 312 | Gtid              |         5 |         350 | BEGIN GTID 0-5-1                               |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

Question

To recap, MySQL is complaining that it "Could not find first log file name in binary log index file".  However, as shown above, it appears that MySQL does know what the "first log file name" is, and it can, indeed, access it.

What else should I check to ensure that MySQL can actually "find [the] first log file name"?

As it turns out, the problem was that we were connecting to an old (wrong) master.  The previous master was domain.com, but many months ago, we migrated to db.domain.com.  It seems that we were erroneously connecting to domain.com.

We’re using autossh to set up SSH tunnels for our replication.  To confirm that we were connecting to the old (wrong) master, I executed telnet 127.0.0.1:3305 on the slave.  From this, I could see that the reported MySQL version matched the version from the old master.  I did a full reboot of the slave which seems to have fixed the problem.  I think the root of the problem was likely due to some DNS quirks on our network - it would seem that when the autossh connection was established, db.domain.com resolved to domain.com, hence the symptoms of connecting to the wrong master.  (Certainly, these DNS issues on our network are something to look into)

Note: Major thanks to @Andrew for his answer on the original question: https://dba.stackexchange.com/a/140259/55530


Stored Procedure to evaluate column if condition is true execute another stored procedure I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case statement but I get a invalid syntax near CASE and according to this response executing a stored procedure in a select or case clause is not allowed how would I go about executing a stored procedure on a table if a condition is met.

This is the stored procedure:

 SET NOCOUNT ON;

 UPDATE Supply
 SET [Quantity] = [Quantity] - [SupplyHalfLife]

 UPDATE Supply
 SET SupplyAlert = CASE WHEN Quantity <= SupplyQuantityAlert THEN 'Y' ELSE 'N' END

and this is what I want to add to it:

SELECT a.SupplyName
CASE a.SupplyAlert
WHEN 'Y' THEN dbo.sp_SendMail @body = a.SupplyName END
FROM Supply a

which checks column for a alert status of 'Y' and executes a stored procedure.

Code I finally used:

DECLARE @name varchar(255)

DECLARE cur CURSOR LOCAL FOR
SELECT SupplyName FROM Supply WHERE SupplyAlert = 'Y'

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC dbo.SendMail @name

    FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

link

how would I go about executing a stored procedure on a table if a condition is met.

if exists (select 1 from Supply where SupplyAlert = 'Y')
begin
exec dbo.sp_SendMail
end
else print 'Nothing to send !'

Remember that CASE is a function and not a control flow statement.


Group fields obtained after join as attributes of the main entity object I work with Postgres 10.

I have 2 entities Item and Image. An Item can have multiple Image(s) so I have a Foreign key from Image to Product.

I have the following query:

SELECT name, i.image, i.type FROM item AS t
 INNER JOIN image AS i on t.id = i.item_id

By default I obtain records by Image(s), meaning the same Item can appear multiple times.

I want in the results the image to be a property of an Item object:

item.images and loop over it

It is possible to do that at the database level ?

In PostgreSQL, it is possible to create an array containing tuples:

SELECT name,
       (SELECT array_agg((image, type))
        FROM image
        WHERE item_id = item.id
       ) AS images
FROM item;

Alternatively, use an array containing another array:

SELECT name,
       (SELECT array_agg(ARRAY[image, type])
        ...

(Whether your language's database driver supports any of these types in the result is another question …)


Postgres Upsert Performance Considerations (millions of rows/hour) I have tried doing some research for quite a while on the performance implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when a lot of upserts are made. The scale is something like 1 million records/hour, that is split up in groups of around 300 records each.

The table is fairly simple, just a few int/bool fields and a date field. The total size of the table is somewhere between 50-60 million records. Essentially, all the rows are supposed to stay up-to-date within a certain cycle (that is currently about 2 days). Sometimes a lot of information changes, sometimes very little/no information changes (e.g. 300 records get upserted but they are identical to the existing records).

So far, one hypothesis is that this project seems to be suffering from the large amount of writes that happen constantly since even if the upsert results in no inserts/updates, the "failed" inserts from the upsert will still get written somewhere (according to our knowledge). Therefore, the idea is to utilize old-fashioned upserts (writeable CTEs) and do more granular operations that can make sure to only insert data that doesn't already exist, and only update data that has actually changed. Naturally, however, this will put more read-load on the DB and increase query complexity.

The question is, are we right in our assumptions that the built-in upsert is useless from a performance perspective (e.g. it's only good for much smaller tasks) or are we wrong? I read a bit about HOT updates and autovacuum tuning, but nothing that references something more similar to this question.

Worth mentioning is that this DB (PostgreSQL 10.9) is running on Heroku so we are not able to tune it to our needs. We are planning to move at some point, depending on how important it ends up being. Finally, it is also worth mentioning that this DB also has one follower (i.e. read replica).

Would really appreciate some good insight on this! Thanks beforehand.

INSERT ... ON CONFLICT is the fastest way to upsert data. If it is fast enough for you can only be seen from a performance test.

It matters a lot if you use ON CONFLICT DO NOTHING or if you use an UPDATE clause:

With DO NOTHING, there won't be any dead tuples that have to be cleaned up. If you perform an UPDATE, there will be a dead tuple, and cleaning up these dead tuples may turn out to be the bottleneck in your processing.

Use a fillfactor of 70 or so and make sure that none of the updated columns are indexed. Then you can get HOT updates which may save your day.


Transforming Huge Database table to Another Table We are migrating lot of data from our old database to new database.

Presently, both of them are actively[writing/reading] used based on flows/activity as migration is in progress.

Here's the real challenge for me now. We have old database table which's still getting used now.

  Atleast 1,00,000 new records are getting inserted every day and we
  need to move 6months old data [01-01-2014] to new database.

We are using MySQL in both places. The new table to old table need some logical-transformation of data which will be carried through Java-JDBC, as the table structures and functionality is changed.

What are the ideal steps that I need to follow and taken care to do this transformation as quick as possible without fail.

As per my calculation, we will be migrating atleast 2,00,00,000 records.

  Some info:

Presently index exists only on ID column. creation_date exists without index
How to fetch efficiently these records, a better query to fetch??
Efficient way to transform using multi-threading
Efficient way to write the data into new database

Since existing records are static they can be converted to the new schema any time after the migration process is signed off.  Specifically, as soon as the new DB is available you can start loading it with old data.  Start with the oldest and work your way forward one day at a time.  Assuming it takes less than twenty four hours to convert one day's data, you can run this process non-stop until you have caught up with today's data.

Thereafter you can continue with a more-or-less parallel run, with two options.  Option "A" is to load each new day's data into the old DB, then run the convertion process to get it into the new DB.  Option "B" is to run the old ETL to load today's source file into the old DB and run the new ETL to load the same soruce file into the new DB.  Option "B" has the advantage of testing your new ETL process, too.

Then on cut-over day you will have at most one day's worth of data to move.

As for the convertion process itself I would suggest you read all the data for one business transaction from the old tables (i.e. a referentially in tact set), convert it, then write it entirely to a set of staging tables in the new schema.  Note not to the live tables themselves but to a parallel set of staging tables.  Then you can write lots of reconciliation checkes against these staging tables.  If there's a problem you can bulk-delete the staging tables without affecting the new DB's data, fix the convertion and re-run.

Once the reconciliation completes successfully you can bulk-copy from the staging to the live tables.  The staging tables can have additional columns to help with reconciliation that you would not want in the live tables (source surrogate ID, for example).  Retain the staging tables for a few months for any post-mortem on your go-live problems.

I know it sounds like all this staging and reconciliation is eating precious time during a cut-over.  And it is, but is nothing compared to the time it will take to post-fix two hundred million rows in an active production system.


Trigger to update only if value is in lookup table I only want to update the comment if a description for the event is found in the TagDescLookup Table.  If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column.  The current trigger will update the comment column with the correct value unless the value isn't found in the lookup table, it sets comment to Null.  I'm using SQL server 2017.

USE [AwxLogger]
GO

/****** Object:  Trigger [dbo].[TagDescTrigger]    Script Date: 14/03/2019 16:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TagDescTrigger]
    ON [dbo].[EventLog]
    AFTER INSERT
    AS
       BEGIN
           SET NOCOUNT ON
              UPDATE [dbo].[EventLog]
        SET Comment =
                  (
            SELECT [Description] FROM TagDescLookup
            WHERE Tag = I.NameOfParameter
            )
        FROM [dbo].[EventLog] AS E
        JOIN INSERTED AS I
              ON E.NameOfParameter = I.NameOfParameter
       END

TagDescLookup Table

ID int NOT NULL PRIMARY KEY IDENTITY (1,1)
Tag nvarchar(MAX) NOT NULL
Description nvarchar(MAX)

You could change the trigger to only update when the NameOfParameter matches the Tag from TagDescLookup

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TagDescTrigger]
    ON [dbo].[EventLog]
    AFTER INSERT
    AS
       BEGIN
       SET NOCOUNT ON

        UPDATE E
        SET E.Comment =TDL.[Description]
        FROM
        [dbo].[EventLog] AS E
        JOIN INSERTED AS I
        ON E.NameOfParameter = I.NameOfParameter
        JOIN TagDescLookup AS TDL
        ON TDL.Tag = I.NameOfParameter;
        END
    END

You really need to look out for duplicate Tag's.

Testing

CREATE TABLE  TagDescLookup
(ID int NOT NULL PRIMARY KEY IDENTITY (1,1)  ,
 Tag nvarchar(MAX) NOT NULL  ,
 Description nvarchar(MAX)  );

 CREATE TABLE [dbo].[EventLog]
(Comment  nvarchar(MAX),
NameOfParameter nvarchar(max));

Example1

  INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
  VALUES('bla','bla');

  select * from dbo.EventLog;

Result1

Comment NameOfParameter
bla bla

Example2

  Insert into TagDescLookup(Tag,Description)
  VALUES('TaggedVal','NewDescription');

  INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
  VALUES('OldDescription?','TaggedVal');

  SELECT * From dbo.EventLog;

Result2

Comment NameOfParameter
bla bla
NewDescription  TaggedVal


Use of smallserial gives error with PostgreSQL I know the very basics of databases. I worked in the past with MySQL and recently switched to PostgreSQL (9.1) with PostGIS 2.0.1.

At the moment I'm designing a new database and I use the tool pgModeler for that. In MySQL I could use autoincrement for my primary keys, but PostgreSQL doesn't have that. Some searching showed me that I should use serial instead.
There are some small lookup tables in my design which have a smallint as primary key. So I switched them to smallserial, which is an option in pgModeler.
Now I'm trying to execute my query and it returns with the error:
ERROR: type "smallserial" does not exist

According to the documentation I see now that smallserial is not listed in table 8.2 but later on in the document (8.1.4) it is mentioned. Is this an small error in the documentation or am I missing something like some setting somewhere?

Smallserial has been introduced in core with Postgres 9.2. In earlier version you could emulate it as it is only a short form. Documentation of 9.2 gives a hint for it


sqllogship.exe - how to get it to encrypt log file when backing up log on source server? SQL Server 2014 and 2016, standard edition:

How do I get sqllogship.exe to use built in sql server encryption when backing up and restoring log? (as part of minute to minute log shipping)

No way to do this natively through the log shipping GUI or command line with logship.exe

You can roll your own "poor man's log shipping" scenario. Basically all you are doing is backing up, copying, restoring with standby or norecovery and building some alerting.

An older post but an example of what I mean.


What's wrong with my provided admin password for Oracle Apex instance administrator account? I have installed Oracle Database 19c Enterprise Edition on Oracle Linux 7.8.I am now trying to install Apex 20.1 .
Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance administrator password. Executed the @apxchpwd.sql command, it asks me for user name, e-mail and password but then it gives an error as if the password does not have enough complexity, although the password I provide is literally H29dN8%Ih^^IV$Y3$j4Mx. Tried various other passwords, nothing seems to be acceptable.
What can be the problem?
Here is how I got to the error:
[oracle@oraclemachine apex]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 25 09:50:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apexadmin
User "apexadmin" does not yet exist and will be created.
Enter apexadmin's email [apexadmin] a@b.c
Enter apexadmin's password []
--------------------------------------------------------------------------------
PASSWORD_COMPLEXITY_ERROR
* PASSWORD_ONE_ALPHA_ERR
* PASSWORD_ONE_PUNCTUATION_ERR
* PASSWORD_ONE_UPPER_ERR
* PASSWORD_ONE_LOWER_ERR
--------------------------------------------------------------------------------
declare
*
ERROR at line 1:
ORA-20001: Password validation failed.
ORA-06512: at line 30
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 3744
ORA-06512: at line 20

Nothing is wrong with that password. But just to be sure, check the variable if it really contains the password you entered (print :PASSWORD):
Thank you for installing Oracle Application Express 20.1.0.00.13

Oracle Application Express is installed in the APEX_200100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:06.97
timing for: Complete Installation
Elapsed: 00:05:01.54

PL/SQL procedure successfully completed.

1 row selected.

...null1.sql

Then:
SYS> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apexadmin
User "apexadmin" does not yet exist and will be created.
Enter apexadmin's email [apexadmin] a@b.c
Enter apexadmin's password []
Created instance administrator APEXADMIN.

SYS> print :PASSWORD

PASSWORD
--------------------------------------------------------------------------------
H29dN8%Ih^^IV$Y3$j4Mx

SYS>


Could not open file "pg_clog/0000": No such file or directory My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed.

I have checked the log and found:

2014-05-28 10:16:03 CEST LOG:  database system was interrupted while in recovery at 2014-05-28 10:11:41 CEST
2014-05-28 10:16:03 CEST HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2014-05-28 10:16:03 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2014-05-28 10:16:03 CEST LOG:  redo starts at 0/25544D08
2014-05-28 10:16:03 CEST LOG:  file "pg_clog/0000" doesn't exist, reading as zeroes
2014-05-28 10:16:03 CEST CONTEXT:  xlog redo commit: 2014-04-24 18:48:48.775+02; rels: base/16855/140563 base/16855/140562 base/16855/140561 base/16855/140560 base/16855/140559 base/16855/140558 base/16855/140557 base/16855/140556
2014-05-28 10:16:03 CEST FATAL:  the database system is starting up
2014-05-28 10:16:04 CEST FATAL:  the database system is starting up
2014-05-28 10:16:05 CEST FATAL:  the database system is starting up
2014-05-28 10:16:06 CEST FATAL:  the database system is starting up
2014-05-28 10:16:07 CEST FATAL:  the database system is starting up
2014-05-28 10:16:08 CEST FATAL:  the database system is starting up
2014-05-28 10:16:12 CEST FATAL:  the database system is starting up
2014-05-28 10:16:13 CEST FATAL:  the database system is starting up
2014-05-28 10:16:14 CEST FATAL:  the database system is starting up
2014-05-28 10:16:15 CEST FATAL:  the database system is starting up
2014-05-28 10:16:16 CEST FATAL:  the database system is starting up
2014-05-28 10:16:18 CEST FATAL:  the database system is starting up
2014-05-28 10:16:19 CEST FATAL:  the database system is starting up
2014-05-28 10:16:20 CEST FATAL:  the database system is starting up
2014-05-28 10:16:21 CEST FATAL:  the database system is starting up
2014-05-28 10:16:22 CEST FATAL:  the database system is starting up
2014-05-28 10:16:23 CEST FATAL:  the database system is starting up
2014-05-28 10:16:24 CEST FATAL:  the database system is starting up
2014-05-28 10:16:25 CEST FATAL:  the database system is starting up
2014-05-28 10:16:26 CEST FATAL:  the database system is starting up
2014-05-28 10:16:27 CEST FATAL:  the database system is starting up
2014-05-28 10:16:28 CEST FATAL:  the database system is starting up
2014-05-28 10:16:29 CEST FATAL:  the database system is starting up
2014-05-28 10:16:30 CEST FATAL:  the database system is starting up
2014-05-28 10:16:31 CEST FATAL:  the database system is starting up
2014-05-28 10:16:32 CEST FATAL:  the database system is starting up
2014-05-28 10:16:33 CEST FATAL:  the database system is starting up
2014-05-28 10:16:34 CEST FATAL:  the database system is starting up
2014-05-28 10:16:35 CEST FATAL:  the database system is starting up
2014-05-28 10:16:36 CEST FATAL:  the database system is starting up
2014-05-28 10:16:37 CEST FATAL:  the database system is starting up
2014-05-28 10:16:38 CEST FATAL:  could not access status of transaction 0
2014-05-28 10:16:38 CEST DETAIL:  Could not open file "pg_clog/0000": No such file or directory.
2014-05-28 10:16:38 CEST CONTEXT:  xlog redo zeropage: 4
2014-05-28 10:16:38 CEST LOG:  startup process (PID 8024) exited with exit code 1
2014-05-28 10:16:38 CEST LOG:  aborting startup due to startup process failure

It is not a real issue if I have lost the database content, because I use it for development purposes only.

How should I proceed with this? Should I reinstall PostgreSql 9.2? Is there a command to restore it in a proper state?

If pg_clog is gone..... thats bad, this stores the commit log so the database knows what has and has not been committed.  I would start looking at backups now, or if the data is disposable and there is no backup then building a new cluster.  There are ways you can get the data out, but as far as I know they are very time consuming.

The bigger question though is what happened to your machine to cause this?


Adding new tables -- memory usage increases I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema.

Does adding more tables increase memory usage? I am using about 1.7 gb of ram for mysql where before I was using 1.4-1.5gb

Does it increase memory ??? Absolutely !!!

I wrote a post about INFORMATION_SCHEMA (Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?)

In that post I explained how metadata for MySQL tables are stored in temporary tables using the MEMORY storage engine. For example, when you create a table mydb.mytable, look at what gets populated into these tables:

INFORMATION_SCHEMA.TABLES (One row for mydb.mytable)
INFORMATION_SCHEMA.PARTITIONS (If mydb.mytable is partitioned, one row for each partition)
INFORMATION_SCHEMA.COLUMNS Info about every column in mydb.mytable
INFORMATION_SCHEMA.STATISTICS : Information about index statistics in mydb.mytable
INFORMATION_SCHEMA.KEY_COLUMN_USAGE : Information about a column's use in all indexes in mydb.mytable
See the full list of INFORMATION_SCHEMA tables

In short, all these tables have to increase in memory usage for each table you create. In your particular case, that would be multiplied by 200.


Will an hourly T-Log backup in a Maintenance plan write a file if there are no changes to a DB? I have an hourly maintenance plan the runs a T-Log backup. Some of my databases may have every hour represented in the file system, while others have some hours "missing" or even none at all. Is it possible that the maintenance plan won't write a T-Log backup if there have been no transactions? If this is not the case, do I have a problem that doesn't seem to be called out in the error log? I'm concerned with some DBs that are marked as full recovery.

Review the backupset and related tables in msdb as the source for if a successful backup was taken.  If you see gaps in when a scheduled backup was to have occurred, your job either failed or never ran.

Every backup command generates an internal checkpoint, which in turn, moves the log sequence number (lsn).  Review the first_lsn and last_lsn pattern with every log backup in the backupset table.  Likewise, a file will always be generated if your job backs up to a time-stamped filename (default behavior of maintenance plans).  You can verify this behavior by manually/interactively running a log backup command multiple times on a database without an transactions between runs.


Postgres 9.2 under FreeBSD 8.4 - How do I find the databases & tables? I'm trying to create an osm tileserver and am at the point where i've created the database to hold the output of osm2pgsql.  But I can find neither the database (map_data) nor, of course, its tables.  I need to softlink (ln -s) the tables to at least one other spindle, which has nearly a full TB of space open.  I look in pgsql/data/base and see a lot of subtrees with numeric names.  Presumably one of them is my map_data, but I can't find a name:number mapping table.

I'm used to MariaDB/MySQL, which seems much more straightforward to me.

Don't mess with the data files in the filesystem it's a sure way to get your database corrupted.

You can achieve what you want, by creating a tablespace and then move the table to the new tablespace:

create tablespace big_one LOCATION '/path/to/other/spindle';
alter table map_data set tablespace big_one;
commit;

Btw: MySQL isn't "more straightforward" - it's just different. And I could list several things that are much more "straightforward" in Postgres than in MySQL


Billing Module (Fee) for School Management System I am working on a School Management software which includes module for billing of students i.e, fee collection from students.

The fee is collected on monthly basis (12 times a year) and the total monthly fee is combination of various charges, fixed as per class and month. Some of the charges include Tuition Fee, Bus Fee, Printing Charge or others. A fixed amount of late fee is also charged on per month basis starting from the date, which can vary from month to month. Bus fee is charged as per the bus category of a particular student. There is a provision for partial payments as well.

My current approach is like this:

A master table to store fee settings, which include month & class and the fee settings.

feeMaster
    fid  -> Primary key
    month_year -> Stores Month Year
    stu_class -> Class of Student
    tuition_fee -> Tuition fee for that class
    tuition_fee_percent -> Percentage of Tuition fee to take, defaults to 100%
    bus_fee_percent -> Percentage of bus fee
    late_fee_start -> Day of month from which to charge late fee
    late_fee -> fixed late fee on per month basis
    printing_charge -> Printing charge if any
    other_fee -> Other fee if any
    other_fee_reference -> Other fee reference

Every time a student comes to pay his/her fee, calculation is made and a transaction takes place in the system. The details of the transaction are stored in two tables.

Transaction master table is used to store the transactions

transMaster
    tid -> Primary key
    purpose -> purpose of transaction, monthly fee
    amount -> amount of transaction
    type -> transaction mode / cash / cheque / dd
    created -> date

The details of this transaction are stored in another table as

studentFeeDetails
    sfid -> unique id
    tid -> transaction id from transMaster table
    fid -> fee id from fee settings table feeMaster
    tuition_fee -> calculated tuition_fee
    bus_fee -> calculated bus_fee
    printing_charge -> calculated printing_charge
    other_fee -> calculated other_fee
    late_fee -> calculated late_fee
    total_fee -> total fee calculated
    discount -> discount if given any
    amount_payable -> net amount payable
    amount_paid -> paid amount
    balance -> balance - if paid amount is greater or lesser than the original one,
               it is stored here
    status -> status - true if partial fee else false
    created -> date of creation

This is the current architecture of the module. There is no accounting practices involved and hence creates lots of problems for our accounting deptt.

To report the total payable fee for a month, every time, the system runs calculation algorithm on all the students and comes up with figure.
To find the pending fee for a class, the system again first checks the receivable fee for that class and removes the entries found in studentFeeDetails table to generate the pending report.
There is no proper segregation of fee heads under this system.
Now the current system needs to be converted into proper accounting system which can track the advances and balances.

I am thinking of a system where every month a posting process debits every student's account with the fee due for that month and on every late fee start date another process debits late fee to a student's account, if the fee is still pending.

This approach can keep a check on receivables, pending and received fee.

Please help, if the approach is correct and how to go with it. I am stuck with the db schema part and its implementation.

In addition to seconding Joel Brown's comment I would like to suggest a couple things to help.

If this is for internal use only start with open source accounting and ERP db's.  For example, LedgerSMB or Adempiere.  Also do this if you are releasing open source.  If you are not releasing open source, consider interoperating with such software for billing.  I would be happy to help (conflict of interest alert).
Look specifically at batch processing and review requirements.  This allows human review as to how much money is owed before the money hits the books.

One thing we are doing in LedgerSMB is to have the possibility of "template transactions" which can then be copied out and filled in (possibly by automatic processes).

Accounting is a large field but if you are handling billing you really need to know it.

Along these lines, I don't really see anything wrong with what you have posted but it is quite incomplete.  You need line items for the invoices, and you need line items for the payments.  In this way it becomes a lot simpler to track how much is owed.  You may also want to separate out students from customers in case one parent has multiple children enrolled in the school.


Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)? We have a system on AWS RDS Aurora, PostgreSQL v 9.6.8 option.

We are trying to move its roles to a new encrypted 10.5 instance, but we don't have superuser access that's required to do pg_dumpall -g on the old instance. (I am aware of the update to Postgres 10.0 that lets you do this without superuser access, but this is 9.6.8).

Any ideas for a workaround? Thanks in advance for any suggestions whatsoever!

It turns out the 10.0 solution was backported (apparently) to 9.6.8, so:

pg_dumpall --host <aws_endpoint> --globals-only --no-role-passwords \
    --username masteruser >roles.sql

Then run roles.sql against the new database.


How can I pass object to procedure from trigger? I am using mysql 5.6.
After insert or update, inside the trigger I am calling procedure, So how can i  pass NEW and OLD object from trigger to procedure ?

I found this: SOURCE

  There is great reason why you should never call stored procedures from
  within triggers.

  Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB,
  you will experience a proportional volume of shared row locks and
  annoying intermittency from exclusive row locks. Such would be the
  case if triggers were manipulating tables with INSERTs and UPDATEs
  being stagnated to perform heavy duty MVCC inside each call to a
  trigger.

  Don't forget that Triggers require overhead. In fact, According to MySQL Stored Procedure Programming, page 256 under the head "Trigger
  Overhead" says the following:

  It is important to remember that, by necessity, triggers add overhead
  to the DML statement to which they apply. the actual amount of
  overhead will depend upon the nature of the trigger, but --- as all
  MySQL triggers execute FOR EACH ROW --- the overhead can rapidly
  accumulate for statements that process large numbers of rows. You
  should therefore avoid placing any expensive SQL statements or
  procedural code in triggers. An expanded explanation of trigger
  overhead is given on pages 529-531. The concluding point from that
  section states the following:

  The lesson here is this: since the trigger code will execute once for
  every row affected by a DML statement, the trigger can easily become
  the most significant factor in DML performance. Code inside the
  trigger body needs to be as lightweight as possible and -- in
  particular -- any SQL statements in the trigger should be supported by
  indexes whenever possible.

Check RolandoMySQLDBA answer.


Selecting one variant out of different similar results I have a table that looks as follows:

Book    Format    Contents
A       html      ...
A       markdown  ...
B       html      ...
C       markdown  ...

I want to return only 1 version of a book. If a markdown version exists, I want markdown, otherwise I want html.

I'd love to see a SELECT statement that does this, and also a reason why it works.

I think this query is very close to what I need, but I don't understand how it works.

Thanks!

One option is to use window functions to select those books:

select book, format, contents
from (
  select book, format, contents,
         row_number() over (partition by book order by case when format = 'markdown' then 1 else 2 end) as rn,
         count(*) over (partition by book) as format_count
  from books
) t
where rn = 1
   or format_count = 1;

The window function row_number() in the inner query assigns a number to each format. markdown will get a 1 everything else will get a 2.

The outer select then gets those rows where there is a markdown available or where only a single format is available.

Another (faster) option is to use Postgres' distinct on operator:

select distinct on (book) book, format, contents
from books
order by book,
         case when format = 'markdown' then 1 else 2 end;

This again sorts on an expression that will give markdown a higher "priority" than html.

The first solution is portable as it is ANSI SQL. The second one is Postgres specific.

SQLFiddle example: http://sqlfiddle.com/#!15/7a027/3


Does shardCollection acquire lock in mongo db We are planning to shard a collection that contains few hundred gigs of data. I am going to use sh.shardCollection api for this. This is supposed to take hours to complete. I couldn't find any reference in the docs explaining whether the command acquires lock on the collection or not.

In short, all I want to know is whether collection is going to be available for read and write during while shardCollection is executing or not.

Yes, the collection is available during the process. Actually, that command sh.shardCollection will take only milliseconds to execute, but actual balancing work will take hours or days to be ready. Balancing will not lock collection, it's just moving chunks from one shard to other one.

Just be careful when selecting sharding key, you should have good knowledge of basics of sharding and how it works.


Database design appoach confirmation I am working for a mortgage company in migrating a legacy application to new application for managing claims, means lender fore close a loan and request for a claim to get its funds from government or private firm based on the insurer. If government is insurer, claim is made to government, if private firm is insurer its made to private.

Currently in the legacy application, claim is taken as parent and loan is taken as child. But i want just opposite.I want loan as parent and  claim as child. There will be many claims for a loan, but single loan for a claim.

Current structure is claim id and loan id will be there in claim table.In case of different types of claims, there are different tables (claim type tables), which i need to eliminate. Means, suppose it is a government claim, it has another table with government insurance related columns , plus the claim related columns. Similarly for private claim,  it has another table with private insurance related columns , plus the claim related columns. So the claim related columns appear in each type of claim related tables, here it is government and private which are common.

So my approach to design the database to make loan as parent and claim as child is as below:

Group all loan related columns to a table called loan
Group all claim related columns from different claim type tables to another table called claim
Remaining columns in claim type tables will be group as different tables along with loanid and claimid as forien key.

Is this approach is correct or is there any better approach?

From the comments added to the original question, I gather that the central problem with the legacy design centers around types and subtypes (often called classes and subclasses) of claims.  This is a classic problem in relational design, Often, new database designers will have had some formal learning concerning many-to-many relationships, data normalization, and similar topics, but no formal learning regarding classes and subclasses.

The Info tab under the Subtypes tag has a brief summary of the problem.  It mentions inheritance and object modeling in passing.  I want to expand on this a little.  The relational data model, in its original form, has no support for inheritance, although some DBMS products have extended the model with inheritance features.

Fortunately, the problem is well understood, and you can gain valuable insights from people who have studied the problem in depth.  I particularly like Martin Fowler's treatment.  He bridges the gap between how programmers think and how data architects think.

In your case, it appears from your description as though there are a bunch of claim subtype tables, but there is no generic claim table.  I'm going to recommend one particular design for your evaluation.  It's called "class table inheritance".  And I'm going to also recommend a technique called "Shared Primary Key".

In class table inheritance, there will be one table for the generic data that applies to all claims, no matter which subclass they belong to.  This table will have a claim ID, used to point to a row, like the usual table.  The other columns in the table will contain the attributes that are common to claims regardless of what class they belong to.

The other tables contain data that pertain to particular classes (types) of claims.  The ID column of these subclass tables deserves particular attention.  It's not populated in the usual way, by having the DBMS assign a unique number to it.  Instead it is an "inherited" copy of the ID column of the Class table.  This column is both the primary key of the subclass table and a foreign key that references the appropriate row of the claims table.

When you want to deal with a particular class of claim, just join the claims table with the appropriate subclass table.  The irrelevant claims will drop out of the join.

The reason I put "inherited" in quotes is that it requires some programming on your part to cause this inheritance to take place when a new claim is added.   You have to add the row to the claim table, then obtain the automatically generated ID, then propagate it to the subclass table, before making the insert into the subclass table.

That's my recommendation, but it's your call.


Downgrading SQL Server 2012 to Lower Edition I have a Win2008 R2 server with an enterprise edition of SQL Server 2012 installed. Because my production server is going to be standard edition, is there a way to downgrade editions from enterprise to standard? I have databases and users already configured in the enterprise instance, and analysis and native reporting service installed and configured.

Is this downgrade possible, or am I looking at a total re-install? Is there anything special I would need to do with my databases other than detaching prior to un-install and then attaching in the standard instance?

You cannot directly change edition of SQL Server 2012 from Enterprise to Standard. See this page for the supported edition change scenarios.

There are a few different ways to go about doing this -- some recommend making copies of the system databases and overwriting them on the new instance (make sure you're at the exact same SP/CU level), or you could just set up a new environment from scratch. It really depends on how complex your environment is. I would definitely recommend testing the process in a separate environment if you can.

Also, before you start, go through your databases and check sys.dm_db_persisted_sku_features to make sure there aren't any Enterprise edition features in use.


Okcupid.com (can find matches without accessing a database!?) I'm doing a research project on a popular dating site called: OkCupid
I would like to talk about how the database is used.
After reading this explanation from the Co-Founder of the site, I became very confused.
The statements:

  when a user performs a match search on OkCupid, we have to do the
  following:

  -Retrieve (from somewhere not the DB) their question answers, their ideal match's answers, and their question importances. On average,
  each user on OkCupid has 250 questions answered in 3 parts.
  -Figure out who qualifies for their search, typically a very complicated query across a few million users. On average, tens of
  thousands of people qualify, and we need to figure out who they are
  without hitting the DB.

How do they do all this without consulting a database??
I'm utterly confused.
Here is a link to the post
I appreciate any explanations as to how they do things there

They didn't explicitly mention this, so I am only guessing here, but they are using some search index such as Lucene to perform the actual searches.  They'll have a persistent database of some sort but their search index is periodically built from that data set.


Lossless Join Decomposition Criteria On Wikipedia, it says:

The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in
F+ (where
F+ stands for the closure for every attribute or attribute sets in
F):

R1 ∩ R2 → R1 or R1 ∩ R2 → R2

Unfortunately, I do not understand this criteria. It is known that the decomposition is lossless if the join of R1 and R2 is R, but how is this derivable from the criteria above?

Here is the proof.

(⇐) Suppose T1 ∩ T2 → T1 ∈ F+. Let r a valid instance of R⟨T, F⟩, and s = π T1(r ) ⨝ π T2(r ). If t ∈ s, then we need to show that t ∈ r.
By definition of s, we have two tuples u and v in r such that u [T1] = t [T1], v [T2] = t [T2] and u [T1 ∩ T2] = v [T1 ∩ T2] = t [T1 ∩ T2]. Since T1 ∩ T2 → T1 ∈ F+, then u [T1] = v [T1] and so t = v.

The case T1 ∩ T2 → T2 ∈ F+ can be proved in the same way.

(⇒) Suppose that, for each valid instance r of R⟨T,F⟩, r = π T1(r ) ⨝ π T2(r ); we need to show that T1 ∩ T2 → T1 ∈ F+ or T1 ∩ T2 → T2 ∈ F+.

Using reductio ad absurdum, we suppose that none of the two functional dependencies is implied by F.
Let W = (T1 ∩ T2)+, Y1 = T1 − W and Y2 = T2 − W. Y1 and Y2 are not empty by hypothesis, and W, Y1 and Y2 are a partition of T.
For each Ai ∈ T, 1 ≤ i ≤ k, we consider two values ai, ai′ ∈ dom(Ai), such that ai ≠ ai′. Let’s build a relation r with attributes WY1Y2 constituted by the two tuples:

e1[Ai] = ai, 1 ≤ i ≤ k

e2[Ai] =  ai if Ai ∈ W; ai′  if Ai ∈ Y1Y2

r satisfies each dependency V → Z ∈ F. In fact, if V ⊈ W, then e1[V] ≠ e2[V], and r obviously satisfies the dependency. If V ⊆ W, then (T1 ∩ T2) → V, so (T1 ∩ T2) → Z for transitivity, from which Z ⊆ W, and so e1[Z] = e2[Z], an this implies that r satifies the dependency. Moreover, since Y1 and Y2 are not empty, π T1 (r ) and π T2 (r ) contain two tuples and their natural join contains four tuples, more than those of r:

π T1 (r ) ⨝ π T2 (r )

contradicting the hypothesis.


What privileges are need for a normal Oracle user to access V$ and DBA_ views? I'm connecting to Oracle 19c as HR user. I want to execute the following SQL and some dba_... views:

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;

Oracle tells me the objects doesn't exist. It seems like a permission issue. What privileges are needed for me to access dynamic performance views and DBA_... views?

If you want to allow that user to read all system views, you can use

grant select_catalog_role to the_user;

Predefined Roles in an Oracle Database Installation


Update query with two different where conditions I would like to set status to false for all rows except one, which should should be set to true. How can I achieve that?

Here is the query I tried:

UPDATE students set status = false where status = true and
set status = true where _id = 1;

You can use a case expression:

UPDATE students
    set status = CASE WHEN _ID = 1 THEN true
                                   ELSE false
                 END;

A bit shorter (but perhaps not as obvious) is:

UPDATE students
    set status = COALESCE(_ID = 1,false);

COALESCE is need if _ID can be NULL.

One may argue that it is not necessary to update status at all times (for efficiency reasons). If we take into consideration that both _ID and status can be null there are 9 different situations (we let _ID = 0 represent all rows where _ID is not null and _ID <> 1).

_ID    Status    Action
-----------------------
null   null      set false
null   true      set false
null   false     -
   0   null      set false
   0   true      set false
   0   false     -
   1   null      set true
   1   true      -
   1   false     set true

All Actions is handled by:

set status = COALESCE(_ID = 1,false);

So we need to filter out the 3 situations where no update should take place:

(null, false), (0, false), (1, true)

In other words, our WHERE clause should include the other 6 combinations:

UPDATE students
    set status = COALESCE(_id = 1,false)
WHERE ((coalesce(_id, 0) = 1) <> coalesce(status,(coalesce(_id, 0) <> 1)))

This is not trivial (IMO), and even though it probably can be simplified I would not recommend to use it in any real situation (I would recommend disallowing nulls for boolean attributes in the first place, but that's another story).

A useful technique (once again IMO) to verify boolean expressions with truth tableau's, is to use CTE's for the domains and investigate the result. Example from above:

with status (s) as ( values (null),(false),(true))
  ,     _id (i) as ( values (null),(0),(1))
select i, s from status cross join _id
where ((coalesce(i, 0) = 1) <> coalesce(s,(coalesce(i, 0) <> 1)));

IS DISTINCT FROM as demonstrated by @Erwin Brandstetter is a nice abstraction that is very useful in situations like these.


Cannot login to postgres with newly created users I have created a number of users in postgres using createuser --interactive --password but whenever I run sudo -i -u newuser I receive the following error:

sudo: unknown user: staging
sudo: unable to initialize policy plugin

I then checked the user list when using the default postgres user to see if there were in fact any other users. It returned the following:

List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 newuser   | Superuser, Create role, Create DB              | {}
 newuser2  | Superuser, Create role, Create DB              | {}
 newuser3  | Superuser, Create role, Create DB, Replication | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

So I know that they are there. Why is it failing?

My end goal is to use this local db as my database for my deployed server. They are both installed on the same vm and I just want the REST calls from my server to interact with the local database, which I assume should be no problem, if I could ever connect to it.

2 things were required to solve this problem

service postgresql reload and I had to give postgres a password.
Unaware how to connect externally without a password but I wouldn't want to anyways so not concerned about it.

I also failed to have a new user function properly, only the default one worked. But it does work now.


Running a sql server Trace of 1 GB using sqlcmd I am load testing two sql server instances. I have a trace file from sql server 2000  database with a file size of little less than 1 GB. I am running this using the sqlcmd utility and it is been running for more than 5 hours now. The original trace file's duration was around 5 hours. Is this something expected with sqlcmd utility?

I assume the original trace contains the actions of multiple clients, with many queries executing in parallel? Your playback via SQLCMD is occuring serially, which is why it will take much longer to complete the same workload.

SQLCMD is the wrong tool for this, you need the RML Utilities. A very brief outline of the tools (taken from a previous answer):

ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
OStress is used to replay .rml and .sql files against a server.
ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
Reporter provides performance analysis reports.

This way you'll get the parallel playback you need to draw any meaningful comparison between the workloads on different instances.


SQL Server Setup Error I'm getting the following error at the very end of SQL Server 2016 setup.

  Updating permission setting for file 'Local Machine' failed.  Principal 'SYSTEM\CurrentControlSet\Services\Eventlog\Appliaction\DatabaseMail' was tried to be added to the file permission setting.  The file permission setting was supposed to be set to 'account name'.

It seems like a permissions issue.  Any help would be greatly appreciated. Thanks

Thanks for the help.  I found out it was a HIPS (Host Intrusion Protection System) blocking the registry entries.  Once I disabled it, the installation completed successfully.  Thanks again.


Linux Error: 29: Illegal seek in LSNRCTL for Linux Version 11.2 I'm trying to get Oracle Database 11g Express set up on my server so that it is accessible from the outside.  I can talk to the DB locally using SQLPlus, so the database is definitely running.  I think my issue is with the listener.

Here's what I'm getting:

$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 27-AUG-2012 16:14:38

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

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

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek

I've tried two solutions I found online, and neither seemed to work.  The first, from dba-oracle.com, suggests changing tcp.validcode_checking=yes to tcp.validnode_checking=no in sqlnet.ora.  There was no such line, so I added it, and no luck.  My sqlnet.ora is otherwise unchanged from when I installed.  Note that this referred to version 10.2.

The second suggested adding 127.0.0.1   localhost.localdomain localhost to /etc/hosts, which I did, again with no change.  No processes are listening on port 1521 (netstat -aopn | grep 1521 returns nothing).

Any suggestions would be most welcome.

Update

It seems I misunderstood what I was looking at.  The sqlnet.ora file is in the samples directory, so it's not actually being read by the listener.  Instead it is using the listener.ora file, which looks like this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

The tnsnames.ora file looks like this:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

These are again the files that came with the application.  Is there something I need to change here?

I was having the exact same problem, searched a lot and tried about everything suggested (like setting /etc/hosts correctly).

In my case (OEL6, 11gR2 XE) it was solved with the following:

export LD_BIND_NOW=1


Receiving error : ORA-01747 While trying to calculate COUNT(*) of a specific table Hope you're doing well.
I have two tables and as I far as I know for this scenario there is no need for their structures.

When I run this query :

SELECT COUNT(T.*) AS X
FROM table1 T INNER JOIN table2 M
     ON T.col1= M.col2

I receive this error:

ORA-01747:invalid user.table.column, table.column,or column specification

Is there something I'm missing here?? Dose this query have something which is against database rule?
thanks in advance

That is not valid syntax.

SQL> select count(t.*) as x from dual t;
select count(t.*) as x from dual t
               *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

SQL> select count(*) as x from dual t;

         X
----------
         1

SQL>


SQL Server service start automatically only after Windows Shutdown, how can I make restart automatically without batch file? I am using MS SQL Server 2014, When I restart my Pc the SQL Server Service does not start automatically, however, it does after Windows Shutdown and run again, how can I make it restart automatically ?
Without using batch file

You can set the service property to restart it (service properties --> recovery --> first / second / third failure and have a delay between each restarts e.g. 1 min). You should also, have the service set to automatic delayed start.

Alternatively, you can use powershell and schedule it in task manager to check if the service is running or not. If not running, restart it.

get-service and restart-service are your friends.


Parsing a manifest.xml of a table schema to script? I have a manifest.xml sent to me by a vendor that accompanies some extracts they are also sending.  I assume there should be a way to parse this into an actual table structure (or a script to create a table).  It's an extremely wide table (153 columns) and I'd rather not manually create a table if I can avoid it, especially since there are a variety of extracts coming and most are extremely wide.

Here's a sample of what the manifest.xml looks like that accompanies each file extract:

<DocumentElement>
  <SchemaTable>
    <ColumnName>somecolumn1</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn1</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  <SchemaTable>
    <ColumnName>somecolumn2</ColumnName>
    <ColumnOrdinal>1</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn2</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  ...
</DocumentElement>

Here's some code to get you started:

declare @doc xml = '
<DocumentElement>
  <SchemaTable>
    <ColumnName>somecolumn1</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn1</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>
  <SchemaTable>
    <ColumnName>somecolumn2</ColumnName>
    <ColumnOrdinal>1</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <IsUnique>false</IsUnique>
    <BaseColumnName>somecolumn2</BaseColumnName>
    <DataType>System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <AllowDBNull>false</AllowDBNull>
    <ProviderType>8</ProviderType>
    <IsIdentity>false</IsIdentity>
    <IsAutoIncrement>false</IsAutoIncrement>
    <IsRowVersion>false</IsRowVersion>
    <IsLong>false</IsLong>
    <ProviderSpecificDataType>System.Data.SqlTypes.SqlInt32, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ProviderSpecificDataType>
    <DataTypeName>int</DataTypeName>
    <NonVersionedProviderType>8</NonVersionedProviderType>
  </SchemaTable>

</DocumentElement>';

with q as
(
    select t.c.value('ColumnName[1]','nvarchar(200)') ColumnName,
           t.c.value('ColumnOrdinal[1]','int') ColumnOrdinal,
           t.c.value('DataTypeName[1]','varchar(50)') DataTypeName
    from @doc.nodes('/DocumentElement/SchemaTable') t(c)
)
select concat(case when ColumnOrdinal = 0 then '    ' else '   ,' end, quotename(columnname), ' ', DataTypeName, ' ') columnDeclaration
from q
order by ColumnOrdinal


MySQL Index Questions I have 2 MyISAM tables:

table: search_hit with primary key: search_hit_id, and foreign key: search_id (150 million rows and growing with constant inserts)
table: search with primary key: search_id (about 30,000 rows and growing)

search_hit table has a number of indices,including search_id, but this query is excruciatingly slow:

select sq.search_id, count(ssh.search_hit_id)
from search sq
inner join search_hit ssh
use index(by_search_id) on sq.search_id = ssh.search_id
where sq.search_id >= 47000
group by sq.search_id

here is the explain results:

1, 'SIMPLE', 'sq', 'range', 'PRIMARY', 'PRIMARY', '4', '', 202, 'Using where; Using index'
1, 'SIMPLE', 'ssh', 'ref', 'by_search_id', 'by_search_id', '4', 'lims.sq.search_id', 3358, ''

query returns with status like:
/* 0 rows affected, 1,575 rows found. Duration for 1 query: 3.198 sec. (+ 5.975 sec. network) */

Our server setup MySQL 5.0.45 on 64 bit linux with 8GB dual core (hyperthreaded quancore).
Would dropping the index, optimize table then re-create the index help here, or anything?

Thanks

David

You must refactor the query to perform WHERE, JOIN and GROUP BY clauses IN THAT EXACT ORDER !!!

Here is the refactored query :

select
    sq.search_id,
    count(1) search_id_count
from
    (select search_id from search where search_id > 47000) sq
    inner join
    (select search_id from search_hit where search_id > 47000) ssh
    using (search_id)
group by
    sq.search_id;

Running OPTIMIZE TABLE (reduces table fragmentation and recreate indexes) may not be necessary unless you do heavy INSERTs, UPDATEs, DELETEs. I'll say it is optional.

Give it a Try !!!


How can I limit MySQL connections? Last week, our MySQL database got overwhelmed with a lot of connections doing INSERT / SELECT queries.

I have talked to the developers who say it will take weeks to fix this issue.

In the mean time, I would love to have a way to limit the amount of live connections to the MySQL database if I see it getting out of control.

There is the option max-connections but I am not sure if I can throttle this so that if I the database is getting swamped, I would just set global max-connection = 100, and it would stop accepting new connections.

Is this a viable solution?  What have you done before when your database gets overwhelmed?

You could limit the number of connections per hour per user. Restarting mysql would not be necessary for this.

For example, suppose all your web servers connect to mysql from the 10.1.2.% netblock. You should already have a user in mysql.user named something like 'myuser'@'10.1.2.%'.

To set the maximum number of queries per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_questions = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of updates per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_updates = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

To set the maximum number of connections per hour at 1000 on a given connection do this:

UPDATE mysql.user SET
max_connections = 1000
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;

For more clarification on this, please see MySQL Documentation.

BTW once you are done with the maintenance window for throttling user connections, simply set the values you changed back to zero(0) to remove the limits as follows:

UPDATE mysql.user SET
    max_questions = 0,
    max_updates = 0,
    max_connections = 0
WHERE user='myuser' AND host='10.1.2.30';
FLUSH PRIVILEGES;


What do you call numerical coding systems that are left-aligned? I'm writing an importing mechanism for the NAICS database. I have a few questions about this code-format. I've seen it before and I like it the setup. I'm going to ask some other questions about best-practices and navigation of this data, and I'd like to simply refer to it by the right name.

Essentially this is an example of the data

CODE, TITLE
"21","Mining, Quarrying, and Oil and Gas Extraction"
"212","Mining (except Oil and Gas)"
"2121","Coal Mining"
"21211","Coal Mining"
"212111","Bituminous Coal and Lignite Surface Mining "
"212112","Bituminous Coal Underground Mining "

So if Bituminous Coal Underground Mining was your organization type, your code would be 212112. You could look up to find you were in the business-genre of Coal Mining, and up again to find you were in the business of Mining (except Oil and Gas), and up again to find you were in the business of "Mining, Quarrying, and Oil and Gas Extraction".

What is such a scheme called, is there a term to refer to this kind of organization of data?

I want to call something like recursive-base10 or recursive-decimal is there a name of it though?

That sort of coding format is simply a representation of a tree or forest - each code represents the node's location in a hierarchy where each node (except the root node(s)) has exactly one parent.

That hierarchy could be directly modelling the physical world (as Dewey Decimal Classification does for books in a library) or it could be something more "virtual".

I don't think this sort of coding has a generally recognised name, so if you need a concise phrase for use in your documentation just pick something short that results in a relatively unambiguous acronym, be consistent with its use, and make sure it is included in your terms of reference. Perhaps "Hierarchical Encoded Identifiers", "Hierarchical Classification", "Business Location Codes", or similar.

Don't be hung up on it being numerical: this is not significant as there are no mathematical operations that are meaningful when applied to these codes. The codes could be using any set of characters, even multiple characters per level in the hierarchy, and for this system someone has chosen to limit the alphabet of the encoding to the characters 1, 2, 3, ... It would be just as meaningful for the encoding to use other characters or to have separators between each position, so your last two examples could be MQE/EOG/CM/CM/BaL & MQE/EOG/CM/CM/BUM or MXCCL & MXCCU in similar schemes with different alphabets - all operations would be equally meaningful (searching, lexical sorting, extracting the meaning back from the code, and so forth) or not (mathematical operations) in these encodings.


reapply permissions post sql server restart In our environment, users were granted read/write/ddladmin permissions on tempdb database (dev/qa/prod).
These permissions will be lost when SQL Services restart, as tempdb rebuilds every time. One solution that is currently in place is - reapply the permissions scripts from a SQL Agent job, when ever SQLAgent starts. This is not accurate, as the users/permissions will be modified over time. Another thought, is take a daily backup (with a 15day retention policy) of tempdb permissions and use the same job to pick the latest file to reapply the permissions when SQL restarts. Is this an accurate solution for the problem.
Thanks!!

An alternative to modifying model might be to create a stored procedure in the master database that contains whatever you need to do. Then you mark this as startup proc (sp_procoption).
You might have to put in a WAITFOR in there in case SQL Server executes this before model has been recovered and tempdb has been created. For you to play with.
I've only used startup proc occasionally, so make sure you do thorough testing.
And, I fully agree with J.D.: modify them jobs instead, if at all possible.


SQL Server's `mssql-conf setup`, error Aborted sudo When I run sudo /opt/mssql/bin/mssql-conf setup to configure SQL Server 2017 I get the following error,

/opt/mssql/lib/mssql-conf/invokesqlservr.sh: line 15: 12710 Aborted                 sudo -EH -u mssql /bin/bash -c "$CMDLINE"

sudo disables core dump

 To prevent the disclosure of potentially sensitive information, sudo disables core dumps by default while it is executing (they are re-enabled for the command
 that is run).  This historical practice dates from a time when most operating systems allowed setuid processes to dump core by default.  To aid in debugging
 sudo crashes, you may wish to re-enable core dumps by setting “disable_coredump” to false in the sudo.conf(5) file as follows:

       Set disable_coredump false

The Aborted above is actually generated from this

sudo -EH -u mssql /opt/mssql/bin/sqlservr
Aborted

Which is actually,

sudo -EH -u mssql sh
$ /opt/mssql/bin/sqlservr
Aborted (core dumped)

Make sure you have a new copy. In the event you updated your distro, you may have to re-add the repository and key. A core dump, may or may not be normal practice for Microsoft, from their own docs, license_Eval_Linux.txt

  TIME-SENSITIVE SOFTWARE. The software will stop running one hundred eighty days after you install it. You will not receive any other notice. You may not be able to access data used with the software when it stops running.


Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication. It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?

Thanks in advance for any advice.

Joe

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication.

Make sure you do implement a change freeze to your schema.

From BOL :

  For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

Based on above truth,

Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers.
Freeze all the data and schema changes <-- This is your downtime.
Migrate your database from sql server 2005 to 2014 and do all post restore steps.
Repoint your application to the new server instance.
Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql.
Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql.

Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast.

Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above.

Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.


Why do database host services increment id by > 1 unit? I have worked with various databases and notice that an id column (primary key) with auto_increment usually increases by a seemingly random, but static, value like 3, 5, or 10.

So instead of the id counting up by one for each row: 1,2,3,4, etc. the id might count up by 5 like 1,6,11,16, etc.

Why is this practice in place?

My guess is that it can serve to obscure information to outside users.  For example my user id  might show in the URL on a website, and if the id on the users table counts up by 1, I'd be able to gauge the number of users that have signed up for a specific service.  However I am curious to know if that is the reason or if other factors are involved.

The only reason I can think of is "reserving" some data slots for the future usage. That would work if a table is index-organized ( or clustered in Microsoft terminology). For instance,  if we have rows with id 1,6,11,....1001, and we manually insert row with id =2 it will be physically stored (assuming data page is big enough to store 2 rows) in the same data page as row with id =1.
Personally, I doubt efficiency of such approach, but it seems to make some sense.

Security explanation sounds very weird to me, increasing by 5 (or 3, or 103) is by no means more "secure" than by 1.

There might be some very application specific reasons, but it's better to ask whoever designed particular application and db schema for it.


Some SQLs sometimes take all available RAM I'm running postgresql server on Windows Server 2008, which has 6GB of ram. My dataset is something near 1,5GB big.

I have problems, when I run specific SQLs in table, which has 51mio of records. The fields which I use in SQLs are indexed (alarm, id_elektrarna, id_razsmernik). Below is the ANALYZE SQL, which causes problems. Do you have any suggestions what to do?

UPDATE:

When I run my query, I see how memory of the system where database is running is increasing, while some scripts that are using database just "pause" working. When I stop SQL from running, everything works again.

There are additional data:  #work_mem = 1MB (commented), shared_buffers = 32MB,

index definition, table definition, SQL that is the issue: http://pastebin.com/4iMtpD4r

The database server is using the RAM, not the client application. This is the SQL with EXPLAIN (ANALYZE,BUFFERS) that executes in less than 1sec and do not cause problems. Same SQL, different UNIT in database (different ID) takes more than 15-30minutes: http://explain.depesz.com/s/OdqU.

The main problem is with the shared_buffers = 32MB parameter. It is 2014 not 2000. You cannot expect any kind of decent performance on a 6Gb machine, if you do not increase that to at least 512Mb. I believe that you are also using PostgreSQL 64bit (if not make the change now). Also, you say that your dataset = 1.5 GB. Are you sure? 50 million records fit on 1.5GB on disk? Have you run SELECT pg_database_size('myDbName'); Does it still show 1.5GB?

I also see you do not have an index at status_razsmernika field and you do a join on that, which leads to a sequential scan. You also have (razsmernik.status_razsmernika!=6) and (razsmernik.status_razsmernika !=0). So, you really need to add a b-tree index to status_razsmernika.

Also you are using the "updated" field for order in descending value. If this is always the case, specify the order in the related index as well. http://www.postgresql.org/docs/9.3/static/sql-createindex.html


How to copy table content before deleting to flat file using BCP I have created stored procedure where, I delete data in any given time. I want to save the records to a flat file before deleting in case I need to import the data back to table.
I DO NOT want to call from the command line as I have a program that calls the stored procedure to schedule the job.

CREATE PROCEDURE [dbo].[Clean] (
 @DeletionDate date
) AS

DECLARE @id int,
    @intError int

        DELETE FROM
      Errn
     WHERE
    erid IN (
         select
             distinct m.ID
         from
             Member m
         left join
             (select
                  *, row_number() over (PARTITION BY rid order by ceid desc)
           as rn
              from TypeA) x
             on m.ID = x.ID and x.rn = 1
           where
             (
                 (x.ceid is null and m.DateTime < @Deletion)

             );
     ON_ERROR:
     RETURN @intError
    RETURN 0

How can I copy the results of the select statement to a .txt file before deleting the records?

As scsimon has pointed out, you can take advantage of OUTPUT clause instead of using a flat file.

CREATE TABLE MyTable(id int identity, foo int);
CREATE TABLE MyTableDeleted(id int, foo int);

INSERT INTO MyTable VALUES
(100),(200),(300),(400),(500);

DELETE FROM
    MyTable
OUTPUT
    DELETED.* INTO MyTableDeleted
WHERE
    foo <= 300;

SELECT * FROM MyTable;

id | foo
-: | --:
 4 | 400
 5 | 500

SELECT * FROM MyTableDeleted;

id | foo
-: | --:
 1 | 100
 2 | 200
 3 | 300


Is there any tangible difference between a unique clustered index and a clustered primary key? I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?

One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.

Oh, and the fact that a table can have one PK and many UK :-).

These are both differences in INTENT not in PERFORMANCE.
Otherwise, I don't think there's any difference.
Behind any PK or UK the SQL Server builds an index (depending on the request, clustered or not) and the way it's used is transparent for the source is coming from.


Why did PostgreSQL choose an elephant as it's logo? Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki.

UPDATE: Now wiki updated :) Thanks!

In the email thread postgreSQL and history of relational databases, Oleg Bartunov states that the logo idea probably originated from a PostgreSQL logo thread on the pgsql-hackers mailing list. Quote:

  [...]
  but if you want an animal-based logo, how about some sort of elephant?
  After all, as the Agatha Christie title read, elephants can remember ...

  David Yang


Separating Devops Instance Hard Drives We currently have SQL Server Dev, QA, and StagingUAT instances sitting on same VM.  The production resides in its own server.

The issue is, if one database MDF or TransactionLog on Dev, suddently consumes too much space, it wipes out hard drive space from all other Devops instances. They all share the same drive. This is a single point of failure. I inherited this legacy system.

Currently
The Dev, QA, and StagingUAT Database MDF have the same G:\ drive.

Dev Databases: on G:\Dev

QA Databases on G:\QA

Staging Databases on G:\Staging

Transaction Logs are on:

Dev Database: on H:\Dev

QA Databases on H:\QA

Staging Databases on H:\Staging

What is the best way to prevent issue of one instance suddenly takes too much space?

Method 1:
I recommended each Devops Instance having its own drive.

MDF Database:

Dev Database: on A:\Dev

QA Databases on B:\QA

Staging Databases on D:\Staging  (skip C:)

Transaction Log:

Dev Database: on E:\Dev

QA Databases on F:\QA

Staging Databases on G:\Staging

Method 2:

Have each subfolder have a max hard drive space it can consume.
G:\Dev can only take 10 GB max.

G:\QA can only take 10 GB max.

G:\Staging can only take 10 GB max.

We have current rule, databases on the same Devops instance should be on same drive. Currently we can only provision 1 VM for these devops instances.

Just curious best way to resolve this issue. When creating Devops instances,should each instance be separated from other instances?

For each of the databases you can set a max size per file.  Post about setting grow and file size

Here is Microsoft’s details on the alter file command

The advantages of doing it this way is that you can set each database to have a different file size and you don’t have to go to the trouble of creating the new drives and moving each database.

You can do this through click ops in the files section of each database setting the max file size for each file.

Alternatively you can script out the statements by doing a select on the sys.files table with some string building.

declare @usage table(Fileid int, FileGroup int, TotalExtents float, UsedExtents float, Name sysname, FileName nchar(520))
insert  @usage exec('dbcc showfilestats');
select [name], ((totalextents * cast(64 as float))/1024) [total]
,((usedextents * cast(64 as float))/1024) [used]
,((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024) [free]
, ((totalextents * cast(64 as float))/1024) - (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ) [reduce]
,(1-(((usedextents * cast(64 as float))/1024) / ((totalextents * cast(64 as float))/1024))) [free %]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', MAXSIZE = 50MB);' [Set Max File]
,'DBCC shrinkfile ([' + [name] + '],' +
    cast(
        cast((((usedextents * cast(64 as float))/1024) + ( (((totalextents * cast(64 as float))/1024) - ((usedextents * cast(64 as float))/1024)) * .3  ))
        as int)
     as varchar)
+ ');' [shrink]
, len(substring([name], 1, 36))
--, (select gender + ' ' + sport from v_sportseason where id = substring([name], 1, 36))
, [filename]
, 'alter database ' + DB_NAME() + ' modify file (name=' + rtrim([name]) + ', filename=''' + rtrim([filename]) +''');' [Move File]
 from @usage


How can I access certain information from YQL? I notice the YQL commands such as

select * from yahoo.finance.quote where symbol in ("YHOO")

return all the data for that certain stock. I was wondering is it possible to access data from YQL based on volume or other specific data? For example is it possible to get the highest volume stock from the day?

From reading the YQL Documentation and viewing the data sources it appears that while the yahoo.finance.quote does provide data on all the stock data, running a query on a large data set make take some time and the data may be returned in multiple pages which means that your application will have to handle the pages. It also appears that the YQL data requires a where clause in order to run so you would need to know the symbols for all of the stocks.

There is a YQL Console which allows you to access the YQL data and run queries, view the output etc. I would suggest reading the YQL documentation and running some sample queries in the YQL Console to see if the data you require is available.

To access the YQL Console you can

Start at the YQL Console
In the top left hand corner under Datatables ensure you tick the Show Community Tables
In the search box type Finance. This should display all of the Finance tables (approx 31)
In the "Your YQL Statement" window type your YQL statment.
In order to get the average daily volume for Yahoo you could use the following YQL:

select symbol,AverageDailyVolume from yahoo.finance.quote where symbol in ("YHOO")