• 5Notes - PGConfEU 24/10/17

• Date : October 24th, 2017
• Place : PG Conference Europe 2017 @ Warsaw Marriott Hotel in Warsaw, Poland
• RSVP : http://eepurl.com/cWELmn
• Registration : Closed
• Sponsor : PostgreSQL Europe
• Organizers : Damien Clochard, Leo Cossic and the PostgreSQL Europe Board

Agenda : The schedule bellow is temporary, chances are it'll slightly change.

1. PostgreSQL By 1996, it became clear that the name “Postgres95” would not stand the test of time. We chose a new name, PostgreSQL, to reflect the relationship between the original POSTGRES and the more recent ver-sions with SQL capability. At the same time, we.
2. 11 1.c builds 21 1 te website revews countrv Connected. Reviewer ratina ttle 5 Excellent and very usefu S utility 3 go ahead! 5 Fills a HUGE need Workg wall 5 great app I de s and a time 5 utility! 5 What it says on the can s worked ag degcrihed 5 Creat 5 und gut d 5 — Watch out 5 Worked as it sh Page I Of I PostgreSQL c 11 12 date 1 2011-02.
3. . Administer multiple servers. Support for PostgreSQL 7.0.x, 7.1.x, 7.2.x, 7.3.x, 7.4.x, 8.0.x, 8.1.x, 8.2.x, 8.3.x. Manage all aspects of: o Users & groups o Databases o Schemas o Tables, indexes, constraints, triggers, rules & privileges o Views, sequences & functions o Advanced objects o Reports. Easy data manipulation: o Browse tables.

09:00-09:30 - Welcoming participants and coffee/tea time

Postico will look familiar to anyone who has used a Mac before. Just connect to a. Postico 1.4.3 – A modern PostgreSQL client. November 10, 2018.

09:30-12:30 - Introduction - developers will have has 10 minutes to introduces there project, objectives, needs, etc

12:30-13:30 - Lunch break (sponsored by PGEU)

13:30-14:30 - Either keep on presenting developers and projects or Work in small groups of same interests

14:30-15:00 - Coffee break / Tea time

15:00-17:00 - Work in small groups

17:00 - Everyone is free to leave, the meeting's presumed to be over, but people can stay and keep on exchanging ideas and concerns or else.

19:00 - Dinner (optional and not sponsored)

• KEXI, with Jaroslaw Staniek
• Postico and Postgres.app, with Jakob Egger
• pgAdmin, with Dave Page
• DBeaver, with Serge Rider and Andrew Khitrin
• Joe Conway
• SQL Tabs, with Sasha Aliashkevich
• pg_view, patroni and bg_mon, with Oleksii Kliukin and Alexander Kukushkin
• pgBadger and pgFormatter, with Gilles Darold
• Postgres Professional Manager, with Ivan Panchenko and Oleg Bartunov
• temBoard and PoWA, with Pierre Giraud
• PostgreSQL Studio and pgDevOps, with Jim Mlodgenski
• pg_activity and temBoard, with Julien Tachoires

Organizers' topic suggestions:

• How can we organize ourselves to keep in touch with each other and take actions after the meeting?
• Additional PostgreSQL information: total RAM usage (from memory contexts), progress reporting
• Ease of enabling query monitoring (pg_stat_statements), plan monitoring
• Exchanging ideas on UI/UX Design
• Client based tools vs web based

Topic suggestions from developers (included in the registration form):

• Optimizing KEXI (MS Access-like tools) for PostgreSQL, joining forces with the PostgreSQL community
• Making PostgreSQL more accessible for newcomers.
• User centered design best practices, importance of user research, role of design for open source products.
• Working with advanced PostgreSQL features in UI tools.
• I am mainly interested in joining workshops.
• Additional PostgreSQL information: total RAM usage (from memory contexts), progress reporting
• Ease of enabling query monitoring (pg_stat_statements), plan monitoring.
• Talking about interoperability & Exchanging ideas on UI/UX Design.
• Developing comunity standards for management and monitoring tools and their interoperablity.
• More common APIs/Functions in core like pg_get_viewdef, Client based tools vs web based.

Hopes for this first meeting:

• Finding common goals
• consider sharing small subprojects
• building different user personas/profiles
• try to implement one tool's feature as a component in another PostgreSQL tool (OmniDB and SQL Tabs)

General questions:

• Should we develop a multi database interface or a PostgreSQL only interface ?

Damien Clochard : Introduction

Slides : https://wiki.postgresql.org/images/9/95/PGTDM_2017_%286%29.pdf

KDE : KEXI

'Continue where MS Access / Filemaker / IBM Lotus Approach stopped'

Slides : File:Kexi pgsql 2017.pdf

• In order to support multiple backends (SQLite, PostgreSQL, MySQL...) KEXI has own parser for its own SQL dialect. Support for nontrivial PostgreSQL's SQL is limited, can be extended by adding optional support for native features.
• Kexi Reports support previewing, printing, document generation, it's a fork of the xTuple OpenRPT engine.
• Mobile and web integrations, macros and scripting not yet released.
• Near plans: bring Mac and Windows versions back.
• Largely stable, used in production
• Issues: small development team, small number of testers, funding -> slow development of new features
• Ideas for sharing small subprojects: advanced CSV import/export, SQL parser for SQL editors, improved server instrumentation, more SQL (scalar) functions

Egger Apps : Postico / Postgres.app

Slides : Postico and Postgres.app: https://wiki.postgresql.org/images/3/3c/Postico-postgresapp-Egger.pdf

• URL handlers, trying to access and handle every instances by just by clicking a link

EDB : pgAdmin

Slides : https://wiki.postgresql.org/images/e/e0/PgAdmin_Project_Intro.pdf

• seritous issues often arise from 3rd party software
• very difficult to find people to work on packaging
• testing can be extremely difficult
• community issues: disagree with technology choices, prefer pgAdminIII to IV

Gilles Darold : pgBadger + pgFormatter

slides : pgBadger - pgFormatter: https://wiki.postgresql.org/images/4/42/Pgbadger_darold.pdf

DBWeaver : DBeaver

Slides : https://wiki.postgresql.org/images/a/a4/PG2017_-_UI_Tools_Last.pdf

• new hot features support
• important functionality depend on optional extension
• custom data types support
• JDBC issues (custom types, streaming, transaction)

Crunchy Data

• Prometheus + Graphana

2nd Quadrant : SQL Tabs

Slides : https://wiki.postgresql.org/images/1/17/Sqltabs_warsaw_2017_1_%281%29.pdf

• Idea : Common javascript UI Components (EXPLAIN display)
• Principle : psql is great !
• Principle : Rich client is the future
• Main User : PostgreSQL Expert

Zalando : pg_view / bg_mon

• Need : better memory stats
• Need : query progress informations

Postgres Pro : Postgres Professional Manager

Slides : Postgres pro Manager: https://wiki.postgresql.org/images/8/83/Postgrespro_ui-Panchenko.pdf

• Need : unified approaches to agent plugin interface (Common API)

dalibo : PoWA

Slides : PoWA: https://wiki.postgresql.org/images/2/22/PoWA_-_Giraud.pdf

• Dashboard like homepage
• Index suggestions

Open SCG : pgDevOps

Dalibo : Temboard

Postico 1 3 3 – A Modern Postgresql Client Centered Approach

Slides : temBoard: https://wiki.postgresql.org/images/3/32/Temboard-dev-grap-Tachoires.pdf

• Query ID

• Better error reporting from libpq ('libpq-side errors' could return a SQL State)

• Localization independant errors / Error Codes ?

• As much details as possible in errors (driver side ?)

• Python driver (psycopg) improvments : multiple result sets

• External parser ? -> https://github.com/jconway/pgsynck

• SQL Definitions for all objects (Tables, function headers) (Google Summer of Code)

• System triggers / Logging Triggers

• more info about 'waits' in the catalogs

• more info about memory consumption / memory context

• more scalar functions (GREATEST,MAX,) in an extension ?

• Progress report for QUERY / CREATE INDEX / etc. -> https://github.com/postgrespro/pg_query_state

• Lightning Talk @ PG Conference Europe 2017

• Communication :
  • yet another mailing list ? General GUI topics, nothing specific to a certain project + Announcements ?
  • dedicated wiki pages
  • psql-announce : https://wiki.postgresql.org/wiki/NewsEventsApproval

• Share Sample databases / Sample Data :
  • Xtuple
  • dell store
  • stack overflow for Text Search

Hopes for the next meeting ?

• PGCon 2018 ?
• Who wants to organize it? Dave Page + ?
• More structured presentations ? Small group work ?

The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing your PostgreSQL database cluster using initdb. It can be overridden when you create a database, so you can have multiple databases each with a different character set.

An important restriction, however, is that each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other libc-provided locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) If you have ICU support configured, ICU-provided locales can be used with most but not all server-side encodings.

Table 23.1 shows the character sets available for use in PostgreSQL.

Table 23.1. PostgreSQL Character Sets

Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10.

The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0-127 according to the ASCII standard, while byte values 128-255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.

initdb defines the default character set (encoding) for a PostgreSQL cluster. For example,

sets the default character set to EUC_JP (Extended Unix Code for Japanese). You can use --encoding instead of -E if you prefer longer option strings. If no -E or --encoding option is given, initdb attempts to determine the appropriate encoding to use based on the specified or default locale.

You can specify a non-default encoding at database creation time, provided that the encoding is compatible with the selected locale:

This will create a database named korean that uses the character set EUC_KR, and locale ko_KR. Another way to accomplish this is to use this SQL command:

Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data. For more information see Section 22.3.

The encoding for a database is stored in the system catalog pg_database. You can see it by using the psql-l option or the l command.

Important

Postico 1 3 3 – A Modern Postgresql Client Centered Role

On most modern operating systems, PostgreSQL can determine which character set is implied by the LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.

PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX. As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.

PostgreSQL supports automatic character set conversion between server and client for certain character set combinations. The conversion information is stored in the pg_conversion system catalog. PostgreSQL comes with some predefined conversions, as shown in Table 23.2. You can create a new conversion using the SQL command CREATE CONVERSION.

Table 23.2. Client/Server Character Set Conversions

To enable automatic character set conversion, you have to tell PostgreSQL the character set (encoding) you would like to use in the client. There are several ways to accomplish this:

Postico 1 3 3 – A Modern Postgresql Client Centered Server

• Using the encoding command in psql. encoding allows you to change client encoding on the fly. For example, to change the encoding to SJIS, type:

• libpq (Section 34.10) has functions to control the client encoding.

• Using SET client_encoding TO. Setting the client encoding can be done with this SQL command:

  Also you can use the standard SQL syntax SET NAMES for this purpose:

  To query the current client encoding:

  To return to the default encoding:

• Using PGCLIENTENCODING. If the environment variable PGCLIENTENCODING is defined in the client's environment, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

• Using the configuration variable client_encoding. If the client_encoding variable is set, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

If the conversion of a particular character is not possible — suppose you chose EUC_JP for the server and LATIN1 for the client, and some Japanese characters are returned that do not have a representation in LATIN1 — an error is reported.

If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.

These are good sources to start learning about various kinds of encoding systems.

Contains detailed explanations of EUC_JP, EUC_CN, EUC_KR, EUC_TW.

The web site of the Unicode Consortium.

UTF-8 (8-bit UCS/Unicode Transformation Format) is defined here.