フィードアグリゲーター

Bruce Momjian: Imperative to Declarative to Imperative

planet postgresql - 2019-02-16(土) 02:00:01

This email thread from 2017 asks the question of whether there is an imperative language that generates declarative output that can be converted into an imperative program and executed. Specifically, is there an imperative syntax that can output SQL (a declarative language) which can be executed internally (imperatively) by Postgres?

The real jewel in this email thread is from Peter Geoghegan, who has some interesting comments. First, he explains why developers would want an imperative language interface, even if it has to be converted to declarative:

Some developers don't like SQL because they don't have a good intuition for how the relational model works. While SQL does have some cruft — incidental complexity that's a legacy of the past — any language that corrected SQL's shortcomings wouldn't be all that different to SQL, and so wouldn't help with this general problem. QUEL wasn't successful because it was only somewhat better than SQL was at the time.

Continue Reading »

カテゴリー: postgresql

Baron Schwartz: Citus: Scale-Out Clustering and Sharding for PostgreSQL

planet postgresql - 2019-02-15(金) 10:02:02

I wrote yesterday about Vitess, a scale-out sharding solution for MySQL. Another similar product is Citus, which is a scale-out sharding solution for PostgreSQL. Similar to Vitess, Citus is successfully being used to solve problems of scale and performance that have previously required a lot of custom-built middleware.

カテゴリー: postgresql

11.2, 10.7, 9.6.12, 9.5.16, 9.4.21 リリース (2019-02-14)

www.postgresql.jp news - 2019-02-15(金) 09:02:58
11.2, 10.7, 9.6.12, 9.5.16, 9.4.21 リリース (2019-02-14) harukat 2019/02/15 (金) - 09:02
カテゴリー: postgresql

Longhorn PHP 2019 Schedule

php.net - 2019-02-15(金) 00:03:51
カテゴリー: php

11.2

postgresql.org - 2019-02-14(木) 09:00:00
11.2 is the latest release in the 11 series.
カテゴリー: postgresql

10.7

postgresql.org - 2019-02-14(木) 09:00:00
10.7 is the latest release in the 10 series.
カテゴリー: postgresql

9.6.12

postgresql.org - 2019-02-14(木) 09:00:00
9.6.12 is the latest release in the 9.6 series.
カテゴリー: postgresql

9.5.16

postgresql.org - 2019-02-14(木) 09:00:00
9.5.16 is the latest release in the 9.5 series.
カテゴリー: postgresql

9.4.21

postgresql.org - 2019-02-14(木) 09:00:00
9.4.21 is the latest release in the 9.4 series.
カテゴリー: postgresql

Markus Winand: PostgreSQL 11 Reestablishes Window Functions Leadership

planet postgresql - 2019-02-14(木) 09:00:00
What’s new in PostgreSQL 11

PosgreSQL 11 was released four months ago and my review is long overdue. Here we go!

With respect to standard SQL, the main theme in PostgreSQL 11 is window functions (over). For almost eight years, from 2009 until 2017, PostgreSQL was the only major free open-source product to support SQL window functions. Just a year later, by September 2018, all open-source competitors have caught up…and some even overtook PostgreSQL. The PostgreSQL community was prepared. PostgreSQL 11 was just released in 2018, and it has restored and even expanded its leadership position.0

This article explains this race and covers other improvements in PostgreSQL 11.

Complete SQL:2011 Over Clause

The over clause defines which rows are visible to a window function. Window functions were originally standardized with SQL:2003, and PostgreSQL has supported them since PostgreSQL 8.4 (2009). In some areas, the PostgreSQL implementation was less complete than the other implementations (range frames, ignore nulls), but in other areas it was the first major system to support them (the window clause). In general, PostgreSQL was pretty close to the commercial competitors, and it was the only major free database to support window functions at all—until recently.

In 2017, MariaDB introduced window functions. MySQL and SQLite followed in 2018. At that time, the MySQL implementation of the over clause was even more complete than that of PostgreSQL, a gap that PostgreSQL 11 closed. Furthermore, PostgreSQL is again the first to support some aspects of the over clause, namely the frame unit groups and frame exclusion. These are not yet supported by any other major SQL database—neither open-source, nor commercial.

The only over clause feature not supported by PostgreSQL 11 are pattern and related clauses. These clauses were just standardized with SQL:2016 and do a framing based on a regular expression. No major database supports this this framing yet.1

Frame Units

Before looking into the new functionality in PostgreSQL 11, I’l

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

Jobin Augustine: plprofiler – Getting a Handy Tool for Profiling Your PL/pgSQL Code

planet postgresql - 2019-02-14(木) 03:20:18

