Per -hackers, we need to have two things for each of our desired features:

(1) a clear specification on how the feature should work, and how it
should be implemented.

(2) specific people to work on the feature.

As such, I'm starting with a feature which isn't the highest priority as
far as votes are concerned, but is *my* highest priority as far as stuff
I'd personally like to use.

Now, I have my idea on how I'd like the Data Queue / Modification
Triggers to work, but I'm not the one who proposed those two closely
related features. Can the original proposers speak up on what kind of
spec you had in mind?

--Josh Berkus

Search Discussions

  • Josh Berkus at Mar 3, 2010 at 5:42 pm

    Now, I have my idea on how I'd like the Data Queue / Modification
    Triggers to work, but I'm not the one who proposed those two closely
    related features. Can the original proposers speak up on what kind of
    spec you had in mind?
    Since nobody has spoken up about this, I'll spec it myself ...
    particularly since I care strongly about it for heterogenous replication
    (that is, replication to non-PostgreSQL systems).

    As far as I can see, things would be of general utility:

    (1) The ability to send asynchronous (or synchronous?) notifications, on
    a per-row basis, whenever data is modified *only after commit*. This
    has been generally described as "on-commit triggers", but could actually
    take a variety of forms.

    (2) A generic yet compact portable serialization format for a row of
    PostgreSQL data, just as protobuf, or something internal.

    (3) A method of marking DDL changes in the data modification stream.

    Of the three above, #2 seems the most optional, and possibly completely
    counter-productive. Even if we decided that a general portable data
    format made sense, we could retrofit it later. So dropping it from the
    spec.

    The question is, can we satisfy (1) with the new LISTEN/NOTIFY, or is it
    inadequate? I'll also point out that currently DEFFERABLE
    CONSTRAINT/TRIGGERS can be perverted to work like an after-commit trigger.

    For (3), it kind of depends on another item on the list, the DDL triggers.

    Discussion?

    --Josh Berkus
  • Greg Sabino Mullane at Mar 3, 2010 at 7:28 pm

    On Wed, Mar 03, 2010 at 09:42:22AM -0800, Josh Berkus wrote:
    (1) The ability to send asynchronous (or synchronous?) notifications, on
    a per-row basis, whenever data is modified *only after commit*. This
    has been generally described as "on-commit triggers", but could actually
    take a variety of forms.
    I'm not sure I like the idea of this. Could be potentially dangerous, as
    listen/notify is not treated as a "reliable" process. What's wrong with
    the current method, namely having a row trigger update an internal
    table, and then a statement level trigger firing off a notify?
    (2) A generic yet compact portable serialization format for a row of
    PostgreSQL data, just as protobuf, or something internal.
    Jan has been tinkering with this for some time (granted, in a slightly
    different context, but basically something faster and lower level
    than COPY, that could perhaps be fed something external).
    (3) A method of marking DDL changes in the data modification stream.
    Hmm..can you expand on what you have in mind here? Something more than
    just treating the DDL as another item in the (txn ordered) queue?

    --
    Greg Sabino Mullane greg@endpoint.com
    End Point Corporation
    PGP Key: 0x14964AC8
  • Josh Berkus at Mar 3, 2010 at 7:53 pm
    Greg,
    (1) The ability to send asynchronous (or synchronous?) notifications, on
    a per-row basis, whenever data is modified *only after commit*. This
    has been generally described as "on-commit triggers", but could actually
    take a variety of forms.
    I'm not sure I like the idea of this. Could be potentially dangerous, as
    listen/notify is not treated as a "reliable" process. What's wrong with
    the current method, namely having a row trigger update an internal
    table, and then a statement level trigger firing off a notify?
    Well, the main problem with that is that it doubles the number of writes
    you have to do ... or more. So it's a major efficiency issue.

    This isn't as much of a concern for a system like Slony or Londiste
    where the replication queue is a table in the database. But if you
    were, say, replicating through ApacheMQ? Or replicating cached data to
    Redis? Then the whole queue-table, NOTIFY, poll structure is needless
    overhead.
    (3) A method of marking DDL changes in the data modification stream.
    Hmm..can you expand on what you have in mind here? Something more than
    just treating the DDL as another item in the (txn ordered) queue?
    Yeah, that would be one way to handle it. Alternately, you could have
    the ability to mark rows with a DDL "version".

    --Josh Berkus
  • Hannu Krosing at Mar 3, 2010 at 9:02 pm

    On Wed, 2010-03-03 at 11:52 -0800, Josh Berkus wrote:
    Greg,
    (1) The ability to send asynchronous (or synchronous?) notifications, on
    a per-row basis, whenever data is modified *only after commit*. This
    has been generally described as "on-commit triggers", but could actually
    take a variety of forms.
    I'm not sure I like the idea of this. Could be potentially dangerous, as
    listen/notify is not treated as a "reliable" process. What's wrong with
    the current method, namely having a row trigger update an internal
    table, and then a statement level trigger firing off a notify?
    Well, the main problem with that is that it doubles the number of writes
    you have to do ... or more. So it's a major efficiency issue.

    This isn't as much of a concern for a system like Slony or Londiste
    where the replication queue is a table in the database.
    Yes. For Londiste, in addition to WAL writes, which write bigger chunks
    of data, but need the same number of seeks and syncs, only deferred
    writes to heap and a single index would be added and even those may
    never be actually written to disk if replication is fast enough and the
    event tables are rotated faster than background writer and checkpoints
    try to write them down.
    But if you
    were, say, replicating through ApacheMQ? Or replicating cached data to
    Redis? Then the whole queue-table, NOTIFY, poll structure is needless
    overhead.
    I't may seem easy to replace a database table with "something else" for
    collecting the changes which have happened during the transaction, but
    you have to answer the following questions:

    1) do I need persistence, what about 2PC ?

    2) does the "something else" work well for all situations an event table
    would work (say, for example, a load of 500GB of data in one
    transaction)

    3) what would I gain in return for all the work needed to implement the
    "something else" ?
    (3) A method of marking DDL changes in the data modification stream.
    Yes, DDL triggers or somesuch would be highly desirable.
    Hmm..can you expand on what you have in mind here? Something more than
    just treating the DDL as another item in the (txn ordered) queue?
    Yeah, that would be one way to handle it. Alternately, you could have
    the ability to mark rows with a DDL "version".
    But the actual DDL would still need to be transferred, no ?

    --
    Hannu Krosing http://www.2ndQuadrant.com
    PostgreSQL Scalability and Availability
    Services, Consulting and Training
  • Josh Berkus at Mar 3, 2010 at 9:44 pm

    I't may seem easy to replace a database table with "something else" for
    collecting the changes which have happened during the transaction, but
    you have to answer the following questions:

    1) do I need persistence, what about 2PC ?

    2) does the "something else" work well for all situations an event table
    would work (say, for example, a load of 500GB of data in one
    transaction)
    Those are good questions, and a generic system would need to work for
    all three of those requirements.
    3) what would I gain in return for all the work needed to implement the
    "something else" ?
    Speed. In my test case, which was replicating view snapshots between
    PostgreSQL and Redis, the difference between using an event table and
    perverting the constrainttriggers to do an after-insert trigger directly
    to redis was a speed difference of around 400%, not counting vacuum
    overhead.
    (3) A method of marking DDL changes in the data modification stream.
    Yes, DDL triggers or somesuch would be highly desirable.
    Hmm..can you expand on what you have in mind here? Something more than
    just treating the DDL as another item in the (txn ordered) queue?
    Yeah, that would be one way to handle it. Alternately, you could have
    the ability to mark rows with a DDL "version".
    But the actual DDL would still need to be transferred, no ?
    Yes. It may be that having a ddl change simply inserted into the
    replication stream is the way to go. Alternatively, DDL versioning
    makes a certain amount of sense except that it's pretty hard to make
    generic, and would require additional catalog tables.

    --Josh Berkus
  • Hannu Krosing at Mar 3, 2010 at 10:12 pm

    On Wed, 2010-03-03 at 13:43 -0800, Josh Berkus wrote:
    I't may seem easy to replace a database table with "something else" for
    collecting the changes which have happened during the transaction, but
    you have to answer the following questions:

    1) do I need persistence, what about 2PC ?

    2) does the "something else" work well for all situations an event table
    would work (say, for example, a load of 500GB of data in one
    transaction)
    Those are good questions, and a generic system would need to work for
    all three of those requirements.
    3) what would I gain in return for all the work needed to implement the
    "something else" ?
    Speed. In my test case, which was replicating view snapshots between
    PostgreSQL and Redis, the difference between using an event table and
    perverting the constrainttriggers to do an after-insert trigger directly
    to redis was a speed difference of around 400%, not counting vacuum
    overhead.
    What do you mean by "speed difference" ?

    Lag ?

    Or the DMS speed the system could keep up with ?

    Or something else ?
    (3) A method of marking DDL changes in the data modification stream.
    Yes, DDL triggers or somesuch would be highly desirable.
    Hmm..can you expand on what you have in mind here? Something more than
    just treating the DDL as another item in the (txn ordered) queue?
    Yeah, that would be one way to handle it. Alternately, you could have
    the ability to mark rows with a DDL "version".
    But the actual DDL would still need to be transferred, no ?
    Yes. It may be that having a ddl change simply inserted into the
    replication stream is the way to go. Alternatively, DDL versioning
    makes a certain amount of sense except that it's pretty hard to make
    generic, and would require additional catalog tables.
    But what would DDL versioning _gain_ ? I assume that you just have to
    stop and wait for the new version of DDL to arrive, once your DML stream
    switches to new DDL version ?

    --
    Hannu Krosing http://www.2ndQuadrant.com
    PostgreSQL Scalability and Availability
    Services, Consulting and Training
  • Takahiro Itagaki at Mar 4, 2010 at 3:01 am

    Josh Berkus wrote:

    (1) The ability to send asynchronous (or synchronous?) notifications, on
    a per-row basis, whenever data is modified *only after commit*. This
    has been generally described as "on-commit triggers", but could actually
    take a variety of forms.
    The question is, can we satisfy (1) with the new LISTEN/NOTIFY, or is it
    inadequate? I'll also point out that currently DEFFERABLE
    CONSTRAINT/TRIGGERS can be perverted to work like an after-commit trigger.
    I think Modification Trigger is also used by not only clustering middlewares
    but also materialized view and VACUUM FULL CONCURRENTLY, where we can replay
    the modifications using the queued items. So, we could separate the trigger
    parts into (1.1) store modifications into persistent storage and (1.2) send
    the items to another server. IMHO, "on-commit" part is not so important.

    We can implement 1.1 with existing triggers like Slony-I and PgQ,
    but there is a problem in trigger-based approach that the trigger
    needs to be called at the last of a trigger chain. PostgreSQL calls
    the trigger chain in alphabetical order, but it we cannot forbid users
    to add triggers with bottom names (ex. 'zzz_trigger').

    We can develop 1.2 on the top of 1.1. If we try to improve performance
    of the queue storage, it would be implemented with global temp tables
    for which we don't write any WALs.

    Regards,
    ---
    Takahiro Itagaki
    NTT Open Source Software Center
  • Josh Berkus at Mar 10, 2010 at 5:23 am
    All,

    I wrote this up on http://wiki.postgresql.org/wiki/ModificationTriggerGDQ

    Please expand.

    --Josh Berkus

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-cluster-hackers @
categoriespostgresql
postedFeb 5, '10 at 9:50p
activeMar 10, '10 at 5:23a
posts9
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase