フィードアグリゲーター

Tim Colles: string_to_array behaviour with an empty string

planet postgresql - 2018-10-25(木) 17:49:23

So this caught me out the other day. We had a query which broke out multiple delimited strings each with one or more elements (but always the same number of elements in each) into multiple records using string_to_array and unnest. In each string any element could potentially have an empty value, but at least one string would always have values for all the elements. This meant there should always be at least one output row. So below worked as expected:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a|b'),('1|2')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | 1 b | 2 (2 rows)

Below worked entirely as expected as well:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a|b'),('|')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | b | (2 rows)

So did this when we had some data with just the minimum one element in the input:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a'),('1')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | 1 (1 row)

But then we discovered that there was some data that was being completely lost in the query output. Turns out that this occurred when, like above there was just one element in the input, but one value was empty:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a'),('')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ (0 rows)

So after some investigation it turns out that this happens because string_to_array on an empty string returns an array with no elements rather than an array with one element which is an empty string. This does actually make sense because without any application specific context it is indeterminate whether an empty string should be treated a

[...]
カテゴリー: postgresql

Ozgun Erdogan: Why Citus Data is donating 1% equity to PostgreSQL organizations

planet postgresql - 2018-10-24(水) 21:15:00

Today, we’re excited to announce that we have donated 1% of Citus Data’s stock to the non-profit PostgreSQL organizations in the US and Europe. The United States PostgreSQL Association (PgUS) has received this stock grant. PgUS will work with PostgreSQL Europe to support the growth, education, and future innovation of Postgres both in the US and Europe.

To our knowledge, this is the first time a company has donated 1% of its equity to support the mission of an open source foundation.

To coincide with this donation, we’re also joining the Pledge 1% movement, alongside well-known technology organizations such as Atlassian, Twilio, Box, and more.

Why make this donation? Because it’s the right thing to do

At Citus Data, we have a special relationship with PostgreSQL. Our flagship product Citus builds on and transforms Postgres into a distributed database. Our team attends and presents at Postgres conferences. We sponsor PostgreSQL events and meetups. We learn from and contribute back to the community. Our customers use many drivers, tools, and connectors in the Postgres ecosystem.

In short, our team and our company have hugely benefited from Postgres over the years. We owe a lot of our success to the PostgreSQL project and the many talented people involved.

Today, we’re giving back to the community that who made this project so successful.

Why is this important? Because PostgreSQL is the world’s only independent database

PostgreSQL is unique.

Postgres is not only the world’s most advanced open source relational database. It is also the world’s only independent database. No single company backs or represents the Postgres project.

In one sense, the PostgreSQL community came to be, rather than being put together by one company. And the Postgres open source community has been steadily building new features for more than 20 years.

Signage for the first PostgreSQL Conference (PGCon) celebrating 10 years of development, in 2006. Photograph by Oleg Bartunov.

Today, Postgres offers a capable, robust, and popular alternativ

[...]
カテゴリー: postgresql

Nickolay Ihalainen: PostgreSQL locking, part 2: heavyweight locks

planet postgresql - 2018-10-24(水) 20:39:16

PostgreSQL locking visibility for application developers and DBAs is in most cases related to heavyweight locks. Complex database locking operations require full instrumentation using views from the system catalog. It should be clear which object is locked by a specific database “backend” process. An alternative name for any lock is “bottleneck”. In order to make database operations parallel we should split a single bottleneck into multiple operation specific tasks.

This is the second part of three blog posts related to table level locks. The previous post was about row-level locks, and a subsequent post reviews the latches protecting internal database structures.

Example environment

A simple table with several rows:

CREATE TABLE locktest (c INT); INSERT INTO locktest VALUES (1), (2); Helper view

In order to check different types of these locks, let’s create a helper view as suggested by Bruce Momjian in his presentation:

CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted, CASE WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL THEN virtualxid || ' ' || transactionid WHEN virtualxid::text IS NOT NULL THEN virtualxid ELSE transactionid::text END AS xid_lock, relname, page, tuple, classid, objid, objsubid FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE -- do not show our view’s locks pid != pg_backend_pid() AND -- no need to show self-vxid locks virtualtransaction IS DISTINCT FROM virtualxid -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; RowShareLock (ROW SHARE)

Many applications use the read-modify-write paradigm. For instance, the appl

[...]
カテゴリー: postgresql

David Rowley: PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11

planet postgresql - 2018-10-24(水) 18:17:41

During the PostgreSQL 11 development cycle an impressive amount of work was done to improve table partitioning.  Table partitioning is a feature that has existed in PostgreSQL for quite a long time, but it really wasn’t until version 10 that it started to become a highly useful feature. We’d previously claimed that table inheritance was our implementation of partitioning, which was true.  It just left you to do much of the work yourself manually. For example, during INSERTs, if you wanted the tuples to make it to your partitions then you had to set up triggers to do that for you. Inheritance partitioning was also slow and hard to develop additional features on top of.

In PostgreSQL 10 we saw the birth of “Declarative Partitioning”, a feature which is designed to solve many of the problems which were unsolvable with the old inheritance method of partitioning. This has resulted in a much more powerful tool to allow you to horizontally divide up your data!

Feature Comparison

PostgreSQL 11 comes complete with a very impressive set of new features to both help improve performance and also to help make partitioned tables more transparent to applications.

Feature PG9.6 PG10 PG11 Declarative Partitioning Auto Tuple Routing – INSERT Auto Tuple Routing – UPDATE Optimizer Partition Elimination  1 1 Executor Partition Elimination 2 Foreign keys 3 Unique indexes 4 Default Partitions Hash Partitions FOR EACH ROW triggers Partition-level joins  5 Partition-level aggregation Foreign partitions Parallel Partition Scans
  1. Using constraint exclusion
  2. Append nodes only
  3. On partitioned table referencing non-partitioned table only
  4. Indexes must contain all partition key columns
  5. Partition constraint on both sides must match exactly

 

Performance

We’ve also got good news here!  A new method of performing partition elimination has been added. This new algorithm is able to determine matching partitions by looking at the query’s WHERE clause. The previous algo

[...]
カテゴリー: postgresql

Quinn Weaver: How to set up scram-sha-256 authentication in PostgreSQL

planet postgresql - 2018-10-24(水) 15:09:00
md5: everyone uses it. But it's insecure — unsuitable for authentication or password storage. PostgreSQL 10 adds support for SCRAM SHA-256 authentication, which is far better1… but how do you use it? Documentation is scarce.

Below I give the complete steps, for two different cases:
  • The case where you migrate every user.
  • The case where you migrate only a subset of users. Step 1 explains why you might need to do this. In this case, you can save time and complexity by creating a containing role, adding all the users you want to migrate to that role, and then configuring that role to work with scram-sha-256. That way you don't have to do it once for each user.
Enough preamble; let's begin!
  1. First, decide which users to migrate to scram-sha-256. The ideal is to migrate everyone, all at once. In practice you may be unable to do that, for a few reasons:
    • Older client tools don't support scram-sha-256. You need at least psql 10, or pgAdmin 4.
    • Your app's client libraries may not support scram-sha-256. At a minimum, you will probably have to upgrade your client libraries2.
    • And it's not enough just to upgrade your libraries; you also have to change the password for each user who's migrating, because PostgreSQL needs to hash this password in scram-sha-256 format. There's no automatic rehash (there can't be, since PostgreSQL doesn't store the cleartext of the old password).
    • Finally, besides your own code, you may have third-party apps connecting to your database, whose library versioning policy and password reset policy you don't control.
  2. Having chosen the users to migrate, upgrade your relevant client libraries, psql/postgresql-client packages, and/or pgAdmin versions.
  3. If you're migrating all users, skip to the next step. If, instead, you're migrating a subset of users, then create a new role, and put all those users in that role. This step saves a bit of repetitive config work. $ sudo su - postgres
    psql
    postgres=# create role scram_sha_256_users nologin ROLE alice, bob, eve; … where alice, bob, and eve are the users yo
[...]
カテゴリー: postgresql

Joshua Drake: PostgresConf Silicon Valley recap

