Postgres datetime sort slow and not using index Using Postgres 9.4, I'm trying to order a query by an indexed datetime column.

explain
SELECT g.*, array_agg(gp.platform_id) as platform_list,
            array_agg(gm.metacritic_id) as metacritic_list,
            array_agg(gr.review_id) as review_list,
            array_agg(gn.genre_id) as genre_list,
            array_agg(gp.id) as release_list
FROM gb_game g
LEFT JOIN gb_release gp ON g.id = gp.game_id
LEFT JOIN gb_game_metacritic gm ON g.id = gm.game_id
LEFT JOIN gb_game_reviews gr ON g.id = gr.game_id
LEFT JOIN gb_game_genre gn ON g.id = gn.game_id
GROUP BY g.id
ORDER BY g.release_date
DESC limit 25 offset 0

Limit  (cost=66391.46..66391.52 rows=25 width=294)
  ->  Sort  (cost=66391.46..66511.58 rows=48051 width=294)
        Sort Key: g.release_date
        ->  GroupAggregate  (cost=1.56..65035.49 rows=48051 width=294)
              Group Key: g.id
              ->  Merge Left Join  (cost=1.56..53579.05 rows=691686 width=294)
                    Merge Cond: (g.id = gn.game_id)
                    ->  Merge Left Join  (cost=1.14..16374.78 rows=63464 width=290)
                          Merge Cond: (g.id = gp.game_id)
                          ->  Merge Left Join  (cost=0.85..10856.60 rows=48051 width=282)
                                Merge Cond: (g.id = gr.game_id)
                                ->  Merge Left Join  (cost=0.58..10676.85 rows=48051 width=278)
                                      Merge Cond: (g.id = gm.game_id)
                                      ->  Index Scan using platforms_game_pkey on gb_game g  (cost=0.29..8984.87 rows=48051 width=274)
                                      ->  Index Scan using gb_game_metacritic_6072f8b3 on gb_game_metacritic gm  (cost=0.29..1264.75 rows=24569 width=8)
                                ->  Index Scan using gb_game_reviews_6072f8b3 on gb_game_reviews gr  (cost=0.28..51.04 rows=686 width=8)
                          ->  Index Scan using platforms_release_6072f8b3 on gb_release gp  (cost=0.29..4604.75 rows=63464 width=12)
                    ->  Materialize  (cost=0.42..27979.58 rows=523702 width=8)
                          ->  Index Scan using gb_game_genre_6072f8b3 on gb_game_genre gn  (cost=0.42..26670.33 rows=523702 width=8)

The release_date column doesn't have many repeats other than null.
Everything is fast until the final sort.

Is there a special kind of index I need to use to speed up this query?

Better query

Seems like a perfect occasion for a couple of LATERAL joins, which should be much faster here:

SELECT g.*, p.platform_list, m.metacritic_list
     , r.review_list, n.genre_list, p.release_list
FROM  (
   SELECT *
   FROM   gb_game
   ORDER  BY release_date DESC NULLS LAST  -- !? see below
   LIMIT  25
   OFFSET 0
   ) g
,      LATERAL (
   SELECT array_agg(platform_id) AS platform_list
        , array_agg(id)          AS release_list
   FROM   gb_release
   WHERE  game_id = g.id
   ) p
,      LATERAL (
   SELECT ARRAY (
      SELECT metacritic_id
      FROM   gb_game_metacritic
      WHERE  game_id = g.id)     AS metacritic_list
   ) m
,      LATERAL (
   SELECT ARRAY (
      SELECT review_id
      FROM   gb_game_reviews
      WHERE  game_id = g.id)     AS review_list
   ) r
,      LATERAL (
   SELECT ARRAY (
      SELECT genre_id
      FROM   gb_game_genre
      WHERE  game_id = g.id)     AS genre_list
   ) n
ORDER   BY g.release_date DESC NULLS LAST;  -- repeat inner sort order

Indexes

You need an index on each game_id column in the child tables.
Multicolumn indexes might be even better if (and only if) you can get index-only scans out of them:

gb_release (game_id, id, platform_id)  -- special: 2 additional columns
gb_game_metacritic (game_id, metacritic_id)
gb_game_reviews (game_id, review_id)
gb_game_genre (game_id, genre_id)

The order of columns matters.

And finally, you need an index on gb_game (release_date).
There is no data type called datetime in Postgres. Assuming you mean timestamp (some ORMs map this way).

Either way, if you really mean to ORDER BY g.release_date DESC, a plain index on (release_date DESC) or just (release_date) does the job

If the column can be NULL, I suspect you actually want to sort NULL values last. Adapt your query like I did to ORDER BY g.release_date DESC NULLS LAST. A plain index on (release_date) still works, but is not the optimum. Use a matching sort order in your index:

gb_game (release_date DESC NULLS LAST)

Or (almost equivalent):

gb_game (release_date ASC NULLS FIRST)

More:

Unused index in range of dates query
PostgreSQL sort by datetime asc, null first?

Explain query

You join to multiple tables that can have n related rows for a single row in gb_game, resulting rows in the intermediary derived table are multiplied with every join to multiple rows. That's not only a major performance issue, it's also incorrect. Or at least I assume as much while detailed information is missing. Related:

Two SQL LEFT JOINS produce incorrect result
Is there a way I can speed up queries against this particular view?

I avoid the problem a priori by aggregating each child table separately in LATERAL subqueries. That seems particularly efficient, since you only pick 25 rows total.

While being at it I use a faster ARRAY constructor for single column aggregation (while I still use array_agg() for the case where two columns from the same table are aggregated separately.

Note that the order of elements in each array is arbitrary. You can add an ORDER BY clause in each lateral subquery to determine the order.

The last three lateral subqueries could be replaced with a correlated subqueries:

What is the difference between LATERAL and a subquery in PostgreSQL?


What is the recommended way to backup a MySQL/Amazon RDS database to S3? I have two purposes for this:

To have an offsite backup in case of a region wide problem with Amazon Web Services.
To copy production data from a production billing account to a beta billing account.

Currently it does not appear that Amazon supports either of these two use cases out of the box.

I have seen mysqldump and xtrabackup (see form post) mentioned.

I have also seen a more complicated process (documented here)

A new RDS server in the source billing account (prod) is spun up off a recent backup.
A new EC2 instance is spun up that has access to the RDS server in step 1.
mysqldump is used to make a backup of this database.
Backup is copied to an offsite location (S3?).
In a separate account and/or region, a fresh RDS server is spun up.
Database dump is imported.

Tips and suggestions are appreciated.

The recommended way to back up RDS is with automatic backups and DB snapshots.  DB snapshots are basically the same as EBS snapshots, which are stored in S3 behind the scenes, but are only available within the same region.

If you need cross-region fault tolerance (good plan!), there is no way to restore your data  in another region without doing it "the hard way" from a mysqldump.  Your alternatives are to back up using mysqldump (slow and terrible for any reasonable sized dataset), or set up your own EC2-based slave in another region and back that up using any available method (xtrabackup, EBS snapshots, etc).  However, then you are back to managing your own MySQL instances, so you might as well abandon RDS entirely.

For my money, RDS provides absolutely no benefits in really any way and a whole lot of disadvantages in performance, flexibility, and reliability.  I would ask yourself what value RDS provides to you.


Moving Tables Indexes to Separate file / file-groups, What Happens? I'm trying to get a grasp on whats going on when you move objects to a separate filegroup / .ndf file.

*This is for a 3rd party app that sucks that I have no access to source, and is entirely Ad-Hoc OLTP workload.

My setup is datafile's on BB 4G cached raid 5 array.  (Very fast write speeds / Slower then desired reads)

Logs on Mirrored Solid States (Fast Reads / Fast Enough Writes) array

tempdb on 2nd Mirrored Solid States (Fast Reads / Fast Enough Writes)  array

My plan was to move objects with high reads / very low writes to a 3rd SSD array (Very Fast < 1ms Read).  I moved a good amount of tables and indexes by dropping the Clustered Index and  Rebuilding on the Secondary File Group.  When I run

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
order by f.name
GO

It Show the objects are indeed on the secondary filegroup.  However when I look at the reads it show that the filegroup is not being used.  Also when I look at the size:

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total   Size in MB],
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In    MB], [file_id]
FROM sys.database_files;

It looks like very little of the .ndf datafile is being utilized.  I tried rebuilding the indexes, same thing.

So long story short my question is what happens when you drop / rebuild on a secondary filegroup.  Am I going about this the wrong way ?

Thanks in advance

Edit -- at Marks Request

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[lntmuser].   [matter]') AND name = N'matter_k__2')
ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2]
GO

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
ALTER TABLE [lntmuser].[matter] ADD  CONSTRAINT [matter_k__2] PRIMARY KEY CLUSTERED
(
    [sysid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,    FILLFACTOR = 90)
ON [Alternate] --Instead of primary
GO

Your example should indeed work but it's not the recommended approach. By doing a drop/create on the CL followed by a drop/create in the NC, you're rebuilding the NC indexes 3 times.

Drop CL = table converts from cluster to heap, which requires NC rebuild
Create CL = table rebuilds from heap to cluster, which again rebuilds NC
Drop/Create NC = rebuilds them again

Despite your approach not being the fastest way to do this, it should work. Can't explain what's going wrong but can suggest a different method. Can you try the following:

Drop NC indexes
Run the following to drop the PK/CL and rebuild on the new FG

ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2]
GO

