restore db from .bak file that contains multi backup I backup my database and I Mistakenly saved it without a name.

what happened is that instead of replace the last file it add to it the last backup.

now I have a .bak file that has 74 backups

I want to restore the db in position 74 with query ,since restore from the wizrad always return "db in use"

I tried

alter database <dbname>
set single_user with rollback immediate

with no luck,so my only option is throw query window

any idea how to restore the one db from the file in position 74?

Thanks for any help

Baaroz

try the following:

USE [MASTER]
GO
ALTER DATABASE [spetztest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [spetztest] FROM  DISK = N'c:\sdk.bak' WITH FILE = 74,  REPLACE,  STATS = 1
GO

Make sure that your session that you are using to do the restore is not using the database you want to restore.


Getting rid of partitioning in Postgresql 9 it turns out that our approach with list partitioning was not the best idea. We have  way to much small partitions and a lot of time is wasted on partition choosing by query optimizer.

What's the easiest way to merge all partitions back into one table? I don't expect any data clash problems.

<!-- language: lang-sql -->
PARENT_TABLE(id,col1,col2,col3,client_id)

CREATE TABLE CHILD_TABLE_1 (CHECK (client_id = '1')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_2 (CHECK (client_id = '2')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_3 (CHECK (client_id = '3')) INHERITS (PARENT_TABLE);

The easiest way is to first remove any triggers which direct new rows to the child tables, and then do this for each child table:

with t as (delete from child_table_1 returning *)
    insert into parent_table select * from t;

The locking and performance implications may dictate against this method, but it is hard to beat it for simplicity.

You might want to wrap it in a transaction block that starts with locking the child table and ends with dropping it, to make sure no one can keep using it for new tuples while you are cleaning out the old ones.


comparing column values when there are nothing in common between tables I have a need to display the value stored in columns of separate tables where the tables and columns have nothing in common. I just want to compare the content of the rows of each column side-by-side. And just the 1st 10 rows would be sufficient for my purposes. There are no primary keys or common link between the tables.

I have many tables with hundreds of columns to compare. Is there a simple way to do this?

i.e.
Table1, column3 | Table2, column6 | Table3, column1

Resulting in something like the following

tbl1col3 | tbl2col6 | tbl3col1
123.9    | abc      | x12
234.0    | bcd      | o23

Using postgresql 9.6, pgadmin4 v3.5

like this perhaps.

WITH t1 AS ( SELECT col3 AS tbl1col3,row_number()OVER() FROM tbl1 limit 10)
    ,t2 as ( select col6 AS tbl2col6,row_number()OVER() FROM tbl2 limit 10)
    ,t3 as ( select col1 AS tbl3col1,row_number()OVER() FROM tbl3 limit 10)
SELECT tbl1col3, tbl2col6, tbl3col1
FROM t1
    FULL OUTER JOIN t2 USING (row_number)
    FULL OUTER JOIN t3 USING (row_number)
;


SQL Server can't see subst/virtual drive I have been having trouble getting SQL Server to see subst/virtual drives at all.

For example:
I've used the subst command to create a F:/ drive that maps to C:/FDrive. The stored procedures use this F:/ drive to generate various files. If I programmatically change the folder location within the stored procs from F:/ to C:/FDrive they execute successfully.

Is there a fix or workaround available?

It's because subst just gives you a drive letter alias for your current session; it's not a server level thing.  This is not going to be possible using this command.  Use the real path instead.


MS SQL Server 2005 DB backup with partitioned tables to non-partitioned SQL Server 2012 I have a database with several partitioned tables on MS SQL Server 2005 Enterprise, and I have to restore this DB on MS SQL Server 2012 Standard. Since Standard doesn't support partitioning - it's a bit complicated to restore all the data to the new server. I have to find a simplest and quickest solution.
My first idea is to remove partitions on 2005 DB, then make a backup and restore it on SQL Server 2012. But my database is large and to merge it's partitioned data takes couple of hours for just one table.
The quickest way, from my point of view, would be the data transfer from old to new DB, but it will probably also take too much time.
Please share your ideas - may be I'm not aware of some features in 2012 that might be helpful.

Community Wiki answer compiled from comments to the question†

Jon Seigel:

  It isn't possible to restore a database that uses Enterprise edition
  features on an instance that doesn't support those features. You'll
  have to either remove partitioning and then use backup/restore, or
  export/import the data.

Kin

  See this answer on another question that shows how to use bcp
  out and BULK INSERT in native mode to transfer the data quickly.

†Comments may be deleted and are not searchable.


Kibana-like tool for PosgreSQL I'm looking for a tool that could browse and visualize data from db like Kibana does.

I'd prefer a native Posgres tool to any that transfers data from a db somewhere else. I know that rivers for Elasticsearch did the latter and it's now deprecated.

Thank you in advance.

Free...

I'm not 100% sure that there's a good, free, open source alternative, if that's what you're looking for. The closest you are likely to find is Orange, a data mining and visualization tool.  For a little bit of instruction, just do a little Googling, or consult helpful Youtube videos such as the one linked.

or Paid?

If you're willing to pay for it, I would highly recommend using Tableau.  They have a free version, called Tableau Public, which only works with flat files, but will allow you to do some basic testing. The licensed version allows native connections to many databases, including Postgres. Lastly, if you are a larger enterprise client who has a business model which permits cloud storage, Chartio may also be a good alternative.

Full disclosure:  I have no business connections to any of the companies mentioned, and my post is meant to be purely informative and non-promotional for the companies listed.


Relational tables should contain data and not logic - correct? Ok, I couldn't find a better title, to be honest.

I'm recently landed in a new working place, and I've been asked to help with the DB. I wanted to try to explain a concept, but I'm not sure how to word it at best, and maybe this concept has already a better explanation and a name, too. I don't label myself as a DB expert, so I'd like to present them something more official than my own point of view, no matter how many good DBs I've designed in my life.

One of the things I've found mostly amusing (but not in a good way) is that they seems to follow design the db according to the logic of the problem and not according to "this is a database, it should contain data", so for example to know if an element is in a certain category, the query goes this way (in pseudocode):

SELECT Column1, Column2...ColumnN
FROM Invoices [LIST OF JOIN]
WHERE
Invoces.Field1 IN
    (TableA WHERE TableA.Field NOT IN
        (TableB WHERE TableB.Field IN ((TableC WHERE) OR (TableD WHERE)))
OR Invoces.Field1 NOT IN
    (TableB WHERE TableB.Field IN ((TableC WHERE) AND (TableD WHERE))
OR TableB.Field IN
    (TableC WHERE TableC.Field IN (TableC WHERE))
OR TableB.Field NOT IN
    (TableC WHERE TableC.Field NOT IN (TableD WHERE))

and so on and on for other twenty conditions or so. No, I'm not joking.

This happens because instead of saying "this is an invoice and its category is an attribute of the invoice itself", they go all the way with things like "the invoice is been inserted by that user, in that day, and he could have inserted it into a certain department or none at all, and in case of the department then that department could be part of a certain structure..." and so on. It's not even a matter of referential integrity, there are no FKs at all in this case...

Another practical example is the date of last edit of a document: instead of being stored as an attribute, it's calculated by a function which goes something along this way:

  if that document is related to another, then if the other is this
  kind, then if the first document is related to this other document
  too, then the date of last edit is the date when the administrator has
  printed the daily appointment calendar"...

clearly with a bunch of else for every if, too. AND, it's not because there is some sort of dependencies, it's just because many things are stored to reflect their logic.

In a way I'd be tempted to say "write once, read many", with the meaning of "write that attribute once and then read it for free as many times as you need", because we have a low frequency of writes and a very high frequency of reads, and clearly all reads must every time recalculate everything. But I've got a feeling there must be some principle that says that you should design databases to hold data...

It Depends

The purpose of a database is to store valid data in a secure fashion for multiple users across multiple applications.

Some of the logic will focus on the valid data portion of that statement.  These will be your constraints and data types.

Other parts of code within the database will focus on the multiple applications portion.  For example, a VIEW will hide some complex logic to ensure that all applications see the data in an identical manner.  One application could be a web based application, the other application could be a 3rd party report generator.

OLTP

Within an OLTP system, the complex SELECT statement and the last modified date logic doesn't make much sense.

If this is suppose to be an OLTP system, you should try to change things so that they follow normal relational database practices.

Data Warehouse

The complexity of your pseudo-code SELECT statement and your date of last edit logic makes perfect sense in a Data Warehouse [DW] environment.  However, they are probably part of an ETL/ELT process for materializing the data into a denormalized table for faster report generation.

The TABLES and VIEWS and MATERIALIZED VIEWS are built to solve a very specific set of business problems.  From my experience, those objects are used as the source for various Business Reports.  In some cases, one table/view per report is created to solve a Business Requirement.

No FKs?  That's probably normal for a DW.  The data in the tables could be refreshed with TRUNCATE followed by INSERT...SELECT.  In this situation, a Foreign Key could do more harm than good.

As I read the comments, it seams your company needs to move to a more formal OLTP hierarchical design.  I wish you luck.


Disc IO for query in MongoDB is there a way to tell how much data MongoDB is reading from disc and how much from cache for a query ?
I looked at the explain()-Function but did not find this kind of informations.

Thanks, Andre

It is not possible per request, but you might want to give the free MMS a try, which has quite some metrics. Together with the hardware monitoring provided by munin-node, you get a pretty good picture on what's going on on your MongoDB servers.


Comparing Two Values when one or more could be NULL I am writing a trigger and want to see if one column has changed could be NULL and trying to avoid the following:

IF     NEW.value != OLD.value
    OR (NEW.value IS NULL AND OLD.value IS NOT NULL)
    OR (NEW.value IS NOT NULL AND OLD.value IS NULL) THEN
...
END IF;

This is postgres 9.5 in a function (stored procedure)

Equivalent and simpler:

IF  NEW.value IS DISTINCT FROM OLD.value  THEN
...
END IF;


How to verify the index on a table was created "Concurrently" Normally after I create an index, I check pgAdmin to verify that the index was created correctly. However, I'm not sure how to verify if the "concurrently" argument I passed in went through since I don't see it listed in the SQL Pane as I do with the other properties I've specified. Please help. Currently using version 1.22.1 connected to a PostgreSQL 9.5 server.

You would know that the index was created concurrently by observing the level of the lock on held on the table while the index was being built.

Once the index creation is done with, it is just an ordinary index.  Building an index concurrently is not an ongoing property of the index.


Problem when connecting to SQL Server I'm trying to connect to my SQL Server Express and I get this error. This happens both locally and on client.

  Connection Timeout Expired. The timeout period elapsed during the
  post-login phase. The connection could have timed out while waiting
  for server to complete the login process and respond; Or it could have
  timed out while attempting to create multiple active connections. The
  duration spent while attempting to connect to this server was
  [Pre-Login] initialization=216; handshake=7899;
  [Login] initialization=0; authentication=0;
  [Post-Login] complete=5999;  (.Net SqlClient Data Provider)

Can someone help me?

This answer is intentionally Community Wiki to encourage others to edit in other useful links and information for troubleshooting connection problems.

Please try:

Troubleshooting: Timeout Expired
How to Troubleshoot Connecting to the SQL Server Database Engine

Both are Microsoft TechNet guides targeted at your type of problem.

Aaron Bertrand wrote a blog post listing many different error codes for login related connection errors

The Stack Overflow Q & A Help troubleshooting SqlException: Timeout expired on connection, in a non-load situation provides lot of good pointers to check on a box facing this type of issue.


How to read postgres.log output? I need to improve my database performance, in order to achieve that, I enabled the log in my postgresql.conf:

log_statement = 'all'
log_connections = on
log_disconnections = on

Why do I have 3 duration output in this SELECT ?

2016-02-29 15:35:07 CET [3492-349] postgres@uat LOG:  execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = (SELECT ID FROM public.status WHERE status = 'New') ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1
2016-02-29 15:35:07 CET [3492-350] postgres@uat LOG:  duration: 31.096 ms
2016-02-29 15:35:08 CET [3494-223] postgres@uat LOG:  duration: 0.090 ms
2016-02-29 15:35:08 CET [3494-224] postgres@uat LOG:  duration: 0.061 ms

EDIT

More information:

log_min_duration_statement = 2
log_line_prefix = '%t [%p-%l] %q%u@%d '

Another example:

2016-02-29 16:56:14 CET [2145-1225] postgres@uat LOG:  execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = 1 ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1
2016-02-29 16:56:14 CET [2139-9001] postgres@uat LOG:  duration: 48.190 ms
2016-02-29 16:56:14 CET [2139-9002] postgres@uat LOG:  duration: 0.022 ms
2016-02-29 16:56:14 CET [2139-9003] postgres@uat LOG:  execute S_3: COMMIT
2016-02-29 16:56:14 CET [2139-9004] postgres@uat LOG:  duration: 0.207 ms
2016-02-29 16:56:14 CET [2145-1226] postgres@uat LOG:  duration: 51.172 ms
2016-02-29 16:56:15 CET [2143-1691] postgres@uat LOG:  duration: 0.079 ms
2016-02-29 16:56:15 CET [2143-1692] postgres@uat LOG:  duration: 0.055 ms

With csvlog as output:

2016-02-29 17:06:28.475 CET,"postgres","uat",2317,"eniac:50690",56d46bee.90d,1765,"SELECT",2016-02-29 17:03:58 CET,15/442,0,LOG,00000,"execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = 1 ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1",,,,,,,,,""
2016-02-29 17:06:28.507 CET,"postgres","uat",2317,"eniac:50690",56d46bee.90d,1766,"SELECT",2016-02-29 17:03:58 CET,15/442,0,LOG,00000,"duration: 31.351 ms",,,,,,,,,""
2016-02-29 17:06:28.694 CET,"postgres","uat",2308,"eniac:50677",56d46bc2.904,3043,"PARSE",2016-02-29 17:03:14 CET,8/762,0,LOG,00000,"duration: 0.079 ms",,,,,,,,,""
2016-02-29 17:06:28.694 CET,"postgres","uat",2308,"eniac:50677",56d46bc2.904,3044,"BIND",2016-02-29 17:03:14 CET,8/762,0,LOG,00000,"duration: 0.053 ms",,,,,,,,,""

For clients using the extended query protocol, durations of PARSE, BIND, and EXECUTE steps are logged independently. See also the description of log_min_duration_statement.

Note: when not using csvlog as log_destination, one can still collect all the necessary information by configuring the log_line_prefix setting in postgresql.conf (and reload PostgreSQL when done).

log_line_prefix = '%t %h %p %u '        # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d>'


Copy Database wizard fails but leaves no text file log I've been using the copy database wizard to create a development copy of my database.

Unfortunately, the copy database wizard has repeatedly failed.  I suspect the problem is that the sql server agent service account does not have the necessary permissions.  I can't verify this because the log file isn't actually being saved.  I've tried having it save to the default directory where I know the account running the copy database operations must have permissions and to another location on the server.

Any thoughts about how I can resolve this problem?  The new database is showing up but without any tables...

Please use backup / restore. The copy database wizard is a disappointing sack of, well, let's leave it at that.


What is the proper event in Extended Events to track stored procedure executions? My goal is to track executions of a single stored procedure using Extended Events

In the event selection list, I can find following:

sql_statement_completed
sql_batch_completed
sp_statement_completed
rpc_completed

Which one should I chose ?
I only need a distinct execution count

Include the module_start event filtered on the stored procedure name and a histogram target bucket based on the object_name field. This will capture and aggregate the execution counts every time the stored procedure is executed, whether it is called directly or indirectly (i.e. called other stored procedures and triggers).

CREATE EVENT SESSION [module_start_execution_summary] ON SERVER
ADD EVENT sqlserver.module_start(
    WHERE ([object_name]=N'YourStoredProcedure'))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.module_start',source=N'object_name',source_type=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

Note that if the same stored procedure name exists in more than one database or schema, the different procs will be counted as one with this method. An additional filter on the database can be used to limit counting to only the desired database.


How high can I set undo retention on Oracle 11gR2 Is it viable to set a very high undo retention, as to allow flashback queries several weeks back in time?

Naturally, enough space must be available in the undo tablespaces to contain the amount of undo data required. Are there other limitations I should be aware of?

What happens if there is not enough undo space available? Will production be affected in any way, or is it just a matter of flashback and rollback being limited (snapshot too old etc).

Update:

With a typical undo generation of a little less than 1 GB per day per instance, and up to 64 GB worth of undo space per instance it sounds viable to run with a 30-day undo retention target. No?

Very high is a relative term that depends on perspective.  You could mean 1 hour, 1 month, 1 year, etc.  Assuming you mean something between one minute and one month, the answer will depend on how much undo is being produced and how much storage you have available.  I haven't seen anything higher than two weeks.

Oracle automatically manages the undo space and will dynamically decrease the retention below the undo retention setting if space constraints require it.  The caveat to that is when the undo tablespace has guaranteed retention.

The Oracle Database Administrator's Guide covers all this information on one page.  Here are some excerpts:

  Oracle Database automatically tunes the undo retention period based on
  undo tablespace size and system activity. You can optionally specify a
  minimum undo retention period (in seconds) by setting the
  UNDO_RETENTION initialization parameter. The exact impact this
  parameter on undo retention is as follows:

  The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for
  the best possible retention, based on system activity and undo
  tablespace size. See "Automatic Tuning of Undo Retention" for more
  information.
  For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by
  UNDO_RETENTION. When space is low, instead of overwriting unexpired
  undo information, the tablespace auto-extends. If the MAXSIZE clause
  is specified for an auto-extending undo tablespace, when the maximum
  size is reached, the database may begin to overwrite unexpired undo
  information. The UNDOTBS1 tablespace that is automatically created by
  DBCA is auto-extending.

  ...

  To guarantee the success of long-running queries or Oracle Flashback
  operations, you can enable retention guarantee. If retention guarantee
  is enabled, the specified minimum undo retention is guaranteed; the
  database never overwrites unexpired undo data even if it means that
  transactions fail due to lack of space in the undo tablespace. If
  retention guarantee is not enabled, the database can overwrite
  unexpired undo when space is low, thus lowering the undo retention for
  the system. This option is disabled by default.


MySql Vs. Postgresql for a blog application I am designing a blog application. Which of these fits better?
In which use cases will mysql be good and for what use cases will postgresql be good?

MySQL's home territory is in CMS.  It will be good enough.  I like PostgreSQL as much as the next guy but a blog will work on either, and MyISAM tables might actually be really nice there.

As for where PostgreSQL does better than MySQL, here is my take. There are two big areas:

Advanced data modelling and
Multi-application databases

The first means that PostgreSQL is far more mature at doing complex data modelling than MySQL is.  It has a very mature extensible type system, a wide range of procedural languages, and a great deal of flexibility in how these languages can be plugged into existing queries.

If that wasn't enough, the fact is that you can essentially build your data model in PostgreSQL based not only on what information you are storing but what information is commonly derived from what you are storing.  This makes things like not-first-normal-form actually sane to use where they are needed.  Add collections and multiple inheritance in table structure and you have a very sophisticated data modelling platform.  For examples, see my recent blog entries.

On to the other side.  MySQL developers tend to see the application as the bottom tier of the application stack, while PostgreSQL developers tend to see the application as the center of the data environment.  Besides the content management system market, MySQL's other major market is in applications where data is not expected to be exposed to more than one writing application at a time.  This leads to a significant difference in handling data validation, etc.

In PostgreSQL validation is always equally strict.  If the app expects special error treatment it had better call functions or casts to handle this explicitly.  For example, if I want to store up to a maximum of 144 characters in a text field, I can:  insert into mytable values (?::varchar(144)); and absent such, PostgreSQL will throw an error if it isn't crystal clear what the application wants, and whether it will fit into the data schema as defined.

MySQL however places the application in charge of defining the data validation rules.  The sql_mode setting (which includes such things as whether '0000-00-00' is a valid date or whether '2008-02-30' can be stored in a date field) is something any application can set without restriction, and the DBA has no way of restricting this.  So while PostgreSQL allows the relational and object-relational interface to serve as a public API, it is essentially intended largely to be a private API for applications in MySQL.  This is a huge difference and not readily understood by many people trying to make the choice.  This leads to major differences in application design.

MySQL is a data storage and reporting solution for your application.  PostgreSQL is a data centralization, modelling, and reporting solution for your organization.  The two are remarkably different.


Getting timeouts and backup errors after mirroring and log shipping is setup OS is Windows Server 2003 32 bit. SQL Server is SQL Server 2008 SP3 32 bit

Today I finished configuring database mirroring and log shipping for 3 databases on this SQL instance and started receiving timeouts (connection and query timeouts from our applications) and log backup errors (log backup was performed by log shipping executable). Never had this kind of issues before when database mirroring was not active and log backups were performed via our custom SSIS package instead of using log shipping (also previously log backup was setup only for 1 database every 15 minutes and now it is for 3 databases every 5 minutes).

More details:

1) Connection timeout from one of our applications:

Event Text:   System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at App.Component1.EventBroadcaster.ConnectToSql(SqlConnection connect, Boolean bulk)
   at App.Component1.Broadcaster.ReBroadCastLocate(Int32 LocateId, BroadCastType bcType_)

2) Query timeout from another of our applications:

Event Text:   System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at App1.RTFS.DBProcess.exeNonQuery(SqlCommand cmd_)
   at App1.RTFS.RTDB.submitOrder(NewOrderParams& params_)
   at App1.RTFS.NewOrderMessageProcess.processSingle()
   at App1.RTFS.NewOrderMessageProcess.processSubmit()
   at App1.RTFS.NewOrderMessageProcess.process()
   at App1.RTFS.Application.processMessageRequest(IMessageProcess msgProcess, SessionID sessionID)

3) Log backup failure (all these are created at the same time in Event log)

  Event 3041
  BACKUP failed to complete the command BACKUP LOG DB0. Check the backup application log for detailed messages.

  Event 701
  There is insufficient system memory in resource pool 'default' to run this query.

  Event 701
  There is insufficient system memory in resource pool 'internal' to run this query.

These errors are not that frequent 5-6 times a day

I think error messages related to failed log backup ("There is insufficient system memory in resource pool") indicate possible bottleneck. From what I found possible solution is to allocate more memory for SQL Server by using AWE. However, this server only has 4GB of physical RAM and SQL Server is already using 1.8GB (as shown by task manager).

How can I better troubleshoot these issues? Is there a way to see detailed memory situation for SQL Server and trace exactly what queries/connections are timing out?

If your server has only  4GB of RAM, AWE isn't going to help you much.  AWE is designed for when you have greater than 4GB of RAM you need to use.

Timeouts are harder to define.  The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts.  However, what you're running into are queries that take to long to complete.  To troubleshoot this, I would first start by reviewing the wait stats to identify specifically what is causing things to drag in your SQL Server.  Some waits to look for:

BACKUPTHREAD means your queries are waiting on backup operations to complete.  Other backup waits could indicate problems.
Memory pressure could be any number of waits, but look for things like RESOURCE_SEMAPHORE or IO waits.  Also, check the trend on your Page Life Expectancy in Perfmon (SQL Server: Buffer Manager -> Page Life Expectancy).  If this continually trends low, your pages are cycling out of memory to quickly.

Another thing you can do is check how long your log shipping backups are taking to complete:

SELECT
    database_name
    ,backup_finish_date
    ,datediff(ss,backup_start_date,backup_finish_date) SecondsToBackup
FROM
    msdb.dbo.backupset
WHERE
    type='l'
ORDER BY
    backup_finish_date desc;

As for resolution, if your log shipping is happening all at the same time, you might want to try and stagger that out.  If it isn't necessary to log ship every 5 minutes, I'd try and go with your 15 minutes and stagger each database off by 5 minutes so that they're running separately.

The other thing is to consider a hardware upgrade.  The box your describing sounds fairly out of date, considering there are laptops you can purchase now that have more horse power than what you're describing.  If you can move the server to a virtual machine, you'll be able to immediately upgrade to better resources, which could resolve these issues for you.


What is a good way to determine the performance I should expect from a PostgreSQL server? Trying to figure out how I should expect my database to perform.  Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server scaling better.

This question aims more towards ways that I can calculate or estimate (really, any idea of this would be good) expected performance.  This should ideally help me come up with a formula I can run based on several factors (like record size, number of rows, etc), instead of a subjective assumption based on a particular server / DB.

So, what is a good way to determine expected performance on a PostgreSQL server?

Thanks very much!

I think determining the expected performance is a lot harder than planning for capacity.  My guess is that is what you are really trying to do so I will go over what I consider to be the most important rules for OLTP workloads here.

You want the working data set to fit in memory if possible.  Calculating performance where this is not the case depends on how often the disks are hit and what sort of disk I/O.  If you can't fit all of it in memory, at least you should try to fit the most commonly used data, and all commonly used indexes in memory.
You want to have available at least one core per two concurrent queries.  Better is one core per concurrent query.

Both of these are a little tricky to calculate and they depend on row size, number of pages in the table, number of dead tuples in the table, which in turn depends partly on the number of concurrent updates.

Beyond this, performance will slowly degrade, hopefully gracefully.  Estimating performance when these are or aren't met is very difficult because it is very fact specific.


what happens when InnoDB logspace is too small I've just seen several mysql errors in my log:

InnoDB: ERROR: the age of the last checkpoint is X
InnoDB: which exceeds the log group capacity Y

I understand, that I do have to increase the innodb_log_file/buffer_size setting in order to fix the issue.
What I don't understand is, what happens to my data when this error occurs?
Does MySql skip the log buffer file and write the corresponding data to the bin log directly?

The InnoDB Logfile is not related to your Binlog.
The InnoDB log is used in case of crash recovery. So you may loose some transactions in case of a crash. In addition to that a small InnoDB log can impact your overal MySQL performance because MySQL has to flush the data very often to disk if you have a small iblog.

In short: there is no direct impact impact when this error happens, but if you encounter a mysql crash you may loose some transactions.
In addition to that you can optimized your IO performance if you increase the size because the logfiles are sequential IO which is much faster than the random IO if you have to actually write your data to the tablespace.


Mongodb - sharding + replicating an existing collection I have an existing mongo (3.4) database that is growing quickly, so I need to shard it. I am only getting started with sharding/replication and I'm going through the documentation at the moment.

Is this a logical sequence of events for sharding and replicating an existing collection:

create 3x config server replica set
connect the existing mongo server with the config set
enable sharding on the existing db
create another mongo server and connect with the config set
shard existing collection
create replica servers and connect with the config set

Thanks

Yes, you can do it just like that. Shards in the cluster don't need to be replica sets, but it is of course recommended. And later, it is possible to convert those nodes to single node replica sets and after that add more nodes to those replica sets. However, after making node as replica set, you need to do little "under the hood" updates, so that config server (and mongos) will know that shard(s) are now replica sets and not single nodes anymore.


Is there any way to graceful restart mysqld? I think my question is impossible but I sure I do not know everything.

In my opinion, when mysqld restart, some transaction is not still completed will be rollback on startup. Mysqld use undo log to do it. But is there any way to keep long transactions on restarting mysqld ?

I am using MariaDB Galera with three nodes.

Exact duplicate?

Is there a graceful or safe restart for mysql like for apache httpd?

Basically, it involves some SQL scripting along with whatever operating system scripts or controls you are using to do the server restart.
Just be mindful of how the locking interacts with transactions: http://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html


Finding mode in SQL Server I have the below query which returns me the mode which is the most repeated value in a data set
select top 1 TargetPriceRec as mode
from PricingRecurring
Group By TargetPriceRec order by Count(*) desc

I get the most repeated value from this but the issue is that if there is no repeated value I am still getting the highest value in the dataset. How can I get an empty value if there are no repeating values in the dataset and also if there are multiple repeating values I still get an empty value since there is no single mode.
Any help is much appreaciated.
Thanks

You want to use the HAVING keyword
SELECT TOP 1 TargetPriceRec as mode
FROM PricingRecurring
GROUP BY TargetPriceRec
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


How long does query optimisation take relative to the execution time? Could anyone please point me to a benchmark comparing the time spent by the query optimiser relative to the overall query execution time (any relational DBMS will do, but PostgreSQL would be preferable)?
I'm curious about roughly how significant is the time spent on optimizing the query relative to the overall execution time.
In particular, whilst reading Bao: Learning to Steer Query Optimizers by Marcus et al. 2020 I was surprised by the fact that the DBMS' query optimizer was run 48 times for each query (required even at inference), whilst the model outperformed the default PostgreSQL query performance regardless.

There is no absolute number for the percentage of database time spent planning queries, it depends on your workload.

If the workload consists of analytical queries, planning time is usually insignificant in comparison to query execution time.

If the workload consists of simple OLTP queries, planning time can exceed query execution time.

I didn't read the article you quote, but the PostgreSQL optimizer is invoked once per SQL statement executed. Of course it considers several plans to find the best one.
For a simple benchmark, you can use the built-in PostgreSQL benchmark tool pgbench. While it is not a full-fledged workload simulator, it is surprisingly volatile. I'll use the default workload, which is a simple OLTP workload.
First, create a sample database with a scale factor appropriate for what you want to test (in this case, 10 million rows in the largest table):
pgbench -i -s 100 mydb

Then run the benchmark with the “simple query protocol” (one-shot queries that are planned each time they are executed):
pgbench -c 4 -T 60 mydb
[...]
tps = 3922.296560 (excluding connections establishing)

Repeat the benchmark using the extended query protocol with prepared statements, so that query plans are reused (this roughly corresponds to query execution time without planning):
pgbench -c 4 -M prepared -T 60 test
[...]
tps = 5022.185127 (excluding connections establishing)

So in this simple scenario, we got more than 25% more transactions done by excluding planning time. For more reliable values, run the test for more than 60 seconds.
Of course this number doesn't accurately measure query planning time versus query execution time, because there is still the latency of the round trip between client and server to consider. You could get better numbers by writing your own PL/pgSQL code that runs static or dynamic SQL statements in a tight loop. Since the plans of static SQL statements in PL/pgSQL statements are cached, their runtime will exclude query planning time.
You can also write your own benchmarks with pgbench; it allows you to write custom scripts and even offers a simple scripting language.


Escaping delimiter in postgresql I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped:

Data structure:

create table v (a varchar);

Data file:

bus
'Gat\,\\e\'way_MQB'

Import command (run under Linux from bash)

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\\' CSV header"

Error:

ERROR:  extra data after last expected column

I've been fighting and google against this problem for quite a few hours and do not understand what I am doing wrong?

Try:

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\' quote '''' CSV header"

You don't need to escape the escape character when specifying it. QUOTE defaults to double-quote so you need to pass that.


Not unique table/alias: 'tbl_users' I have difficulties with my codes in inner join. I need to join 4 of my tables but it has a error:

  "Not unique table/alias: 'tbl_users'"

and when I added alias my table didn't recognized and its attribute.

$sqltran = mysqli_query($con, "
    SELECT  `tbl_users`.`userid` , `tbl_applicant`.`applicant_id`,`tbl_payment`.`userid`

    FROM `tbl_users` , `tbl_applicant`

    INNER JOIN `tbl_users` ON `tbl_users`.`userid` = `tbl_applicant`.`applicant_id`

    INNER JOIN `tbl_applicant`  ON `tbl_applicant.applicant_id` = `tbl_payment`.`userid`

    WHERE `u_id.userid` = '".$_SESSION['userid']."'"
)or die(mysqli_error($con));

$arrVal = array();

thank you!!..
i'm hoping for your answers.

You seem to be referring to the tables too many times. The lines

 FROM `tbl_users` , `tbl_applicant`
    INNER JOIN `tbl_users`

causes the table tbl_users to be joined with itself, which is probably not what you intend.

Instead, start with tbl_users and then 'join' the other tables one at a time, each time stating which fields you are joining on, like this...

SELECT  tbl_users.userid , tbl_applicant.applicant_id, tbl_payment.userid
FROM tbl_users
INNER JOIN  tbl_applicant
       ON tbl_users.userid = tbl_applicant.applicant_id
INNER JOIN tbl_payment
      ON tbl_applicant.applicant_id = tbl_payment.userid
WHERE tbl_users.userid = '".$_SESSION['userid']."'"

Other points are these:

You have

WHERE `u_id.userid` =

but you do not include the table u_id in any of your joins. Did you mean tbl_users.userid ?

If not and u_id is really another table, then you need another JOIN to join it to one of the other three tables on the appropriate column(s).
You do not really need to have all the back ticks in your table/column names. They are only really needed if you have a table with the same name as a keyword or if you have spaces in your column names. Leaving them out makes it easier to read I think.
Although it's good practice to include the table name in your SELECT line you do not need to do so unless the column name is repeated in the other tables, as with tbl_users.userid and tbl_payment.userid. Then using the table name removes ambiguity.
By default, just saying JOIN will do an INNER JOIN and return only those rows whose key appears in both the joined tables.

Incidentally, to answer why you get the Non unique table/alias message, it's because you select tbl_users.userid from tbl_users which you later join to tbl_users again, so it it ambiguous as to which of the two versions of  tbl_users you are referring to the SELECT line.

Sometimes it is useful to join a table to itself, as you seem to be inadvertently doing (it's called a self join). However in that case you should include a new table name as an alias to one of the real table names to make them unambiguous. eg

FROM tbl_users
INNER JOIN tbl_users AS copy_tbl_users --  here I am using copy_tbl_users to refer to the tbl_users that I'm joining with.
ON tbl_users.userid = copy_tbl_users.some_id_in_the_table --  Now I use the alias so the table names are unambiguous.

The above code, using AS to make the alias, is for MySQL. Although even in MySQL you don't strictly need the AS keyword but I feel leaving it out for the sake of saving two characters makes the code harder to understand so I always use it.


IF UPDATE in trigger returns true when no rows were affected Why is the trigger executing the update(col1) block in the following:

USE tempdb
go
CREATE TABLE t1(id INT, col1 VARCHAR(10))
go
INSERT INTO dbo.t1( id, col1) VALUES(1,'aa'),(2,'sdf'),(3,'fg')
go
CREATE TRIGGER r_test ON t1 after UPDATE
AS
PRINT 'Trigger'
IF UPDATE(col1)
    PRINT 'col updated'
GO

UPDATE t1 SET col1='werwer' WHERE id=4

output

  col updated

I understand why the trigger executed but since id = 4 doesn't exist, shouldn't UPDATE(col1) be returning false?

Because IF UPDATE(col1) only checks if col1 was referenced in the UPDATE statement. It does not check how many rows were affected, nor does it check if the value has actually changed.

To not get the trigger to fire when zero rows are affected:

CREATE TRIGGER dbo.r_test
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF @@ROWCOUNT > 0
  BEGIN
    ...
  END
END

Note that MERGE may complicate this - @@ROWCOUNT can be non-zero even if no rows are updated, only inserted/deleted (more details here).

Instead you can:

CREATE TRIGGER dbo.r_test
  ON dbo.t1 after UPDATE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM inserted)
     AND EXISTS (SELECT 1 FROM deleted)
  BEGIN
    ...
  END
END

To determine if the value has changed:

IF EXISTS
(
  SELECT 1
    FROM inserted AS i
    INNER JOIN dbo.t1 ON i.id = t1.id
    WHERE i.col1 <> t1.col1
);

This is grossly simplified; NULLs will complicate this, as will columns with encrypted values.

Also, please always use the schema prefix.


Page Number in SQL Server database is not starting from zero I have a question on SQL Server page, I have created a new database with only one new table as below
Here is the Code:

CREATE TABLE PAGETEST
(
    ID int primary key
)

When i see my staring page number= FileID:PageNumber it is starting 1:285.
Q 1). Why my page not starting from 1:0 or 1:001?
Q 2).Will this page number have dependency from model database? Kindly find the below images

Let me preface this answer with the statement that there could be entire books written on this subject and this is without a doubt some of the most interesting and complicated parts of SQL Server.

This answer will not attempt to go into great lengths of detail, but a simplified overview of certain items.

  Q 1). Why my page not starting from 1:0 or 1:001?

In SQL Server there is the logical structure of your database and the physical. Since you're asking about the physical structure, there are two things at play here:

Internal data structures and tracking
Internal metadata storage

Your table doesn't start at page 0 of file 1, however that space in indeed occupied and used. This page is reserved and called the file header page, each file will have one of these.

Additionally there are GAM intervals which happen roughly every 4 GB, PFS that happen every roughly 64 MB, etc.

Finally there are the metadata tracking tables, such as sys.sysschobjs, which aren't readily available for consumption yet do need space in the database.

  Q 2).Will this page number have dependency from model database?

Simplistically speaking, yes. The more objects created and inherited from model, the more internal tracking and space that will be needed. This is grossly over simplified but should do, given the previous explanation as data can be extrapolated from it.

That's just the beginning ...

Allocation algorithms in SQL Server may be different between base objects. For example, when to allocate a new page may differ between heaps and clustered indexes. Where the data is allocated depends on how long the database has been in use, where it has been internally last filled to, and a host of many other different variables.

It truly is very interesting stuff and it's great to have fun with.


Calculate percent of roads (by length) where condition is >=15 I have a road condition table:
CONDITION       LENGTH
    2              5
    4             10
    6             15
    8             20
   10             25
   12             30
   14             35
   16             40
   18             45
   20             50

Roads that are at a condition of 15 or greater ( a.k.a. 15_OG) are considered to be in a good condition.
I want to calculate the percentage of roads (by length) that are at a good condition.
LENGTH_15_OG    TOTAL_LENGTH    PERCENT_15_OG
     135             275            49.09%

How can I do this calculation?

There will be other solutions - but how about ...

select
  length_15_og
, total_length
, round( length_15_og / total_length * 100, 2 ) as percent_15_og
from (
  select
    ( select sum( length ) from road_condition where condition >= 15 ) as length_15_og
  , ( select sum( length ) from road_condition ) as total_length
  from dual
) ;

-- result
LENGTH_15_OG  TOTAL_LENGTH  PERCENT_15_OG
135           275           49.09

Dbfiddle


sql server down bad things happens Following Situation:

You have a Database with very high transactions, the Database is about 4100 GBs
The Recovery Modell from your Database is full
Every week you make a full backup from your database
Every day you make a diff backup from your database
You make hour  a Backup from the log

Now bad things happens:
Your Server is down (lost power connection) , your emergency power supply is far away :/
What should the experience dba do, so that the database is going as fast as possible back online.

Remember: you do not have a recovery plan until you tested your recovery plan...

The theory is simple: on the disaster recovery machine you have prepared for such cases you restore the most recent full backup, then you apply the most recent diff backup (if any) and then all the hourly log backups taken after the most recent diff (or after the most recent full if no diff was yet taken since the last full). Since you were not able to backup the tail of the log, you will loose up to one hour of transactions. Since your whole recovery schedule was based on a plan to be able to restore within the maximum allowable downtime, your restored server will be up and running (including all jobs, migrated logins etc etc, and the application will be redirected to it...) within the allowed downtime, because you tested this before and is a known procedure, and the operator follows the well know checklist you have set in place for such an event (and had tested it!). And btw, if the maximum allowable downtime is shorter than the physical time required to do a full restore a 4.1Tb DB (which is measured in hours), then some warm or even hot standby (mirror, log shipping) is ready to take on the work...

What happens in the real world is that usually this is the moment when the power of hope (let's hope this never happens...) hits the hard wall of reality...


Insert top 253 values where value in select distinct tradernames I am trying to insert top 253 values for each trader from a temp traders table into temp results table.

I am using following code to do it.

CREATE TABLE #trad ( Trad varchar(50))
IF @trad is not null
INSERT INTO #Trad
SELECT DISTINCT * FROM dbo.Split(@trad,',') -- split will have traders seperated by commas
ELSE INSERT INTO #Traders
Select distinct Trader from TraderMap tm
where traderorgroup = 'trader'

CREATE TABLE #result
(port VARCHAR(50),portdate DATE,P MONEY,V_95 MONEY,
V_99 MONEY,VLimit MONEY,V_90 MONEY,H_99 MONEY,
H_975 MONEY,H_95 MONEY,H_90 MONEY,H_80 MONEY,dCnt
INT,Trad varchar(100))

DECLARE @traderlist AS Varchar(100)
DECLARE traderlist CURSOR FOR
SELECT DISTINCT Traders FROM #traders
OPEN traderlist
FETCH next FROM traderlist INTO @traderlist
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit,
dCnt,Trad)
SELECT  TOP 253 pl.Port,pl.Portdate,pl.dtd_P
PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,
Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC)
dCnt,pl.TradName
FROM Trad_PL pl INNER JOIN rep_tab var
ON pl.Portdate = var.V_Date
AND pl.Port = var.Port
AND pl.TradName = var.Trad
INNER JOIN Trad_Guid gui
ON pl.Port = gui.Port
AND pl.TradName = gui.Trad
WHERE pl.Portdate <= @portdate
AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE
@port END
AND pl.ONA = 'Act'
AND pl.TradName = @tradlist
AND var.PType = '0Trad'
group by
pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC
FETCH next FROM tradlist INTO @tradlist
END
CLOSE tradlist
DEALLOCATE tradlist

This code runs for hours. Is there any efficient way to get top 253 rows for each trad from Trad_PL and rep_tab.

In general if you have a cursor that's your first place to look for performance issues.  It probably works great if you have 10 traders, not so well if you have 10,000.

Try this.

INSERT INTO #result (port, portdate, P, V_95, V_99, VLimit, dCnt,Trad)
SELECT pl.Port,pl.Portdate,pl.dtd_P
    PL,var.V_95,var.V_99, sum(gu.Guid * -1) VLimit,
    Row_number() OVER(partition by pl.TradName ORDER BY Portdate DESC)
    dCnt,pl.TradName
FROM Trad_PL pl
INNER JOIN #Traders
    ON #Traders.Traders = pl.TradName
INNER JOIN rep_tab var
    ON pl.Portdate = var.V_Date
    AND pl.Port = var.Port
    AND pl.TradName = var.Trad
INNER JOIN Trad_Guid gui
    ON pl.Port = gui.Port
    AND pl.TradName = gui.Trad
WHERE pl.Portdate <= @portdate
    AND pl.Port = CASE WHEN @port = 'Her' THEN 'Herport' ELSE @port END
    AND pl.ONA = 'Act'
    AND var.PType = '0Trad'
    AND dCnt <= 253
group by
    pl.TradName,pl.Port,pl.Portdate,pl.dtd_P,var.V_95,var.V_99
ORDER BY Portdate DESC

I removed the where clause that hit @tradlist and just joined to your temp table (which was already created from a distinct list of traders so no worries there).  I also eliminated the TOP 253.  You are already creating a row number with your dCnt field so I just added an entry to the WHERE clause where dCnt <= 253.

If you are still having performance problems I'd make sure you have indexes related to each of your joins and take a look at the query plan.  Performance tuning in general is a big subject but you could do worse than buying a copy of Grant Fritchey's Execution Plan book.  There is even a free ebook copy.

https://www.red-gate.com/simple-talk/books/sql-server-execution-plans-third-edition-by-grant-fritchey/

Last but not least, completely away from your actual question, formatting is your friend when posting pieces of code.  You are far more likely to get positive responses (and answers) if your code is easier to read.  If you look, the piece of code I am posting is formatted to make it easier to read.  You certainly don't have to use this particular layout, formatting can be a very personal thing, but something other than all of the code down the left hand side of the screen is usually helpful.


Expanding a partitioned table I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the database files and file groups for these years.

The current scheme in place covers 200901 - 201512 and I now need to add 200001 - 200812. What is the best way to bring this online for the partitioned table?

Create a new partition scheme and partition function and change the clustered index to this new partition scheme; or
Can I somehow alter the existing partition scheme and partition function?

I'm asking because the current table has 18 billion rows and I want to do this the efficient way :)

Here are two best practices for partitioning that pertain to the question:

Keep an empty staging partition at the leftmost and rightmost ends
of the partition range to ensure that the partitions split when
loading in new data, and merge, after unloading old data, do not
cause data movement.
Do not split or merge a partition already populated with data
because this can cause severe locking and explosive log growth.

http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5
If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.
To check if the leftmost partition is empty, use a query like the following:
DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';

SELECT
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;

If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.
Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.
Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.


Can you use BigQuery to run on top of Bigtable I need to run BigQuery on top of Bigtable live, not as an export. I have found the information stating it was in beta but only as an export function. I would like to run BigQuery against Bigtable data without exporting. Has anyone done this or is it possible? The importance is that running it realtime no data is lost by doing a point in time export.

Yes you can. Link to Bigtable using it as an external table. It is not then created in BigQuery itself. Per the documentation:

  To query Cloud Bigtable data using a permanent external table, you:

  Create a table definition file (for the CLI or API)
  Create a table in BigQuery linked to the external data source
  Query the data using the permanent table

It is in Beta, but this is standard for Google and usually only lasts 6 months or so. In my experience the features and products and alot more stable in Beta than you find on other providers.


SSIS Packages with SQL Server Express 2008 R2 I understand from various questions [1] [2] that SQL Server Express 2008 R2 doesn't support SSIS packages and that there's only primitive support for ad-hoc data imports / exports.

I have already got SQL Server 2008 Client tools (not express version) installed for use with an instance of SQL Server Standard edition.

Will I be able to use BIDS and SSIS to create packages for use between the Standard edition of SQL Server and the Express instance of SQL Server?

I think you need to be clearer on your exact intended setup. That will allow for a better reading of those two linked questions. I would guess that those two links are actually irrelevant. SQL Server Express comes with a stripped down version of SSIS (or can be installed via the Tools or Advanced install), according to Link #2. But yet your question regards "creat[ing] packages for use between the Standard edition of SQL Server and the Express instance of SQL Server" which implies that you have Standard Edition. If that is the case, then you can create and run packages from the BIDS and SSIS that came with Standard Edition, and connect to Express Edition instances to transfer data back and forth. Documentation that says "SQL Server Express 2008 R2 doesn't support SSIS" is not saying that Express cannot be connected to (unless, of course, your testing indicates otherwise).

EDIT:
Testing indicated that this did indeed work; creating an SSIS package from BIDS that came with Standard Edition, and running it from Standard Edition, was able to connect to Express Edition. The only caveat is that, as noted by @Gareth in a comment below, "certain functionality won't work, for example History Cleanup, as it's not supported by Express Edition".


What are the problems with sharing an Access database through online storage service, like Dropbox? I have a desktop database I created in Access. I'd like to share the database with a single collaborator, who can have full privileges to the database (table access). However, my collaborator is much less tech-savvy so to make it easier for him, I have set up forms to make table updates, so table access is not a must for him. With that in mind, I am willing to split up the database if this would be a better option.

I had considered sharing the database with him over a Sharepoint website, but have little experience with this and would not know where to begin. I also do not think it would be worth the (relatively low) monthly price to share with a single person. I do not expect our team to grow either, so there is no need to prepare for this. I think the best option is to share the database on Dropbox so that we both have the same version to work with. I've read some forums posts on doing those and all seem to discourage sharing Access databases like this. However, I've had a hard trouble understanding, as I am a novice. There is no need for us to both be able to work on the database at the same time, but I have no idea how I can restrict access to a single user at a time. Can you please explain what the problems are with sharing an Access database in simple terms and what workarounds there is, if any?

Please don't suggest other options as I am trying to keep this project as simple as possible and my experience with SQL is minimal. Plus, I need to keep this simple for my collaborator as well and this is a sort of personal project I don't have a ton of time and resources for.

Thank you for the help!

To put it simply - it would work if only one of you were editing the database at a time. If both of you were to edit the database, it could corrupt the file, discard each other changes or cause sync issues.

By the way, have you considered Google Docs?


Detecting running pg_dump conflicting with schema changes I use Heroku PostgreSQL and their PGBackups for my production environment. I have found, for reasons that I'm not quite clear on, that at least some schema changes do not run correctly during the time that the backups are happening.
According to these Heroku docs, it seems like pgbackups is using pg_dump internally, though there may be additional things going on as well. The docs indicate that the output is essentially the same as this pg_dump command.
pg_dump -F c --no-acl --no-owner --quote-all-identifiers $DATABASE_URL

Attempting to run migrations during the backup window has repeatedly caused me some pain, as the migrations lock up and hang. To mitigate this, I'd like to detect when the backups are running, and avoid running the migrations by having a pre-check in the script that runs the migrations.
I can do this by checking with Heroku's CLI, and that will work for my current specifics, but I'm wondering if something less specific to Heroku might be reasonable, by watching for a running pg_dump instead.
Is there a good way to detect if pg_dump is currently running, and may block schema changes?

Unless somebody has overridden the application_name parameter for pg_dump, you could do this:
SELECT count(*) FROM pg_stat_activity
WHERE application_name = 'pg_dump';

If that gives you 0, no pg_dump is running.


mysql taking too long to send data I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".

The table

CREATE TABLE IF NOT EXISTS details (
  id int(11) NOT NULL,
  date date NOT NULL,
  time int(2) NOT NULL,
  minutes_online decimal(5,0) NOT NULL,
  minutes_playing decimal(5,0) NOT NULL,
  minutes_chatting decimal(5,0) NOT NULL,
  minutes_away decimal(5,0) NOT NULL
  PRIMARY KEY (id,date,time)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The simple query

mysql> SELECT * FROM details WHERE id = 3014595;

Explain

mysql> EXPLAIN SELECT * FROM details WHERE id = 3014595;
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | details   | ref  | PRIMARY       | PRIMARY | 4       | const | 1482 |       |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+

Profile for the query

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000078 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000126 |
| System lock                    | 0.000011 |
| Table lock                     | 0.000030 |
| init                           | 0.000027 |
| optimizing                     | 0.000117 |
| statistics                     | 0.040077 |
| preparing                      | 0.000029 |
| executing                      | 0.000006 |
| Sending data                   | 7.536960 |
| end                            | 0.000013 |
| query end                      | 0.000004 |
| freeing items                  | 0.000037 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

As you can see, the SELECT statement used the index and read only 1482 rows. Yet, the query spent 7.536960 seconds sending the data. It is like the query read much more rows it needed.

It is a simple query, with just 7 fields (row avg 59 Bytes) and no fancy function. Any idea what can be causing this?

Note: id is the user ID. Each user may have at least one entry for every hour of every day. Therefore, id is not unique.

Edit: I have another table with the same structure and much more rows (34 Million). If I run the same query on this larger table, it returns the results in less than 1 second.

The only difference is that the larger table does not get as many queries as the smaller table.

Is it possible that the number of queries is slowing down the process? MySQL cache is on. I've also CakePHP caching the queries to reduce the number of queries.
Is it possible that the file where the table is saved is corrupted or something?

Update
The issue was solved by separating the data tier from the web tier. The data tier also got an upgrade on the RAM and is running on raid10.

For anyone who stumble upon this question and wondering, even without upgrade of RAM, why sending data was taking so much longer. It is because sending data actually include time of searching the data that is to be sent.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

  The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.


Mysql dump restore doing some funny stuff I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command

[root@localhost /]# mysqldump -u root -p fms  < /device.sql

The funny part it keep running and this dump only consist of table data and I see other table data being run when I run this command where is the other table data comes from?

I think you meant this:

mysql -u root -p fms < /device.sql

This will import SQL from /device.sql into the fms database

mysqldump only outputs and is not designed to input anything

Doing this

mysqldump -u root -p fms

will echo the mysqldump all all tables currenly in the fms database


SQL Server 2000 Replication I have a challenge with replication on SQL Server 2000. The replication has full permissions, but still I get this below error. The SQL Server 2000 is installed on the C: Drive, but the database is on E:.

  \myserver\Replication\unc\myserver_GS_ECC25_2675_Publication1\20130813090100\SpInsertSbraConf_1469.sch'
  could not be propagated to the subscriber. (Source: Merge Replication
  Provider (Agent); Error number: -2147201001)

  Insert Error: Column name or number of supplied values does not match
  table definition. (Source: myserver (Data source); Error number: 213)

That error would imply to me that there is a table layout difference between the publisher and the subscriber. Have any recent updates to the DB structure been applied to one but not the other?

You could run a DB comparison tool (something like the free edition of http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html should be more than sufficient) against the two databases to check for this.

You probably shouldn't be using SQL 2000 any more - it is somewhat old and the extended support period for its last release (SP4) ended in April this year.


Getting Error/Exception In GoldenGate Replication I am planning to configure a simple GoldenGate configuration and want to implement an exception table that will store all the exceptions. I followed two links, neither of which work for me:

Troubleshoot Oracle GoldenGate Using Exception Handler
Creating a GoldenGate Exception Handler to Trap and Log Oracle Errors

My Extract Parameter File

extract ext9
userid ogg_user, password oracle
rmthost targetGG, mgrport 7809
rmttrail /u01/app/oracle/product/gg_trg/dirdat/np
table scott.emp;

Replicat File

replicat rep9
ASSUMETARGETDEFS
userid ogg_user, password oracle
include ./dirprm/EXCEPTION_HANDLER.mac
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
MAP scott.emp, TARGET scott.emp;
MAP scott.emp #exception_handler();

Exception Handler Macro

Created in ./dirprm/EXCEPTION_HANDLER.mac:

MACRO #EXCEPTION_HANDLER
BEGIN
, TARGET GGATE_ADMIN.ALLEXCEPTIONS
, COLMAP ( REPLICAT_NAME = @GETENV("GGENVIRONMENT", "GROUPNAME"))
, TABLE_NAME = @GETENV ("GGHEADER", "TABLENAME")
, ERRNO = @GETENV ("LASTERR", "DBERRNUM")
, DBERRMSG = @GETENV ("LASTERR", "DBERRMSG")
, OPTYPE = @GETENV ("LASTERR", "OPTYPE")
, ERRTYPE = @GETENV ("LASTERR", "ERRTYPE")
, LOGRBA = @GETENV ("GGHEADER", "LOGRBA")
, LOGPOSITION = @GETENV ("GGHEADER", "LOGPOSITION")
, COMMITTIMESTAMP = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;

This is the error I have been getting. I have tried/Googled a lot to get rid of this, finally posting it here for help:

  2016-06-09 17:18:12  ERROR   OGG-00212  Invalid option for MAP: TABLE_NAME.

I don't know what is causing the problem. I have tried all space issues found on Google and with other bloggers but no success.

There is an error in the code:

, COLMAP ( REPLICAT_NAME = @GETENV("GGENVIRONMENT", "GROUPNAME"))

There is double bracket at the end of the line.


Table not analyzed after partition has been split On my table I split a subpartition successfully. After that I rebuild the indexes and gather statistics.

ALTER TABLE T_MO_DPI SPLIT SUBPARTITION ACTIVE_RELATIONS VALUES (24) INTO (
   SUBPARTITION ACTIVE_RELATIONS_2G,
   SUBPARTITION ACTIVE_RELATIONS);

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI');

Now, when I check the statistics then apparently table is not analyzed, i.e. table statistics are not present:

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'T_MO_DPI';

+---------------------------------------------------------------+
|TABLE_NAME|SUBPARTITION_NAME      |NUM_ROWS|LAST_ANALYZED      |
+---------------------------------------------------------------+
|T_MO_DPI  |ACTIVE_ELEMENTS        |9146    |18.10.2018 22:18:51|
|T_MO_DPI  |ACTIVE_CELLS           |39812   |13.06.2019 22:32:33|
|T_MO_DPI  |ACTIVE_RELATIONS_2G    |        |                   |
|T_MO_DPI  |ACTIVE_RELATIONS       |        |                   |
|T_MO_DPI  |DELETED_ELEMENTS       |1160    |19.11.2018 22:18:14|
|T_MO_DPI  |DELETED_CELLS          |14102   |10.06.2019 22:17:53|
|T_MO_DPI  |DELETED_RELATIONS      |14865562|11.03.2019 23:10:10|
+---------------------------------------------------------------+

Any idea what I am doing wrong?

Running

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS_2G');
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_MO_DPI', 'ACTIVE_RELATIONS');

does not help.

Use the proper GRANULARITY option.

CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         )
      );

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SQL>

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40

Test1:

SQL> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'GLOBAL');

PL/SQL procedure successfully completed.

SQL> alter table QUARTERLY_REGIONAL_SALES split subpartition q1_1999_northwest into (subpartition q1_1999_northwest_1 values('OR'), subpartition q1_1999_northwest_2);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST                       0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:46:40
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:46:40

7 rows selected.

Test2:

SQL> exec DBMS_STATS.SET_PARAM ('GRANULARITY', 'SUBPARTITION');

PL/SQL procedure successfully completed.

SQL> alter table QUARTERLY_REGIONAL_SALES split subpartition Q1_1999_SOUTHEAST into (subpartition Q1_1999_SOUTHEAST_1 values ('FL'), subpartition Q1_1999_SOUTHEAST_2);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'QUARTERLY_REGIONAL_SALES');

PL/SQL procedure successfully completed.

SELECT TABLE_NAME, SUBPARTITION_NAME, NUM_ROWS, LAST_ANALYZED
FROM USER_TAB_SUBPARTITIONS
  3  WHERE TABLE_NAME = 'QUARTERLY_REGIONAL_SALES';

TABLE_NAME                     SUBPARTITION_NAME                NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHWEST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_1                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHEAST_2                     0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHWEST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHEAST                       0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_NORTHCENTRAL                    0 2019-07-04 11:52:31
QUARTERLY_REGIONAL_SALES       Q1_1999_SOUTHCENTRAL                    0 2019-07-04 11:52:31

8 rows selected.

SQL>


What does this margin symbol in the SSMS text editor mean? While editing text in SSMS I mistakenly pressed a random keyboard combination, which I think began with Ctrl-K, and found this diagonal arrow symbol appeared in the text margin where the breakpoints normally go:

Can anyone tell me what it means?

SQL Server 2016 13.0.15900

I found the answer on Stack Overflow, as soon as I remembered that SSMS is a 'special flavour' of the Visual Studio Shell.

In short, it's a Task List item, which is toggled on/off with Ctrl-K, Ctrl-H. Items can be displayed with the menu View/Task List.


Error on creating database in SSMS I am using SQL Server Management Studio 2008. I am connect to localhost and I want to create a database. When I click on New Database, type the name and click ok, I get the message:

  Index was outside the bounds of the array (Microsoft.SQLServer.Smo).

Perhaps you have Management Studio 2008 and your localhost is a newer version. If this is the case, you should always use the most recent version of Management Studio even if you sometimes manage lower versions. You can determine the version of the engine you're managing by looking at the top-level node in Management Studio's Object Explorer - 2008 is going to show 10.0.xxxx, 2008 R2 is going to show 10.50.xxxx, and 2012 is going to show 11.0.xxxx. You can tell what version of the client tools you have by going to Help > About.

Another possibility is that you are still using the RTM version of Management Studio 2008. If this is the case, you should apply SP3 (if you are in fact on 2008) or apply SP2 (if you are on 2008 R2, which a lot of people confuse with 2008).

Your best bet, IMHO, is going to be to uninstall the 2008 client tools entirely and install Management Studio 2012 (in SP1, the client tools are now free and fully functional). Not only does it have far fewer bugs like this, and plenty more features, but you won't have to do this later when you have to manage your first 2012 instance (which the 2008 tools aren't going to be very good at).

In the meantime, instead of using the UI as a crutch to create a database, you could learn the DDL and perform this in a query window, which won't have any problems:

CREATE DATABASE MyNewDatabaseName;

You can learn more about the CREATE DATABASE syntax in Books Online.


Many queries are slower on new hardware and newest version of PostgreSQL Note: I have significantly re-worked this question based on the direction the commentators have taken me and what I have learned on the way.

I am setting up a new development database server. The new one is a 6th Gen i7-6700 with 16GB DDR4 memory and an Intel 750 SSD with PostgreSQL 9.6. The old one is a 4th gen i5-4570 with 16GB DDR3 memory and a Crucial SSD Raid 0 array with PostgreSQL 9.2. I benchmarked the 750 and it is much faster than the RAID. However, the read performance below 256KB is slower.

I used pg_dump and pg_restore to move the data and I ran vacuum analyze on all databases.

The new computer should be quite a bit faster than the old one and in some types of queries is it, but in others it is several times slower. I also installed 9.2 on the new computer to try and isolate the database versions from the hardware. I kept the postgresql.conf identical where possible (9.6 changes some wal settings.)

I set up pg_stat_statements to get some statistics to narrow down the problem.  I have 500+ schemas and 3000+ tables. The transformations I am running are creating new tables from some of these tables and updating those new tables from these 3000 other tables. I am bouncing all over this 50GB of data.

On the new server and 9.6 create table, copy, vacuum, analyze, drop table, updates that affect many rows, create index, create table as are usually faster, up to twice as fast. But occasionally they will be several times slower. The new server running 9.2 sees a similar pattern, but the performance increases are not nearly as pronounced. Interestingly, when 9.6 is slower on some particular copy or vacuum, 9.2 will usually be similarly slow.

drop index, drop table, select queries that return few rows, select count(*), update queries that affect a single row are all usually 50%-300% slower in the new 9.6 server. 9.2 on the new server is also slower, but usually only 20%-30%. I do not know how PostgreSQL accesses the disk, but I suspect that the Intel 750's slower read performance on smaller files is having an effect as seen in the slower 9.2 speed. But that does not explain the 5x-10x performance degradation between 9.2 and 9.6 on the same server.

So my conclusion is 9.6 is significantly slower for certain types of queries.

I have run explain on many of the queries in question and the plans are identical.

Here are some representative examples (from pg_stat_statements) of the queries that are slow. Note that they are all primary keyed, indexed vacuumed and analyzed and otherwise identical between the two databases. Also, I only selected queries that have at least 50 calls.

Update finalal Set jsondone=?, json=? Where id=? //20% slower
SELECT * FROM archive_natrilis_2015_q3.natrilis_releases WHERE siteid = ? ORDER BY reporting_year ASC LIMIT ? // 404% slower
SELECT * FROM work_narcra00_2016_q3.narcra_handler WHERE siteid = ? ORDER BY handler_sequence_number ASC LIMIT ? // 182% slower
select * from listtypedesc // small 20 row table, 173% slower.
select * from INFORMATION_SCHEMA.schemata where schema_name not like ? and schema_name not like ? AND schema_name like ? Order by schema_name Desc // 52% slower
Select count(*) From work_allustis_2016_q3.allustis_details Where siteid = ? // 234% slower
SELECT * FROM archive_naerns14_2015_q2.naerns14_material_involved WHERE siteid = ?  LIMIT ? // 287% slower

Is my conclusion correct? And if so is there a 9.6 specific way to optimize these slow queries? If not, are there other debug actions to recommend?

You have changed too many things at once.  First run 9.2 on your new hardware and check the performance. Then upgrade to 9.6 once you get that sorted out.
How did you move the data?  Logically, by using pg_dump and then a restore, or physically by using pg_basebackup (or a cold copy) and then pg_upgrade?

Update finalal Set jsondone=1 Where id=1
The old server runs this at an average of 0.2 ms over 72 iterations. The new server is 1.43ms.

72 iterations of the same id=? value, or consecutive ones, or random ones?  And all in one transaction, or each a separate one?

The new server is showing half the cost (but more startup costs for some reason) so I assume it would be faster, especially on faster hardware.

Those costs are not general time estimates, they are internal accounting used to make reasonable planner choices.  Things for which there are no choices (there is only way to update a row) are not costed at all, but obviously they take actual time to do.  And even with that in mind, you have to use great caution comparing them across versions.  They just aren't meant for that purpose.

I did notice that shared_blks_read and shared_blks_dirtied were about twice as high on the new server. shared_blks_hit were only slightly higher. Maybe a clue in that , but I do not really know that this means.

I think what this means is that your data in the new server is packed more tightly than it was in the old server, probably because you used pg_dump to get the data over rather than a physical copy.  The default fillfactor for tables is 100%, so the rows are packed into a page as many as will fit.
So on the old server when you do an update, there is room to put the new version of the updated row in the same page as the old version.  On the new server, there is no room on the page, so it has to seek out a different page to put the new version of the row on (and then both pages get dirtied).  Which also means it has to update the indexes, so they know where to find the new version.


Can I find errors without using testing data for all the branches of each function? In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This is a big pain while debugging functions with many conditions in them. The only way I know now is to make testing data for every branch in each function and run the function over them. This should be fine, but just making the data for all the conditions in all the ordinary and trigger functions would consume hours, so I wonder if there is some better solution (free and open source, if possible).

So how to avoid making so much testing data while debugging my PL/pgSQL functions?

Well, there is the Postgres extension plpgsql_check, successor of plpgsql_lint:

  plpgsql_check is next generation of plpgsql_lint. It allows to check
  source code by explicit call plpgsql_check_function.

I'm not currently using either, but the author Pavel Stehule is one of the core developers of PL/pgSQL (and also active around here).

Pavel announced it Dec. 2013 in this blog post.


My SQL Replication to an online database server I have two MySQL Databases:

On a local machine (connected to internet via a static IP)
My SQL database on my webserver (JustHost, which I can access remotely)

I want a replicate my local database to the online one. AFAIK I cannot modify the configuration files of the database. So is there any way to set up replication between these two, or is there any way to accomplish my requirement?

You can use Tungsten for this, but only master->slave replication. Set up your local machine as a master, and connect tungsten to it. It will then run the statements as a normal client on the remote machine.


Slow access to JSONB column I have the following query that accesses a JSONB column in the table datasets:

SELECT count(*), my_jsonb_column->0->'PARAM1'
FROM datasets
GROUP BY my_jsonb_column->0->'PARAM1'

The explain analyze output is

HashAggregate  (cost=4778.92..5577.57 rows=53243 width=40) (actual time=1189.083..1189.135 rows=10 loops=1)
  Group Key: ((my_jsonb_column -> 0) -> 'PARAM1'::text)
  ->  Seq Scan on datasets  (cost=0.00..4510.44 rows=53696 width=32) (actual time=0.052..1165.469 rows=53696 loops=1)
Planning time: 0.081 ms
Execution time: 1189.242 ms

A similar query that uses a plain column is roughly 50x faster, with a pretty much identical query plan also using a sequential scan.

The JSONB column contains an array with typically 1-3 elements, each one an object containing between 10 to 300 keys.

I expected the JSONB version to be significantly slower than the plain column version, but I didn't expect it to be that slow. Am I doing something particularly inefficient here?

Is there anything I can do to speed up this kind of access to a JSONB column? I'll need to access different keys, not just one specific one.

Does the size of the JSONB column play a role here? I would have assumed that this kind of access to a specific key should be mostly independent of the column size, but I'm not entirely sure.

The JSONB object is probably large enough that it is compressed and stored out of line, in a TOAST table.  Having to retrieve and decompress the TOASTed value for every row is going to slow things down by a lot.

I just wouldn't use JSON or JSONB for this purpose if I wanted speed.  If you need to access a small number of keys, pulling them out into the table proper could help.


SQL Server Index vs Statistic What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?

Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution.  Therefore, CREATE INDEX will be the DDL to create an index (clustered, nonclustered, etc.) and CREATE STATISTICS is the DDL to create the statistics on columns within the table.

I recommend you read about these aspects of relational data.  Below are a couple of beginner, introductory articles.  These are very broad topics, and therefore the information on them can go very wide and very deep.  Read up on the general idea of them below, and ask more specific questions when they arise.

BOL reference on Table and Index Organization
BOL reference on Clustered Index Structure
BOL reference on Nonclustered Index Structures
SQL Server Central on the Introduction to Indexes
BOL reference on Statistics

Here is a working example to see these two parts in action (commented to explain):

use testdb;
go

create table MyTable1
(
    id int identity(1, 1) not null,
    my_int_col int not null
);
go

insert into MyTable1(my_int_col)
values(1);
go 100

-- this statement will create a clustered index
-- on MyTable1.  The index key is the id field
-- but due to the nature of a clustered index
-- it will contain all of the table data
create clustered index MyTable1_CI
on MyTable1(id);
go

-- by default, SQL Server will create a statistics
-- on this index.  Here is proof.  We see a stat created
-- with the name of the index, and the consisting stat
-- column of the index key column
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');

-- here is a standalone statistics on a single column
create statistics MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- now look at the statistics that exist on the table.
-- we have the additional statistics that's not necessarily
-- corresponding to an index
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');

-- what is a stat look like?  run DBCC SHOW_STATISTICS
-- to get a better idea of what is stored
dbcc show_statistics('MyTable1', 'MyTable1_CI');
go

Here is what a test sample of statistics can look like:

Notice that Statistics are the containment of the data distribution.  They help SQL Server determine an optimal plan.  A good example of this is, imagine you are going to life a heavy object.  If you knew how much that weight because there was a weight marking on it, you'd determine the best way to lift and with what muscles.  That's sort of what SQL Server does with statistics.

-- create a nonclustered index
-- with the key column as my_int_col
create index IX_MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- let's look at this index
select
    object_name(object_id) as object_name,
    name as index_name,
    index_id,
    type_desc,
    is_unique,
    fill_factor
from sys.indexes
where name = 'IX_MyTable1_MyIntCol';

-- now let's see some physical aspects
-- of this particular index
-- (I retrieved index_id from the above query)
select *
from sys.dm_db_index_physical_stats
(
    db_id('TestDB'),
    object_id('MyTable1'),
    4,
    null,
    'detailed'
);

We can see from the example above that the index actually contains data (depending on the type of index, leaf pages will be different).

This post has only shown a very very very brief overview of these two large aspects SQL Server.  Both of these could take up chapters, and books.  Read some of the references, and then you will have a better grasp.


How can I show the values from my input that don't exist in a table? Could you help me on this one?

See I have this order_table where I have to find the order numbers that don't exist in the table.

Let's say I have columns id, order_number in the table named order_table.

What I want is to show in my result all the values that don't exist in order_table.

Like for example:

I have order_number values 43454235423, 785686847856, 1243215421, 6437647, 978423454. I then do

SELECT *
FROM order_table
WHERE order_number = ('43454235423', '45423534252','785686847856', '1243215421',
                      '6437647', '978423454','5634636','4354235235')
--return order_numbers that don't exist in the table as 'status'

This is my sample query result:

order_number  status
------------  ---------
43454235423   not found
785686847856  not found
1243215421    not found
6437647       not found
978423454     not found

create table order_table(order_number number);
insert into order_table values (43454235423);
commit;

with input_values as
(
  select
    to_number(column_value) as order_number
  from
    xmltable('43454235423, 785686847856, 1243215421, 6437647, 978423454')
)
select
  v.order_number, 'not found' as status
from
  input_values v
left join
  order_table o on (v.order_number = o.order_number)
where
  o.order_number is null
;

        ORDER_NUMBER STATUS
-------------------- ---------
        785686847856 not found
          1243215421 not found
             6437647 not found
           978423454 not found


Decrease WAL file size for backup I'm setting up a backup strategy based on WAL files archiving and transmission via rsync.

As

DB activity will propably be very low (less than 100 inserts/updates/deletes per day), at least in the beginning
I'm planning to backup remotely once a day

I wonder if I should decrease WAL file size to decrease storage consumption, because from my understanding Postgres switches to another WAL file and archives the current one once it reaches 16MB size, which in my case could normally take more than one day (maybe more than one week); in this case I would end up loosing the small number of operations happened in between. Alternatively I could force WAL file switch via select pg_switch_xlog() (which in any case I will use to backup once a day) but would end up having lots of 16MB files where probably 1MB files would be more than enough.

Am I missing something? If not how can I reduce WAL file size?

I've also been suggested to use streaming replication but I must use a NAS as my backup device and don't know if it allows such strategy.

If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use  pg_clearxlogtail utility to zero the end of any WAL segment as part of your archive script before you gzip your archive files.

In 9.4 and above you just need to gzip the file, with no need for pg_clearxlogtail.

The change was made commit 9a20a9b, but it's only in tag REL9_4_BETA1, so the change will first appear in 9.4:

commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:   Mon Jul 8 11:23:56 2013 +0300

    Improve scalability of WAL insertions.

    This patch replaces WALInsertLock with a number of WAL insertion slots,
    allowing multiple backends to insert WAL records to the WAL buffers
    concurrently. This is particularly useful for parallel loading large amounts
    of data on a system with many CPUs.

    This has one user-visible change: switching to a new WAL segment with
    pg_switch_xlog() now fills the remaining unused portion of the segment with
    zeros. This potentially adds some overhead, but it has been a very common
    practice by DBA's to clear the "tail" of the segment with an external
    pg_clearxlogtail utility anyway, to make the WAL files compress better.
    With this patch, it's no longer necessary to do that.

BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.

Update: As Jeff noted, pg_clearxlogtail hasn't been updated for 9.3. The incompatibility is very simple though; the definition of XLogRecPtr changed from

typedef struct XLogRecPtr
{
        uint32          xlogid;                 /* log file #, 0 based */
        uint32          xrecoff;                /* byte offset of location in log file */
} XLogRecPtr;

to

typedef uint64 XLogRecPtr;

when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.

The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.


mysql inner join if value = true ( share system ) users

|  id  | name  |
|------|-------|
|   1  | John  |
|   2  | Clark |

groups

|  id  | group_name|
|------|-----------|
|   1  | groupName |

posts
authorType*
1- user
2- group

|  id  | post_data   | author | authorType* |
|------|-------------|--------|-------------|
|   1  | Lorem Ipsum |    1   |      1      |
|   2  | Lorem Ipsum |    1   |      1      |
|   3  | Lorem Ipsum |    2   |      1      |
|   4  | Lorem Ipsum |    1   |      2      |
..........

user_posts
share_in*
1- user
2- group

|  id  | user_id | sender_id | post_id | shared_id | share_in* |
|------|---------|-----------|---------|-----------|-----------|
|   1  |    1    |     1     |    1    |    null   |   null    |
|   2  |    1    |     1     |    2    |    null   |   null    |
|   3  |    2    |     2     |    3    |    null   |   null    |
|   4  |    1    |     1     |    3    |      3    |     1     |
|   5  |    1    |     1     |    3    |      1    |     2     |

How to execute the query

select * from user_posts where user_id = 1
if( user_posts.share_in = 1 ) { join users on user_posts.shared_id = users.id }
else join groups on user_posts.shared_id = groups.id

While I don't think anybody is going to be confident they know what you're asking based on the first revision of your "question," I hope the following statement will give you at least some form of direction with how to achieve your... goal, I guess.  Here is an SQL Fiddle in case you would like to see the query in action.  I can only assume you'll also be interested in learning about COALESCE, as illustrated in this SQL Fiddle.

SELECT  *
FROM    `UserPost` up
LEFT JOIN `User` u
    ON  up.shared_id = u.id
    AND up.share_in = 1
LEFT JOIN `Group` g
    ON  up.shared_id = g.id
WHERE   up.user_id = 1;