PostgreSQL wiki - User contributions [en]

PgCon 2013 Developer Meeting

2013-05-01T09:14:49Z

Dim: /* Proposed Agenda Items */

A meeting of the most active PostgreSQL developers is being planned for Wednesday 22nd May, 2013 near the University of Ottawa, prior to pgCon 2013. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org).

Please note that this year the attendee numbers have been kept low in order to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.3 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.

This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.

== Time & Location ==

The meeting will be from 8:30AM to 5PM, and will be in the "Red Experience" room at:

Novotel Ottawa
33 Nicholas Street
Ottawa
Ontario
K1N 9M7

Food and drink will be provided throughout the day, including breakfast from 8AM.

[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]

== Attendees ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Josh Berkus (secretary)
* Jeff Davis
* Andrew Dunstan
* Peter Eisentraut
* Dimitri Fontaine
* Andres Freund
* Stephen Frost
* Peter Geoghegan
* Kevin Grittner
* Robert Haas
* Magnus Hagander
* Alexander Korotkov
* Fujii Masao
* Noah Misch
* Bruce Momjian
* Tom Lane
* Dave Page (chair)
* Simon Riggs
* KaiGai Kohei

== Proposed Agenda Items ==

Please list proposed agenda items here:

* 9.4 Commitfest schedule
* [http://wiki.postgresql.org/wiki/Parallel_Query_Execution Parallel Query Execution] (Bruce, Noah)
* logical changeset generation review & integration (Andres)
* utilization of upcoming non-volatile RAM device (Kaigai)
* pluggable plan/exec nodes (Kaigai)
** to offload targetlist calculation, sorting, aggregates, ...
* [[GIN generalization]] (Alexander)
* An Extensibility Roadmap (dim)

FOSDEM 2013

2013-02-04T09:56:27Z

Dim: dim's talks at FOSDEM

==Schedule==

Schedule is available at the FOSDEM website: https://fosdem.org/2013/schedule/track/postgresql/

== PGDay FOSDEM 2013 ==

We are also organizing one-day event: https://wiki.postgresql.org/wiki/PGDay_FOSDEM_2013

==Presentations==

Slides of the presentations:

== Talks: Friday, February 1st, 2013 ==

=== Radisson Blu ===

* Welcome (Magnus Hagander)
* Sqitch: VCS-enabled database change management (Ronan Dunklau)
* Postgresql 9.2 FTS Solutions (Emanuel Calvo)
* Pagination done the PostgreSQL way (Markus Winand) [[File:Pagination_Done_the_PostgreSQL_Way.pdf]]
* Announcements FOSDEM PGDay 2013 (Dave Page, Jean-Paul Argudo)
* 3D an exact geometries for PostGIS (Hugo Mercier)
* Understanding PostgreSQL timelines (Heikki Linnakangas)
* Maintaining Very Large Databases (VLDs) (Devrim GÜNDÜZ)

== Talks: Sunday, February 3rd, 2013 ==

=== PostgreSQL FOSDEM Devroom ===

* openbarter, a possible solution for ecological regulation (Olivier Chaussavoine)
* [http://tapoueh.org/images/confs/Fosdem2013_Event_Triggers.pdf Event Triggers] (Dimitri Fontaine)
* PostGIS 2.0 and beyond (Vincent Picavet)
* Making apt.postgresql.org a reality (Christoph Berg)
* Postgres Demystified (Craig Kerstiens)
* PostgreSQL as a Schemaless Database (Christophe Pettus)
* [http://tapoueh.org/images/confs/Fosdem2013_High_Availability.pdf Implementing High Availability] (Dimitri Fontaine)
* Practical Tips for Better PostgreSQL Applications (Marc Balmer)

Speakers: please upload your slides to the wiki and add a link above.

[[Category:PostgreSQL Events]]

Event Triggers

2013-01-30T17:06:24Z

Dim: /* DROP CASCADE support */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Use Cases ===

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions
* Impact analysis/performance

==== Logical Replication ====

We want to record a DDL changes in a way that allows it to be replayed elsewhere in a flexible way. So we need access to both the original text and the fully qualified text once all search paths are resolved, making both available to allow replication to decide which to use. We don't need to fire actions for every conceivable sub-statement, only enough that we can accurately reproduce what occurred. So for example, a DROP CASCADE might just need to be replayed as a DROP CASCADE. Query text is available from utility hooks, but fully cooked SQL event info is required.

==== Auditing ====

We need to be able to record Who did What to Whom/WhichObject, When they did it and whether it succeeded. So we need access to userid, username, actioncategory, actiontext, subjectcategory, subjectid, action time, rc

==== Performance ====

How long did we hold locks for? How long did the action take?

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that release.

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Features still in the work for 9.3 ===

The main features we need and which are not yet commited in 9.3 are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

The next point is detailing where the discussion is at about those features that we didn't commit yet.

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE / DROP OWNED support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T17:02:33Z

Dim:

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Use Cases ===

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions
* Impact analysis/performance

==== Logical Replication ====

We want to record a DDL changes in a way that allows it to be replayed elsewhere in a flexible way. So we need access to both the original text and the fully qualified text once all search paths are resolved, making both available to allow replication to decide which to use. We don't need to fire actions for every conceivable sub-statement, only enough that we can accurately reproduce what occurred. So for example, a DROP CASCADE might just need to be replayed as a DROP CASCADE. Query text is available from utility hooks, but fully cooked SQL event info is required.

==== Auditing ====

We need to be able to record Who did What to Whom/WhichObject, When they did it and whether it succeeded. So we need access to userid, username, actioncategory, actiontext, subjectcategory, subjectid, action time, rc

==== Performance ====

How long did we hold locks for? How long did the action take?

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that release.

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Features still in the work for 9.3 ===

The main features we need and which are not yet commited in 9.3 are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

The next point is detailing where the discussion is at about those features that we didn't commit yet.

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:35:32Z

Dim: /* Missing features for 9.3 */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main features we need and which are not yet commited in 9.3 are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

The next point is detailing where the discussion is at about those features that we didn't commit yet.

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:29:36Z

Dim: /* Distributed Sequences */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:25:41Z

Dim: /* Distributed Sequences */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

==== Distributed Sequences ====

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:25:07Z

Dim: /* Command String Normalisation */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

See
* http://www.postgresql.org/message-id/CAFNqd5VuowUqXrHfWf_Ld-_szCUxaN3=RZD=XiVmNr_Yd=53QQ@mail.gmail.com
* http://www.postgresql.org/message-id/51055F11.6040208@ca.afilias.info

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:23:40Z

Dim: /* How to expose Information to Event Triggers Functions */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

==== How to expose Information to Event Triggers Functions ====

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:22:38Z

Dim: /* Command String Normalisation */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

==== Command String Normalisation ====

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:22:24Z

Dim: /* DROP CASCADE support */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

==== DROP CASCADE support ====

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:19:53Z

Dim: /* How to expose Information to Event Triggers Functions */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:19:24Z

Dim: /* How to expose Information to Event Triggers Functions */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to expose some magic PL variables. The already commited code exposes ''TG_EVENT'' and ''TG_TAG''.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:13:36Z

Dim: /* Discussion */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement them.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T16:13:01Z

Dim: /* = Missing features for 9.3 */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

=== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T15:56:21Z

Dim: /* Expected Features */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

==== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T15:55:30Z

Dim: /* Main advantages over ProcessUtility hooks */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

==== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T15:53:57Z

Dim: /* Event Triggers */

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disable, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

==== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Event Triggers

2013-01-30T15:47:45Z

Dim: First Version.

== Event Triggers ==

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support

=== Main advantages over ProcessUtility hooks ===

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

* You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;

* You can actually list the currently installed Event Triggers by doing \dy in psql;

* In ''single user'' mode the Event Triggers are disable, staying out of the way while you're busy fixing your production services;

* It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the ''shared object librairies'' are going to be ''_Init()''ialized.

=== Expected Features ===

The first release of Event Triggers in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

==== Use Cases ====

We attempt at being able to solve those use cases:

* Logical Replication
* Auditing
* DDL support for Extensions

==== Features ====

So we want to provide those features:

* Event Triggers that run either before or after a DDL command
* User Functions should be provided with detailed information:
** event name
** command tag
** operation (CREATE, ALTER or DROP)
** object kind (TABLE, FUNCTION, VIEW, etc)
** object OID
** object name
** schema name where the object lives
**
* support for DROP CASCADE
* support for dropping multiple objects
* support for ''generated'' commands

=== Commited patches from the series ===

Here's the list of what we already have:

* Syntax support and documentation for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3855968f328918b6cd1401dd11d109d471a54d40
** that was the first step, catalogs and grammar
* Make new event trigger facility actually do something.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc
** and now you can actually have your function called
** it will only know about ''tg_event'' and ''tg_tag''
** and you can only write it in PLpgSQL
* Adjust many backend functions to return OID rather than void.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c504513f83a9ee8dce4a719746ca73102cae9f13
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=82b1b213cad3a69cf5f3dfaa81687c14366960fc
** that's a preparing step so that we can expose the object OID in the event trigger
** not yet exposed though
* Add ddl_command_end support for event triggers.
** http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=841a5150c575ccd89e4b03aec66eeeefb21f3cbe
** so that you can call your event trigger when the object has already been ''CREATE''d

==== Missing features for 9.3 ===

The main missing features are:

* No specific information about the objects that the command target
* No access to the parse tree or the command string, raw or normalized
* No support for multiple targets per command
** only DROP can target more than one object at a time
** and DROP CASCADE is another special case of that
* No support for ''generated'' commands

=== Discussion ===

Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding ''new'' features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

==== ''generated'' and ''internal'' commands ====

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have ''CREATE SEQUENCE'' then a new ''CREATE TABLE'' statement using the name of the sequence we just created, then ''ALTER SEQUENCE'' and finally a ''CREATE INDEX'' command. The current implementation of PostgreSQL backend is to form a ''parse node'' from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a ''CONTEXT'' of ''GENERATED'' that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a ''hook''. The problem with the ''hook'' approach is that you need to build and expose the exact same amount of information.

=== DROP CASCADE support ===

A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

=== How to expose Information to Event Triggers Functions ===

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

* Variables
** TG_OBJECTID
** TG_OBJECTNAME
** TG_SCHEMANAME
** TG_OPERATION
** TG_OBTYPENAME
** TG_COMMAND
** TG_CONTEXT

* Accessors
** pg_get_event_command_string()

About ''TG_CONTEXT'', see previous point.

=== Command String Normalisation ===

The main use case for the ''command string'' is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

=== Distributed Sequences ===

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a ''CREATE SEQUENCE'' will in fact create a ''distributed'' sequence, in certain cases determined by the logical replication system (not known in the backend).

=== Features For Next Releases ===

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.

==== INSTEAD OF ====

The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
execute procedure my_create_extension();

create function my_create_extension()
returns event_trigger
language plpgsql
as $$
begin
alter event trigger my_create_extension disable;
-- do some stuff here
create extension tg_objectid;
-- do some more stuff here, presumably
end;
$$;

Unfortunately it's already clear that the ''INSTEAD OF'' feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.

==== table_rewrite ====

The idea is to provide an event called ''table_rewrite'' that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

==== create table on insert ====

Some developers are getting used to ''schema less'' databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table ''look_me_i_dont_exist'' is automatically created by PostgreSQL with two columns ''key'' of type ''integer'' (presumably) and ''value'' of type ''text'' here.

[[Category:PostgreSQL 9.3]]

Development projects

2013-01-30T14:52:01Z

Dim: /* Active Projects */

__NOTOC__
If you are working on a PostgreSQL project and collaborating using the wiki, please add a link to your page(s) below.

== Active Projects ==
* [[Hot Standby]] and [[Hot Standby TODO|TODO]] items - 9.0
* [[Streaming Replication]] - async is done in 9,0 / sync is under development
* [[Extensions]]
* [[Performances QA testing]]
* [[Temporal Extensions]]
* [[In-place upgrade]]
* [[Table partitioning]]
* [[JDBC]]
* [[SEPostgreSQL|Security-Enhanced PostgreSQL]]
* [[Clustering Development Projects]]
* [[Serializable]]
* [[SQL/MED]]
* [[Event Triggers]] - a patch series for 9.3

== Developer project trackers ==
* [[Simon Riggs' Development Projects]]
* [[Greg Stark's Development Projects]]
* [[User:Petere|Peter Eisentraut's Page]]
* [[Aster's Development Projects]]
* [[Robert Haas' Development Projects]]

== General feature wishlists ==
* [[DataWarehousing]]
* [[Statistics the planner doesn't have that it really needs]]
* [[:Image:Partitioning Requirements.pdf | Partitioning Requirements]]
* [[Logging Brainstorm]]
* [[Refactor Type System]]

== Pending Projects ==
* [[Updatable views]]
* [[GUCS Overhaul]]
* [[FreeSpaceMapRewrite]]
* [[C++ Compatibility]]

== Completed Projects ==
* [[64bit Windows port]] - 9.0
* [[DefaultACL|Default ACL Properties for DB Objects]] - 9.0
* [[GSoC_2008|Google Summer of Code 2008]]
* [[XML Support]] ([[XML Todo]]) - 8.3
* [[Enumerated Types]] - 8.3
* [[Wishlist For 8.3]]
* [[Gborg migration]]
* [[TrackerDiscussion| Tracker Discussion]]

[[Category:Development|!]]

Event Triggers

2013-01-30T14:51:29Z

Dim: Created page with "Category:PostgreSQL 9.3"

[[Category:PostgreSQL 9.3]]

Development projects

2013-01-30T14:50:43Z

Dim: /* Active Projects */

__NOTOC__
If you are working on a PostgreSQL project and collaborating using the wiki, please add a link to your page(s) below.

== Active Projects ==
* [[Hot Standby]] and [[Hot Standby TODO|TODO]] items - 9.0
* [[Streaming Replication]] - async is done in 9,0 / sync is under development
* [[Extensions]]
* [[Performances QA testing]]
* [[Temporal Extensions]]
* [[In-place upgrade]]
* [[Table partitioning]]
* [[JDBC]]
* [[SEPostgreSQL|Security-Enhanced PostgreSQL]]
* [[Clustering Development Projects]]
* [[Serializable]]
* [[SQL/MED]]
* [[Event Triggers]]

== Developer project trackers ==
* [[Simon Riggs' Development Projects]]
* [[Greg Stark's Development Projects]]
* [[User:Petere|Peter Eisentraut's Page]]
* [[Aster's Development Projects]]
* [[Robert Haas' Development Projects]]

== General feature wishlists ==
* [[DataWarehousing]]
* [[Statistics the planner doesn't have that it really needs]]
* [[:Image:Partitioning Requirements.pdf | Partitioning Requirements]]
* [[Logging Brainstorm]]
* [[Refactor Type System]]

== Pending Projects ==
* [[Updatable views]]
* [[GUCS Overhaul]]
* [[FreeSpaceMapRewrite]]
* [[C++ Compatibility]]

== Completed Projects ==
* [[64bit Windows port]] - 9.0
* [[DefaultACL|Default ACL Properties for DB Objects]] - 9.0
* [[GSoC_2008|Google Summer of Code 2008]]
* [[XML Support]] ([[XML Todo]]) - 8.3
* [[Enumerated Types]] - 8.3
* [[Wishlist For 8.3]]
* [[Gborg migration]]
* [[TrackerDiscussion| Tracker Discussion]]

[[Category:Development|!]]

PGDay FOSDEM 2013

2013-01-07T08:12:13Z

Dim: /* Dinner */

= PGDay FOSDEM 2013 =

This is the first PgDay we hold in Belgium. FOSDEM PGDay 2013 will be held on Feb 1st in Brussels, Belgium, at the Radisson Blu Royal hotel. As an extension to the regular PostgreSQL devroom at FOSDEM, it will cover topics for PostgreSQL users, developers and contributors, and anybody else interested in PostgreSQL

== Details ==

* '''Date:''' Feb 01st, 2013 9am-5pm
* '''Venue:''': Radisson Blu Royal Hotel
* '''Coordinator:''': PostgreSQL Europe [mailto:contact@pgconf.eu contact@pgconf.eu]
* '''Website:''': http://fosdem2013.pgconf.eu/

== Registration ==

Free attendance, web registration required: http://fosdem2013.pgconf.eu/registration/ (limited seats)

== Schedule ==

Schedule will be published online: http://fosdem2013.pgconf.eu/registration/

== Location and Venue ==

http://fosdem2013.pgconf.eu/venue/

Address:

http://www.radissonblu.com/royalhotel-brussels/location

== Dinner ==

We are organizing a dinner after the event on Friday 1st, 2013 at Hard Rock Cafe Brussels. We have limited (30) number of seats, so please add your name to this list before going there.

Attendees:

* Devrim Gündüz (x2)
* Magnus Hagander
* Andreas Scherbaum (+1.5)
* Jean-Paul Argudo
* Patryk Kordylewski (x2)
* Dimitri Fontaine

[[Category:PostgreSQL Events]]

PostgreSQL Conference Europe Talks 2012

2012-10-30T14:09:41Z

Dim: /* Bellevue (Lightning Talks) */

= PostgreSQL Conference Europe 2012 Talks =

== Trainings: Tuesday 23th October, 2012 ==

=== Clyde ===

* Mastering PostgreSQL Administration (Bruce Momjian, Devrim GÜNDÜZ)

=== Seine ===

* PostgreSQL Performance Training (Greg Smith, Peter Geoghegan)
* PostgreSQL Replication Training (Dimitri Fontaine, Simon Riggs)

=== Thames ===

* Implementace uložených procedur v PostgreSQL (Pavel Stehule)
* Čtení exekučních plánů (Tomas Vondra)

=== Vltava ===

* A day of SQL with Celko (Joe Celko)

== Talks: Wednesday 24th October, 2012 ==

=== Bellevue ===

* Opening keynote (Joe Celko)

=== Seine ===

* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions (Bruce Momjian)]
* PostgreSQL on AWS (Christophe Pettus)
* Understanding EXPLAIN's output (Guillaume Lelarge)
* [http://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf Boosting Performance and Reliability by using pgpool-II (Tatsuo Ishii)]
* CREATE EXTENSION pgchess; (Gianni Ciolli)

=== Thames ===

* [[Media:Pg-fdw.pdf|Writing a foreign data wrapper (Bernd Helmle)]]
* [http://anarazel.de/2ndquadrant/pgconf-eu-2012-10-24 MultiMaster Replication: Applications, Comparison, Implementation ] (Andres Freund, Simon Riggs)
* [[Media:Pgconfeu12-collectd%2Bpsql.pdf|Watch your Elephants -- Using collectd for PostgreSQL performance analysis]] ([http://tokkee.org/ Sebastian 'tokkee' Harl])
* [[Media:Range-types.pdf|Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same (Jonathan S. Katz)]]
* High availability in Postgres-XC, the symmetric PostgreSQL cluster (Koichi Suzuki)

=== Vltava ===

* Provoz PostgreSQL na AWS (Tomas Vondra)
* [[Media:Plpgsql internals.pdf| PL/pgSQL internals -- some details from PL/pgSQL environment]]
* Migrace z MySQL na PostgreSQL (Tomas Vondra)
* [[Media:Indexy.pdf| Indexy jsou grunt -- basic and enhanced using of indexes in PostgreSQL]]
* Load dat do PostgreSQL (Jan Holčapek)

== Talks: Thursday 25th October, 2012 ==

=== Bellevue (Lightning Talks) ===

* [[Media:Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf| Full-text search in PostgreSQL in milliseconds (Oleg Bartunov, Alexander Korotkov)]]
* [[Media:Pgconfeu-2012-docbot-print.pdf|#PostgreSQL pg_docbot (Andreas 'ads' Scherbaum)]]
* [http://tapoueh.org/images/pgq-coop.pdf PGQ Cooperative Consumers] (Dimitri Fontaine & Marko Kreen)
* [http://www.pgexperts.com/document.html?id=60 PostgreSQL Drinking Game] (Josh Berkus)

=== Seine ===

* How fast is PostgreSQL? (Cédric Villemain)
* [http://tapoueh.org/images/high-availability.pdf Implementing High Availability] (Dimitri Fontaine)
* [http://momjian.us/main/presentations/internals.html#shared_memory Inside PostgreSQL Shared Memory (Bruce Momjian)]
* [https://plv8-pgconfeu12.herokuapp.com Embracing the Web with JSON and PLV8] ([http://bitfission.com Will Leinweber])

=== Thames ===

* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 Topology and network analysis with PostgreSQL and PostGIS (Vincent Picavet)]
* [[Media:Universal_Data_Access_with_SQL_MED.pdf|Universal Data Access with SQL/MED (David Fetter)]]
* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 PostGIS 2.0 and beyond (Vincent Picavet)]
* Practical Tips for Better PostgreSQL Applications (Marc Balmer)
* Pacemaker and PostgreSQL: to serve and protect your data (Jehan-Guillaume (ioguix) de Rorthais)

=== Vltava ===

* [[Media: Marketing-postgres.pdf|Marketing PostgreSQL (Jonathan S. Katz)]]
* [[Media: Pgconf2012_sprocwrapper.pdf‎|Java Stored Procedure Wrapper and PGObserver (Jan Mussler)]]
* [http://www.pgexperts.com/document.html?id=59 Elephants and Windmills] (Josh Berkus)
* [[Media: Pgeu2012.pdf|PostgreSQL in Research and Development: Three success stories. (Roland Sonnenschein)]]
* [[Media: Index_support_for_regular_expression_search.pdf‎ |Index support for regular expression search (Alexander Korotkov)]]

== Talks: Friday 26th October, 2012 ==

=== Bellevue ===

* Postgres Adoption at the Tipping Point: Users Around the World and Their Deployment Profile (Ed Boyajian)
* Community PostgreSQL (Simon Riggs, Harald Armin Massa)
* Closing (Dave Page)

=== Seine ===

* Beyond Query Logging (Greg Smith, Peter Geoghegan)
* [http://www.hagander.net/talks/Backup_strategies_pgeu.pdf PostgreSQL Backup Strategies] (Magnus Hagander)
* [http://www.gunduz.org/download.php?dlid=196 Maintaining Very Large Databases (VLDs)] (Devrim GÜNDÜZ)
* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL] (Dimitri Fontaine)

=== Thames ===

* [[Media: Postbis_pgcon_eu_2012.pdf|PostBIS - A Bioinformatics Booster for PostgreSQL (Michael Schneider)]]
* Migrating Oracle queries to PostgreSQL (Alexey Klyukin)
* Debugging complex SQL queries with writable CTEs (Gianni Ciolli)
* [http://www.cybertec.at/download/2012_prag_linux_v5.pdf Limiting PostgreSQL resource consumption using the Linux kernel (Hans-Jürgen Schönig)]

=== Vltava ===

* [[Media: Pg_xnode_pgconf_2012.pdf|pg_xnode extension (Antonin Houska)]]
[[Category:PostgreSQL Europe]]
* PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case (Julien Rouhaud)
* [[Media: PGconEU2012-KaiGai-PGStrom.pdf|PG-Strom - GPU Accelerated Asynchronous Query Execution Module (KaiGai Kohei)]]
[[Category:PostgreSQL Europe]]
* [http://tokkee.org/talks/pgconfeu12-time-series-data.pdf Using PostgreSQL for storing time-series data] ([http://tokkee.org/ Sebastian 'tokkee' Harl])

PostgreSQL Conference Europe Talks 2012

2012-10-30T14:09:15Z

Dim: /* Bellevue (Lightning Talks) */

= PostgreSQL Conference Europe 2012 Talks =

== Trainings: Tuesday 23th October, 2012 ==

=== Clyde ===

* Mastering PostgreSQL Administration (Bruce Momjian, Devrim GÜNDÜZ)

=== Seine ===

* PostgreSQL Performance Training (Greg Smith, Peter Geoghegan)
* PostgreSQL Replication Training (Dimitri Fontaine, Simon Riggs)

=== Thames ===

* Implementace uložených procedur v PostgreSQL (Pavel Stehule)
* Čtení exekučních plánů (Tomas Vondra)

=== Vltava ===

* A day of SQL with Celko (Joe Celko)

== Talks: Wednesday 24th October, 2012 ==

=== Bellevue ===

* Opening keynote (Joe Celko)

=== Seine ===

* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions (Bruce Momjian)]
* PostgreSQL on AWS (Christophe Pettus)
* Understanding EXPLAIN's output (Guillaume Lelarge)
* [http://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf Boosting Performance and Reliability by using pgpool-II (Tatsuo Ishii)]
* CREATE EXTENSION pgchess; (Gianni Ciolli)

=== Thames ===

* [[Media:Pg-fdw.pdf|Writing a foreign data wrapper (Bernd Helmle)]]
* [http://anarazel.de/2ndquadrant/pgconf-eu-2012-10-24 MultiMaster Replication: Applications, Comparison, Implementation ] (Andres Freund, Simon Riggs)
* [[Media:Pgconfeu12-collectd%2Bpsql.pdf|Watch your Elephants -- Using collectd for PostgreSQL performance analysis]] ([http://tokkee.org/ Sebastian 'tokkee' Harl])
* [[Media:Range-types.pdf|Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same (Jonathan S. Katz)]]
* High availability in Postgres-XC, the symmetric PostgreSQL cluster (Koichi Suzuki)

=== Vltava ===

* Provoz PostgreSQL na AWS (Tomas Vondra)
* [[Media:Plpgsql internals.pdf| PL/pgSQL internals -- some details from PL/pgSQL environment]]
* Migrace z MySQL na PostgreSQL (Tomas Vondra)
* [[Media:Indexy.pdf| Indexy jsou grunt -- basic and enhanced using of indexes in PostgreSQL]]
* Load dat do PostgreSQL (Jan Holčapek)

== Talks: Thursday 25th October, 2012 ==

=== Bellevue (Lightning Talks) ===

* [[Media:Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf| Full-text search in PostgreSQL in milliseconds (Oleg Bartunov, Alexander Korotkov)]]
* [[Media:Pgconfeu-2012-docbot-print.pdf|#PostgreSQL pg_docbot (Andreas 'ads' Scherbaum)]]
* [http://tapoueh.org/images/pgq-coop.pdf PGQ Cooperative Consumers]
* [http://www.pgexperts.com/document.html?id=60 PostgreSQL Drinking Game] (Josh Berkus)

=== Seine ===

* How fast is PostgreSQL? (Cédric Villemain)
* [http://tapoueh.org/images/high-availability.pdf Implementing High Availability] (Dimitri Fontaine)
* [http://momjian.us/main/presentations/internals.html#shared_memory Inside PostgreSQL Shared Memory (Bruce Momjian)]
* [https://plv8-pgconfeu12.herokuapp.com Embracing the Web with JSON and PLV8] ([http://bitfission.com Will Leinweber])

=== Thames ===

* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 Topology and network analysis with PostgreSQL and PostGIS (Vincent Picavet)]
* [[Media:Universal_Data_Access_with_SQL_MED.pdf|Universal Data Access with SQL/MED (David Fetter)]]
* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 PostGIS 2.0 and beyond (Vincent Picavet)]
* Practical Tips for Better PostgreSQL Applications (Marc Balmer)
* Pacemaker and PostgreSQL: to serve and protect your data (Jehan-Guillaume (ioguix) de Rorthais)

=== Vltava ===

* [[Media: Marketing-postgres.pdf|Marketing PostgreSQL (Jonathan S. Katz)]]
* [[Media: Pgconf2012_sprocwrapper.pdf‎|Java Stored Procedure Wrapper and PGObserver (Jan Mussler)]]
* [http://www.pgexperts.com/document.html?id=59 Elephants and Windmills] (Josh Berkus)
* [[Media: Pgeu2012.pdf|PostgreSQL in Research and Development: Three success stories. (Roland Sonnenschein)]]
* [[Media: Index_support_for_regular_expression_search.pdf‎ |Index support for regular expression search (Alexander Korotkov)]]

== Talks: Friday 26th October, 2012 ==

=== Bellevue ===

* Postgres Adoption at the Tipping Point: Users Around the World and Their Deployment Profile (Ed Boyajian)
* Community PostgreSQL (Simon Riggs, Harald Armin Massa)
* Closing (Dave Page)

=== Seine ===

* Beyond Query Logging (Greg Smith, Peter Geoghegan)
* [http://www.hagander.net/talks/Backup_strategies_pgeu.pdf PostgreSQL Backup Strategies] (Magnus Hagander)
* [http://www.gunduz.org/download.php?dlid=196 Maintaining Very Large Databases (VLDs)] (Devrim GÜNDÜZ)
* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL] (Dimitri Fontaine)

=== Thames ===

* [[Media: Postbis_pgcon_eu_2012.pdf|PostBIS - A Bioinformatics Booster for PostgreSQL (Michael Schneider)]]
* Migrating Oracle queries to PostgreSQL (Alexey Klyukin)
* Debugging complex SQL queries with writable CTEs (Gianni Ciolli)
* [http://www.cybertec.at/download/2012_prag_linux_v5.pdf Limiting PostgreSQL resource consumption using the Linux kernel (Hans-Jürgen Schönig)]

=== Vltava ===

* [[Media: Pg_xnode_pgconf_2012.pdf|pg_xnode extension (Antonin Houska)]]
[[Category:PostgreSQL Europe]]
* PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case (Julien Rouhaud)
* [[Media: PGconEU2012-KaiGai-PGStrom.pdf|PG-Strom - GPU Accelerated Asynchronous Query Execution Module (KaiGai Kohei)]]
[[Category:PostgreSQL Europe]]
* [http://tokkee.org/talks/pgconfeu12-time-series-data.pdf Using PostgreSQL for storing time-series data] ([http://tokkee.org/ Sebastian 'tokkee' Harl])

PostgreSQL Conference Europe Talks 2012

2012-10-30T14:08:28Z

Dim: /* Seine */

= PostgreSQL Conference Europe 2012 Talks =

== Trainings: Tuesday 23th October, 2012 ==

=== Clyde ===

* Mastering PostgreSQL Administration (Bruce Momjian, Devrim GÜNDÜZ)

=== Seine ===

* PostgreSQL Performance Training (Greg Smith, Peter Geoghegan)
* PostgreSQL Replication Training (Dimitri Fontaine, Simon Riggs)

=== Thames ===

* Implementace uložených procedur v PostgreSQL (Pavel Stehule)
* Čtení exekučních plánů (Tomas Vondra)

=== Vltava ===

* A day of SQL with Celko (Joe Celko)

== Talks: Wednesday 24th October, 2012 ==

=== Bellevue ===

* Opening keynote (Joe Celko)

=== Seine ===

* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions (Bruce Momjian)]
* PostgreSQL on AWS (Christophe Pettus)
* Understanding EXPLAIN's output (Guillaume Lelarge)
* [http://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf Boosting Performance and Reliability by using pgpool-II (Tatsuo Ishii)]
* CREATE EXTENSION pgchess; (Gianni Ciolli)

=== Thames ===

* [[Media:Pg-fdw.pdf|Writing a foreign data wrapper (Bernd Helmle)]]
* [http://anarazel.de/2ndquadrant/pgconf-eu-2012-10-24 MultiMaster Replication: Applications, Comparison, Implementation ] (Andres Freund, Simon Riggs)
* [[Media:Pgconfeu12-collectd%2Bpsql.pdf|Watch your Elephants -- Using collectd for PostgreSQL performance analysis]] ([http://tokkee.org/ Sebastian 'tokkee' Harl])
* [[Media:Range-types.pdf|Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same (Jonathan S. Katz)]]
* High availability in Postgres-XC, the symmetric PostgreSQL cluster (Koichi Suzuki)

=== Vltava ===

* Provoz PostgreSQL na AWS (Tomas Vondra)
* [[Media:Plpgsql internals.pdf| PL/pgSQL internals -- some details from PL/pgSQL environment]]
* Migrace z MySQL na PostgreSQL (Tomas Vondra)
* [[Media:Indexy.pdf| Indexy jsou grunt -- basic and enhanced using of indexes in PostgreSQL]]
* Load dat do PostgreSQL (Jan Holčapek)

== Talks: Thursday 25th October, 2012 ==

=== Bellevue (Lightning Talks) ===

* [[Media:Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf| Full-text search in PostgreSQL in milliseconds (Oleg Bartunov, Alexander Korotkov)]]
* [[Media:Pgconfeu-2012-docbot-print.pdf|#PostgreSQL pg_docbot (Andreas 'ads' Scherbaum)]]
* [http://www.pgexperts.com/document.html?id=60 PostgreSQL Drinking Game] (Josh Berkus)

=== Seine ===

* How fast is PostgreSQL? (Cédric Villemain)
* [http://tapoueh.org/images/high-availability.pdf Implementing High Availability] (Dimitri Fontaine)
* [http://momjian.us/main/presentations/internals.html#shared_memory Inside PostgreSQL Shared Memory (Bruce Momjian)]
* [https://plv8-pgconfeu12.herokuapp.com Embracing the Web with JSON and PLV8] ([http://bitfission.com Will Leinweber])

=== Thames ===

* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 Topology and network analysis with PostgreSQL and PostGIS (Vincent Picavet)]
* [[Media:Universal_Data_Access_with_SQL_MED.pdf|Universal Data Access with SQL/MED (David Fetter)]]
* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 PostGIS 2.0 and beyond (Vincent Picavet)]
* Practical Tips for Better PostgreSQL Applications (Marc Balmer)
* Pacemaker and PostgreSQL: to serve and protect your data (Jehan-Guillaume (ioguix) de Rorthais)

=== Vltava ===

* [[Media: Marketing-postgres.pdf|Marketing PostgreSQL (Jonathan S. Katz)]]
* [[Media: Pgconf2012_sprocwrapper.pdf‎|Java Stored Procedure Wrapper and PGObserver (Jan Mussler)]]
* [http://www.pgexperts.com/document.html?id=59 Elephants and Windmills] (Josh Berkus)
* [[Media: Pgeu2012.pdf|PostgreSQL in Research and Development: Three success stories. (Roland Sonnenschein)]]
* [[Media: Index_support_for_regular_expression_search.pdf‎ |Index support for regular expression search (Alexander Korotkov)]]

== Talks: Friday 26th October, 2012 ==

=== Bellevue ===

* Postgres Adoption at the Tipping Point: Users Around the World and Their Deployment Profile (Ed Boyajian)
* Community PostgreSQL (Simon Riggs, Harald Armin Massa)
* Closing (Dave Page)

=== Seine ===

* Beyond Query Logging (Greg Smith, Peter Geoghegan)
* [http://www.hagander.net/talks/Backup_strategies_pgeu.pdf PostgreSQL Backup Strategies] (Magnus Hagander)
* [http://www.gunduz.org/download.php?dlid=196 Maintaining Very Large Databases (VLDs)] (Devrim GÜNDÜZ)
* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL] (Dimitri Fontaine)

=== Thames ===

* [[Media: Postbis_pgcon_eu_2012.pdf|PostBIS - A Bioinformatics Booster for PostgreSQL (Michael Schneider)]]
* Migrating Oracle queries to PostgreSQL (Alexey Klyukin)
* Debugging complex SQL queries with writable CTEs (Gianni Ciolli)
* [http://www.cybertec.at/download/2012_prag_linux_v5.pdf Limiting PostgreSQL resource consumption using the Linux kernel (Hans-Jürgen Schönig)]

=== Vltava ===

* [[Media: Pg_xnode_pgconf_2012.pdf|pg_xnode extension (Antonin Houska)]]
[[Category:PostgreSQL Europe]]
* PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case (Julien Rouhaud)
* [[Media: PGconEU2012-KaiGai-PGStrom.pdf|PG-Strom - GPU Accelerated Asynchronous Query Execution Module (KaiGai Kohei)]]
[[Category:PostgreSQL Europe]]
* [http://tokkee.org/talks/pgconfeu12-time-series-data.pdf Using PostgreSQL for storing time-series data] ([http://tokkee.org/ Sebastian 'tokkee' Harl])

PostgreSQL Conference Europe Talks 2012

2012-10-30T14:07:41Z

Dim: /* Seine */

= PostgreSQL Conference Europe 2012 Talks =

== Trainings: Tuesday 23th October, 2012 ==

=== Clyde ===

* Mastering PostgreSQL Administration (Bruce Momjian, Devrim GÜNDÜZ)

=== Seine ===

* PostgreSQL Performance Training (Greg Smith, Peter Geoghegan)
* PostgreSQL Replication Training (Dimitri Fontaine, Simon Riggs)

=== Thames ===

* Implementace uložených procedur v PostgreSQL (Pavel Stehule)
* Čtení exekučních plánů (Tomas Vondra)

=== Vltava ===

* A day of SQL with Celko (Joe Celko)

== Talks: Wednesday 24th October, 2012 ==

=== Bellevue ===

* Opening keynote (Joe Celko)

=== Seine ===

* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions (Bruce Momjian)]
* PostgreSQL on AWS (Christophe Pettus)
* Understanding EXPLAIN's output (Guillaume Lelarge)
* [http://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf Boosting Performance and Reliability by using pgpool-II (Tatsuo Ishii)]
* CREATE EXTENSION pgchess; (Gianni Ciolli)

=== Thames ===

* [[Media:Pg-fdw.pdf|Writing a foreign data wrapper (Bernd Helmle)]]
* [http://anarazel.de/2ndquadrant/pgconf-eu-2012-10-24 MultiMaster Replication: Applications, Comparison, Implementation ] (Andres Freund, Simon Riggs)
* [[Media:Pgconfeu12-collectd%2Bpsql.pdf|Watch your Elephants -- Using collectd for PostgreSQL performance analysis]] ([http://tokkee.org/ Sebastian 'tokkee' Harl])
* [[Media:Range-types.pdf|Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same (Jonathan S. Katz)]]
* High availability in Postgres-XC, the symmetric PostgreSQL cluster (Koichi Suzuki)

=== Vltava ===

* Provoz PostgreSQL na AWS (Tomas Vondra)
* [[Media:Plpgsql internals.pdf| PL/pgSQL internals -- some details from PL/pgSQL environment]]
* Migrace z MySQL na PostgreSQL (Tomas Vondra)
* [[Media:Indexy.pdf| Indexy jsou grunt -- basic and enhanced using of indexes in PostgreSQL]]
* Load dat do PostgreSQL (Jan Holčapek)

== Talks: Thursday 25th October, 2012 ==

=== Bellevue (Lightning Talks) ===

* [[Media:Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf| Full-text search in PostgreSQL in milliseconds (Oleg Bartunov, Alexander Korotkov)]]
* [[Media:Pgconfeu-2012-docbot-print.pdf|#PostgreSQL pg_docbot (Andreas 'ads' Scherbaum)]]
* [http://www.pgexperts.com/document.html?id=60 PostgreSQL Drinking Game] (Josh Berkus)

=== Seine ===

* How fast is PostgreSQL? (Cédric Villemain)
* [http://tapoueh.org/images/high-availability.pdf Implementing High Availability] (Dimitri Fontaine)
* [http://momjian.us/main/presentations/internals.html#shared_memory Inside PostgreSQL Shared Memory (Bruce Momjian)]
* [https://plv8-pgconfeu12.herokuapp.com Embracing the Web with JSON and PLV8] ([http://bitfission.com Will Leinweber])

=== Thames ===

* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 Topology and network analysis with PostgreSQL and PostGIS (Vincent Picavet)]
* [[Media:Universal_Data_Access_with_SQL_MED.pdf|Universal Data Access with SQL/MED (David Fetter)]]
* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2012 PostGIS 2.0 and beyond (Vincent Picavet)]
* Practical Tips for Better PostgreSQL Applications (Marc Balmer)
* Pacemaker and PostgreSQL: to serve and protect your data (Jehan-Guillaume (ioguix) de Rorthais)

=== Vltava ===

* [[Media: Marketing-postgres.pdf|Marketing PostgreSQL (Jonathan S. Katz)]]
* [[Media: Pgconf2012_sprocwrapper.pdf‎|Java Stored Procedure Wrapper and PGObserver (Jan Mussler)]]
* [http://www.pgexperts.com/document.html?id=59 Elephants and Windmills] (Josh Berkus)
* [[Media: Pgeu2012.pdf|PostgreSQL in Research and Development: Three success stories. (Roland Sonnenschein)]]
* [[Media: Index_support_for_regular_expression_search.pdf‎ |Index support for regular expression search (Alexander Korotkov)]]

== Talks: Friday 26th October, 2012 ==

=== Bellevue ===

* Postgres Adoption at the Tipping Point: Users Around the World and Their Deployment Profile (Ed Boyajian)
* Community PostgreSQL (Simon Riggs, Harald Armin Massa)
* Closing (Dave Page)

=== Seine ===

* Beyond Query Logging (Greg Smith, Peter Geoghegan)
* [http://www.hagander.net/talks/Backup_strategies_pgeu.pdf PostgreSQL Backup Strategies] (Magnus Hagander)
* [http://www.gunduz.org/download.php?dlid=196 Maintaining Very Large Databases (VLDs)] (Devrim GÜNDÜZ)
* Large Scale MySQL Migration to PostgreSQL (Dimitri Fontaine)

=== Thames ===

* [[Media: Postbis_pgcon_eu_2012.pdf|PostBIS - A Bioinformatics Booster for PostgreSQL (Michael Schneider)]]
* Migrating Oracle queries to PostgreSQL (Alexey Klyukin)
* Debugging complex SQL queries with writable CTEs (Gianni Ciolli)
* [http://www.cybertec.at/download/2012_prag_linux_v5.pdf Limiting PostgreSQL resource consumption using the Linux kernel (Hans-Jürgen Schönig)]

=== Vltava ===

* [[Media: Pg_xnode_pgconf_2012.pdf|pg_xnode extension (Antonin Houska)]]
[[Category:PostgreSQL Europe]]
* PostgreSQL makes dev happy, a pgAgent + pl/pgsql use case (Julien Rouhaud)
* [[Media: PGconEU2012-KaiGai-PGStrom.pdf|PG-Strom - GPU Accelerated Asynchronous Query Execution Module (KaiGai Kohei)]]
[[Category:PostgreSQL Europe]]
* [http://tokkee.org/talks/pgconfeu12-time-series-data.pdf Using PostgreSQL for storing time-series data] ([http://tokkee.org/ Sebastian 'tokkee' Harl])

Postgres Open 2012

2012-09-19T19:42:43Z

Dim:

Slides for talks will be linked here!

== Tuesday September 18 ==

* [https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same]
* Scaling out by distributing and replicating data in Postgres-XC
* PostgreSQL When It's Not Your Job

* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL]
* Choosing a logical replication system: Slony vs Bucardo
* Temporal Database Demo

* 12 Calm Years of PostgreSQL in Critical Messaging
* PostgreSQL in the cloud: Theory and Practice
* [http://www.hagander.net/talks/Backup%20strategies.pdf PostgreSQL Backup Strategies]

* This Is PostGIS
* Embracing the Web with JSON and PLV8
* How Akiban Implemented a New Database Compatible with the PostgreSQL Protocol

* [[Media:Ha_postgres.pdf|High Availability with PostgreSQL and Pacemaker]]
* Logging: Not Just for Lumberjacks
* [http://stuff.coffeecode.net/2012/pgopen_fulltext/pgsql-fulltext-intro.html Full-text search - seek and ye shall find]

=== Lightning Talks ===

TBD!

== Wednesday September 19 ==

* [http://pgexperts.com/document.html?id=54 Super Jumbo Deluxe], plus [http://pgexperts.com/presentations.html more presentations here]
* [[Media:OO_approach.pdf|An object oriented approach to data driven software development]]
* TransLattice Elastic Database Architecture Deep Dive

* [http://www.pateldenish.com/2012/04/deploying-maximum-ha-architecture-with-postgresql.html Deploying maximum HA architecture with Postgres] , for pg_reorg [http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=p90xyourdatabase-13010796913746-phpapp02&stripped_title=p90-x-your-database p90x talk]
* Scaling Postgres with some help from Redis
* Retail DDL

* Query Logging and Workload Analysis
* A Shared-nothing cluster system: Postgres-XC
* DVDStore Benchmark and PostgreSQL

* Performance Improvements in PostgreSQL 9.2
* A Batch of Commit Batching
* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions]

* Postgres is the new default – how we transitioned our platform at Engine Yard and why you should too
* Leveraging PLV8 in Javascript-heavy Web Applications
* PG Extractor - A smarter pg_dump

Lock Monitoring

2012-05-25T09:18:01Z

Dim:

* Looking at [http://www.postgresql.org/docs/current/static/view-pg-locks.html pg_locks] shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems:
select relation::regclass, * from pg_locks where not granted;
* Figuring out what the processes holding or waiting for locks is easier if you cross-reference against the information in [http://www.postgresql.org/docs/current/static/monitoring-stats.html pg_stat_activity]
* The following query may be helpful to see what processes are blocking SQL statements:
select bl.pid as blocked_pid, a.usename as blocked_user,
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement
from pg_catalog.pg_locks bl
join pg_catalog.pg_stat_activity a
on bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka
on kl.pid = ka.procpid
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;
* Here's an alternate view of that same data that includes an idea how old the state is:

select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;

* Here's almost quite the same thing but with some more details:

select bl.pid as blocked_pid, a.usename as blocked_user,
ka.current_query as blocking_statement, now() - ka.query_start as blocking_duration,
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement,
now() - a.query_start as blocked_duration
from pg_catalog.pg_locks bl
join pg_catalog.pg_stat_activity a
on bl.pid = a.procpid
join pg_catalog.pg_locks kl
join pg_catalog.pg_stat_activity ka
on kl.pid = ka.procpid
on bl.transactionid = kl.transactionid and bl.pid != kl.pid
where not bl.granted;

* If you suspect intermittent locks are causing problems only sometimes, but are having trouble catching them in one of these live views, setting the [http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS log_lock_waits] and related [http://www.postgresql.org/docs/current/static/runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT deadlock_timeout] parameters can be helpful. Then slow lock acquisition will appear in the database logs for later analysis.

See also [[Lock dependency information]]
[[Category:Administration]][[Category:Performance]][[Category:Monitoring]]

Queuing

2012-05-16T13:24:51Z

Dim: /* Queueing in core */

= Queueing in core =

Internal use cases:

* Materialized Views
* Alter Table Concurrently
* Cluster Concurrently
* Parallel queries
* PG/Strom: GPU Offloading (parallel too)

Parallel queries have different transactional semantics needs (it's all running in the same transaction, producer and consumer)

External use cases:

* Very Common Design Pattern
* Datawarehouse and pruning historical data (avoid vacuum issues)

Implementation:

* it's a table?
* concurrent inserts and pruning
* no update
* payload would be a record/tuple (create type / create table)
* FIFO, Insert Only Segment Optimisation with a starting block that can be > 0

PgCon 2012 Developer Meeting

2012-05-16T13:23:57Z

Dim: /* Agenda */

A meeting of the most active PostgreSQL developers is being planned for Wednesday 16th May, 2012 near the University of Ottawa, prior to pgCon 2012. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org).

Please note that this year the attendee numbers have been cut to try to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.2 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.

This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.

== Time & Location ==

The meeting will be from 8:30AM to 5PM, and will be in the "Red Experience" room at:

Novotel Ottawa
33 Nicholas Street
Ottawa
Ontario
K1N 9M7

Food and drink will be provided throughout the day, including breakfast from 8AM.

[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]

== Attendees ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Oleg Bartunov
* Josh Berkus (Secretary)
* Jeff Davis
* Andrew Dunstan
* Dimitri Fontaine
* Stephen Frost
* Peter Geoghegan
* Kevin Grittner
* Robert Haas
* Magnus Hagander
* Shigeru Hanada
* Hitoshi Harada
* KaiGai Kohei
* Tom Lane
* Noah Misch
* Bruce Momjian
* Dave Page (Chair)
* Simon Riggs
* Teodor Sigaev
* Greg Smith

== Proposed Agenda Items ==

Please list proposed agenda items here:

* Agree CommitFest schedule for 9.3 (Strawman from Simon)
** CF1 June 15, 2012 - 1 month
** CF2 Sep 15, 2012 - 1 month
** CF3 Nov 15, 2012 - 1 month
** CF4 Jan 15, 2013 - 2 months
* Queuing [Dimitri, Kevin]
** Description: efficient and transactional queuing is a very common need for application using databases, and could help implementing some internal features
** Goals: get an agreement that core is the right place where to solve that problem, and what parts of it we want in core exactly
* Materialized views [Kevin]
** Description: Declarative materialized views are a frequently requested feature, but means many things to many people. It's not likely that an initial implementation will address everything. We need a base set of functionality on which to build.
** Goals: Reach consensus on what a minimum feature set for commit would be.
* Partitioning and Segment Exclusion [Dimitri]
** Description: to solve partitioning, we need to agree on a global approach
** Goals: agreeing on SE as a basis for better partitioning, having a "GO" on working on SE
* MERGE: Challenges and priorities [Peter G]
** Description: Implementing the MERGE statement for 9.3. It is envisaged specifically as an atomic "upsert" operation.
** Goals: To get buy-in on various aspects of the feature's development, and, ideally, to secure reviewer resources or other support. Because of the complexity of the feature, early interest from reviewers is preferable.
* Row-level Access Control and SELinux [KaiGai]
** Security label on user tables
** Dynamic expandable enum data types
** Enforcement of triggers by extension
* Enhancement of FDW at v9.3 [KaiGai]
** Writable foreign tables
** Stuffs to be pushed down (Join, Aggregate, Sort, ...)
** Inheritance of foreign/regular tables
** Constraint (PK/FK) & Trigger support.
* Type registry [Andrew]
** Provide for known OIDs for non-builtin types, and possibly for their IO functions too
** Would make it possible to write code in core or in extension X that handles a type defined in extension Y.
* Ending CommitFests in a timely fashion, especially the last one. Avoiding a crush of massive feature patches at the end of the cycle. Handling big patches that aren't quite ready yet. Getting more people to help with patch review. [Robert]
* What Developers Want [Josh]
** Description: a top-5 list of features and obstacles to developer adoption of PostgreSQL (with slides)
** Goal: to set priorities for some features aimed at application users
* In-Place Upgrades & Checksums [Greg Smith, Simon]
** Description: Revisit in-place upgrades of the page format, now that pg_upgrade is available and multiple checksum implementations needing it have been proposed.
** Goal: Nail down some incremental milestones for 9.3 development to aim at.
* Autonomous Transactions [Simon]
** Overview of idea, relationship to stored procedures
** Feedback, buy-in and/or alternatives
* Parallel Query [Bruce Momjian]
** Hope to get buy-in for what parallel operations we are hoping to add in upcoming releases
* Report from Clustering Meeting [Josh] (10 min)
** Description: to summarize the discussions of the cluster-hackers meeting from the previous day
** Goal: inter-team synchronization. Possibly, decisions requested on specific in-core features.
* Double Write Buffers [Simon]
** Is anyone committing to do that for 9.3?

* Goals, priorities, and resources for 9.3 [All]
** For roadmap and planning purposes, set expectations and coordinate work schedules for 9.3. Confirm who is doing what, identify interested reviewers at start, and check for gaps.

== Agenda ==

{| border="1" cellpadding="4" cellspacing="0"
!Time
!Item
!Presenter
|- style="font-style:italic;background-color:lightgray;"
|08:00
|Breakfast
|

|- style="font-style:italic;background-color:lightgray;"
|08:30 - 08:45
|Welcome and introductions
|Dave Page

|-
|08:45 - 09:15
|Autonomous transactions
|Simon Riggs

|-
|09:15 - 09:40
|[[Queuing]]
|Dimitri Fontaine/Kevin Grittner

|-
|09:40 - 09:50
|Report from the Clustering Meeting
|Josh Berkus

|-
|09:50 - 10:10
|Type registry
|Andrew Dunstan

|-
|10:10 - 10:30
|Access control and SELinux
|KaiGai Kohei

|- style="font-style:italic;background-color:lightgray;"
|10:30 - 10:45
|Coffee break
|

|-
|10:45 - 11:15
|Enhancement of FDWs in 9.3
|KaiGai Kohei

|-
|11:15 - 11:30
|What developers want
|Josh Berkus

|-
|11:30 - 12:00
|Parallel Query
|Bruce Momjian

|-
|12:00 - 12:30
|MERGE: Challenges and priorities
|Peter Geoghegan

|- style="font-style:italic;background-color:lightgray;"
|12:30 - 13:30
|Lunch
|

|-
|13:30 - 14:00
|Materialised views
|Kevin Grittner

|-
|14:00 - 14:20
|In place upgrades and checksums
|Simon Riggs/Greg Smith

|-
|14:20 - 14:45
|Partitioning and segment exclusion
|Dimitri Fontaine

|-
|14:45 - 15:00
|Commitfest Schedule
|All

|- style="font-style:italic;background-color:lightgray;"
|15:00 - 15:15
|Tea break
|

|-
|15:15 - 15:40
|Commitfest management
|Robert Haas

|-
|15:40 - 16:45
|Goals, priorities, and resources for 9.3
|All

|- style="font-style:italic;background-color:lightgray;"
|16:45 - 17:00
|Any other business/group photo
|Dave Page

|- style="font-style:italic;background-color:lightgray;"
|17:00
|Finish
|
|}

==Minutes==

Queuing

2012-05-16T13:23:51Z

Dim: /* Queueing in core */

= Queueing in core =

Internal use cases:

* Materialized Views
* Alter Table Concurrently
* Cluster Concurrently
* Parallel queries
* PG/Strom: GPU Offloading

External use cases:

* Very Common Design Pattern
* Datawarehouse and pruning historical data (avoid vacuum issues)

Implementation:

* it's a table?
* concurrent inserts and pruning
* no update
* payload would be a record/tuple (create type / create table)
* FIFO, Insert Only Segment Optimisation with a starting block that can be > 0

Queuing

2012-05-16T13:22:08Z

Dim: moved Queueing to Queuing: Typo.

= Queueing in core =

Internal use cases:

* Materialized Views
* Alter Table Concurrently
* Cluster Concurrently
* Parallel queries

External use cases:

* Very Common Design Pattern
* Datawarehouse and pruning historical data (avoid vacuum issues)

Implementation:

* it's a table?
* concurrent inserts and pruning
* no update
* payload would be a record/tuple (create type / create table)
* FIFO, Insert Only Segment Optimisation with a starting block that can be > 0

Queueing

2012-05-16T13:22:08Z

Dim: moved Queueing to Queuing: Typo.

#REDIRECT [[Queuing]]

Queuing

2012-05-16T13:19:06Z

Dim: /* Queueing in core */

Queuing

2012-05-16T13:15:41Z

Dim:

= Queueing in core =

Internal use cases:

* Materialized Views
* Alter Table Concurrently
* Cluster Concurrently
* Parallel queries

External use cases:

* Very Common Design Pattern
* Datawarehouse and pruning historical data (avoid vacuum issues)

Implementation:

* it's a table
* concurrent inserts and pruning
* no update
* payload would be a record/tuple (create type / create table)

Queuing

2012-05-16T13:14:39Z

Dim: Queueing in Core

= Queueing in core

Internal use cases:

- Materialized Views
- Alter Table Concurrently
- Cluster Concurrently
- Parallel queries

External use cases:

- Very Common Design Pattern
- Datawarehouse and pruning historical data (avoid vacuum issues)

Implementation:

- it's a table
- concurrent inserts and pruning
- no update
- payload would be a record/tuple (create type / create table)

PgCon 2012 Developer Meeting

2012-05-04T21:56:36Z

Dim: /* Proposed Agenda Items */

A meeting of the most active PostgreSQL developers is being planned for Wednesday 16th May, 2012 near the University of Ottawa, prior to pgCon 2012. In order to keep the numbers manageable, this meeting is '''by invitation only'''. Unfortunately it is quite possible that we've overlooked important code developers during the planning of the event - if you feel you fall into this category and would like to attend, please contact Dave Page (dpage@pgadmin.org).

Please note that this year the attendee numbers have been cut to try to keep the meeting more productive. Invitations have been sent only to developers that have been highly active on the database server over the 9.2 release cycle. We have not invited any contributors based on their contributions to related projects, or seniority in regional user groups or sponsoring companies, unlike in previous years.

This is a PostgreSQL Community event. Room and refreshments/food sponsored by EnterpriseDB. Other companies sponsored attendance for their developers.

== Time & Location ==

The meeting will be from 9AM to 5PM, and will be in the "Red Experience" room at:

Novotel Ottawa
33 Nicholas Street
Ottawa
Ontario
K1N 9M7

Food and drink will be provided throughout the day, including breakfast from 8AM.

[http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=novotel+ottawa&aq=&sll=49.891235,-97.15369&sspn=36.237851,79.013672&ie=UTF8&hq=novotel+ottawa&hnear=&ll=45.421528,-75.683699&spn=0.036869,0.077162&z=14&iwloc=A&layer=c&cbll=45.425741,-75.689638&panoid=Z4FUGnkZkdHAOkIxyjjS9Q&cbp=12,25.83,,0,-0.6 View on Google Maps]

== Attendees ==

The following people have RSVPed to the meeting (in alphabetical order, by surname):

* Oleg Bartunov
* Josh Berkus (Secretary)
* Jeff Davis
* Andrew Dunstan
* Dimitri Fontaine
* Stephen Frost
* Peter Geoghegan
* Kevin Grittner
* Robert Haas
* Magnus Hagander
* Shigeru Hanada
* Hitoshi Harada
* KaiGai Kohei
* Tom Lane
* Noah Misch
* Bruce Momjian
* Dave Page (Chair)
* Simon Riggs
* Teodor Sigaev
* Greg Smith

== Proposed Agenda Items ==

Please list proposed agenda items here:

* Priorities for 9.3 [All]
** Description: discuss what people are working on and what's likely to be in 9.3.
** Goals: set expectations and coordinate work schedules for 9.3.
* Queuing [Dimitri, Kevin]
** Description: efficient and transactional queuing is a very common need for application using databases, and could help implementing some internal features
** Goals: get an agreement that core is the right place where to solve that problem, and what parts of it we want in core exactly
* Materialized views [Kevin]
* Partitioning and Segment Exclusion [Dimitri]
** Description: to solve partitioning, we need to agree on a global approach
** Goals: agreeing on SE as a basis for better partitioning, having a "GO" on working on SE
* The MERGE statement: Challenges and priorities [Peter G]
* Row-level Access Control and SELinux [KaiGai]
** Security label on user tables
** Dynamic expandable enum data types
** Enforcement of triggers by extension
* Enhancement of FDW at v9.3 [KaiGai]
** Writable foreign tables
** Stuffs to be pushed down (Join, Aggregate, Sort, ...)
** Inheritance of foreign/regular tables
** Constraint (PK/FK) & Trigger support.
* Type registry [Andrew]
** Provide for known OIDs for non-builtin types, and possibly for their IO functions too
** Would make it possible to write code in core or in extension X that handles a type defined in extension Y.
* Ending CommitFests in a timely fashion, especially the last one. Avoiding a crush of massive feature patches at the end of the cycle. Handling big patches that aren't quite ready yet. Getting more people to help with patch review. [Robert]
* What Developers Want [Josh]
** Description: a top-5 list of features and obstacles to developer adoption of PostgreSQL (with slides)
** Goal: to set priorities for some features aimed at application users
* In-Place Upgrades & Checksums [Greg Smith, Simon]
** Description: Revisit in-place upgrades of the page format, now that pg_upgrade is available and multiple checksum implementations needing it have been proposed.
** Goal: Nail down some incremental milestones for 9.3 development to aim at.
* Autonomous Subtransactions [Simon]
* Parallel Query [Bruce Momjian]
* Report from Clustering Meeting [Josh] (10 min)
** Description: to summarize the discussions of the cluster-hackers meeting from the previous day
** Goal: inter-team synchronization. Possibly, decisions requested on specific in-core features.

== Agenda ==

{| border="1" cellpadding="4" cellspacing="0"
!Time
!Item
!Presenter
|- style="font-style:italic;background-color:lightgray;"
|08:00
|Breakfast
|
|- style="font-style:italic;background-color:lightgray;"
|08:45 - 09:00
|Welcome and introductions
|Dave Page
|-
|- style="font-style:italic;background-color:lightgray;"
|10:30 - 10:45
|Coffee break
|
|- style="font-style:italic;background-color:lightgray;"
|12:30 - 13:30
|Lunch
|
|- style="font-style:italic;background-color:lightgray;"
|15:00 - 15:15
|Tea break
|
|- style="font-style:italic;background-color:lightgray;"
|16:45 - 17:00
|Any other business/group photo
|Dave Page
|- style="font-style:italic;background-color:lightgray;"
|17:00
|Finish
|
|}

==Minutes==

PgCon 2012 Developer Meeting

2012-05-04T21:54:15Z

Dim: /* Proposed Agenda Items */

PgCon 2012 Developer Meeting

2012-02-21T08:53:17Z

Dim: /* Proposed Agenda Items */

PgCon 2012 Developer Meeting

2012-02-17T15:58:19Z

Dim: /* Proposed Agenda Items */

PgCon 2012 Developer Meeting

2012-02-17T15:56:53Z

Dim: /* Attendees */

Project Hosting

2012-01-10T21:57:44Z

Dim:

PostgreSQL provides several way to integrate with [http://www.postgresql.org/docs/9.0/interactive/external-projects.html External Projects], both inside and outside of the core database itself. The PostgreSQL Development Group (PGDG) provides some resources toward hosting PostgreSQL projects.

The current home for many PostgreSQL projects is [http://pgfoundry.org/ PgFoundry], a free resource provided by the PGDG. PgFoundry provides projects with a full set of resources, including version control, mailing lists with archives, and bug tracking. The only source code management (SCM) repository type supported is CVS. Because many projects want more modern SCM options, some existing and new projects have instead hosted their resources at other places. An upgrade to PgFoundry is not expected, and the PGDG is exploring a plan to eventually shut the site down. New projects should consider an alternative hosting site.

Some add-on projects are considered critical to PostgreSQL adoption. One such category are drivers for popular programming languages that are not shipped with the database itself (JDBC, ODBC, others). Critical projects can request space on [http://git.postgresql.org git.postgresql.org] along with a [http://www.postgresql.org/community/lists/ mailing list].

[https://github.com/ github] is a popular site for hosting projects using the git SCM. It provides some basic project tools such as an issue tracker. There is a [https://github.com/postgres-mirror PostgreSQL mirror at github] you can fork to easily create patches against the database source code. github doesn't provide a good way to provide project documentation, source releases (AKA tarballs), or any sort of mailing list for the project. Some projects have combined github for the main project hosting with [http://groups.google.com Google groups] to provide an archived mailing list.

Other sites that are possible places to host a PostgreSQL related project at include:
* [http://sourceforge.net/ sourceforge]
* [http://code.google.com/ Google code]
* [https://launchpad.net/ Launchpad]
* [https://bitbucket.org/ bitbucket]
* [http://tuxfamily.org/en/about TuxFamily]

Each of these sites has a different mix of features it supports. Support for the preferred SCM of the developer is often the first thing considered, since some of these sites only support one of them. For example, github only handles git, and Launchpad only supports Bazaar.

Project Hosting

2012-01-10T21:57:24Z

Dim: http://tuxfamily.org/en/about

PostgreSQL provides several way to integrate with [http://www.postgresql.org/docs/9.0/interactive/external-projects.html External Projects], both inside and outside of the core database itself. The PostgreSQL Development Group (PGDG) provides some resources toward hosting PostgreSQL projects.

The current home for many PostgreSQL projects is [http://pgfoundry.org/ PgFoundry], a free resource provided by the PGDG. PgFoundry provides projects with a full set of resources, including version control, mailing lists with archives, and bug tracking. The only source code management (SCM) repository type supported is CVS. Because many projects want more modern SCM options, some existing and new projects have instead hosted their resources at other places. An upgrade to PgFoundry is not expected, and the PGDG is exploring a plan to eventually shut the site down. New projects should consider an alternative hosting site.

Some add-on projects are considered critical to PostgreSQL adoption. One such category are drivers for popular programming languages that are not shipped with the database itself (JDBC, ODBC, others). Critical projects can request space on [http://git.postgresql.org git.postgresql.org] along with a [http://www.postgresql.org/community/lists/ mailing list].

[https://github.com/ github] is a popular site for hosting projects using the git SCM. It provides some basic project tools such as an issue tracker. There is a [https://github.com/postgres-mirror PostgreSQL mirror at github] you can fork to easily create patches against the database source code. github doesn't provide a good way to provide project documentation, source releases (AKA tarballs), or any sort of mailing list for the project. Some projects have combined github for the main project hosting with [http://groups.google.com Google groups] to provide an archived mailing list.

Other sites that are possible places to host a PostgreSQL related project at include:
* [http://sourceforge.net/ sourceforge]
* [http://code.google.com/ Google code]
* [https://launchpad.net/ Launchpad]
* [https://bitbucket.org/ bitbucket]
* [http://tuxfamily.org/en/about]

Each of these sites has a different mix of features it supports. Support for the preferred SCM of the developer is often the first thing considered, since some of these sites only support one of them. For example, github only handles git, and Launchpad only supports Bazaar.

File:Using-extensions.pdf

2011-10-24T13:13:44Z

Dim:

PostgreSQL 9.1 features extensibility to the next level. While this often is a concern for C-developers among us, this talk will detail why extensions are a first-class facility for anyone doing Stored Procedures, whatever their implementation language.

If you ever did type CREATE OR REPLACE FUNCTION, be it in LANGUAGE SQL, then you're in!

File:Using-extensions.pdf

2011-10-24T13:11:34Z

Dim:

PostgreSQL Conference Europe Talks 2011

2011-10-24T13:10:50Z

Dim: Dimitri Fontaine: Extensions are good for Business Logic

= PostgreSQL Conference Europe 2011 Talks =

== Talks: Wednesday 19th October, 2011 ==

=== Ams 1 ===
* [[Media:Mohan_PGCon_Keynote_10-19-2011.pdf|Ram Mohan Keynote: Afilias Winning Bet on Open Source]]
* [http://www.oslandia.com/files/1646fd60f9b0a4558d1e256cf3264572/pgconfeu2011_vincent_picavet_postgis.pdf Vincent Picavet - Geo in your database : Postgis]
* Heralding the Death of nosql — Will Leinweber, Heroku — http://pgeu-plv8.herokuapp.com
* [http://scanningpages.wordpress.com/?attachment_id=420| Slony Troubleshooting - Steve Singer, Afilias ]

=== Ams 2 ===
* [http://www.mgrid.net/blogs/mgrid-at-pgdayeu-2011 Yeb Havinga: Introducing ISO-21090 Healthcare Datatypes]
* [http://ora2pg.darold.net/ora2pg-best-practices.pdf Gilles Darold: Best practices with Ora2Pg]

=== Ams 3 ===

* [[Media:Ciolli-window-2011.pdf|Gianni Ciolli, Look Out The Window Functions (and free your SQL)]]
plus four 2D heat diffusion movies, with different starting scenarios: [[http://vimeo.com/30797130 a hot point]],
[[http://vimeo.com/30797509 a hot segment]],
[[http://vimeo.com/30797715 a hot square]] and
[[http://vimeo.com/30797797 a cold square]].
* [[Media:Smartgrid_for_the_datacenter_web.pdf|Stefan Kaltenbrunner, A smartgrid for the datacenter]]
* [http://momjian.us/main/presentations/internals.html#optimizer Explaining the Postgres Query Optimizer]

== Talks: Thursday 20th October, 2011 ==

=== Ams 1 ===

* A Postgres Service (Peter van Hardenberg, Heroku) http://pgconfeu11-service.herokuapp.com/

* [[Media:SSI-PGConfEU2011.pdf|Heikki Linnakangas: Serializable Snapshot Isolation ]]

* [[Media:Fast_GiST_index_build.pdf|Alexander Korotkov: Fast GiST index build ]]

* [[Media:pgconf2011.eu-berg-pgapt.pdf|Christoph Berg: Connecting the Debian and PostgreSQL worlds ]]

=== Ams 2 ===
* [[Media:using-extensions.pdf|Dimitri Fontaine: Extensions are good for business logic]]
* Exposing the Power of Postgres to Ruby — Will Leinweber, Heroku — http://pgeu-ruby.herokuapp.com
* [http://wiki.postgresql.org/images/e/e6/Django-extensions.pdf Jonathan S. Katz: Writing Django Extensions for PostgreSQL]
* [[Media:Pgconf-2011-mysql-to-pg-print.pdf|Andreas 'ads' Scherbaum: Port databases from MySQL to PostgreSQL]]

=== Ams 3 ===
* [[Media:Ciolli-debug-wcte-2011.pdf|Gianni Ciolli, Debug complex SQL queries with writable CTEs]]
* [[Media:pgconfeu_stats.odp|Guillaume Lelarge, What use are the statistics views?]]
* Lightning talk: [[Media:Ciolli-waiting-for-the-barman-2011.pdf|Gabriele Bartolini and Gianni Ciolli, waiting for (the) BaRMan]]

== Talks: Friday 21st October, 2011 ==

=== Ams 1 ===
* Reliable Databases in The Cloud with WAL-E (redux) - Dan Farina, Heroku - http://wal-e-pgeu2011.herokuapp.com/
* [http://wiki.postgresql.org/images/4/46/Knn.pdf Jonathan S. Katz: Accelerating Local Search With PostgreSQL 9.1]

=== Ams 2 ===

* [http://bunsen.credativ.com/~mme/2011/PGConf_EU.pdf Michael Meskes: Mission Impossible?]

=== Ams 3 ===
* [http://wiki.postgresql.org/images/f/fa/Pg-eu-2011-closing-keynote.pdf Bruce Momjian: Closing Keynote]

PostgreSQL 9.1 Open Items

2011-06-06T13:37:53Z

Dim: /*core extensions */

== Project Planning ==
See the [[PostgreSQL 9.1 Development Plan]].

== Meta-Issues ==
* [[standard_conforming_strings]] -- readiness of drivers and applications
* Review "Long Term" list of items from [[PostgreSQL 9.0 Open Items]]

== Open Issues ==

=== Blockers for Beta2 ===
* {{MessageLink|4DC00203020000250003D1E8@gw.wicourts.gov|Make DDL commands SSI-aware}}
* {{MessageLink|7486.1306253575@sss.pgh.pa.us|Domains over arrays no longer match ANYARRAY}}
* {{MessageLink|201105261137.p4QBbTlt077425@wwwmaster.postgresql.org|HS slaves do not handle unlogged tables nicely (bug 6041)}}
* {{MessageLink|BANLkTiknTSLMbGuTaOYg4O0V7UAMsr_rOA|NOT VALID constraints don't dump properly}}

=== Not Blockers for Beta2 ===
* [http://archives.postgresql.org/pgsql-general/2011-01/msg00890.php generate_series boundary issue] [http://archives.postgresql.org/pgsql-hackers/2011-02/msg00423.php and prototype patch]
** This is a pre-existing bug, not a regression, so it is not a beta blocker.
* {{MessageLink|4CEA5A0F.1030602@enterprisedb.com|do latches have memory-ordering problems?}}
** [http://archives.postgresql.org/message-id/11500.1302097336@sss.pgh.pa.us mostly just needs testing]
* {{MessageLink|4DA58686.1050501@enterprisedb.com| throw an error if you try to start from incomplete backup taken with pg_basebackup}}
* {{MessageLink|4DE8B1A8.2020505@enterprisedb.com| add a proof to README-SSI for the validity of the READ ONLY optimizations}}
** {{MessageLink|20110602070419.GA10064@csail.mit.edu| Dan offered a concise proof, but we need a cite for one premise it uses}}
** {{MessageLink|4DE67235020000250003DFBC@gw.wicourts.gov| Kevin offered a self-contained proof, but it's much longer than Dan's}}
* {{MessageLink|4DD3D6C6.5060006@2ndquadrant.com|core extensions, part of the main docs, shipped by default}}

== Resolved Issues ==

=== Issues Resolved Prior to Beta2 ===
* {{MessageLink|20110428194112.GB12161@tornado.leadboat.com|ALTER TYPE DROP + composite-typed col vs. pg_upgrade}}
* {{MessageLink|4DB7D194.6030906@enterprisedb.com|Memory leak in foreign scans}}
* [http://archives.postgresql.org/message-id/19738.1306338472@sss.pgh.pa.us vacuum sometimes fails to update relpages/reltuples]
* {{MessageLink|BANLkTik8KxxjJ1KW-pO+WWBdTEAT+80ArQ@mail.gmail.com|SSI HOT chain traversal issue}}

=== Issues Resolved Prior to Beta1 ===

* [http://archives.postgresql.org/message-id/AANLkTin4o+eSgQsP=0i6EM=Evu3oX=ewHp8Bwod1UaWZ@mail.gmail.com wal_buffers = -1 causes spurious chatter on reload]
* [http://archives.postgresql.org/message-id/AANLkTi=jCTGC+Qxfkum6EXcw8q4tRFd2KHNpe+4HYUeS@mail.gmail.com is there a safeguard to prevent recovery from pausing before consistency is reached?]
* {{MessageLink|09B23E7BF70425478C1330D893A722C602FEC019BD@MailSVR.invera.com|Walreceiver crashes in AIX}}
** {{MessageLink|4C753155.3070708@ca.afilias.info|Steve Singer can't reproduce, suggests a possible way this could be pilot error}}
* {{MessageLink|AANLkTik9HZi8GfSiKuHVY2N7g7xDV+sN46eRxPbOjO7P@mail.gmail.com|bug of the hot standby feedback}}
* {{MessageLink|201011271931.oARJVV427882@momjian.us|GIST rewrite vs. pg_upgrade}}
* [http://archives.postgresql.org/message-id/AANLkTin3PPOwXq2Cpf+tLNNKSv4OmHfDn5qr0aoeczA-@mail.gmail.com replication/README needs to be updated]
* [http://archives.postgresql.org/message-id/AANLkTikJmP+bo1N-mFUWEpJiV6_OKisYw512OGeTJUbm@mail.gmail.com backend wrongly waits for sync rep even though max_wal_senders = 0]
* [http://archives.postgresql.org/message-id/AANLkTik4tuG2EA6oeiov1=DO6UcDoARP45Lk+KGfy7HC@mail.gmail.com reload of the configuration file should not cause the server to end unexpectedly]
* [http://archives.postgresql.org/pgsql-bugs/2010-11/msg00002.php Fix ecpg preprocessor regression]
* {{MessageLink|AANLkTimDUiRrrWzZ2ZXWSRfeP5tHm9PGmpp6zfqaFpte@mail.gmail.com|Once sync_standbys_defined becomes true, there's no way for it to ever become false again. That can't be right.}}
* {{MessageLink|AANLkTimfvNzJy490wp95vP1RLumkxEZBTWUjhA_Dd1QS@mail.gmail.com|backend no longer needs to wait for replication when synchronous_standby_names is set to '' and configuration file is reloaded.}}
* [http://archives.postgresql.org/message-id/AANLkTi=z+eg6yMEtJCqYb0OnGaNMoTg9eYiK-xm8v0AQ@mail.gmail.com pause_at_recovery_target needs to be added in recovery.conf.sample]
* {{MessageLink|201101181630.p0IGU45v047971@wwwmaster.postgresql.org|BUG #5842: Memory leak in PL/Python when taking slices of results}}
* {{MessageLink|4D5EB8ED.7010002@enterprisedb.com|hot standby feedback message needs to be explained at protocol-replication.html}}
* [http://archives.postgresql.org/pgsql-hackers/2011-03/msg00720.php consider renaming ident to peer authentication on local connections]
* [http://archives.postgresql.org/message-id/AANLkTi=q5=x_WoOccf5dqHoAWXyDc7qXG0cpbnsNKUCB@mail.gmail.com serious problem by multiple backups]
* [http://archives.postgresql.org/message-id/AANLkTi=qR0zy5cS98n=TDCxda5sj=NODDA=xAZUCdJOE@mail.gmail.com FK constraints "NOT VALID" by default]
* [http://archives.postgresql.org/message-id/AANLkTikvpjkUjwksZK=wOw7CDyMtaVEddmPN9yFv7jX-@mail.gmail.com sync rep very slow when fsync=off on standby]
* [http://archives.postgresql.org/message-id/AANLkTinbzFaJXkzwm2xEegfytK1LPw8odo61wgZkkGp=@mail.gmail.com No longer need to check synchronous_standby_names and max_wal_senders at startup]
* {{MessageLink|201012071131.55211.gabi.julien@broadsign.com|pg_last_xact_replay_timestamp limitations}}
** rhaas says: It's not clear there's an action item here, so moving to resolved. Feel free to move back with more details.
* [http://archives.postgresql.org/message-id/AANLkTikUAOYoStwkwG+DZOzTwT2QVj0H9aDywpzxvhxn@mail.gmail.com either remove write_location completely, or revert the change that broke it]
** [http://archives.postgresql.org/message-id/AANLkTinHrymKd56m5AfawCdujuNM6B2g_--9UiOSSKGx@mail.gmail.com original report of problem with write_location] (but the other issues in that email are now fixed)
** [http://archives.postgresql.org/message-id/AANLkTikphXd4LMXXOAZJg2s_8q0Fu5e3uuec4BG4xD4f@mail.gmail.com possible patch]
* {{MessageLink|29244.1295376372@sss.pgh.pa.us|DO blocks leak memory}}
* {{MessageLink|AANLkTimnwxEv-ZbqBLCSBSvmq-80vzvDb2u0pPchGm2r@mail.gmail.com|replication timeout}}
* {{MessageLink|1293977249.5984.17.camel@vanquo.pezone.net|raise protocol version number}}
** [http://archives.postgresql.org/message-id/1240.1301353669@sss.pgh.pa.us not done, would break things]
* [http://archives.postgresql.org/message-id/AANLkTik6ArKPwnvA8_9XHo9j9+w4A2UEnsheX-mwR=Aj@mail.gmail.com fix attinhcount tracking]
* [http://archives.postgresql.org/pgsql-hackers/2011-04/msg00033.php SSI: SIReadLock lines in pg_locks don't show pid]
** questions have been raised whether pid should be suppressed after connection closes or even maybe after transaction completion
* [http://archives.postgresql.org/message-id/AANLkTi%3DkoHqna9WMm8_ATJN6c1GOLLp_0Tx6VswKhAdi%40mail.gmail.com synchronous_commit and synchronous_replication]
* {{MessageLink|AANLkTimUuzn3DAaO1OLCijjjfcw0MXL-0HAvZKr9xRyV@mail.gmail.com|conversion from integer literals to money type}}
* [http://archives.postgresql.org/message-id/4D892157.7070607@lelarge.info comments on SQL/MED objects]
** rhaas says: [http://archives.postgresql.org/message-id/AANLkTimS1tDEuEeocz9PBjQ-RLSdjJ+VKFc_+F+Jm3Ou@mail.gmail.com proposed patch]
** rhaas says: thom brown points out that [http://archives.postgresql.org/message-id/AANLkTi==NemsL7Vo=YfsPc8DdZActYF7ZwCFVO-0Nu21@mail.gmail.com I forgot USER MAPPINGs]
** [http://archives.postgresql.org/message-id/7102.1302029271@sss.pgh.pa.us fixing user mappings opens unduly large can of worms]
* [http://archives.postgresql.org/pgsql-bugs/2011-03/msg00352.php SSI: assertion failure on marking conflict-in due to race condition]
** existing patch to recheck after trading shared LW locks for exclusive should fix
* [http://archives.postgresql.org/message-id/29987.1301930239@sss.pgh.pa.us keywords table needs updating]
* [http://archives.postgresql.org/pgsql-hackers/2011-03/msg01170.php SSI: clumsy error handling results in generic message rather than more specific message with hint]
* [http://archives.postgresql.org/pgsql-hackers/2011-04/msg00157.php SSI: disable optimization when in subtransaction]
* [http://archives.postgresql.org/message-id/4D9DD97B020000250003C53C@gw.wicourts.gov SSI: LOG message about SLRU wrap-around]
* [http://archives.postgresql.org/message-id/BANLkTi=W8OrvqLHS+suU8R2b_rhFaqeEaw@mail.gmail.com sync rep and fast shutdown]
** rhaas says: no easy resolution, i guess we'll leave this alone for now?
* [http://archives.postgresql.org/pgsql-hackers/2011-03/msg01913.php SSI: three different HTABs contend for shared memory in a free-for-all]
** Heikki proposes "We'll need to teach dynahash not to allocate any more entries after the preallocation. A new HASH_NO_GROW flag to hash_create() seems like a suitable interface."
** [http://archives.postgresql.org/message-id/BANLkTimVuicyZG4j3F427BgfA2iYP8Od_Q@mail.gmail.com Alternatively, we could just use an initial allocation which matches the maximum entries, to ensure that all HTABs can allocate ''at least'' the configured maximum.] (There's an existing patch for that.)
** I committed a patch to add the new flag to hash_create() - Heikki
* [http://archives.postgresql.org/pgsql-hackers/2011-04/msg00083.php SSI: failure to clean up some SLRU-summarized locks]
** existing patch to properly set commitSeqNo on the offending locks should fix
* {{MessageLink|20110410103636.GC10697@tornado.leadboat.com|ALTER TABLE ADD COLUMN not creating TOAST tables for inheritance children}}
* {{MessageLink|20110410015728.GA10162@tornado.leadboat.com|typed table DDL loose ends}}
* [http://archives.postgresql.org/message-id/29173.1301114203@sss.pgh.pa.us assorted collation issues]
** [http://archives.postgresql.org/message-id/21742.1303137667@sss.pgh.pa.us almost there...]
* [http://archives.postgresql.org/pgsql-hackers/2011-04/msg00334.php pl/python traceback fix]
* [http://archives.postgresql.org/message-id/BANLkTinz2do3EqWO9RH66bY2FBOmL=uSoA@mail.gmail.com gin indexes don't get used unless you vacuum]
* [http://archives.postgresql.org/message-id/4DB015B3020000250003CB51@gw.wicourts.gov SSI: High-contention UPDATE load on RAM disk database slows non-serializable transactions by a fraction of a percent]
** [http://archives.postgresql.org/message-id/20110422220734.GG57793@csail.mit.edu Or speeds it up by a fraction of a percent. Either way it's too close to tease out from the noise easily.]
** [http://archives.postgresql.org/message-id/20110425033308.GJ57793@csail.mit.edu As insurance against any performance hit in extreme high contention loads, it might be worthwhile to do a quick return (without taking any locks) if no serializable transactions are active.]
* [http://archives.postgresql.org/message-id/201104201411.p3KEBOfA009414@wwwmaster.postgresql.org SSI: UPDATE setting a TOASTed value is broken, regardless of transaction isolation level]
** [http://archives.postgresql.org/message-id/4DAEF7E6.7080107@enterprisedb.com proposed patch]
* [http://archives.postgresql.org/message-id/BANLkTim6a_rfZ+UPPATTXap9Ed7-X7BzoA@mail.gmail.com foreign table permissions issues]
* [http://archives.postgresql.org/pgsql-bugs/2011-04/msg00141.php CREATE TABLE IF NOT EXISTS doesn't work]
* {{MessageLink|201103111328.p2BDSFd10499@momjian.us|Typed-tables patch broke pg_upgrade}}
** {{MessageLink|20110418235041.GB2769@tornado.leadboat.com|proposed patch (tt2v2-binary-upgrade.patch)}}

[[Category:PostgreSQL 9.1]]

GSoC 2011

2011-03-14T16:09:55Z

Dim: /* Project Ideas */

== What is GSoC? ==

Google Summer of Code (GSoC) is a global program that offers student developers stipends to write code for various open source software projects. We have worked with several open source, free software, and technology-related groups to identify and fund several projects over a three month period. Since its inception in 2005, the program has brought together over 4,500 students and more than more than 4,000 mentors & co-mentors from over 85 countries worldwide, all for the love of code. Through Google Summer of Code, accepted student applicants are paired with a mentor or mentors from the participating projects, thus gaining exposure to real-world software development scenarios and the opportunity for employment in areas related to their academic pursuits. In turn, the participating projects are able to more easily identify and bring in new developers. Best of all, more source code is created and released for the use and benefit of all.

PostgreSQL has an official summer of code page: http://www.postgresql.org/developer/summerofcode.html

We also have a Ukraine translation: http://webhostinggeeks.com/science/project-postgresql-ua

== Project Ideas ==

* Add support for multi-line GUCs (configuration variables)
* Add ability to control CSV log fields and order and allow CSV log format changes on SIGHUP
* Eliminate 1GB memory allocation limit for individual queries (MaxAllocSize and friends)
* Build SELinux demonstration for addressing PCI compliance
* [[Bulk_insert_for_GiST_GSoC_2011|Bulk insert for GiST]]
* Improve the auto-configuration script (Python or Perl)
* Write Foreign Data Wrappers for several external data sources (ODBC, SQL Server, Oracle, MySQL, CouchDB, Redis, etc.)
* Integrate SQL/MED and PL/Proxy
* Refactor the NewSysViews project to integrate it with information_schema for inclusion in core PostgreSQL.
* Create autotuning utilities for automated interaction with system information and/or logs
* Help finish the JSON data type
* PL/Erlang
* Please add ideas here!

More ideas:

=== Core Source Code ===

* TODO Items: A number of the items on our TODO list have been marked as good projects for beginners who are new to the PostgreSQL code. Items on this list have the advantage of already having general community agreement that the feature is desireable. These items should also have some general discussion available in the mailing list archives to help get you started. You can find these items on the TODO list, they will be marked with an [E].
* DDL Functions: Create a SQL-callable function capable of generating DDL scripts for objects within the database.
* EXPLAIN Enhancements: Add information to EXPLAIN documenting I/O activity, discarded plans, costing detail, memory usage and more.
* Text-Array Indexing: Add support for indexing text or multi-typed array data, capable of supporting indexed queries, similar to intarray.

=== Infrastructure ===

* Enhance Buildfarm to test external packages, patches, or performance: The PostgreSQL project maintains a public buildfarm that continuously communicates with several machines that checkout and build the PostgreSQL source on a regular basis. The idea behind this project is to extend the current buildfarm code to allow it download external modules and report back on their build status, to download unapplied patches and test them, or to run performance tests.
* Enhance "Performance-Farm" framework for continuous performance regression testing: Similar to the buildfarm, the idea behind this project is to create a public infrastructure that continuously communicates with several machines that checkout and build the PostgreSQL source on a regular basis, running and reporting on agreed performance benchmarks.
* Finish the AOX integration for archives (see http://archives.beccati.org/ which has been done in PHP, has to be redone in django to integrate into the architecture)

=== External Applications ===

* pgAdmin III / phpPgAdmin Enhancements: PostgreSQL supports a number of popular GUI Tools that are not distributed with the core project. Projects like these often have their own TODO lists and compatibility issues with the core PotgreSQL that need development. We welcome ideas for these projects as well.
* pgAdmin III : add graph support to the server status window, add more ways to view an explain analyze in the query tool (and handle xml explain)
* Procedural Language Improvements: PostgreSQL provides support for more than a dozen different procedural languages, however the level of support varies depending on the language implementation. Enhancing support of these procedural languages might include fixing build issues, adding SPI support, adding trigger support, adding support for IN/OUT parameters and more.
* Replication and Clustering: PostgreSQL provides a wide range of replication solutions for varying types of replication needs. Many of these projects need assistance with building against different versions of PostgreSQL, installation and setup, administrative tools, and general bugfixing.
* Teaching & Learning Tools: External tools to help in teaching the internals of PostgreSQL such as enhanced visual EXPLAIN, graphical models of the query engine, educational guides to the code, and interactive tools to demonstrate various query types.
* PostGIS & GEOS: Add performance and feature enhancements to PostgreSQL's geographic data support.

Additional projects may be found by browsing the [http://pgfoundry.org PostgreSQL Development Projects] website.

== Projects ==

The GSoC projects for 2011 are:

* TBD :)

We are also working on a [http://twitter.com/selenamarie/gsoc2011 twitter list] for those involved this year. If you are missing, please send us your info:

== Key Info ==
* March 7th - 11th, Mentoring Application Due
* March 29th - April 9th, Student Applications Due

* [http://google-opensource.blogspot.com/2011/02/mentoring-organization-applications-now.html Announcement for GSoC 2011]
* [http://www.google-melange.com// GSOC 2011 Home page ]
* [http://www.google-melange.com/document/show/gsoc_program/google/gsoc2011/faqs GSOC 2011 FAQ]
* [http://www.postgresql.org/developer/summerofcode Postgres GSOC Page, Needs Updating!]
* [https://twitter.com/selenamarie/gsoc2011 Twitter stream of mentors and students]

== Project Admins ==
* Selena Deckelmann - Co-admin, Mentor Summit attendee.
* Josh Berkus - Co-admin, Mentor Summit attendee.
* Robert Treat - Past mentor 2x, co-admin, Mentor Summit attendee.

== 2011 Mentors ==
* Dave Page - Former mentor - pgAdmin, Windows, Packaging, Infrastructure
* Heikki Linnakangas - Postgres Committer
* Magnus Hagander - Postgres Committer, Windows, pgAdmin
* Guillaume Lelarge - pgAdmin
* Jehan-Guillaume de Rorthais - phpPgAdmin
* Joe Abbate - Python-related, catalog-related projects
* David E. Wheeler - Perl-related, extensions, PGXN
* Mark Wong - benchmarking, monitoring, performance
* Tatsuo Ishii - Postgres Committer, pgpool-II
* Stephen Frost - Postgres contributor
* Devrim Gündüz - Administration related software (dashboard)

=== additional reviewers ===
* Josh Berkus - auto-configuration, performance testing
* Selena Deckelmann - configuration, testing
* Andreas Scherbaum - performance, configuration, testing

== Past Success ==
* Florian - read-only on snapshots, no advancement of xid on select statements
* Ivan - Full-Text Support in phpPgAdmin
* Mickael Deloison - pgScript engine for pgAdmin
* Luis Alberto Ochoa Paz - Graphical query builder for pgAdmin
* Leonardo Sapiras - browsing data through FK in phpPgAdmin + some additionnal stuffs

== GOALS: ==
* usable code
** useful/novel ideas
** research projects
* longer term contributors

== TODOs: ==

* Kick-off Meeting for Community Members
* Update GSOC page
* Advertising?
* Blog that we're participating and seeking students
* Round of private emails to people who have participated in the past: Heikki, Simon, Mark
** request interest, and then follow up in asking about possible topics for students
* Mentor recruitment and then email to -hackers
** do this much later when we have some proposals in?

* Recruitment -- no organized group effort?
** -announce, -general, -hackers
** user group lists
** phppgadmin/pgadmin
** berkeley
** Univ. of Maryland -- contact them?

* Identify the commitfest that the code will be submitted to

== Expectations ==

* Stuff to keep students together:
** Regular blogging from students
** weekly group IRC checkin? -- two checkin times maybe?

* Have students communicate on -hackers where appropriate (didn't really work?)
** Or other relevant -devel lists

* Mailing list
** pgsql-students (?) vs. -hackers (?) maybe up to mentor?
** mentors mailing list -admin mailing list, berkus said?
** students mailing list via gsoc

[[Category:Google Summer of Code]]

File:PGDay2010-Extensions.pdf

2010-12-08T20:53:16Z

Dim: PostgreSQL extensibility is remarkable but incomplete. It lacks dump and restore support. What that means is that once an extension is installed into your database, PostgreSQL currently has no idea of what SQL objects belongs to the extension rather itself, so the dump will contain the instructions to install the extension. That's only practical if you want to restore your dump targeting the very same extension's version, but when upgrading systems that's seldom what happens. This talk will detail how to fix this problem and more, explaining you how to benefit from the extensions capabilities for your own work within the database.

PostgreSQL extensibility is remarkable but incomplete. It lacks dump and
restore support. What that means is that once an extension is installed
into your database, PostgreSQL currently has no idea of what SQL objects
belongs to the extension rather itself, so the dump will contain the
instructions to install the extension. That's only practical if you want
to restore your dump targeting the very same extension's version, but
when upgrading systems that's seldom what happens. This talk will detail
how to fix this problem and more, explaining you how to benefit from the
extensions capabilities for your own work within the database.

PGDay Europe 2010 Talks

2010-12-08T20:52:03Z

Dim: PostgreSQL Extension's Development

PGDay.EU 2010 was held in Stuttgart, Germany on December 6th - 8th.

== Monday, December 6, 2010 ==

=== Berlin 1+2 ===

==== 9:45 - 10:45 [[Keynote: Back To The Future of Open Source (Simon Phipps)]]====
==== 11:10 - 12:00 [[Play chess against PostgreSQL (and get beaten) (Gianni Ciolli)]] ====
==== 12:10 - 13:00 [[Rapid Upgrades With Pg_Upgrade (Bruce Momjian)]] ====
==== 14:00 - 14:50 [[Media:Postgis beyond courtin caveayland pgdayEU2010.pdf|PostGIS 1.5 and beyond: a technical perspective (Mark Cave-Ayland, Olivier Courtin)]] ====
==== 15:20 - 16:10 [[Managing PostgreSQL Replication (Simon Riggs)]] ====

=== Berlin 3 ===

==== 11:10 - 12:00 [[Media:Postgis_das_wo_in_der_datenbank_pgday_eu_2010.pdf|PostGIS - Das Wo? in der Datenbank (Stefan Keller, Andreas Neumann)]]====

==== 12:10 - 13:00 [[Media:Pgsql_detailhandel.pdf|PostgreSQL als Basis für Detailhandels-Anwendungen (Marc Balmer)]]====

==== 14:00 - 14:50 [[Media:Pg-community.pdf|Die PostgreSQL Community (Bernd Helmle)]]====

==== 16:20 - 17:10 [[Media:Pgday-2010-ose-print.pdf|Open Source Entscheidungen]] ([[User:Ads|Andreas Scherbaum]]) ====

=== Glasgow ===

==== 15:20 - 16:10 [[Media:Concurrency.pdf|Concurrency & PostgreSQL (Marko Tiikkaja)]]====

==== 16:20 - 17:10 [[Media:PostgreSQL_Clustering_with_Red_Hat_Cluster_Suite_06122010.odp|PostgreSQL Clustering with RedHat Cluster Suite (Devrim Gündüz)]]====

=== Tara ===

==== 14:00 - 14:50 [[Media:PGDay_EU_2010.pdf|Servermonitoring Postgres mit RHQ (Heiko Rupp)]]====

==== 15:20 - 16:10 [[Media:Psycopg-2010-stuttgart.pdf|Advanced PostgreSQL Access from Python with Psycopg]] ([[User:dvarrazzo|Daniele Varrazzo]]) ====

== Tuesday, December 7, 2010 ==

=== Berlin 1+2 ===
==== 11:35 - 12:20 [[Media:Web-arch-pgday2010.pdf|How PostgreSQL 9 Makes Web Architecture Sweeter]] (Jonathan S. Katz) ====
==== 14:10 - 15:00 [[Media:PGDay2010-Statistics.pdf|Statistics in PostgreSQL]] (Heikki Linnakangas) ====

=== Berlin 3 ===

==== 09:00 - 09:50 [[Media:PGDay2010_-_ILMSraster.pdf|Verwendung von PostgreSQL und GRASS-GIS in einer "Virtual Appliance" für datenbankbasierte Rasterhaltung in ILMS]] ([[User:c3schs|Christian Schwartze]]) ====

==== 10:25 - 11:10 [[Media:Pgday_eu_2010_benchmarking.pdf|Benchmarking und Performancetesting von und mit PostgreSQL]] ([[User:mastermind|Stefan Kaltenbrunner]]) ====

==== 11:35 - 12:20 [[Media:Pgday-2010-mysql-to-pg-print.pdf|Datenbanken von MySQL zu PostgreSQL portieren]] ([[User:Ads|Andreas Scherbaum]]) ====

==== 14:10 - 15:00 [[Media:Postgresql_query_cache_memcached_bjoern_haeuser.pdf|Postgresql Query Cache mit Memcache]] ====

=== Glasgow ===
==== 10:25 - 11:10 [http://www.slideshare.net/Tim.Bunce/pl-perl90-201012 PL/Perl new features in 9.0] ([[User:timbunce|Tim Bunce]]) ====
==== 13:10 - 14:00 [[Media:Postgis vincent picavet pgdayEU2010.pdf|PostGIS, a PostgreSQL plugin for spatial data]] ([[User:Vincentp|Vincent Picavet]]) ====
=== Tara ===

==== 10:25 - 11:10 [http://www.dalibo.org/_media/pgpool.pdf Why your PostgreSQL 9.0 cluster needs pgpool-II] ([[User:jpargudo|Jean-Paul Argudo]]) ====
==== 14:10 - 15:00 [[Media:PGDay2010-Extensions.pdf|PostgreSQL extension's development]] (Dimitri Fontaine) ====