Determine when a PostgreSQL database was last changed I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed?

Instead of using pg_dumpall, I'd like to use pg_dump and only dump those databases that have changed since the last backup (some databases don't get updated very often)-- the idea being that if nothing has changed then the current backup should still be good.

Does anyone know of a way to determine when a specific database was last updated/changed?

Thanks...

Update:

I was hoping to not have to write triggers all over tha place as I have no control over
the creation of databases in one particular cluster (let alone creation of db objects within a database).

Digging further, it looks like there is a correlation between the contents of the $PGDATA/global/pg_database file (specifically the second field) and the directory names under $PGDATA/base.

Going out on a limb, I'd guess that the second field of the pg_database file is the database oid and that each database has its own subdirectory under $PGDATA/base (with the oid for the subdirectory name). Is that correct? If so, is it reasonable to use the file timestamps from the files under $PGDATA/base/* as the trigger for needing a backup?

...or is there a better way?

Thanks again...

While using select datname, xact_commit from pg_stat_database; as suggested by @Jack Douglas doesn't quite work (apparently due to autovacuum), select datname, tup_inserted, tup_updated, tup_deleted from pg_stat_database does appear to work. Both DML and DDL changes will change the values of tup_* columns while a vacuum does not (vacuum analyze on the other hand...).

In the off chance that this may be useful for others, I'm including the backup script that I've put in place. This works for Pg 8.4.x but not for 8.2.x-- YMMV depending on the version of Pg used.

#!/usr/bin/env perl
=head1 Synopsis

pg_backup -- selectively backup a postgresql database cluster

=head1 Description

Perform backups (pg_dump*) of postgresql databases in a cluster on an
as needed basis.

For some database clusters, there may be databases that are:

 a. rarely updated/changed and therefore shouldn't require dumping as
    often as those databases that are frequently changed/updated.

 b. are large enough that dumping them without need is undesirable.

The global data is always dumped without regard to whether any
individual databses need backing up or not.

=head1 Usage

pg_backup [OPTION]...

General options:

  -F, --format=c|t|p    output file format for data dumps
                          (custom, tar, plain text) (default is custom)
  -a, --all             backup (pg_dump) all databases in the cluster
                          (default is to only pg_dump databases that have
                          changed since the last backup)
  --backup-dir          directory to place backup files in
                          (default is ./backups)
  -v, --verbose         verbose mode
  --help                show this help, then exit

Connection options:

  -h, --host=HOSTNAME   database server host or socket directory
  -p, --port=PORT       database server port number
  -U, --username=NAME   connect as specified database user
  -d, --database=NAME   connect to database name for global data

=head1 Notes

This utility has been developed against PostgreSQL version 8.4.x. Older
versions of PostgreSQL may not work.

`vacuum` does not appear to trigger a backup unless there is actually
something to vacuum whereas `vacuum analyze` appears to always trigger a
backup.

=head1 Copyright and License

Copyright (C) 2011 by Gregory Siems

This library is free software; you can redistribute it and/or modify it
under the same terms as PostgreSQL itself, either PostgreSQL version
8.4 or, at your option, any later version of PostgreSQL you may have
available.

=cut

use strict;
use warnings;
use Getopt::Long;
use Data::Dumper;
use POSIX qw(strftime);

my %opts = get_options();

my $connect_options = '';
$connect_options .= "--$_=$opts{$_} " for (qw(username host port));

my $shared_dump_args = ($opts{verbose})
    ? $connect_options . ' --verbose '
    : $connect_options;

my $backup_prefix = (exists $opts{host} && $opts{host} ne 'localhost')
    ? $opts{backup_dir} . '/' . $opts{host} . '-'
    : $opts{backup_dir} . '/';

do_main();

########################################################################
sub do_main {
    backup_globals();

    my $last_stats_file = $backup_prefix . 'last_stats';

    # get the previous pg_stat_database data
    my %last_stats;
    if ( -f $last_stats_file) {
        %last_stats = parse_stats (split "\n", slurp_file ($last_stats_file));
    }

    # get the current pg_stat_database data
    my $cmd = 'psql ' . $connect_options;
    $cmd .= " $opts{database} " if (exists $opts{database});
    $cmd .= "-Atc \"
        select date_trunc('minute', now()), datid, datname,
            xact_commit, tup_inserted, tup_updated, tup_deleted
        from pg_stat_database
        where datname not in ('template0','template1','postgres'); \"";
    $cmd =~ s/\ns+/ /g;
    my @stats = `$cmd`;
    my %curr_stats = parse_stats (@stats);

    # do a backup if needed
    foreach my $datname (sort keys %curr_stats) {
        my $needs_backup = 0;
        if ($opts{all}) {
            $needs_backup = 1;
        }
        elsif ( ! exists $last_stats{$datname} ) {
            $needs_backup = 1;
            warn "no last stats for $datname\n" if ($opts{debug});
        }
        else {
            for (qw (tup_inserted tup_updated tup_deleted)) {
                if ($last_stats{$datname}{$_} != $curr_stats{$datname}{$_}) {
                    $needs_backup = 1;
                    warn "$_ stats do not match for $datname\n" if ($opts{debug});
                }
            }
        }
        if ($needs_backup) {
            backup_db ($datname);
        }
        else {
            chitchat ("Database \"$datname\" does not currently require backing up.");
        }
    }

    # update the pg_stat_database data
    open my $fh, '>', $last_stats_file || die "Could not open $last_stats_file for output. !$\n";
    print $fh @stats;
    close $fh;
}

sub parse_stats {
    my @in = @_;
    my %stats;
    chomp @in;
    foreach my $line (@in) {
        my @ary = split /\|/, $line;
        my $datname = $ary[2];
        next unless ($datname);
        foreach my $key (qw(tmsp datid datname xact_commit tup_inserted tup_updated tup_deleted)) {
            my $val = shift @ary;
            $stats{$datname}{$key} = $val;
        }
    }
    return %stats;
}

sub backup_globals {
    chitchat ("Backing up the global data.");

    my $backup_file = $backup_prefix . 'globals-only.backup.gz';
    my $cmd = 'pg_dumpall --globals-only ' . $shared_dump_args;
    $cmd .= " --database=$opts{database} " if (exists $opts{database});

    do_dump ($backup_file, "$cmd | gzip");
}

sub backup_db {
    my $database = shift;
    chitchat ("Backing up database \"$database\".");

    my $backup_file = $backup_prefix . $database . '-schema-only.backup.gz';
    do_dump ($backup_file, "pg_dump --schema-only --create --format=plain $shared_dump_args $database | gzip");

    $backup_file = $backup_prefix . $database . '.backup';
    do_dump ($backup_file, "pg_dump --format=". $opts{format} . " $shared_dump_args $database");
}

sub do_dump {
    my ($backup_file, $cmd) = @_;

    my $temp_file = $backup_file . '.new';
    warn "Command is: $cmd > $temp_file" if ($opts{debug});

    chitchat (`$cmd > $temp_file`);
    if ( -f $temp_file ) {
        chitchat (`mv $temp_file $backup_file`);
    }
}

sub chitchat {
    my @ary = @_;
    return unless (@ary);
    chomp @ary;
    my $first   = shift @ary;
    my $now     = strftime "%Y%m%d-%H:%M:%S", localtime;
    print +(join "\n                  ", "$now $first", @ary), "\n";
}

sub get_options {
    Getopt::Long::Configure('bundling');

    my %opts = ();
    GetOptions(
        "a"             => \$opts{all},
        "all"           => \$opts{all},
        "p=s"           => \$opts{port},
        "port=s"        => \$opts{port},
        "U=s"           => \$opts{username},
        "username=s"    => \$opts{username},
        "h=s"           => \$opts{host},
        "host=s"        => \$opts{host},
        "F=s"           => \$opts{format},
        "format=s"      => \$opts{format},
        "d=s"           => \$opts{database},
        "database=s"    => \$opts{database},
        "backup-dir=s"  => \$opts{backup_dir},
        "help"          => \$opts{help},
        "v"             => \$opts{verbose},
        "verbose"       => \$opts{verbose},
        "debug"         => \$opts{debug},
        );

    # Does the user need help?
    if ($opts{help}) {
        show_help();
    }

    $opts{host}         ||= $ENV{PGHOSTADDR} || $ENV{PGHOST}     || 'localhost';
    $opts{port}         ||= $ENV{PGPORT}     || '5432';
    $opts{host}         ||= $ENV{PGHOST}     || 'localhost';
    $opts{username}     ||= $ENV{PGUSER}     || $ENV{USER}       || 'postgres';
    $opts{database}     ||= $ENV{PGDATABASE} || $opts{username};
    $opts{backup_dir}   ||= './backups';

    my %formats = (
        c       => 'custom',
        custom  => 'custom',
        t       => 'tar',
        tar     => 'tar',
        p       => 'plain',
        plain   => 'plain',
    );
    $opts{format} = (defined $opts{format})
        ? $formats{$opts{format}} || 'custom'
        : 'custom';

    warn Dumper \%opts if ($opts{debug});
    return %opts;
}

sub show_help {
    print `perldoc -F $0`;
    exit;
}

sub slurp_file { local (*ARGV, $/); @ARGV = shift; <> }

__END__

Update: the script has been put on github here.


postgresql - a local replication entry doesn't work Maybe it's just too early in the morning, but I can't seem to get pg_basebackup working for a local user. This is part of testing for an hourly backup automated script. I added a line to pg_hba.conf for this and it looks like:

local     replication      postgres                      trust

But, this entry causes a FATAL error: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off. What did I screw up? I'll be changing the user once I get this part working. The pg_basebackup command looks like:

pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz

FYI, if I add "-h ", it does work as I do have another pg_hba.conf entry for:

host    replication   postgres   10.10.1.129       md5

Does local not work for replication entries?

(Using PostgreSQL 9.3)

The problem is: "localhost" can be UNIX sockets, IPv4 or IPv6. The first rule you passed is for UNIX sockets. Try to add a rule for IPv4 and one for IPv6. It should work then.
Btw, a SIGHUP is enough in case you change pg_hba.conf.


Alternative Database Tools for Web Admin I currently use phpmyadmin for most of my database administration.  It works well enough, but I'm interested in other tools that might work more quickly/efficiently/etc...

What are the best and/or alternative admin tools for MySQL on a *nix box?

A list of web replacements is available here:

http://www.webdesigneronline.co.uk/10-great-alternatives-to-phpmyadmin

And this question was asked before in Stackoverflow as well:

https://stackoverflow.com/questions/1324972/ajax-phpmyadmin-alternative

In my opinion, a alternative phpmyadmin should not just look good, but must at all cost keep up with security updates. If you are intranet user, it might be a different case, but I know from the early days of phpmyadmin that security is very important for anything involving "http and sql" ;-)


SQLite - No such column when column exists I have a table (trackedinfo) inside my database that has the following columns (columns obtained by running PRAGMA table_info(trackedinfo);)

The problem is that even though the column sendok exists, when running a query on the database with that field, it throws an error.

Example queries:

SELECT * FROM trackedinfo WHERE sendok IS NULL;
SELECT sendok FROM trackedinfo;

Error:

SQLITE_ERROR: SQL error or missing database (no such column: sendok)

But, if I run a query selecting all of the fields, it brings me the info regarding sendok:

Here is the CREATE command of the database:

CREATE TABLE trackedinfo
(
    id INTEGER PRIMARY KEY,
    date_time_start TEXT,
    date_time_end TEXT,
    tracked_name TEXT,
    tracked_origin TEXT,
    tracked_maker TEXT,
    tracked_version TEXT,
    tracked_type TEXT,
    sendok TEXT,
    tracked_id TEXT
);

EDIT

It also happens with the column tracked_id

EDIT 2

Info that I got by executing .schema trackedinfo

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);

The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces.
Before:
CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);

After:
CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok" TEXT, "tracked_id" TEXT);

In the above examples white spaces whithin "sendok" and "tracked_id" columns has been removed.


DB2 9.5 - Shrink Tablespace I'm trying to run the following:

db2 alter tablespace TABLESPACE1 reduce

I keep getting errors. As you can see, I have around 50% free pages. I'd like to shrink it in order to reclaim OS disk space.

 Tablespace ID                        = 281
 Name                                 = TABLESPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8943720
 Useable pages                        = 8943696
 Used pages                           = 4559696
 Free pages                           = 4384000
 High water mark (pages)              = 8914736
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 8
 Prefetch size (pages)                = 24
 Number of containers                 = 3
 Minimum recovery time                = 2014-12-09-16.28.18.000000

  Container ID                         = 0
 Name                                 = /data/db2dat_10/sida1/TABLESPACE1_01.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /data/db2dat_11/sida1/TABLESPACE1_02.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /data/db2dat_12/sida1/TABLESPACE1_03.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes

I ended up figuring this one out.

Shrink a tablespace with DB2 9.5 is much more difficult.
The reduce and resize command exist but will only work if the high water mark is low.
You cannot use reduce or resize below the HWM size.

You need to use DB2DART -LHWM and have the database deactivated while doing so.


How to grant Connect & Create Privilege on current Database in PostgreSQL? After looking at the docs, I know that you can grant Connect & Create Privileges by using the following commands:

GRANT CREATE ON DATABASE <db_name> TO user;
GRANT CONNECT ON DATABASE <db_name> TO user;

How do I grant these privileges on the current database without specifying it's name?

I'm trying to write a script which will be used to grant all rights whenever a new database is created by my application, and hence I don't know what the name will be while writing the script.

You could use a DO block with dynamic SQL:

do
$$
begin
  execute format('grant create, connect on database %I to %I', current_database(), 'some_user');
end;
$$;


What do most people use to work with SSIS packages since VS2015 UI is buggy? I don't think I can post bugs on Microsoft connect. I'm trying to use SSDT on VS community edition. And the UI is terribly buggy. Here are some screenshots:

The Enumerator properties manager is just painful. I can drag it down a tiny bit but can still barely read one line at a time.

And this image is another example. What tools do most people use to develop SSIS in? I can't believe that it's the same ones I'm using...

I'm using a surface pro 4 that is up-to-date as far as I know

You can post on connect with any version and I would encourage you to.

These issues aside I think the only sane way to work with ssis is to use biml to write your packages and compile that into ssis.

This means you don't need to keep going into the properties of each task ad-infinitum- if you want to see how a task is configured you scroll down in the biml and look.

It is a bit more work upfront but as soon as you need to make a change or look at an old package it will pay off dividends.

There are other advantages like easy source code management, comparing biml files for changes is much easier than the raw ssis xml that changes id's every time you press "build".

Forget writing ssis packages using the ide and use it to debug packages when you really really need to .

Just to add on that when you experience the pain of being in the script editor ide and needing to close it down, close down th script properties, go into the data flow and look at the properties of a destination task and then go into the mappings tab to then close it all back to go back to the script task you realise that native ssis development is horrible!


Removing older components after upgrading I will upgrade to SQL Server 2016 from a SQL Server 2014 in my company.

This server has had many versions of SQL Server (2008, 2012, etc).

What should we do with the old version's components? When a version is upgraded, older components are left behind.

You could uninstall them, but why? They're just sitting their idle not hurting anything, but you could get over-zealous and remove something you didn't realize you need.

I'd be more concerned about the underlying OS - has it been upgraded along the way too? We just had a scenario where we couldn't upgrade to 2016 because the OS was no longer supported by SQL Server.

IMHO: Just leave those old components be.

If some pointy-haired security person automatically thinks that "old" == "insecure" without any evidence, then there are probably some components you can remove without causing problems, and some that involve more risk.