CREATE UNIQUE CLUSTERED INDEX CLIX_matter_sysid ON [lntmuser].[matter] (sysid ASC)
-- Add appropriate fill factor option etc
ON [Alternate]
GO

ALTER TABLE [lntmuser].[matter] ADD CONSTRAINT PK_Matter PRIMARY KEY (sysid)
GO
Create the NC indexes

If that works, we can go back to working out why your first script failed.

Edit: Just in case... you mentioned checking reads for the new filegroup. What are you using to check this? Are you looking at sys.dm_io_virtual_file_stats and seeing no change as the tables are fully cached in the buffer pool?


My MySQL Server Died Approximately 1 time per week my MySQL server is shutting down.

This is what the log looks like:

2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
2016-03-18 13:46:25 13166 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-03-18 13:46:25 13166 [Note] Plugin 'FEDERATED' is disabled.
2016-03-18 13:46:25 13166 [ERROR] Function 'innodb' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'federated' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'blackhole' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'archive' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2016-03-18 13:46:25 13166 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-18 13:46:25 13166 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-18 13:46:25 13166 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-18 13:46:25 13166 [Note] InnoDB: Memory barrier is not used
2016-03-18 13:46:25 13166 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-18 13:46:25 13166 [Note] InnoDB: Using Linux native AIO
2016-03-18 13:46:25 13166 [Note] InnoDB: Using CPU crc32 instructions
2016-03-18 13:46:25 13166 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12
2016-03-18 13:46:25 13166 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' init function returned error.
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-03-18 13:46:25 13166 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-03-18 13:46:25 13166 [ERROR] Aborting
2016-03-18 13:46:25 13166 [Note] Binlog end
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'partition'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-03-18 13:46:25 13166 [Note] /usr/sbin/mysqld: Shutdown complete

160318 13:46:25 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What do you think? Maybe there is not enough memory?

This is running on a Virtual Private Server running Ubuntu 15.10 with 512MB of memory.

Now I can't even restart my MySQL server.  My my.cnf is empty, What can I do?

Honestly, I don't think mysqld is dying.
The message you posted reminds me of someone or something trying to restart mysqld in the absence of the PID file. When there is no PID file, doing service mysql stop does not work. I wrote about this in Percona-server time out on /etc/init.d/mysql start back on Dec 14, 2012 and MySQL Start/Stop 3 days later.
What's happening is that mysqld is already running and a mysqld start is being issued. This destroys the PID file (See MySQL Start/Stop under the heading PROCESSES (Shutdown)). The messages you are seeing is the second attempt to start mysqld only to find out that the plugins are already loaded.
What makes this more obvious is something I wrote back on April 24, 2014 in Two MySQL Server accessing same database over NFS:

Only one mysqld instance can attain an exclusive log buffer thread on a system tablespace
Only one mysqld instance can attain an exclusive insert buffer thread on a system tablespace

It only makes sense that the Buffer Pool cannot be allocated because a Buffer Pool already exists
SUGGESTION
Try shutting down mysqld with
mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p shutdown

This will prompt for password. After entering the password, if this returns no output, then mysqld performed a proper shutdown. (BTW this is handled in MySQL 5.7 because you can run the SHUTDOWN command from within a mysql client session). Then, you can run service mysql start with the PID file situation straightened out.
I hope you have the user root@127.0.0.1 or mysqld is smart enough to use root@localhost credentials (See my old post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) on how mysqld authenticates users)


Is there a way to export an Oracle database to a CREATE DATABASE command? I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a list of the datafiles and tablespaces that would be needed to create the same database. I'm not interested in preserving individual schema objects.

I ask because there's the DBMS_DDL package which will give you the DDL to re-create any table, etc. Wondering if there's something similar at a database level.

Thanks!

Rather than doing this, I'd advise on getting the character set info from the nls_database_parameters view & then using DBCA to create a new database, along with dbms_metadata.get_ddl to handle tablespace creation. - Much easier and less prone to error. The character set and national character set are really the only things that are a pain to change when a database has already been created & DBCA lets you chose them in the UI. I imagine you're needing to package some scripts up to automate creation or something?

Anyway, if you really must proceed, you can't do exactly what you're asking out of the box, but you can dump the Control File which will give you most of the information you need to construct a CREATE DATABASE statement, including most pfile parameters (a pfile is required to start a new instance before issuing a CREATE DATABASE statement), redo logs & character set etc.

For example:

SQL> alter database backup controlfile to trace as '/tmp/db.sql';

Database altered.

SQL> !cat /tmp/db.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="PHIL112"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PHIL112" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PHIL112/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PHIL112/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PHIL112/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PHIL112/system01.dbf',
  '/u01/app/oracle/oradata/PHIL112/sysaux01.dbf',
  '/u01/app/oracle/oradata/PHIL112/undotbs01.dbf',
  '/u01/app/oracle/oradata/PHIL112/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PHIL112/temp01.dbf'
     SIZE 937426944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PHIL112" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PHIL112/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PHIL112/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PHIL112/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PHIL112/system01.dbf',
  '/u01/app/oracle/oradata/PHIL112/sysaux01.dbf',
  '/u01/app/oracle/oradata/PHIL112/undotbs01.dbf',
  '/u01/app/oracle/oradata/PHIL112/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/PHIL112/archivelog/2013_01_10/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PHIL112/temp01.dbf'
     SIZE 937426944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

SQL>

Then you can extract the tablespace creation DDL with:

select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) as ts_ddl
from dba_tablespaces;

If you want me to expand further on all of the steps required to manually create a database, just ask (if you don't already know).


How to select rows with a particular rank per partition? I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the complete problem.
The tables are:

Employee table:

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table:

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

The objective is to pick employees who have the highest salary in each of the departments.
My query:
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id
      group by d.id
      having salary_rank=1) temp;

Error:

You cannot use the alias 'salary_rank' of an expression containing a window function in this context.'

How can I use a particular rank in the having clause in order to get the highest ranking employee in each department?
I am using the where clause that works:
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id) temp
where salary_rank=1;

but I wanted to use the having clause.
I have tried reading the documentation and watched a couple of tutorials, all of them use the having clause for ranking per partition. What am I missing here?

window functions have some limitation for example you can use them in having.

You can only use window functions in the SELECT list and ORDER BY clauses of a query.

Window functions are listed between the two keywords SELECT and FROM at the same place where usual functions and columns are listed. They contain the keyword OVER.
The definition :
-- Window functions appear between the key words SELECT and FROM
SELECT   ...,
         <window_function>,
         ...
FROM     <tablename>
...
;

And in mysql WINDOW functions restriction is written

Using such functions in a subquery of these statements (to select rows) is permitted.

So your approach is correct. You Have to put the SELECT in a subquery or as Akina said you can use cte , which is only another FORM
Your query has to be
select department, employee, salary
from (select e.name as employee, d.name as department, e.salary as salary, rank() over(partition by d.name order by salary desc) as salary_rank
      from employee e
      join department d on e.departmentid=d.id
      group by d.id ) temp
WHERE salary_rank=1;


replace string in field I have these entries in a field in one table under a DB running Postgres 9.4:
copy schema_name.table_name to '/path/path2/path3/path4/reports/export"

and would like to replace ONLY the word "reports" by "reports_test"
What would be the proper way to do this?
I understand is a regular expression in an SQL update query but not exactly sure.

This replacement can be done with:
UPDATE tableName SET fieldName =
     regexp_replace(fieldName, '\yreports\y', 'reports_test', 'g');

From the documentation:

\y matches only at the beginning or end of a word

The last argument 'g' is meant to replace all occurrences, otherwise it would replace only the first found.
You may also add a WHERE clause to limit the rows updated to those that match:
UPDATE tableName SET fieldName =
     regexp_replace(fieldName, '\yreports\y', 'reports_test', 'g')
WHERE fieldName ~ '\yreports\y';


the verbosity of "(SELECT * FROM A)"? This might be a novice question. But sometimes, I find it necessary to use things like (SELECT * FROM A) in doing relational algebra in SQL (instead of using the relation A itself).

For example, when trying to work on the UNION of two relations A and B having the same structure, the following

SELECT * FROM (A UNION B) t;

generates a syntax error (with PostgreSQL 9.x).

ERROR:  syntax error at or near "UNION"

And I had to do

SELECT * FROM ((SELECT * FROM A) UNION (SELECT * FROM B)) t;

My questions are:

Is the verbosity of the subquery (SELECT * FROM A) really necessary in the SQL language (or am I missing something about better way to write it)? Why can't the relations A and B be used directly here?

Is (SELECT * FROM A) equivalent to A in relational algebra? What does the expansion (SELECT * FROM A) buy us? When is the long form required?

If you want shorter syntax in SQL, there is the TABLE name syntax that you can use - where name is a table name (base table or view or cte):

SELECT * FROM ((TABLE A) UNION (TABLE B)) t ;

