Is it recommended to install extensions into pg_catalog schema?

Don't install extensions to pg_catalog (unless that's their default: very few extensions are designed that way), because you don't mess with system catalog, ever. @Chris demonstrates one reason why. There are others.

However, the "public" schema is in no way special. It's just the default schema that's pre-installed in standard distributions so we can get started right away. Some DB admins don't use the "public" schema at all, some even delete it.

CREATE EXTENSION is not affiliated to the "public" schema. It installs into the current schema unless instructed otherwise - except some extensions have a pre-set schema (like PGQ / Londiste). The documentation:

  schema_name

  The name of the schema in which to install the extension's objects,
  given that the extension allows its contents to be relocated. The
  named schema must already exist. If not specified, and the extension's
  control file does not specify a schema either, the current default
  object creation schema is used.

  Remember that the extension itself is not considered to be within any
  schema: extensions have unqualified names that must be unique
  database-wide. But objects belonging to the extension can be within schemas.

Bold emphasis mine.
Decide how to manage users, schemas and the search_path:

How does the search_path influence identifier resolution and the “current schema”

Then decide where to install extensions. You can install to any schema of your choice and include that schema in the default search_path for all users or just for some or no users at all (so that qualified references are required). It all depends on what you want to achieve.
Whatever you do, stay consistent.

I like to install extensions (that allow it) in a dedicated "extensions" schema, which I include in the default search_path after "public" (and "$user" - if you use that). Helps with a clean separation of my own public functions and other public objects. My setting in postgresql.conf:

search_path = "$user",public,extensions

Or:

search_path = public,extensions

And I install extensions with:

CREATE EXTENSION some_extension SCHEMA extensions;

One thing to note: This way you can "hide" (unqualified) objects in the extensions schema behind objects of the same name (and parameters) in the public schema.

Related:

Best way to install hstore on multiple schemas in a Postgres database?
How can I fake inet_client_addr() for unit tests in PostgreSQL?


Update the value when the row already exists

As mentioned in comments on the question, MERGE is the answer you are looking for.

Check out this great blog as well:

MERGE statement explained by Serge Rielau (瑞赛奇) and Rick Swagerman.

It will look somewhat like this for your case:

MERGE INTO <tab> AS ...
  WHEN MATCHED THEN
     UPDATE ...
  WHEN NOT MATCHED THEN
     INSERT...


Using Inteval Datatype to store result of calculation

I don't believe you can.

The expression you use to create a virtual column must be deterministic, i.e. always return the same value when called with the same arguments. Involving the current date is fundamentally incompatible with that requirement.

You could use a plain old view though.

create table mytab (hiredate date);
create view myview
as
 select
   hiredate,
   numtodsinterval(sysdate-hiredate, 'day') delta
 from mytab;

insert into mytab values (to_date('20120101', 'YYYYMMDD'));
commit;
select * from myview;

Output:

HIREDATE        DELTA
01/01/12    247 9:15:55.0


Converting unix timestamp to an sql timestamp in postgres

to_timestamp accepts the epoch value ("Unix time") with second precision, while your value seems to have millisecond precision. You need to do something like

to_timestamp(@date/1000) AT TIME ZONE 'UTC';


Query problem: there can be only one auto column

This will apparently work with MyISAM as storage engine, not InnoDB, if you can live with that.

Another way to get it to work is if you swap places of queue and idx in the primary key declaration.


Do you have to use a shared disk with AlwaysOn FCI?

There's two main forms of failover you can use with SQL post 2012,

The Windows Failover Cluster (WFC) which has been around for a while and the Always on High Availability Group(AO)

WFC uses a single database which can move between multiple machines this method requires the database to be installed on shared drives (such as a SAN) so that when the cluster moves the node the other machine has direct access to the information that makes up that database

AO uses two completely seperate instances of SQL Server, both instances run at all times, (depending on how close they are you can set them up to be synchronous or a-synchronous, (if they're on the same network synchronous is better if they're separated globally a-sync is a better choice), due to there being two databases you do not require any shared storage, the two machines work completely independently of each other, we currently have ours set up with a quorum witness which is actually on a shared drive but is not technically required (it is advisable to have a witness however)

Worth Noting about using AO, if you use the secondary node for any data access / backups (which you can as it can be accessable through readonly) you will require the extra licences to access it, if it sits there purely as a redundancy for when your primary node fails you do not require any extra licences as only one database will be accessible at any one time, with WFC since there is only actually one instance you only require one set of licences for that instance as it moves, you do have the disadvantage of only one disk failure will cause problems but using a RAID 10 on those disks will limit this from becoming a problem

Ste


How to restore a postgres database created via an older version of postgres?

When I tried to install via Azure Data Studio I got an error:

Restore: pg_restore: [archiver] unsupported version (1.13) in file header

Version 1.13 of the pg_dump archive format was introduced in February 2018 as part of the changes for CVE-2018-1058. The version bump was mentioned in the commit message with a specific warning about the problem:

  Since this change requires a change in the behavior of pg_restore when
  processing an archive file made according to this new convention, bump
  the archive file version number; old versions of pg_restore will
  therefore refuse to process files made with new versions of pg_dump.

That was six months before PostgreSQL 11 was released, so an installation that supports PostgreSQL 11 is not supposed to use an older pg_restore. I'm not sure what exactly is "Azure Data Studio" in terms of the software stack it provides, but it looks like it has an outdated version of pg_restore that is not suitable for your needs.


How to determine if a postgres database needs to be vaccumed?

There is no single parameter that determines, whether vacuum is necessary, but it can be quite interesting to look at the thresholds that postgres uses to trigger autovacuum.

Details and a query to do so can be found in another thread: Aggressive Autovacuum on PostgreSQL.


You may find this useful. It's TwinDB's data recovery kit. According to the site:

TwinDB data recovery toolkit is a set of tools that operate with InnoDB tablespaces at low level.
The toolkit allows to recover data:

After table was dropped
If InnoDB tablespace is corrupt so badly so MySQL doesn’t start with innodb_force_recovery
If file system with InnoDB data is corrupt
If InnoDB files were accidentally deleted
After records were deleted from table

It's from by Aleksandr Kuzminsky, a former Percona employee - Percona are big hitters in the MySQL ecosphere. The source code for the toolkit is available here.


You can accomplish this with pivot, when you are dealing with translating the columns into rows you will still need to utilize an aggregate. The pivot has three primary pieces:

Group
Spread
Aggregate

DECLARE @table TABLE (name VARCHAR(255), code INT);

INSERT INTO @table
VALUES ('Test A', 1), ('Test B', 2), ('Test C', 3);

SELECT [1] AS 'Customer', [2] AS 'Fleet', [3] AS 'Account'
FROM @table
PIVOT(MAX(name) FOR code IN (
            [1],
            [2],
            [3]
            )) pvt;

In the working example above we see the grouping as Name and we spread the results of code across three columns with hard coded values. The aggregate is based on the MAX value of name, this is what let's us pivot the row by pivoting on a group with a max value that would only return one row.

Now if you want to create this dynamically vs hardcoding, you'll need to look into dynamic sql.

Here's an article on dynamic pivot sql on MS SQL Tips by Aaron Bertrand


Returning values for Stored Procedures in PostgreSQL

Procedure are a new thing in Postgresql as of version 11,   there are two big differences between Procedures and Functions

One Procedures can issue a  commit or rollback and keep processing,  Functions can not issue a commit or rollback.

Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is  an Exception Block

Procedure can return a value via INOUT argument   see create functions how that works

create procedure increase(inout i integer )
LANGUAGE 'plpgsql'
AS $$
begin
    i = i+1;
    return;
end ;
$$;

call increase(1);

2nd biggest difference is

select increase () -- fails with an error
-- must use CALL to use a procedure
call increase (3) ;

Unless there is a need to commit or rollback use Functions, there are more features for calling and return values...


Missing "public" schema when converting a "regclass" value to text

This is not related to the schema public per se - which is just another schema with no special powers except that it's created by default - and included in the search_path by default.

The current search_path is behind this. The text representation of a regclass value builds on it. The table name is only schema-qualified if the schema is not the first match in the search_path anyway. The manual:

  The regclass input converter handles the table lookup according to the
  schema path setting, and so it does the “right thing” automatically.
  Similarly, casting a table's OID to regclass is handy for symbolic
  display of a numeric OID.

You could force Postgres to print the schema name by setting an empty search_path (locally):

SET LOCAL search_path = '';
SELECT 'public.tt'::regclass::text;

But that forces you to schema-qualify everything for the rest of the transaction or until you set the search_path again. Well, most everything, pg_catalog and pg_temp are special. See:

How does the search_path influence identifier resolution and the “current schema”

Or you avoid the special cast and the search_path and get the schema name from pg_class directly:

SELECT relnamespace::regnamespace::text
FROM   pg_catalog.pg_class
WHERE  oid = 'public.tt'::regclass;


Why doesn't \COPY work when called from a PL/pqSQL function?

TLDR: \copy is the psql instruction, not a PL/pgSQL command. It cannot be interpreted by the server, only by the psql client itself.

The psql client, as any other database client software, has very limited capabilities. It cannot by itself read or write table rows (because they are physically located at the database server), execute stored procedures and functions (because, again, they reside on the server and execute on the server) etc. All it can do is send an SQL command to the server and present its result to the user.

As an example, when you type select foo from bar in psql and press Enter, the client opens a cursor for the given query text, lets the server process the query, fetches the result set, and shows it to you formatted in some way.

Going back to \copy, here's what documentation has to say:

  Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client.

Similarly, when you execute with psql a script file that contains your function definition:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;

it (psql) doesn't actually execute any of that; it sends the code as a single statement to the server for processing. The server attempts to parse it as PL/pgSQL code (because of LANGUAGE plpgsql), compile,  and store in the database for future invocations. Since \COPY is not a valid PL/pgSQL command, compilation fails with the error you see.

PS. Others may correct me, but I don't think you can achieve what you want in a function even using a proper COPY command, because it would require dynamic SQL, which I think cannot be used with COPY.


Exactly. Which is why some databases use alternative indexing mechanisms for text. SQL Server has a full text indexing capability that works by breaking the string into WORDS, removing bad ones (like "in" in english - happens too often) and then uses a different syntax for finding words in the text index.

Standard SQL Indices are simply not suited for the task.

MySQL, from a 30 second run in google, also seems to have a full text search mechanism.


Index on an input value without saving it to a column?

Postgres supports indexes on expressions so if the value you want to index can be computed from other values in the row this will work (assuming the queries you are using a written in a way that the query planner can see the use for the index). See https://www.postgresql.org/docs/10/static/indexes-expressional.html

For more complex requirements a materialised view might be required, though these appear to need "manual" updates via REFRESH MATERIALIZED VIEW which might make them imprctical depending on your need.

From your later edit:

  or I have to create a view and remember to use that for reads but not for writes

You can use "INSTEAD OF" triggers to make views like this appear to be directly writable, though of course you need to be careful of performance issues if the data is written to often.


There is a way to fix this issue but it may not work all the time, but still it worth a try (note take a backup first):

execute the following command mysqlfrm –server=root:PWD@machine_ip –port=xxxx “/path/to/mytable.frm” > “/destination/path/recovered_mytable.sql”
This will generate  create table script you can you can execute it to create the table
remove  new tablespace for mysql alter table mytable drop tablespace;
copy the original one to the new table location for example cp /home/mysql/x.ibd /mysql/data/mydb/
reactivate your tablespace alter table mytable import tablespace;

for more information about mysqlfrm please refer to https://docs.oracle.com/cd/E17952_01/mysql-utilities-1.4-en/mysqlfrm.html
file can be downloaded from https://downloads.mysql.com/archives/utilities/


Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

You need to make /var/log/mysql fully owned by mysql

chown -R mysql:mysql /var/log/mysql

Then go start MariaDB

Make sure you are logged in as abuser that can start MariaDB

If not, use sudo

sudo service mysql start


Why does the "Date Created" for the master database show such an old date (8 April 2003) in SQL Server 2008 R2?

This is just an educated guess mind you, but I would assume it is the date the new master database was created at Microsoft for SQL Server 2005.

I checked SQL Server 2012, 2014 and 2016 and all have the exact same date/time.


Are auto incrementing identities always given in the order of inserts in SQL Server?

If this is the only transaction on that table, then yes.  However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE isolation.
From IDENTITY (Property) (Transact-SQL) in the product documentation:

The identity property on a column does not guarantee the following:

Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated


Shrink database after removing extra data

After index recreation, using this command:

ALTER INDEX ALL ON Production.Product REBUILD

and than I run SHRINK command again - end up with 2.5G database.


In DB2, the SQL Server's recovery model simple equivalent is called circular.

<p>C:\WINDOWS\system32>db2 get db cfg for sample |findstr "LOGARCHMETH1 LOGARCHMETH2" <\p>
<p> First log archive method                 (LOGARCHMETH1) = OFF </p>
<p> Second log archive method                (LOGARCHMETH2) = OFF </p>

If both logarchmeth1 and logarchmeth2 are set to OFF, the database uses circular logging and is not rollforward recoverable.


IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc.
STABLE can use any inputs that are themselves STABLE: other STABLE or IMMUTABLE functions, and SELECT queries of tables. It's safe to query tables because the function's view of those tables will not change within the query's current snapshot. You can access GUC values (current_setting(...)) so long as you know they won't also be assigned to within the current statement.
VOLATILE functions are everything that doesn't fit the above:

Anything with side-effects
Anything that does writes
Anything that queries external data not managed by the PostgreSQL snapshot
...

In general, just leave everything VOLATILE unless you have a good reason not to.
The main reason to use IMMUTABLE is when writing functions that are to be used as part of index expressions.


How to add time from a Date value to another without a varchar conversion?

Simple addition and subtraction.

Although the code is SQL code, the math works the same in PL/SQL.

This code:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
with data as (
    select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo
         , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual
)
select foo, foo + (bar - trunc(bar)) foo_modified, bar from data

Produces this desired results:

FOO                 FOO_MODIFIED        BAR
------------------- ------------------- -------------------
2019-10-13 00:00:00 2019-10-13 16:33:11 1914-10-01 16:33:11


Postgres database migration "zero" downtime

how can I get close to zero downtime when having database migrations.

Set up a streaming replica pair, with a master and a replica. Use WAL archiving in addition to streaming replication to deal with connectivity drops. Make sure your application is easily reconfigured to switch to a different server for if you need to fail over; if it's not easy to reconfigure, run a local PgBouncer on each node so you can switch it to connect to a different PostgreSQL without needing to mess with the rest of the server config.

A tool like repmgr makes this considerably simpler.

To learn more, read the manual.


How to get max value of a column from one table with its matching record in second table in PostgreSQL?

In Postgres the most efficient way is to do this using distinct on ():

select distinct on (o.id) o.id, o.name, t.edu, t.sortby
from one o
  join two t on o.grade = t.grade_id
order by o.id, t.sortby desc


several idle connections SET bytea_output

Many libraries which manage connections for you will execute some housekeeping queries on the new connection before returning it to the caller.  SET bytea_output='escape' is just the type of housekeeping query you might expect to see.

So the likely explanation is that someone keeps asking for a new connections, and then immediately forgets about it without either using it or closing it.  To figure out who, you should be looking at other fields of "pg_stat_activity", like usename, application_name, and client_addr.

And ultimately if you don't know who is connecting to your database, you should be changing the passwords or other authentication methods.


Postgres jsonb column or standard normalized table?

The rule of thumb in every relational database is normalizing (usually up to 3NF or even 4NF).  With the advent of JSON into the relational world, people are often tempted to solve everything using JSON, as it probably means a very simple way of channeling some data moving in the application into the database.  However, while it has its own place, I see it more often to be the case than not, that the data could be mostly or in its entirety normalized.

Basically whenever you see that your JSONs share the same keys (having values of the same data type), you should go and extract those keys into proper columns.  If the whole structure is always the same, extract everything, create tables as needed (if you have arrays in the JSON, for example, it can be a sign you need a table for that key), and - as basically always in the relational world - do joins between them.

Besides all the problems normalization solves, this has performance benefits, too.  First, you don't store the same key again and again with every transaction, sparing probably a lot of storage space.  Second, in the typical case you can have more efficient indexes over your columns (or groups of them).  Third, you can use primary and foreign keys, and constraints to enforce data integrity (well, this is partly covered in the normalization part...).  Finally, RDBMSes are very good at joining tables and coming up with efficient query plans to retrieve the data you need.

As for keeping a separate table for each year: this can be achieved by paritioning (with recent PostgreSQL versions having important improvements with it).  If you need to do that or not, is hard to tell - it depends on the data volume in the first place, but other factors, too.  You can possibly create your transaction table as partitioned, but only with a single default partition, at least if you opt to use version 11 or later.  In this case, if necessary, you can decide to add yearly partitions later.


SQL SERVER 2008 TVF OR CHARINDEX to search column with comma

You need a split TVF on both sides to make it a JOIN

SELECT DISTINCT
   M.ID
FROM
   MyTable M
   CROSS APPLY
   dbo.SomeSplitTVF(M.Blahcolumn) M2
   JOIN
   dbo.SomeSplitTVF(@parameter) P ON M2.value = P.value

There are several solutions for "dbo.SomeSplitTVF" on SO, but you could start with this article by our own Aaron Bertrand


oracle expdp similar method to export datas for SQL Server

There is no way to restore a partial database from a SQL Server backup file.  If you are looking to export and import data from various tables in one SQL Server database to another, you'll need to use one of the following methods:

BCP export and import
SQL Server Integration Services (SSIS)
SQL Server Replication
A third party tool, such as Informatica.  This is not an endorsement of their product.

This question and potentially others listed in the "Related" section to the left may offer more help.


Access operator added by extension inside a schema

You could schema qualify the operators when you invoke them.

For example, instead of writing

SELECT current <@> destination

You can write:

SELECT current operator(public.<@>) destination

See Operator Invocations for more information


SQL Server Data types and operators clarification needed

Untrue. You can use them but the "approximate" nature means they could be unreliable. Use range queries (BETWEEN etc) or use decimal
If you use the max types (text, image etc are deprecated) then you can CAST or SUBSTRING etc as needed to search and filter
For XML you can search using XQuery
Note: a binary BLOB that is a word document or such can not be searched in SQL Server
Never tried any. timestamp (deprecated, use rowversion) isn't designed to be manipulatedUnless you mean datetime which as datetime functions


Finding what the source of a specific PostgreSQL configuration setting

pg_settings - look for source*

For example, after

alter system set array_nulls to off;

(and reloading configuration)

select * from pg_settings where name = 'array_nulls';

╔═[ RECORD 1 ]════╤════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ name            │ array_nulls                                                                                ║
║ setting         │ off                                                                                ║
║ unit            │ ░░░░                                                                                ║
║ category        │ Version and Platform Compatibility / Previous PostgreSQL Versions                                          ║
║ short_desc      │ Enable input of NULL elements in arrays.                                                                   ║
║ extra_desc      │ When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally. ║
║ context         │ user                                                                                ║
║ vartype         │ bool                                                                                ║
║ source          │ configuration file                                                                                ║
║ min_val         │ ░░░░                                                                                ║
║ max_val         │ ░░░░                                                                                ║
║ enumvals        │ ░░░░                                                                                ║
║ boot_val        │ on                                                                                ║
║ reset_val       │ off                                                                                ║
║ sourcefile      │ /var/lib/postgresql/9.5/main/postgresql.auto.conf                                                          ║
║ sourceline      │ 3                                                                                ║
║ pending_restart │ f                                                                                ║
╚═════════════════╧════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Update
And there is the query to get db/role settings, without relogin required:

with
  opt_db as (
    select s.*, d.datname, opt_name, opt_value from
      pg_db_role_setting s join
      pg_database d on (s.setdatabase = d.oid) cross join lateral (
        select
          split_part(opt, '=',1) as opt_name,
          split_part(opt, '=', 2) as opt_value
        from
          unnest(setconfig) as opt) as opt
    ),
  opt_role as (
    select s.*, r.rolname, opt_name, opt_value from
      pg_db_role_setting s join
      pg_roles r on (s.setrole = r.oid) cross join lateral (
        select
          split_part(opt, '=', 1) as opt_name,
          split_part(opt, '=', 2) as opt_value
        from
          unnest(setconfig) as opt) as opt
  )

select
  datname,
  rolname,
  opt_name,
  opt_db.opt_value as db_value,
  opt_role.opt_value as role_value
from opt_db full join opt_role using(opt_name);

Possible result:

╔══════════╤══════════╤═════════════╤═════════════════╤═════════════════╗
║ datname  │ rolname  │  opt_name   │    db_value     │   role_value    ║
╠══════════╪══════════╪═════════════╪═════════════════╪═════════════════╣
║ postgres │ nd       │ search_path │ "$user", public │ "$user", public ║
║ postgres │ postgres │ array_nulls │ off             │ on              ║
║ ░░░░     │ nd       │ foo.bar     │ ░░░░            │ 3               ║
╚══════════╧══════════╧═════════════╧═════════════════╧═════════════════╝


REPMGR - change reconnect_attempts and reconnect_interval not working

I had the same problem, it's already solved:
https://github.com/2ndQuadrant/repmgr/issues/673

Issue has been reproduced and a fix applied, which will be in the upcoming 5.2.1 release.


Restart the active node services without failover

Use the Failover Cluster Manager application to manually stop the services.

This tells the cluster you are intentionally shutting down the service, so it won't attempt to bring it back up on another node.

The Failover Cluster service will wait until you manually bring the service back online using Failover Cluster Manager, where you can decide which node to use to bring the service up.


There is no 'demote' option for pg_ctl - if the primary was cold when the standby was promoted perhaps it could be converted to a standby manually, but otherwise it will need a new base backup from the new primary.

The normal reason for switchover would be if the primary has failed so this would not be necessary.


Do SQL Sharding or Cluster help speed up slow queries, or just concurency?

Sharding requires a bunch of changes to the infrastructure, connections, etc.  The product "Spider" makes most of that transparent.
Sharding may improve scaling and concurrency for simple queries.  But for table scans and JOINs, it is likely to make performance worse.
Partitioning is excellent for "deleting old data".  See my blog.  Otherwise, the DELETE competes with other queries, making the whole system suffer when you do the purging.  (For 3 years, I would PARTITION BY RANGE(TO_DAYS(...)) and hav 38 partitions.)  DROP PARTITION for the old month is essentially instantaneous, regardless of size.
Loading data can be a bottleneck.  Do you use LOAD DATA?  That is probably the best.  Second best would be batched INSERTs of 100-1000 rows per batch.
If you just need read scaling, then adding Slaves gives you unlimited read scaling.
If you need write scaling, a Galera-based cluster might help some, but Sharding is the only real solution.
A few Summary tables, as you allude to, is an excellent way to get performance from a Data Warehouse setup.  I blog on that, too.
"I can't afford to rewrite everything for now" -- Any shortcut will take just as long.  And then you will still have to "rewrite everything" to take the next step.
Manually splitting data onto different devices is not as good as RAID striping of all the drives into one large drive.
A RAID controller with Battery Backed Write Cache makes writes appear to be instantaneous, without losing data.
Normalization can be used to keep the tables smaller, hence cacheable.  Are you doing a reasonable amount of that, and using reasonably small ids (eg, SMALLINT UNSIGNED instead of BIGINT where appropriate)?
Slow queries -- There are too many variables to make a general statement.  Let's see some specifics.

I would be happy to discuss any of these further.  Since you have not said what you really need, I don't know what to focus on.


General purpose view for last value in a group

This form with DISTINCT ON is typically faster than either v1 or v2:

CREATE VIEW v3 AS
SELECT DISTINCT ON (id) a.*
FROM   a
ORDER  BY id, seq DESC NULLS LAST;

You get sorted output (by id) on top of it, which is typically a welcome side effect.
Minor difference: this form includes a row for ids with only NULL values in seq. This, too, would be a welcome effect, typically. And irrelevant if seq is defined NOT NULL.

If performance is essential I strongly suggest to create a matching (!) multicolumn index of the form:

CREATE INDEX a_id_seq_idx ON a (id, seq DESC NULLS LAST);

A lot more details and more alternatives under this related question on SO:
Select first row in each GROUP BY group?

Switch query depending on input

If you are interested in a switching technique per se, rather, you could create a table function instead of the view which takes an id as parameter.

CREATE OR REPLACE FUNCTION f_a(_id int DEFAULT NULL)
  RETURNS SETOF a AS
$func$
BEGIN

IF _id IS NULL THEN
    RETURN QUERY
    SELECT ... ;
ELSE
    RETURN QUERY
    SELECT ...
   WHERE  id = _id;
END IF;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_a();     -- for the whole table
SELECT * FROM f_a(123);  -- for a particular id

Could be improved with more parameters.

This approach is perfect for simple calls. Even saves some planning overhead and is typically faster than raw SQL this way. Be advised, though, that a plpgsql function poses as optimization barrier. The body of the function cannot be inlined or optimized in the context of a bigger query (like a view or a simple SQL function could be). More in this related answer:
PostgreSQL Stored Procedure Performance


Truncate text output of COPY command for postgres

Instead of COPY <table>, you can use COPY (SELECT <columns> FROM <table>), and work the truncation logic into the column list.

Of course, you'll need to build this list based on the type information in the catalog. This should do it:

  SELECT
    string_agg(
      quote_ident(column_name) ||
        CASE udt_name
          WHEN 'timestamp' THEN '::timestamp(0)'
          WHEN 'timestamptz' THEN '::timestamptz(0)'
          WHEN 'text' THEN '::varchar(6000)'
          ELSE ''
        END,
      ','
    )
  FROM information_schema.columns
  WHERE
    table_schema = '$schema' AND
    table_name = '$TBL';

This query just generates the <columns> string to put into the COPY statement. Run it inside your while loop, put the output in a variable (e.g. $columns), and then run psql -c "COPY (SELECT $columns FROM \"$schema\".\"$TBL\")...


LSCopy job fails on SQL Server 2005 - Insufficient space

The destination path for the LSCopy job has to be an UNC Path for all practical purposes, to make things simpler to debug I would reccomend not to use the default \d$ shares as you will have to change the permissions on them but connecting to \localhost\c$ is a network connection. Now the error

  ***Error: Not enough server storage is available to process this command.

Is an operating system error. but not an error from the SQL Server. You are probably starving the computer on memory or having some network issues, If on Windows Server 2003 a quick reboot of the secondary server will fix this temporary and then I would look into memory settings on the server


Oracle 11g - drop maxvalue partition, keep data

Split above current maximum value, drop the empty MAXVALUE partition, then convert to interval partitioning:

alter table parttest4 split partition p_max at (2030) into (partition p_2030, partition p_max) update global indexes;
alter table parttest4 drop partition p_max;
alter table parttest4 set interval (10);


Storage provisioning for Microsoft SQL Server

A back-of-the envelope "guess" would be to put the log files on one drive, the data files on another drive, and in the case of prod, put the tempdb files on a third drive.  You should also consider a location for SQL Server backup files that is not on the same drive subsystem as either your data or log files; preferably an off-machine location.

Having said that, you've provided too little data for us to really help you decide on the highest performance setup.


Can I replicate a database from SQL Server 2012 to SQL Server 2014 (with 2014 distributor)?

If you are using Transactional Replication then yes, this configuration is supported.

This is covered in Using Multiple Versions of SQL Server in a Replication Topology:

  For all types of replication, the Distributor version must be no
  earlier than the Publisher version. (Frequently, the Distributor is
  the same instance as the Publisher.)

Since your Distributor version is a later version than your Publisher version, you are good here.

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

Since your Subscriber is within two versions of your Publisher version, you are good here.

However, this configuration is not supported if you are using Merge Replication since your Subscriber will be a later version than the Publisher:

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version.

If you have anymore questions, please let me know.  I hope this helps.


if you put count(*), count(1) or count("test") it will give you the same result because mysql will count the number of rows, for example:

select count(fieldname) from table;

will display the same result that

select count(*) from table;

or

select count(1) from table

mysql> select * from language;
+-------------+----------+---------------------+
| language_id | name     | last_update         |
+-------------+----------+---------------------+
|           1 | English  | 2006-02-15 05:02:19 |
|           2 | Italian  | 2006-02-15 05:02:19 |
|           3 | Japanese | 2006-02-15 05:02:19 |
|           4 | Mandarin | 2006-02-15 05:02:19 |
|           5 | French   | 2006-02-15 05:02:19 |
|           6 | German   | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.00 sec)

mysql> select 1 from language;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
6 rows in set (0.00 sec)

mysql> select 'anything'from language;
+----------+
| anything |
+----------+
| anything |
| anything |
| anything |
| anything |
| anything |
| anything |
+----------+
6 rows in set (0.00 sec)

mysql> select count(1), count(*), count('anything') from language;
+----------+----------+-------------------+
| count(1) | count(*) | count('anything') |
+----------+----------+-------------------+
|        6 |        6 |                 6 |
+----------+----------+-------------------+
1 row in set (0.00 sec)

the result will be a number equal to the number of rows of that table.

PLEASE BE AWARE THAT NULL FIELDS WILL NOT BE COUNTED....

mysql> select original_language_id from film where original_language_id is null;
+----------------------+
| original_language_id |
+----------------------+
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
|                 NULL |
......
|                 NULL |
+----------------------+
1000 rows in set (0.00 sec)

mysql> select count(original_language_id) from film where original_language_id is null;
+-----------------------------+
| count(original_language_id) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

FOR THAT REASON IF YOU WANT TO COUNT ROWS I THINK count(something) is a good option

mysql> SELECT COUNT(1) FROM film;
+----------+
| COUNT(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)


Indexing SQL Server datetime Columns for Performance

Adding an index will increase performance on SELECT statements, assuming your range of dates is not sufficiently large as to force an index scan as opposed to an index seek.  Adding an index will decrease performance on INSERT, UPDATE, and DELETE operations, as this new index will need to be maintained.  The clustered/nonclustered question is highly dependent on how many other columns from the table you are including in the SELECT clause.

If this question is more than a homework question, and you have examples you would like to include, it would help my answer to be more complete.

Hope this helps,

Matt


How to compare and aggregate data in a table

Since you want to count differing values in a set that may contain identical values, I would say COUNT(DISTINCT ...) is the perfect tool to use. For instance, the following will count distinct levels per product:

SELECT
  PRODUCTID,
  COUNT(DISTINCT LEVEL)
FROM
  @example
GROUP BY
  PRODUCTID
;

This will be the output for your example:

PRODUCTID
---------  ---
100        1
101        1
102        2
103        3

Now, how can you count only the distinct values matching a specific condition? The answer is, by using conditional aggregation, i.e. one that uses a CASE expression as the aggregate function's argument. So, to count only distinct passed levels, you can use a query like this:

SELECT
  PRODUCTID,
  COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END)
FROM
  @example
GROUP BY
  PRODUCTID
;

And the results will now be

PRODUCTID
---------  ---
100        1
101        1
102        1
103        3

The figures seem correct now, so how can you turn those 1, 3 into 0, 1? Why, by using another CASE expression, of course:

SELECT
  PRODUCTID,
  ISPASSED = CASE
                WHEN COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END) >= 2
                THEN 1
                ELSE 0
              END
FROM
  @example
GROUP BY
  PRODUCTID
;

Now you can enjoy the expected output of

PRODUCTID  ISPASSED
---------  --------
100        0
101        0
102        0
103        1


Generally, if you move a BLOB column to another table, and the new BLOB column has the same attributes (such as "DISABLE STORAGE IN ROW", see my other answer), performance wouldn't change much. How Oracle internally stores LOB/BLOB/CLOB columns is, as I understand, not relevant to you, as your question deals more with the user-visible data model.

One obvious performance implication of using a foreign key is that it slows access down a little bit.

But throwing multiple BLOB columns into a single column is a very bad idea in terms of design. Although I don't know Hibernate ORM, to me your question sounds here as "Is it a good idea to spoil the design of database to fix a problem with Hibernate?". Well, it's not a good idea.

What if you need to export or import blobs referenced from only one of your tables?

How would you partition your blobs if they are in a single table? (If your database is "large", you need to plan for that already!)

What if the BLOB updates/inserts that were concurrent previously, now get serialized because you have a single BLOB column?


suddedenly increased in size of Log files form 32GB to 70 GB

A full database backup won't help your log size.

Full Recovery Model means "Can restore to any point in time." - including before that new database backup was taken.

Simple Recovery Model means "Can restore to the last database backup".

So... have a think about whether you need to restore to this morning or not (before your database backup). If you don't, then put your database into Simple Recovery Model, then back into Full. Then take a differential backup. Once you've done those three steps you will be able to recover to your last full database backup, the differential backup you just took, and any point in the future. Take regular log backups - try every ten-to-fifteen minutes.

And then look at your log. You should find it's mostly empty.

If not, look in sys.databases to see why it's not truncating. Other possible reasons include Replication that's broken, for example.


If this is a language translation issue and the districts are the same whether they are listed in Hebrew or Russian, you might actually have more success with a slightly different table structure.

Right now, based on your description, you have:

ESTATE
+---------+------------+------------+
| Columns | DistrictRU | DistrictHE |
+---------+------------+------------+
|       1 | 1          | NULL       |
|       2 | NULL       | 2          |
|       3 | 3          | 4          |
+---------+------------+------------+

DISTRICTS
+----+-----+
| ID | STR |
+----+-----+
|  1 | R1  |
|  2 | H2  |
|  3 | R3  |
|  4 | H3  |
+----+-----+

Instead of doing this, I'd propose something slightly different:

LANGUAGE
+----+---------+
| ID |   STR   |
+----+---------+
|  1 | Russian |
|  2 | Hebrew  |
+----+---------+

DISTRICTS
+----+------------+-----+
| ID | LanguageID | STR |
+----+------------+-----+
|  1 |          1 | R1  |
|  2 |          2 | H2  |
|  3 |          1 | R3  |
|  4 |          2 | H3  |
+----+------------+-----+

ESTATE
+---------+------------+
| Columns | DistrictID |
+---------+------------+
|       1 |          1 |
|       2 |          2 |
|       3 |          3 |
+---------+------------+

There are a few advantages here.  First of all, if you need more languages than Russian and Hebrew (say, English or possibly Amharic), you don't need to modify the table structure.  Secondly, you now have three normalized tables rather than one normalized and one with repeating structures.  And thirdly, you don't have to worry about an enumeration field on your DISTRICTS table that might be out of sync with a value in ESTATE.

Tying these together with a query assumes that you know the language you want to display results in (presuming that you don't need to display both names at once).  Displaying all of the names at once becomes slightly more difficult, but if you're pulling this data through a business layer and displaying to end users through an application, the easy version is to use two queries:  one to get the estate(s) and one to get the full listing of districts by estate.  Then, the web server could piece it together.


I'm sure, by now, you've rebuilt your clustering index and your 25 non-clustered indexes as well.  While it might be difficult to determine why your clustering index got disabled in the first place, you could try implementing one or more server DDL triggers that would fire after an ALTER INDEX command and interrogate the command for the word 'disable' and roll the transaction back - (and wait for someone or some process to complain about that action).

Example here:

--DDL trigger to prevent index disable
USE [master]
GO
alter TRIGGER [xx_TestDisable]
ON ALL SERVER
FOR ALTER_INDEX as

declare @text nvarchar(max)

SET @text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

if @text like '%disable%'
    rollback
GO

ENABLE TRIGGER [xx_TestDisable] ON ALL SERVER
GO

--Testing to see if the DDL trigger rolls back a disable
CREATE TABLE T (X INT CONSTRAINT IX_Index PRIMARY KEY);

ALTER INDEX IX_Index ON T DISABLE;

SELECT *
FROM T

As far as your 25 indexes, I think I'll borrow a quote that is attributed to Albert Einstein (paraphrased) - “Everything should be made as simple as possible, but no simpler.”

If you 'need' 25 indexes, then you 'need' 25 indexes.

Only you can decide which indexes should be created and maintained, but you should definitely use sys.dm_db_index_usage_stats to see how active your indexes are. Here is a good start.


Rick James was right the problem was the left join that did not worked there. I replaced it with an INNER join and it works perfect.

The final query working and optimised looks like this:

SELECT
    w0.name AS 'Product Name',
    p.sku AS 'Product(sku)',
    p.product_id AS 'product_id',
    p.parent_id AS 'parent_id',
    COALESCE(w0.quantity, 0) AS 'Denmark - main'
FROM
    (SELECT
        *
    FROM
        oc_product p
    ORDER BY 'Product Name'
    LIMIT 0 , 20) p
        LEFT JOIN
    oc_warehouse_product wp ON (p.product_id = wp.product_id)
        INNER JOIN
    oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
        LEFT JOIN
    (SELECT
        pd.name, wp.quantity, p.sku
    FROM
        oc_product p
    LEFT JOIN oc_warehouse_product wp ON (p.product_id = wp.product_id)
    INNER JOIN oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
    LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
    WHERE
        wl.warehouse_name = 'Denmark - main') AS w0 ON p.sku = w0.sku
GROUP BY p.sku
ORDER BY 'Product Name'
LIMIT 0 , 20;


Oracle 11g: functions or procedures in C/C++

Oracle database supports Java and external C calls. They can be used as wrappers to call programs written in other languages such as Python.

Publishing External Procedures

  Oracle Database can only use external procedures that are published
  through a call specification, which maps names, parameter types, and
  return types for your Java class method or C external procedure to
  their SQL counterparts.

Create a sample program:

$ cat /home/oracle/1.c
char* myfunc()
{
   return "Hello World!";
}

$ gcc -shared -o /home/oracle/1.so -fPIC /home/oracle/1.c

In the database, create a library and a function using it:

create or replace library lib_myfunc is '/home/oracle/1.so';
/

create or replace function myfunc
return varchar2 as external
name "myfunc"
library lib_myfunc
language c;
/

Enable the use of this library in $ORACLE_HOME/hs/admin/extproc.ora:

SET EXTPROC_DLLS=/home/oracle/1.so

Then finally use it as:

SQL> select myfunc from dual;

MYFUNC
------------
Hello World!

More about this:

Developing Applications with Multiple Programming Languages


Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

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

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)


SQL Server 2014 .bak file error - The media family on device 'D:\..' is incorrectly formed

There can be several causes for this error:

The backup is corrupted as it has not been transferred in binary mode If you are using FTP to transfer the backup, make sure the file is transferred in binary mode
The version of your backup is different than the SQL Server instance you are trying to restore it to. You can try to restore to a SQL server instance with the right version and transfer the data with SSIS
The backup is corrupt. In this case your options are limited


Split mile limits to the thousandth based on ID

If you don't want to use a recursive CTE you could use below query example:

SELECT m.ID,
       a.rn as start_mile,
       a.rn+ CONVERT(decimal(15, 3),0.001) as end_mile
FROM dbo.Miles m
CROSS APPLY
(
    SELECT TOP(CONVERT(int,(( end_mile -  Start_mile)* 1000)))
    CONVERT(decimal(15, 3),(Start_mile + (CONVERT(decimal(15, 3),ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) / 1000))) as rn
    FROM master..spt_values spt1
    CROSS APPLY master..spt_values spt2
) as a;

This should work up until 6M miles for one id.

The spt values table could be replaced by a numbers table of some sort.

DB<>Fiddle


MSSQL - what is the best isolation level for a website

To switch a database to RCSI:

USE mydb;
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE mydb SET MULTI_USER;


You can get the result by using a LEFT JOIN between the maincolor and the subcolor.

The key is placing the filter of status=1 on the JOIN condition.  Your code will be:

select m.mainid,
  m.color,
  s.subcolor
from maincolor m
left join subcolor s
  on m.mainid = s.mainid
  and s.status = 1

See SQL Fiddle with Demo.

The LEFT JOIN will return all rows from the maincolor table and any matching rows in the subcolor table. Since you what to apply a WHERE to the data in the subcolor table, then you will want to move that filter to the JOIN condition to get the result.


Allow users to only view the databases they have access to

By default a login can see all the databases on a server.

You can DENY VIEW ANY DATABASE TO [your login or serverrole];, after which a login can only see the databases where he has access.

To verify what databases a login can see, try this when you're connected as an administrative user:

--- This changes the security context to the login "dummy"
EXECUTE AS LOGIN='dummy';

--- Lists the databases visible to that user:
SELECT * FROM sys.databases;

--- Important: changes the security context back again:
REVERT;

The VIEW ANY DATABASE permission is assigned to the server-level principal (the login, i.e. not the user, which is the database-level principal), and therefore, you'll need to apply it to each availability group replica. I would recommend that you apply (deny) this permission on a server role rather than individual users to keep it manageable.

More detailed reading on the subject:

https://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/


Why can't a differential backup specify its base?

A differential backup uses what is called the differential change map to build a list of pages that have been modified since the last full backup.  This list is a "differential" list, hence the name of the backup type, and the reason the backup can only ever be restored over top of the associated full backup.

Performing a full backup resets the differential change map.  From that point forward, any page that is modified is recorded in the map.  If you then take a differential, that backup only contains pages that have been modified since the last full backup, and recorded in the map.

In your analogy, the two full backups, which serve as a base for the entire restore process would likely have different contents, and therefore different differential maps.  If you restore a diff based on the first backup over the 2nd backup, the database would likely be corrupted.  In fact, SQL Server prevents the restore of a diff backup over anything except the original full backup it is based upon.

When you ask SQL Server to take a differential backup, the only "base" for the differential is the single differential change map present in the database at the time the differential backup starts.  This is why you cannot specify the base for the differential backup.

In response to a comment from @MartinSmith - you might be able to use COPY_ONLY backups to restore a differential backup over a number of full backups.  Consider the following scenario:

BACKUP DATABASE xyz TO DISK = 'path_to_backup.bak';
BACKUP DATABASE xyz TO DISK = 'path_to_backup_2.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_3.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_4.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_diff.bak' WITH DIFFERENTIAL;

The differential backup in step 5 should be capable of being restored over any of the backups taken in steps 1 to 4, since the differential change map is only cleared when the full backup in step 1 occurs.  The COPY_ONLY backups in steps 2, 3, and 4, do not reset the change map. Since the differential change map accumulates changes made since the full backup, each of the successive COPY_ONLY backups contains enough information for the differential backup to work against any of the previous 4 backups.

Although it seems like it should work, in practice, restoring a differential over top of a copy_only backup results in the following error:

  Msg 3136, Level 16, State 1, Line 1
  This differential backup cannot be restored because the database has not been restored to the correct earlier state.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I've created a SQL Server 2012 platform repro for testing differential and copy_only restores, and saved the file on gist.github.com - WARNING the script will drop any database named RestoreTest as its first step.


Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE

Found out the issue. Ran out of storage space on RDS while running Alter table query.

For some reason it did not throw error disk space issue.

But when i tried without ALGORITHM=INPLACE like the query below, it throws out error of disk space issue.

Alter table dbname.tablename
add column column1 varchar(50),
add column column2 text ,
LOCK=NONE;

I increased 100G to see if that suffices the problem, it worked.

But i still did not understand the fact why does the alter query take 60mins to add two columns when i run the Alter statement above.

While Alter statement to add single column takes 30mins to complete.

How can we improvise the execution time for Alter statement, any suggestions would help?


The transaction log for that database contains all transactions since the last transaction log backup, or the last time it was switched from simple recovery mode. Execute the following to get the definitive answer as to why SQL Server can not truncate the log and subsequently why the log is growing.

SELECT  d.Name
        ,d.log_reuse_wait_desc
FROM    sys.databases d
ORDER BY
        d.name

If you want point in time recovery then leave the DB in the full recovery model and take frequent log backups. Each log backup will contain all transactions since the last log backup. The log backup process is also responsible for clearing the log and marking the space for reuse i.e. the next transaction made in the DB will be written to the start of the truncated log in a circular fashion. This backup and reuse of the log is what prevents the log file from growing.

If you are not interested in point in time recovery and want to simplify the administration of the database. Then set the database to the simple recovery model and do not take t-log backups. SQL Server will automatically truncate the transaction log after each transaction is committed. Meaning that once the transaction has been committed to the log the record is overwritten by the next transaction etc.

Either way, once you've made one of these two decisions you can then shrink the log file down to a more reasonable size. Note ideally you want to make it large enough so it doesn't grow but not so large that you'll need to shrink it again. Also note that you can not shrink the active part of the log.

Download and deploy https://ola.hallengren.com/ database administration solution to cover backups, index fragmentation, statistics and CHECKDB.

You might also find the 'disk usage' report returned by right clicking the DB in Object Explorer > Reports > Standard reports > 'disk usage' useful for returning the free space in the t-log.

I also recommend that you Google why it's so important to keep the log chain intact from a DR point of view, and how switching from full to simple  breaks the chain leaving you exposed to data loss.


SQL Server 2012 installation silently fails

If this is RTM, I would recommend slipstreaming SP1 + the latest CU into setup before installing, and making sure you install from a local source rather than an ISO, network folder, external drive, etc.

To slipstream you can just download SP1 (likely SQLServer2012SP1-KB2674319-x64-ENU.exe) and/or a CU (CU3 is here; CU4 should be released any moment now) to a folder (say C:\SP1Updates\), extract the files (you should end up with .exe files like SQLServer2012-KBxxxxxxx-x64.exe and you can ignore or get rid of the source 4xxxxx_intl_x64_zip.exe files) and then run setup from a command line like so:

setup.exe /UpdateEnabled=1 /UpdateSource="C:\SP1Updates\"

Or you can download the slipstreamed ISO from here (they have slipstream in the name, just note they contain SP1 but no CU):

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) (download link)

Some blog posts that go into greater detail about slipstreaming:

Installing SQL Server 2012 + Service Pack 1 + SP1 Cumulative update in one installation using SQL Server 2012 Product Update
How to create an SQL Server 2012 installer with integrated CU1 (Product Updates – Slipstream)
SQL Server 2012: Merged Product Updates (Slipstream)

You might also try the technique here, and you should be able to use slipstream here as well if you want (you'll just need to use a different PatchLevel, 11.00.3000.00 I believe):

SQL 2008/2008 R2/2012 setup disappears/fails when installing Setup Support files

In case that blog post disappears, I'm going to steal the content and paraphrase (since part of the instructions seem incorrect):

  Save the following in a .reg file and merge to populate the registry:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap]
"BootstrapDir"="C:\\Program Files\\Microsoft SQL Server\\110\\Setup Bootstrap\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Bootstrap\Setup]
"PatchLevel"="11.00.2100.60"

  Next, copy the following files and folders from all of the media to:

 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

  Next, run setup from the new location, and it should proceed beyond the point of error this time.

(Note that I haven't tried this technique.)


Source Database of a restore stuck Restoring

This can happen when you don't uncheck in the GUI to take a tail-log backup.
It is a nasty behavior by the GUI. You type a new database name in the restore GUI, the one to be created by the RESTORE command. But the GUI still does a tail-log backup of the source database, and it does it by default using the NORECOVERY option (which s precicely what puts the source db in restoring state).
The image you posted doesn't reflect this, so I can deduce that it wasn't the actual screen from your restore. You would have a note in the upper left of the dialog.
So, in the future, go to the Options page and uncheck the tail log backup when you restore into a new database name.
To handle the immediate situation, you just execute below (obviously substituting the database name):
RESTORE DATABASE dbname WITH RECOVERY

No backup files needed.


Exclude rows with consecutive data that may repeat

This seems to be similar to a solution from Itzik Ben-Gan to gaps and islands

SELECT [device_owner], [device_id],MIN(timestamp) as timestamp,status

FROM
(
SELECT *
   ,diff = ROW_NUMBER()OVER(PARTITION BY device_owner,device_id ORDER BY timestamp ASC)
         - ROW_NUMBER()OVER(PARTITION BY device_owner,device_id , status ORDER BY timestamp ASC)
FROM @t
)A
--WhERE status = 0
GROUP BY [device_owner], [device_id],status , diff
ORDER BY timestamp

output of  it:

device_owner    device_id   timestamp           status
owner1          device_1    01/01/2001 09:00:00 False
owner1          device_2    01/01/2001 09:15:00 False
owner1          device_1    01/01/2001 09:30:00 True
owner1          device_2    01/01/2001 09:45:00 True
owner1          device_1    01/01/2001 09:55:00 False
owner1          device_1    01/01/2001 10:11:00 True

dbfiddle


Is it any potential problem with having 2 constraints suppored by one index in Oracle?

As Nat has noted your UQ_ACTION constraint duplicates the primary key constraint.  Based on your FK usage into this table, your primary key is (action_id, action_type_id). As such it is redundant and should be removed.  If you wish your primary key to be action_id alone, then your foreign keys must reference only action_id.

I believe you were correct in modeling action_id as the primary key.  In this case change your child references.  (Having the action type id in the foreign key violates normal form and forces redundant information into the referencing tables.).  Child tables of your action table would have the action type id in their key if they apply to multiple types.

You may want a UK on action_id, but it is not necessary.  If you have such a UK with a corresponding index, then I would reverse the order on the index containing (action_type_id, action_id).  This will allow fast retrieval by action_id alone and by action_type_id where statistics indicate an index is useful.

However, to answer your question in cases where an index can be used for multiple constraints, no there is no penalty. In fact, the opposite is the case.

Maintaining indexes can be a relatively expensive operation.  Adding redundant indexes does have a penalty.  Each insert, update, or delete that changes the column in the redundant index will require additional work to update the index.

It appears you have recognized the value of not creating unused indexes.  While common, foreign key indexes are not necessary in many cases.  It is a good idea to have an index if you do deletes from the referenced table.  A surrogate index beginning with the foreign key may result in more efficient queries.  If you do frequent lookups by action_type_id, the index would be useful.  It might be more useful to have a multi-column index with other values used to filter the result set.


SQL Server : I need to combine multiple rows into one row with different column values

You can use an appropriate aggregation function. In this case, it seems like MIN or MAX will do the trick:

SELECT Order,
       MIN(Drink) Drink,
       MIN(Entree) Entree,
       MIN(Desert) Desert
FROM dbo.YourTable
GROUP BY Order;


Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO


A case for window functions:

SELECT count(*) As longest_streak
FROM  (
   SELECT row_number() OVER w
          - count(status = 'WON' OR NULL) OVER w AS grp
   FROM   game
   WHERE  player_id = 1          -- %some id%
   WINDOW w AS (ORDER BY date)
   ) sub
GROUP  BY grp
ORDER  BY count(*) DESC
LIMIT  1;

Or use the aggregate FILTER clause in Postgres 9.4 or later:

...
   SELECT row_number() OVER w
          - count(*) FILTER (WHERE status = 'WON') OVER w AS grp
...

Return counts for multiple ranges in a single SELECT statement

We count rows ordered by date and subtract the running number of wins. If the next row is a win, it still falls in the same group grp. Then get the maximum count per group.

Detailed explanation:

Select longest continuous sequence


The phenomenon is generally known as parameter sniffing. You got a bad execution plan due to an unusual set of parameters, and by dropping and recreating the stored proc, you've just cleared the cached execution plan for that stored procedure.

Related reading material:

Slow in the Application, Fast in SSMS by Erland Sommarskog
Why Is This Query Sometimes Fast and Sometimes Slow by Brent Ozar (uh, me)
How to Start Troubleshooting Parameter Sniffing Issues by Brent Ozar (dang, that guy is good)


Why $group is very slow in aggregation and how can faster it in MongoDB

As per MongoDB Documentation here Groups documents by some specified expression and outputs to the next stage a document for each distinct grouping. The output documents contain an _id field which contains the distinct group by key. The output documents can also contain computed fields that hold the values of some accumulator expression grouped by the $group’s _id field. $group does not order its output documents.

The $group stage has the following prototype form:

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ... } }

The _id field is mandatory; however, you can specify an _id value of null, or any other constant value, to calculate accumulated values for all the input documents as a whole.

$group Operator and Memory

The $group stage has a limit of 100 megabytes of RAM. By default, if the stage exceeds this limit, $group will produce an error. However, to allow for the handling of large datasets, set the allowDiskUse option to true to enable $group operations to write to temporary files.

As per blog of @VladMihalcea here the  MongoDB aggregation framework is extremely useful and its performances can’t go unnoticed.that didn’t require any extra optimization, aiming to demonstrate the out-of-the-box performance of MongoDB.

For further your ref here


It's not exactly clear how the one or the two table is to be chosen but something like this should work (I assume that the check_poll table has a single row always):

SELECT
    COALESCE(p.name, u.name) AS name,
    COALESCE(p.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u
  LEFT JOIN poll_one AS p //I wan to make this conditional
    ON p.id = u.id
WHERE c.poll = 1        -- whatever shows which poll table to choose

UNION ALL

SELECT
    -- same list
FROM check_poll AS c
  CROSS JOIN user_edit_table u
  LEFT JOIN poll_two p
    ON p.id = u.id
WHERE c.poll = 2 ;

or even combine the two subqueries in one:

SELECT
    COALESCE(p1.name, p2.name, u.name) AS name,
    COALESCE(p1.size, p2.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u
  LEFT JOIN poll_one AS p1
    ON  p1.id = u.id
    AND c.poll = 1
  LEFT JOIN poll_two AS p2
    ON  p2.id = u.id
    AND c.poll = 2 ;


Is this schema usable for multi-tenant clients?How to remove multi-cascade paths?

To achieve multi-tennancy you should limit the number of relationships you have to the factor that determines the tennant.

For example, each of Course, Department and Staff have a foreign key to School.  However, these child tables are also interrelated.  Is it necessary for Staff to have a schoolid?  You can get to the school a staff member works for by following the foreign key to Course.

Similarly, you have grand-child tables (SD and DSC) which are relating tables in curious ways.  What has "months indicating duration of the course" to do with which staff member is teaching the course?  Is it really necessary for DSC to relate to Staff?  Also, you appear to have repeating groups defined in your SD and SDC tables.  I believe you may have a normalization issue.

By over-relating tables you create cascade cycles.  Consider the following ERD as an alternative:

Here there is only one potential cycle to be concerned with, instead of the several that are in your current model.  Fewer potential cycles means less extra work that might need to be done with triggers instead of declarative referential constraints.  This keeps your system simpler and more maintainable.


Multiple tables entry with current timestamp

I would suggest creating your actions table with a timestamp field using a default of current_timestamp, and have the other tables use regular timestamp fields with no default value.

Then, after inserting a new row into your actions table, you can retrieve that row and insert the required rows into the additional tables using that returned value to populate the relevant fields.

In this way, the values between the tables will match as you require.

You may require an auto-incrementing field in your actions table, and then use the SQL command SELECT LAST_INSERT_ID(); within the same connection to get the auto-incrementing id from the actions table, allowing you to then perform another SELECT in order to get the timestamp value.

It will add a few queries to your overall insert, but will achieve the desired result.


TSQL (2014) - Importing XML with accented and punctuation Characters

The XML you have is invalid in UTF-8 encoding. The accented characters needs to be encoded. For instance ° should be encoded as Â°.

Here is a shorter version that also fails.

declare @X xml = '<?xml version="1.0" encoding="UTF-8"?>°';
select @X.value('text()[1]', 'nchar(1)');

  Msg 9420, Level 16, State 1, Line 1 XML parsing: line 1, character 39,
  illegal xml character

Should be like this instead if you use UTF-8.

declare @X xml = '<?xml version="1.0" encoding="UTF-8"?>Â°';
select @X.value('text()[1]', 'nchar(1)');

You need to trace the source of the data backwards to the producer to see where you end up with invalid XML.


I don't think it's possible within OpenLDAP.

When I've had to do something similar in the past, it was against an iPlanet/SunONE Directory Server, and we had a daily cron job that polled the payroll server for terminations, and then adjusted attributes as appropriate to lock the accounts.

The only approach I can think to try is if there's some way to get the equivalent of NOW in an ldap filter, so you could add a 'what' clause like:

filter=( termDate < NOW )

... but I don't know of any way to do that without some sort of external intervention to set NOW.


How to get a COUNT for both IN and NOT IN a table

You can use conditional aggregation with an outer join

select count(*) filter (where at.asset_id is not null) with_unique_id,
       count(*) filter (where at.asset_id is null) as without_unique_id
from assets a
  left join associated_table at on at.asset_id = a.asset_id;


What happens at the data page level when the record length changes

how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

Well, there is a false assumption here, that being that adding a new column is always done quickly. This is not a true statement.

Now, when adding a column that allows NULLs, that can be done quickly as the meta-data of the Table definition gets updated but the NULL isn't physically added to the datapages in that moment. SQL Server can return the correct NULL to queries since it is logically obvious what the "value" is. When rows are inserted or updated, the records that are written to the datapages do include the NULL (for fixed-length columns, unless the SPARSE option was used for the new column or Data Compression is enabled on the Clustered Index). But the rest of the rows that have not been updated will not have the NULL physically added until an index REBUILD.

However, when adding a column marked as NOT NULL, then, prior to SQL Server 2012 (and even then, only if the new value was a runtime constant), then the actual value was written physically to the datapages in that moment, and that operation could take a looooooooooong time, depending on how many rows and/or how much data was in the table. You can find plenty of questions and articles about trying to overcome this issue as tables with many GBs of data and/or hundreds of millions of rows could take hours to add a new NOT NULL column.

Then came a truly wonderful new feature in SQL Server 2012 (Enterprise Edition-only, which also implies Developer Edition) whereby adding a new NOT NULL column with a default value could be an instantaneous, meta-data only operation, just like adding a column marked as NULL. The only caveats are that the datatype not be a LOB (e.g. MAX-types, XML, etc) or CLR-based type, and that the value be a runtime constant (i.e. mainly literal values). Something like NEWID() would not be instantaneous since it would need a different value per each row. But for values that are runtime constants, SELECT operations can easily get the correct value by just looking at the meta-data of the DEFAULT which gives the logically-obvious value.

The MSDN page for ALTER TABLE, in the Locks and ALTER TABLE section (under "Adding NOT NULL Columns as an Online Operation"), talks about this behavior.


A little late but this block will surely help someone.

Replace OWNER_SCHEMA_NAME with desired table owner schema.
Replace NEW_TARGET_SCHEMA_NAME in below script to the schema you want to give permissions.
Please take a look at the permissions below (which are placed as sample) before you execute this script.
Similarly, you can build a script for revoking permissions.

here goes the pl/sql block.

BEGIN
    FOR t IN (
        SELECT
            object_name,
            object_type
        FROM
            all_objects
        WHERE
            owner = 'OWNER_SCHEMA_NAME'
            AND   object_type IN (
                'TABLE',
                'VIEW',
                'PROCEDURE',
                'FUNCTION',
                'PACKAGE',
                'SEQUENCE'
            )
    ) LOOP
        IF
            t.object_type IN (
                'TABLE',
                'VIEW'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF
            t.object_type IN (

                'SEQUENCE'
            )
        THEN
            EXECUTE IMMEDIATE 'GRANT SELECT ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        ELSIF t.object_type IN (
            'PROCEDURE',
            'FUNCTION',
            'PACKAGE'
        ) THEN
            EXECUTE IMMEDIATE 'GRANT EXECUTE ON OWNER_SCHEMA_NAME.'
            || t.object_name
            || ' TO NEW_TARGET_SCHEMA_NAME';

        END IF;
    END LOOP;
END;


SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014

This most probably has something to do with the new cardinality estimator.

See my reply to this question.

If performance improves when you change the compatibility mode you have a good chance you are running in the same problem.


Update table from calculated temporary table

This might be a good case for using an update trigger.

Look at some of the examples here: http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

What you'll basically do is create a trigger on the first table that iterates a matching row in the second table when a row is added to the first table (on insert).


Use quoted schema in an insert statement executed thorough PreparedStatement in Java

Are you connecting to the database using the credentials of "Quoted_User"? In this case I see no reason to specify the schema name in your SQL statements. On the contrary, do you have the required privileges on the "Quoted_User" schema?

By the way, using mixed case identifiers is really NOT a good idea.


Query detailing differences between rows for a large amount of data

This is not going to look pretty, especially given the more than 300 columns and unavailability of LAG, nor is it likely to perform exceedingly well, but just as something to start with, I would try the following approach:

UNION the two tables.
For each PK in the combined set, get its previous "incarnation" from the archive table (the implementation below uses OUTER APPLY + TOP (1) as a poor man's LAG).
Cast each data column to varchar(max) and unpivot them in pairs, i.e. the current and the previous value (CROSS APPLY (VALUES ...) works well for this operation).
Finally, filter the results based on whether the values in each pair differ from each other.

The Transact-SQL of the above as I see it:

WITH
  Combined AS
  (
    SELECT * FROM dbo.bigtable
    UNION ALL
    SELECT * FROM dbo.bigtable_archive
  ) AS derived,
  OldAndNew AS
  (
    SELECT
      this.*,
      OldCol1 = last.Col1,
      OldCol2 = last.Col2,
      ...
    FROM
      Combined AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          *
        FROM
          dbo.bigtable_archive
        WHERE
          PK = this.PK
          AND UpdateDate < this.UpdateDate
        ORDER BY
          UpdateDate DESC
      ) AS last
  )
SELECT
  t.PK,
  t.UpdateDate,
  x.ColumnName,
  x.OldValue,
  x.NewValue
FROM
  OldAndNew AS t
  CROSS APPLY
  (
    VALUES
    ('Col1', CAST(t.OldCol1 AS varchar(max), CAST(t.Col1 AS varchar(max))),
    ('Col2', CAST(t.OldCol2 AS varchar(max), CAST(t.Col2 AS varchar(max))),
    ...
  ) AS x (ColumnName, OldValue, NewValue)
WHERE
  NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
  t.PK,
  t.UpdateDate,
  x.ColumnName
;


There was never a rule that bitmap indexes were only useful on columns that had relatively few distinct values.  That was a myth that derived from the fact that bitmap indexes aren't appropriate for columns that are unique or mostly unique and that a lot of the columns that you would want to put bitmap indexes on happen to have relatively few distinct values.

Richard Foote (who probably knows more about indexes in Oracle than any other person on the planet) has a nice article on bitmap indexes with many distinct values that walks through why this is perfectly reasonable and appropriate in much more detail.  A followup article comparing bitmap and b-tree indexes on columns with many distinct values is also well worth reading.


Your outer select is incomplete, it has no FROM clause.

Once that's fixed your query may work, if not try putting each of the sub selects inside of a pair of parenthesis, and possibly the whole operation.

Insert Into Production(A, B, C)
SELECT A
     , B
     , ( (SELECT SUM(Month_Production) FROM Production)
       / (SELECT COUNT(Month_Production) FROM Production)
       ) C
  from some_table;  -- <== This is missing

By the way since both the numerator and the denominator are coming from the same table, this would be a more efficient query:

SELECT A
     , B
     , (SELECT SUM(Month_Production)
             / COUNT(Month_Production)
          FROM Production
       ) C
  from some_table;

Since it only has to access the whole Production table once instead of twice


What is causing BETWEEN to be replaced with =?

If you write a query and generate an execution plan, you will see that it, too, translates BETWEEN to a pair of >= and <= predicates. These mean the same thing. Redgate is just showing you what SQL Server stored when you created the constraint, not what you typed. And it has nothing to do with SQL Server 2019.
That said, I prefer the two-predicate pattern, and talk about why BETWEEN is bad for date ranges here and here. My opinion is to avoid exceptions, especially when it is just convenient shorthand / syntactic sugar, and especially when it contradicts how SQL Server stores it internally. In other words, if you don't like that scripting yields a different result than your code, change your code and embrace >= AND <=.


Postgres Logical Replication For Specific tables

Have you tried specifing the list of tables to be logically replicated while creating the publisher?

Create publisher pub_1 for table table_a;
Create subscription sub_1 publication pub_1;

This will send the changes only for table_a to the subscriber.
This is the way to whitelist a selected set of tables to replicate.

ALTER PUBLICATION name ADD TABLE table_b;

This Alter publication command can be used to add/remove any tables after creating a publication.

I dont think there is a way to do this with replication slots.


Having md5(NULL) return a non-NULL value in postgresql

I suggest this alternative design:

-- setup
CREATE TABLE example (content_id serial PRIMARY KEY, content text);
CREATE TABLE test (id serial PRIMARY KEY, tags TEXT, content_id int REFERENCES example);

CREATE UNIQUE INDEX ON example ((md5(content)::uuid)) INCLUDE (content_id); -- !

-- usage
INSERT INTO example(content) VALUES (NULL);        -- allowed multiple times
INSERT INTO example(content) VALUES ('some text');

INSERT INTO test (tags, content_id)
SELECT 'some content defining tags', content_id
FROM   example
WHERE  md5(content)::uuid = md5('some text')::uuid;

db<>fiddle here

Major points

Use a serial column (content_id) as surrogate PK of table example - and as FK reference everywhere. 4 bytes instead of 16.

Enforce uniqueness with a unique index on the expression md5(example)::uuid. Be aware that hash collisions are possible (even if very unlikely while your table isn't huge).

While being at it, add the serial PK column to the index with an INCLUDE clause (Postgres 11 or later) to make it a covering index for fast index-only lookup.

As opposed to a PK column, this allows NULL, and NULL is not considered to be a duplicate of NULL, which should cover your use case. See:

Allow null in unique column

In Postgres 10 or older don't add content_id to the index. Then you don't get index-only scans, of course:

CREATE UNIQUE INDEX ON example ((md5(content)::uuid));

Unless you want to allow only a single instance of NULL, which could be enforced with a function like you posted (introducing the risk of a collision - even if unlikely) or a tiny partial index in addition to the one above:

CREATE UNIQUE INDEX ON example (content_id)
WHERE md5(content)::uuid IS NULL;

See:

Create unique constraint with null columns

Do not store the md5 value as table column (redundantly) at all.

If you want to keep using the function you posted in your answer, consider optimizing it:

CREATE OR REPLACE FUNCTION pg_temp.md5zero(data text)
  RETURNS uuid PARALLEL SAFE IMMUTABLE LANGUAGE sql AS
$func$
SELECT COALESCE(md5(data)::uuid, '00000000000000000000000000000000')
$func$

Faster, and can be inlined. See:

Query slow when using function in the WHERE clause


How to make a single row result query to return multiple rows

A possible Solution to the Problem I found working is a step-by-step solution which looks like this:

BEGIN;

CREATE TEMP TABLE route ON COMMIT DROP AS
  SELECT seq, source, target, km, kmh, clazz, geom_way
  FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex
    WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box',
    1362258, 1625523, FALSE, FALSE) AS route
  LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id
  ORDER BY seq;

CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id)
  SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
  UPDATE filling SET destination =
    (SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <->
      (SELECT geom FROM de_tt_stations WHERE id = filling.station_id)
    LIMIT 1);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
  SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr,
    (SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box
     WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route
     LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;

COMMIT;

Execution Time is fair with around 500ms and less. Is there any possibility to further optimize the Querys?


You can create a time table which stores time in minutes. Then have a column as the foreign key to the record which has got half an hour difference to it. If you need to get half an hour before a time slot and half an hour after it, you will have 2 columns. One referring to the record which has got the half an hour prior value and another has got the half an hour after value. In means the foreign key referenced to the table itself. The table definition would be something like below:

Create Table TimeTbl

(

Id int not null primary key,

TimeValue time not null,

HalfHrBeforeId int null,

HalfHrAfterId int null

)

HalfHrAfter Foreign Key referenced TimeTbl(Id)

HalfHrBeforeId Foreign Key referenced TimeTbl(Id)

In this table you will have:

1, 00:00:00, null, 30

2, 00:01:00, null, 31

...

30, 00:30:00, 1, 60

31, 00:31:00, 2, 61

...

60,01:00:00, 30, 90

...

So if you query this table each includes all time records of a day in minute, you will have foreign key to half an hour before and after and can get and compare their values, to choose the minimum one(the closest half an hour increment).


This answer is about (default) B-tree indexes. See this later, related answer about GiST, GIN etc.:

Why is a GiST index used for filtering on non-leading column?

Here are the results of querying a table on the second column of a multicolumn index.
The effects are easy to reproduce for anybody. Try it at home.
I tested with PostgreSQL 9.0.5 on Debian using a medium sized table of a real-life database with 23322 rows. It implements the n:m relationship between the tables adr (address) and att (attribute), but that's not relevant here. Simplified schema:
CREATE TABLE adratt (
  adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp NOT NULL DEFAULT (now()::timestamp)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);

The UNIQUE constraint effectively implements a unique index. I repeated the test with a plain index to be sure and got identical results as expected.
CREATE INDEX adratt_idx ON adratt(adr_id, att_id);

The table is clustered on the adratt_uni index and before the test I ran:
CLUSTER adratt;
ANALYZE adratt;

Sequential scans for queries on (adr_id, att_id) are as fast as they can possibly be. The multicolumn index can still be used for a query condition on the second index column alone.
I ran the queries a couple of times to populate the cache and the picked the best of ten runs to get comparable results.
1. Query using both columns
SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
  Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms

2. Query using first column
SELECT * FROM adratt WHERE adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       126 |     10 |     10 | 2008-07-29 09:35:54
       125 |     10 |     13 | 2008-07-29 09:35:54
      4711 |     10 |     21 | 2008-07-29 09:35:54
     29322 |     10 |     22 | 2011-06-06 15:50:38
     29321 |     10 |     30 | 2011-06-06 15:47:17
       124 |     10 |     62 | 2008-07-29 09:35:54
     21913 |     10 |     78 | 2008-07-29 09:35:54
       123 |     10 |     90 | 2008-07-29 09:35:54
     28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
  Index Cond: (adr_id = 10)
Total runtime: 0.058 ms

3. Query using second column
SELECT * FROM adratt WHERE att_id = 90;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
       180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)

Output of EXPLAIN ANALYZE:
Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
  Index Cond: (att_id = 90)
Total runtime: 0.849 ms

4. Disable indexscan & bitmapscan
SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Output of EXPLAIN ANALYZE:
Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
  Recheck Cond: (att_id = 90)
  ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
        Index Cond: (att_id = 90)
Total runtime: 0.894 ms

SET enable_bitmapscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Output of EXPLAIN ANALYZE:
Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
  Filter: (att_id = 90)
Total runtime: 2.680 ms

Conclusion
As expected, the multi-column index is used for a query on the second column alone.
As expected, it is less efficient, but the query is still 3x faster than without the index.
After disabling index scans, the query planner chooses a bitmap heap scan, which performs almost as fast. Only after disabling that, too, it falls back to a sequential scan.
See other answer for the original quote from the manual.
Updates since Postgres 9.0
Everything still basically true in Postgres 13. Most notable changes:

index-only scans in Postgres 9.2
True covering indexes with the INCLUDE keyword in Postgres 11
Multiple performance and space improvements (especially for multicolumn indexes) in Postgres 12
Deduplication in Postgres 13.

All in favor of index performance. (Sequential scans got faster, too, though.)


Procedures using TVPs are slower when the TVP numeric value gets larger?

Table Variables, even when used as a parameter (TVP), are given very poor cardinality estimates as opposed to Temp Tables which are much more accurately estimated. This difference is especially noticeable as the amount of data grows that's being utilized in a TVP vs Temp Table. If you looked closely at the Estimated Number of Rows vs the Actual Number of Rows in the execution plans of each implementation, you should see Temp Tables are a lot more accurately estimated.
You can read more on TVPs and their downsides in this Jeremiah Peschka post. Specifically the Gotchas section:

First: the table variable that comes in as a table valued parameter
cannot be changed. You’re stuck with whatever values show up. No
inserts, updates, or deletes can be applied.
Second: table valued parameters are still table variables – they get
terrible cardinality estimates.
We can get around both of these problems with the same technique –
copy the contents of the TVP into a temp table.

Additionally, TVPs used in procedures can result in parameter sniffing issues, as this other post details. This quote adds some specifics to the cardinality estimates you'll encounter with TVPs regardless of how big the actual Table Variable is:

Table Variables (unless you Recompile, or use a Trace Flag), will
sport either a 1 or 100 row estimate, depending on which version of
the Cardinality Estimator you use. The old version guesses 1 row, the
new guesses 100 rows.

This is an additional good article on cardinality estimate issues that result from Table Variables, by Pinal Dave.

One key reason why having a bad cardinality estimate such as an under-estimate in this case, is because it'll result in the SQL Engine under-provisioning the necessary server resources to process the query and serve the data. For example, your query is likely requesting much less memory than it needs for it to be processed because of the low cardinality estimate making the SQL Engine think there's a lot less rows going to be returned than there actually will be. The larger the table variable gets, the larger the discrepancy between the estimate vs actual.

You should pretty much always opt for Temp Tables when possible for the very reason they have many more performance benefits than a Table Variable, and can do almost everything a Table Variable can do and more. In the cases where you need to use a Table Variable, selecting it into a Temp Table first and using that Temp Table in your subsequent querying is the way to go.


Add UNIQUE index fails with duplicate entry error, but no duplicates found

There is no guarantee that the valueaaaaaaaaaaaaaaa-11111 in the message

[23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'mykey'

is the value that actually causes the violation. Seems to be a bug in MariaDB and in MySQL.


How Do I Query this Data in Access Without Creating a Circular Reference

It would probably be easiest to do what you are trying to do by using views, instead of trying to add calculated columns to the table.

EventTheme_IsActive

SELECT ThemeID,
       EventID,
       StartDate,
       FinishDate,
       IIf(Date()>=StartDate And Date()<=FinishDate,True,False) AS IsActive
FROM EventTheme;

Events_Subactive

SELECT e.EventID, e.Name, et.Subactive
FROM Events AS e
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, True, False) AS Subactive
    FROM EventTheme_IsActive
    WHERE IsActive = TRUE
    GROUP BY EventID)  AS et ON e.EventID = et.EventID;

Events_RetroActive

SELECT et.ThemeID, et.EventID, et.StartDate, et.FinishDate, et.IsActive, a.RetroActive
FROM EventTheme_IsActive AS et
LEFT JOIN (
    SELECT EventID, IIF(COUNT(*) > 0, TRUE, FALSE) AS RetroActive
    FROM EventTheme_IsActive
    WHERE IsActive = TRUE
   GROUP BY EventID)  AS a ON et.EventID = a.EventID;

These views may not be updateable but if you just need read access then this should suit your needs.