Safe (and I'll list those alphabetically):

old versions of upgrade advisor - current one, even, after you've upgraded.

Not slam dunks:

setup support files (until everything involving that version has been cleanly removed)
older management objects (on a system I have with 2014, 2016, and v.Next, I only have 2014 management objects listed)
CLR types for older versions (I would ensure that all of your databases are in at least that version+1 compatibility level and that you don't have custom CLR objects that rely on that version)
Integration Services (how do you know these aren't being used?)

For things that are services (like SSIS), a less risky way to do it would be to turn the services off for some period of time (a full business cycle is ideal). If nobody complains that the service isn't up throughout that time, than it is possibly safe to take the next step. As a bonus, your security folks should be happy in the meantime because if the service isn't running, it isn't a security risk.

At some point it makes more sense to just back up all your databases, pave a new OS, install a brand new instance fresh, and blow the old system away. This Chicken Little approach to "cleanup" for imaginary security risks just doesn't sound like a good time to me, and in fact seems to involve more potential risk than any security concerns they could possibly have.


Can I send a string over TCP using T-SQL? I've set up some alerts, based on this article

Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same machine of the SQL instance, and then the C# application sends the information to other machine).

My application can accept TCP connections so I was thinking about using that, but any other form for the SQL server to communicate with my application is also welcome.

Any suggestions in how I can do this?

*edit:*Being able to do an insert in another database(in other instance) via T-SQL would also solve my problem.

Background info:
I'm doing this to monitor when the Principal server is running exposed, and when the Mirror server is synchronized. So that if the witness goes down, and then the principal also goes down, based on the records of these alerts I can tell if its safe to promote the Mirror to Principal.

Note, that in this case scenario I cant read the mirror database without break the mirroring configuration, so using a table inside the Mirrored database is not a solution.

The article linked uses the dreaded WMI middle-man for delivering the notifications. That is so error prone that is not worth talking about. Cut out the WMI middle man (that also looses events...) and go straight to the source: create an event notification for the Database Mirroring State Change Event. Deliver the notifications locally to your [msdb] database. Then you can react in your activation procedure directly to the state change notification. Yo do not need any TCP or other communication, each mirroring partner can do this monitoring independently and each partner reacts to the local state change.
For a C# application to react to the notification simply have it monitor a queue (WAITFOR(RECEIVE...)) where, again, you have the system deliver event notifications for mirroring state change event. You can even use the external activation app.
The Pro SQL Server 2008 Mirroring book has an example showing exactly this technique.
Just don't use WMI... and absolutely don't connect to client back from server via TCP.


How to shift a value one record 'down' Suppose we have a field ("Flag"). How we can shift records of this flag in another field ("ShiftField") by using SQL code?

See the example:

ID   Flag     ShiftFlag
1    0
2    1        0
3    1        1
4    1        1
5    0        1
6    0        0
7    1        0
8    1        1
9    1        1
10   0        1

My efforts already was something like below:

for i=1 to n str =
  "UPDATE HoboData Set ShiftFlag = Wet_Estimated80 WHERE ID = " & i + 1
next i
Dim cmd As OleDbCommand = New OleDbCommand(str, Connect)

but this way is very slow

This will do the trick in recent versions of SQL Server and Oracle:

SELECT ID, Flag,
  LAG(Flag) OVER(ORDER BY id) AS ShiftFlag
FROM tab
ORDER BY id;

If you don't have LAG(), for example in MS SQL Server 2008, you can use ROW_NUMBER() like this:

;WITH num AS (
  SELECT ID, Flag, ROW_NUMBER() OVER(ORDER BY id) AS n FROM tab
)
SELECT t1.ID, t1.Flag, t2.Flag AS ShiftFlag
FROM num t1
LEFT OUTER JOIN num t2 ON t2.n = t1.n - 1
ORDER BY t1.ID;

Tested on SQLFiddle.


mysql between condition I am trying to understand a portion of a query i am looking at. it was written elsewhere.

between now() - interval 30 day and now();

Does this mean between now and 30 days above (returns data between now and one month in advance or does it mean between now and one month prior?

The problems i am having to understand are:

Why is there a minus sign instead of an AND after between? Unless the and condition is (between now-interval 30 day AND now).
If so, does the minus sign incinuate back 30 days?

Parenthesis will probably help:

col BETWEEN (now() - interval 30 day) AND now()

now() - interval 30 day means "30 days ago" - the whole expression is "between 30 days ago and now". Actually, unless you have values higher than current datetime, you can simply use:

col >= NOW() - interval 30 day

This page is a good explanation of the INTERVAL syntax. MySQL documentation about time and date functions DATE_ADD() and DATE_SUB() explains the syntax as well: Date and time functions.

now() - interval 30 day is equivalent to date_sub(now(), interval 30 day), so you could also write the condition like this:

col BETWEEN date_sub(now(), interval 30 day) AND now()

Note that NOW() - interval 30 day means 30 days ago at this exact time, because NOW() returns a DATETIME value.


How to implement composite partition (Range list / Hash range) partition in SQL server? Our production database table already have a 18 partitions based on date range for a single state wise, records of around 200 million rows. Now we are planning to include multiple states with state ID and the record count will hit around 3 billions. Is it possible to implement List partition based on state id and create a sub partition based on date range in SQL server similar to Oracle. I am aware that SQL server will support only Range partition, is there any work around to implement like that? Do not want to create any tables based on state wise. ours is a single staging table and multiple objects depends on the table so any additional objects will be strictly avoided because it requires lot of dependency objects code change. Kindly help me to create composite partition similar to Oracle in SQL server 2012 enterprise edition.

My requirement is first do the list based on state then do the range based on year

That's not a requirement: that's a design.  SQL Server doesn't do that.

Instead, you need to look to the data structure to provide the efficient access pattern within each partition.

If you use a clustered index, then within each partition SQL can perform efficient range scanning by the leading index columns.

And if you use a clustered Columnstore index SQL can use Rowgroup Elimination


Source of network_name for distributor So, I have a really weird case.
Was doing a migration from sql server 2005 to 2012. Built the server, and at the last minute we found that the server was called abc/xyz. We just wanted abc, so the DBA did a bunch of stuff including dropping servernames and adding entries to the TCPIP and now we have abc!

Now, I come to creating a publisher. Start by configuring a distributor, and it fails. I get the message that the abc cannot be configured as a distributor because it cannot connect to abc/xyz (old server name) since 'distributor_admin' is not defined as a remote login at the server.

If I do select @@servername, the answer is abc. If I do sp_helpserver I see that a linkedserver has been created to repl_distributor to abc/xyx (old server).

I've tried editing repl_distributor to point to an actual server, but thus far, no success.

Any ideas? I'm totally stuck.

"abc" would be a default instance, where "abc" is the actual server name. Sounds like you created a named instance where /xyz is the name of the instance. This allows you to have multiple instances of SQL Server on the same server. The only way to remove the instance is to uninstall SQL Server and then reinstall with the name you want.


Design for table with multiple links to other table I am designing a database to keep track of our network equipment. My db has 2 tables:

sites & circuits

(circuit = cable) & (site = property location)

Each circuit runs between two sites, so it should relate to two sites, yes? The circuit table has the following  columns: ID_circuit (PK), circuit_number, site1, site2. I thought that I could relate my ID_site field to both site1 and site2, but I cannot. Would I be better off creating a whole other table to hold the locations of both cable ends?

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of.

Example Query:

SELECT CT.site1, CT.site2, S1.site_description, S2.site_description
FROM circuit_table AS CT
INNER JOIN site_table as S1
  ON CT.site1 = S1.ID_site
INNER JOIN site_table as S2
  ON CT.site2 = S2.ID_site


Calculate new percentage when new record is added I have this MySQL that I am using to calculate a percentage for a segment in a call. When a new record is added, I am able to get the correct percentage for each row.

$sumGreeting = $dbcon->prepare("
    SELECT  SUM(greeting_msg + provide_agent_name + clear_and_aud +
                confirm_caller_name + confirm_caller_tel +
                ask_alt_number) AS SumGrt
    FROM  table
    GROUP BY  coach , id

");
    $sumGreeting->execute();

I want to calculate the final percentage each time a new record is added. Taking in mind the previous or existing records. At the moment I'm getting this result below:

I'm using this code:

    $initRows = 0;
    $result = 0;
    while ($row = $sumGreeting->fetch(PDO::FETCH_ASSOC))
    {
         $initRows = $row['SumGrt'];
         $result = ($initRows/12)*100;
     echo var_dump($result);
    echo $result ." <br />";
    }

The Expected results will be the final percentage of all the columns.

This section has a total of 12 points.

Thanks in advance

Why don't you sum the result for each group and use that sum to calculate the overall percentage?
E.g. You have 4 groups with result 34,23,56,78. The total percent can be calculated by:
 total = sum(34,23,56,78)/4 * 100;

After running the aforementioned php code on insertion, use the value of the $result variable to perform the calculation.

Wanted to put as a comment but I can't... I will try to put the above in code. Does this help?

This is a sample:

$conn = mysqli_connect("localhost","root","","test"); # replace the details as it becomes necessary
if(!$conn){
die("Connection failed: " . mysqli_connect_error());
}
else{
    $initRows = 0;
    $result = 0;
    $sql = "SELECT SUM(num1 + num2 + num3) AS SumNumbers  FROM numbers GROUP BY numid";
    $res = $conn->query($sql);
    $initRows = 0;
    $rowNum = 0;
    $result = 0;
    $total = 0;
    if ($res->num_rows > 0) {
       while ($row = $res->fetch_assoc())
        {
            $initRows = $row['SumNumbers'];
            $rowNum = $res->num_rows;     #this will give the number of rows
            $columncount = mysqli_num_fields($res); # this will give the total number of columns
            $result = floor(($initRows/$columncount)*100); #this will give the percentage
            $total += $result;     #this will give a total of all results but not the percentage. if you need the persentage of the total, you can now derive it using the $total variable
            echo $result ."
";
        }
        echo "Total is " . $total . "
";
        $totalres = floor(($total/$rowNum)*100);
        echo "Total result percentage is " . $totalres;
    }
}


Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions? I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

queries like this can use it for index-only scans:

SELECT y FROM tab WHERE x = 'key';

Now I am wondering if such a covering index could also allow index-only scans when the covering columns appear as conditions. For instance, assume a covering index:

CREATE INDEX tab_x_y_z ON tab(x) INCLUDE (y, z);

Would this allow for index-only scans for the following queries?

SELECT z FROM tab WHERE x = 'key' AND y = 1;

SELECT x, y, z FROM (VALUES ('key1'),('key2'),('key3')) sub(id)
JOIN tab ON tab.x = sub.id WHERE y = 1;

I should have tried a bit more first. Now that I run a few examples, I seems like yes, the above queries can all use an index-only scan. As soon as a column outside of the covering index is referred to, a regular access method is used (e.g. an index scan id a fitting index is in place).

It would be nice if the Postgres documentation would make this more clear (or I overlooked the information).


WITH
cte1 AS ( SELECT DISTINCT created_at
          FROM t
          ORDER BY 1 DESC LIMIT 2 ),
cte2 AS ( SELECT src.*,
                 LEAD(src.followers) OVER (PARTITION BY src.id
                                           ORDER BY src.created_at DESC) prev_followers,
                 src.followers - LEAD(src.followers) OVER (PARTITION BY src.id
                                                           ORDER BY src.created_at DESC) gain
          FROM t src
          JOIN cte1 ON src.created_at = cte1.created_at )
SELECT *
FROM cte2
WHERE gain IS NOT NULL
ORDER BY gain DESC LIMIT 1;

fiddle

whenever there are two records with the same difference margin..It always shows one instead of two.

WITH
cte1 AS ( SELECT DISTINCT created_at
          FROM t
          ORDER BY 1 DESC LIMIT 2 ),
cte2 AS ( SELECT src.*,
                 LEAD(src.followers) OVER (PARTITION BY src.id
                                           ORDER BY src.created_at DESC) prev_followers,
                 src.followers - LEAD(src.followers) OVER (PARTITION BY src.id
                                                           ORDER BY src.created_at DESC) gain
          FROM t src
          JOIN cte1 ON src.created_at = cte1.created_at ),
cte3 AS ( SELECT *, RANK() OVER (ORDER BY gain DESC) rnk
          FROM cte2
          WHERE gain IS NOT NULL )
SELECT *
FROM cte3
WHERE rnk = 1;

fiddle


In Postgres, how to get a list of current defined savepoint? I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection.

To be more precise: I'd like to check which name would NOT trigger the "no such savepoint" error in a connection.

Interesting question! Short answer: no.

Long answer: there does not appear to be any existing way to get a list of savepoints defined. Even worse, it doesn't seem possible to create a PostgreSQL extension which would let you do this: looking at src/backend/access/transam/xact.c, you can see that functions like RollbackToSavepoint (which is where that "no such savepoint" error message you mentioned comes from) rely on the variable CurrentTransactionState, which is declared static to xact.c, i.e. would not be visible globally to extension code.

Now, if you were daring and quite desperate to generate a list of defined savepoints from the server-side (as opposed to just having your client remember...), you could add in a helper function to xact.c that would display this information for you. In fact, here is just such a patch. That's a very rough patch for illustration purposes only, and just elogs the savepoint names, it should really be returning those names as setof text.

As to why this feature is missing, I surmise that there is simply no plausible use case for a client needing to fetch a list of defined savepoints from the server. What would the client do with this list -- just choose one at random and ROLLBACK to it? ROLLBACK to the last one blindly? AFAICT savepoints are only useful if a client remembers what savepoints it has defined and where they were in order to be able to make use of them.


PostgreSQL - how to generate an array containing column name and column data...? I'd like to create a dictionary - an array - from a simple query, to include column names in output.

IE, I'd like to turn this:

SELECT id, last, first FROM names;

  id | last     | first
-----+----------+-------------------------
001  | Smith    | John

into:

{ "id": "001","last": "Smith", "first": "John" }

Yes, bonus points for the quotes and colons! Ha!

We do have this very interesting catalog function, which will return all column names into an array.

SELECT array_agg(column_name::TEXT)
FROM information_schema.columns
WHERE table_name = 'people';

How to aggregate (couldn't help myself) that into a regular query?

The first one can be done using a JSON function

select to_jsonb(n)
from names n;

I have no idea what you want to achieve with the second query.


Equivalent of UTF8_UNICODE_CI collation in PostgreSQL I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried:

CREATE TABLE thing (
    id    BIGINT PRIMARY KEY
   ,name  VARCHAR(120) NOT NULL COLLATE "UTF8_UNICODE_CI"
);

but I get:

ERROR: collation "UTF8_UNICODE_CI" for encoding "UTF8" does not exist

Reading around, I found that the pg_collation table displays collations, which showed:

=# SELECT * from pg_collation;
 collname | collnamespace | collowner | collencoding | collcollate | collctype
----------+---------------+-----------+--------------+-------------+-----------
 default  |            11 |        10 |           -1 |             |
 C        |            11 |        10 |           -1 | C           | C
 POSIX    |            11 |        10 |           -1 | POSIX       | POSIX
(3 rows)

So it's clearly not there... Is there any way to add it, or obtain the behavior I want? (I want a search WHERE name LIKE '%lala%' to match 'lalá', 'LÂLÄ', etc.)

In Postgres, you would typically solve this differently.

For starters, just use ILIKE for case insensitive matching. The manual:

  The key word ILIKE can be used instead of LIKE to make the match
  case-insensitive according to the active locale. This is not in the
  SQL standard but is a PostgreSQL extension.

WHERE name ILIKE '%lala%'

You do not need a different collation for this. Trigram indexes support both case sensitive and insensitive pattern matching:

LOWER LIKE vs iLIKE
How is LIKE implemented?

Or use the more portable lower(col) LIKE

WHERE lower(name) LIKE '%lala%'  -- pattern must be lower case, too.

Generic Ruby solution for SQLite3 “LIKE” or PostgreSQL “ILIKE”?

Or you could use the special data type citext provided by the additional module citext:

Index on column with data type citext not used

If you insist on a special collation, you can create your own:

Add a new collation to a Postgres database

I would not do that.

If you want to take string "normalization" one step further (remove all diacritic signs), look to unaccent():

Does PostgreSQL support “accent insensitive” collations?

You can combine both, instructions in the linked answer. More here:

PostgreSQL accent + case insensitive search

Overview:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I would just use ILIKE and support it with a trigram index for big tables. Preferably use the latest version Postgres 9.6. It has several improvements for trigram GIN indexes.


How to use Limit in query on my index column without scan all rows? Here is my table :

In my table

Clustering_key (Primary key and auto incremental)
ID (Index Column)
Data (Text datatype column)
Position(Index column) maintain the order of Data

My table have 90,000 rows with same ID equal to 5. I want to fetch first 3 rows and my query like this

Select * from mytable where ID=5 Limit 3;

ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.

Here Explain query :

Any possibility to avoid all rows scan.

Please give me some solution

Thanks in advance

EXPLAIN is not as smart as one would like.  It provided 42415 without noticing your LIMIT 3.

The important clue that says that it will use the index is the Key column lists the index name.

That query, with that index (I assume ID is a numeric datatype) will touch only 3 rows of the index, then 3 rows of the data.

Two ways to get more info:

EXPLAIN FORMAT=JSON SELECT ...;

or

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

I would expect to see 2 (3-1) in one or two rows, and not see a number that matches the table size.


Is there any way to find out if any alteration is done to any table? I am using SQL Server and a database has too many tables. I am preparing the list of tables which have been recently modified.

Is there any way to find out which tables are modified?

I am interested in knowing:

if any datatypes are modified to any columns,
new column is added,
any column is deleted.

Note
The answers below shows tables which has been modified but do not give enough information of what changes has been done to the tables.

You can check the modify_date column in either the sys.tables or sys.all_objects (with type = 'U' for user table) system views.  The below query will work, note that this cast defaults to midnight.

select name from sys.all_objects where type = 'U' and
modify_date >= cast('2018-08-22' as datetime)


Relaying changes to a slave from another master with Tungsten on MySQL I am trying to setup a multi-master type of replication with each master having an additional slave using Tungsten. As an example, lets say I have 4 hosts (A,B,C,D) and hosts A and C are the masters, with B being a slave to A and D being a slave to C.

With my current configuration, I will add data to A. This gets replicated to its slave B and to the other master C with no issue. The same happens with I add data to C, its slave D gets updated and the other master A gets updated. The problem is when A gets updated, D will not see the changes. I do not want A to update D, but rather have D get all of the updates from C, which would also have B get all of its updates from A.

Here is the configuration script I have been working on.

MASTER1='master1-ca' #<-- 'A' from example
MASTER2='master2-va' #<-- 'C' from example
SLAVE1='slave1-ca'   #<-- 'B' from example
SLAVE2='slave2-va'   #<-- 'D' from example

./tools/tpm configure defaults  \
    --reset \
    --replication-user=tungsten \
    --replication-password=password \
    --home-directory=/home/tungsten/PCS_DB \
    --datasource-mysql-conf=/etc/mysql/my.cnf \
    $( cat ./cookbook/security.options ) \
    --start

./tools/tpm configure PCS_Multi \
    --masters=$MASTER1,$MASTER2 \
    --master-services=west,east \
    $( cat ./cookbook/security.options ) \
    --topology=all-masters

./tools/tpm configure west \
    --hosts=$MASTER1,$SLAVE1 \
    --slaves=$SLAVE1 \
    $( cat ./cookbook/security.options ) \
    --master=$MASTER1

./tools/tpm configure east \
    --hosts=$MASTER2,$SLAVE2 \
    --slaves=$SLAVE2 \
    $( cat ./cookbook/security.options ) \
    --master=$MASTER2

./tools/tpm install

I have been looking through documentation and tutorials for a few days now and can't seem to figure out this last piece. I have tried to setup a relay from $MASTER1 to $SLAVE1 but since there is not a lot of documentation on setting up a relay (at least from what I could find) I do not know if I am doing it correctly (or if I should even be doing that in the first place).

I don't have direct experience with a similar setup, but you probably need to set this option on the master services:

--log-slave-updates           Should slaves log updates to binlog [false]

By default, changes made by appliers are not written to the binlog. As Tungsten watches the binlog to detect changes, the master extractor will not see those changes made by appliers, unless you specify the options above.


What's the best way to deal with a database with a big table with information that will RARELY be used? I think useless is a heavy word for this.

We have a database with 500GB, and most of it's information is only there if we MAYBE need to use them. it's a big database with credit card information and until now, we've never had the need to select them ( the table has 500millions rows ).

this database is growing daily and I would like to know what is the best approach of this?

I can't think in something smart. I don't think a backup and delete all rows will be the best approach. then if we need those rows, we restore the backup.

Is there something like partitioning to be used here?
there are 2 tables with this "problem". how can I get rid of these 2 tables, but maintaining these tables secured?

You basically have a couple of options, but in the end if you have records, they must occupy space.

You can try to compress the data. This will reduce disk space, but has side effects (it can make queries slower, for example). And it may not be available in your version of SQL. If I remember correctly, in SQL 2008 r2, compression requires Enterprise Edition. Brent Ozar's website talks a bit about some other side effects.

Otherwise, the best you can do is either move the data to cheaper (slower) disks via partitions and/or file groups or work out a retention policy that lets you purge data past a certain age (possibly after exporting it?)


Colloboration between T-SQL Table and Excel Sheet(s) Let's say I have a T-SQL table with 20-30 columns and 2-3 millions rows (huge table). I want to allow a user to update this data directly (the solution must work for different tables). I generally have the following options:

to create simple web interface displaying only part of the data via paging
to create desktop application which can download the data (on parts) and sync it when it is changed

This will probably work, but the userwill be force to learn how to work with the interface, and the application functionalities will consume a lot of time to develop - it will be easier to use tool that is already created and the user is familiar with (for example excel).

I know I can export/import data between T-SQL table and excel file, but is there a way to build some kind of collaboration between them - for example the excel to call a stored procedure which is returning the data (it will be nice if I can send a hash of the data or the rows in order to return only the rows that have been changed). And to call a stored procedure which can upload/delete/insert data in the table.

If not, I guess I am going to work with files - download the file, edit the data in excel, then upload it, if there aren't other ways?

It's very easy to do this in access. Setup an ODBC Connection on the users computer then add in a linked-table into the access db.

I don't think it would take too long for someone to create an application with a nice winform interface. We have one at my office where if you create a function/stored procedure in SQL starting with certain characters, lets say dbo.app_*******, it will appear as a report in the application and any parameters in the stored procedure will prompt the user to input a value in the app.


String replacement in SQLCMD I'm trying to set up a test environment that simulates (very roughly) having three servers. I'm doing this by creating 3 databases on my local instance of SQL which I'll call [A], [B], and [C]. Why I can't simply set up three test instances, is a story for another time. I've got a bunch of code files I want to execute using SQLCMD, which is no problem. I've got a parent SQLCMD file that looks like

:Setvar db gt
:Setvar filepath "C:\Documents\test\"
:r "C:\Documents\test\master.sql"

then another file (master.sql) with a few statements like create database $(db) and so on.

The problem now however is in the actual code files, the database referenced is [Alerts] on the actual server, but in my text harness the database has to be [A], [B] or [C] (since I'm using the database names to simulate the server).

Imagine I also have a file MyTable.sql which looks roughly like this:

create table [alerts].[dbo].[MyTable]
(
    id int identity(1,1)
)

In pseudocode, I'd like to do something like this:

(:r $(filepath)MyTable.sql).Replace('[Alerts]', '$(db)')

Does anyone know if this is possible?

If I understand your question correctly, let me try providing an alternate solution.

You could write a quick powershell script to automate the whole process -
-reading source .sql files
In a loop -
-replacing 'Alerts' word with A (B and C).
-run those files calling sqlcmd within the powershell script


SQL Server Configuration File and InstanceName parameter We have a configuration file at E:\SQL_Server_2016_Install\MyConfigFile.ini that looks like below:
[OPTIONS]
ACTION = "Install"
FEATURES = SQLENGINE
INSTANCENAME = MSSQLSERVER
AGTSVCACCOUNT = "MyDomain\MyAccount"
SQLSVCACCOUNT = "MyDomain\MyAccount"
QS
IACCEPTSQLSERVERLICENSETERMS

SQL Server is then installed using below batch file:
E:\SQL_Server_2016_Install\Setup.exe /ConfigurationFile=MyConfigFile.ini /InstanceName="TestSQLServer"
Question:
Will /InstanceName="TestSQLServer" parameter override what we have in the configuration file (/InstanceName = MSSQLSERVER (default instance)), and install SQL Server as a named instance TestSQLServer ?

Will /InstanceName="TestSQLServer" parameter override what we have in
the configuration file (/InstanceName = MSSQLSERVER (default
instance)), and install SQL Server as a named instance "TestSQLServer"
?

Yes, per the below excerpt from the configuration file documentation :

The configuration file overwrites the defaults in a package

Command-line values overwrite the values in the configuration file


T-SQL CTE Compare previous non-NULL and current row to filter out by difference value My sample database (in fact a CTE statement data) looks like this:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  3974
2012-03-28  3965
2012-03-29  3967
2012-03-30  3959
2012-04-02  3951
2012-04-03  3961
2012-04-04  3944
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I want to remove the values that are less than 12 in difference. This is my query and output:

SELECT
eventdate,
CASE
WHEN ABS(val - LAG(val) OVER (ORDER BY eventdate)) <= 12
THEN NULL
ELSE val
END AS val
FROM tbl_1
ORDER BY eventdate

Output:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  NULL
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  NULL
2012-04-03  NULL
2012-04-04  3944
2012-04-05  NULL
2012-04-09  3901
2012-04-10  3822

The problem is that it compares between current and previous row. I need to compare between current and previous non-NULL value, I mean the last value that was not NULLed on the previous step.

This is what I need:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  3965
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  3951
2012-04-03  NULL
2012-04-04  NULL
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I tried the following.
I made a self-referenced CTE query as above replacing THEN NULL with THEN LAG(val) OVER (ORDER BY eventdate) to copy last appropriate value and compare with next. Then remove duplicates. But it deadly loops on OPTION (MAXRECURSION 0) with no results at all.

Seems this could be done by CURSOR only.
I need the result set as CTE statement (within the CTE), to use it in next query.

As far as I looked for CURSOR examples they all are done as a final SELECT to CTE.
This is not an option for me!

I am running SQL Server 2014.

Many thanks for your help or thoughts!

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan.

declare @T table
(
  Eventdate date index IX_Eventdate clustered,
  Val int
);

insert into @T(Eventdate, Val) values
('2012-03-23',  3965),
('2012-03-26',  3979),
('2012-03-27',  3974),
('2012-03-28',  3965),
('2012-03-29',  3967),
('2012-03-30',  3959),
('2012-04-02',  3951),
('2012-04-03',  3961),
('2012-04-04',  3944),
('2012-04-05',  3935),
('2012-04-09',  3901),
('2012-04-10',  3822);

with C as
(
  select top(1)
         T.Eventdate,
         T.Val,
         T.Val as PrevVal1,
         T.Val as PrevVal2
  from @T as T
  order by T.eventdate
  union all
  select T.Eventdate,
         T.Val,
         T.PrevVal1,
         T.PrevVal2
  from (
       select T.Eventdate,
              T.Val,
              iif(abs(T.Val - C.PrevVal1) <= 12, C.PrevVal1, T.Val) as PrevVal1,
              iif(abs(T.Val - C.PrevVal1) <= 12, null, T.Val) as PrevVal2,
              row_number() over(order by T.Eventdate) as rn
       from @T as T
       inner join C
         on C.Eventdate < T.Eventdate
       ) as T
  where T.rn = 1
)
select C.Eventdate,
       C.PrevVal2 as Val
from C
order by C.Eventdate
option (maxrecursion 0);


Oracle Temporary table Space reasons for excessive use and sizing In Oracle how to find which operations/queries are causing excessive use of Temporary table Space and how to estimate its size?

you can use this query to find whats exactly is filling temp tablespace

SELECT a.username, a.SID, a.serial#, a.osuser, b.TABLESPACE, b.blocks,
c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address = a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.TABLESPACE, b.blocks;


Website is too slow when number of records increases? I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system working smoothly when the number of users is low. But the number of users over 50,000, then the system gets slows.

The problem is users comes into the system is like binary trees, and when he/she enter into the system, a commission is distributes  up to top level(Root node or admin). A tree traversing is need to be done on each registration. It takes some more time. Five (or more) types of commissions must be given to each user register in the system, when 100,000 users in the system 500,000 records will be there. Then the system gets too slow. And 600-1000 users will be online. They can't do anything because the slow.

At last we consolidate the 500,000 records to 100,000 (we keep the sum only), the n its OK. They can use the application. We changed InnoDB to MyISAM, its faster but doesn't support transaction.  But we should keep the original records.
How it to be done ?

EDITED - 1

I am listing the server features here.

Operating System : CentOS
RAM : 25 GB
Hard disc : 1500 GB
Current Data Base size : 800 MB
MySQL Version : 5.1 (or above)
PHP  : 5.3

MySQL error “Deadlock found when trying to get lock; try restarting transaction”

We are running a cron job (daily basis), it needs more time to complete because complex calculation is running in the huge database.  When a user register or login to the system, then shows the above error. I just look here and here dealing with the same error, but i didn't get the exact solution. We are doing lot of SELECT / UPDATE / INSERT

EDITED - 2
This is the database structure I used, also other table are there, but I think it is not important now.

The function I used is here

public function getAllUplineId($id,$i,$position,$profile_field_id){
$select = "SELECT father_id
            FROM 161_ft_individual
            WHERE id=$id ";
$result = db_query($select);
while($node = db_fetch_object($result))
    {
    $user_id = $node->father_id;
    if($user_id > 0)
        {
        $amount = 1;
        $this->update_profile_value($user_id,$profile_field_id,$amount);
        $i=$i+1;
        $this->getAllUplineId($user_id,$i,$position,$profile_field_id);
        }

    }
}

public function update_profile_value($user_id,$profile_field_id,$amount){
$query = "UPDATE 161_ft_individual SET total_leg=total_leg+$amount
            WHERE id='$user_id' LIMIT 1";
$result = db_query($query);
return $result;
}

The getAllUplineId() function takes and update all upline users from the point who register. The number of users more than 50,000 (in table 161_ft_individual) it has become too slow and nobody can do anything.  The table 161_leg_amount has 500,000 records.

Thanks in advance.

The basic problem you have is that everything is recursive and you are attempting to lock pages of data in InnoDB, creating lots of MVCC info. In some case, you may have attempted to lock the same InnoDB page repeatedly. That explains the deadlocks. I have commented on this situation deeply with someone in three separate posts on this same issue:

Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?
Reasons for occasionally slow queries?

You need a workaround. You may want to try the following algorithm:

For this example

id = 12
amount = 22.75

Step 01) Make a table to collect all father_ids to be updated

DROP TABLE IF EXISTS fathers_to_update;
CREATE TABLE fathers_to_update
(
    father_id INT NOT NULL,
    PRIMARY KEY (father_id)
);

Step 02) Making a table that will be a FIFO queue. Put the value of 12 into the queue:

DROP TABLE IF EXISTS fathers_to_queue;
CREATE TABLE fathers_to_queue
(
    father_id INT,
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);
INSERT INTO fathers_to_queue (father_id) VALUES (12);

Step 03) Get the Count of fathers_to_queue

SELECT COUNT(1) FROM fathers_to_queue;

If the count is 0, goto Step 07)

Step 04) Remove the front number in fathers_to_queue

SELECT MIN(id),father_id INTO @frontndx,@dad FROM fathers_to_queue;
DELETE FROM fathers_to_queue WHERE id = @frontndx;

Step 05) Collect all father_ids from 161_ft_individual whose id is @dad into the queue

INSERT INTO fathers_to_update SELECT father_id FROM 161_ft_individual WHERE id = @dad;
INSERT INTO fathers_to_queue (father_id) SELECT father_id FROM 161_ft_individual WHERE id = @dad;

Step 06) Go back to Step 03

Step 07) Perform a single UPDATE of all records in 161_ft_individual you collected in fathers_to_update

UPDATE fathers_to_update A
LEFT JOIN 161_ft_individual B
USING (father_id)
SET B.total_leg=B.total_leg+@amount;

That's it. I basically performed a preoder tree traversal back up the hierarchy to all records until father_id was NULL, which makes Step 03 stop collecting father_ids.

Give it a Try !!!

UPDATE 2011-12-16 12:18 EDT

Back on October 24, 2011, I actually wrote an algorithm in the MySQL Stored Procedure language to perform preorder tree traversal: Find highest level of a hierarchical field: with vs without CTEs . Hope it helps !!!


Is it possible to have one Sql-Server cluster node in multiple Sql-Server clusters? Is it possible to create FAILOVER CLUSTER A with Node01 (active) and Node02 (passive) and FAILOVER CLUSTER B with the same Node02 (passive) and Node03 (active)?

I realized that SQL Server instances on passive nodes are not charged, only the OS. So sharing a node for more than one passive Sql-Instance would make sense here. I know, once cluster A and cluster B have an issue I am nipped...

But is this an allowed and/or common scenario?

Is it possible to create FAILOVER CLUSTER A with Node01 (active) and
  Node02 (passive) and FAILOVER CLUSTER B with the same Node02 (passive)
  and Node03 (active)?

No you cannot. This is enforced by Windows Server Failover Clustering.

  I realized that SQL Server instances on passive nodes are not charged,
  only the OS. So sharing a node for more than one passive Sql-Instance
  would make sense here. I know, once cluster A and cluster B have an
  issue I am nipped...

There is nothing stopping you from creating a 3-node cluster (all nodes in the same cluster) and installing two SQL Server FCIs. You'll get roughly the same results.


SQL Server 2000 performance I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by day.

This is a corporate SQL server and we have never had a DBA to help with the server and it has gotten to the point where pulling 1000 records from one table can take 20 min.

The cpu is not pegging above 50% nor is the ram going above 2 gig of usage.

The I/O reads however are at 1.9 trillion reads in the last 48hrs.

I need some direction on how to attack this issue.

I heard something about having rowguid's causing issues with tables with millions of rows is this accurate?

sql 2000 does have some performance tools built into them.  Run these on your queries to see if they give you any help.

Query Analyzer
Profiler
Index Wizard
Performance Monitor


Set a filegroup to readonly I have a set of dimensions which are updated only during specific times.

I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it will set the filegroup to read-write, run, then set it back to readonly.

But when I run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, I get the message Database state cannot be changed while other users are using the database 'MYDB'.

What's the least intrusive way of avoiding new transactions, waiting current ones to finish, then run this command? Is there a way to make a DB single user for some time, or make it offline, whatever?

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:

  For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).