planet postgresql - 2018-10-24(水) 03:31:00


PostgresConf ran its first Silicon Valley conference on October 15th and 16th in conjunction with Silicon Valley Postgres. The two day conference was considered a “local” or development conference where our focus was introducing the PostgresConf experience to new attendees and partners with world class content. We believe we succeeded in that.

We brought new friends to the community with the addition of Yugabyte, Scalegrid, and AgensGraph. We also had old friends return with Pivotal, AWS, Microsoft, 2ndQuadrant, Timescale, Compose, and Nutanix.

This is the first conference we have organized where the attendance went up during the conference. All community conferences know the stress of waiting for people to register. We all register at the last possible moment but for attendance to increase as the conference is running was new for us. It added a new level of legitimacy to our purpose of:

The conference had 139 attendees and as the goal was 100-150, we are quite pleased with the turn out. We will be returning in 2019 and we look forward to continuing to build the Silicon Valley Community.
Thank you to the attendees, supporters, and organizers for helping pull off yet another amazing event!
カテゴリー: postgresql

Jobin Augustine: PostgreSQL Q&A: Building an Enterprise-Grade PostgreSQL Setup Using Open Source Tools

planet postgresql - 2018-10-20(土) 01:50:33

Hello everyone, and thank you to those that attended our webinar on Building an Enterprise-grade PostgreSQL setup using open source tools last Wednesday. You’ll find the recordings of such as well as the slides we have used during our presentation here.

We had over forty questions during the webinar but were only able to tackle a handful during the time available, so most remained unanswered. We address the remaining ones below, and they have been grouped in categories for better organization. Thank you for sending them over! We have merged related questions and kept some of our answers concise, but please leave us a comment if you would like to see a particular point addressed further.

Backups Q: In our experience, pg_basebackup with compression is slow due to single-thread gzip compression. How to speed up online compressed full backup?

Single-thread operation is indeed a limitation of pg_basebackup, and this is not limited to compression only. pgBackRest is an interesting alternative tool in this regard as it does have support for parallel processing.

Q: Usually one setup database backup on primary DB in a HA setup. Is it possible to automatically activate backup on new primary DB after Patroni failover? (or other HA solutions)

Yes. This can be done transparently by pointing your backup system to the “master-role” port in the HAProxy instead – or to the “replica-role” port; in fact, it’s more common to use standby replicas as the backup source.

Q: Do backups and WAL backups work with third party backup managers like NetBackup for example?

Yes, as usual it depends on how good the vendor support is. NetBackup supports PostgreSQL, and so does Zmanda to mention another one.

Security and auditing Q: Do you know a TDE solution for PostgreSQL? Can you talk a little bit about the encryption at rest solution for Postgres PCI/PII applications from Percona standpoint.

At this point PostgreSQL does not provide a native Transparent Data Encryption (TDE) functionality, relying instead in the underlying file system for data

[...]
カテゴリー: postgresql

Sebastian Insausti: How to Minimize RPO for Your PostgreSQL Databases Using Point in Time Recovery

planet postgresql - 2018-10-19(金) 23:48:02

In a disaster recovery plan, your Recovery Point Objective (RPO) is a key recovery parameter that dictates how much data you can afford to lose. RPO is listed in time, from seconds to days. Effectively, RPO is directly dependent on your backup system. It marks the age of your backup data that you must recover in order to resume normal operations.

If you do a nightly backup at 10 p.m. and your database system crashes beyond repair at 3 p.m. the following day, you lose everything that was changed since your last backup. Your RPO in this particular context is the previous day's backup, which means you can afford to lose one day’s worth of changes.

The below diagram from our Whitepaper on Disaster Recovery illustrates the concept.

For tighter RPO, a backup might not be enough though. When backing up your database, you are actually taking a snapshot of the data at a given moment. So when you are restoring a backup, you will miss the changes that happened between the last backup and the failure.

This is where the concept of Point In Time Recovery (PITR) comes in.

What is PITR? Related resources  How to do Point-in-Time Recovery of MySQL & MariaDB Data using ClusterControl  Become a PostgreSQL DBA: Point-in-Time Database Restoration  PostgreSQL Streaming Replication - a Deep Dive  Become a ClusterControl DBA: Safeguarding your Data  How to Overcome Accidental Data Deletion in MySQL & MariaDB

Point In Time Recovery (PITR), as the name states, involves restoring the database at any given moment in the past. For being able to do this, we will need to restore a backup, and then apply all the changes that happened after the backup until rightbefore the failure.

For PostgreSQL, the changes are stored in the WAL logs (for more details about WALs and the data they store, you can check out this blog).

So there are two things we need to ensure for being able to perform a PITR: The backups and the WALs (we need to setup continuous archiving for them).

For performing the PITR, we will need to recover the backup and th

[...]
カテゴリー: postgresql

Bruce Momjian: Long-Haul Behavior

planet postgresql - 2018-10-19(金) 22:30:01

People new to the Postgres community are often confused by the deliberateness of the community on some issues, and the willingness to break backward compatibility in other areas. The source code was created 32 years ago, and many of us have been involved with Postgres for 10-20 years. With that length of involvement, we are acutely aware of the long-term consequences of our decisions. Many proprietary and open source developers don't think years in advance, but with Postgres, it is our normal behavior.

This leads to some unusual Postgres behaviors:

  • Weekly source code comment improvements
  • Yearly source code reformatting
  • Exhaustive discussions about even minor changes, particularly user-visible ones
  • API redesigns

This focus on detail often strikes new users as unusual, but it makes sense when a multi-year view is considered.

カテゴリー: postgresql

Regina Obe: http extension for windows 64 and 32-bit

planet postgresql - 2018-10-19(金) 16:41:00
Updated October 19th, 2018 64-bit package for PostgreSQL 11 http extension v1.2.4 release

For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension v1.2.2 release for PostgreSQL 10, 9.6, 9.5, and 9.4 Windows 64-bit.

These should work fine on both PostgreSQL EDB windows distributions as well as the BigSQL windows 64 distributions. Instructions in the ReadMe.txt for where to copy the files to for each distribution. Caution should be taken especially with BigSQL to not overwrite existing BigSQL files. Many of the files in this already exist in the BigSQL distribution since we both compile using the Mingw64 chain.

If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.

Continue reading "http extension for windows 64 and 32-bit"
カテゴリー: postgresql

Tatsuo Ishii: Pgpool-II 4.0 released! (SCRAM authentication)

planet postgresql - 2018-10-19(金) 16:09:00
After 1 year of hard work by Pgpool-II developers,  Pgpool-II 4.0 is finally out.
This version celebrates the 15th anniversary of Pgpool-II, and introduces rich new features. I will explain them one by one in a series of blogs.
Support for SCRAM authentication SCRAM authentication is a modern, secure authentication method supported by PostgreSQL 10 or later. Pgpool-II 4.0 supports the method by providing multiple new functionalities.

  • pool_hba.conf now has "scram-sha-256" authentication method.
  • If this method is specified, users connecting to Pgpool-II are required  to response in proper way of SCRAM authentication.
  • Passwords stored in pool_passwd can be used to verify user's password before a session starts.
  • Passwords can be store in pool_passwd in multiple ways: clear text or AES256 encrypted (md5 hashed passwords can also be stored in pool_passwd but in this case SCRAM cannot use the password).
  • AES256 encryption is preferred and the key to decrypt it is stored in .pgpoolkey under the user's home directory who started Pgpool-II, or a file specified by PGPOOLKEYFILE environment variable.
  • For AES256 encryption, new tool pg_enc can be used.
Of course, Pgpool-II can authenticate itself in SCRAM while connecting to PostgreSQL, if it requires SCRAM authentication to Pgpool-II.

Good thing with Pgpool-II 4.0 is, it allows users to choose different authentication method in users vs. Pgpool-II  and Pgpool-II vs. PostgreSQL. For example, using SCRAM between users and Pgpool-II, while using md5 authentication method between Pgpool-II and PostgreSQL.

SCRAM authentication can also be used for health check and replication delay check.  Before 4.0, user name and password for them are stored in clear text format in pgpool.conf. This is not the most secure way. Pgpool-II 4.0 allows to store AES 256 encrypted passwords in pgpool.conf. .pgpookey is also used for decrypting these passwords.

Next time I will explain about other features of authentication method in Pgpool-II 4.0.
カテゴリー: postgresql

PostgreSQL 11 がリリースされました! (2018-10-18)

www.postgresql.jp news - 2018-10-19(金) 15:07:59
PostgreSQL 11 がリリースされました! (2018-10-18) kuwamura 2018/10/19 (金) - 15:07
カテゴリー: postgresql

Jignesh Shah: Hello PostgreSQL 11 - Get ready to say goodbye to PostgreSQL 9.3

planet postgresql - 2018-10-19(金) 14:26:00
Earlier today (Oct 18, 2018), the PostgreSQL community announced the release of PostgreSQL 11.  Having done multiple software releases earlier, I appreciate the hard work by all contributors to get yet another major release on schedule. It is hard to do a major release every year and the community has been doing it since PostgreSQL 8.4 making this the 10th  release in the last decade. 
Everybody has their favorite feature in PostgreSQL 11 and I have one that is top on my list which is the transactional support in stored procedures2nd Quadrant had first announced that feature end of last year and at that time, it instantly became my favorite as I see it as a giant leap in PostgreSQL as it allows people to essentially write long data routines like ETL broken down in multiple transactions. Of course many users will certainly enjoy the improvements in  table partitioning system, query parallelism, and just-in-time (JIT) compilation for accelerating the execution of expressions in queries. However, the developers will certainly get more freedom with the stored procedure improvements.
With the release of PostgreSQL 11, now there are 6 major releases supported: PostgreSQL 9.3, 9.4, 9.5, 9.6, 10 and, 11. It is definitely a good time to start thinking to upgrade your PostgreSQL 9.3 databases. As per the versioning policy, the final minor release for PostgreSQL 9.3 will be on November 8th, 2018.  PostgreSQL 9.3 will be the last major version which does not support logical replication which was first introduced in PostgreSQL 9.4.  Hence, I expect this is will be the last painful upgrade because PostgreSQL 9.4 onwards you can always leverage logical replication to minimize the downtime while switching to a new version. All is not lost for PostgreSQL 9.3, while the experience is not exactly the same there are still tools available using the older trigger based replication to help or just bite the bullet and upgrade once with a small maintenance window as later versions will give you more options for your next major vers[...]
カテゴリー: postgresql

Jobin Augustine: PostgreSQL 11! Our First Take On The New Release

planet postgresql - 2018-10-18(木) 23:19:38

You may be aware that the new major version of PostgreSQL has been released today. PostgreSQL 11 is going to be one of the most vibrant releases in recent times. It incorporates many features found in proprietary, industry-leading database systems, further qualifying PostgreSQL as a strong open source alternative.

Without further ado, let’s have a look at some killer features in this new release.

Just In Time (JIT) Compilation of SQL Statements

This is a cutting edge feature in PostgreSQL: SQL statements can get compiled into native code for execution. It’s well know how much Google V8 JIT revolutionized the JavaScript language. JIT in PostgreSQL 11 supports accelerating two important factors—expression evaluation and tuple deforming during query execution—and helps CPU bound queries perform faster. Hopefully this is a new era in the SQL world.

Parallel B-tree Index build

This could be the most sought after feature by DBAs, especially those migrating large databases from other database systems to PostgreSQL. Gone are the days when a lot of time was spent on building indexes during data migration. Index maintenance (rebuild) for very large tables can now make an effective use of multiple cores in the server by parallelizing the operation, taking considerably less time to complete.

Lightweight and super fast ALTER TABLE for NOT NULL column with DEFAULT values

In the process of continuous enhancement and adding new features, we see several application developments that involve schema changes to the database. Most such changes include adding new columns to a table. This can be a nightmare if a new column needs to be added to a large table with a default value and a NOT NULL constraint. This is because an ALTER statement can hold a write lock on the table for a long period. It can also involve excessive IO due to table rewrite. PostgreSQL 11 addresses this issue by ensuring that the column addition with a default value and a NOT NULL constraint avoids a table rewrite.  