The parentheses are not needed:

SELECT * FROM (TABLE A UNION TABLE B) t ;

and you could even write:

TABLE A UNION TABLE B ;

Regarding whether the syntax is ISO/ANSI SQL, I found this in a (copy of a) draft of SQL 2011:

6.41 <multiset value constructor>

Function

Specify construction of a multiset.

Format

<multiset value constructor> ::=
    <multiset value constructor by enumeration>
    | <multiset value constructor by query>
    | <table value constructor by query>

<multiset value constructor by enumeration> ::=
    MULTISET <left bracket or trigraph> <multiset element list> <right bracket or trigraph>

<multiset element list> ::=
    <multiset element> [ { <comma> <multiset element> }... ]

<multiset element> ::=
    <value expression>

<multiset value constructor by query> ::=
    MULTISET <table subquery>

<table value constructor by query> ::=
    TABLE <table subquery>


MySQL / MariaDB change feeds Is there a way to get a notification when a row in a table is created or updated? Much like RethinkDB's change feeds.

Anything would work really - a message on a WS channel, or a HTTP request with the data that was created/updated...

Cannot use trigger to copy to temp table and poll, this is for a real time currency fx feed.

I've also been looking at reading from binlogs but it's too complicated, adds overhead and multiple possible points of failure.

Later edit: also tried lib_mysqludf_sys to sys_exec() a PHP script that connects to the WS server to send the update, it takes between .25 and .50 seconds, which is very very slow considering the single table I am interested in gets updated several thousand times per minute.

MySQL and MariaDB do not have any way for a TRIGGER to invoke anything outside of the MySQL/MariaDB server.  (To do so would be to create a security problem.)

Well, you could write a UDF that could do anything, and compile a special version of the server, etc.

You are probably better off polling, as you mentioned.  If there are thousands of updates per minute, the polling could "optimize" the fetch via GROUP BY and other techniques.


Avoid tables locking while doing reindex We are using PostgreSQL 10 and we need to reindex tables to reorganize data and free space.

The problem we met is that this operation locks tables that we need to remain available at all time.

We found a workaround, but this seems to free much less space than a standard reindex, so we turned that off.

Is there some solution that will allow us to reindex, and to have the table available all the time?

PostgreSQL documentation at https://www.postgresql.org/docs/10/sql-reindex.html
says the following:

  REINDEX is similar to a drop and recreate of the index in that the
  index contents are rebuilt from scratch. However, the locking
  considerations are rather different. REINDEX locks out writes but not
  reads of the index's parent table. It also takes an exclusive lock on
  the specific index being processed, which will block reads that
  attempt to use that index. In contrast, DROP INDEX momentarily takes
  an exclusive lock on the parent table, blocking both writes and reads.
  The subsequent CREATE INDEX locks out writes but not reads; since the
  index is not there, no read will attempt to use it, meaning that there
  will be no blocking but reads might be forced into expensive
  sequential scans.

So based on your requirements, you could change REINDEX into DROP INDEX + CREATE INDEX, which is what Pg::Reindex that you link to does.

The associated PostgreSQL documentation about why REINDEX is useful for B-Trees indexes reiterates the idea (at https://www.postgresql.org/docs/10/routine-reindex.html):

  REINDEX can be used safely and easily in all cases. But since the
  command requires an exclusive table lock, it is often preferable to
  execute an index rebuild with a sequence of creation and replacement
  steps. Index types that support CREATE INDEX with the CONCURRENTLY
  option can instead be recreated that way. If that is successful and
  the resulting index is valid, the original index can then be replaced
  by the newly built one using a combination of ALTER INDEX and DROP
  INDEX. When an index is used to enforce uniqueness or other
  constraints, ALTER TABLE might be necessary to swap the existing
  constraint with one enforced by the new index. Review this alternate
  multistep rebuild approach carefully before using it as there are
  limitations on which indexes can be reindexed this way, and errors
  must be handled.

Other than that the classical methodology is to create a new table, copy data into it, index it, and then rename it so that it takes the place of the old table. Of course you will need to handle properly accesses during that time, and all synchronization errors.

Also more generally how did you count about the free space gained with each method, and are you using autovacuum properly? Are you really so much constrained by space continously that you need to do this reindexing regularly? Otherwise you could schedule it during other maintenance windows.


Performance counter MSSQLInstance\SQL Errors\Errors/sec\Info Errors whats in it? Im wondering what kind of errors would show up in the:

  SQL Errors\Errors/sec\Info errors

performance counter. I read the TechNet article it just states:

  Information related to error messages that provide information to
  users but do not cause errors.

Anyone can shed some light on this, examples of what errors might show up here is more than welcome.

I dug a little deeper myself and it seems that the definition of information errors is errors with severity 10 according to the documentation of Severity and found my specific "Info errors" by profiling on the User Error Message Event.  In my case it was database context switches with the message:

  Changed database context to 'DBNAME'

and language changes with the message

  Changed language setting to us_english

And a few of the ones that @aaron-bertrand mentions


3 minutes to import 327Mo database We do database import a lot of times per day on a server with those specs :

Xeon E5-1620v2 4c/8t @ 3,8Ghz
32 Go DDR3 ECC 1600MHz
Hard Drive   2x 3 To SATA3
MariaDB 10.14 with XtraDB

Given a 327Mb Database stored on the same server that will do the import, the import will take 3 minutes which is unusually long for this.

My my.cnf :

[mysqld]
character-set-server=latin1
innodb_file_per_table=1
max_allowed_packet=64M
skip-external-locking

innodb_buffer_pool_size=15G
innodb_log_file_size=2G
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode=2

max-connect-errors=100000
max-connections=500

I'm importing the database using the MySQL ansible import module

What could improve import performance without altering the backup database file

mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method.

There are several things that you can do in the MySQL configuration that will speed up the import, but you will be missing the most important one: parallel import. If you can import using the same file in 4-8 threads, you will not get a linear performance grow, but it will scale without problems. Split the file in different tables/partitions and you will get almost a linear speedup. With specialized tools like mydumper you can get 5-10x speedup. With raw backup tools you would probably get almost-infinite speedup.

Regarding configuration, which is probably the only thing you are interested in, you can set:

innodb_flush_log_at_trx_commit = 2. This is the most important suggestion. Makes the writes inconsistent with disk, but provides a great improvement if you are not using a hardware cache. It dependes on how slow is your disk, but can give you a 10x speedup. Not safe for normal operation, many people use it for unimportant data.
innodb_doublewrite = 0. Disables corruption checks. Very unsafe, and likely to corrupt your database in case of a crash. Can give you in your case around 2x speedup. Do not run MySQL never on this mode except for the import.
checksums = none (the syntax may have changed in 10). Disables page checksum. it wont't be very useful, as your CPU is not the bottleneck
performance_schema = off- not sure the default in Maria 10, probably can be disabled for the import without problems, but it won't give you a large speedup
innodb_buffer_pool_size and innodb_log_file_size they are already large on your setup -way larger than your data-, so no need to change in your case)

There are other things, but they require changing the file, like creating the secondary indexes afterwards.


What does 'exclude rows that do not contain values' checkbox do in SQL Profiler? I want to filter a trace and wonder what does  'exclude rows that do not contain values' checkbox exactly do? Is it to filter all the data columns that are not selected in any event class?

Thanks.

It's basically this

WHERE .. AND TheValue NOT NULL

Like/Not like filters only works on values that exist for that event.
No value is always true and passes the filter: this checkbox removes that

For example:

You want to see SQL:BatchStarting events
You filter on Duration

Duration is not valid for SQL:BatchStarting, so the filter is ignored


Using X at the end of a SELECT in SQL Server Script? I'm relatively new to T-SQL and looking through my predecessors scripts, I found a line I can't quite parse. It's a hard thing to look up on my own. Hoping for advice.

This line occurs inside a WHILE loop, but I don't think that matters. I believe functionally, that this is gathering the values from a column across a few rows that share an ID and concatenating them with commas into 1 field. But I don't know what this is called or how to look it up to use it myself.

What is this 'x' doing at the end? Can anyone point me to information on this?

(select @invtid = coalesce(@invtid + ',',' ')+rtrim(RoleID)
FROM (
    Select distinct roleid
    from UserAccounts
    WHERE CustID = @custID
     )
 x)

Thanks.

The "X" is an alias for the following portion:

Select distinct roleid
from UserAccounts
WHERE CustID = @custID

So you'd be able to use it as:

SELECT x.roleid
FROM
(    Select distinct roleid
from UserAccounts
WHERE CustID = @custID) x
where x.roleid >100

In the grand scheme of things, your query would look like:

 (select @invtid = coalesce(@invtid + ',',' ')+rtrim(x.RoleID)
 FROM (
    Select distinct roleid
    from UserAccounts
    WHERE CustID = @custID
     )x
 )

technically you can use anything as your Alias.  "X", usually a more descriptive alias works.  Something like "roles"


SSDT Schema Compare doesn't work while a BULK INSERT is in progress I'm working at a large ETL and DW project where we use TFS/source control together with both SSIS and SSDT.

