フィードアグリゲーター

Shaun M. Thomas: PG Phriday: Around the World in Two Billion Transactions

planet postgresql - 2019-04-20(土) 02:00:21

Transaction IDs (XID) have been something of a thorn in Postgres’ side since the dawn of time. On one hand, they’re necessary to differentiate tuple visibility between past, present, and concurrent transactions. On the other hand, the counter that stores it is only 32-bits, meaning it’s possible to eventually overflow without some kind of intervention. […]

The post PG Phriday: Around the World in Two Billion Transactions appeared first on 2ndQuadrant | PostgreSQL.

カテゴリー: postgresql

Ernst-Georg Schmid: The Hare and the Hedgehog. Muscle, brain - or both?

planet postgresql - 2019-04-19(金) 07:22:00
In the famous fairy tale the hedgehog wins the race against the hare because he uses his brain to outwit the much faster hare: Brain beats muscle. But is that always the case? And what if we combine the two virtues?

The case at hand: Screening large sets of molecules for chemical simliarity.

Since (sub)graph isomorphism searching faces some mathematical challenges because of nonpolynomial O - even if you can use a specialized index, like pgchem::tigress does - fast similarity searching based on binary fingerprints has gained popularity in recent years.

I was tasked with evaluating a solution to the problem of similarity screening large sets of molecules with PostgreSQL where the fingerprints are generated externally, e.g. with the CDK.

This is, what I came up with...

Preparing the Racetrack
CREATE TABLE cdk.externalfp (
id int4 NOT NULL,
smiles varchar NOT NULL,
pubchemfp varbit NULL,
"cardinality" int4 NULL,
CONSTRAINT externalfp_pk PRIMARY KEY (id)
);

Above is the table definition of the final table. The cardinality column will be not used now, but since it is calculated by the fingerprint generator anyway, keeping it will save some work later. If you want to copy my example code 1:1, please use a database named chemistry and a schema named cdk.

First we need to load some data into the table. I used the free NCISMA99 dataset  from the National Cancer Institute, containing 249081 chemical structures in SMILES notation.

COPY cdk.externalfp (id, smiles) FROM '/tmp/NCISMA99' 
WITH (DELIMITER ' ', HEADER false, FORMAT csv);

And a few seconds later you should have 249081 rows in the table. Now we need to generate the fingerprints. The generator code is here, additionally you need the CDK 2.2 and a PostgreSQL JDBC driver. After changing the code to reflect your JDBC URL you are good to go.

Running the FingerprintGenerator should show no errors and takes about 30 Minutes on my Core i5 Linux Notebook. The fingerprint used is the PubChem fingerprint as described here.
Now we can put an index on the cardinality column[...]
カテゴリー: postgresql

Tomáš Votruba Blog: Pattern Refactoring

phpdeveloper.org - 2019-04-19(金) 04:00:02

In Removing Static - There and Back Again post we tried looked at anti-patterns in legacy code from a new point of view. It can be static in your code, it can be active record pattern you needed for fast bootstrapping of your idea, it can be moving from the code in controllers to command bus.

...

カテゴリー: php

Blog entries :: phly, boy, phly: From Zend Framework To The Laminas Project

phpdeveloper.org - 2019-04-19(金) 04:00:02

Ten years ago this month, I was involved in a couple of huge changes for Zend Framework. First, I helped spearhead integration of the JavaScript library Dojo Toolkit into Zend Framework, and finalized the work that month. I'd worked closely with the two developers who had been leading that proj...

カテゴリー: php

Technical Thoughts, Tutorials, and Musings: PSR-14: Example - plugin registration

phpdeveloper.org - 2019-04-19(金) 04:00:02

PSR-14: Example - plugin registration

In Content Management Systems and similar highly-configurable applications, a common pattern is to have a registration mechanism of some sort. That is, some part of the system asks other parts of the system "give me a list of your Things!", and t...
カテゴリー: php

Derick Rethans: PHP Internals News: Episode 6: PHP Quality Assurance

phpdeveloper.org - 2019-04-19(金) 04:00:02
PHP Internals News: Episode 6: PHP Quality Assurance London, UK Thursday, April 18th 2019, 09:06 BST In this sixth episode of "PHP Internals News" we talk to Remi Collet (Twitter, Website, GitHub, Donate) about the work that he does through...
カテゴリー: php

Voices of the ElePHPant: Interview with Andrew Caya

phpdeveloper.org - 2019-04-19(金) 04:00:01

@andrewscaya

Show Notes Linux for PHP PHP Continuous Learning Light MVC Framework

Audio

This episode is sponsored by

The post Interview with Andrew Caya appeared first on Voices of the ElePHPant.

カテゴリー: php

Pavel Stehule: new release of plpgsql_check - possibility to check SQL injection issue

planet postgresql - 2019-04-19(金) 03:22:00
Yesterday I released next version of plpgsql_check.

With this release a developer can check some well known patterns of SQL injection vulnerabilities. The code of stored procedures of native languages like PL/SQL, T-SQL or PL/pgSQL is secure, and there is not a risk of SQL injection until dynamic SQL is used (the EXECUTE command in PL/pgSQL). The safe programming requires sanitization of all string variables. Anybody can use functions: quote_literal, quote_ident or format. This check can be slow, so it should be enabled by setting security_warnings parameter:

CREATE OR REPLACE FUNCTION public.foo1(a text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE result text;
BEGIN
-- secure
EXECUTE 'SELECT $1' INTO result USING a;
-- secure
EXECUTE 'SELECT ' || quote_literal(a) INTO result;
-- secure
EXECUTE format('SELECT %L', a) INTO result;
-- unsecure
EXECUTE 'SELECT ''' || a || '''' INTO result;
-- unsecure
EXECUTE format(e'SELECT \'%s\'', a) INTO result;
RETURN result;
END;
$function$

postgres=# select * from plpgsql_check_function('foo1');
┌────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════╡
└────────────────────────┘
(0 rows)

postgres=# select * from plpgsql_check_function('foo1', security_warnings => true);
┌─────────────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞═════════════════════════════════════════════════════════════════════════════╡
│ security:00000:11:EXECUTE:text type variable is not sanitized │
│ Query: SELECT 'SELECT ''' || a || '''' │
│ -- ^ │
│ Detail: The EXECUTE expression is SQL injection vulnerable. │
│ Hint: Use quote_ident, quote_literal or format function to secure variable. │
│ security:00000:13:EXECUTE:text type variable is not sanitized │
│ Query: SELECT format(e'SELECT \'%s\'', a) [...]
カテゴリー: postgresql

Julien Rouhaud: New in pg12: Statistics on checkums errors

planet postgresql - 2019-04-18(木) 20:02:26
Data checksums

Added in PostgreSQL 9.3, data checksums can help to detect data corruption happening on the storage side.

Checksums are only enabled if the instance was setup using initdb --data-checksums (which isn’t the default behavior), or if activated afterwards with the new pg_checksums tool also added in PostgreSQL 12.

When enabled, checksums are written each time a block is written to disk, and verified each time a block is read from disk (or from the operating system cache). If the checksum verification fails, an error is reported in the logs. If the block was read by a backend, the query will obviously fails, but if the block was read by a BASE_BACKUP operation (such as pg_basebackup), the command will continue its processing . While data checkums will only catch a subset of possible problems, they still have some values, especially if you don’t trust your storage reliability.

Up to PostgreSQL 11, any checksum validation error could only be found by looking into the logs, which clearly isn’t convenient if you want to monitor such error.

New counters available in pg_stat_database

To make checksum errors easier to monitor, and help users to react as soon as such a problem occurs, PostgreSQL 12 adds new counters in the pg_stat_database view:

commit 6b9e875f7286d8535bff7955e5aa3602e188e436 Author: Magnus Hagander <magnus@hagander.net> Date: Sat Mar 9 10:45:17 2019 -0800 Track block level checksum failures in pg_stat_database This adds a column that counts how many checksum failures have occurred on files belonging to a specific database. Both checksum failures during normal backend processing and those created when a base backup detects a checksum failure are counted. Author: Magnus Hagander Reviewed by: Julien Rouhaud

 

commit 77bd49adba4711b4497e7e39a5ec3a9812cbd52a Author: Magnus Hagander <magnus@hagander.net> Date: Fri Apr 12 14:04:50 2019 +0200 Show shared object statistics in pg_stat_database This adds a row to the pg_stat_database view with datoid 0 and datname NULL fo[...]
カテゴリー: postgresql

Interview with Andrew Caya

planet PHP - 2019-04-18(木) 20:00:00

@andrewscaya Show Notes

Audio

This episode is sponsored by


The post Interview with Andrew Caya appeared first on Voices of the ElePHPant.

カテゴリー: php

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Log all statements from a sample of transactions

planet postgresql - 2019-04-18(木) 17:01:31
On 3rd of April 2019, Alvaro Herrera committed patch: Log all statements from a sample of transactions   This is useful to obtain a view of the different transaction types in an application, regardless of the durations of the statements each runs.   Author: Adrien Nayrat Commit message makes it pretty clear, so let's see … Continue reading "Waiting for PostgreSQL 12 – Log all statements from a sample of transactions"
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Report progress of CREATE INDEX operations

planet postgresql - 2019-04-18(木) 10:15:52
On 2nd of April 2019, Alvaro Herrera committed patch: Report progress of CREATE INDEX operations     This uses the progress reporting infrastructure added by , adding support for CREATE INDEX and CREATE INDEX CONCURRENTLY.   There are two pieces to this: one is index-AM-agnostic, and the other is AM-specific. The latter is fairly elaborate … Continue reading "Waiting for PostgreSQL 12 – Report progress of CREATE INDEX operations"
カテゴリー: postgresql

PSR-14: Example - plugin registration

planet PHP - 2019-04-18(木) 08:08:00
PSR-14: Example - plugin registration

In Content Management Systems and similar highly-configurable applications, a common pattern is to have a registration mechanism of some sort. That is, some part of the system asks other parts of the system "give me a list of your Things!", and then modules/extensions/plugins (whatever the system calls them) can incrementally build up that list of Things, which the caller then does something with. Those Things can be defined by the extension, or they can be defined by user-configuration and turned into a Thing definition by the module. Both are valid and useful, and can be mixed and matched.

This pattern lends itself very well to an Event system like PSR-14, and in fact the "give me a list of Things" pattern was one of the explicit use cases the Working Group considered. Today let's look at how one could easily implement such a mechanism.

Continue reading this post on SteemIt.

Larry 17 April 2019 - 6:08pm
カテゴリー: php

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Generated columns

planet postgresql - 2019-04-18(木) 04:16:03
On 30th of March 2019, Peter Eisentraut committed patch: Generated columns   This is an SQL-standard feature that allows creating columns that are computed from expressions rather than assigned, similar to a view or materialized view but on a column basis.   This implements one kind of generated column: stored (computed on write). Another kind, … Continue reading "Waiting for PostgreSQL 12 – Generated columns"
カテゴリー: postgresql

From Zend Framework To The Laminas Project

planet PHP - 2019-04-18(木) 01:58:00

Ten years ago this month, I was involved in a couple of huge changes for Zend Framework.

First, I helped spearhead integration of the JavaScript library Dojo Toolkit into Zend Framework, and finalized the work that month. I'd worked closely with the two developers who had been leading that project at the time, and one thing that came up during our discussions was that they had helped create an open source foundation for the project, to ensure its continuity and longevity, and to ensure the project can outlive the ups and downs of any commercial company. This idea intrigued me, and has stuck in the back of my mind ever since.

The other thing that happened that month was that I was promoted to Project Lead of Zend Framework. I've held that position ever since.

Today, I get to announce another change: Zend Framework is transitioning to an open source project under the Linux Foundation!

How Zend Framework Becomes Laminas

As I noted, I've been thinking about this for 10 years now, and actually doing research and trying to figure out a way to make it happen for almost two years. When my employer announced some restructuring of the Zend portfolio last fall, moving the project to a foundation was foremost on my mind.

So, imagine my surprise when an old PHP friend, John Mertic, reached out to me and offered the assistance of the Linux Foundation!

I had no idea that this was even a possibility. But, as it turns out, the mission of the foundation is to help create sustainable open source communities, and the primary way they do that is to help create foundations for projects. The beauty is that they take care of the business stuff that developers like myself don't have expertise in: legal issues, taxes, bookkeeping, and even help with things like marketing. They do this so that those of us working on open source projects can focus on our communities and our code. It's an absolutely perfect scenario for the project.

Over the last few months, I've worked with Rogue Wave (my employer) and the Linux Foundation to work out the logistics of this transition, including coming up with some initial budgets, helping flesh out a governance model, and identifying potential founding members. I've also worked with the Zend Framework community review team to come up with a name for the project, and work out some of the technical details for migrating both the project and its users.

So, please say a warm hello to the Laminas Project.

What we announced today is just a beginning. The project is not yet operational. We're still working on tooling for migrating the project and its users, and, more importantly, recruiting more founding members. If your company is interested, please fill out our form, and we'll get back to you to discuss the details.

Acknowledgments

As a parting note, I need to acknowledge a number of people who helped me through the last few months:

  • My wife, Jen, who has been my chief sounding board, and helped me keep my sanity while I juggle meetings, emails, and growing task lists. Love you!
  • Enrico Zimuel, who has been my co-worker, confidante, and friend for years, and continued to help even when he left Rogue Wave last month. I'm excited for this new chapter!
  • The various folks in the Zend Professional Services team, for letting me bounce ideas off of them and occasionally voice my frustrations. You all know who you are!
  • The entire Zend Framework community review team: Rob, Gary, Marco, Frank, James, Evan, Adam, Aleksei, Andreas, Ben, Geert, Ryan, Michał, Michael, and Mike (yes, those last three are all different people!). In particular, Michał has been putting in crazy hours working on migration tooling, and Frank just this morning sent over changes for the Laminas website for me to incorporate!
  • John Mertic and Michael Dolan of the Linux Foundation for holding my hand through this entire process and helping make it happen. We still have work to do, but even getting this far feels like a huge accomplishment, and I couldn't have done it without your support.

If all goes to plan, I'm hoping we'll be announcing the project is operational in the next few months; keep an eye on the ZF and Laminas websites and twitter handles for updates!

From Zend Framework To The Laminas Project was originally published 17 April 2019 on https://mwop.net by .
カテゴリー: php

The Zend Blog: Zend Framework transitions to the Linux Foundation

phpdeveloper.org - 2019-04-18(木) 00:30:02

Since its inception, Zend Technologies, and later Rogue Wave Software, has been single-handedly leading and sponsoring the Zend Framework project. Over the years, Zend Framework has seen wide adoption across the PHP ecosystem, with an emphasis on the Enterprise market. It has formed the basis of num...

カテゴリー: php

JoliCode blog - les derniers articles: Find Segfaults in PHP like a boss

phpdeveloper.org - 2019-04-18(木) 00:30:02

A bit of history

Sometimes, a segfault happens, but you don't know where, and your PHP installation does not have tools to find it. Or sometime, you think PHP is hanging, but you don't know where. You may use xdebug, but you don't want to click so many times on the "next call" button.

To ad...

カテゴリー: php

Toptal Engineering Blog: Laravel Zero Downtime Deployment

phpdeveloper.org - 2019-04-18(木) 00:30:02

Updating live applications used to be a tedious and somewhat risky affair, but with the emergence of modern development frameworks and tools, updates and deployment are becoming straightforward.

In this article, Toptal Java Developer Elene Latsoshvili explains how you can update a Laravel app wit...

カテゴリー: php

ページ