PostgreSQL is emerging as the standard destination for database migrations from proprietary databases. As a consequence, there is an increase in demand for database side code migration and associated performance troubleshooting. One might be able to trace the latency to a plsql function, but explaining what happens within a function could be a difficult question. Things get messier when you know the function call is taking time, but within that function there are calls to other functions as part of its body. It is a very challenging question to identify which line inside a function—or block of code—is causing the slowness. In order to answer such questions, we need to know how much time an execution spends on each line or block of code. The plprofiler project provides great tooling and extensions to address such questions.

Demonstration of plprofiler using an example

The plprofiler source contains a sample for testing plprofiler. This sample serves two purposes. It can be used for testing the configuration of plprofiler, and it is great place to see how to do the profiling of a nested function call. Files related to this can be located inside the “examples” directory. Don’t worry—I’ll be running through the installation of plprofiler later in this article.

$ cd examples/

The example expects you to create a database with name “pgbench_plprofiler”

postgres=# CREATE DATABASE pgbench_plprofiler; CREATE DATABASE

The project provides a shell script along with a source tree to test plprofiler functionality. So testing is just a matter of running the shell script.

$ ./prepdb.sh dropping old tables... .... Running session level profiling

This profiling uses session level local-data. By default the plprofiler extension collects runtime data in per-backend hashtables (in-memory). This data is only accessible in the current session, and is lost when the session ends or the hash tables are explicitly reset. plprofiler’s run command will execute the plsql code and capture the profile information.

This is illustrated by below

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

Joe Conway: PostgreSQL Deep Dive: How Your Data Model Affects Storage

planet postgresql - 2019-02-14(木) 02:35:00

I want to take a few minutes for a deep dive into the effect your data model has on storage density when using PostgreSQL. When this topic came up with a customer, I explained my thoughts on the matter, but I realized at the time that I had never done a reasonably careful apples-to-apples test to see just exactly what the effect is, at least for a model sample size of one. So here it is.

カテゴリー: postgresql

Bruce Momjian: Composite Values

planet postgresql - 2019-02-14(木) 02:15:01

You might not be aware that you can store a virtual row, called a composite value, inside a database field. Composite values have their own column names and data types. This is useful if you want to group multiple statically-defined columns inside a single column. (The JSON data types are ideal for dynamically-defined columns.)

This email thread explains how to define and use them, I have a presentation that mentions them, and the Postgres manual has a section about them.

カテゴリー: postgresql

KUNTAL GHOSH: Data alignment in PostgreSQL

planet postgresql - 2019-02-13(水) 17:23:00
When data are naturally aligned, CPU can perform read and write to memory efficiently. Hence, each data type in PostgreSQL has a specific alignment requirement. When multiple attributes are stored consecutively in a tuple, padding is inserted before an attribute so that it begins from the required aligned boundary. A better understanding of these alignment requirements may help minimizing the amount of padding required while storing a tuple on disk, thus saving disk space.
Data types in Postgres are divided into following categories:
  • Pass-by-value, fixed length: Data types that are passed by values to Postgres internal routines and have fixed lengths fall into this category.. The length can be 1, 2,  or 4 (or 8 on 64-bit systems) bytes.
  • Pass-by-reference, fixed length: For these data types, an address reference from the in-memory heap page is sent to internal Postgres routines. They also have fixed lengths.
  • Pass-by_reference, variable length: For variable length data types, Postgres prepends a varlena header before the actual data. It stores some information about how the data is actually stored on-disk (uncompressed, compressed or TOASTed) and the actual length of the data. For TOASTed attributes, the actual data is stored in a separate relation. In these cases, the varlena headers follow some information about the actual location of the data in their corresponding TOAST relation. Typically, on-disk size of a varlena header is 1-byte. But, if the data cannot be toasted and size of the uncompressed data crosses 126 bytes, it uses a 4-bytes header. For example, CREATE TABLE t1 (
    , a varchar
    );
    insert into t1 values(repeat('a',126));
    insert into t1 values(repeat('a',127));
    select pg_column_size(a) from t1;
    pg_column_size
    ---------------------
    127
    131 Besides, attributes having 4-bytes varlena header need to be aligned to a 4-bytes aligned memory location. It may waste upto 3-bytes of additional padding space. So, some careful length restrictions on such columns may save space.
  • Pass-by_referen
[...]
カテゴリー: postgresql

When I started writing PHP...

planet PHP - 2019-02-13(水) 10:46:00
When I started writing PHP...

I don't know exactly when I started writing PHP. It was shortly after the start of my second quarter of my freshman year of college, when a newly-met friend of mine introduced me to PHP as an easier to understand alternative to Perl. That puts it, I think, somewhere in January or February of 1999.

20 years ago, give or take a week. I have been writing PHP for two decades. That's more than half my lifetime. I feel old.

I thought it would be amusing (mostly at my expense) to look back a bit on just how much the PHP world has changed in the last two decades.

Larry 12 February 2019 - 7:46pm
カテゴリー: php

Craig Kerstiens: SQL: One of the most valuable skills

planet postgresql - 2019-02-13(水) 01:52:00

I’ve learned a lot of skills over the course of my career, but no technical skill more useful than SQL. SQL stands out to me as the most valuable skill for a few reasons:

  1. It is valuable across different roles and disciplines
  2. Learning it once doesn’t really require re-learning
  3. You seem like a superhero. You seem extra powerful when you know it because of the amount of people that aren’t fluent

Let me drill into each of these a bit further.

SQL a tool you can use everywhere

Regardless of what role you are in SQL will find a way to make your life easier. Today as a product manager it’s key for me to look at data, analyze how effective we’re being on the product front, and shape the product roadmap. If we just shipped a new feature, the data on whether someone has viewed that feature is likely somewhere sitting in a relational database. If I’m working on tracking key business metrics such as month over month growth, that is likely somewhere sitting in a relational database. At the other end of almost anything we do there is likely a system of record that speaks SQL. Knowing how to access it most natively saves me a significant amount of effort without having to go ask someone else the numbers.

But even before becoming a product manager I would use SQL to inform me about what was happening within systems. As an engineer it could often allow me to pull information I wanted faster than if I were to script it in say Ruby or Python. When things got slow in my webapp having an understanding of the SQL that was executed and ways to optimize it was indespensible. Yes, this was going a little beyond just a basic understanding of SQL… but adding an index to a query instead of rolling my own homegrown caching well that was well worth the extra time learning.

SQL is permanent

I recall roughly 20 years ago creating my first webpage. It was magical, and then I introduced some Javascript to make it even more impressive prompting users to click Yes/No or give me some input. Then about 10 years later jQuery came along and while

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

411 Length Required

planet PHP - 2019-02-13(水) 00:00:00

Most HTTP requests that have a request body, will also have a Content-Length header indicating how big the body will be. However, this is optional for some cases, such as when Chunked Transfer Coding is used.

It’s useful for a client to not include a Content-Length header for a few different cases. For instance, a client might send a HTTP request body based on a stream.

If a server does not support this feature, it can indicate this by sending back 411 Length Required.

In a situation like this, a recourse a client might have is to buffer the entire request to determine the real length.

Example HTTP/1.1 411 Length Required Content-Type: text/html Server: curveball/0.6.0 <h1>This server requires a Content-Length</h1> References
カテゴリー: php

Help! My tests stopped working.

planet PHP - 2019-02-12(火) 16:00:00
カテゴリー: php

Christophe Pettus: What’s up with SET TRANSACTION SNAPSHOT?

planet postgresql - 2019-02-12(火) 07:44:33

A feature of PostgreSQL that most people don’t even know exists is the ability to export and import transaction snapshots.

The documentation is accurate, but it doesn’t really describe why one might want to do such a thing.

First, what is a “snapshot”? You can think of a snapshot as the current set of committed tuples in the database, a consistent view of the database. When you start a transaction and set it to REPEATABLE READ mode, the snapshot remains consistent throughout the transaction, even if other sessions commit transactions. (In the default transaction mode, READ COMMITTED, each statement starts a new snapshot, so newly committed work could appear between statements within the transaction.)

However, each snapshot is local to a single transaction. But suppose you wanted to write a tool that connected to the database in multiple sessions, and did analysis or extraction? Since each session has its own transaction, and the transactions start asynchronously from each other, they could have different views of the database depending on what other transactions got committed. This might generate inconsistent or invalid results.

This isn’t theoretical: Suppose you are writing a tool like pg_dump, with a parallel dump facility. If different sessions got different views of the database, the resulting dump would be inconsistent, which would make it useless as a backup tool!

The good news is that we have the ability to “synchronize” various sessions so that they all use the same base snapshot.

First, a transaction opens and sets itself to REPEATABLE READ or SERIALIZABLE mode (there’s no point in doing exported snapshots in READ COMMITTED mode, since the snapshot will get replaced at the very next transaction). Then, that session calls pg_export_snapshot. This creates an identifier for the current transaction snapshot.

Then, the client running the first session passes that identifier to the clients that will be using it. You’ll need to do this via some non-database channel. For example, you can’t use LISTEN / NOTIFY,

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

Bruce Momjian: AT TIME ZONE Confusion

planet postgresql - 2019-02-12(火) 05:00:01

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it out. I then updated the Postgres documentation to explain it better, and here is what I found.

First, AT TIME ZONE has two capabilities. It allows time zones to be added to date/time values that lack them (timestamp without time zone, ::timestamp), and allows timestamp with time zone values (::timestamptz) to be shifted to non-local time zones and the time zone designation removed. In summary, it allows:

  1. timestamp without time zone &roarr timestamp with time zone (add time zone)
  2. timestamp with time zone &roarr timestamp without time zone (shift time zone)

It is kind of odd for AT TIME ZONE to be used for both purposes, but the SQL standard requires this.

Continue Reading »

カテゴリー: postgresql

ページ