Today, I found out that while an SSIS package is performing a BULK INSERT into a database table, it is not possible to perform an SSDT Schema Compare against that database. This is unfortunate, as some of our packages take quite a long time to complete. We want to use the Schema Compare function to detect changes to the database structure in order to save them in our SSDT project for version control of the database.

Looking a little more into this, I found that the Schema Compare function in SSDT executes an SQL script that calls the OBJECTPROPERTY() system function on the tables in the database. Specifically in my case, any calls to OBJECTPROPERTY(<object_id>, N'IsEncrypted') seems to be blocked, when <object_id> refers to a table that is currently being bulk inserted.

In Visual Studio, the SSDT Schema Compare simply times out after a while and claims that no differences have been detected.

Is there a workaround to this issue in SSDT, or should I perhaps try to file a MS Connect bug report?

Alternatively, since the BULK INSERT happens from an SSIS package, is there perhaps some way to make this insertion without locking OBJECTPROPERTY-calls on the table? Edit: In SSIS OLE DB Destinations, we can remove the check mark from "Lock Table", which does what it says, but this might hurt performance in some situations. I am much more interested in a solution that allows the SSDT Schema Compare to do its job, even if some objects are locked.

The OBJECTPROPERTY call requires a schema stability (Sch-S) lock, which is only incompatible with a schema modification (Sch-M) lock.

The BULK INSERT will take a Sch-M lock in some circumstances. These are listed in the "Table Locking and Logging During Bulk Import" section of Guidelines for Optimizing Bulk Import in Books Online:

If the destination table is clustered, you may find enabling trace flag 610 helps. Please read the whole series of those posts and the Data Loading Performance Guide and test thoroughly if you decide to go this route.

I have no idea why SSDT checks the IsEncrypted property for tables. I cannot imagine a scenario where that makes sense, but that's a question for the SSDT people.


How to troubleshoot "BCP copy in failed" I have a table that looks something like this in a SQL server database in Azure:

CREATE TABLE [dbo].[TableName](
[col01] [int] IDENTITY(1,1) NOT NULL,
[col02] [varchar](255) NOT NULL,
[col03] [varchar](255) NOT NULL,
[col04] [datetime] NULL,
[col05] [datetime] NULL,
[col06] [nvarchar](255) NULL,
[col07] [nvarchar](max) NULL,
[col08] [nvarchar](255) NULL,
[col09] [nvarchar](max) NULL,
[col10] [int] NULL,
[col11] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I have a data file that looks a bit like this:

$ head datafile.csv
2134024,SRC001,SE,NULL,NULL,NULL,NULL,NULL,NULL,1,SRC
2134025,SRC002,SE,NULL,NULL,NULL,NULL,NULL,NULL,2,SRC
2134026,SRC003,SE,NULL,NULL,NULL,NULL,NULL,NULL,3,SRC
2134027,SRC004,SE,NULL,NULL,NULL,NULL,NULL,NULL,4,SRC
2134028,SRC005,SE,NULL,NULL,NULL,NULL,NULL,NULL,5,SRC
2134029,SRC006,SE,NULL,NULL,NULL,NULL,NULL,NULL,6,SRC
2134030,SRC007,SE,NULL,NULL,NULL,NULL,NULL,NULL,7,SRC
2134031,SRC008,SE,NULL,NULL,NULL,NULL,NULL,NULL,8,SRC
2134032,SRC009,SE,NULL,NULL,NULL,NULL,NULL,NULL,9,SRC
2134033,SRC010,SE,NULL,NULL,NULL,NULL,NULL,NULL,10,SRC
$ wc -l
713224
$

I try to import it using bcp running on my linux laptop:

$ bcp TableName in ./datafile.csv -S niceclouddbservername.database.windows.net -U myusername -dmydatabasename -c -t ','
Password: <I paste my password here>

Starting copy... <this takes a few seconds>

BCP copy in failed
$

Two questions, one is interesting right now:

Why doesn't this work?
How can I debug it?

What I have done so far:

Used duckduckgo  (and google for good measure) to look for an explanation
Re-read the docs, found other examples, tried different variations
Tried with just the first 10 records
Verified (tried to at least) that the datatypes matches the input
Testing with a known wrong password to verify that it would produce a different error message
Consulted everything that seemed remotely related from the list of "Similar Questions"

Here is how I recommend doing it, which is tailored for character data:

generate format file
edit format file to match the input data structure
upload the data file using the format file generated at step 2

corresponding commands are then as follows:

run bcp [<my_db_name>].<my_schema_name>.<my_table_name> format nul -c -f
<my_format_file_name>.fmt -t\<my_delimiter_char> -S <my_db_server>
-U <my_db_user>
use any text editor if edition is needed
run bcp [<my_db_name>].<my_schema_name>.<my_table_name> in <my_data_file> -f <my_format_file_name>.fmt -t\<my_delimiter_char>  -b <my_batch_size> -m <my_max_errors> -e <my_error_file>  -S <my_db_server> -U <my_db_user>

Then, you can inspect <my_error_file> to see how it went. Execution without any error should produce empty <my_error_file>.


Creating roles and granting rights to the role I wanted to create a role called "user_updates" and this role should have the rights "table_note_updates".
The rights called "table_note_updates" is already available in my database. How to grant the rights to role that i create.

A role can be granted to another role.
create role user_updates;
grant table_note_updates to user_updates;


MonetDB ODBC driver memory allocation error on Windows The driver is the latest from a month ago: 2014-SP2: 11.17.17, 64-bit.
Windows is Server 2012.

I have limited options to test whether earlier versions of the driver would work or this version works on other systems. I also found no other resource on this version of Windows whether ODBC works outside of Stata (ODBC Data Sources does see the database, but there is no way to test or query it).  I received little tech support about troubleshooting this error:

To be clear, I noticed that MonetDB.org instructs us to install both the 64-bit and the 32-bit ODBC driver. I did that. The following screenshot shows that the MonetDB server is running, and Windows sees both the 32-bit and 64-bit data sources I set up (I only changed the names of the database to ifn, the rest are the defaults of MonetDB).

I understand this is not a coding issue, slightly off topic, though probably relevant for the DBA community.

They just released new server & driver packages (7/25/2014). I had the same issue with the old drivers but with the 7/25 release it is gone.


Migrating data from 11G Linux to 19C on Windows I'm using datapump to move data from 11.2.0.4 on Linux to 19C on Windows. It seems to be working except for some objects in the system schemas. Is there any reason to bring these schemas across or can I simply exclude them?

Always exclude them. They contain version and instance-specific metadata that should not be migrated. When performing this kind of migration (porting applications to new DB versions), only ever export/import your application schemas and user accounts. Never do a "full" export/import.
If you have already overwritten some data dictionary objects and experienced errors, you may need to validate the state of your dictionary (see here: https://oracle-base.com/articles/misc/hcheck-sql-check-known-data-dictionary-problems) and consider a restore from backup to a point prior to your import.


Is there any disadvantage to using Oracle's Easy Connect syntax? Oracle allows a few different syntaxes for specifying where to find your database service. The one I've seen most commonly (in the few short years I've been developing software professionally) looks something like this:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)))

I don't think this syntax has any particular name, but it is what Oracle generates in its TNS files if you use graphical tools to modify it. But Oracle also allows the following much more compact syntax, at least with tools like SQL*Plus and application divers:

myserver:1521/myservice

This is known as Easy Connect, and I have read that it was introduced by Oracle 10g. That makes it around 10 years old. I'm not sure if this can be used in a TNS file, and as short and simple as it is, I don't see much need to put it in a TNS file. Which is the good thing about it in my opinion: you can just use it directly with your application instead of needing to maintain a TNS file.

However, from the work I've done and the clients I've worked with, its usage seems to be rather uncommon. The only reason I even know about it is because ESRI started recommending its usage for establishing connections in newer versions of ArcGIS. That leaves me wondering why it's uncommon, so is there some kind of downside to using Easy Connect that I'm missing?

(I'd appreciate if someone could add more tags. I couldn't think of/find any more that made sense.)

The only real downside is that there are a number of configuration options that you can put in a TNS alias that you can't use when you use the easy connect syntax.  Easy connect is designed to simplify the syntax for simple connections.  In order to do that, though, it loses the ability to create more complex TNS aliases that do things like load balancing/ failover or that request a dedicated server session.  If you don't need any of that, there is no downside to using the simpler syntax.  As soon as an organization has one use case that uses something that the easy connect syntax doesn't support, though, it either has to support a hybrid configuration where some things use easy connect and others use TNS aliases or it has to use TNS aliases everywhere.

Supporting hybrid configurations or changing how connections are handled can be somewhat painful depending on your perspective.  It's relatively easy for the Oracle client to handle a bunch of different naming conventions.  It's much more difficult for an organization to support.

It's relatively easy for organizations to come up with ways of distributing a single, shared tnsnames.ora file to everyone or to stand up a central LDAP server to host the organization's TNS information.  If some applications use easy connect syntax and some organizations use TNS aliases, that can be problematic when an organization wants to do things like consolidate databases or move databases from one host to another.  If an organization wants to add load balancing support, it can be problematic when some applications fail over automatically and some don't.  Someone has to track which applications are which, admins have to treat applications differently rather than being able to count on organization conventions that are adhered to across the board.  None of this is impossible, of course.  But consistency across the environment makes DBAs and admins much more productive and allows teams to support many more applications/ databases/ whatever with any given level of staffing.


Best approach to apply a long list of LIKE patterns? As a follow-up to this question, I have one of my own.
The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously the SQL is pretty hairy. The OP's question concerned SQL Server 2012, my question, however, is about PostgreSQL.
In my answer, I proposed the use of a VIEW as a "one-stop-shop" solution - i.e. declare the VIEW once, use it anywhere - and this applies for any query in the future also and any variant thereof.
Another poster (@AndriyM) proposed the use of a CROSS APPLY to resolve the issue which is another solution. The PostgreSQL syntax is JOIN LATERAL
I then added a CTE (Common Table Expression) to my original answer as yet another possible solution.
So, the OP now has 5 options:

CASE
VIEW
JOIN LATERAL (CROSS APPLY for SQL Server)
CTE
Separate table

I excluded the option of changing the underlying data since, frequently in this forum, consultants/DBA's/programmers are not allowed change underlying data - makes the answers more interesting also!
Obviously, a CASE expression with > 100 options (x4) is horribly cumbersome and complex - but when is it a good idea to use CASE and at what point does it become a minus rather than a plus?
In my opinion (and not just because it's my answer!), a VIEW is the optimal solution - it's simple, will work for all RDBMS's and is permanent and will work for all queries now and into the future should the OP wish to modify the query.
The JOIN LATERAL construct will work also as a kind of derived table, which is pretty much what a CTE is also. They can both be used down the line in the same query.
Which of the 5 approaches is better/best and at what point does the technical (ease of use, speed, query plan optimisation) tilt in the particular solution's favour?

I would use a translation table in a LATERAL subquery. Demo (Postgres 10+):
CREATE TABLE ac_translate (
   ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , like_pattern text NOT NULL
 , target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES
   ('AIR NEW Z%'       , 'AIR NEW ZEALAND')  -- rows in order of precedence!
 , ('AIR BP%'          , 'AIR BP')
 , ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
 , ('AIA%'             , 'AIA')
;

See:

Does Postgres preserve insertion order of records?

Manipulate ord_nr to adjust priorities.
Query:
SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;
Or with a correlated subquery:
SELECT COALESCE(
        (SELECT a1.target
         FROM   ac_translate a1
         WHERE  ac.accountname LIKE a1.like_pattern
         ORDER  BY a1.ord_nr
         LIMIT  1), ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;

This is easy to handle, keeps the long list of options out of the code and puts it into a table where it can be maintained properly. And is moderately fast.
We can't easily use a plain LEFT JOIN ac_translate since CASE traverses patterns in order to return the single, first match. We can't just join to a set, that might return multiple matches if one pattern is the prefix of the other, like 'AIR%' and 'AIR N%'. So we use an ordering number in the translation table to prioritize matches in the subquery.
The ELSE clause in the referenced question resolves to the original value. That's implemented with COALESCE here. Basically, this combines the virtues of the top two answers over there.
To top it off, I slipped in GROUP BY 1 as another way to avoid repeating lengthy expressions (which is not actually needed here any more). See:

Concatenate multiple result rows of one column into one, group by another column [duplicate]

Speed
Performance deteriorates with the number of rows in the translation table since Postgres is forced to walk through all of them sequentially and evaluate the LIKE expression. If that's not fast enough any more, we need index support, but the expression is not "sargable" - the expression we need to index is to the right of the operator and there is no COMMUTATOR for LIKE. Details:

Can PostgreSQL index array columns?

There is a workaround, though. My example requires that patterns have at least 3 leading characters (3 is my arbitrary pick). Add a CHECK constraint in the translation table to enforce this rule, and an expression index on the leading trigram:
CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));

Adapt the query:
SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  left(ac.accountname, 3) = left(a1.like_pattern, 3)
   AND    ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;
With enough rows in the translation table (and favorable estimates and cost settings) Postgres will use a very fast index scan to narrow it down to the few candidates (if any) and only filter the rest with the LIKE expression. Should scale just fine. I added the EXPLAIN output to the fiddle as proof of concept:
db<>fiddle here


SSDT Schema Compare fails when comparing my DB Project to my Azure Server I have a SQL Database project that I have built our enterprise DB on. It has deployed several times on internal and AWS hosted SQL servers using SSDT's Schema Compare tool.

The problem when I post to Azure Hosted Win 2012 Server running SQL Ent 2012 sp2. It comes back with "Comparison complete. No differences detected."

I know this is wrong, because I can open Enterprise Manager and compare the schema to the SQL Project and see that there is a difference.

I found several articles talking about how the 2014 release broke the tool, but those were in version differences.

[Yes I did Google this. Stating because I am notorious in forgetting to do so. ] https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=ssdt+data+compare+fail+to+detect+difference&start=10

Other things I have checked include making sure my DB account has unlimited access. I can connect with Management console. I can connect with local programs.

Last confirmation that there was a problem:

I created an SP that has a single return of the number 1.

It could have a return of nothing for the sake of testing.

After I created the SP, I ran schema compare on all of my instances and all but the Azure server showed the variance.

Update

I have verified that this is explicitly with the server, in that now two different users on two different computers are having the same exact problem.

TL;DR

The query Visual Studio is executing is being blocked and the query times out.

Turns out I've managed to work this out for our situation: timing out due to blocking!

I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events.

It would reach a certain query and then stop, when VS would claim that the comparison was complete.

Here's the batch of queries in question:

select object_id as id,
    null as id2,
    case
        when [type] != 'D ' then [type]
        when parent_object_id != 0 then 'D '
        else 'LD'
    end as [type],
    schema_name(schema_id) as name1,
    name as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.objects
where
    type != 'S'
    and is_ms_shipped != 1

union

select principal_id as id,
    null as id2,
    type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.database_principals

union

select assembly_id as id,
    null as id2,
    'CLR' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.assemblies
where
    is_user_defined =1

union

select schema_id as id,
    null as id2,
    'SCH' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.schemas

union

select user_type_id as id,
    null as id2,
    case
        when is_assembly_type = 1 then 'UDT'
        when is_table_type = 1 then 'TT'
        else 'UDDT'
    end as [type],
    schema_name(schema_id) as name1,
    name as name2,
    null as name3,
    null as a1,
    binary_checksum(system_type_id, schema_id, max_length, precision, scale, is_nullable, is_assembly_type, default_object_id, rule_object_id) as v1,
    null as mod
from sys.types
where
    is_user_defined = 1

union

select idx.object_id as id,
    idx.index_id as id2,
    case
        when idx.type = 3 then 'XIX'
        when idx.type = 4 then 'SIX'
        when idx.type = 6 then 'CIX'
        else 'IDX'
    end as [type],
    schema_name(tab.schema_id) as name,
    tab.name as name2,
    idx.name as name3,
    null as a1,
    binary_checksum(is_unique, data_space_id, ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks) as v1,
    null as mod
from sys.indexes idx
    inner join sys.tables tab on idx.object_id = tab.object_id
where
    idx.is_primary_key = 0
    and idx.name is not null
    and idx.is_unique_constraint = 0
    and idx.is_hypothetical = 0

union

select stat.object_id as id,
    stat.stats_id as id2,
    'STAT' as [type],
    schema_name(tab.schema_id) as name,
    tab.name as name2,
    stat.name as name3,
    null as a1,
    no_recompute as v1,
    null as mod
from sys.stats stat
    inner join sys.tables tab on stat.object_id = tab.object_id
where
    stat.user_created = 1

union

select xml_collection_id as id,
    null as id2,
    'XSC' as type,
    schema_name(schema_id),
    name as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.xml_schema_collections
where xml_collection_id > 1

union

select
    object_id id,
    null id2,
    'DDT' as type,
    name,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.triggers
where
    parent_class = 0

union

select
    database_specification_id id,
    null as id2,
    'DAS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.database_audit_specifications

union

select
    certificate_id id,
    null as id2,
    'CERT' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(pvt_key_encryption_type, thumbprint) as v1,
    null as mod
from sys.certificates

union

select
    asymmetric_key_id id,
    null as id2,
    'ASMK' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.asymmetric_keys

union

select
    symmetric_key_id id,
    null as id2,
    case
        when name = N'##MS_DatabaseMasterKey##' then 'MK'
        else 'SYMK'
    end as type,
    case
        when name = N'##MS_DatabaseMasterKey##' then null
        else name
    end as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.symmetric_keys
where
    name = N'##MS_DatabaseMasterKey##'
    or name not like N'##%'

union

select
    fulltext_catalog_id id,
    null as id2,
    'FTC' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(is_default, is_accent_sensitivity_on) as v1,
    null as mod
from sys.fulltext_catalogs

union

select
    ft.object_id id,
    ft.unique_index_id as id2,
    'FTI' as type,
    schema_name(o.schema_id) as name1,
    o.name as name2,
    null as name3,
    null as a1,
    binary_checksum(is_enabled, change_tracking_state) as v1,
    null as mod
from sys.fulltext_indexes ft
inner join sys.objects o on ft.object_id = o.object_id

union

select
    stoplist_id id,
    null as id2,
    'FTSL' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.fulltext_stoplists

union

select
    data_space_id id,
    null as id2,
    [type] as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    is_default as v1,
    null as mod
from sys.data_spaces

union

select
    function_id id,
    null as id2,
    'PF' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.partition_functions

union

select
    file_id id,
    null as id2,
    'FILE' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(type, data_space_id, physical_name, state, growth, is_read_only, is_sparse, is_percent_growth) as v1,
    null as mod
from sys.database_files

union

select
    message_type_id id,
    null as id2,
    'MT' as [type],
    name collate database_default as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(validation, xml_collection_id) as v1,
    null as mod
from sys.service_message_types

union

select
    service_contract_id id,
    null as id2,
    'SC' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.service_contracts

union

select
    service_id id,
    null as id2,
    'SERVICE' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(service_queue_id) as v1,
    null as mod
from sys.services

union

select
    route_id id,
    null as id2,
    'ROUTE' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(remote_service_name, broker_instance, lifetime, address, mirror_address) as v1,
    null as mod
from sys.routes

union

select
    remote_service_binding_id id,
    null as id2,
    'RSB' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(service_contract_id, remote_principal_id, is_anonymous_on) as v1,
    null as mod
from sys.remote_service_bindings

union

select
    object_id id,
    null as id2,
    'DEN' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.event_notifications

union

select
    priority_id id,
    null as id2,
    'SBP' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(service_contract_id, local_service_id, remote_service_name, priority) as v1,
    null as mod
from sys.conversation_priorities

union

select
    principal_id id,
    null as id2,
    'LOG' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_principals
where
    type != 'R'

union

select
    audit_id id,
    null as id2,
    'SAD' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_audits

union

select
    credential_id id,
    null as id2,
    'CRD' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.credentials

union

select
    msg.message_id id,
    msg.language_id as id2,
    'SEM' as type,
    cast(msg.message_id as sysname) as name1,
    lang.alias collate database_default as name2,
    null as name3,
    null as a1,
    binary_checksum(msg.severity, msg.is_event_logged, msg.text) as v1,
    null as mod
from sys.messages msg inner join sys.syslanguages lang on (msg.language_id = lang.lcid)
where
    msg.message_id > 50000

union

select
    base.endpoint_id id,
    null as id2,
    'SEP' as type,
    base.name as name1,
    null as name2,
    null as name3,
    base.principal_id as a1,
    binary_checksum(*) as v1,
    null as mod
from sys.endpoints base
    left outer join sys.http_endpoints http on (base.endpoint_id = http.endpoint_id)
    left outer join sys.service_broker_endpoints sb on (base.endpoint_id = sb.endpoint_id)
    left outer join sys.soap_endpoints soap on (base.endpoint_id = soap.endpoint_id)
    left outer join sys.tcp_endpoints tcp on (base.endpoint_id = tcp.endpoint_id)
    left outer join sys.database_mirroring_endpoints mirror on (base.endpoint_id = mirror.endpoint_id)

union

select
    server_id id,
    null as id2,
    'SLS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.servers

union

select
    logins.server_id id,
    logins.local_principal_id as id2,
    'SLL' as type,
    srvs.name as name1,
    lp.name as name2,
    null as name3,
    null as a1,
    null as v1,
    logins.modify_date as mod
from sys.linked_logins logins
    inner join sys.servers srvs on (logins.server_id = srvs.server_id)
    left join sys.server_principals lp on (logins.local_principal_id = lp.principal_id)

union

select
    object_id id,
    null as id2,
    'SST' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_triggers
where
    is_ms_shipped = 0

union

select
    server_specification_id id,
    null as id2,
    'SAS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_audit_specifications

union

select
    event_session_id id,
    null as id2,
    'SES' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    null as mod
from sys.server_event_sessions

union

select
    provider_id id,
    null as id2,
    'SCP' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(guid, version, dll_path, is_enabled) as v1,
    null as mod
from sys.cryptographic_providers

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    binary_checksum(event_id, name, package, module) as minorid,
    name as name,
    null as id1,
    null as id2
from sys.server_event_session_actions

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    event_id as minorid,
    name as name,
    binary_checksum(predicate) as id1,
    null as id2
from sys.server_event_session_events

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    object_id as minorid,
    name as name,
    binary_checksum(value) as id1,
    null as id2
from sys.server_event_session_fields

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    event_id as minorid,
    name as name,
    null as id1,
    null as id2
from sys.server_event_session_events

union

select
    perms.class as class,
    objectpropertyex(perms.major_id, 'BaseType') as type,
    perms.major_id as majorid,
    perms.minor_id as minorid,
    perms.type + perms.state collate database_default as name,
    perms.grantee_principal_id as id1,
    perms.grantor_principal_id as id2
from sys.database_permissions perms
where
    perms.major_id >= 0

union

select
    props.class as class,
    case
        when objs.parent_object_id > 0 then objectpropertyex(objs.parent_object_id, 'BaseType')
        else objectpropertyex(props.major_id, 'BaseType')
    end as type,
    case
        when objs.parent_object_id > 0 then objs.parent_object_id
        else props.major_id
    end as majorid,
    props.minor_id as minorid,
    props.name as name,
    binary_checksum(props.value) as id1,
    null as id2
from sys.extended_properties props left outer join sys.objects objs on props.major_id = objs.object_id

union

select
    cast(4 as tinyint) as class,
    'R' as type,
    roles.role_principal_id as majorid,
    roles.member_principal_id as minorid,
    'drm' as name,
    null as id1,
    null as id2
from sys.database_role_members roles

union

select
    cast(6 as tinyint) as class,
    'TT' as type,
    tt.user_type_id as majorid,
    cols.column_id as minorid,
    cols.name as name,
    binary_checksum(cols.user_type_id, cols.max_length, cols.precision, cols.scale,
        cols.collation_name, cols.is_nullable, cols.is_ansi_padded, cols.is_rowguidcol, cols.is_identity,
        cols.is_computed, cols.is_xml_document, cols.xml_collection_id, cols.default_object_id, cols.rule_object_id) as id1,
    null as id2
from sys.table_types tt inner join sys.columns cols on (tt.type_table_object_id = cols.object_id)

select
    cols.object_id as tableid,
    cols.column_id as columnid,
    binary_checksum(cols.name) as checksum
from sys.tables tabs
    inner join sys.columns cols  on (tabs.object_id = cols.object_id)
    inner join sys.index_columns idx_cols on (idx_cols.object_id = tabs.object_id and idx_cols.column_id = cols.column_id)
where
    tabs.is_ms_shipped = 0

union

select
    object_id as tableid,
    column_id as columnid,
    binary_checksum(type_column_id, language_id) as checksum
from sys.fulltext_index_columns

Running the query in SSMS would never finish executing on the offending server but would take less than a second on every other environment.

So I started to pull it apart and found that the queries that are reading from the following 3 system views were the problem:

sys.server_event_session_actions
sys.server_event_session_events
sys.server_event_session_fields

The first of these system views only contained 28 records (found by running a Count(*)), but wouldn't return the data unless I used the (NOLOCK) hint... [b]locking problem!

Finding out the head of the blocking chain was easy enough but it was not killable as it was an Extended Events session. There's a fix for this issue here: https://support.microsoft.com/en-gb/kb/2511963

But hopefully in your case you can just kill off the head of the chain nicely and return things to normal!

EDIT: Having now had a viable maintenance window for the affected SQL Server, a restart has cleared the SPID and now compares are working once more!

Also, here's a quick script I wrote to find the head of the blocking chain(s) that might be helpful for future readers: http://gvee.co.uk/files/sql/admin.blocking_chains.sql

IF Object_ID('admin.blocking_chains', 'V') IS NULL
  BEGIN
    EXEC ('CREATE VIEW admin.blocking_chains AS SELECT NULL As buffer;');
  END
;
GO

ALTER VIEW admin.blocking_chains
  AS
WITH processes AS (
  SELECT s.spid
       , s.blocked As blocking_spid
       , DB_Name(s.dbid) As database_name
       , s.program_name
       , Cast(x.text As varchar(Max)) As definition
       , hostname As host_name
       , loginame As login_name
       , nt_domain
       , nt_username
  FROM   sys.sysprocesses (NOLOCK) As s
   CROSS
    APPLY sys.dm_exec_sql_text(s.sql_handle) As x
  WHERE s.spid > 50
)
, blocking (spid, blocking_spid, blocking_statement, sequence, level, database_name) AS (
  SELECT s.spid
       , s.blocking_spid
       , s.definition
       , Row_Number() OVER (ORDER BY s.spid) As sequence
       , 0 As level
       , s.database_name
  FROM   processes As s
   INNER
    JOIN processes As s1
      ON s1.blocking_spid = s.spid
  WHERE  s.blocking_spid = 0

    UNION ALL

      SELECT r.spid
           , r.blocking_spid
           , r.definition
           , d.sequence
           , d.level + 1
           , r.database_name
      FROM   processes As r
       INNER
        JOIN blocking As d
          ON d.spid = r.blocking_spid
      WHERE  r.blocking_spid > 0
)
, results AS (
  SELECT blocking_spid
       , spid
       , blocking_statement
       , level
       , Min(sequence) As sequence
       , Min(database_name) As database_name
  FROM   blocking
  GROUP
      BY blocking_spid
       , spid
       , blocking_statement
       , level
)
SELECT Cast(CASE WHEN results.blocking_spid = 0 THEN 1 ELSE 0 END As bit) As is_head_of_chain
     , results.sequence
     , results.level
     , results.database_name
     , results.spid
     , results.blocking_spid
     , results.blocking_statement
     , processes.host_name
     , processes.program_name
     , processes.login_name
     , processes.nt_domain
     , processes.nt_username
     , Right('0000' + Cast(results.sequence As varchar(11)), 5) + '-' + Right('0000' + Cast(results.level As varchar(11)), 5) As sort_order
FROM   results
 LEFT
  JOIN processes
    ON processes.spid = results.spid
;


Using Composite Keys To Define "Dynamic" Relationship Types The scenario
I'm working on a project that has pretty heavy relationship requirements. We have two competing ideas on how to approach the database design, and we're split on which one to use.
We have two types of entities, pet and adult. We are recording the common columns in a "master" table called Organism, and the columns only applicable to pets and adults are in their respective tables, Pet and Adult.
The issue is how to relate them, as well as defining relationship type. If it was just a matter of relating pet to adults, this would be simple: a "bridge" table between the two. The complexity comes in where we have to also relate pet to pet and adult to adult. We might need to say one adult is the spouse to another adult, or the pet sitter to another adult. We may need to say two pets live together, or don't live together but are blood related. We may need to later add other tables like Vet where we relate both Pet and Adult tables to as well.
Idea no. 1
So the first idea is that for each type of relationship we add in a "bridge" table, so one between Pet and Adult, and to relate Pet to Pet we would have a "bridge" table that relates Pet to Organism, and another "bridge" table to relate Adult to Organism.
If we later added Vet, we would need to add a "bridge" table between it and Adult as well as between Vet and Pet, and continue adding more "bridge" tables for each new relationship.
Idea no. 2
The other idea is to create a "dynamic" relationship table that has composite keys for both primary and foreign keys, so:
PrimaryID    PrimarySource  Relationship  ForeignID    ForeignSource
---------    -------------  ------------  ---------    -------------
1            Adult          Pet Sitter    4            Pet
2            Adult          Spouse        10           Adult
42           Pet            Blood Sibling 76           Pet

The "source" columns determine what tables the relationships are from, and can scale up so that any additional relationships are easy to make.
Considerations
The first option from what I'm told is more standard but the people against the second method can't seem to come up with any practical reason why this design would fail. I am interested in the practical application here, and what issues could arise in the two systems, if any. Is there a clear right answer here, and if so, what is it?

The relationships can be simplified as

Entity <---Realtionship---> Entity

Like this

create table entity (id, entity_type /** etc **/)
create table relation (id, from_id, to_id, relation_type_id)
create table relation_type (id, name)

Pet/owner would then be

Entity(1, 'Dog')
Entity(2, 'Person')
Relation(n, 1, 2, 'Owner')

This concept also accomodates shared ownership, i.e. a dog owned by two people:

create table relation(id, from_id, to_id, relation_type_id, share)
Entity(3, 'Person')
Relation(n, 1, 2, 'Owner', 50.0)
Relation(n, 1, 3, 'Owner', 50.0)

You should of course create constrains/checks to ensure that share is always 100 etc.

If you need to enforce business rules, for example A pet can only be owned by a person, and a person can not be owned by anybody

create table allowed_relations(from_entity_type_id, to_entity_type_id, type_id)

In order to enforce this you will need to create a trigger on the     relation table and check if the proposed relation matches the allowed_relations table. If the number of business rules is small (and seldom changes), the rules can be hardcoded as a check constraint on the relation table.


Implications of using PostgreSQL COPY command to stdout and then immeadiately reading it from stdin When transferring data between two databases, I would like to know if there is any disadvantages in the following approach:

psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2]  -d [DB2] -U postgres -c [SQL2]

where

[SQL1]="\copy (SELECT [FIELDS_SUBSET] FROM [TABLE_NAME]) TO STDOUT"

[SQL2]="\copy [NEW_TABLE_NAME]( [FIELDS_SUBSET] ) FROM STDIN"

Basically, I extract some data from DB1 to stdout and then I immediately read it from stdin in order to import this data into DB2. Both databases are in different networks. This approach removes the need of an intermediate file.

In this context, I would like to know if this strategy has any drawbacks if compared to using intermediate files. For instance, is this approach suitable for transfering large volumes of data (GBs of data)?

Also, by using COPY with STDIN and STDOUT, do I still have to worry about the COPY command caveats? (Reference)

The drawback is that if the operation fails part way through due to network hiccup, a reboot of HOST1, or something like that, you've done a lot of work on DB2 that needs to be rolled back and repeated.  This can blow up your WAL archive, and can lead to table bloat if you don't vacuum the table in DB2 before repeating the operation.  I'd spool it to a local-storage file on HOST2 first, unless I had a good reason not to.

Most of the caveats you link to are about PostgreSQL interpreting the data format in a different way than Excel, Python, Perl, human beings, etc. might interpret it.  Going PostgreSQL to PostgreSQL (either with a pipe or using a file intermediate) eliminates most of those caveats, assuming you choose the same options to \copy on each end and have the same table structure.  The caveats about performance and foreign keys, however, remain.


Checking @@ROWCOUNT failing I have a code similar to the one below in one of my SQL procedures,

declare @rowcount int

update table1
set value = @value
where id = @id

select @rowcount = @@ROWCOUNT

 if ( @rowcount = 0 )
 begin
    insert into table1(id, value1,value2...)
    select (@id, @value1, @value2...)
 end

But it fails rarely, like once in a day etc. That means the data exists in the table and @@rowcount is 0 and it tried to insert data and primary key violation happening. Here all values which are inserting ie. id,value1,value2 etc are integers. Any thoughts?

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.
set transaction isolation level serializable;
begin transaction;

update dbo.table1
  set value = @value
  where id = @id;

if (@@ROWCOUNT = 0)
begin
  insert dbo.table1(id, value1,value2...)
    values(@id, @value1, @value2...);
end

commit transaction;

I blogged about this since writing this answer:

Please stop using this UPSERT anti-pattern


Defining the host on a new installation of Oracle 12c Enterprise Manager I just started working with Oracle's products, and I downloaded the "Developer Day" VM from the following link:

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

The VM boots up fine. Then, I proceeded to install the Enterprise Manager, from the following link:

http://www.oracle.com/technetwork/oem/grid-control/downloads/linuxx8664soft-085949.html

It was only missing one prerequisite (glibc-devel.i686), which installed without an issue. The problem I have is when I get to the page which asks for the host... by default, it says "localhost," but the setup doesn't like this, and doesn't let me proceed.

Changing it to another irrelevant value doesn't work.

Sorry if this is on the wrong "stack," I assumed that dba would be the best.

1- Avindra  follow this link to a list of how to articles on Oracle Enterprise Manager 12c

2- And as for your host question, you need to setup a name for your host and that name must carry an valid ip address, unless you want to monitor all targets on the localhost.

To change your host name follow this article


How do I use the IF...ELSE condition in a WHERE clause? I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.

Should this work?

CREATE OR REPLACE package body If_Else_Pack IS

    PROCEDURE Moving
    (
        obj_A       IN varchar2,
        obj_B       IN varchar2,
        obj_C       IN varchar2,
        obj_D       IN varchar2,
        cur_Result  OUT T_CURSOR
    ) IS
    BEGIN
        OPEN cur_Result FOR
        SELECT
            w.assetid,
            w.vehiclenumber,
            w.LatLong,
            w.CurrentSpeed,
            w.timeOfMovement,
            w.CurrentPlace,
            w.curTime,
            w.motion,
            w.fuelR,
            w.VehicleStart
        FROM waypoints1 w
        WHERE
            IF ((obj_D= '0' OR obj_D IS NULL) AND (obj_C= '0' OR  obj_C IS NULL)) THEN
                WHERE w.customer_id =obj_A
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D= '0 'OR obj_D IS NULL) AND (obj_C<> '0' OR obj_C IS NOT NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.category_id = obj_C
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D<> '0' OR obj_Dis NOT NULL) AND(obj_C= '0' OR obj_C IS NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.fleet_id = obj_D
                AND w.delegate_user_id = obj_B;
    END MOVING;
END IF_ELSE_PACK;

I think the question is a little misleading & causes people to not think properly. Looks like the 3 conditions won't overlap, so all you need to do is OR the 3 statements together:

create or replace package body If_Else_Pack is

  Procedure Moving(obj_A IN varchar2,
                   obj_B  IN varchar2,
                   obj_C IN varchar2,
                   obj_D    IN varchar2,
                   cur_Result     OUT T_CURSOR) is

   begin

        open cur_Result for
        select w.assetid,
                 w.vehiclenumber,
                 w.LatLong,
                 w.CurrentSpeed,
                 w.timeOfMovement,
                 w.CurrentPlace,
                 w.curTime,
                 w.motion,
                 w.fuelR,
                 w.VehicleStart
        from waypoints1 w
        where
        (((obj_D= '0' or obj_D is null) and (obj_C= '0' oR  obj_C is null))
                 and w.customer_id =obj_A
                 and w.delegate_user_id = obj_B)
        or
        (((obj_D= '0 'or obj_D is null) and (obj_C<> '0' or obj_C is not null))
           and w.customer_id = obj_A
           and w.category_id = obj_C
           and w.delegate_user_id = obj_B)
        or
        (((obj_D<> '0' or obj_D is not null) and (obj_C= '0' or obj_C is null))
           and w.customer_id = obj_A
           and w.fleet_id = obj_D
           and w.delegate_user_id = obj_B);
END MOVING;
END IF_ELSE_PACK;

The WHERE condition can be further simplified to:

        WHERE
              w.customer_id = obj_A
          AND
              w.delegate_user_id = obj_B
          AND
              ( (obj_D = '0' or obj_D IS NULL) AND (obj_C= '0' OR obj_C IS NULL)
             OR
                (obj_D = '0' or obj_D IS NULL) AND (w.category_id = obj_C)
             OR
                (w.fleet_id = obj_D) AND (obj_C= '0' OR obj_C IS NULL)
              ) ;


query processor ran out of internal resources - extremely long sql query I have a SQL Server 2012  database with around 2000 tables (daily log files) with around 20 million records each.
Output of select @@Version

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: )

Now, in order to generate a yearly or 3-years report, I need to run query on each table and "Union All" the results. The queries are generated dynamically and are usually small in size. However after doing "Union all: with 1000 tables, it become a single huge query.

While processing such queries, I am getting an error as highlighted below.

The application is already built, tested and in live production and hence I cannot use temp tables or hash tables to store intermediate results.

Now, how do I get rid off this error message?

  The query processor ran out of internal resources and could not
  produce a query plan. This is a rare event and only expected for
  extremely complex queries or queries that reference a very large
  number of tables or partitions.

The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead.

As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance.

This carries a range of advantages:

Temp table can be indexed for performance
You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached)
The query won't take forever to "compile" (i.e. turn into an execution plan)

I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.


SQL Server - convert complicated view to table - the best way The view I'm building is getting more complicated and bigger every week. The idea for it was to have a source for a number of multiple reports for management, including ad-hoc queries done by some of them (Accountants - what can I say). I'm not worried about the load on the server - this one is dedicated just for that, so it will be a while until any serious resources got used. But it's taking longer and longer to do anything on it.

It's about 30% calculated fields, has at the moment with 25 joins of which I can drop maybe 8, but there will be more added at some point. No way to make it indexed, as it's spanning 4 different databases (for now), so performance is tortoisery at best.

So I'm thinking to dump the contents of the view into a table on schedule and the question is: is there a better way? And if not, what obstacles to look out for?

EDIT: Staleness of data: up to previous day. Data range: all rows, unfortunately, with current count 3.2 million.
Also, there is the issue of the development phase - right now we're working out what we need, want and desire, so the performance tuning is pointless ATT. Indexing would be a good start, but not right now on this view. Split into multiple views/queries sounds good, but will not cut down execution time in any meaningful sense (down 20 minutes sounds great until you realize that starting point is 75 minutes).

Data I'm using is already copied from other databases(and servers) via SSIS package. Reworking it to consolidate data in one database might be doable, but that's not my jurisdiction. This may involve financial costs and until all the dev work is finalized will be rejected by management.

All I know that I need to speed things up considerably and fast. May be dirty. Unless I'll spend more time waiting for queries to execute than on development proper.

I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over.

Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason.

A more sensible approach would be to have a separate, optimized, query for each report and have the bare minimum tables involved. I know that this sounds like a lot of work, but code reuse is really difficult to achieve in T-SQL without giving up performance.

That said, if all your reports are based on more or less the same data, an indexed view or a materialized intermediate table could be a viable solution. Indexed views have some limitations that make them impractical in some contexts (no self joins, no outer joins, no UNIONs etc...) and may introduce an unacceptable write overhead, but are really powerful when used correctly.


mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error .

 # mysqlbinlog mysql-bin.000035 | mysql -u root -p db
 Enter password:
 ERROR 1032 (HY000) at line 48: Can't find record in 'dlfilerank'

dlfilerank is used by Liferay CMS App and the total records for this table keeps on changing when Liferay Portal is being accessed.

Following are the latest few binary log files generated by MySQL in my DB Server.

-rw-rw---- 1 mysql mysql 1073742407 Dec 27 18:26 /var/lib/mysql/mysql-bin.000033
-rw-rw---- 1 mysql mysql 1073742182 Feb  2 18:58 /var/lib/mysql/mysql-bin.000034
-rw-rw---- 1 mysql mysql        665 Feb  2 18:58 /var/lib/mysql/mysql-bin.index
-rw-rw---- 1 mysql mysql  422392027 Feb 18 15:50 /var/lib/mysql/mysql-bin.000035

From the above listing , we can see that the latest binary log (mysql-bin.0000350) was created around Feb 2,19:00 and all DB transactions from this timestamp onwards are being written to mysql-bin.000035

Now since latest binary log was created on  Feb 2, , so I am considering full backup taken using mysqldumpon Feb 2. For full backup , we have an automated script using mysqldump which runs every midnight to take the backup.

So , to test restore for this setup , I am doing the  following steps:

Restore from full backup taken using mysqldump on  Feb 2
Next step , I execute mysqlbinlog generated on Feb 2 for PITR restore but it is failing with the error mentioned in original description.

Kindly suggest how to resolve the issue ? Do I need to manually generate a new binary log file before taking full backup .

PROBLEM

You are starting from the wrong log. You should start with mysql-bin.000034 not mysql-bin.000035

The timestamp on each binlog shows the last time a binlog event was written to it

mysql-bin.000033 was last written Dec 27 18:26
mysql-bin.000034 was last written Feb  2 18:58. That's 18 hrs 58 min after Feb  2 00:00
mysql-bin.000035 had to be created Feb  2 18:58

Given the timestamps mentioned, mysql-bin.000034 is the log you need to start with.

SOLUTION

You should collect the binlog events from Feb  2 00:00

Please run the following

MYCHANGES=ChangesSinceFeb2Backup.sql
BINLOGS="mysql-bin.000034 mysql-bin.000035"
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" ${BINLOGS} > ${MYCHANGES}

Restore the Feb 2 mysqldump Backup, then play all the events

mysql -u root -p db < ${MYCHANGES}

This will bring back everything up to Feb 18 15:50

If you want to do the PITR up to today at 12:45, you do this

MYCHANGES=ChangesSinceFeb2Backup.sql
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" --stop_datetime="2014-02-18 12:45:00" ${BINLOGS} > ${MYCHANGES}

SUGGESTION

Note that mysql-bin.000035 contains several days worth of binlog events.

Before you run the midnight backup, you should run

FLUSH BINARY LOGS;

That way, you can clearly identify what binary logs to use in PITR

Give it a Try !!!


Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement:

RESTORE DATABASE [Db]
FROM  DISK = N'C:\temp\sql\DB.bak' WITH  FILE = 1,
MOVE N'DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf',
MOVE N'DB_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf',
REPLACE,  STATS = 10
GO

But It keeps giving this error.

  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'.
  Msg 3156, Level 16, State 5, Line 1
  File 'Db' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.mdf'. Use WITH MOVE to identify a valid location for the file.
  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '5(failed to retrieve text for this error. Reason: 15105)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'.
  Msg 3156, Level 16, State 5, Line 1
  File 'Db_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\NewDB.ldf'. Use WITH MOVE to identify a valid location for the file.
  Msg 3119, Level 16, State 1, Line 1
  Problems were identified while planning for the RESTORE statement. Previous messages provide details.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I am logged into SQL Server as SA. I have Admin permissions on the system.

I have also tried to remove the restore location to another path (that has full permission) with the same error.

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in.  The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance:

You can also verify this path through the restore prompt in SSMS, clicking on the "files":

However also noting that the error message  operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

  The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'


OVERLAPS for Informix Does Informix have a function equivalent to OVERLAPS, which finds out whether or not two time intervals have any time in common?

I don't think there is a OVERLAPS() function that you could use. You'll have to stick to the usual test:

WHERE a.StartTime < b.EndTime
  AND b.StartTime < a.EndTime

Change < to <=, depending on how you want your Overlap test to function on end cases, when an end time (or date) is exactly the same as the other start time. I guess such behaviour may be wanted for dates.


selecting the month day and year from a transaction table i want to create two queries for my table which has fields name,surname and amount paid,the first query should select the day,month and the amount paid,the second query should select a month,year in that year and the total amount paid in that month,lets say john paid $200 on 2013-05-01,$400 on 2013-05-03,while peter paid $50 on 2013-04-08, i want the first query to output

month and day   amount
  05-01          200
  05-03          400
  04-08          50

and the second query should output

month and year   total
2013-05           600
2013-04            50

i know i can use the sum aggregate function to select the total but the tricky part is how to select the day and the month in the format above ,thanks in advance

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |