Sammlung von Newsfeeds
Have you ever wondered how invalid or ambiguous times are handled by Postgres? For example, during a daylight saving time transition in the USA, time switches either from 2AM to 3AM, or from 2AM back to 1AM. On a fall-forward day (the former), how would 2:30am be represented? Is 1:30AM during a fall-back day represented as the first or second 1:30AM of that day?
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
An introductory story (with some slight “drama” added for fun) from those good old days of on-site consulting So…I’m at a client where the database is not behaving nicely among other things…what a crappy DB product indeed I hear, it gets cranky every morning although there should be a constant workload and is saturating the CPU and making some important queries time out!
PostGIS is a "geospatial database" and the "geo" in "geospatial" is an important qualifier: it means that all the coordinates in PostGIS point, lines, and polygons can be located somewhere on the earth.
What is the difference between two dates? You would think there was one answer, but there isn't. You can give an answer in calendar terms (years/months/days), the number of days, or the number of seconds. Postgres offers all of these options:
The INTERVAL data type stores time duration as months, days, and seconds. Years are represented as a fixed number of months, and hours and minutes as a fixed number of seconds. Using INTERVAL values makes time computation very simple:
Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II.
There is a GUC to enable stacktrace generation : backtrace_functions. Set it to a comma-separated function names.
SET backtrace_functions TO 'func1,func2';
If the error is thrown from one of these functions, a backtrace will be generated and logged into the server log.
A lot has been written about effective_cache_size in postgresql.conf and about PostgreSQL performance in general. However, few people know what this famous parameter really does. Let me share some more insights.
In my years with Postgres, I have seen some amazingly complex queries posted to the email lists. I have never understood how people can read complex queries with no formatting, e.g., no keyword capitalization, no indenting, no line breaks for new clauses:
Backups are a key staple of running any database. Way back in the day, a good friend and colleague wrote one of the most used Postgres backup tools called wal-e. Wal-e was initially written in just a few days, and rolled out to the fleet of databases we managed in the early days at Heroku.
The PostgreSQL 13 Beta is out in the testing phase with a rich feature set. It is a very good learning effort to participate in the testing of one of the finest databases in the world. It does not matter how much development, coding, and administration experience you have for the testing of the PostgreSQL version; you can participate in reviewing the documentation, validation of features, and on some small tasks. The complete testing guide is also available on the wiki page.
Making security easy to use is crucial because hard-to-use security is likely to be neglected entirely. SCRAM with channel binding is a variation of password authentication that is almost as easy to use, but much more secure.
In basic password authentication, the connecting client simply sends the server the password. Then the server checks that it’s the right one, and allows the client to connect. Basic password authentication has several weaknesses which are addressed with SCRAM and channel binding.
There seem to be as many methods of writing SQL queries as ways of writing essays. While spacing, capitalization, and naming are all personal preferences, there are also logical arguments for why certain styles are better than others.