Stored procedures with transaction control

Post

[...]
カテゴリー: postgresql

Andrew Dunstan: Adding new table columns with default values in PostgreSQL 11

planet postgresql - 2018-10-18(木) 22:51:01

In PostgreSQL version 10 or less, if you add a new column to a table without specifying a default value then no change is made to the actual values stored. Any existing row will just fill in a NULL for that column. But if you specify a default value, the entire table gets rewritten with the default value filled in on every row. That rewriting behavior changes in PostgreSQL 11.

In a new feature I worked on and committed, the default value is just stored in the catalog and used where needed in rows existing at the time the change was made. New rows, and new versions of existing rows, are written with the default value in place, as happens now. Any row that doesn’t have that column must have existed before the table change was made, and uses this value stored in the catalog when the row is fetched. The great advantage of this is that adding a column with a default value is now quite a fast and cheap operation, whereas before for very large tables it has been horribly, often intolerably slow. Rewriting a whole multi-terabyte table is really something you want to avoid.

The default value doesn’t have to be a static expression . It can be any non-volatile expression, e.g. CURRENT_TIMESTAMP. Volatile expressions such as random() will still result in table rewrites. For a non-volatile expression, it is evaluated at the time the statement runs and the result is what is stored in the catalog for use with existing rows. Of course, for new rows the expression will be re-evaluated at the time the row is created, as happens now.

Any time that the table does get rewritten, say by VACUUM FULL, all this is cleaned up, as there will be no rows left needing the value from the catalog. Likewise, any time that a row is updated the column’s value is filled in on the new row version, so over time the proportion of rows using the value fetched from the catalog will decrease.

カテゴリー: postgresql

Andrea Cucciniello: PGDay Down Under 2018

planet postgresql - 2018-10-18(木) 21:34:36

After the success of last year’s event, the second PGDay held in Australia, we’re back this year with PGDay Down Under. The name “Down Under” refers to Australia and New Zealand, due to the fact these countries are located in the lower latitudes of the southern hemisphere.

The conference is a one-day community event organized by the newborn PostgreSQL Down Under Incorporated (also known as PGDU), a not-for-profit association established to support the growth and learning of PostgreSQL, the world’s most advanced open source database, in Australia and New Zealand.

PGDay Down Under aims to satisfy a large audience of PostgreSQL users and enthusiasts by selecting a wide range of talks and presentations that are of interest to:

  • Database administrators that are already using PostgreSQL, or use a different database and are interested in learning about PostgreSQL
  • Team leaders, architects and decision makers considering PostgreSQL as an alternative database to more traditional proprietary databases
  • Developers that have data persistence requirements or are just wanting to expand their knowledge
  • IT enthusiasts who enjoy getting involved in the open source community

Don’t wait until last minute, early bird tickets are now available, you can register for PGDay Down Under here. To complete the registration log in with your community account or preferred third party platform.

The Call for Papers is open until October 21, at midnight (AEDT). We have already received many submissions and we hope to get some more. To submit your proposal for a presentation please visit 2018.pgdu.org and click on Call For Papers on the top right corner.

This event would not be possible without the generous sponsorship and support from many different organisations, such as 2ndQuadrant. Any proceeds from the conference will be used to organize more community events in other locations, creating a virtuous cycle to better support the growth of the PostgreSQL community in the region.

If you are interested in sponsoring and/or volunteering at the event

[...]
カテゴリー: postgresql

11.0

postgresql.org - 2018-10-18(木) 09:00:00
11.0 is the latest release in the 11 series.
カテゴリー: postgresql

Bruce Momjian: "Get Off My Lawn"

planet postgresql - 2018-10-17(水) 23:45:01

As you might know, Postgres is old-school in its communication methods, relying on email for the bulk of its development discussion, bug reports, and general assistance. That's the way it was done in 1996, and we are still doing it today. Of course, some projects have moved on to github or Slack.

As a project, we try to take advantage of new technologies while retaining technologies that are still optimal, and email is arguably one of them. Email hasn't changed much since 1996, but email clients have. Previously all email was composed using a text-editor-like interface, which allowed for complex formatting and editing. New email tools, like Gmail, provide a more simplified interface, especially on mobile devices. This simplified interface is great for composing emails while commuting on a train, but less than ideal for communicating complex topics to thousands of people.

This email captures the requirements of communicating technical points to thousands of people, and the need for fine-grained composition. Basically, when you are emailing thousands of people, taking time to make the communication clear is worthwhile. However, this email explains the reality that many email tools are more tailored for effortless communication on devices with limited features.

Continue Reading »

カテゴリー: postgresql

Craig Kerstiens: Commenting your Postgres database

planet postgresql - 2018-10-17(水) 23:36:00

At Citus whether it’s looking at our own data or helping a customer debug a query I end up writing a lot of SQL. When I do write SQL I do my best to make sure it’s readable in case others need to come along and understand or modify, but admittedly I do have some bad habits from time to time such as using implicit joins. Regardless of my bad habits I still try to make my SQL and database as easy to understand for someone not already familiar with it. One of the biggest tools for that is comments.

Even early on in learning to program we take advantage of comments to explain and describe what our code is doing, even in times when it seems obvious. I see this less commonly in SQL and databases, which is a shame because data is just as valuable so making it easier to reason and work with seems logical. Postgres has a few great mechanisms you can start leveraging when it comes to commenting so you can better document things.

Inline commenting your queries

The place I most use comments is in larger queries I write. Yes, [common table expressions] can be an optimization fence, but they also allow you to create building blocks within your SQL making a query easier to understand. Though you shouldn’t stop there. Within SQL you can have a line comment when you preface it with --. You can do this at the start of a line, or at the end with what follows becoming a comment. We can see this in action on our query example from our earlier CTE blog post:

-- Getting a list of all opportunities opened longer than 30 days ago, but earlier than 60 days ago WITH opp_list AS ( SELECT opportunities.id as opportunity_id, account_id, accounts.name as account_name, opportunities.amount as opportunity_amount, opportunities.created_at as opportunity_created FROM opportunities, accounts WHERE opportunities.created_at <= now() - '30 days'::interval AND opportunities.created_at >= now() - '60 days'::interval AND opportunities.account_id = accounts.id ), -- Get a list of all contacts we[...]
カテゴリー: postgresql

Nickolay Ihalainen: PostgreSQL locking, Part 1: Row Locks

planet postgresql - 2018-10-16(火) 23:26:00

An understanding of PostgreSQL locking is important to build scalable applications and avoid downtime. Modern computers and servers have many CPU cores and it’s possible to execute multiple queries in parallel. Databases containing many consistent structures with changes made by queries or background processes running in parallel could crash a database or even corrupt data. Thus we need the ability to prevent access from concurrent processes, while changing shared memory structures or rows. One thread updates the structure while all others wait (exclusive lock), or multiple threads read the structure and all writes wait. The side effect of waits is a locking contention and server resources waste. Thus it’s important to understand why waits happen and what locks are involved. In this article, I review PostgreSQL row level locking.

In follow up posts, I will investigate table-level locks and latches protecting internal database structures.

Row locks – an overview

PostgreSQL has many locks at different abstraction levels. The most important locks for applications are related to MVCC implementation – row level locking. In second place – locks appearing during maintenance tasks (during backups/database migrations schema changes) – table level locking. It’s also possible—but rare—to see waits on low level PostgreSQL locks. More often there is a high CPU usage, with many concurrent queries running, but overall server performance reduced in comparison with normal number of queries running in parallel.

Example environment

To follow along, you need a PostgreSQL server with a single-column table containing several rows:

postgres=# CREATE TABLE locktest (c INT); CREATE TABLE postgres=# INSERT INTO locktest VALUES (1), (2); INSERT 0 2 Row locks

Scenario: two concurrent transactions are trying to select a row for update.

PostgreSQL uses row-level locking in this case. Row level locking is tightly integrated with MVCC implementation, and uses hidden xmin and xmax fields.

xmin  and xmax  store the transaction id. All statement[...]
カテゴリー: postgresql

ページ