Keep H2 in-memory database between connections I create and use a H2 database in-memory (no writing to storage) for demos and quick tests using this code in Java:

 Connection conn = DriverManager.getConnection( "jdbc:h2:mem:example_db" ) ;

This works the first time, but then the database seems to disappear, unavailable for further work. How can I make use of the same in-memory database over time?

Set DB_CLOSE_DELAY=-1
By default, an in-memory database in H2 is discarded after the connection closes.
To override this default behavior, set DB_CLOSE_DELAY to a negative one. You can do so either:

When making a connection with a JDBC path
By calling a SQL command

JDBC path
To keep the database in existence despite the connection closing, add an element to your JDBC path.  The database will continue to live on in memory until your app exits. So you can make successive connections to the same continuing database.
Tip: Notice the SEMICOLON (not COLON) between the database name and this element.
Connection conn =
    DriverManager.getConnection(
        "jdbc:h2:mem:example_db;DB_CLOSE_DELAY=-1"  // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
    )
;

If you want to explicitly call for the default behavior, set 0. For a number of seconds before discarding the database after the last connection closes, pass a positive integer ( >=1 ).
This feature is covered in the manual in the section on In-Memory Database in the last paragraph.

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

SQL command
This setting can also be manipulated at runtime via the SET DB_CLOSE_DELAY command, passing an integer.
To quote that documentation:

SET DB_CLOSE_DELAY int
Sets the delay for closing a database if all connections are closed. The value -1 means the database is never closed until the close delay is set to some other value or SHUTDOWN is called. The value 0 means no delay (default; the database is closed if the last connection to it is closed). Values 1 and larger mean the number of seconds the database is left open after closing the last connection.
If the application exits normally or System.exit is called, the database is closed immediately, even if a delay is set.
Admin rights are required to execute this command, as it affects all connections. This command commits an open transaction in this connection. This setting is persistent. This setting can be appended to the database URL: jdbc:h2:test;DB_CLOSE_DELAY=-1
Example:
SET DB_CLOSE_DELAY -1


MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench I just setup the phpMyAdmin and I have this problem: When I login, I can't see any tables.

I had tried this with MySQLWorkbench and I got the same result.

Any suggestions ?

The account that you are using to log into PHPmyAdmin/Workbench may not have permissions on the required tables. Do you see the required tables when you use the same login in the MySQL monitor console? Try logging in with a higher privileged account and viewing again.


Automatically purging binlogs after successful replication I have a dedicated server hosting a MySQL database, and want to use replication to have an always up-to-date backup somewhere.

I chose to use Amazon RDS as my replica, and I'm following this guide:

Replication with a MySQL Instance Running External to Amazon RDS

What I fail to understand however, is who/what is supposed to take care of purging binary logs. The only configuration I found is expire_logs_days, which the documentation describes as:

  You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (...). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

Is this the standard procedure DBAs use?

Isn't it possible to have RDS, which is the only replica, purge the logs on the master as soon as it's processed them?

Server is responsible to purge their own file. Guess what happens if your master has two slaves and one of it catches up faster deleting logs required for second?!!

So yes expire_logs_days is the way to go. Set that value wisely and let master do its business.

That said why do you want to purge logs? You have purge binary logs to command but I'd still let purging upto master unless disk is critical.

If you want it, then you can script out the manual purging logic:
Check all slaves have caught up to latest
Purge a logs before the exec_master_log_file.

In my opinion this is absolutely unnecessary unless there some odd requirement that u just can't think about...


Single column join vs multicolumn join In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected.

In one scheme a surrogate key is used which means that in the join condition between the two tables a single join condition is used. This join is done using a hash join.

The other schema uses a natural key of 4 columns. In this case postgres uses a merge join which is much slower. However I had understood that postgresql can do multi column hash joins.

Is this speed difference normal? Can I get the speed of the 4 column join more on par?

Here are the two queries and the explain analyze:

Natural key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.filiaal_id=kbr.kassabon_filiaal_id AND
        kb.kassanr=kbr.kassabon_kassanr AND
        kb.jaar=kbr.kassabon_jaar AND
        kb.bonnr=kbr.kassabon_bonnr);

"Merge Join  (cost=289690.28..309517.94 rows=11981 width=280) (actual time=5507.702..9675.564 rows=907105 loops=1)"
"  Merge Cond: ((kbr.kassabon_bonnr = kb.bonnr) AND (kbr.kassabon_filiaal_id = kb.filiaal_id) AND (kbr.kassabon_kassanr = kb.kassanr) AND (kbr.kassabon_jaar = kb.jaar))"
"  ->  Sort  (cost=183246.72..185514.48 rows=907105 width=141) (actual time=4608.588..7210.120 rows=907105 loops=1)"
"        Sort Key: kbr.kassabon_bonnr, kbr.kassabon_filiaal_id, kbr.kassabon_kassanr, kbr.kassabon_jaar"
"        Sort Method: external merge  Disk: 134664kB"
"        ->  Seq Scan on kassabon_regel kbr  (cost=0.00..28374.05 rows=907105 width=141) (actual time=0.013..617.881 rows=907105 loops=1)"
"  ->  Materialize  (cost=106442.89..109233.22 rows=558067 width=139) (actual time=899.101..1321.491 rows=907133 loops=1)"
"        ->  Sort  (cost=106442.89..107838.05 rows=558067 width=139) (actual time=899.097..1182.711 rows=558067 loops=1)"
"              Sort Key: kb.bonnr, kb.filiaal_id, kb.kassanr, kb.jaar"
"              Sort Method: external merge  Disk: 48808kB"
"              ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.009..95.594 rows=558067 loops=1)"
"Total runtime: 9757.286 ms"

Surrogate key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.id=kbr.kassabon_id);

"Hash Join  (cost=31538.51..121752.89 rows=907105 width=270) (actual time=226.936..1817.946 rows=907105 loops=1)"
"  Hash Cond: (kbr.kassabon_id = kb.id)"
"  ->  Seq Scan on kassabon_regel kbr  (cost=0.00..27461.05 rows=907105 width=131) (actual time=0.005..159.972 rows=907105 loops=1)"
"  ->  Hash  (cost=13117.67..13117.67 rows=558067 width=139) (actual time=226.770..226.770 rows=558067 loops=1)"
"        Buckets: 16384  Batches: 8  Memory Usage: 7652kB"
"        ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.004..70.969 rows=558067 loops=1)"
"Total runtime: 1866.136 ms"

First, your estimates are bad.  I would expect estimates to be harder to calculate on multi-column joins because, as in here, the values may be co-existing in ways they may not.  Additionally the rows on joining may be wider and so you might have work_mem issues too.

Another important area to check is caching.  Try:

explain (analyse true, buffers true) [query]

The two issues I could see would have to do with planning costs and multiple columns, particularly the question of coexistence of various values across the join, and the memory usage.  Multi-column joins complicate both these areas.


Limit LAG function to a subset I'm having a difficult time wrapping my head around this issue. I need to get the previous status of our finished customers. I learned about the LAG function and it almost does what I need, but the issue is it can potentially get the status from a different customer because the previous row is a different CustomerId.

Is there a way to limit the LAG function to only return a value of null if the previous CustomerId is different or something that will generate the same affect? I created some dummy data here: Rexter Lag Testing

As you can see, the 4th row is for customerId 2, but PreviousStatus value is from CustomerId 1. It should be NULL.

SELECT cs.CustomerId
     , s.StatusName
     , LAG(s.StatusName) OVER (PARTITION BY cs.CustomerId
                               ORDER BY cs.CustomerStatusId) as PreviousStatus
FROM #CustomerStatus as cs
left join #Status as s on s.StatusId = cs.StatusId

fiddle


leading 0 missing in column when exporting to csv I have a query I export which is then used to import products into our website.

One of the ways we help people find products more easily is by using a long and short item code. It's basically the last 5 digits of the main code.

People can search for the whole or partial number.

However when I export my query as a csv, any leading zeroes are removed.

Here is how I create the column in Access:

(Right(products_vendor1.shortISBN13,5)) AS item_code

Obviously, in Access I have no problem viewing this query and the values display even with the zeroes.

How can I export the csv without removing the leading zeroes?

These are the settings I'm using when I export:

  UTF-8 delimited by comma and quotes ,"

  and checking the box Include field names in first row.

And it's not that the leading zeroes are missing only with excel. It doesn't matter if I open with notepad++ or libre office calc

I'm using Access 2013 64bit.

Leading zeroes are removed if the exporter thinks the column is numeric.  You need to force the exporter to treat that column as text.

For instance, this might work:

chr(34) + (Right(products_vendor1.shortISBN13,5)) + chr(34)


Avoid PLE drops while performing DELETE operations If my quesion is not specific enough please tell what details are missing.
We are runing daily archive operations on the whole database where we keep data from the last 30 days. We are deleting data in 100 records batches. For one particular table Which has the biggest(in terms of size) records it take around 3-5 hours to delete all 2 millions of old records even just after index maintenance operations. Also deletions of that particular table causes PLE to drop. My question is how to accelerate that proces and avoid PLE drops?

For one particular table Which has the biggest(in terms of size)
  records it take around 3-5 hours to delete all 2 millions of old
  records

we need more detail like table structure along with data type and index details.

Try opposite method.

Keep data of last 30 days and truncate old table.

For example (Step 1),

declare @CurDate datetime=getdate()

select *
into NewTable
from ExistingTable
where datecol >= @CurDate and datecol <= dateadd(day,-30,@CurDate)

Rename old table to anything unique or drop table as per requirement. In fact drop table only after testing and approval from other.
Rename new table to existing table


Bulk grant execute permissions for scalar functions & stored procedures We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions and stored procedures in the dbo schema.

I've seen bits of documentation with T-SQL queries to grant execute permissions on single stored procedures or scalar functions, but I need to do this for multiple DBs and multiple stored procedures.

Unfortunately, my experience with MSSQL is limited so I have no idea where to begin with this.

Any help would be awesome.

This bit of nested dynamic SQL grants exec or select on all the various procedure and function types, that were not shipped by MS, and that exist in any database outside of master, msdb, tempdb and model. It is certainly not easy on the eyes, tedious to reverse engineer, and I haven't thoroughly tested it, but hopefully if you work through it you can figure out what I've done. ALl you really need to specify is the name of the login you want to grant the rights to - of course this assumes that the login has been mapped to a database user with the same name in each database. That was a layer of complexity I wasn't prepared to add, sorry. :-)

DECLARE
  @principal SYSNAME = N'some_server_principal';

DECLARE
  @sql1 NVARCHAR(MAX) = N'',
  @sql2 NVARCHAR(MAX) = N'',
  @sql3 NVARCHAR(MAX) = N'';

SELECT @sql1 += N'
SELECT @sql += N''SELECT @sql += N''''
USE ' + QUOTENAME(name) + ';
GRANT ''''
  + CASE WHEN type IN (''''P'''',''''PC'''') THEN ''''EXEC'''' ELSE ''''SELECT'''' END
  + '''' ON '''' + QUOTENAME(SCHEMA_NAME(schema_id)) + ''''.'''' + QUOTENAME(name)
  + '''' TO ' + QUOTENAME(@principal) + ';'''' FROM ' + QUOTENAME(name) + '.sys.objects
  WHERE is_ms_shipped = 0 AND type IN (''''P'''',''''PC'''',
    ''''FN'''',''''AF'''',''''FN'''',''''TF'''',''''IF'''');'';'
  FROM sys.databases
  WHERE database_id > 4;

EXEC sp_executesql @sql1, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql2 OUTPUT;

EXEC sp_executesql @sql2, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql3 OUTPUT;

-- you won't be able to validate all of this, since Management Studio will
-- truncate the output quietly. But you should get a good sense of what the
-- end result will be:

SELECT @sql3;

-- When you are happy with the above, uncomment this:

-- EXEC sp_executesql @sql3;

This is kind of like Inception. In order to generate the right output, you have to figure out which layer every element is on. This is why the initial string is so ugly, and littered with escaped apostrophes in sets of 2 and 4. It can be a little unnerving at first, but you can become adept very quickly at writing dynamic SQL for metadata purposes.


Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index Lets say for example that I have a table structured this way:

ID1 int
ID2 int
ID3 int
DTM datetime

I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a combination of two of them or individually would lead to duplicate rows).

Would it be better performance wise to create a clustered index by ID1, ID2, and ID3? Or create a clustered index on just ID1 and have SQL Server add a uniquifier to the index to make each row unique?

I know that the uniquifier is 4 bytes in size (same for ints) so technically it would save 4 bytes per row if I make the latter index, but I'm not sure how this would affect my queries.

One of the most important considerations for a clustered index in terms of performance is that it be ever increasing (or decreasing) and not something that will be changed (except possibly very rarely).  The clustered index represents the physical order of your table.  If you are constantly inserting into the middle of the index, or modifying the values of your clustered index then you will get bad page splits where SQL has to move part of the data from one page and move it into another one in order to make room for the new data.  These moves take time and cause fragmentation that degrades performance.

That being said the size of the clustered index, while important, is not your biggest concern.  I did some experiments with using date columns (with a uniqueifier) vs an int column and found that if my queries were date based I still got a big bump in performance.

My suggestion to you is that if ID1, ID2 and ID3 are ever increasing (and rarely change) then use that as your clustered index.  If not, and you still want to enforce uniqueness then either make them a non-clustered primary key or a non-clustered unique key.  If they are not ever increasing then you can consider your date column or just create a surrogate key for the table.  Use an int data type and make it an identity column.

If you do create a surrogate key then you can create a non-clustered index on ID1 to improve the performance on those queries assuming you didn't already create a unique index for it.  If you frequently need to return, say ID2 and DTM then you could INCLUDE those in your index to additionally improve your performance.


Whether or not to create separate tables for different product types? I'm in the process of designing a database and I'm having second thoughts about my initial design decisions...

Product types are as follows... Models, parts, replacement part kits and options.

Option A (first design):
I planned on having separate tables for the above product types. I'd say about 75% of the fields would be the same in each table.

I created each product type as separate tables because of the associations I need to create between them. For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on...

Option B:
Instead of having separate tables I could create a table called Product that encompasses model, part, replacement part kits and options. I could have one field called type to differentiate between model, options, etc. I suppose a down side is several fields would never be used (left null) for certain product types. I'm guessing this is where "not best practices" would come into play..

Option B would greatly reduce the complexity of the db design. I also wouldn't have to worry about referencing a bunch of tables when pulling out data for queries...

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).
First, why not 'Option B':
For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.
For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)
Option C

Create a parent table that holds only the columns that all types share
Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.


.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314) I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings.

The error I get is:

  An error occurred in the Microsoft .NET Framework while trying to load assembly id 65540. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly 'clrsplit, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

  System.IO.FileLoadException:      at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)     at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)     at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)     at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)     at System.Reflection.Assembly.Load(String assemblyString) [SQLSTATE 42000] (Error 10314).

Error: 10314, Severity: 16, State: 11.

The database TRUSTWORTHY bit is set:

name             is_trustworthy_on
msdb             1
SimplusStaging   1

PERMISSION_SET is set to UNSAFE. Assembly is marked UNSAFE because those were the vendor installation instructions. Have tried dropping the Assembly and related T-SQL objects and re-creating them. No change. The DBO user is the SA login. And, this is the only CLR assembly we use for this database/server.

This is a new install using detach/attach. Did the KB2919355 install because otherwise SQL Server 2016 wouldn't install.

I would love to use the new STRING_SPLIT function in 2016 except that the application is supported by a third-party and they built everything for 2008 R2. Wanted to take advantage of some of the new features in 2016 where "it just runs faster".

The SID of the db owner is the same for both the original install and the new install. Both of the following queries return 0x01 when run in the context of the problem database:

SELECT [sid] FROM sys.database_principals WHERE [name] = N'dbo';
SELECT [owner_sid] FROM sys.databases WHERE [database_id] = DB_ID();

Do I need to recompile the CLR assembly for a new .Net Framework or should it just work?

Doing this same migration to SQL 2012 and 2014 on Windows 2008 R2 worked without issue.

An error in the form of:

  Msg 10314, Level 16, State 11, Line 1
  An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

The very much preferred approach

Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.

If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html
Create an Asymmetric Key in [master] from the DLL of that Assembly
Create a Login from that Asymmetric Key
Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
Create the Assembly in whichever Database(s) it should exist in
Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.

The NOT preferred approach

Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.

If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
Try to avoid this approach, if at all possible :-)


Postgres: Trigger always uses the same value for now() I would like to create a trigger that keeps a last-updated time on each row. It would fire on CREATE and UPDATE, and store the current time into a column. I'm following basically the exact recipe from here: https://www.postgresql.org/docs/9.2/plpgsql-trigger.html

When I run it, the value in the column is always the same, no matter how many times I update the field. It's as if 'now()' was only evaluated once, and the value was cached.

Here is my code:

CREATE FUNCTION alex_trigger() RETURNS trigger AS $alex_trigger$
    BEGIN
        NEW.v := now();
        RETURN NEW;
    END;
$alex_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER alex_trigger BEFORE INSERT OR UPDATE ON alex
    FOR EACH ROW EXECUTE PROCEDURE alex_trigger();

I must be doing something dumb. Any ideas?

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client).
clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.


Copy a database to a new schema in an existing database Just curious if there is a way to create a new schema that is based on an existing database.
Assuming the existing database has dbo schema only.
I will be taking an entire database and moving it to an existing database as a new schema within that database.

For copy all tables into new schema in current database :

SELECT 'ALTER SCHEMA New Schema TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];' as 'queries'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'Old Schema' -- 'dbo' if that is schema name of tables
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Running the above script will generate the SQL commands needed to transfer objects from one schema to another inside result view table in bottom of page.

Something like this :

ALTER SCHEMA Inf TRANSFER [dbo].[t_Link];
ALTER SCHEMA Inf TRANSFER [dbo].[t_News];

Now click on column header ( queries ) and right click, copy all rows, then paste in query windows and Execute.

Now all of objects are in your new schema.


postgres sequence problems, manually created pids, and proper sequence resetting Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created based on the last inserted record's pid, and resetting the sequence using this code:

SELECT pg_catalog.setval(pg_get_serial_sequence('mytable', 'pid'),
       (SELECT MAX(pid)+1 FROM mytable) );

I am experienced enough to know that we should be able to rely on the database to create our ids, and that creating one's own PIDs based on last max is not a 'best practice' nor safe for all scenarios, though it generally has worked for our daily use.

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

While I am reading up, I would like to know if someone point me in the right direction today to get the sequence back up, and working flawlessly, on an existing table - and have it work even though code is in place that will sometimes insert a pid from code, based on last max.  (Longer term, of course, I am fully aware it is best all such code will is dropped - but is there a way to work around that code for now?)

Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!

Finally - and this is one disturbing fact that got me here - after resetting the sequence in pg admin I see two pids in the table in pg admin 3 and 4, which also shows in the create script.  The 'second' PID column does not show when doing \d in psql, which is good - but I thought it might be relevant.

UPDATE - on that last point, the ghost duplicate PIDs for a table in pg admin were caused because there were two sequences for that same column/table combo, the second created at some point to try and fix broken sequences over time (eg mytable_pid_seq and mytable_pid_seq1).  Not sure why/how this was allowed to happen in the database.

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

If you're restoring from pg_dump there is no way that can be the problem. Take for instance,

CREATE TABLE foo (id serial PRIMARY KEY);
INSERT INTO foo DEFAULT VALUES;

This generates a table like this,

                         Table "public.foo"
 Column |  Type   |                    Modifiers
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

You can see here that the type of id is just int and all the sequence does is default it to nextval. Using pg_dump, this will generate a dump like this (removing comments and permissions)

CREATE TABLE foo (
    id integer NOT NULL
);

CREATE SEQUENCE foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);

COPY foo (id) FROM stdin;
1
\.

SELECT pg_catalog.setval('foo_id_seq', 1, true);

ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

That a lot of stuff, but it's the safest way to restore even a simple table. So notice here the call to pg_catalog.setval

SELECT pg_catalog.setval('foo_id_seq', 1, true);

After you merge in other pkids that are potentially higher, you'll have to call that with the new highest value.

SELECT pg_catalog.setval(
  'foo_id_seq',
  (SELECT max(id) FROM foo),
  true
);

Then viola, you're back to working. They're not broken at all.

Important note, this is for serial types. Pg 10 will change this interface if you're using Identity Columns and it will instead be a standardized ALTER TABLE command to change the current value.


Why aren't unsigned integer types available in the top database platforms? Databases are usually very customizable with varying data types and custom lengths.

It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither PostgreSQL and MS SQL Server. MySQL and Oracle seem to.

This seems like a glaring omission on their part - the next best perfomant option being a long/bigint, (8 byte integer), but could be completely unneccessary! Does anyone know why they would choose to not include native unsigned int support?

Microsoft's Jim Hogg has responded to this issue with the following:

There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.
On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.
I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.

Source: Microsoft Connect
I would add significantly to the pro list, and reiterate that their SQL engine is already doing FAR more complex things than this, and so their team can handle the added complexity. While I don't agree with their summation, this is Why SQL Server doesn't support unsigned types.
The Connect link was originally posted by Martin Smith in the question comments.


How to improve the performance of this select query? I have two tables: TABLE1 and TABLE2.

And I am running this select query to fetch the data. It takes long time to run:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')
  AND TABLE1.COL2= 1800
  AND TABLE2.COL1 = 10519198
  AND TABLE1.COL3>= 100
  AND TABLE1.COL4= TABLE2.COL2
  AND TABLE2.COL3=('TEXT')

How could I improve the performance of this select query? Does too many AND affect my performance?

Based on your comments, here is a maybe faster version:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')
  AND TABLE1.COL2=1800
  AND TABLE1.COL3>=100
  AND TABLE2.COL1=10519198
  AND TABLE2.COL3=('TEXT')

And you need to create a tree-index on TABLE1(COL2, COL1, COL3). Beware: hash-based index weren't o.k, because of your condition of TABLE1.COL3>=100 is an interval-query, which is impossible to be fastened by hash-based indices! This is also the cause, why must be COL3 the last element of the TABLE1(COL2, COL1, COL3) index.

Another index which can make things better were a hash-based index on TABLE2(COL1,COL3).

Further indices which will probably help: TABLE1(COL4) and TABLE2(COL2) (both).

None of the indices should be unique.


POSTGRESQL: Refresh the materialized view which effect when there are change based on columns I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works fine. Can someone help me to make a function so that the trigger only refresh the affected matview based on changes in the column?
for example table_1 has columns a and b. table 2 has columns c and d.
matview_1 takes columns a and c, matview_2 takes columns a and d, matview_3 takes columns b and c. I need if there is a changes in a row of table 1 column a, only matview_1 and matview_2 is refresh by the store procedure trigger. but if there are changes in row of table_1 column b, only matview_3 is refresh.

table_1
a  |  b
"""""""
   |

table_2
c  |  d
"""""""
   |

matview_1
a  |  c
"""""""
   |

matview_2
a  |  d
"""""""
   |

matview_3
b  |  c
"""""""
   |

Here my function trigger for all table:
CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger AS
$$
DECLARE
rec RECORD;
BEGIN
    FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')
    LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname;
    END LOOP;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

Here my create trigger:
CREATE TRIGGER trigger_tabel AFTER
INSERT OR UPDATE OR DELETE ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();

The most efficient solution is to not fire the trigger in that case:

CREATE TRIGGER trigger_tabel
AFTER INSERT
   OR UPDATE OF (column_a) --<< only fire the trigger if column_a is updated
   OR DELETE
ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();

If you have multiple columns that should trigger the refresh, you can separate them using commas: OR UPDATE OF (column_a, column_c, column_d)

A less efficient solution is to check if the column changed inside the trigger function:

CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger A
$$
DECLARE
rec RECORD;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'DELETE' OR
       -- add all columns that need to be checked for changes
       (old.column_b IS DISTINCT FROM new.column_b)
    THEN
      FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')
      LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname;
      END LOOP;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

If you are using the same trigger function for all tables, then obviously this only works if all tables have the same columns that you want to check. But not firing the trigger to begin with will be much more efficient and should be your preferred solution.


"where" on json field causing Arithmetic overflow error I have a table:

when I select all from the table:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x FROM
(VALUES
(2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
(3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
(4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
(5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')
)
[AmirTestTable](Id,LastReadings )

I get

+-----+
|  x  |
+-----+
|  12 |
|  32 |
|  22 |
| 123 |
+-----+

However when i try to add a where clause:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x
 FROM
(VALUES
  (2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
  (3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
  (4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
  (5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')
)
[AmirTestTable](Id,LastReadings )
where JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') > 12.0

I get the error:

  Arithmetic overflow error converting nvarchar to data type numeric.

If I change the 123 value to 99 the query works, or if i change the where clause to > 12. It also works if i cast it explicitly to a number.

I don't understand why it doesn't work directly though.

This isn't anything to do with JSON specifically. You see the same with

SELECT 1
WHERE  '123' > 12.0;

JSON_VALUE has a return type of nvarchar(4000).

The literal 12.0 has a datatype of numeric(3,1).

As numeric/decimal has a higher datatype precedence than nvarchar the result of the function call is cast to numeric(3,1)

You can use an explicit cast to control the scale and precision used yourself.

e.g.

 CAST(JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') AS NUMERIC(4,1)) > 12.0

Would work fine for your example data.


Clustered columnstore index performance SQL Server 2014 I'm setting up an OLAP database using SQL Server 2014. The core fact table has about 40,000,000 rows, 225 columns with an average row size of 181 bytes. I've been toying around with the clustered columnstore index a bit without much luck. In general I find query performance more than 4 times slower with the new technology.

One particular example - selecting a single row using an int32 primary key now takes 12 seconds ... this is a sub-second operation on a rowstore table (of course it had a unique index on the PK, which is not allowed in conjunction with the clustered columnstore index).

I'm trying to figure out what I'm doing wrong - from the MS docs it sounds like this is ideal technology for this task; maybe I'm missing something.

I'm running SQL 2014 Enterprise on Windows 8.1 64-bit with 128GB RAM and SSD for data storage.  The data is read only for this app.

If you can post the specific data and queries you are using, that is probably the only way we can help answer the question in the context of your specific case.  You can use a script that generates anonymous data in roughly the same scale as your real example.

However, I went ahead and created a similar type of script myself.  For the sake of simplicity, I am using fewer than 225 columns.  But I am using the same number of rows and random data (which is unfavorable for columnstore) and I saw results that are much different than yours.  So my initial thought is that yes, you do have some sort of problem with either your configuration or your test queries.

A few of the key takeaways:

Columnstore has dramatically faster performance than rowstore for simple aggregations across all rows in a column
If loaded carefully, columnstore can perform surprisingly well for singleton seeks.  There is an I/O hit, but with a warm cache performance was very good.  But not as good as rowstore for this use case, of course.
If you need to be able to perform both singleton seeks and large aggregation queries, you might consider using a non-clustered columnstore index on top of a standard b-tree table.
You mention that you have 225 columns, but an average row is just 181 bytes.  This seems a little unusual; is your table mostly BIT columns?  That might be something to look into further.  I did see very good compression ratios on a simple BIT column columnstore (over 99%), but it may be the case that much of that is due to the absence of row overhead and this advantage would disappear with many BIT columns on a single row.
If you want to learn (a lot) more about columnstore, Niko's 66-part (and counting) blog series has been the most valuable reference that I've come across.

And now on to the details:

Create rowstore data set

Nothing too exciting here; we create 40MM rows of pseudo-random data.

SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
--  Jun  9 2015 12:06:16
--  Copyright (c) Microsoft Corporation
--  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO

-- Create a rowstore table with 40MM rows of pseudorandom data
;WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
SELECT TOP 40000000 ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0) AS id
    , ISNULL((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1, 0) AS col1
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col2
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col3
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col4
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col5
INTO dbo.test_row
FROM E8
GO
ALTER TABLE test_row
ADD CONSTRAINT PK_test_row PRIMARY KEY (id)
GO

Create columnstore data set

Let's create the same data set as a CLUSTERED COLUMNSTORE, using the techniques described to load data for better segment elimination on Niko's blog.

-- Create a columnstore table with the same 40MM rows
-- The data is first ordered by id and then a single thread
-- use to build the columnstore for optimal segment elimination
SELECT *
INTO dbo.test_column
FROM dbo.test_row
GO
CREATE CLUSTERED INDEX cs_test_column
ON dbo.test_column (id)
GO
CREATE CLUSTERED COLUMNSTORE INDEX cs_test_column
ON dbo.test_column WITH (DROP_EXISTING = ON, MAXDOP = 1)
GO

Size comparison

Because we are loading random data, columnstore achieves only a modest reduction in table size.  If the data was not as random, the columnstore compression would dramatically decrease the size of the columnstore index.  This particular test case is actually quite unfavorable for columnstore, but it's still nice to see that we get a little bit of compression.

-- Check the sizes of the two tables
SELECT t.name, ps.row_count, (ps.reserved_page_count*8.0) / (1024.0) AS sizeMb
FROM sys.tables t WITH (NOLOCK)
JOIN sys.dm_db_partition_stats ps WITH (NOLOCK)
    ON ps.object_id = t.object_id
WHERE t.name IN ('test_row','test_column')
--name          row_count   sizeMb
--test_row      40000000    2060.6328125
--test_column   40000000    1352.2734375
GO

Performance comparison

In the following two test cases, I try two very different use cases.

The first is the singleton seek mentioned in your question.  As commenters point out, this is not at all the use case for columnstore.  Because an entire segment has to be read for each column, we see a much greater number of reads and slower performance from a cold cache (0ms rowstore vs. 273ms columnstore).  However, columnstore is down to 2ms with a warm cache; that's actually quite an impressive result given that there is no b-tree to seek into!

In the second test, we compute an aggregate for two columns across all rows.  This is more along the lines of what columnstore is designed for, and we can see that columnstore has fewer reads (due to compression and not needing to access all columns) and dramatically faster performance (primarily due to batch mode execution).  From a cold cache, columnstore executes in 4s vs 15s for rowstore.  With a warm cache, the difference is a full order of magnitude at 282ms vs 2.8s.

SET STATISTICS TIME, IO ON
GO

-- Clear cache; don't do this in production!
-- I ran this statement between each set of trials to get a fresh read
--CHECKPOINT
--DBCC DROPCLEANBUFFERS
GO

-- Trial 1: CPU time = 0 ms,  elapsed time = 0 ms.
    -- logical reads 4, physical reads 4, read-ahead reads 0
-- Trial 2: CPU time = 0 ms,  elapsed time = 0 ms
    -- logical reads 4, physical reads 0, read-ahead reads 0
SELECT *
FROM dbo.test_row
WHERE id = 12345678
GO 2
-- Trial 1: CPU time = 15 ms,  elapsed time = 273 ms..
    -- lob logical reads 9101, lob physical reads 1, lob read-ahead reads 25756
-- Trial 2: CPU time = 0 ms,  elapsed time = 2 ms.
    -- lob logical reads 9101, lob physical reads 0, lob read-ahead reads 0
SELECT *
FROM dbo.test_column
WHERE id = 12345678
GO 2

-- Trial 1: CPU time = 8441 ms,  elapsed time = 14985 ms.
    -- logical reads 264733, physical reads 3, read-ahead reads 263720
-- Trial 2: CPU time = 9733 ms,  elapsed time = 2776 ms.
    -- logical reads 264883, physical reads 0, read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_row
GO 2
-- Trial 1: CPU time = 1233 ms,  elapsed time = 3992 ms.
    -- lob logical reads 207778, lob physical reads 1, lob read-ahead reads 341196
-- Trial 2: CPU time = 1030 ms,  elapsed time = 282 ms.
    -- lob logical reads 207778, lob physical reads 0, lob read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_column
GO 2


Update can't see records just committed by another connection I am performing a transaction using a connection. Whenever a second connection immediately tries to update the rows just inserted, the rows are not found. Even if the transaction was committed before issuing an UPDATE.

I have the mysql query log which describes the scenario.

The last statement fails with 0 rows affected (UPDATE refresh_token...) :

2018-08-03T13:31:24.829038Z  1150 Query START TRANSACTION
2018-08-03T13:31:24.830026Z  1150 Prepare   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.830493Z  1150 Execute   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.831345Z  1150 Close stmt
2018-08-03T13:31:24.833228Z  1150 Prepare   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.833666Z  1150 Execute   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.834356Z  1150 Close stmt
2018-08-03T13:31:24.834477Z  1150 Prepare   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835155Z  1150 Execute   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835621Z  1150 Close stmt
2018-08-03T13:31:24.835747Z  1150 Query COMMIT
2018-08-03T13:31:24.840374Z  1150 Prepare   UPDATE refresh_token SET accessed = ? WHERE token = ?
2018-08-03T13:31:24.840799Z  1150 Execute   UPDATE refresh_token SET accessed = '<redacted>' WHERE token = '<redacted>'
2018-08-03T13:31:24.843346Z  1150 Close stmt

If I wait 500ms before issuing the UPDATE refresh_token SET..., the record is found.

Here is the transaction isolation level:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
'REPEATABLE-READ', 'REPEATABLE-READ'

Without knowing more (like the query execution plan, number of rows affected, indexing, etc.) it will be hard to provide a concise answer. But REPEATABLE_READ raises a flag, at least for me.

Transaction isolation levels are a matter of give-and-take. None of them are "the best"; rather, you choose one that's a "best fit". Each one comes with a cost. If you want high consistency, you get less concurrency.

I like this post:
https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

This excerpt from the post sums it up nicely:

  In REPEATABLE READ every lock acquired during a transaction is held
  for the duration of the transaction.

  In READ COMMITTED the locks that did not match the scan are released
  after the STATEMENT completes.


How to know if mysql server is using sha_256 or caching_sha_256 authentication plugin? Seems like I am using an older version on Mysql Server which uses sha_256 plugin for Authentication (which is now deprecated) .Because of this, I am unable to connect to my server  with third party softwares like beekeper studio.
But I am able to connect from the terminal itself. So I went through the Mysql manual . It shows how to change the default authentication plugin , but before changing it,  I just want to know what is my default authentication plugin.  How to do that?

When you have a connection, (ref SHOW CREATE USER),
show create user {user}

will how you the plugin used for any specific user.
SHOW GLOBAL VARIABLES LIKE 'default_authentication_plugin'

will show which authentication is used for created users if none is specified. ref


timeline 2 of the primary does not match recovery target timeline 1 I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this:

  .-----.                  .--------.
  |     |           W      |   DB   |
  | APP |-----+----------->| MASTER |----.
  |     |     |            |        |    |
  `-----`     |            `--------`    | STREAMING REPLICATION
              |            .--------.    |
              |     R      |   DB   |    |
              +----------->| SLAVE1 |<---+
              |            |        |    |
              |            `--------`    |
              |            .--------.    |
              |     R      |   DB   |    |
              `----------->| SLAVE2 |<---`
                           |        |
                           `--------`
after failover:

  .-----.                  .--------.
  |     |                  |   DB   |
  | APP |-----+----        | MASTER |
  |     |     |            |  FAIL  |
  `-----`     |            `--------`
              |            .--------.
              |    W/R     |   DB   |
              +----------->| SLAVE1 |---.
              |            |        |   |
              |            `--------`   |
              |            .--------.   | STREAMING REPLICATION
              |     R      |   DB   |   |
              `----------->| SLAVE2 |<--`
                           |        |
                           `--------`

Follow the post for HA PostgreSQL Cluster by Streaming Replication + pgpool-II I am created 4 virtual machines (vm) with vagrant. One Master (M), two Slaves (S1 and S2) and other for pgpool (APP). When I do some queries in APP all work fine. When I stop the master, S1 is promoted to M and the S2 now have a new master. But this get me a problem. I don't know what this error mean and I am lost thinking how to fix it:

2014-08-18 17:04:56 UTC FATAL:  timeline 2 of the primary does not match recovery target timeline 1

I am using:

postgres 9.1
pgpool 3.1.1-1

UPDATE

This error is emitted by S2 when S1 is promoted to Master.

UPDATE 2

Nodes IP Address:

M  - 192.168.1.10
S1 - 192.168.1.11
S2 - 192.168.1.12
APP- 192.168.1.13

This is my recovery.conf file configuration for S1 and S2.

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=replicator'
trigger_file = '/tmp/postgresql.trigger.5432'

UPDATE 3

Here is my backends into the /etc/pgpool2/pgpool.conf file:

...
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 0
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '192.168.1.12'
backend_port2 = 5432
backend_weight2 = 0
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------

failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'

And it is failover.sh file:

#!/bin/sh
FALLING_NODE=$1
OLD_MASTER=$2
NEW_MASTER=$3
SLAVE1='slave1'
SLAVE2='slave2'
if test $FALLING_NODE -eq 0
then
ssh -T $SLAVE1 touch /tmp/postgresql.trigger.5432
ssh -T $SLAVE1 "while test ! -f /var/lib/postgresql/9.1/main/recovery.done; $
ssh -T $SLAVE2 "sed -i 's/192.168.1.10/192.168.1.11/' /var/lib/postgresql/9.$
ssh -T $SLAVE2 /etc/init.d/postgresql restart
/usr/sbin/pcp_attach_node 10 localhost 9898 pgpool pgpool 2
fi

The issue is covered in this detailed blog.

In short, the master and the standby servers must all have archive_mode = on and an archive_command, until 9.3 which removes this requirement.


Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5? I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$.

$do$
BEGIN
    FOR yyyy in 2012..2016 THEN
        EXECUTE $$COPY table$$||yyyy||$$ FROM 'E:\data\file$$||yyyy||$$.csv DELIMITER ',' CSV;$$;
    END LOOP;
END;
$do$ LANGUAGE plpgsql

This entire process should take about 15 hours and I'm hoping that all the imports won't be rolled back if there's an import error at some point.

IIRC COMMIT doesn't work within stored functions bc the entire function is treated as a single transaction.

From the documentation for $do$

  The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

I'm assuming this means that the entire $do$ is one transaction, and so commits within the block won't work. Am I correct?

No,

You can't control a transaction inside a plpgsql function (or anonymous block).

The only option that you have its creating a transaction outside the block, eg:

BEGIN;

DO $$
  -- function stuff

  -- but if you use a exception, you will force a rollback
  RAISE EXCEPTION 'message';
$$ LANGUAGE 'plpgsql';

COMMIT; -- OR ROLLBACK

BTW, DO BLOCKS have the same effect that functions who returns void.

Please, see more at the doc:

https://www.postgresql.org/docs/current/static/sql-do.html


Unable to connect to Oracle from SQL Developer below is the error

  Status : Failure -Io exception: The Network Adapter could not
  establish the connection

It's connecting from my system but unable to connect from other systems on the network.
ping command is working fine and tnsping is also working. any suggestion and help is very helpful.

problem resolved thanks for the support..It wasn't the database fault...It was an network issue....

This seems to be a hostname resolution problem. Please try to connect to the IP address instead of the hostname.

If this is not the solution could you please post the output of the following command:

lsnrctl status

EDIT 1:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)(KEY = EXTPROC1521)
    )
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = 192.168.2.64)(PORT = 1521)
    )
  )
)

EDIT 2:

It turned out to be an firewall problem. See comments below.


MySQL: Does access to table data not in an index via a join causes a file-sort when ordered The inclusion of category.name in the SELECT, causes the query to perform a
Using index; Using temporary; Using filesort, and also the rows scanned are not affected by the LIMIT 0,1.

CREATE TABLE `item` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `category` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `feature` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` INT(10) UNSIGNED DEFAULT NULL,
  `category_id` INT(10) UNSIGNED DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tbl_feature_id_item_id_foreign` (`item_id`),
  KEY `tbl_feature_id_category_id_foreign` (`category_id`),
  CONSTRAINT `tbl_feature_id_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`),
  CONSTRAINT `tbl_feature_id_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `item` (`id`) VALUES (1),(2),(3),(4),(5);

INSERT INTO `category` (`id`, `name`, `created_at`, `updated_at`)
  VALUES
    (1, 'a', '2016-12-02 22:49:46', NULL),
    (2, 'b', '2016-12-02 22:49:48', NULL)
;

INSERT INTO `feature` (`id`, `item_id`, `category_id`, `start_date`, `created_at`, `updated_at`)
VALUES
(1,  1, 1, '2016-12-01', NOW(), NOW()),
(2,  1, 2, '2016-12-02', NOW(), NOW()),
(3,  2, 1, '2016-12-01', NOW(), NOW()),
(4,  2, 2, '2016-12-02', NOW(), NOW()),
(5,  3, 1, '2016-12-01', NOW(), NOW()),
(6,  3, 2, '2016-12-02', NOW(), NOW()),
(7,  4, 1, '2016-12-01', NOW(), NOW()),
(8,  4, 2, '2016-12-02', NOW(), NOW()),
(9,  5, 1, '2016-12-01', NOW(), NOW()),
(10, 5, 2, '2016-12-02', NOW(), NOW())
;

EXPLAIN EXTENDED
SELECT
    item.id
  , feature.id
  , category.id
  , category.name -- Is the cause of the temporary table; file-sort;
FROM
  item
LEFT JOIN feature ON (
  feature.item_id = item.id
  AND feature.start_date = (
    SELECT
      MAX(start_date) AS start_date
    FROM
      feature
    WHERE
      feature.item_id = item.id
  )
)
LEFT JOIN category ON (
  category.id = feature.category_id
)
ORDER BY item.id DESC -- or ASC
LIMIT 0, 1 -- Is ignored in the table scan
;

MySQL 5.7.16

I believe it may be because of the need to access the source table after the sort is done. because the file-sort is performed in Mode 2

http://s.petrunia.net/blog/?p=24

Mode 2: sort  pairs and produce a sequence of rowids which one can use to get source table’s rows in the required order (but this will be essentially hit the table in random order and is not very fast)

Adding an index to category.name does resolve the issue. Is this is because category.name is now part of the index?

Can anybody confirm, and possibly provide a better solution than adding the index, as in reality, it does not resolve the issue as the point in time construct is repeated for multiple other features i.e. feature_a, feature_b.

Why is Using index; Using temporary; Using filesort performed?

Why does the LIMIT 0,1 not affect the rows scanned?

The temporary table is caused because you have a dependent subquery in your join to calculate the MAX(startdate).

I was able to improve the EXPLAIN by adding an index:

ALTER TABLE feature
  ADD INDEX item_startdate_category (item_id, start_date, category_id);

And changing the query to avoid the dependent subquery:

EXPLAIN EXTENDED SELECT i.id, f1.id, c.id
FROM item i
LEFT JOIN feature f1 ON (f1.item_id = i.id)
LEFT JOIN feature f2 ON (f1.item_id=f2.item_id AND f1.start_date < f2.start_date)
LEFT JOIN category c ON (c.id = f1.category_id )
WHERE f2.item_id IS NULL
ORDER BY i.id DESC LIMIT 0, 1;

This join between f1 and f2 is meant to ensure that f1 has the largest start_date for its respective item_id. If the LEFT JOIN to f2 finds no other row with the same item_id and a greater start_date, then f1 must have the greatest start_date.

Here's the EXPLAIN I got:

+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys                                          | key                     | key_len | ref                 | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+
|  1 | SIMPLE      | i     | NULL       | index  | NULL                                                   | PRIMARY                 | 4       | NULL                |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | f1    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.i.id           |    2 |   100.00 | Using index              |
|  1 | SIMPLE      | f2    | NULL       | ref    | tbl_feature_id_item_id_foreign,item_startdate_category | item_startdate_category | 5       | test.f1.item_id     |    2 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                                                | PRIMARY                 | 4       | test.f1.category_id |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+--------+--------------------------------------------------------+-------------------------+---------+---------------------+------+----------+--------------------------+

I tested on MySQL 8.0.0-dmr on my Macbook.


How do I calculate the average weight of riders by competition? I have an assignment where I need to calculate the average Weight of Riders by Competition. The assignment has a lot of different parts but I got stuck on this one so please help my out if you can.

I have the following tables:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

CREATE TABLE Participants(
    competition VARCHAR(30) NOT NULL,
    rider VARCHAR(30) NOT NULL,
    PRIMARY KEY(competition, rider),
    FOREIGN KEY(competition) REFERENCES Competition(name),
    FOREIGN KEY(rider) REFERENCES Rider(name)
) ENGINE=innodb;

CREATE TABLE Competition(
    name VARCHAR(30),
    PRIMARY KEY(name)
) ENGINE=innodb;

With the following data:

--Riders
Name : Weight
----   ------
Alan : 55
David : 60
Peter : 65
Stan : 60

--Competition
Name
-----
Derby
York

--Participants
Competition : Rider
-----------   -----
Derby : Alan
Derby : David
York : Alan
York : Peter
York : Stan

So the results I should get from my SQL query for the different competitions should be:

Derby: 57,5 (average of Alan and David)
York: 60 (average of Alan, Peter and Stan)

I've tried multiple queries. At the moment my best is:

SELECT Competition.name, AVG(Rider.weight)
FROM Rider, Compeition, Participants
GROUP BY Competition.name;

How should I tackle this SQL query?

You need to join the rider and participants tables together using the common column (name->rider), group all of the participants for a given competition together, and calculate the average, like so:

select competition, avg(weight) as average_weight
from participants
join rider on rider.name = participants.rider
group by competition
;

Fiddle here.


Moving Oracle / Oracle datafiles to different file system on Linux We have Oracle installed on /root, which is now running out of space due to increasing data in Oracle database. On the same machine we have another file system with free space.

I want to know the mechanism by which we can move Oracle / Oracle data files to this new file system and continue to use the same data files.

Appreciate any formal steps reference.

It is Oracle 12c on RHEL

The simplest way, in 12c, you can move datafiles online, e.g:

alter database move datafile '/current_fs/system01.dbf' to '/another_fs/system01.dbf';


Why Azure SQL Server table used space is too high? I have two tables in my SQLServer database (azure managed), and the structure of two tables as below (for easy reference renamed them)
dbo.TABLE1
CREATE TABLE [dbo].[TABLE1] (
  [ID]             VARCHAR (50)  NOT NULL,  -- PK index
  [S_NUMBER]       VARCHAR (50)  NOT NULL,
  [MAKE]           VARCHAR (12)  NULL,
  [MODEL]          VARCHAR (40)  NULL,
  [C_ID]          VARCHAR (50)  NOT NULL,
  [C_CODE]         REAL          NULL,
  [C_DESCRIPTION]  VARCHAR (50)  NULL,
  [S_CODE]         REAL          NULL,
  [S_DESCRIPTION]  VARCHAR (50)  NULL,
  [LS_TIME]       DATETIME2 (7) NOT NULL,
  [LE_TIME]        DATETIME2 (7) NOT NULL,
  [YEAR]           DATE          NULL,
  [MONTH]          DATE          NULL,
  [WEEK]           DATE          NULL,
  [DAY]            DATE          NULL,
  [HOUR]           DATETIME2 (7) NULL,
  [WEEK_DAY]       VARCHAR (12)  NULL,
  [F_USED]         REAL          NULL,
  [F_USED_UOM]     VARCHAR (12)  NULL,
  [DIST_TRAVE]     REAL          NULL,
  [DIST_TRAV_UOM]  VARCHAR (12)  NULL,
  [DURATION]      REAL          NULL,
  [DURATION_UOM]   VARCHAR (12)  NULL,
  [WEIGHT_MOVED]   REAL          NULL,
  [WEIGHT_STORED]  REAL          NULL,
  [WEIGHT_UOM]     VARCHAR (12)  NULL,
  [CREATED_TIME]   DATETIME      NULL,
  [MODIFIED_TIME]  DATETIME      NULL,
  [OPER_ID]        VARCHAR (12)  NULL,
  [OPER_TYPE]      VARCHAR (12)  NULL,
  [START_TIME]     DATETIME2 (7) NULL,
  [END_TIME]       DATETIME2 (7) NULL,
  [UTC_DAY]        DATE          NULL,
  [UTC_WEEK]       DATE          NULL,
  [UTC_MONTH]      DATE          NULL,
  [UTC_YEAR]       SMALLINT      NULL,
  [UTC_HOUR]       TINYINT       NULL,
  [LOCAL_HOUR]     TINYINT       NULL,
  [EFFICIENCY]     DECIMAL (5)   NULL,
  [JS_ID]          BIGINT        NOT NULL,
  [L_ZONE]         NVARCHAR (50) NULL,
  [D_ZONE]         NVARCHAR (50) NULL,
  [SHIFT_TYPE]     NVARCHAR (12) NULL,
  [SHIFT_DATE]     DATE          NULL,
  [D_CATEGORY]     NVARCHAR (50) NULL,
  [REC_CATEGORY]   NVARCHAR (12) NULL,
  [LSERIAL_NUMBER] VARCHAR (50)  NULL,
  [GPS_TIME]       DATETIME      NULL,
  [GPS_STATUS]     VARCHAR (12)  NULL,
  [A_ID]           VARCHAR (50)  NULL,
  [E_ID]           VARCHAR (50)  NULL,
  [TAG_GUID]       VARCHAR (64)  NULL,
  [LZ_ID]          VARCHAR (64)  NULL,
  [DZ_ID]          VARCHAR (64)  NULL,
  [LZ_VERSION]     INT           NULL,
  [DZ_VERSION]     INT           NULL,
  [Z_ID]           BIGINT        NULL,
  [SHIFT_ID]       NVARCHAR (64) NULL,
  [LI_TIME]        DATETIME2 (3) CONSTRAINT [S_CREATED_TIMESTAMP] DEFAULT (sysdatetime()) NULL,
  [PC_ID] INT NOT NULL DEFAULT 0,
  [L_MAKE]               VARCHAR (32)    NULL,
  [TV_MOVED]        DECIMAL (18, 2) NULL
);

dbo.TABLE2
CREATE TABLE [dbo].[TABLE2](
  [ID] NVARCHAR(32) NOT NULL,  -- PK index
  [C_ID] NVARCHAR(64),
  [DURATION] DECIMAL(18,2),
  [P_COUNT] BIGINT,
  [LS_TIME] DATETIME,
  [LE_TIME] DATETIME,
  [S_AGG_ID] NVARCHAR(32)   -- FK index
)

dbo.TABLE2 is subset of dbo.TABLE1 with only 5 columns, and the row count is same. We capture only 5 columns from dbo.TABLE1 and insert in dbo.TABLE2. But the size(space used) of dbo.TABLE2 is almost 3 times the dbo.TABLE1.
Indexes: Apart from PK, FK indexes we have one additional index on dbo.TABLE1 with include columns [JS_ID, MAKE, S_NUMBER, TAG_GUID, LE_TIME]
Why the space used is too high for smaller table even though row count is same as its source table?

Using bigger data types
These columns in dbo.Table1 are varchar(50):

[ID]
[C_ID]

In dbo.Table2, they are nvarchar(32) and nvarchar(64).
The added "n" makes each character take up twice as much space as the varchar version.  It's possible the same thing is going on with the FK column and P_COUNT, but I don't see those defined on the original table, so I can't confirm.
There are a couple of datetime2(7) columns changed to datetime, but these have the same storage cost, so that shouldn't make a difference.
If you get your data types back to what they were in the original table, you should see a pretty big reduction in space used.  Note that you will need to REBUILD the clustered index (or just drop and reload the table) after making the data types changes.
Fragmentation?
It's possible that fragmentation is contributing to the additional size of dbo.Table2.  If the size is still significantly different after fixing the data types and recreating the table, you could try loading the table with ORDER BY [ID] OPTION (MAXDOP 1) to limit fragmentation in the target table.


How do I retrieve data in SQL from a previous date in the pass 12 months in a WHERE CLAUSE using the date and time functions? How do I retrieve data in SQL from a previous date in the pass 12 months in a WHERE CLAUSE using the date and time functions?

--ServiceDates in past 12 months
WHERE YEAR(CONVERT(DATE, CLM_FROM_DT)) = 2016.

Both of these should work depending on what you need.

For any date range:

WHERE CLM_FROM_DT BETWEEN '20160331' AND '20170331'

If you are looking for 1 year back from current date:

WHERE CLM_FROM_DT>DATEADD(year,-1,GETDATE())

If you are looking for 1 year back from specific date (2017-3-31) as per your comment:

WHERE CLM_FROM_DT>DATEADD(year,-1, '20170331')


How to search for compound words, and get the word parts returned I need to be able to search for compound words like "menneskerettighedsforkæmperens" (the human rights activist's) and find the words "menneske" (human), "rettighed" (right) and "forkæmper" (activist).

I have all the words (and hundreds of thousands of other words) listed in MySQL, all in separate rows with additional information about each word (like hyphenation, which is what I need the DB to return), but I need to search a lot of words at the same time, which can be painfully slow.

I'm currently using a MySQL database, but willing to switch it out for something better suiting my needs, maybe some kind of NoSQL or elasticsearch. But I haven't been able to find any examples of how to accomplish what I'm looking for in any other type of databases. So if anyone can help me out, I would really appreciate it.

I would look for a library that specializes in splitting compound words.  Search Java, PHP, Perl, etc.  SQL is not likely to be involved.

If you must use a database, then consider this for a piece of the algorithm:

SELECT word FROM words
    WHERE word < 'menneskerettighedsforkæmperens'
    ORDER BY word DESC
    LIMIT 5;

That is likely to return a few compound words that begin with 'menneske' and perhaps 'menneske', itself.

In MySQL, that query will be quite fast (assuming an index on word).  You would need some code in addition to a few SQL calls.


When is Postgres autovacuum executed I am using an older version of Postgres (8.4.20). I know that the autovacuum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database which does not have frequent deletes or updates.

Does it take less time and memory to process autovacuum on such a database, or does it solely depend on the size and number of objects in the database?

First things first — 8.4 is no longer supported, so consider upgrading.

Autovacuum settings are documented.

Let's focus on the settings that affects when autovacuum kicks in. As you might know, this process is responsible for both, vacuuming and analyzing tables.

One of the settings affecting ANALYZE frequency is autovacuum_analyze_threshold. As you can read from the manual, this parameter specifies the minimal amount of rows that should be changed in order for ANALYZE to be triggered. This serves well for the small tables, but on big ones and/or tables with high activity this will lead to a way too frequent analyzing. In order to avoid this, another parameter exists, namely autovacuum_analyze_scale_factor. It specifies the fraction of the table to add to the threshold in order to check whether ANALYZE should kick in.

Say, we have a table with 10,000 rows and 200 of them had changed:

autovacuum_analyze_threshold tells us, that we're over the default 50;
we calculate the fraction based on autovacuum_analyze_scale_factor (which defaults to 0.1) and this gives us 1000 rows;
total calculated threshold therefore is 1050;
as 200 is less then 1050, ANALYZE is not started (we wait for more changes).

For the VACCUM there's another pair of parameters with completely similar behavior: autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor, except default scale for vacuuming is 0.2 or 20%.

Now, as you can guess, the bigger your table becomes, the more time it takes to trigger the autovacuum on it. Therefore, for bigger tables (typically over 1M rows) it is highly recommended to adjust these settings. You can do this on a per-tables basis using ALTER TABLE ... SET ( storage_parameter = ... ) syntax.

It might be tempting to set *_scale_factor to 0 and increase only the threshold for the bigger tables. Still, it better to keep factor to a small, but non-zero value anyway, as for a table with high activity 100,000 rows change might be occurring too frequently, causing unnecessary autovacuuming. See this thread in the pgsql-performance list.


Postgres plpgsql: Is a view better than a select query in a plpgsql function I have a recursive query to the general effect of:

WITH RECURSIVE text1 (selfid, parentid, text) AS
(
SELECT DISTINCT
  text.selfid,
  text.parentid,
  text.text
FROM
  text
WHERE
  text.selfid = entryPoint
UNION ALL
SELECT
  text.selfid,
  text.parentid,
  text.text
FROM
  text,
  text1
WHERE
  text1.selfid = text.parentid
)
SELECT
  *
FROM
  text1
ORDER BY
  text1.selfid;

I'm using this in a plpgsql function where the value entryPoint is the starting point of the recursion and is passed in.

Question:  In the plpgsql, should I:

code the SELECT directly into the function as above?

or

programatically create a VIEW in the pl/sql function, then get my result set from the newly created VIEW?

Some of the queries will get very large result sets, sometimes as many as 500K rows (up to 4GB in the total result set). I wonder if a view would have better performance than a select.

There will be no difference in performance between a view and a straight-up query. View is basically a saved query text. If you expect to call this exact query in multiple places in your code, it may make sense to create a view. Otherwise, put a query in your pgplsql code, it will improve readability of it.


Ubnutu/MySQL - Error "Unable to locate package innotop" I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29.

Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop"

root@server1:~# apt-get install innotop
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package innotop

I checked the Ubuntu package site at http://packages.ubuntu.com/search?keywords=innotop&searchon=names&suite=raring&section=all
and there is no mention, but I know I used to see it there.

root@server1:~# apt-get update

did not help.

I think I might have once had this problem before, and fixed it by installing some prerequisite, but I cant recall anymore.

I checked Google for the error message, but no mention.

It seems to be in the file list for mysql-client-5.5.

sudo apt-get install mysql-client-5.5 should be all you need to do.

Note that the mysql client program is in a separate package, mysql-client-core-5.5, so it's possible to have mysql installed without any of the other useful tools like mysqladmin or mysqldump.


Can't create a view for other user's table despite having select privilege I already executed the following commands:

grant create view to scott;
conn hr/hr;
grant select on employees to scott;

but I conn to scott:

create or replace view company_phone_book as
select employee_id emp_id, last_name||','||first_name name, phone_number, email
from hr.employees;

gives me the error:

  ORA-01031: insufficient privileges.

SCOTT will have the ability to expose the new view to other users, thus indirectly granting data access on HR to other users. SCOTT therefore needs WITH GRANT OPTION on the grant.

GRANT SELECT ON employees TO scott WITH GRANT OPTION;


Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future? I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'BEGIN  MyProcedure();  END;'
   ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)+12/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => FALSE);
COMMIT;
END;
/

Job Number is: 131

When I looked at the status of job 131 it was executing already! THIS_DATE was 23/04/2012 13:16:12

Why did this job execute immediately?

Was it because of the SYS.DBMS_JOB.BROKEN call? I used TOAD to generate this script, and I changed the default broken => TRUE value that TOAD generated to be FALSE instead.

Setting the job to "unbroken" reschedules it:

    SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X
  6     ,what      => 'BEGIN  NULL;  END;'
  7     ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
  8     ,interval  => 'TRUNC(SYSDATE+1)+12/24'
  9     ,no_parse  => FALSE
 10    );
 11    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12
 13  COMMIT;
END;
 14   15  /
Job Number is: 5

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='DDMMYYYY HH24:MI:SS';

Session altered.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   24042012 12:00:00 N

SQL> BEGIN
  2  SYS.DBMS_JOB.BROKEN(job=>5,broken=>FALSE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL>

Once you hit commit, the job scheduler sees the job and executes it:

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL> commit;

Commit complete.

SQL>  select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5 23042012 21:59:06 24042012 12:00:00 N

SQL>

Moral of the story: If creating a new job, don't include a call to DBMS_JOB.BROKEN().


Alternatives to NULL for missing data I am using PostgreSQL and trying to write value constraints.

Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value?

For many fields, I want to ensure that the record is either numeric or recorded as missing, but not NULL.

A common solution is to use -9999 to indicate a missing value. However, this can lead to silent errors (e.g. calculating a mean). Is there a better solution than creating a new type that allows either a value of "Missing" or a number?

Your alternatives are:

use 6th normal form and don't include a row if something doesn't exist.
use nulls
use some other value that will screw up all of your calculations.

My recommendation would be to use nulls. Why don't you want to use nulls?


Update and Insert Statement taking forever I have a database with two columns that is called using the following query:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

In my application it is taking a ridiculous amount of time to call this statement

PredTable = PredTA.GetDataByPartialWord(combo)
            If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
            If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

Indexing does not help because it is only a two column database and is constantly being updated

Is there any other way that I can improve the speed of this statement?

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.)

First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned.

That is the base cost of this query.

Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.

Create a full text index on the Predictions table for the Word column. This can be done through SSMS or by a script. This will populate the keywords table in the FTI catalog tables. Example from: http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX pk_PredictionsID ON dbo.Predictions(PredictionsID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Predictions(Word)
   KEY INDEX pk_PredictionsID
   WITH STOPLIST = OFF;

Then here is a sample script that would help you to then find all the rows with wildcards.

DECLARE @SearchText NVARCHAR(2000)
SET @SearchText = ''

DECLARE @Pattern NVARCHAR(100)
SET @Pattern = '%colat%'

SELECT @SearchText = @SearchText + display_term + ' OR '
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
    object_id('Predictions') )
   WHERE display_term like @Pattern

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM Predictions
WHERE CONTAINS(*, @SearchText)

By building a full text query string that includes all strings containing the pattern joined by the OR operator, it allows Full Text Search to then directly access the rows that contain one (or more) of the words.  This should be a lot faster than scanning the table.


Setting MySQL slow query to less than 1 second I have a heavily loaded LAMP system serving about 40 requests per second and doing about 300 MySQL queries per second. Here is the output of MySQLAdmin status:

Uptime: 5051  Threads: 1  Questions: 1418500  Slow queries: 0  Opens: 456
Flush tables: 1  Open tables: 450  Queries per second avg: 280.835

However, the global status show about 5-10% table lock contention.

So, I want to figure out the queries that are taking longer time.

Is it possible to set the slow query log to less than one second? For my queries, even execution time of 100ms is high.

Just set long_query_time to the value you want. If you want to log queries that take longer than 100ms, you would set it to 0.1.

The ability to set this option to a value less than 1 was added in MySQL 5.1.21.


Query on Container-Pluggable database My Container database (CDBROOT) is showing as read-write mode and while my pluggable database is showing as mount-state. Does that mean the application is using the container database and not the pluggable? Also, is it possible to move back to the non-container database architecture?

If your pluggable database is not open, then you will not be able to connect to it in regular fashion. You need to check the connection descriptor that you application uses in order to check where it connects.

When you create a PDB, it is automatically added in the listener as a service. You can check using

lsnrctl status

To connect to a specific pluggable database, you need to use its service name.
You can start a pluggable database by connecting to the container and issuing:

alter pluggable database <PDB_NAME> open;

To switch your context to a specific pluggable database when connected to the container, use:

alter session set container = <PDB_NAME>;

After that all commands will be ran against that pluggable database.

References:

Introduction to the Multitenant Architecture

Connecting to Container Databases (CDB) and Pluggable Databases (PDB)


How to cache inserted data with ProxySQL to avoid delays we are using MariaDB 10.2 with Galera plugin. Cluster consists of 4 nodes - 3 are busy service complex queries and 1 is more for a backup.

Recently we came across ProxySQL and this sounds very interesting for us in terms of splitting insert queries to one server and selecting data from others. We are hoping to avoid deadlocks and autoincrement problems during high activity.

But one of our developers has discovered a delay - when using ProxySQL he tries to insert a simple record and do instant select of that record. In general every second or third such select-request fails due to latency. If he adds tiny 0.01s delay - he never gets failure, as all galera nodes are able to sync within this time.

Now the question is - is it possible to cache inserted data with ProxySQL, so instant "select" would be returned from the cache, at least for the first 0.01seconds.

Or are there any other ways or suggestions in this scenario?

Thank you. :)

Adding delay of 10 milliseconds before fetching a freshly inserted record solves the issue, and it has been implemented as a temporary fix, but I hope there is a prettier solution to it.


MySQL: how to implement a text search without delimiter I have a table table1 that contains several fields, some textual and other non-textual, on which I do search queries. Let's focus on some text fields, which I simply call field1, field2, field3.
The table contains about 1.5 million records and the queries are not so fast, because when I have to search on these fields, I'm doing in fullscan mode.

I give you a concrete example:

SELECT *
FROM table1
WHERE [some conditions]
AND (field1 LIKE "%str1%" OR field2 LIKE "%str1%" or field3 LIKE "%str1%")
AND (field1 LIKE "%str2%" OR field2 LIKE "%str2%" or field3 LIKE "%str2%")
AND (field3 LIKE "%str3%")

Point out that str1, str2, etc ... can be a word or a part of a word (for example "Table" or "Tabl").

Considering how the query is build, I don't see any sense to define any index on field1, field2, field3: they would not bring any advantage, just an useless waste of resources in my opionion.

So, I have evaluated the FULLTEXT indexes of MySQL and the query MATCH() AGAINST(): this is not good, because in my case it works only when exactly field1=word1 or field2=word2, while often the fields contain concatenated words without delimiters (for example field1 contains the value "HOUSETABLERAIN" and not "HOUSE TABLE RAIN").

What can I do to improve the execution time? My question includes changes to the query and the edit of data structure as well.

I thought two solutions, but I'm not convinced of either.

Solution A)
Introduce another table table2 (table1_id INT, field VARCHAR, string VARCHAR); for each record in table1, insert all possible substrings (confining itself to combinations with at least 4 characters).
Practical example:

If in table1 there is the record

id | field1
45 | HOUSETABLERAIN

then in table2 there will be

table1_id | field | string
       45 | field1 | HOUS
       45 | field1 | HOUSE
       45 | field1 | HOUSET
       45 | field1 | HOUSETA
       45 | field1 | HOUSETAB
       45 | field1 | HOUSETABL
       45 | field1 | HOUSETABLE
       45 | field1 | HOUSETABLER
       45 | field1 | HOUSETABLERA
       45 | field1 | HOUSETABLERAI
       45 | field1 | HOUSETABLERAIN
       45 | field1 | OUSE
       45 | field1 | OUSET
       45 | field1 | OUSETA
...

Afterwards I could define an index on table2.string and I should have some advantages; furthermore, I could also eliminate a part of the fields in table1, because I would no longer search on them.

But I fear it's madness ... even before testing it.

Solution B)
Add a TEXT field for the search and enter all possible combinations separated by the blank space " ", then define a FULLTEXT index. The problem are the stopwords that are not considered by the MATCH AGAINST query.

I'd like to have some opinions and maybe some better ideas.
Thank you.

Digrams (or trigrams).

Take each pair (or triple) of letters in the text, hash it down to 0..63, set a bit in a BIGINT UNSIGNED.  The "OR" of a few dozen bits together like that will usually lead to about half the bits being on.  (If most of the bits are on, the method is less effective; if too few bits are on, it wastes space.

Do that as you store the data, and store the bigints in extra columns.  When testing, do likewise to the data coming in -- get a bigint representing the query.

WHERE (big3 & test3) = test3

Where big3 is the bigint for field3 and test3 is the bigint for "str3", that clause will check that all of the letter pairs of "str3" are probably in field3.

For the combined test of field1/2/3 against "str1",

AND ((big1 | big2 | big3) & test1) = test1

Better yet is to precompute (big1 | big2 | big3) into a combined big123 initially.

I said "probably".  That means you will get some false positives and need to double check the 'hard' way, as you already discussed.

Note:  If there is some meaning you would like to apply for "word boundary", that can easily be done in the building of the bigints.

Hash

One such hash:

SELECT HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F));
+-------------------------------------------------------------+
| HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F)) |
+-------------------------------------------------------------+
| 8000                                                        |
+-------------------------------------------------------------+

Translate this into your programming language; it would probably be easier there, not in SQL.

Dissecting it:

HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F))
                              ^^  sample digraph
                       ^^^^^  fold case unless you need to differentiate
                   ^^^  a readily available hashing function
               ^^^                   ^^^  -- pick some hex digits out of it
          ^^^^                             ^^^^^^  -- hex -> decimal
                                                   ^^^^^^  -- only 6 bits
    ^^^^  -- shift "1" to the bit position given by the 6-bit number
^^^  -- (just for displaying the result; not part of the algorithm)


Wrong sort order using utf8_unicode_ci and swedish characters like ö I am confused about what encoding and collation I should use so my queries returns the correct sort order.

Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and lastname.

And the sort order would be something like this:

firstname  lastname
aaa        aaa
ööö        ööö
ooo        ooo
ppp        ppp
qqq        qqq

ööö ööö should be after qqq qqq

Changing collation to utf8_swedish_ci works correctly:

firstname  lastname
aaa        aaa
ooo        ooo
ppp        ppp
qqq        qqq
ööö        ööö

But utf8_unicode_520_ci give me the same result as utf8_unicode_ci, shouldn't it work too?

The funny part is if I only change firstname collation to utf8_unicode_520_ci it seems to work.

Interestingly, someone addressed this apparent difference.

Matthias Bynens answered the post MySQL Collation utf8_unicode differences abour 4 years ago.

He mentioned "Weight Keys"

version-4.0.0 UCA weight keys
utf8_unicode_520_ci is based on UCA 5.2.0 weight keys

For more information, go to The Unicode Consortium.


Minimum credentials on SQL Server to connect via Power BI Desktop Note: This is not a Power BI issue, this is a DB credential issue.

In Power BI Desktop, I am trying to connect to a database, but I only want that user to have SELECT access.

I have one User that is a "sysadmin" under the Server Role and that works.  But, that's too many permissions.

I created a new User and gave it Login Properties > Server Role = "public", then went into the database I need and gave it Membership > db_datareader.  I thought this would work, but it doesn't.  When I log into SSMS as that User and try to query that database, it says "The database {name} is not accessible."  In Power BI, the error reads: "We couldn't authenticate with the credentials provided. Please try again".

Power BI Users: Yes, I am on the Database tab, not the Windows tab.

I made a test SQL user, created the test user in a Test database I created, and granted it datareader access using scripts, and this worked without an issue.  Switch out the usernames and database names for your app and report in on what happens when you start fresh.  It could be as simple as you're using a old user that is orphaned to other issues so starting fresh could help you isolate it.

Note if you are using this for production you'll want to probably remove the 'check_policy=off' which lets you have passwords that don't conform to your Windows policy.

This creates the user in SQL Server:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

This creates it in the DB and adds it to the datareader list:

USE [test]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO


After moving a DB to a new disk, the log file isn't dumping data to the DB I recently added a new disk to our SQL Server box and followed these steps from Microsoft to move the .mdf file to the new drive. I opted to leave the log file on the original disk for performance reasons.

When I tried to bring the database back online, I was met with a systems permissions exception (admin access was needed). I went into Windows Services and changed SQL Server to log on as the local system account. This appeared to have resolved the problem.

However, two days later, the .mdf file is the same size as pre-transfer (I took it offline and only the timestamp changed). I checked the log, which is usually under 1GB, and found that it has now reached 30GB.

Am I missing something? Why hasn't SQL Server written any of this new data to the DB file itself?

However, two days later, the .mdf file is the same size as
  pre-transfer (I took it offline and only the timestamp changed).

There was enough free space in your data file for any new data that was written. When that spare/free space is consumed the file will grow, not before. You can confirm the size and free space in your data file with:

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

  I checked the log, which is usually under 1GB, and found that it has
  now reached 30GB.

Your database is in full recovery mode and you aren't backing up the log. For this and all manner of transaction log growth explanations, see @MikeWalsh's canonical answer on the topic at Why Does the Transaction Log Keep Growing or Run Out of Space?


Unable to start MongoDB with NUMA interleave When I login to Mongo Shell in my production server, running Oracle Linux v7, I see the message:

  WARNING: You are running on a NUMA machine.

To address this, I have used the init script as suggested here : https://github.com/mongodb/mongo/blob/master/rpm/init.d-mongod.

I have restarted mongod as well as the machine, yet it looks like my mongod does not start with the --interleave=all setting and I see the same warning on start up. Any help?

Your startup script doesn't actually use numactl command to start mongod.

Your startup command should be:

/usr/bin/numactl --interleave=all /usr/bin/mongod

See also a similar question at AskUbuntu: How should I start MongoDB on a NUMA machine?


Weird result when grouping and self-joining a table I'm on MySQL 5.5.49, the results of the following query seem wrong to me.

Test data :

CREATE TABLE `test` (
  `invoice_item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `previous_invoice_item_id` int(10) unsigned DEFAULT NULL,
  `price` decimal(12,4) unsigned NOT NULL,
  PRIMARY KEY (`invoice_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test (invoice_id, previous_invoice_item_id, price)
VALUES (1, NULL, 1), (1, 1, 0);

Query :

SELECT
 ii.invoice_item_id,
 pii.invoice_item_id,
 ii.previous_invoice_item_id,
 ii.price - pii.price,
 ii.price,
 pii.price
FROM test ii
JOIN test pii ON pii.invoice_item_id = ii.previous_invoice_item_id
GROUP BY ii.invoice_item_id;

The problem is that ii.price - pii.price returns 0 when it should return -1. If I remove the GROUP BY clause, the result is correct. If the difference is positive, the result is also correct.

I'm at a loss to explain the result, can someone enlighten me?

It's because your price column is unsigned.

CASTing the values works ok:

SELECT
 ii.invoice_item_id,
 pii.invoice_item_id,
 ii.previous_invoice_item_id,
 cast(ii.price as signed) - cast(pii.price as signed ),
 ii.price,
 pii.price
FROM test ii
JOIN test pii ON pii.invoice_item_id = ii.previous_invoice_item_id
GROUP BY ii.invoice_item_id