Usually people just want to kick all users out right away, so they use:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

In your case, if you want all existing activity to continue until you get your turn, this will wait indefinitely:

ALTER DATABASE MyDB SET SINGLE_USER;
-- alter filegroup code here
ALTER DATABASE MyDB SET MULTI_USER;

If you want to give some time but not wait forever, you can say something like:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK AFTER 300 SECONDS;

Which will wait 5 minutes and then roll back any remaining transactions. RESTRICTED_USER might be an option for you as well, in case SINGLE_USER allows agent or other background threads from taking your connection.

I don't know of any way to set the state of the database so that existing transactions are allowed to finish and no new connections (or new transactions from existing connections) are possible.

Some information here:

Set a Database to Single-user Mode (MSDN)
ALTER DATABASE SET Options (MSDN)


Reclaiming space from data files in Oracle Is it good to reclaim space using resizing datafiles? What are the things to be taken care before resizing the datafile?

If the data files are growing then any file space you reclaim will be reused as the files grow.

Reasons that cause me to think about re sizing are:

after moving a table/partition/index from one table space to another
after truncating a large table (example: a logging table)

Reasons not to re size:

data files have to be empty to re size below the high water mark, 11g offers online reorganization where the files do not have to be empty
from here: a change that demands some preparation:

take a backup
Export the schema objects that are stored in the specific tablespace/datafile you want to resize.
Drop the exported objects.
Resize the datafiles.
Import the objects.
Perform any required maintenance, like grants, recompilation, rebuild indexes

requires downtime
for a growing table the space will be used again anyway


NULL Agent Timing Out Error:

  Replication-replication Distribution Subsystem: agent (null) scheduled
  for retry.  Query timeout expired.

Based on my research, this error appears when there is a lot of contention on the distribution database.  I do see a distribution query running for over 10 hours.  If I kill this transaction, won't this cause issues with the replication since this may be a transaction that is reading and copying data?  Wouldn't killing it suspend that process and possibly cause corruption.

The publisher and subscribers are all online; these query timeout errors seem to be related to the distribution server and the distribution database contention.  I'm trying to identify what's happening overall, since I have many publications that are getting timeouts and from digging into the servers, it only appears the distribution server is behind.

First, examine what the distribution agent saved about the error by executing select * from [dbo].[MSrepl_errors] order by id desc in the distribution database.

When it comes to poor performance in the distribution database, I've experienced several problems. Besides excessive blocking, which I'm sure you are watching, there are several maintenance related issues. First, I've had virtual log file issues slow my replication in the past. When this happened, there was a noticable impact on performance. To address this run dbcc loginfo on the distribution database. If you get over 100 records returned, it's a slight concern. If you get thousands of records returned, then it's a real problem. Fixing it includes changing the autogrowth settings for the log file to a reasonable value, shrinking the log file, then re-initializing it back to its original size. Unfortunately, the default autogrowth size for data files is 1MB, which should be increased as well.

If you don't have this database included in the index and statistics maintenance, you could experience query timeout issues. I once had a specific table in a user database used by a procedure with a recursive function and if this table got fragmented slightly, the procedure would run very long.

It is also possible to use Profiler or Extended Events to trace all of the commands that the problematic distributor executes to determine which command times out. Locate the SQL Agent job that runs the distribution agent with the issue and filter by program name using that job name.

I'm also not averse to creating new indexes in the distribution database, if necessary. I use the query below to help with this, but don't just create everything without reviewing them to see if you can consolidate.

    USE distribution

    SELECT so.create_date
,dm_mid.database_id AS DatabaseID
,dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) Avg_Estimated_Impact
,dm_migs.last_user_seek AS Last_User_Seek
,OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
        AND dm_mid.inequality_columns IS NOT NULL
        THEN '_'
    ELSE ''
    END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
        AND dm_mid.inequality_columns IS NOT NULL
        THEN ','
    ELSE ''
    END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
,dm_migs.user_seeks
,dm_migs.user_scans
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON
dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
ON
dm_mig.index_handle = dm_mid.index_handle
    JOIN sys.objects so
ON
so.object_id = dm_mid.OBJECT_ID
    WHERE
dm_mid.database_ID = DB_ID()
    ORDER BY
Avg_Estimated_Impact DESC

If all of this still does not resolve the issue, then locate the job for the distribution agent and add the parameter -ErrorFile (path and filename local to the server). Monitor the growth of this file because it might grow faster than you expect, but it will contain more details about the problem.


MySQL Explain: Using index, using temporary, using filesort. Can this query be improved? I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table.   In a report I'm writing, an object can be selected to view statistics against.  The interface shows all objects in order of decreasing importance (ie, hits).

The schema for the two tables (slightly trimmed down, but you get the gist):

CREATE TABLE IF NOT EXISTS `event_log` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(5) DEFAULT NULL,
  `object_id` int(5) DEFAULT NULL,
  `event_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `user_id` (`user_id`),
  KEY `object_id` (`object_id`)
);

CREATE TABLE IF NOT EXISTS `lookup_event_objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_desc` varchar(255) NOT NULL,
  PRIMARY KEY (`object_id`)
);

The query I'm having trouble with is below.  It works fine with my table of ~100 entries, but the EXPLAIN worries me a little.

    explain SELECT
            el.object_id,
            leo.object_desc,
            COUNT(el.object_id) as count_rows
        FROM
            event_log el
            LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
        GROUP BY
            el.object_id
        ORDER BY
            count_rows DESC,
            leo.object_desc ASC

Returns:
Using index; Using temporary; Using filesort

So -- what's wrong with my schema and/or query for MySQL to fall back on temporary and filesort?  Or is it as optimized as it can get using ORDER BY?

Here is your original query

SELECT
    el.object_id,
    leo.object_desc,
    COUNT(el.object_id) as count_rows
FROM
    event_log el
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC
;

It looks as if it is as optimized as it is going to to get

I would change you query

SELECT
    el.object_id,
    leo.object_desc,
    SUM(ISNULL(leo.object_id)=0) as count_rows
FROM
    event_log el
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC
;

This might get you a more accurate count, especially for event logs with no lookup objects.


Error in database export I'm using Oracle 11g databse. For logical backup, we are exporting database on daily basis using below command  and dump size is almost 12GB. , but now I'm facing an issue with my expdp.

expdp 'sys/sys#123@Test as sysdba' DUMPFILE=Backup_04042017.dmp LOGFILE=BakupLog_04042017_1603.log FULL=y consistent=y

Export gets completed successfully, but two tables are not getting exported. The following error is shown in the log.

ORA-31693: Table data object "Owner"."Table_1" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

As read from many blogs, I've set undo_retention value from default to 36000 and set the retention for its corresponding table using below command.

alter table OWNER.TABLE modify lob(DESCRIPTION_HTML_) (retention);

It doesn't work as expected. So, I've set PCTVERSION to 40% using below command.

ALTER TABLE OWNER.TABLE MODIFY LOB (DESCRIPTION_HTML_) (PCTVERSION 40);

My bad, that too didn't work.
Also I tried to export the single table which also given me the same error.

Can I set PCTVERSION more than 40%? Does it inversely affect my production database?

Following are the results that I got related to undo size where my optimal undo_retention is showing lower value than undo_retention. Moreover, needed_undo_size is greater than actual_undo_size.

also,

Do I need to increase undo_size? How it should be calculated based on my database configuration?

Do I need to increase undo_size? …

Yes, that is exactly what you need to do. The export needs a consistent MVCC snapshot from the start of the operation. UNDO_RETENTION is ignored if your UNDO tablespace is fixed size or can't be extended:

  UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

  … How it [undo size] should be calculated based on my database configuration?

It needs to be calculated based on how quickly you are generating UNDO (with DML), and how long the export takes — you'll need to find a size that works for you or try and do the export when less UNDO is being generated, or speed up the export. If you have the space available, increasing the size of your UNDO is the easy solution.


SQL server is getting stuck all of a sudden I manage a virtual SQL server that holds several databases for some applications that run in a front-end server. The issue I'm going through is that, at a specific moment during the day, the server gets stuck and the instance becomes unreachable. I can RDP the server but I cannot connect neither from SSMS nor from the application. I have searched into the OS logs and into the SQL server error logs but I find nothing relevant that could give a hint about what is causing the SQL server to become unresponsive. As a consequence, I have to do a stop-and-start process with the SQL server service and after that the instance will become available once again.

Anyone who might have experienced this in the past? Thanks.

Try running sp_Blitz, a free health check for your SQL Server (disclaimer - I'm the author).  You can also run it with @OutputType = 'markdown' if you want to share the results here at Stack.

I've got a hunch that you're running into THREADPOOL waits, and sp_Blitz alerts about that. THREADPOOL means your SQL Server ran out of worker threads to service incoming queries. It won't show up in the OS or SQL Server error logs.

When it's happening, you'll be able to connect to SQL Server using the Dedicated Admin Connection (DAC) (disclaimer: that's a blog post on my site.) The DAC is a set-aside CPU scheduler used just for emergency troubleshooting. From there, you'll be able to see which queries are burning up all the worker threads - typically it's a blocking problem.


How to avoid modifications during pg_dumpall I want to do a final pg_dumpall on a server, then copy the output to a different server and restore the db there.

How can I avoid modification to the database during pg_dumpall?

From the pg_dump documentation :

  It makes consistent backups even if the database is being used
  concurrently.

So the backup process takes a snapshot of the DB on a specific moment so each commits which are made during the process are ignored.


Regularly updating table with huge amount of rows I have a table containing (in addition to some metadata) a score and a boolean flag:

CREATE TABLE scores
(
  score_id        SERIAL NOT NULL
    CONSTRAINT score_pkey
    PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  score           DOUBLE PRECISION NOT NULL,
  flag            BOOLEAN NOT NULL
);

There are multiple indexes for querying the data:

CREATE UNIQUE INDEX score_pkey ON scores (score_id);
CREATE INDEX ix_scores_user_id ON scores (user_id);
CREATE INDEX ix_scores_score_desc ON scores (score desc);
CREATE INDEX ix_scores_flag ON scores (score_id) WHERE (flag = true);

The table currently contains around 120 million rows and for most of them, the score value is updated once a day. The flag column may be toggled (only from default value false to true but then never back to false) at any point of the day and independently from the score updates.

Scores are generally only updated for some users (around half to one third currently) and where the flag is set to false.

The scores are calculated on a worker machine and updated in batches (ca 3000 at a time) in a single update-query. There is no way of calculating the data on the database server. The query looks like this:

UPDATE scores as s
SET
    score = tmp.score
FROM (
    VALUES (32373477, 0.5566874822853446), (32373478, 0.5243741268418393)
) AS tmp(id, score)
WHERE tmp.id = s.score_id

The huge amount of updates causes some issues with our database as the updates require a lot of disk I/O (index updates and column rewrites), increases the queue depth and thus slowing down other queries.

My primary goal is to reduce the I/O writes, speed up the update process and reduce load on the database.

What are my options here?

It is possible for me to move the flag column to another table and do a complete rewrite of the score table. May this be more performant than updating only a selected amount of rows (~one third is updated each day)?

Alternatively, is writing all changed rows to a separate, index-less table and updating the primary table in-database a preferable solution?

Does anybody have some experience with and solutions for a similar kind of problem they are able to share?

The database server is a m4.large (2 cores, 8 GiB memory, 250 GiB storage, 750 IOPS) PostgreSQL 9.6.6 instance.

It is possible for me to move the flag column to another table and do a complete rewrite of the score table. May this be more performant than updating only a selected amount of rows (~one third is updated each day)?

This is probably the way to go, if you can get away with batching the updates up to the daily level (that is, your "business logic" does not depend on seeing the updates closer to real time in order to get correct-enough answers).  Create the new table, then build the indexes, then in one transaction drop the old one and rename the new one into place.  This renaming into place can be a bit tricky if there are foreign keys, triggers, views, user defined functions which reference it, etc.  You don't even have to move the flag column to a separate table, if you make carrying the value of the flag over to the new table part of its creation process.

  Alternatively, is writing all changed rows to a separate, index-less table and updating the primary table in-database a preferable solution?

This is unlikely to solve the problem, unless you can drop the indexes while that update is in progress, or you have an off-peak window where you just don't care about performance.  Current versions of PostgreSQL do not have a provision for efficient bulk update of live btree indexes.  Your IO queue depth problem would be concentrated into a shorter period, would be more severe during that period.


PostgreSQL 9 'pgstats wait timeout' problem I'm asking about this here because in the PostgreSQL mailing lists no one answered me.
In one of my servers (Windows 2008 R2 Standard/PostgreSQL 9.0.5 64 bits) the logs are being flooded with the following message:

2012-03-26 00:00:11 BRT AVISO:  pgstat wait timeout
2012-03-26 00:00:19 BRT AVISO:  pgstat wait timeout
2012-03-26 00:00:31 BRT AVISO:  pgstat wait timeout

I checked other threads in the PostgreSQL mailing lists that were answered, but there are no definitive answer. Some speculate about I/O overload, but I don't think it would cause this message to be logged almost all the time that the server is up.
Do you, that use the combination of Windows+Postgresql, have ever had this problem? If yes, were you able to fix or identify the problem?

Thanks

Reloading the config makes the errors go away, and performance improves (and db statistics start getting recorded again).

I've set up a Windows Scheduled Task which performs a pg_ctl reload -D"insert here the address of your pg directory" on the hour (we're running an incredibly busy application). Hacky, but it's working.


Optimization on table selected where primary key I have this query:

SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
    AND (wp_dev_posts.ID = wp_dev_postmeta.post_id)
    AND (wp_dev_posts.ID = wp_dev_term_relationships.object_id);

My question, is that when I have this in the where:

wp_dev_posts.ID = wp_dev_term_relationships.object_id

What's the correct way to optimize this? Am I better to use joins? Do I set index's? Do I use foreign keys? On a large database this is actually take a few seconds to run.

SQL newbie here.

FYI, this is Wordpress, with 33k rows in posts, about 250k rows in postmeta.

Generally speaking, you'd want to rewrite that query like this:

SELECT *
FROM wp_dev_posts
    INNER JOIN wp_dev_postmeta
        ON wp_dev_posts.ID = wp_dev_postmeta.post_id
    INNER JOIN wp_dev_term_relationships
        ON wp_dev_posts.ID = wp_dev_term_relationships.object_id
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));

Notice, I've moved parts of the WHERE clause to the FROM clause, using the ON construct.  That makes the query easier to read.

To optimize access to the table, you'd generally like to have indexes on wp_dev_posts.ID, wp_dev_postmeta.post_id, wp_dev_term_relationships.object_id, and post_type.

It might make sense to create a compound index on wp_dev_posts for (post_type, ID).

You'd probably want a foreign key on wp_dev_postmeta pointing to the primary key defined on wp_dev_posts.ID.

Providing more details about your specific environment, including the definition of the tables, any existing queries, row counts, etc, would help us provide a more definitive answer.


Nosql vs sql for genre management I'm looking to keep a simple database for movies, with details like Name, YearofRelease, Genre etc. The key factor is the genres entry, where a movie could fall into multiple genres. Using a sql based approach, one way is to have a separate table for each genre and enter to movie id. Using a nosql mongodb approach, the generes can be stored as an array.
From the purpose of both storing and searching by genres, what is the better approach?

The only real questions to mainly be considered when choosing between NoSQL and a Relational Database Management System (RDBMS) are:

"Does the data have a well defined structure or is it very unstructured / variable in structure?"
"Are the questions I need to ask of the data always simple?"
And less important but valid: "Do I need to distribute the data across multi-nodes of servers in a cluster, or having one centralized server is sufficient?"

For #1 it sounds like you have a well structured set of data. In fact, it's a very simple structure and should be very easy to store and maintain in a RDBMS. And if you go with a RDBMS then to support multiple Genres for multiple Movies all you would need is one additional linking table (aka bridge / junction table) called MoviesGenres that would be many-to-many with Movies by storing the MovieId and GenreId, one row per combination. You wouldn't need a "separate table for each genre" to solve this, just the one additional table MoviesGenres. So if MovieABC had Genre1 and Genre4 then there would be two rows in your MoviesGenres table for the records (MovieABC, Genre1) and (MovieABC, Genre4).
For #2, that's going to be up to your use cases. If you ever want to ask more complex questions like "How many movies with 3 or more genres were released after 2020?" or "Which movies have both Genre7 and Genre9, and were released before 2000?", etc, then a RDBMS makes it much easier to query these kinds of things and probably are more efficient at doing so (generally speaking).
For #3, this just depends on the scale of your data and your preference of how to maintain the hardware behind it. If you plan to have many terabytes of data and multi-billions of rows, do you prefer to pay for a multi-node cluster to horizontally scale the data at the tradeoff that it may not always be consistently in sync across every node in the cluster, or would you prefer to centralize all the data to one main server and scale vertically as the data grows. This is kind of a moot point nowadays, since even RDBMS now exist with features to support horizontal scaling too. So unless you foresee storing a very large amount of data upfront, I wouldn't be as concerned with this question as the prior two.
In general, based on what information you have provided, my recommendation would be to use a SQL (RDBMS) solution so you have the flexibility to query it as you need in a performant manner, given the fact you have a well defined and simple structure for your data.


What principles should I follow when entering data into a complex relational database? I'm new to Databases, so forgive me if this is a basic question.

I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex:

I understand how to insert data into a table, but what I don't understand is how one should insert data into multiple tables that rely on one another for foreign keys. To give a simplified example:

I've got three tables: Entry, Keyword, and Keyword_Priority. Every entry can have multiple keywords, so the Keyword table links back to Entry through a foreign key. And each keyword element can have multiple elements describing the priority of the keyword, so the Keyword_Priority table links back to Keyword via foreign key, as below:

In this simplified scenario, let's say I want to create a new entry using SQL. Where do I start? Do I:

Insert into the Entry table first to create a new EntryID
Then Insert into the Keyword table using the new EntryID as a Foreign Key
Then Insert into the Keyword_Priority table using the Keyword's ID field as a Foreign Key

Or alternatively, should I do it the other way around ...

Insert into the Keyword_Priority table
Insert into the Keyword table
Insert into the Entry table

So to sum up, my question is:

Where should I start when inserting data into multiple tables at once? From the top-down, or from the bottom-up?

A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start by inserting in the tables on the 1-side of the relation (the master or parent or primary key table). Then only you can enter a record on the n-side of the relation (into the detail or child or foreign key table).

Order: 1. Entry, 2. Keyword, 3. Keyword_Priority.

If a table has several foreign keys, then the related records in all the other tables with the corresponding primary keys must be entered first. For instance, before entering a record into SenseRestricted..., the corresponding records in Sense and in Reading must have been inserted.


Search for authors whose records are separated by at least X days I am using Postgres and have a table which contains metadata about several million images - many of which have the same author.

I am looking for contributors who add content regularly - not people who add images in "bursts" of many photos in a short period of time. In other words, I'd rather people who post six photos over a matter of months than 12 photos in just one week, but never post outside that short time window.

Writing a statement like SELECT count(*) AS i_count,author_id FROM images GROUP BY author_id ORDER BY i_count DESC will give me a list in descending order of who has contributed the most photos to the least, but how might I be able to select only people who have contributed images in a period that is greater than 30 days?

I have a date_added column as well.

A check in the HAVING clause would do the trick:

SELECT author_id, count(*) AS i_count
FROM   images
GROUP  BY author_id
HAVING max(date_added) > min(date_added) + interval '30 days'
ORDER  BY i_count DESC;


MySQL: Is there any possible way to get notified about mysql-server FOREIGN KEY CONSTRAINT violation during INSERT, if FOREIGN_KEY_CHECKS=0 ? Plese don't bite me, i'm a newbie here ...
So here is the question:

Is there any possible way to get notified about mysql-server FOREIGN KEY CONSTRAINT violation during INSERT, if FOREIGN_KEY_CHECKS=0 ? Is it possible to recive this info at my php code? Something like mysql warnig ? Any info would be useful.

Thank you

Check out here - the point about using this system (or session) variable is to load tables in an order which is " different from that required by their parent/child relationships". You switch it off for that reason - if you want warnings of breaches of RI (Referential Integrity), then set it to 1 and you will get warnings. It can be set on the session level.


PostgreSQL - Check value is not the same in multiple columns I have a table to hold user information and is used as part of the login/registration process.
Part of the requirement is to allow the user to login using either their username or email address.
A snippet of the table is below and DB is PostgreSQL 12.4
CREATE TABLE account_users
(
    id            int          default nextval('postgres.account_users_id_seq'),
    username      varchar(100) not null unique,
    email         varchar(100) not null unique,
);

However this has raised a concern that the username field could be set to an email address of another user, which could cause a potential issue with users being able to login to their account as the SQL to get the users password from the database is:
SELECT password FROM account_users WHERE username = $1 or email = $1 LIMIT 1;

What I'm looking for is a way to ensure that the value in username and email are not the same for any row
For example:
username          | email
----------------------------------------------
user1             | user1@example.com - OK
user2             | user2@example.com - OK
user3@example.com | user3@example.com - Not OK
user1@example.com | user4@example.com - Not OK

Is this possible to do at the DB level or would it be something for the application to handle?
There is going to be validation done at the application level, client-side and server-side, however would a be nice to have if the DB could also catch this as well.

To ensure that the e-mail field has priority, use ORDER BY:
SELECT password
FROM account_users
WHERE username = $1
   OR email = $1
ORDER BY email = $1 DESC
LIMIT 1;

Alternatively, use UNION ALL, which might be easier to understand:
SELECT password FROM account_users WHERE email = $1
UNION ALL
SELECT password FROM account_users WHERE username = $1
LIMIT 1;

Is there any reason to allow @ in the user name?
username      text not null unique check (username !~ '@'),
email         text not null unique check (email    ~  '@'),

(You might allow the user name to be an e-mail address if it is the user's own.)

A CHECK constraint is not allowed to look at other rows. So you would have to use triggers (on any INSERT or UPDATE, check that no other row with the same value exists).


Attaching MDF without LDF I only have an .mdf file of my database which is not attaching.

I am using the following command:

EXEC sp_attach_single_file_db @dbname = 'Test_Data',
@physname = N'D:\Test_Data\Test_Data.mdf';

and I get the following error message:

  Msg 1813, Level 16, State 2, Line 1
  Could not open new database 'Test_Data'. CREATE DATABASE is aborted.
  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:24; actual 0:0). It occurred during a
  read of page (1:24) in database ID 11 at offset 0x00000000030000 in
  file 'D:\Test_Data\Test_Data.mdf'.  Additional messages in the SQL
  Server error log or system event log may provide more detail. This is
  a severe error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.

Please help!

Your MDF is actually corrupt, based on that error message. This means you can't attach it.

You could create a database with the same name, substitute MDF (and rename LDF), and hope it comes online or at least suspect to start recovering it.

Otherwise, you'll need to find a 3rd party tool that understands MDF files to get data back.

Or just restore from your latest backup...?


Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in error by a third-party application with strings, so that column needs to stay as is.

The table is often joined to one that stores that value as an INT. I defined a PERSISTED computed column that converts that VARCHAR to an INT:

[iOrderNumber] AS (CONVERT([int],
case
   when [ordernumber] like '#%' then (-99) when isnumeric([ordernumber])=(0) then (-99)
   when CONVERT([bigint],[ordernumber],(0))>(2147483647) then (-99)
   else [ordernumber]
end,(0))) PERSISTED NOT NULL

With an appropriate index on the computed column, performance dramatically improved for queries that could now join to the INT column. However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

I tried using a UDF in the computed column definition to get rid of the warning (I found a blog post that suggested that), but then execution of my queries took much longer and used much more CPU although the logical reads stayed the same. But the UDF did eliminate the warning.

Is there a reason to consider the warning anything other than a bug? Is there a reason to consider the optimizer's handling of a persisted computed column defined with a UDF as anything other than a bug?

More importantly, is there any way to get rid of the warning without incurring the performance penalty from the UDF solution?

I considered using a trigger and a translation table containing the VARCHAR and INT versions of the data instead of using a computed column, but that seems like a lot of unnecessary overhead.

However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

Persisting a computed column does not guarantee that the persisted value will be used. The optimizer makes a cost-based decision between using the persisted value and computing the expression afresh, though there are also other factors in play. Simplifying, the process looks like this:

The computed column reference is always expanded to its definition before query compilation and optimization begins. This is very similar to the way view references are handled (unless indexed and referenced with a NOEXPAND hint). The expansion provides the greatest opportunity for orthogonal simplifications and optimizations to be applied.
Depending on the query and the code path taken through the query optimizer, a cardinality estimate may be requested for an expression in the expanded computed column definition. Deriving this cardinality estimation may result in a plan-affecting convert warning being added to an internal warning list.
The expanded computed column expression may be matched back to a persisted column or index later in the compilation and optimization process. Any plan-affecting convert warning added previously is not tracked and removed when this substitution occurs.

Like most warnings, the plan-affecting convert warning is opportunistic and informational. It is opportunistic in that it is only added if the optimizer follows a code path that attempts to compute cardinality on a suitable expression. This is similar to 'missing index suggestions', which are only added if the optimizer attempts to match to an 'ideal' index definition that is not found. To put it another way: neither of these facilities are based on exhaustive analysis.

  I tried using a UDF in the computed column definition to get rid of the warning

This 'works' because the optimizer cannot expand a scalar UDF into its definition before optimization. The UDF is a 'black box' with guessed cardinality and horrible runtime performance (close to the cost of running a complete separate query per function invocation). Without expansion, a cardinality estimation that might generate the warning cannot occur.

  Is there a reason to consider the warning anything other than a bug?

The warning indicates that the convert may affect plan quality because a cardinality estimation was performed on a problematic expression. The complexity of compilation and optimization is such that it is impossible to say if the cardinality estimate will affect the final quality of the plan or not, even if the computed column does end up being resolved to a persisted value or index.

So, the warning is generally useful because it indicates that internal optimization decisions may have been adversely impacted by the convert. I would always check a plan with this warning for inaccurate cardinality estimations, and any resulting performance or resource usage implications.

A final aside: the SQL Server version is not mentioned in the question, but for 2012 and later, TRY_CAST or TRY_CONVERT are more robust ways of handling this sort of requirement. This facility will not generally affect convert warnings one way or the other though.


Which instance's timestamp is used as SCN in Oracle 12c rack? Let us assume 4 oracle12c rack instance's are running on different nodes. Each node has its own clock. No two clock shows the same time.

Which instance's timestamp is used by Oracle as SCN?
And also upon how linearization is achieved.

SCN actually has a 3 second granularity now. As far as RAC goes, all cluster nodes must have synchronized clocks using NTP or some such; if the system times get too out of sync the cluster will start evicting nodes, trying to get them on the same page again. For more details on what SCN is and how it works, check these links:

http://www.dba-oracle.com/t_rac_scn.html
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48052838748707
https://www.red-gate.com/simple-talk/sql/oracle/oracle-system-change-number-an-introduction/