Re: [HACKERS] Global Sequences

[Simon Riggs]

On 16 October 2012 03:03, Christopher Browne  wrote:

> There's a necessary trade-off; you can either have it globally
> *strongly* ordered, and, if so, you'll have to pay a hefty
> coordination price, or you can have the cheaper answer of a weakly
> ordered sequence.  The latter leaves me feeling rather "meh."

Oracle allows you to define whether you want ORDER or not for a
sequence when used in clustered mode.

Requesting a sequence to be strongly ordered across a generic
distributed system is very much like asking performance=none and
high_availability=off, which is why I didn't suggest it. So you're
right about the "hefty coordination price" but our conclusions differ
because of our understanding of that price.

I don't think it makes sense to spend the time implementing that option.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Peter Eisentraut]

On Mon, 2012-10-15 at 11:14 -0700, Josh Berkus wrote:
> I'd be in favor of a warning on create index.

Only if you can turn it off, please.

But I don't think a warning is appropriate if the statement does exactly
what the user wanted.  The place to point out shortcomings of the
implementation is in the documentation.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Stephen Frost]

* Christopher Browne (cbbro...@gmail.com) wrote:
> There's a necessary trade-off; you can either have it globally
> *strongly* ordered, and, if so, you'll have to pay a hefty
> coordination price, or you can have the cheaper answer of a weakly
> ordered sequence.  The latter leaves me feeling rather "meh."

If all the systems involved are local to the system giving out the
sequences, ala PG-XC's GTM, I don't believe it's really all *that*
expensive..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Global Sequences

[Christopher Browne]

On Mon, Oct 15, 2012 at 5:33 PM, Simon Riggs  wrote:
> Sequences, as defined by SQL Standard, provide a series of unique
> values. The current implementation on PostgreSQL isolates the
> generation mechanism to only a single node, as is common on many
> RDBMS.

I remember constructing at least the thought process surrounding your
"option #3" as the one thing thing I arrived at that seemed as though
it might be valuable for the many-years-ago Slony-II summit.

The notion of having each node give out sections of a sequence seems
pretty viable; as a hugely loose analogy, DHCP servers  take a
somewhat similar approach in assigning IP addresses in ranges shared
across those servers.

At the time, back in, I think, 2005, there was some agreement that it
was a viable idea to give out chunks of sequence range; it wasn't one
of the tough problems warranting Heavy Thought, so there wasn't any
concentration on it, and that pretty all went by the wayside.

Today, I am somewhat skeptical that there's much value to the
exercise.  It isn't difficult to come up with pretty unique values,
between constructing something with a node ID prefix or such, or using
a DCE UUID that is very likely to be globally unique.

The reason to want a "global sequence" is that it's supposed to give
out values in pretty much a sequential order.  But a "shared global
sequence" will have aspects of that that are decidedly *not* in
sequential order.  If it's partially *un*ordered, I suspect this
undermines the value of it.

There's a necessary trade-off; you can either have it globally
*strongly* ordered, and, if so, you'll have to pay a hefty
coordination price, or you can have the cheaper answer of a weakly
ordered sequence.  The latter leaves me feeling rather "meh."
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Michael Paquier]

On Tue, Oct 16, 2012 at 10:30 AM, Josh Berkus  wrote:

> Stephen,
>
> > AIUI, PG-XC and PG-R need an order, so they both use an independent
> > system (eg, the PG-XC GTM) to provide that ordering.
>
> You're thinking of XIDs.  This is a proposal for user-defined sequences.
>
XC also provides global sequence values with the GTM.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [HACKERS] [WIP] pg_ping utility

[Phil Sorber]

On Mon, Oct 15, 2012 at 9:18 PM, Tom Lane  wrote:
> Phil Sorber  writes:
>> I would also like it to have a regression test
>> which none of those seem to have.
>
> [ shrug... ]  There is nothing in the current regression infrastructure
> that would work for this, so that desire is pie-in-the-sky regardless of
> where you put it in the source tree.  Also, PQping itself is exercised
> in every buildfarm run as part of "pg_ctl start", so I don't feel a real
> strong need to test pg_ping separately.

My plan was to borrow heavily from the pg_upgrade test. I want to
verify the exit status based on known database state as presumably
people would be using this for monitoring/load balancing, etc. Hoping
to prevent silly breakage like the help output from returning an
'Accepting Connections' exit status.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Stephen Frost]

* Josh Berkus (j...@agliodbs.com) wrote:
> > AIUI, PG-XC and PG-R need an order, so they both use an independent
> > system (eg, the PG-XC GTM) to provide that ordering.
> 
> You're thinking of XIDs.  This is a proposal for user-defined sequences.

Right, I got that it's a proposal for user sequences.  I stand by my
comments regarding that proposal- if you're going to have all the
complexity of having to talk to some other system to figure out what
value(s) you can use, you might as well get an ordering with it.  If
that's not necessary, then doing pre-allocation is probably sufficient
and would provide a direct way, without having to ask any other system,
to figure out which system a given value is on.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Adding comments for system table/column names

[Bruce Momjian]

On Sat, Oct 13, 2012 at 09:14:55PM +0200, Magnus Hagander wrote:
> On Sat, Oct 13, 2012 at 9:12 PM, Bruce Momjian  wrote:
> > On Sat, Oct 13, 2012 at 09:10:05PM +0200, Magnus Hagander wrote:
> >> >> > I think the idea of having the short descriptions in SQL and longer 
> >> >> > ones
> >> >> > in SGML is not maintainable.  One idea would be to clip the SQL
> >> >> > description to be no longer than a specified number of characters, 
> >> >> > with
> >> >> > proper word break detection.
> >> >>
> >> >> I prefer overlong entries to machine-truncated ones.  Seeing "Does the 
> >> >> access
> >> >> method support ordered" for both pg_am.amcanorder and 
> >> >> pg_am.amcanorderbyop
> >> >> thanks to the choice of truncation point does not seem like a win.
> >> >>
> >> >> We could store a short version in the SGML markup, solely for this 
> >> >> process to
> >> >> extract.  In its absence, use the documentation-exposed text. The 
> >> >> extractor
> >> >> could emit a warning when it uses a string longer than N characters, 
> >> >> serving
> >> >> as a hint to add short-version markup for some column.  If that's too 
> >> >> hard,
> >> >> though, I'd still prefer overlong entries to nothing or to truncated 
> >> >> entries.
> >> >
> >> > I think the simplest solution would be to place SGML comment markers
> >> > around text we want to extract from overly-long SGML descriptions.
> >> > Descriptions without SGML comments would be extracted unchanged.
> >>
> >> Not sure how convenient that is, but it would certainly work. And it
> >> would be a lot better than cutting off at word or character limits or
> >> anything like that.
> >
> > Well, I figure we have to do something, because people would like those
> > descriptions, and recording them in two places is too much overhead.
> 
> Agreed, this is definitely better than the other options there. And
> the best suggetsion so far.

OK, I will work on this in the coming months for 9.3.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Josh Berkus]

Stephen,

> AIUI, PG-XC and PG-R need an order, so they both use an independent
> system (eg, the PG-XC GTM) to provide that ordering.

You're thinking of XIDs.  This is a proposal for user-defined sequences.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Tom Lane]

Phil Sorber  writes:
> On Mon, Oct 15, 2012 at 7:12 PM, Tom Lane  wrote:
>> Yeah, I know a whole new executable is kind of a pain, and the amount of
>> infrastructure and added maintenance seems a bit high compared to what
>> this does.  But a lot of the programs in src/bin/scripts are not much
>> bigger.  (In fact that might be the best place for this.)

> I considered src/bin/scripts but all those are for maintenance tasks
> on the DB. createdb/vacuumdb/reindexdb etc. It doesn't need any of the
> bits in common.h/common.c, nor does it need some of the includes that
> the build process has.

Well, we classify all those programs as client-side tools in the
documentation, so I don't see that pg_ping doesn't belong there.

The alternative is to give it its very own subdirectory under src/bin/;
which increases the infrastructure burden *significantly* (eg, now it
needs its own NLS message catalog) for not a lot of value IMO.

> I would also like it to have a regression test
> which none of those seem to have.

[ shrug... ]  There is nothing in the current regression infrastructure
that would work for this, so that desire is pie-in-the-sky regardless of
where you put it in the source tree.  Also, PQping itself is exercised
in every buildfarm run as part of "pg_ctl start", so I don't feel a real
strong need to test pg_ping separately.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Tom Lane]

"David Johnston"  writes:
>> Yeah, I know a whole new executable is kind of a pain, and the amount of
>> infrastructure and added maintenance seems a bit high compared to what
>> this does.  But a lot of the programs in src/bin/scripts are not much
>> bigger. (In fact that might be the best place for this.)

> This seems to be begging for a canonical "pg_monitor" command where
> "pg_ping" would be one sub-command.  A bit much for a single command but it
> would provide a frame onto which additional user interfaces could be hung -
> though I am lacking for concrete examples at the moment.

Meh.  If we had near-term plans for more such subcommands, that might
make sense.  But I think all that's really wanted here is a command-line
wrapper around the libpq PQping() functionality.  People who are trying
to build more complex monitoring tools are likely to be using PQping()
directly anyway, rather than invoking a command-line tool.

> With the recent focus on pg_stat_statements and the current WIP on
> "pg_lwlocks" having an official UI for accessing much of this kind data has
> merit.

None of that stuff is accessible without opening up an actual database
connection, and IMO the whole point of PQping is that it *doesn't* open
a connection and thus doesn't get into problems of user authentication.
So I really think it's a different sort of animal that needs a separate
API.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Stephen Frost]

Josh,

* Josh Berkus (j...@agliodbs.com) wrote:
> I'd also love to hear from the PostgresXC folks on whether this solution
> works for them.  Postgres-R too.  If it works for all three of those
> tools, it's liable to work for any potential new tool.

AIUI, PG-XC and PG-R need an order, so they both use an independent
system (eg, the PG-XC GTM) to provide that ordering.

Again, AIUI, Simon's proposal would not guarantee any ordering but
instead would only guarantee non-overlap.  Since the proposal being
pushed appeared to involve all the complexity of dealing with something
like a GTM, by having to have some third system which manages the
allocations, figure out what to do if it isn't available, etc, perhaps
the requirement to provide an ordering should be added on to it and
then the PG-XC GTM simply used for it.

If we're not going to have an ordering requirement then I'm not
convinced that the pre-allocation approach (where you break the space
up into many more blocks than you would ever expect to use and then
double-up those blocks on to the same physical system) is a bad
solution.  It's certainly also been done a number of times, typically
quite successfully.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Steve Singer]

On 12-10-15 04:51 PM, Andres Freund wrote:


Well, as a crosscheck, could you list your requirements?

Do you need anything more than outputting data in a format compatible to whats
stored in sl_log_*? You wouldn't have sl_actionseq, everything else should be
there (Well, you would need to do lookups to get the tableid, but thats not
really much of a problem). The results would be ordered in complete
transactions, in commit order.

I guess the other tables would stay as they are as they contain the "added
value" of slony?

Greetings,


I actually had spent some time a few weeks ago looking over the 
documents and code.  I never did get around to writing a review as 
elegant as Peter's.   I have not seen any red flags that make me thing 
that what your proposing wouldn't be suitable for slony but sometimes 
you don't see details until you start implementing something.


My initial approach to modifying slony to work with this might be 
something like:


* Leave sl_event as is for non SYNC events, slon would still generate 
SYNC events in sl_event
* We would modify the remote_worker thread in slon to instead of 
selecting from sl_event it would get the the next 'committed' 
transaction from your apply cache.   For each ApplyChange record we 
would check to see if it is an insert into sl_event ,if so we would 
trigger our existing event processing logic based on the contents of the 
ev_type column.
* If the change involves a insert/update/delete/truncate to a replicated 
table we would translate that change into SQL and apply it on the 
replica, we would  not commit changes on the replica until we encounter 
a SYNC being added to sl_event for the current origin.
* SQL will be applied in a slightly different order than slony does 
today.  Today if two concurrent transactions are inserting into the same 
replicated table and they commit one after the other there is a good 
chance that the apply order on the replica will also be intermixed 
(assuming both commits were in between two SYNC events). My thinking is 
that we would just replay them one after the other on the replica in 
commit order. (Slony doesn't use commit order because we don't have it, 
not because we don't like it) this would mean we do away with tracking 
the action id.


* If a node is configured as a 'forwarder' not it would store the 
processed output of each ApplyChange record in a table on the replica. 
If a slon is pulling data from a non-orign (ie if remoteWorkerThread_1 
is pulling data from node 2) then it would need to query this table 
instead of calling the functions that process the ApplyCache contents.


* To subscribe a node we would generate a SYNC event on the provider and 
do the copy_set.  We would keep track of that SYNC event.  The remote 
worker would then ignore any data that comes before that SYNC event  
when it starts pulling data from the apply cache.
* DDL events in 2.2+ go into sl_ddl_script (or someting like that) when 
we see INSERT commands to that table we would now to then apply the DDL 
on the node.


* We would need to continue to populate sl_confirm because nowing what 
SYNC events have already been processed by a node is pretty important in 
a MOVE SET or FAILOVER.  It is possible that we might need to still 
track the xip lists of each SYNC for MOVE SET/FAILOVER but I'm not sure 
why/why not.


This is all easier said than implemented


Steve






Andres




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Bruce Momjian]

On Mon, Oct 15, 2012 at 03:51:58PM -0400, Andrew Dunstan wrote:
> 
> On 10/15/2012 03:23 PM, Bruce Momjian wrote:
> >I have trouble seeing how we could implement Postgres as efficiently
> >without C macros, but maybe that is the point --- efficiency is not
> >critical in SQL --- Java and C++ give other options that are "good
> >enough" and less error-prone.
> >
> >
> 
> Er, C++ uses the preprocessor. In fact, C++ was originally created
> as a set of preprocessor macros, IIRC.

I assumed from the emails that macros were discouraged in C++;  I don't
know myself personally.  I certainly would miss macro abilities in C.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Global Sequences

[Daniel Farina]

On Mon, Oct 15, 2012 at 2:33 PM, Simon Riggs  wrote:
> Sequences, as defined by SQL Standard, provide a series of unique
> values. The current implementation on PostgreSQL isolates the
> generation mechanism to only a single node, as is common on many
> RDBMS.
>
> For sharded or replicated systems it forces people to various hackish
> mechanisms in user space for emulating a global or cluster-wide
> sequence.
>
> The solution to this problem is an in-core solution that allows
> coordination between nodes to guarantee unique values.
>
> There are a few options
> 1) Manual separation of the value space, so that N1 has 50% of
> possible values and N2 has 50%. That has problems when we reconfigure
> the cluster, and requires complex manual reallocation of values. So it
> starts good but ends badly.
> 2) Automatic separation of the value space. This could mimic the
> manual operation, so it does everything for you - but thats just
> making a bad idea automatic
> 3) Lazy allocation from the value space. When a node is close to
> running out of values, it requests a new allocation and coordinates
> with all nodes to confirm the new allocation is good.

While useful to some people, it seems like a way to avoid a crazy
amount of complexity whereas most people are fine just using
uncoordinated 128-bit integers.  There are some who want temporal
locality and smaller datums, but couldn't the problem be minimized
somewhat by presuming non-coordinated identifier generation?

It seems like a proper subset of what you propose, so perhaps that's a
nice way to bisect the problem.  I agree with you that an in-database
way to do this -- even if in principle it could be done by clients
most of the time -- would lend a lot more cohesion to the system.

FWIW, I like "3" otherwise -- much like the foibles of most
partitioning schemes and some of the stated design considerations of
segment exclusion, I think absolute rigidity at all times makes it
really hard to gradually move things into a desired alignment
incrementally while the system is online.

-- 
fdr


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Phil Sorber]

On Mon, Oct 15, 2012 at 7:12 PM, Tom Lane  wrote:
> Andres Freund  writes:
>> Why not add a pg_ctl subcommand for that? For me that sounds like a good 
>> place
>> for it...
>
> I think that's a bad fit, because every other pg_ctl subcommand requires
> access to the data directory.  It would be very confusing if this one
> subcommand worked remotely when the others didn't.
>
> There was also some discussion of wedging it into psql, which would at
> least have the advantage that it'd typically be installed on the right
> side of the client/server divide.  But I still think "wedging into" is
> the appropriate verb there: psql is a tool for making a connection and
> executing some SQL commands, and "ping" is not that.
>
> Yeah, I know a whole new executable is kind of a pain, and the amount of
> infrastructure and added maintenance seems a bit high compared to what
> this does.  But a lot of the programs in src/bin/scripts are not much
> bigger.  (In fact that might be the best place for this.)
>
> regards, tom lane

I considered src/bin/scripts but all those are for maintenance tasks
on the DB. createdb/vacuumdb/reindexdb etc. It doesn't need any of the
bits in common.h/common.c, nor does it need some of the includes that
the build process has. I would also like it to have a regression test
which none of those seem to have. Seems like it would be a bit of a
wedge there as well, but if we did, maybe we call it pingdb instead?

If there is consensus that we want it to live there, I can write a
patch for that as well. Though just to be clear my preference at this
point is still for a standalone binary.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[David Johnston]

> -Original Message-
> From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-
> ow...@postgresql.org] On Behalf Of Tom Lane
> Sent: Monday, October 15, 2012 7:13 PM
> To: Andres Freund
> Cc: pgsql-hackers@postgresql.org; Thom Brown; Phil Sorber
> Subject: Re: [HACKERS] [WIP] pg_ping utility
> 
> Andres Freund  writes:
> > Why not add a pg_ctl subcommand for that? For me that sounds like a
> > good place for it...
> 
> I think that's a bad fit, because every other pg_ctl subcommand requires
> access to the data directory.  It would be very confusing if this one
> subcommand worked remotely when the others didn't.
> 
> There was also some discussion of wedging it into psql, which would at
least
> have the advantage that it'd typically be installed on the right side of
the
> client/server divide.  But I still think "wedging into" is the appropriate
verb
> there: psql is a tool for making a connection and executing some SQL
> commands, and "ping" is not that.
> 
> Yeah, I know a whole new executable is kind of a pain, and the amount of
> infrastructure and added maintenance seems a bit high compared to what
> this does.  But a lot of the programs in src/bin/scripts are not much
bigger.
> (In fact that might be the best place for this.)
> 
>   regards, tom lane
> 

This seems to be begging for a canonical "pg_monitor" command where
"pg_ping" would be one sub-command.  A bit much for a single command but it
would provide a frame onto which additional user interfaces could be hung -
though I am lacking for concrete examples at the moment.  pg_monitor would
be focused on "database" monitoring and not "cluster" monitoring generally
but pg_ping would be a necessary pre-requisite since if the cluster is not
available database monitoring doesn't make any sense.

With the recent focus on pg_stat_statements and the current WIP on
"pg_lwlocks" having an official UI for accessing much of this kind data has
merit.  Encapsulating the queries into commands makes actually using them
easier and there can be associated documentation discussing how to interpret
those specific "commands" and some level of consistency when asking for data
for bug and performance reports.  It may be that psql already does much of
this as I am just not that familiar with the program but if that is the case
then classifying it as "making a connection and executing some SQL commands"
is a limited description.  pg_ping is arguably doing at least the first part
of that.

David J.





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Tom Lane]

Andres Freund  writes:
> Why not add a pg_ctl subcommand for that? For me that sounds like a good 
> place 
> for it...

I think that's a bad fit, because every other pg_ctl subcommand requires
access to the data directory.  It would be very confusing if this one
subcommand worked remotely when the others didn't.

There was also some discussion of wedging it into psql, which would at
least have the advantage that it'd typically be installed on the right
side of the client/server divide.  But I still think "wedging into" is
the appropriate verb there: psql is a tool for making a connection and
executing some SQL commands, and "ping" is not that.

Yeah, I know a whole new executable is kind of a pain, and the amount of
infrastructure and added maintenance seems a bit high compared to what
this does.  But a lot of the programs in src/bin/scripts are not much
bigger.  (In fact that might be the best place for this.)

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH 8/8] Introduce wal decoding via catalog timetravel

[Tatsuo Ishii]

> The design Andres and Simon have advanced already eliminates a lot of
> the common failure cases (now(), random(), nextval()) suffered by pgPool
> and similar tools.  But remember, this feature doesn't have to be

Well, pgpool-II already solved the now() case by using query rewriting
technique. The technique could be applied to random() as well but I'm
not convinced it is worth the trouble. nexval() would be a little
harder because pgpool needs an assistance from PostgreSQL core.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Tuesday, October 16, 2012 12:13:14 AM Christopher Browne wrote:
> On Mon, Oct 15, 2012 at 4:51 PM, Andres Freund  
wrote:
> > On Monday, October 15, 2012 10:08:28 PM Christopher Browne wrote:
> >> On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan 
> > 
> > wrote:
> >> > On 15 October 2012 19:19, Bruce Momjian  wrote:
> >> >> I think Robert is right that if Slony can't use the API, it is
> >> >> unlikely any other replication system could use it.
> >> > 
> >> > I don't accept that. Clearly there is a circular dependency, and
> >> > someone has to go first - why should the Slony guys invest in adopting
> >> > this technology if it is going to necessitate using a forked Postgres
> >> > with an uncertain future? That would be (with respect to the Slony
> >> > guys) a commercial risk that is fairly heavily concentrated with
> >> > Afilias.
> >> 
> >> Yep, there's something a bit too circular there.
> >> 
> >> I'd also not be keen on reimplementing the "Slony integration" over
> >> and over if it turns out that the API churns for a while before
> >> stabilizing.  That shouldn't be misread as "I expect horrible amounts
> >> of churn", just that *any* churn comes at a cost.  And if anything
> >> unfortunate happens, that can easily multiply into a multiplicity of
> >> painfulness(es?).
> > 
> > Well, as a crosscheck, could you list your requirements?
> > 
> > Do you need anything more than outputting data in a format compatible to
> > whats stored in sl_log_*? You wouldn't have sl_actionseq, everything
> > else should be there (Well, you would need to do lookups to get the
> > tableid, but thats not really much of a problem). The results would be
> > ordered in complete transactions, in commit order.
> 
> Hmm.  We need to have log data that's in a compatible ordering.
> 
> We use sl_actionseq, and can mix data from multiple transactions
> together; if what you're providing is, instead, in order based on
> transaction commit order followed by some sequencing within each
> transaction, then that should be acceptable.

Inside the transaction its sequenced by the order the XLogInsert calls were 
made which is the order the client sent the commands. That sounds like it 
should be compatible.

> The stylized query on sl_log_* looks like...
> 
> select log_origin, log_txid, log_tableid,
> log_actionseq, log_tablenspname,
> log_tablerelname, log_cmdtype,
> log_cmdupdncols, log_cmdargs
> from %s.sl_log_%d
> where log_origin = %d
> 
> How about I "quibble" about each of these:
> 
> a) log_origin - this indicates the node from which the data
> originates.  Presumably, this is implicit in a "chunk" of data that is
> coming in.

I think we can just reuse whatever method you use in slony to get the current 
node's id to get it in the output plugin.

> b) log_txid - indicating the transaction ID.  I presume you've got
> this available.  It's less important with the WAL-based scheme in that
> we'd probably not be using it as a basis for querying as is the case
> today with Slony.

Its directly available. The plugin will have to call txid_out, but thats 
obviously no problem.

> c) log_tableid - indicating the ID of the table.  Are you capturing an
> OID equivalent to this?  Or what?

You get the TupleDesc of the table.

> d) log_actionseq - indicating relative sequences of updates.  You
> don't have this, but if you're capturing commit ordering, we don't
> need it.

Good.

> e) log_tablenspname, log_tablerelname - some small amount of magic
> needful to get this.  Or perhaps you are already capturing it?

The relevant backend functions available, so its no problem 
(RelationGetNamespace(change->new_tuple->table)).

> f) log_cmdtype - I/U/D/T - indicating the action
> (insert/update/delete/truncate).  Hopefully you have something like
> this?

Yes:
enum ApplyCacheChangeType
{
APPLY_CACHE_CHANGE_INSERT,
APPLY_CACHE_CHANGE_UPDATE,
APPLY_CACHE_CHANGE_DELETE,
..
}

> g) log_cmdupdncols - for UPDATE action, the number of updated columns.
>  Probably not mandatory; this was a new 2.1 thing...

Hm. NO. We don't have that. But then, you can just use normal C code there, so 
it shouldn't be too hard to compute if neede.d

> h) log_cmdargs - the actual data needed to do the I/U/D.  The form of
> this matters a fair bit.  Before Slony 2.1, this was a portion of a
> SQL statement, omitting the operation (provided in log_cmdtype) and
> the table name (in log_tablerelname et al).  In Slony 2.1, this
> changes to be a text[] array that essentially consists of pairs of
> [column name, column value] values.

The existing C code to generate this should be copy&pasteable into this with a 
relatively small amount of changes.

> I see one place, very notable in Slony 2.2, that would also be more
> complicated, which is the handling of DDL.
> 
> In 2.1 and earlier, we handled DDL as "events", essentially out of
> band.  This isn't actually correct; it could mix very badly if you had
> replication activity mixing with DDL reque

Re: [HACKERS] Global Sequences

[Josh Berkus]

On 10/15/12 2:33 PM, Simon Riggs wrote:
> 3) Lazy allocation from the value space. When a node is close to
> running out of values, it requests a new allocation and coordinates
> with all nodes to confirm the new allocation is good.
> 
> (3) is similar to the way values are allocated currently, so the only
> addition is a multi-node allocation algorithm to allocate new value
> ranges. That seems to be the best way to go. Any implementation for
> that presumes how the node configuration and inter-node transport
> works, which we would like to keep open for use by various external
> tools.

So you're talking about allocating a block of values for each node?
Seems straightforwards.  That does mean that there needs to be a
"master" node in charge of the allocations, though, yes?  How would this
be tooled/managed?

I'd also love to hear from the PostgresXC folks on whether this solution
works for them.  Postgres-R too.  If it works for all three of those
tools, it's liable to work for any potential new tool.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Christopher Browne]

On Mon, Oct 15, 2012 at 4:51 PM, Andres Freund  wrote:
> On Monday, October 15, 2012 10:08:28 PM Christopher Browne wrote:
>> On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan 
> wrote:
>> > On 15 October 2012 19:19, Bruce Momjian  wrote:
>> >> I think Robert is right that if Slony can't use the API, it is unlikely
>> >> any other replication system could use it.
>> >
>> > I don't accept that. Clearly there is a circular dependency, and
>> > someone has to go first - why should the Slony guys invest in adopting
>> > this technology if it is going to necessitate using a forked Postgres
>> > with an uncertain future? That would be (with respect to the Slony
>> > guys) a commercial risk that is fairly heavily concentrated with
>> > Afilias.
>>
>> Yep, there's something a bit too circular there.
>>
>> I'd also not be keen on reimplementing the "Slony integration" over
>> and over if it turns out that the API churns for a while before
>> stabilizing.  That shouldn't be misread as "I expect horrible amounts
>> of churn", just that *any* churn comes at a cost.  And if anything
>> unfortunate happens, that can easily multiply into a multiplicity of
>> painfulness(es?).
>
> Well, as a crosscheck, could you list your requirements?
>
> Do you need anything more than outputting data in a format compatible to whats
> stored in sl_log_*? You wouldn't have sl_actionseq, everything else should be
> there (Well, you would need to do lookups to get the tableid, but thats not
> really much of a problem). The results would be ordered in complete
> transactions, in commit order.

Hmm.  We need to have log data that's in a compatible ordering.

We use sl_actionseq, and can mix data from multiple transactions
together; if what you're providing is, instead, in order based on
transaction commit order followed by some sequencing within each
transaction, then that should be acceptable.

The stylized query on sl_log_* looks like...

select log_origin, log_txid, log_tableid,
log_actionseq, log_tablenspname,
log_tablerelname, log_cmdtype,
log_cmdupdncols, log_cmdargs
from %s.sl_log_%d
where log_origin = %d

How about I "quibble" about each of these:

a) log_origin - this indicates the node from which the data
originates.  Presumably, this is implicit in a "chunk" of data that is
coming in.

b) log_txid - indicating the transaction ID.  I presume you've got
this available.  It's less important with the WAL-based scheme in that
we'd probably not be using it as a basis for querying as is the case
today with Slony.

c) log_tableid - indicating the ID of the table.  Are you capturing an
OID equivalent to this?  Or what?

d) log_actionseq - indicating relative sequences of updates.  You
don't have this, but if you're capturing commit ordering, we don't
need it.

e) log_tablenspname, log_tablerelname - some small amount of magic
needful to get this.  Or perhaps you are already capturing it?

f) log_cmdtype - I/U/D/T - indicating the action
(insert/update/delete/truncate).  Hopefully you have something like
this?

g) log_cmdupdncols - for UPDATE action, the number of updated columns.
 Probably not mandatory; this was a new 2.1 thing...

h) log_cmdargs - the actual data needed to do the I/U/D.  The form of
this matters a fair bit.  Before Slony 2.1, this was a portion of a
SQL statement, omitting the operation (provided in log_cmdtype) and
the table name (in log_tablerelname et al).  In Slony 2.1, this
changes to be a text[] array that essentially consists of pairs of
[column name, column value] values.

I see one place, very notable in Slony 2.2, that would also be more
complicated, which is the handling of DDL.

In 2.1 and earlier, we handled DDL as "events", essentially out of
band.  This isn't actually correct; it could mix very badly if you had
replication activity mixing with DDL requests.  (More detail than you
want is in a bug on this...
).

In Slony 2.2, we added a third "log table" where DDL gets captured.
sl_log_script has much the same schema as sl_log_{1,2}; it needs to
get "mixed in" in compatible order.  What I imagine would pointedly
complicate life is if a single transaction contained both DDL and
"regular replicable activity."  Slony 2.2 mixes this in using XID +
log_actionseq; how this would play out with your log capture mechanism
isn't completely clear to me.  That's the place where I'd expect the
very messiest interaction.

> I guess the other tables would stay as they are as they contain the "added
> value" of slony?

A fair bit of Slony is about the "event infrastructure," and some of
that ceases to be as needful.  The configuration bits probably
continue to remain interesting.

The parts that seem notably mysterious to me at the moment are:

a) How do we pull result sets (e.g. - sl_log_* data)?

b) How is the command data represented?

c) If we have a need to mix together your 'raw logs' and other
material (e.g. - our sl_log_script that captures DDL-like changes to
be m

[HACKERS] Global Sequences

[Simon Riggs]

Sequences, as defined by SQL Standard, provide a series of unique
values. The current implementation on PostgreSQL isolates the
generation mechanism to only a single node, as is common on many
RDBMS.

For sharded or replicated systems it forces people to various hackish
mechanisms in user space for emulating a global or cluster-wide
sequence.

The solution to this problem is an in-core solution that allows
coordination between nodes to guarantee unique values.

There are a few options
1) Manual separation of the value space, so that N1 has 50% of
possible values and N2 has 50%. That has problems when we reconfigure
the cluster, and requires complex manual reallocation of values. So it
starts good but ends badly.
2) Automatic separation of the value space. This could mimic the
manual operation, so it does everything for you - but thats just
making a bad idea automatic
3) Lazy allocation from the value space. When a node is close to
running out of values, it requests a new allocation and coordinates
with all nodes to confirm the new allocation is good.

(3) is similar to the way values are allocated currently, so the only
addition is a multi-node allocation algorithm to allocate new value
ranges. That seems to be the best way to go. Any implementation for
that presumes how the node configuration and inter-node transport
works, which we would like to keep open for use by various external
tools.

So, proposal is to allow nextval() allocation to access a plugin,
rather than simply write a WAL record and increment. If the plugin is
loaded all sequences call it (not OIDs).

We'd call this the Global Sequence API. The API looks like it would be
pretty stable to me. We can put something in contrib if required to
prove it works, as well as providing some optional caching to further
avoid performance effects from being noted.

Note that if you did just want to implement manual separation of
ranges then this would also make it slightly easier, so this approach
supports all flavors, which a more hardcoded solution would not.

Any comments before I demonstrate a patch to do this?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Phil Sorber]

On Mon, Oct 15, 2012 at 5:32 PM, Andres Freund  wrote:
> On Monday, October 15, 2012 11:28:36 PM Thom Brown wrote:
>> On 13 October 2012 22:19, Phil Sorber  wrote:
>> > Based on a previous thread
>> > (http://archives.postgresql.org/pgsql-hackers/2012-10/msg00131.php) I
>> > have put together a first attempt of a pg_ping utility. I am attaching
>> > two patches. One for the executable and one for the docs.
>> >
>> > I would also like to make a regression tests and translations, but
>> > wanted to get feedback on what I had so far.
>>
>> pg_pong:
>>
>> 1 packets transmitted, 1 received, 0% packet loss, time 2 days
>>
>> Well this works for me, and I raised a couple typos directly to Phil.
>> The advantage of this over "pg_ctl status" is that it doesn't have to
>> be run on the machine local to the database, and access to the data
>> directory isn't required if it is run locally.  The advantage over
>> connecting using a regular connection is that authentication and
>> authorisation isn't necessary, and if all connections are in use, it
>> will still return the desired result.  And it does what it says on the
>> tin.
>>
>> So +1 from me.
>
> Why not add a pg_ctl subcommand for that? For me that sounds like a good place
> for it...
>
> Andres
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

We discussed that in the other thread. pg_ctl is often only (always?)
packaged with the server binaries and not client. Discussed adding it
to psql, but Tom said he'd prefer to see it as a standalone binary
anyway. I don't have any real strong opinion about it going into an
existing binary like psql (I have a patch for this too) or being
standalone, I just think we should have some way to do this from the
command line on a client. It seems trivial, but I think it's very
useful and if our libpq already supports this, why not?

FWIW pg_ctl does use the same API (PQping), but it doesn't expose it
as an option you can use exclusively. It just uses it to make sure the
server is up/down depending on what it is trying to do.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Andres Freund]

On Monday, October 15, 2012 11:28:36 PM Thom Brown wrote:
> On 13 October 2012 22:19, Phil Sorber  wrote:
> > Based on a previous thread
> > (http://archives.postgresql.org/pgsql-hackers/2012-10/msg00131.php) I
> > have put together a first attempt of a pg_ping utility. I am attaching
> > two patches. One for the executable and one for the docs.
> > 
> > I would also like to make a regression tests and translations, but
> > wanted to get feedback on what I had so far.
> 
> pg_pong:
> 
> 1 packets transmitted, 1 received, 0% packet loss, time 2 days
> 
> Well this works for me, and I raised a couple typos directly to Phil.
> The advantage of this over "pg_ctl status" is that it doesn't have to
> be run on the machine local to the database, and access to the data
> directory isn't required if it is run locally.  The advantage over
> connecting using a regular connection is that authentication and
> authorisation isn't necessary, and if all connections are in use, it
> will still return the desired result.  And it does what it says on the
> tin.
> 
> So +1 from me.

Why not add a pg_ctl subcommand for that? For me that sounds like a good place 
for it...

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] pg_ping utility

[Thom Brown]

On 13 October 2012 22:19, Phil Sorber  wrote:
> Based on a previous thread
> (http://archives.postgresql.org/pgsql-hackers/2012-10/msg00131.php) I
> have put together a first attempt of a pg_ping utility. I am attaching
> two patches. One for the executable and one for the docs.
>
> I would also like to make a regression tests and translations, but
> wanted to get feedback on what I had so far.

pg_pong:

1 packets transmitted, 1 received, 0% packet loss, time 2 days

Well this works for me, and I raised a couple typos directly to Phil.
The advantage of this over "pg_ctl status" is that it doesn't have to
be run on the machine local to the database, and access to the data
directory isn't required if it is run locally.  The advantage over
connecting using a regular connection is that authentication and
authorisation isn't necessary, and if all connections are in use, it
will still return the desired result.  And it does what it says on the
tin.

So +1 from me.

-- 
Thom


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] parallel pg_dump

[Andrew Dunstan]

On 10/13/2012 10:46 PM, Andrew Dunstan wrote:


On 09/17/2012 10:01 PM, Joachim Wieland wrote:
On Mon, Jun 18, 2012 at 10:05 PM, Joachim Wieland  
wrote:

Attached is a rebased version of the parallel pg_dump patch.

Attached is another rebased version for the current commitfest.


These did not apply cleanly, but I have fixed them up. The combined 
diff against git tip is attached. It can also be pulled from my 
parallel_dump branch on 
 This builds and runs 
OK on Linux, which is a start ...




Well, you would also need this piece if you're applying the patch 
(sometimes I forget to do git add ...)


cheers


andrew

diff --git a/src/bin/pg_dump/parallel.c b/src/bin/pg_dump/parallel.c
new file mode 100644
index 000..65bc8bb
--- /dev/null
+++ b/src/bin/pg_dump/parallel.c
@@ -0,0 +1,1283 @@
+/*-
+ *
+ * parallel.c
+ *
+ *	Parallel support for the pg_dump archiver
+ *
+ * Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *	The author is not responsible for loss or damages that may
+ *	result from its use.
+ *
+ * IDENTIFICATION
+ *		src/bin/pg_dump/parallel.c
+ *
+ *-
+ */
+
+#include "pg_backup_db.h"
+
+#include "dumpmem.h"
+#include "dumputils.h"
+#include "parallel.h"
+
+#ifndef WIN32
+#include 
+#include 
+#include "signal.h"
+#include 
+#include 
+#endif
+
+#define PIPE_READ			0
+#define PIPE_WRITE			1
+
+/* file-scope variables */
+#ifdef WIN32
+static unsigned int	tMasterThreadId = 0;
+static HANDLE		termEvent = INVALID_HANDLE_VALUE;
+static int pgpipe(int handles[2]);
+static int piperead(int s, char *buf, int len);
+#define pipewrite(a,b,c)	send(a,b,c,0)
+#else
+/*
+ * aborting is only ever used in the master, the workers are fine with just
+ * wantAbort.
+ */
+static bool aborting = false;
+static volatile sig_atomic_t wantAbort = 0;
+#define pgpipe(a)			pipe(a)
+#define piperead(a,b,c)		read(a,b,c)
+#define pipewrite(a,b,c)	write(a,b,c)
+#endif
+
+typedef struct ShutdownInformation
+{
+ParallelState *pstate;
+Archive   *AHX;
+} ShutdownInformation;
+
+static ShutdownInformation shutdown_info;
+
+static const char *modulename = gettext_noop("parallel archiver");
+
+static ParallelSlot *GetMyPSlot(ParallelState *pstate);
+static void parallel_exit_msg_func(const char *modulename,
+   const char *fmt, va_list ap)
+			__attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 0)));
+static void parallel_msg_master(ParallelSlot *slot, const char *modulename,
+const char *fmt, va_list ap)
+			__attribute__((format(PG_PRINTF_ATTRIBUTE, 3, 0)));
+static void archive_close_connection(int code, void *arg);
+static void ShutdownWorkersHard(ParallelState *pstate);
+static void WaitForTerminatingWorkers(ParallelState *pstate);
+#ifndef WIN32
+static void sigTermHandler(int signum);
+#endif
+static void SetupWorker(ArchiveHandle *AH, int pipefd[2], int worker,
+		RestoreOptions *ropt);
+static bool HasEveryWorkerTerminated(ParallelState *pstate);
+
+static void lockTableNoWait(ArchiveHandle *AH, TocEntry *te);
+static void WaitForCommands(ArchiveHandle *AH, int pipefd[2]);
+static char *getMessageFromMaster(int pipefd[2]);
+static void sendMessageToMaster(int pipefd[2], const char *str);
+static int select_loop(int maxFd, fd_set *workerset);
+static char *getMessageFromWorker(ParallelState *pstate,
+  bool do_wait, int *worker);
+static void sendMessageToWorker(ParallelState *pstate,
+			int worker, const char *str);
+static char *readMessageFromPipe(int fd);
+
+#define messageStartsWith(msg, prefix) \
+	(strncmp(msg, prefix, strlen(prefix)) == 0)
+#define messageEquals(msg, pattern) \
+	(strcmp(msg, pattern) == 0)
+
+static ParallelSlot *
+GetMyPSlot(ParallelState *pstate)
+{
+	int i;
+
+	for (i = 0; i < pstate->numWorkers; i++)
+#ifdef WIN32
+		if (pstate->parallelSlot[i].threadId == GetCurrentThreadId())
+#else
+		if (pstate->parallelSlot[i].pid == getpid())
+#endif
+			return &(pstate->parallelSlot[i]);
+
+	return NULL;
+}
+
+/*
+ * This is the function that will be called from exit_horribly() to print the
+ * error message. If the worker process does exit_horribly(), we forward its
+ * last words to the master process. The master process then does
+ * exit_horribly() with this error message itself and prints it normally.
+ * After printing the message, exit_horribly() on the master will shut down
+ * the remaining worker processes.
+ */
+static void
+parallel_exit_msg_func(const char *modulename, const char *fmt, va_list ap)
+{
+	ParallelState *pstate = shutdown_info.pstate;
+	ParallelSlot *slot;
+
+	Assert(pstate);
+
+	slot = GetMyPSlot(pstate);
+
+	if (!slot)
+		/* We're the parent, just write the message out */
+		vwrite_msg(modulename, fmt, ap);
+	else
+		/* If we're

Re: [HACKERS] Visual Studio 2012 RC

[Noah Misch]

On Mon, Oct 15, 2012 at 09:17:09PM +0200, Brar Piening wrote:
> Noah Misch wrote:
>> The only matter still requiring attention is a fix for IsoLocaleName(). 
>> 
> Yep - I'll work on this and on some denoisifying of the build log files.

Great.  Just to be clear, I consider the denoisification optional.  Fixing
IsoLocaleName(), however, is essential.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Monday, October 15, 2012 10:08:28 PM Christopher Browne wrote:
> On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  
wrote:
> > On 15 October 2012 19:19, Bruce Momjian  wrote:
> >> I think Robert is right that if Slony can't use the API, it is unlikely
> >> any other replication system could use it.
> > 
> > I don't accept that. Clearly there is a circular dependency, and
> > someone has to go first - why should the Slony guys invest in adopting
> > this technology if it is going to necessitate using a forked Postgres
> > with an uncertain future? That would be (with respect to the Slony
> > guys) a commercial risk that is fairly heavily concentrated with
> > Afilias.
> 
> Yep, there's something a bit too circular there.
> 
> I'd also not be keen on reimplementing the "Slony integration" over
> and over if it turns out that the API churns for a while before
> stabilizing.  That shouldn't be misread as "I expect horrible amounts
> of churn", just that *any* churn comes at a cost.  And if anything
> unfortunate happens, that can easily multiply into a multiplicity of
> painfulness(es?).

Well, as a crosscheck, could you list your requirements?

Do you need anything more than outputting data in a format compatible to whats 
stored in sl_log_*? You wouldn't have sl_actionseq, everything else should be 
there (Well, you would need to do lookups to get the tableid, but thats not 
really much of a problem). The results would be ordered in complete 
transactions, in commit order.

I guess the other tables would stay as they are as they contain the "added 
value" of slony?

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Simon Riggs]

On 15 October 2012 21:03, Tom Lane  wrote:
> Robert Haas  writes:
>> On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  
>> wrote:
>>> On 15 October 2012 19:19, Bruce Momjian  wrote:
 I think Robert is right that if Slony can't use the API, it is unlikely
 any other replication system could use it.
>
>>> I don't accept that. Clearly there is a circular dependency, and
>>> someone has to go first - why should the Slony guys invest in adopting
>>> this technology if it is going to necessitate using a forked Postgres
>>> with an uncertain future?
>
>> Clearly, core needs to go first.  However, before we commit, I would
>> like to hear the Slony guys say something like this: We read the
>> documentation that is part of this patch and if the feature behaves as
>> advertised, we believe we will be able to use it in place of the
>> change-capture mechanism that we have now, and that it will be at
>> least as good as what we have now if not a whole lot better.
>
>> If they say something like "I'm not sure we have the right design for
>> this" or "this wouldn't be sufficient to replace this portion of what
>> we have now because it lacks critical feature X", I would be very
>> concerned about that.
>
> The other point here is that core code without any implemented use-cases
> is unlikely to be worth a tinker's damn.  Regardless of what time-frame
> the Slony guys are able to work on, I think we need to see working code
> (of at least prototype quality) before we believe that we've got it
> right.  Or if not code from them, code from some other replication
> project.
>
> A possibly-useful comparison is to the FDW APIs we've been slowly
> implementing over the past couple releases.  Even though we *did* have
> some use-cases written right off the bat, we got it wrong and had to
> change it in 9.2, and I wouldn't bet against having to change it again
> in 9.3 (even without considering the need for extensions for non-SELECT
> operations).  And, despite our very clear warnings that all that stuff
> was in flux, people have been griping because the APIs changed.
>
> So if we ship core hooks for logical replication in advance of proof
> that they're actually usable by at least one (preferably more than one)
> replication project, I confidently predict that they'll be wrong and
> will need revision and the potential users will complain about the
> API instability.

The prototype we showed at PgCon illustrated a working system, so
we're on the right track.

We've split that in two now, specifically to allow other projects to
use what is being built. The exact API of that split is for discussion
and has been massively redesigned on community advice for the sole
purpose of including other approaches. We can't guarantee that
external open source or commercial vendors will use the API. But we
can say that in-core use cases exist for multiple approaches. We
shouldn't put the decision on that in the hands of others.

Jan spoke at length at PgCon, for all to hear, that what we are
building is a much better way than the trigger logging approach Slony
uses. I don't take that as carte blanche for approval of everything
being done, but its going in the right direction with an open heart,
which is about as good as it gets.

There will be a working system again soon, once we have re-built
things around the new API. The longer it takes to get a stable API the
longer we take to rebuild things around it again.

The goal of the project is to release everything open source, PGDG
copyrighted and TPL licenced and to submit to core. We are signed up
to that in various ways, not least of all our word given publicly.
Please give this your backing, so an open source outcome can be
possible.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Monday, October 15, 2012 10:03:40 PM Tom Lane wrote:
> Robert Haas  writes:
> > On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  
wrote:
> >> On 15 October 2012 19:19, Bruce Momjian  wrote:
> >>> I think Robert is right that if Slony can't use the API, it is unlikely
> >>> any other replication system could use it.
> >> 
> >> I don't accept that. Clearly there is a circular dependency, and
> >> someone has to go first - why should the Slony guys invest in adopting
> >> this technology if it is going to necessitate using a forked Postgres
> >> with an uncertain future?
> > 
> > Clearly, core needs to go first.  However, before we commit, I would
> > like to hear the Slony guys say something like this: We read the
> > documentation that is part of this patch and if the feature behaves as
> > advertised, we believe we will be able to use it in place of the
> > change-capture mechanism that we have now, and that it will be at
> > least as good as what we have now if not a whole lot better.
> > 
> > If they say something like "I'm not sure we have the right design for
> > this" or "this wouldn't be sufficient to replace this portion of what
> > we have now because it lacks critical feature X", I would be very
> > concerned about that.
> 
> The other point here is that core code without any implemented use-cases
> is unlikely to be worth a tinker's damn.  Regardless of what time-frame
> the Slony guys are able to work on, I think we need to see working code
> (of at least prototype quality) before we believe that we've got it
> right.  Or if not code from them, code from some other replication
> project.

FWIW we (as in 2ndq), unsurprisingly, have a user of this which is in 
development atm.

> A possibly-useful comparison is to the FDW APIs we've been slowly
> implementing over the past couple releases.  Even though we *did* have
> some use-cases written right off the bat, we got it wrong and had to
> change it in 9.2, and I wouldn't bet against having to change it again
> in 9.3 (even without considering the need for extensions for non-SELECT
> operations).  And, despite our very clear warnings that all that stuff
> was in flux, people have been griping because the APIs changed.

On the other hand, I don't think we would have FDWs today at all if it wouldn't 
have been done that way. So I really cannot see that as an indication of not 
working incrementally.
Obviously thats not an argument for not trying to get the API correct right off 
the bat. I seriously hope the userlevel API continues to be simpler than what 
FDWs need.

Regards,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Christopher Browne]

On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  wrote:
> On 15 October 2012 19:19, Bruce Momjian  wrote:
>> I think Robert is right that if Slony can't use the API, it is unlikely
>> any other replication system could use it.
>
> I don't accept that. Clearly there is a circular dependency, and
> someone has to go first - why should the Slony guys invest in adopting
> this technology if it is going to necessitate using a forked Postgres
> with an uncertain future? That would be (with respect to the Slony
> guys) a commercial risk that is fairly heavily concentrated with
> Afilias.

Yep, there's something a bit too circular there.

I'd also not be keen on reimplementing the "Slony integration" over
and over if it turns out that the API churns for a while before
stabilizing.  That shouldn't be misread as "I expect horrible amounts
of churn", just that *any* churn comes at a cost.  And if anything
unfortunate happens, that can easily multiply into a multiplicity of
painfulness(es?).
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Tom Lane]

Robert Haas  writes:
> On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  
> wrote:
>> On 15 October 2012 19:19, Bruce Momjian  wrote:
>>> I think Robert is right that if Slony can't use the API, it is unlikely
>>> any other replication system could use it.

>> I don't accept that. Clearly there is a circular dependency, and
>> someone has to go first - why should the Slony guys invest in adopting
>> this technology if it is going to necessitate using a forked Postgres
>> with an uncertain future?

> Clearly, core needs to go first.  However, before we commit, I would
> like to hear the Slony guys say something like this: We read the
> documentation that is part of this patch and if the feature behaves as
> advertised, we believe we will be able to use it in place of the
> change-capture mechanism that we have now, and that it will be at
> least as good as what we have now if not a whole lot better.

> If they say something like "I'm not sure we have the right design for
> this" or "this wouldn't be sufficient to replace this portion of what
> we have now because it lacks critical feature X", I would be very
> concerned about that.

The other point here is that core code without any implemented use-cases
is unlikely to be worth a tinker's damn.  Regardless of what time-frame
the Slony guys are able to work on, I think we need to see working code
(of at least prototype quality) before we believe that we've got it
right.  Or if not code from them, code from some other replication
project.

A possibly-useful comparison is to the FDW APIs we've been slowly
implementing over the past couple releases.  Even though we *did* have
some use-cases written right off the bat, we got it wrong and had to
change it in 9.2, and I wouldn't bet against having to change it again
in 9.3 (even without considering the need for extensions for non-SELECT
operations).  And, despite our very clear warnings that all that stuff
was in flux, people have been griping because the APIs changed.

So if we ship core hooks for logical replication in advance of proof
that they're actually usable by at least one (preferably more than one)
replication project, I confidently predict that they'll be wrong and
will need revision and the potential users will complain about the
API instability.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Christopher Browne]

On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
 wrote:
> Robert Haas  writes:
>>> if (select 1 from pg_class where relname = 'foo' and
>>> pg_table_is_visible(oid)) then
>>> truncate table foo;
>>> end if;
>>
>> Yeah, I think the functionality that we need is pretty much there
>> already today.  What we need to do is to get the syntax to a point
>> where people can write the code they want to write without getting
>> tangled up by it.
>
> What about continuing to extend on that incredibly useful WITH syntax we
> already have:
>
>WITH target AS (
>   SELECT oid::regclass AS t
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
>WHERE pg_table_is_visible(oid)
>  AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
>)
>TRUNCATE TABLE t FROM target;

This still seems to be trying rather too hard.

The original suggestion was that, given the original query:

  truncate table public.foo;

that we add syntax to make the request optional:

  truncate table if exists public.foo;

Throwing in $$, oid, pg_class, joins, and such all seem like way more
syntax than we started with.

There are only so many 'clean' ways to modify the truncate request:

a) We could augment TRUNCATE with an "IF EXISTS" modifier, as
described in the initial patch.

b) Perhaps the IF EXIST might fit well afterwards, or be reversed somehow.

   truncate table unless not exists public.foo;
   truncate table public.foo if exists;
   truncate table where exists public.foo;

c) My proposal was to add in a more generic modifier that wouldn't be
specific to TRUNCATE.

Thus:

   truncate table public.foo if exists table public.foo;

That's a *little* longer than what's in b), but this would allow
extending the conditional to any kind of statement, which seems like a
more powerful idea to me.  It would also support doing other actions
on the same conditional basis:

   insert into bar (select id, name from public.foo)
  if exists table public.foo;

If you want a more "prefix-y" version, well, here's how it might look
using a leading WITH clause:

   with exists table public.foo
  truncate public.foo;

  with exists table public.foo
 insert into bar (select id, name from public.foo);

I don't terribly much like that.  I think I'd rather use WHEN than WITH.

   when exists table public.foo
  truncate public.foo;

  when exists table public.foo
 insert into bar (select id, name from public.foo);

That does seem a bit nicer than the { STATEMENT } if (conditional)
idea.  And nary a $$, oid, or pg_class to be seen.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Andrew Dunstan]

On 10/15/2012 03:23 PM, Bruce Momjian wrote:

I have trouble seeing how we could implement Postgres as efficiently
without C macros, but maybe that is the point --- efficiency is not
critical in SQL --- Java and C++ give other options that are "good
enough" and less error-prone.




Er, C++ uses the preprocessor. In fact, C++ was originally created as a 
set of preprocessor macros, IIRC.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Monday, October 15, 2012 09:18:57 PM Peter Geoghegan wrote:
> On 15 October 2012 19:19, Bruce Momjian  wrote:
> > I think Robert is right that if Slony can't use the API, it is unlikely
> > any other replication system could use it.
> 
> I don't accept that. Clearly there is a circular dependency, and
> someone has to go first - why should the Slony guys invest in adopting
> this technology if it is going to necessitate using a forked Postgres
> with an uncertain future?

Well. I don't think (hope) anybody proposed making something release worthy for 
slony but rather a POC patch that proofs the API is generic enough to be used 
by them. If I (or somebody else familiar with this) work together with somebody 
familiar with with slony internals I think such a POC shouldn't be too hard to 
do.
I think some more input from that side is a good idea. I plan to send out an 
email to possibly interested parties in about two weeks...

Regards,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Potential autovacuum optimization: new tables

[Christopher Browne]

On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus  wrote:
> So, problem #1 is coming up with a mathematical formula.  My initial target 
> values are in terms of # of rows in the table vs. # of writes before analyze 
> is triggered:
>
> 1 : 3
> 10 : 5
> 100 : 10
> 1000 : 100
> 10 : 2000
> 100 : 5000
> 1000 : 25000
> 1 : 10

Do we necessarily care about smoothness?

If we don't at all, then this would be fine:

func powerlaw (tuples int) int {
if tuples < 10 {
return 3
}
if tuples < 100 {
return 5
}
if tuples < 1000 {
return 10
}
if tuples < 10 {
return 100
}
if tuples < 100 {
return 2000
}
if tuples < 1000 {
return 5000
}
if tuples < 1 {
return 25000
}
return 10
}

If we want smoothness within the ranges, this is a piecewise linear
representation of your table:

func powerlaw2 (tuples int) int {
if tuples < 10 {
return 3
}
if tuples < 100 {
return 5 + 5 * (tuples - 90)/90
}
if tuples < 1000 {
return 10 + 90 * (tuples - 900)/900
}
if tuples < 10 {
return 100 + 1900 * (tuples - 99000)/99000
}
if tuples < 100 {
return 2000 + 3000 * (tuples - 90)/90
}
if tuples < 1000 {
return 5000 + 22000 * (tuples - 900)/900
}
if tuples < 1 {
return 25000 + 75000 * (tuples - 9000)/9000
}
return 10   
}

That's in Go, but there shouldn't be anything too unfamiliar looking
about it :-).

It would be nice to have a simpler functional representation, but the
above is by no means heinous, and it's not verbose beyond reason.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] smgrsettransient mechanism is full of bugs

[Tom Lane]

I wrote:
> I got a bit suspicious of the transient-file mechanism introduced in
> commit fba105b1099f4f5fa7283bb17cba6fed2baa8d0c after noticing that
> ...
> I believe that we probably ought to revert this mechanism entirely, and
> build a new implementation based on these concepts:
> * An SMgrRelation is transient if and only if it doesn't have an
> "owning" relcache entry.  Keep a list of all such SmgrRelations, and
> close them all at transaction end.  (Obviously, an SMgrRelation gets
> removed from the list if it acquires an owner mid-transaction.)
> * There's no such concept as FD_XACT_TRANSIENT at the fd.c level.
> Rather, we close and delete the VFD entry when told to by SmgrRelation
> closure.

Attached is a draft patch for that, presented in two parts: the first
part just reverts commit fba105b1099f4f5fa7283bb17cba6fed2baa8d0c,
and the second part installs the new mechanism.  I'm reasonably pleased
with the way this turned out; I think it's cleaner as well as more
reliable than the previous patch.

The list-management code could possibly be replaced with slist once that
patch goes in, but since this needs to be back-patched, I didn't
consider that for the moment.

Comments?

regards, tom lane

diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 56095b32501efef651b0e650c5938de9b16d46f9..bdcbe47ac9e9f5ce0db5f90b8ddb567bf34ade00 100644
*** a/src/backend/storage/buffer/bufmgr.c
--- b/src/backend/storage/buffer/bufmgr.c
*** BufferGetTag(Buffer buffer, RelFileNode 
*** 1882,1891 
   * written.)
   *
   * If the caller has an smgr reference for the buffer's relation, pass it
!  * as the second parameter.  If not, pass NULL.  In the latter case, the
!  * relation will be marked as "transient" so that the corresponding
!  * kernel-level file descriptors are closed when the current transaction ends,
!  * if any.
   */
  static void
  FlushBuffer(volatile BufferDesc *buf, SMgrRelation reln)
--- 1882,1888 
   * written.)
   *
   * If the caller has an smgr reference for the buffer's relation, pass it
!  * as the second parameter.  If not, pass NULL.
   */
  static void
  FlushBuffer(volatile BufferDesc *buf, SMgrRelation reln)
*** FlushBuffer(volatile BufferDesc *buf, SM
*** 1909,1920 
  	errcontext.previous = error_context_stack;
  	error_context_stack = &errcontext;
  
! 	/* Find smgr relation for buffer, and mark it as transient */
  	if (reln == NULL)
- 	{
  		reln = smgropen(buf->tag.rnode, InvalidBackendId);
- 		smgrsettransient(reln);
- 	}
  
  	TRACE_POSTGRESQL_BUFFER_FLUSH_START(buf->tag.forkNum,
  		buf->tag.blockNum,
--- 1906,1914 
  	errcontext.previous = error_context_stack;
  	error_context_stack = &errcontext;
  
! 	/* Find smgr relation for buffer */
  	if (reln == NULL)
  		reln = smgropen(buf->tag.rnode, InvalidBackendId);
  
  	TRACE_POSTGRESQL_BUFFER_FLUSH_START(buf->tag.forkNum,
  		buf->tag.blockNum,
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index fed25fd7e7cdda9aaa2c5b646f2e40cf50f000ed..ecb62ba01aeb968aedab3260d95ce07a44aa61fb 100644
*** a/src/backend/storage/file/fd.c
--- b/src/backend/storage/file/fd.c
*** int			max_safe_fds = 32;	/* default if n
*** 126,133 
  /* these are the assigned bits in fdstate below: */
  #define FD_TEMPORARY		(1 << 0)	/* T = delete when closed */
  #define FD_XACT_TEMPORARY	(1 << 1)	/* T = delete at eoXact */
- #define FD_XACT_TRANSIENT	(1 << 2)	/* T = close (not delete) at aoXact,
- 		 * but keep VFD */
  
  typedef struct vfd
  {
--- 126,131 
*** static Size SizeVfdCache = 0;
*** 158,165 
   */
  static int	nfile = 0;
  
! /* True if there are files to close/delete at end of transaction */
! static bool have_pending_fd_cleanup = false;
  
  /*
   * Tracks the total size of all temporary files.  Note: when temp_file_limit
--- 156,166 
   */
  static int	nfile = 0;
  
! /*
!  * Flag to tell whether it's worth scanning VfdCache looking for temp files
!  * to close
!  */
! static bool have_xact_temporary_files = false;
  
  /*
   * Tracks the total size of all temporary files.  Note: when temp_file_limit
*** LruDelete(File file)
*** 607,613 
  	Vfd		   *vfdP;
  
  	Assert(file != 0);
- 	Assert(!FileIsNotOpen(file));
  
  	DO_DB(elog(LOG, "LruDelete %d (%s)",
  			   file, VfdCache[file].fileName));
--- 608,613 
*** OpenTemporaryFile(bool interXact)
*** 971,977 
  		VfdCache[file].resowner = CurrentResourceOwner;
  
  		/* ensure cleanup happens at eoxact */
! 		have_pending_fd_cleanup = true;
  	}
  
  	return file;
--- 971,977 
  		VfdCache[file].resowner = CurrentResourceOwner;
  
  		/* ensure cleanup happens at eoxact */
! 		have_xact_temporary_files = true;
  	}
  
  	return file;
*** OpenTemporaryFileInTablespace(Oid tblspc
*** 1045,1069 
  }
  
  /*
-  * Set the transient flag on a file
-  *
-  * Th

Re: [HACKERS] Truncate if exists

[Dimitri Fontaine]

Robert Haas  writes:
>>WITH target AS (
>>TRUNCATE TABLE t FROM target;
>
> I'm not exactly sure what that is supposed to do, but it doesn't seem
> like an easy-to-use substitute for truncate-if-exists...

Indeed. I'm still a supporter of truncate-if-exists. Still, we're also
talking about a more flexible and powerful design, it seems to me.

> I like the idea of making our SQL dialect capable of working with DDL
> in more powerful ways; I'm not sold on the concrete proposal.

Cool,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Robert Haas]

On Mon, Oct 15, 2012 at 3:18 PM, Peter Geoghegan  wrote:
> On 15 October 2012 19:19, Bruce Momjian  wrote:
>> I think Robert is right that if Slony can't use the API, it is unlikely
>> any other replication system could use it.
>
> I don't accept that. Clearly there is a circular dependency, and
> someone has to go first - why should the Slony guys invest in adopting
> this technology if it is going to necessitate using a forked Postgres
> with an uncertain future?

Clearly, core needs to go first.  However, before we commit, I would
like to hear the Slony guys say something like this: We read the
documentation that is part of this patch and if the feature behaves as
advertised, we believe we will be able to use it in place of the
change-capture mechanism that we have now, and that it will be at
least as good as what we have now if not a whole lot better.

If they say something like "I'm not sure we have the right design for
this" or "this wouldn't be sufficient to replace this portion of what
we have now because it lacks critical feature X", I would be very
concerned about that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Bruce Momjian]

On Mon, Oct 15, 2012 at 02:14:34PM +0100, Peter Geoghegan wrote:
> On 15 October 2012 00:30, Greg Stark  wrote:
> > In fact it's not a very good analogy because the situation is
> > *precisely* the same -- rules *are* macros and manipulate the raw sql
> > before it's run and the reason they can't be replaced by triggers is
> > because, like functions, triggers happen after the code is compiled
> > and run.
> 
> I quite like this analogy, because it nicely illustrates the problems
> with rules.
> 
> C, and the C preprocessor, are essential the same now as they were in
> the early 1970s. I think that *an emphasis* on a preprocessing stage
> of translation is a fairly discredited idea (though there are some
> sensible uses, particularly where alternatives are not available). C99
> introduced inline functions, probably in no small part because it is
> quite obvious that they are often superior to macros. Consider the two
> most successful programming languages that were obviously influenced
> by C: Java and C++. The first doesn't have a preprocessor, and the
> second strongly encourages using numerous alternatives to macros where
> possible, which is almost always. Maybe you don't like this analogy,
> because you consider C to be a systems programming language, and as
> such think it is only right and proper that programmers should be
> given enough rope to hang themselves. Perhaps you're right, but the
> same surely cannot be said for SQL. The original appeal of SQL was
> that it was supposedly possible for non-programmers to write it.

Ah, so Peter confered the Java angle, and I think he does present the
pitfalls of C macros, and that does translate to the pitfalls of rules.

I have trouble seeing how we could implement Postgres as efficiently
without C macros, but maybe that is the point --- efficiency is not
critical in SQL --- Java and C++ give other options that are "good
enough" and less error-prone.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Robert Haas]

On Mon, Oct 15, 2012 at 3:14 PM, Dimitri Fontaine
 wrote:
> What about continuing to extend on that incredibly useful WITH syntax we
> already have:
>
>WITH target AS (
>   SELECT oid::regclass AS t
> FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
>WHERE pg_table_is_visible(oid)
>  AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
>)
>TRUNCATE TABLE t FROM target;

I'm not exactly sure what that is supposed to do, but it doesn't seem
like an easy-to-use substitute for truncate-if-exists...

>> my view the goal ought to be to refine that mechanism to remove the
>> clunkiness and awkwardness, rather than to invent something completely
>> new.
>
> So, what do you think? Smells like empowered SQL this time, right?

I like the idea of making our SQL dialect capable of working with DDL
in more powerful ways; I'm not sold on the concrete proposal.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Simon Riggs]

On 15 October 2012 18:43, Josh Berkus  wrote:

> Perhaps we should take a different tack on this discussion: what feature
> development is the continued presense of RULES currently blocking?  If
> the rest of us had some idea why you considered this deprecation urgent,
> it would help!

>From me, this isn't urgent at all, as I've said. But it is one source
of disrepute for us that I would have liked to see blocked
quicker/better. But we've agreed a way forwards with doc changes and
that is enough for now.

Personally, I think rules block MERGE, but it has already been agreed
that we would ignore rules for that case. But having said that, MERGE
is not being worked on currently, so its hardly a hot topic and
nothing I was thinking about when I brought up the topic.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Peter Geoghegan]

On 15 October 2012 19:19, Bruce Momjian  wrote:
> I think Robert is right that if Slony can't use the API, it is unlikely
> any other replication system could use it.

I don't accept that. Clearly there is a circular dependency, and
someone has to go first - why should the Slony guys invest in adopting
this technology if it is going to necessitate using a forked Postgres
with an uncertain future? That would be (with respect to the Slony
guys) a commercial risk that is fairly heavily concentrated with
Afilias. So, if you're going to attach as a condition to its
acceptance that the Slony guys be able to use it immediately (because
"can integrate" really means "will integrate", right?), you're
attaching it to a rather arbitrary condition that has nothing much to
do with the technical merit of the patches proposed. The fact of the
matter is that Slony was originally designed with a somewhat different
set of constraints to those that exist today, so I don't doubt that
this is something that they're going to need to integrate over time,
probably in a separate release branch, to get the upsides of in-core
logical replication, along with the great flexibility that Slony
currently offers (and that Afilias undoubtedly depend upon today).

Another way of putting this is that Postgres should go first because
we will get huge benefits even if only one of the trigger-based
logical replication systems adopts the technology. Though I hope and
expect that the Slony guys will be able to work with what we're doing,
surely a logical replication system with all the benefits implied by
being logical, but with with only some subset of Slony's functionality
is still going to be of great benefit.

My view is that the only reasonable approach is to build something
solid, well-integrated and generic, in core. I'd certainly like to
hear what the Slony guys have to say here, though.

-- 
Peter Geoghegan   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Visual Studio 2012 RC

[Brar Piening]

Noah Misch wrote:
The only matter still requiring attention is a fix for IsoLocaleName(). 

Yep - I'll work on this and on some denoisifying of the build log files.

Regards,

Brar



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Dimitri Fontaine]

Robert Haas  writes:
>> if (select 1 from pg_class where relname = 'foo' and
>> pg_table_is_visible(oid)) then
>> truncate table foo;
>> end if;
>
> Yeah, I think the functionality that we need is pretty much there
> already today.  What we need to do is to get the syntax to a point
> where people can write the code they want to write without getting
> tangled up by it.

What about continuing to extend on that incredibly useful WITH syntax we
already have:

   WITH target AS (
  SELECT oid::regclass AS t
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
   WHERE pg_table_is_visible(oid)
 AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
   )
   TRUNCATE TABLE t FROM target;

Maybe somewhat involved as far as code support is concerned. That said,
full integration of a PL into the main parser doesn't strike me as that
easier. Maybe a simpler way to reach the feature would be:

   WITH target AS (
  SELECT oid::regclass AS t
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
   WHERE pg_table_is_visible(oid)
 AND nspname = 'public' AND NOT relname ~ 'exclude-pattern'
   )
   EXECUTE 'TRUNCATE TABLE $1' USING target(t);

But I'm not sure it gives anything else than a hint about how to
implement the first idea.

> I think the invention of DO was a big step in the right direction,
> because before that if you wanted procedural logic in your script, you
> had to create a function, call it, and then drop the function.  That

Yes, that's the sentence that got me to think about the above proposal,
because we are already talking about implementing WITH FUNCTION in
another thread, to answer some of Pavel's needs.

> my view the goal ought to be to refine that mechanism to remove the
> clunkiness and awkwardness, rather than to invent something completely
> new.

So, what do you think? Smells like empowered SQL this time, right?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Simon Riggs]

On 15 October 2012 20:04, Jeff Janes  wrote:
> On Mon, Oct 15, 2012 at 11:49 AM, Andres Freund  
> wrote:
>> On Monday, October 15, 2012 08:46:40 PM Jeff Janes wrote:
>>> On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
>>> > I would be in favor of moving them to contrib for 9.4.  Assuming that
>>> > someone can figure out how this interacts with the existing system table
>>> > opclasses.  Them being in /contrib would also put less pressure on the
>>> > next new hacker who decides to take them on as a feature; they can
>>> > improve them incrementally without needing to fix 100% of issues in the
>>> > first go.
>>>
>>> Is there anything currently in contrib that defines its own WAL
>>> records and replay methods?  Are there hooks for doing so?
>>
>> It's not really possible as rmgr.c declares a const array of resource 
>> managers.
>> A contrib module can't sensibly add itself to that. I think changing this has
>> been discussed/proposed in the past, but -hackers wasn't convinced...
>>
>> But then, the idea is to add it to -contrib while no WAL support exists..
>
> Which then virtually guarantees that WAL support never will exist, doesn't it?
>
>> Personally I don't see a point in -contrib'ing it. I would rather see it 
>> throw
>> errors in dangerous situations and be done with that.
>
> +1

All I'm planning to do for now is add docs and the WARNING suggested.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[k...@rice.edu]

On Mon, Oct 15, 2012 at 11:46:40AM -0700, Jeff Janes wrote:
> On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
> >
> > I would be in favor of moving them to contrib for 9.4.  Assuming that
> > someone can figure out how this interacts with the existing system table
> > opclasses.  Them being in /contrib would also put less pressure on the
> > next new hacker who decides to take them on as a feature; they can
> > improve them incrementally without needing to fix 100% of issues in the
> > first go.
> 
> Is there anything currently in contrib that defines its own WAL
> records and replay methods?  Are there hooks for doing so?
> 
> Cheers,
> 
> Jeff
> 

That is a good point. Please do not move it to contrib if that will make
it even harder/impossible to add WAL support.

Regards,
Ken


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Bruce Momjian]

On Mon, Oct 15, 2012 at 12:30:56AM +0100, Greg Stark wrote:
> On Sun, Oct 14, 2012 at 9:30 AM, Simon Riggs  wrote:
> > On 12 October 2012 19:48, Greg Stark  wrote:
> >> On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs  wrote:
> >>> AFAICS all RULEs can be re-expressed as Triggers or Views.
> >>
> >> This is a bizarre discussion. Firstly this isn't even close to true.
> >> The whole source of people's discontentment is that triggers are *not*
> >> equivalent to rules. If they were then they wouldn't be so upset.
> >
> > This may be a confusion on the point of equivalence; clearly the
> > features work differently.
> >
> > I'm not aware of any rule that can't be rewritten as a trigger or a
> > view. Please can anyone show me some examples of those?
> 
> Huh? The one thing we currently use rules for, implementing views,
> couldn't be done in triggers. In general if your source table is empty
> then there's *nothing* you could cause to happen with triggers because
> no triggers will fire.
> 
> The analogy to this discussion would be something like "users get
> confused by macros in C and usually what they're trying to do can be
> better done with functions. now that we have functions we should
> deprecate macros" All of the preconditions in that sentence are true
> but it doesn't follow because macros exist for a reason.

Well, on a related note, I have heard that Java didn't implement macros
because it confuses context-sensitive editors.  Seems like a
wrong-headed reason to remove a feature.

This is not related to my opinion on rules, but I thought it was
interesting.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Jeff Janes]

On Mon, Oct 15, 2012 at 11:49 AM, Andres Freund  wrote:
> On Monday, October 15, 2012 08:46:40 PM Jeff Janes wrote:
>> On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
>> > I would be in favor of moving them to contrib for 9.4.  Assuming that
>> > someone can figure out how this interacts with the existing system table
>> > opclasses.  Them being in /contrib would also put less pressure on the
>> > next new hacker who decides to take them on as a feature; they can
>> > improve them incrementally without needing to fix 100% of issues in the
>> > first go.
>>
>> Is there anything currently in contrib that defines its own WAL
>> records and replay methods?  Are there hooks for doing so?
>
> It's not really possible as rmgr.c declares a const array of resource 
> managers.
> A contrib module can't sensibly add itself to that. I think changing this has
> been discussed/proposed in the past, but -hackers wasn't convinced...
>
> But then, the idea is to add it to -contrib while no WAL support exists..

Which then virtually guarantees that WAL support never will exist, doesn't it?

> Personally I don't see a point in -contrib'ing it. I would rather see it throw
> errors in dangerous situations and be done with that.

+1

Cheers,

Jeff


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Simon Riggs]

On 15 October 2012 19:46, Jeff Janes  wrote:
> On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
>>
>> I would be in favor of moving them to contrib for 9.4.  Assuming that
>> someone can figure out how this interacts with the existing system table
>> opclasses.  Them being in /contrib would also put less pressure on the
>> next new hacker who decides to take them on as a feature; they can
>> improve them incrementally without needing to fix 100% of issues in the
>> first go.
>
> Is there anything currently in contrib that defines its own WAL
> records and replay methods?  Are there hooks for doing so?

Not to date. Search for rmgr plugins for previous discussions.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Hannu Krosing]

On 10/15/2012 04:54 AM, Robert Haas wrote:

PS. I'd love to see a basic Slony plugin for this, for example, to see how
>much extra code on top of the posted patches you need to write in a plugin
>like that to make it functional. I'm worried that it's a lot..

I agree.  I would go so far as to say that if Slony can't integrate
with this work and use it in place of their existing change-capture
facility, that's sufficient grounds for unconditional rejection.

The fact that current work starts with "apply cache" instead of streaming
makes the semantics very close to how londiste and slony do this.

Therefore I don't think there will be any problems with "can't" though 
it may be

that there will be nobody actually doing it, at least not before January.


Hannu


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Bruce Momjian]

On Mon, Oct 15, 2012 at 08:26:08PM +0200, Andres Freund wrote:
> > > > I do not personally believe that a WAL decoding solution adequate to
> > > > drive logical replication can live outside of core, at least not
> > > > unless core exposes a whole lot more interface than we do now, and
> > > > probably not even then.  Even if it could, I don't see the case for
> > > > making every replication solution reinvent that wheel.  It's a big
> > > > wheel to be reinventing, and everyone needs pretty much the same
> > > > thing.
> > > 
> > > Unsurprisingly I aggree.
> > > 
> > > > That having been said, I have to agree that the people working on this
> > > > project seem to be wearing rose-colored glasses when it comes to the
> > > > difficulty of implementing a full-fledged solution in core.
> > > 
> > > That very well might be true. Sometimes rose-colored glasses can be quite
> > > productive in getting something started...
> > > 
> > > Note at this point were only want wal decoding, background workers and
> > > related things to get integrated...
> > 
> > Well, TODO does have:
> > 
> > Move pgfoundry's xlogdump to /contrib and have it rely more closely on
> > the WAL backend code
> 
> Uhm. How does that relate to my statement?
> 
> The xlogreader code I submitted does contain a very small POC xlogdump with 
> almost no code duplication. It needs some work to be really useful though.

I just meant that dumping xlog contents is something we want to improve.

> > I think Robert is right that if Slony can't use the API, it is unlikely
> > any other replication system could use it.
> 
> I aggree and I don't think I have ever said something contrary. I just don't 
> want to be the only one working on slony integration. I am ready to do a good 
> part of that, but somebody with slony experience needs to help, especially on 
> consuming those changes.

Agreed.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Hannu Krosing]

On 10/15/2012 08:44 PM, Andres Freund wrote:

On Monday, October 15, 2012 08:38:07 PM Hannu Krosing wrote:

On 10/11/2012 01:42 PM, Andres Freund wrote:

On Thursday, October 11, 2012 09:15:47 AM Heikki Linnakangas wrote:
...
If the only meaningful advantage is reducing the amount of WAL written,
I can't help thinking that we should just try to address that in the
existing solutions, even if it seems "easy to solve at a first glance,
but a solution not using a normal transactional table for its log/queue
has to solve a lot of problems", as the document says.
Youre welcome to make suggestions, but everything I could think of that
didn't fall short of reality ended up basically duplicating the amount
of writes & fsyncs, even if not going through the WAL.

You need to be crash safe/restartable (=> writes, fsyncs) and you need to
reduce the writes (in memory, => !writes). There is only one
authoritative point where you can rely on a commit to have been
successfull and thats when the commit record has been written to the
WAL. You can't send out the data to be committed before thats written
because that could result in spuriously committed transactions on the
remote side and you can't easily do it afterwards because you can crash
after the commit.

Just curious here, but do you know how is this part solved in current sync
wal replication - you can get "spurious" commits on slave side id master
dies while waiting for confirmation.

Synchronous replication is only synchronous in respect to the COMMIT reply sent
to the user. First the commit is written to WAL locally, so it persists across
a crash (c.f. RecordTransactionCommit). Only then we wait for the standby
(SyncRepWaitForLSN). After that finished the shared memory on the primary gets
updated (c.f. ProcArrayEndTransaction in CommitTransaction) and soon after that
the user gets the response to the COMMIT back.

I am not really sure what you were asking for, does the above explanation
answer this?

I think I mostly got it if master crashes before the commit confirmation
comes back then it _will_ get it after restart.

To client it looks like it doid not commit, but it is no different in this
respect than any other crash-before-confirmation and thus client can
not rely on commit not happening and has to check it.


Greetings,

Andres




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Andres Freund]

On Monday, October 15, 2012 08:46:40 PM Jeff Janes wrote:
> On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
> > I would be in favor of moving them to contrib for 9.4.  Assuming that
> > someone can figure out how this interacts with the existing system table
> > opclasses.  Them being in /contrib would also put less pressure on the
> > next new hacker who decides to take them on as a feature; they can
> > improve them incrementally without needing to fix 100% of issues in the
> > first go.
> 
> Is there anything currently in contrib that defines its own WAL
> records and replay methods?  Are there hooks for doing so?

It's not really possible as rmgr.c declares a const array of resource managers. 
A contrib module can't sensibly add itself to that. I think changing this has 
been discussed/proposed in the past, but -hackers wasn't convinced...

But then, the idea is to add it to -contrib while no WAL support exists..

Personally I don't see a point in -contrib'ing it. I would rather see it throw 
errors in dangerous situations and be done with that.

Regards,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Hannu Krosing]

On 10/15/2012 04:54 AM, Robert Haas wrote:

On Thu, Oct 11, 2012 at 3:15 AM, Heikki Linnakangas
 wrote:

IMHO that's a good thing, and I'd hope this new logical replication to live
outside core as well, as much as possible. But whether or not something is
in core is just a political decision, not a reason to implement something
new.

If the only meaningful advantage is reducing the amount of WAL written, I
can't help thinking that we should just try to address that in the existing
solutions, even if it seems "easy to solve at a first glance, but a solution
not using a normal transactional table for its log/queue has to solve a lot
of problems", as the document says. Sorry to be a naysayer, but I'm pretty
scared of all the new code and complexity these patches bring into core.

I think what we're really missing at the moment is a decent way of
decoding WAL.  There are a decent number of customers who, when
presented with replication system, start by asking whether it's
trigger-based or WAL-based.  When you answer that it's trigger-based,
their interest goes... way down.  If you tell them the triggers are
written in anything but C, you lose a bunch more points.  Sure, some
people's concerns are overblown, but it's hard to escape the
conclusion that a WAL-based solution can be a lot more efficient than
a trigger-based solution, and EnterpriseDB has gotten comments from a
number of people who upgraded to 9.0 or 9.1 to the effect that SR was
way faster than Slony.

I do not personally believe that a WAL decoding solution adequate to
drive logical replication can live outside of core, at least not
unless core exposes a whole lot more interface than we do now, and
probably not even then.  Even if it could, I don't see the case for
making every replication solution reinvent that wheel.  It's a big
wheel to be reinventing, and everyone needs pretty much the same
thing.

That having been said, I have to agree that the people working on this
project seem to be wearing rose-colored glasses when it comes to the
difficulty of implementing a full-fledged solution in core.  I'm right
on board with everything up to the point where we start kicking out a
stream of decoded changes to the user... and that's about it.  To pick
on Slony for the moment, as the project that has been around for the
longest and has probably the largest user base (outside of built-in
SR, perhaps), they've got a project that they have been developing for
years and years and years.  What have they been doing all that time?
Maybe they are just stupid, but Chris and Jan and Steve don't strike
me that way, so I think the real answer is that they are solving
problems that we haven't even started to think about yet, especially
around control logic: how do you turn it on?  how do you turn it off?
how do you handle node failures?  how do you handle it when a node
gets behind?  We are not going to invent good solutions to all of
those problems between now and January, or even between now and next
January.

I understand the the current minimal target is to get on par to current WAL
streaming in terms of setup ease and performance with additional
benefit of having read-write subscribers with at least conflict detection
and logging.

Hoping that we have something by january that solves all possible
replication scenarios out of the box is unrealistic.

PS. I'd love to see a basic Slony plugin for this, for example, to see how
much extra code on top of the posted patches you need to write in a plugin
like that to make it functional. I'm worried that it's a lot..

I agree.  I would go so far as to say that if Slony can't integrate
with this work and use it in place of their existing change-capture
facility, that's sufficient grounds for unconditional rejection.





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Jeff Janes]

On Mon, Oct 15, 2012 at 11:14 AM, Josh Berkus  wrote:
>
> I would be in favor of moving them to contrib for 9.4.  Assuming that
> someone can figure out how this interacts with the existing system table
> opclasses.  Them being in /contrib would also put less pressure on the
> next new hacker who decides to take them on as a feature; they can
> improve them incrementally without needing to fix 100% of issues in the
> first go.

Is there anything currently in contrib that defines its own WAL
records and replay methods?  Are there hooks for doing so?

Cheers,

Jeff


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Monday, October 15, 2012 08:38:07 PM Hannu Krosing wrote:
> On 10/11/2012 01:42 PM, Andres Freund wrote:
> > On Thursday, October 11, 2012 09:15:47 AM Heikki Linnakangas wrote:
> > ...
> > If the only meaningful advantage is reducing the amount of WAL written,
> > I can't help thinking that we should just try to address that in the
> > existing solutions, even if it seems "easy to solve at a first glance,
> > but a solution not using a normal transactional table for its log/queue
> > has to solve a lot of problems", as the document says.
> > Youre welcome to make suggestions, but everything I could think of that
> > didn't fall short of reality ended up basically duplicating the amount
> > of writes & fsyncs, even if not going through the WAL.
> > 
> > You need to be crash safe/restartable (=> writes, fsyncs) and you need to
> > reduce the writes (in memory, => !writes). There is only one
> > authoritative point where you can rely on a commit to have been
> > successfull and thats when the commit record has been written to the
> > WAL. You can't send out the data to be committed before thats written
> > because that could result in spuriously committed transactions on the
> > remote side and you can't easily do it afterwards because you can crash
> > after the commit.
> 
> Just curious here, but do you know how is this part solved in current sync
> wal replication - you can get "spurious" commits on slave side id master
> dies while waiting for confirmation.

Synchronous replication is only synchronous in respect to the COMMIT reply sent 
to the user. First the commit is written to WAL locally, so it persists across 
a crash (c.f. RecordTransactionCommit). Only then we wait for the standby 
(SyncRepWaitForLSN). After that finished the shared memory on the primary gets 
updated (c.f. ProcArrayEndTransaction in CommitTransaction) and soon after that 
the user gets the response to the COMMIT back.

I am not really sure what you were asking for, does the above explanation 
answer this?

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Hannu Krosing]

On 10/11/2012 01:42 PM, Andres Freund wrote:

On Thursday, October 11, 2012 09:15:47 AM Heikki Linnakangas wrote:
...
If the only meaningful advantage is reducing the amount of WAL written,
I can't help thinking that we should just try to address that in the
existing solutions, even if it seems "easy to solve at a first glance,
but a solution not using a normal transactional table for its log/queue
has to solve a lot of problems", as the document says.
Youre welcome to make suggestions, but everything I could think of that didn't
fall short of reality ended up basically duplicating the amount of writes &
fsyncs, even if not going through the WAL.

You need to be crash safe/restartable (=> writes, fsyncs) and you need to
reduce the writes (in memory, => !writes). There is only one authoritative
point where you can rely on a commit to have been successfull and thats when
the commit record has been written to the WAL. You can't send out the data to
be committed before thats written because that could result in spuriously
committed transactions on the remote side and you can't easily do it afterwards
because you can crash after the commit.

Just curious here, but do you know how is this part solved in current sync
 wal replication - you can get "spurious" commits on slave side id master
dies while waiting for confirmation.
What complications are you imagining? Greetings, Andres 


Hannu


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Andres Freund]

On Monday, October 15, 2012 08:19:54 PM Bruce Momjian wrote:
> On Mon, Oct 15, 2012 at 09:57:19AM +0200, Andres Freund wrote:
> > On Monday, October 15, 2012 04:54:20 AM Robert Haas wrote:
> > > On Thu, Oct 11, 2012 at 3:15 AM, Heikki Linnakangas
> > > 
> > >  wrote:
> > > > IMHO that's a good thing, and I'd hope this new logical replication
> > > > to live outside core as well, as much as possible. But whether or
> > > > not something is in core is just a political decision, not a reason
> > > > to implement something new.
> > > > 
> > > > If the only meaningful advantage is reducing the amount of WAL
> > > > written, I can't help thinking that we should just try to address
> > > > that in the existing solutions, even if it seems "easy to solve at a
> > > > first glance, but a solution not using a normal transactional table
> > > > for its log/queue has to solve a lot of problems", as the document
> > > > says. Sorry to be a naysayer, but I'm pretty scared of all the new
> > > > code and complexity these patches bring into core.
> > > 
> > > I do not personally believe that a WAL decoding solution adequate to
> > > drive logical replication can live outside of core, at least not
> > > unless core exposes a whole lot more interface than we do now, and
> > > probably not even then.  Even if it could, I don't see the case for
> > > making every replication solution reinvent that wheel.  It's a big
> > > wheel to be reinventing, and everyone needs pretty much the same
> > > thing.
> > 
> > Unsurprisingly I aggree.
> > 
> > > That having been said, I have to agree that the people working on this
> > > project seem to be wearing rose-colored glasses when it comes to the
> > > difficulty of implementing a full-fledged solution in core.
> > 
> > That very well might be true. Sometimes rose-colored glasses can be quite
> > productive in getting something started...
> > 
> > Note at this point were only want wal decoding, background workers and
> > related things to get integrated...
> 
> Well, TODO does have:
> 
>   Move pgfoundry's xlogdump to /contrib and have it rely more closely on
>   the WAL backend code

Uhm. How does that relate to my statement?

The xlogreader code I submitted does contain a very small POC xlogdump with 
almost no code duplication. It needs some work to be really useful though.

> I think Robert is right that if Slony can't use the API, it is unlikely
> any other replication system could use it.

I aggree and I don't think I have ever said something contrary. I just don't 
want to be the only one working on slony integration. I am ready to do a good 
part of that, but somebody with slony experience needs to help, especially on 
consuming those changes.

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Fix for log_line_prefix and session display

[Bruce Momjian]

On Mon, Oct 15, 2012 at 10:01:29AM +0200, Albe Laurenz wrote:
> Bruce Momjian wrote:
> > Currently, our session id, displayed by log_line_prefix and CSV
> output,
> > is made up of the session start time epoch seconds and the process id.
> > The problem is that the printf mask is currently %lx.%x, causing a
> > process id less than 4096 to not display a full four hex digits after
> > the decimal point.  I think this is confusing because the number .423
> > appears higher than .1423, though it is not.  Here is what our current
> > output looks like with log_line_prefix="%c: ":
> > 
> > 50785b3e.7ff9: ERROR:  syntax error at or near "test" at
> character 1
> > 50785b3e.7ff9: STATEMENT:  test
> > 50785b3e.144: ERROR:  syntax error at or near "test" at
> character 1
> > 50785b3e.144: STATEMENT:  test
> > 
> > With my fix, here is the updated output:
> > 
> > 507864d3.7ff2: ERROR:  syntax error at or near "test" at
> character 1
> > 507864d3.7ff2: STATEMENT:  test
> > 507864d3.013d: ERROR:  syntax error at or near "test" at
> character 1
> > 507864d3.013d: STATEMENT:  test
> > 
> > Patch attached.
> 
> Do you think that anybody wants to apply a linear ordering on
> the second part of the session ID?  If you need the pid, you
> can use %p.
> 
> I would say that this change makes sense if it causes disturbance
> that the part after the period can be than 4 characters long
> (it did not disturb me when I wrote a log file parser).
> 
> If that need is not urgent enough, maybe it would be better to
> preserve the current behaviour in the (unlikely) event that somebody
> relies on it.

I don't think anyone is picking apart the session id, but I do think the
current output is confusing because the session id string length is
pretty variable.  Anyone who is parsing the current session id will
easily be able to parse the more consistent output.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Andres Freund]

On Monday, October 15, 2012 08:14:51 PM Josh Berkus wrote:
> > * Put WARNINGs in the docs against the use of hash indexes, backpatch
> > to 8.3. CREATE INDEX gives no warning currently, though Index Types
> > does mention a caution.
> 
> I'd be in favor of a warning on create index.
> 
> Also, are hash indexes replicated?

No. As they aren't WAL logged they can't be transported via wal based 
replication (PITR/HS/SR). Which rather quickly results in a very broken setup, 
there has been at least one bug on -bugs because of this and there have been 
several people in the irc channel experiencing this.

> > * Mention in the current docs that hash indexes are likely to be
> > deprecated completely in future releases. Should anybody ever make
> > them work, we can change that advice quickly but I don't think we're
> > going to.
> 
> I'm not sure that's true, necessarily.  The nice thing about work on
> hash indexes is that it's potentially rather self-contained, i.e. a good
> GSOC project.  However ...

While self contained I fear you still need quite a bit more knowledge than 
usual students have.

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)

[Bruce Momjian]

On Mon, Oct 15, 2012 at 09:57:19AM +0200, Andres Freund wrote:
> On Monday, October 15, 2012 04:54:20 AM Robert Haas wrote:
> > On Thu, Oct 11, 2012 at 3:15 AM, Heikki Linnakangas
> > 
> >  wrote:
> > > IMHO that's a good thing, and I'd hope this new logical replication to
> > > live outside core as well, as much as possible. But whether or not
> > > something is in core is just a political decision, not a reason to
> > > implement something new.
> > > 
> > > If the only meaningful advantage is reducing the amount of WAL written, I
> > > can't help thinking that we should just try to address that in the
> > > existing solutions, even if it seems "easy to solve at a first glance,
> > > but a solution not using a normal transactional table for its log/queue
> > > has to solve a lot of problems", as the document says. Sorry to be a
> > > naysayer, but I'm pretty scared of all the new code and complexity these
> > > patches bring into core.
> 
> > I do not personally believe that a WAL decoding solution adequate to
> > drive logical replication can live outside of core, at least not
> > unless core exposes a whole lot more interface than we do now, and
> > probably not even then.  Even if it could, I don't see the case for
> > making every replication solution reinvent that wheel.  It's a big
> > wheel to be reinventing, and everyone needs pretty much the same
> > thing.
> Unsurprisingly I aggree.
> 
> > That having been said, I have to agree that the people working on this
> > project seem to be wearing rose-colored glasses when it comes to the
> > difficulty of implementing a full-fledged solution in core. 
> That very well might be true. Sometimes rose-colored glasses can be quite 
> productive in getting something started...
> 
> Note at this point were only want wal decoding, background workers and 
> related 
> things to get integrated...

Well, TODO does have:

Move pgfoundry's xlogdump to /contrib and have it rely more closely on
the WAL backend code 

I think Robert is right that if Slony can't use the API, it is unlikely
any other replication system could use it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Josh Berkus]

Simon,

> * Put WARNINGs in the docs against the use of hash indexes, backpatch
> to 8.3. CREATE INDEX gives no warning currently, though Index Types
> does mention a caution.

I'd be in favor of a warning on create index.

Also, are hash indexes replicated?

> * Mention in the current docs that hash indexes are likely to be
> deprecated completely in future releases. Should anybody ever make
> them work, we can change that advice quickly but I don't think we're
> going to.

I'm not sure that's true, necessarily.  The nice thing about work on
hash indexes is that it's potentially rather self-contained, i.e. a good
GSOC project.  However ...

> Personally, I would like to see them removed into a contrib module to
> allow people to re-add them if they understand the risks. ISTM better
> to confiscate all foot-guns before they go off and then re-issue them
> to marksmen, at the risk of annoying the people that use them with
> full knowledge but that's likely a contentious issue.

I would be in favor of moving them to contrib for 9.4.  Assuming that
someone can figure out how this interacts with the existing system table
opclasses.  Them being in /contrib would also put less pressure on the
next new hacker who decides to take them on as a feature; they can
improve them incrementally without needing to fix 100% of issues in the
first go.

So, +1 with modifications ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Josh Berkus]

Simon, Peter, etc.:

Perhaps we should take a different tack on this discussion: what feature
development is the continued presense of RULES currently blocking?  If
the rest of us had some idea why you considered this deprecation urgent,
it would help!

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stat_lwlocks view - lwlocks statistics, round 2

[Jeff Janes]

On Sun, Oct 14, 2012 at 9:43 AM, Tom Lane  wrote:
> Satoshi Nagayasu  writes:
>> (2012/10/14 13:26), Fujii Masao wrote:
>>> The tracing lwlock usage seems to still cause a small performance
>>> overhead even if reporting is disabled. I believe some users would
>>> prefer to avoid such overhead even if pg_stat_lwlocks is not available.
>>> It should be up to a user to decide whether to trace lwlock usage, e.g.,
>>> by using trace_lwlock parameter, I think.
>
>> Frankly speaking, I do not agree with disabling performance
>> instrument to improve performance. DBA must *always* monitor
>> the performance metrix when having such heavy workload.
>
> This brings up a question that I don't think has been honestly
> considered, which is exactly whom a feature like this is targeted at.
> TBH I think it's of about zero use to DBAs (making the above argument
> bogus).  It is potentially of use to developers, but a DBA is unlikely
> to be able to do anything about lwlock-level contention even if he has
> the knowledge to interpret the data.

Waiting on BufFreelistLock suggests increasing shared_buffers.

Waiting on ProcArrayLock perhaps suggests use of a connection pooler
(or does it?)

WALWriteLock suggests doing something about IO, either moving logs to
different disks, or getting BBU, or something.

WALInsertLock suggests trying to adapt your data loading process so it
can take advantage of the bulk, or maybe increasing wal_buffers.

And a lot of waiting on any of the locks gives a piece of information
the DBA can use when asking the mailing lists for help, even if it
doesn't allow him to take unilateral action.

> So I feel it isn't something that should be turned on in production
> builds.  I'd vote for enabling it by a non-default configure option,
> and making sure that it doesn't introduce any overhead when the option
> is off.

I think hackers would benefit from getting reports from DBAs in the
field with concrete data on bottlenecks.

If the only way to get this is to do some non-standard compile and
deploy it to production, or to create a "benchmarking" copy of the
production database system including a realistic work-load driver and
run the non-standard compile there; either of those is going to
dramatically cut down on the participation.

Cheers,

Jeff


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[k...@rice.edu]

On Mon, Oct 15, 2012 at 10:13:24AM -0400, Robert Haas wrote:
> On Sun, Oct 14, 2012 at 9:45 AM, Simon Riggs  wrote:
> > * Put WARNINGs in the docs against the use of hash indexes, backpatch
> > to 8.3. CREATE INDEX gives no warning currently, though Index Types
> > does mention a caution.
> 
> I'd be in favor of adding such warnings to the documentation if they
> are not there already, and possibly even warning on CREATE INDEX ..
> USING hash().  I don't think I'd go so far as to say that we should
> imply that they'll be removed in a future release.  Given how deeply
> intertwined they are with the planner, I doubt that that will happen;
> and I think there is enough interest in the technology that it's
> likely to eventually be fixed.
> 
+1 for adding more warnings but do not deprecate them.

Regards,
Ken


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Potential autovacuum optimization: new tables

[Josh Berkus]

Robert,

> As I've said before (and I believe Simon has said similar things), I
> think we should be vacuuming the heap much more often but only doing
> index vac when we accumulate enough dead tuples to justify the cost of
> the index scan.  Pruning the heap is cheap and very effective.

You are probably correct, especially since the testing which set the
current thresholds was pre-HOT, even.

We don't have a vacuum method which allows us to vacuum the heap but not
the indexes, though, do we?

Note that the only time I encounter chronic autovacuum issues, it's for
"problem" tables like queue tables, and no default is going to cope with
those.

Anyway, big thing is, we need to be able to test this.

> That's a pretty funny-looking curve, because it doubles between 10 and
> 100 but then increases 10x between 100 and 1000.  It's similarly
> erratic further on.  But I do agree that some kind of log scale might
> be appropriate.

Yeah, it's mainly the endpoints I'm sure of based on experience.  The
middle should be a smooth curve between them, if possible.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Simon Riggs]

On 15 October 2012 18:07, Robert Haas  wrote:
> On Mon, Oct 15, 2012 at 12:59 PM, Simon Riggs  wrote:
>>> I don't think I'd go so far as to say that we should
>>> imply that they'll be removed in a future release.  Given how deeply
>>> intertwined they are with the planner, I doubt that that will happen;
>>> and I think there is enough interest in the technology that it's
>>> likely to eventually be fixed.
>>
>> Hash indexes aren't used in the planner. Hash joins use completely
>> separate code.
>
> It's not really completely separate, because to do a hash join we have
> to find a hash function for the relevant data types, and IIUC we do
> that by looking up the default hash opclass for the datatype and
> finding its first support function.  Of course, if we were to remove
> the hash AM, then you couldn't define a hash opclass against it.

Presumably it defaults to hash_any() but I get the picture.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Robert Haas]

On Mon, Oct 15, 2012 at 12:53 PM, Christopher Browne  wrote:
> The places where *I* care about this are places where performance is
> almost entirely irrelevant to the question.
>
> When I'm writing 'scripts' that are doing this kind of thing, I'm
> doing schema 'surgery', and, within reason, it's not particularly
> performance sensitive.  I'm much more worried about DDL scripts being
> repeatable and manageable than I am about them being fast.
>
> So I'm going to elide the performance bits.
>
> Robert, when you first tossed out the notion of:
>
> do $$
> begin
> if (select 1 from pg_class where relname = 'foo' and
> pg_table_is_visible(oid)) then
> truncate table foo;
> end if;
> end
> $$;
>
> my first reaction was "Ick!   Why am I switching languages (e.g. -
> from plain SQL to pl/pgsql), and running functions to do this?!?"
>
> In retrospect, your later comments make it pretty clear that you're
> not proposing that as the end state, just that that's the
> functionality that needs to be run.

Yeah, I think the functionality that we need is pretty much there
already today.  What we need to do is to get the syntax to a point
where people can write the code they want to write without getting
tangled up by it.

I think the invention of DO was a big step in the right direction,
because before that if you wanted procedural logic in your script, you
had to create a function, call it, and then drop the function.  That
is exceedingly awkward and introduces a number of unpleasant and
unnecessary failure modes.  With DO, you can write the logic you want
as an SQL statement, it's just a clunky and awkward SQL statement.  In
my view the goal ought to be to refine that mechanism to remove the
clunkiness and awkwardness, rather than to invent something completely
new.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Andres Freund]

On Monday, October 15, 2012 07:03:35 PM Simon Riggs wrote:
> On 15 October 2012 15:19, Andres Freund said...
> 
> > I vote for at least logging a wal record when a hash index is modified
> > which uses incomplete actions to set 'indisready = false' in case its
> > replayed. That should only use a rather minor amount of code and should
> > help users to find problems faster.
> 
> Good idea, though might be harder than it first appears.

> How do we issue just one of those per checkpoint, to minimise WAL?

I was thinking per checkpoint, per backend in order to not add any new locks.

> How do we make that change with a physical update WAL? Non-transactional
> update? During recovery?

Thats why I suggested using the incomplete actions/cleanup stuff, so we can do 
the change when replay finished. Thats not enough for HS though... Can we get 
away with putting a if (RecoveryInProgress()) ereport(...) in there?

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Hash id in pg_stat_statements

[Daniel Farina]

On Mon, Oct 15, 2012 at 7:35 AM, Peter Geoghegan  wrote:
> On 3 October 2012 19:04, Tom Lane  wrote:
>> Daniel Farina  writes:
>>> Instead, I think it makes sense to assign a number -- arbitrarily, but
>>> uniquely -- to the generation of a new row in pg_stat_statements, and,
>>> on the flip side, whenever a row is retired its number should be
>>> eliminated, practically, for-ever.  This way re-introductions between
>>> two samplings of pg_stat_statements cannot be confused for a
>>> contiguously maintained statistic on a query.
>>
>> This argument seems sensible to me.
>
> Daniel: Could you please submit the patch that you were working on
> that does this to the next commitfest?

Yes. Sorry I haven't done that already.  I'll clean it up and send it
out Real Soon Now, thanks for the expression of interest.

-- 
fdr


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Robert Haas]

On Mon, Oct 15, 2012 at 12:59 PM, Simon Riggs  wrote:
>> I don't think I'd go so far as to say that we should
>> imply that they'll be removed in a future release.  Given how deeply
>> intertwined they are with the planner, I doubt that that will happen;
>> and I think there is enough interest in the technology that it's
>> likely to eventually be fixed.
>
> Hash indexes aren't used in the planner. Hash joins use completely
> separate code.

It's not really completely separate, because to do a hash join we have
to find a hash function for the relevant data types, and IIUC we do
that by looking up the default hash opclass for the datatype and
finding its first support function.  Of course, if we were to remove
the hash AM, then you couldn't define a hash opclass against it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Simon Riggs]

On 15 October 2012 15:19, Andres Freund said...

> I vote for at least logging a wal record when a hash index is modified which
> uses incomplete actions to set 'indisready = false' in case its replayed. That
> should only use a rather minor amount of code and should help users to find
> problems faster.

Good idea, though might be harder than it first appears.

How do we issue just one of those per checkpoint, to minimise WAL? How
do we make that change with a physical update WAL? Non-transactional
update? During recovery?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Simon Riggs]

On 15 October 2012 15:13, Robert Haas  wrote:
> On Sun, Oct 14, 2012 at 9:45 AM, Simon Riggs  wrote:
>> * Put WARNINGs in the docs against the use of hash indexes, backpatch
>> to 8.3. CREATE INDEX gives no warning currently, though Index Types
>> does mention a caution.
>
> I'd be in favor of adding such warnings to the documentation if they
> are not there already, and possibly even warning on CREATE INDEX ..
> USING hash().

Sounds like a good idea.

> I don't think I'd go so far as to say that we should
> imply that they'll be removed in a future release.  Given how deeply
> intertwined they are with the planner, I doubt that that will happen;
> and I think there is enough interest in the technology that it's
> likely to eventually be fixed.

Hash indexes aren't used in the planner. Hash joins use completely
separate code.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Christopher Browne]

On Mon, Oct 15, 2012 at 11:57 AM, Robert Haas  wrote:
> On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark  wrote:
>> I'm a bit lost. I would think pl/pgsql is precisely the same as
>> Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
>> purely implementation detail. I don't think pl/pgsql is the best
>> implemented part of Postgres but I don't see how integrating it into
>> the core is going to automatically make it all wonderful either.
>
> It isn't.  But (1) there would be a significant usability benefit in
> not having to surround procedural logic with DO $$ BEGIN ... END $$
> and (2) PL/pgsql's performance issues seem to revolve around the fact
> that you don't get one big ol' plan thingy that can be passed to the
> executor and run; instead, you interpret each statement separately and
> pass them off to the executor one piece at a time.

The places where *I* care about this are places where performance is
almost entirely irrelevant to the question.

When I'm writing 'scripts' that are doing this kind of thing, I'm
doing schema 'surgery', and, within reason, it's not particularly
performance sensitive.  I'm much more worried about DDL scripts being
repeatable and manageable than I am about them being fast.

So I'm going to elide the performance bits.

Robert, when you first tossed out the notion of:

do $$
begin
if (select 1 from pg_class where relname = 'foo' and
pg_table_is_visible(oid)) then
truncate table foo;
end if;
end
$$;

my first reaction was "Ick!   Why am I switching languages (e.g. -
from plain SQL to pl/pgsql), and running functions to do this?!?"

In retrospect, your later comments make it pretty clear that you're
not proposing that as the end state, just that that's the
functionality that needs to be run.

That would would be equivalent to my would-be-strawman syntax of:

TRUNCATE TABLE public.foo IF EXISTS TABLE public.foo;

I'm comfortable that Dimitri didn't particularly love the idea of
stowing the conditional at the end; it was just a strawman proposal,
and what was particularly important to me was to make sure that it was
recognizable that other systems (e.g. - Perl, Ruby, probably SNOBOL)
have done the very same thing.  I'd be perfectly happy if someone came
up with something better.  The number of "+1"'s thus far is pretty
gratifying, mind you.

>> Fwiw my experience has consistently been that life got better whenever
>> I moved anything I had implemented as PL/SQL or PL/pgsql into client
>> code in Perl or Python.
>
> Hmm... I've had the opposite experience, which I guess is why I've got
> strong feelings about this.

When I'm "managing schema", I have exactly *zero* interest in
switching over to Perl or Python.  Those aren't languages for managing
database schemas, and, if I wind up using them, my code is going to be
rife with context switches as I'm switching between
   "oh, am I writing Perl code?"
and
   "Am I attached to the right Perl database connection object, with
the proper transaction context?"
and
   "Oh, here is the SQL DDL for managing the schema."

Two of these three varieties of contexts are distracting sidelines to
me.  Guess which are the two?  :-)
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Potential autovacuum optimization: new tables

[Robert Haas]

On Sat, Oct 13, 2012 at 3:49 PM, Joshua Berkus  wrote:
> For my part, over the last 3 years of consulting and dealing with 
> postgresql.conf settings for more than 140 clients:
>
> * only 10% of them ever touched the autoanalyze settings at all
> * of the ~~ 14 who did:
>* 1 improved the tuning of their database
>* 3 of them messed up autoanalyze, causing stats and vacuum issues
>* ~~ 10 had no measurable effect
>
> ... so you'll understand when I say that I don't think ease of knob-twiddling 
> is a priority for autoanalyze design.  In fact, I'd say that removing the 
> knobs entirely is a design goal.

Yeah.  My experience is shorter in time frame, but similar in composition.

> I've been going over the notes and email archives from the period where Matt 
> O'Connor and I arrived at the current settings.  All of our testing was 
> devoted to autovacuum, not autoanalyze.  The threshold+scale_factor design 
> works pretty well for autovacuum; it prevents us from constantly vacuuming 
> small tables, or large tables with less than 20% dead rows.  And I did 
> extensive testing using DBT2 on OSDL to set the current defaults.

However, I disagree with this.  I think that things have changed a lot
in 8.4+, because of the visibility map.  Extra vacuuming is not nearly
so expensive as it used to be, and essentially 100% of the vacuum
problems I see are caused by not vacuuming frequently enough, either
because (1) when vacuum does eventually run it imposes a gigantic
server load for a really long time or (2) too much bloat builds up
between vacuum runs.   If these settings were adjusted in an exactly
middle-of-the-road fashion, I ought to see 50% of the problems from
vacuuming too often and the other 50% from not vacuuming often enough.
 The reality is nothing like that; it's all on one side.

As I've said before (and I believe Simon has said similar things), I
think we should be vacuuming the heap much more often but only doing
index vac when we accumulate enough dead tuples to justify the cost of
the index scan.  Pruning the heap is cheap and very effective.

> Our mistake was assuming that the same formula which worked well for vacuum 
> would work well for analyze.  And since the DBT2 database has entirely 
> medium-sized tables full of random data, no shortcomings in this thinking 
> showed up in the tests.  Since the only counterproposal at the time was to 
> have a flat percentage without a threshold, we got the current defaults.
>
> So, problem #1 is coming up with a mathematical formula.  My initial target 
> values are in terms of # of rows in the table vs. # of writes before analyze 
> is triggered:
>
> 1 : 3
> 10 : 5
> 100 : 10
> 1000 : 100
> 10 : 2000
> 100 : 5000
> 1000 : 25000
> 1 : 10
>
>  etc.  So problem #1 is a mathematical formula which gives this kind of 
> curve.  I've tried some solution-seeking software, but I don't know how to 
> use it well enough to get something useful.

That's a pretty funny-looking curve, because it doubles between 10 and
100 but then increases 10x between 100 and 1000.  It's similarly
erratic further on.  But I do agree that some kind of log scale might
be appropriate.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown

[Fujii Masao]

On Mon, Oct 15, 2012 at 11:27 PM, Heikki Linnakangas
 wrote:
> On 15.10.2012 13:13, Heikki Linnakangas wrote:
>>
>> On 13.10.2012 19:35, Fujii Masao wrote:
>>>
>>> ISTM you need to update the protocol.sgml because you added
>>> the field 'replyRequested' to WalSndrMessage and StandbyReplyMessage.
>>
>>
>> Oh, I didn't remember that we've documented the specific structs that we
>> pass around. It's quite bogus anyway to explain the messages the way we
>> do currently, as they are actually dependent on the underlying
>> architecture's endianess and padding. I think we should refactor the
>> protocol to not transmit raw structs, but use pq_sentint and friends to
>> construct the messages. This was discussed earlier (see
>>
>> http://archives.postgresql.org/message-id/4fe2279c.2070...@enterprisedb.com),
>> I think there's consensus that 9.3 would be a good time to do that as we
>> changed the XLogRecPtr format anyway.
>
>
> This is what I came up with. The replication protocol is now
> architecture-independent. The WAL format itself is still
> architecture-independent, of course, but this is useful if you want to e.g
> use pg_receivexlog to back up a server that runs on a different platform.
>
> I chose the int64 format to transmit timestamps, even when compiled with
> --disable-integer-datetimes.
>
> Please review if you have the time..

Thanks for the patch!

When I ran pg_receivexlog, I encountered the following error.

$ pg_receivexlog -D hoge
pg_receivexlog: unexpected termination of replication stream: ERROR:
no data left in message

pg_basebackup -X stream caused the same error.

$ pg_basebackup -D hoge -X stream -c fast
pg_basebackup: could not send feedback packet: no COPY in progress
pg_basebackup: child process exited with error 1

In walreceiver.c, tmpbuf is allocated for every XLogWalRcvProcessMsg() call.
It should be allocated just once and continue to be used till end, to reduce
palloc overhead?

+   hdrlen = sizeof(int64) + sizeof(int64) + 
sizeof(int64);
+   hdrlen = sizeof(int64) + sizeof(int64) + 
sizeof(char);

These should be macro, to avoid calculation overhead?

+   /* Construct the the message and send it. */
+   resetStringInfo(&reply_message);
+   pq_sendbyte(&reply_message, 'h');
+   pq_sendint(&reply_message, xmin, 4);
+   pq_sendint(&reply_message, nextEpoch, 4);
+   walrcv_send(reply_message.data, reply_message.len);

You seem to have forgotten to send the sendTime.

Regards,

-- 
Fujii Masao


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stat_lwlocks view - lwlocks statistics, round 2

[Satoshi Nagayasu]

2012/10/15 1:43, Tom Lane wrote:
> Satoshi Nagayasu  writes:
>> (2012/10/14 13:26), Fujii Masao wrote:
>>> The tracing lwlock usage seems to still cause a small performance
>>> overhead even if reporting is disabled. I believe some users would
>>> prefer to avoid such overhead even if pg_stat_lwlocks is not available.
>>> It should be up to a user to decide whether to trace lwlock usage, e.g.,
>>> by using trace_lwlock parameter, I think.
> 
>> Frankly speaking, I do not agree with disabling performance
>> instrument to improve performance. DBA must *always* monitor
>> the performance metrix when having such heavy workload.
> 
> This brings up a question that I don't think has been honestly
> considered, which is exactly whom a feature like this is targeted at.
> TBH I think it's of about zero use to DBAs (making the above argument
> bogus).  It is potentially of use to developers, but a DBA is unlikely
> to be able to do anything about lwlock-level contention even if he has
> the knowledge to interpret the data.

Actually, I'm not a developer. I'm just a DBA, and I needed such
instrument when I was asked to investigate storange WAL behavior
that produced unexpected/random commit delays under heavy workload.

And another patch (WAL dirty flush statistic patch) I have submitted
is coming from the same reason.

https://commitfest.postgresql.org/action/patch_view?id=893

Unfortunately, since I didn't have such instrument at that time,
I used SystemTap to investigate WAL behaviors, including calls and
waited time, but using SystemTap was really awful, and I thought
PostgreSQL needs to have some "built-in" instrument for DBA.

I needed to determine the bottleneck around WAL, such as lock contension
and/or write performance of the device, but I couldn't find anything
without an instrument.

I accept that I'm focusing on only WAL related lwlocks, and it is not
enough for ordinally DBAs, but I still need it to understand PostgreSQL
behavior without having deep knowledge and experience on WAL design and
implementation.

> So I feel it isn't something that should be turned on in production
> builds.  I'd vote for enabling it by a non-default configure option,
> and making sure that it doesn't introduce any overhead when the option
> is off.

There is another option to eliminate performance overhead for this
purpose.

As I tried in the first patch, instead of reporting through pgstat
collector process, each backend could directly increment lwlock
counters allocated in the shared memory.

http://archives.postgresql.org/message-id/4fe9a6f5.2080...@uptime.jp

Here are another benchmark results, including my first patch.

[HEAD]
number of transactions actually processed: 3439971
tps = 57331.891602 (including connections establishing)
tps = 57340.932324 (excluding connections establishing)

[My first patch]
number of transactions actually processed: 3453745
tps = 57562.196971 (including connections establishing)
tps = 57569.197838 (excluding connections establishing)

Actually, I'm not sure why my patch makes PostgreSQL faster, :D
but the performance seems better than my second patch.

I think it still needs some trick to keep counters in "pgstat.stat"
over restarting, but it would be more acceptable in terms of
performance overhead.

Regards,
-- 
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Robert Haas]

On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark  wrote:
> I'm a bit lost. I would think pl/pgsql is precisely the same as
> Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
> purely implementation detail. I don't think pl/pgsql is the best
> implemented part of Postgres but I don't see how integrating it into
> the core is going to automatically make it all wonderful either.

It isn't.  But (1) there would be a significant usability benefit in
not having to surround procedural logic with DO $$ BEGIN ... END $$
and (2) PL/pgsql's performance issues seem to revolve around the fact
that you don't get one big ol' plan thingy that can be passed to the
executor and run; instead, you interpret each statement separately and
pass them off to the executor one piece at a time.

It wouldn't technically be necessary to integrate the code fully into
core into realize these benefits; you could maintain some abstraction
layer in between and provide an API to push information back and
forth.  But to take a trivial example, consider a FOR loop that
executes an enclosed SQL statement a large number of times.  Right
now, we build a plan tree for the SQL statement and then start up and
shut down the executor N times.  If we could instead push an "iterate"
not on top of the plan tree to handle the iteration, and then start up
the executor, run the plan, and shut down the executor, my guess is
that it would be way faster than our current implementation.
Everything I've seen leads me to believe that the executor is quite
zippy when it gets going, but bouncing in and out of it repeatedly
seems to be a source of real pain.

> Fwiw my experience has consistently been that life got better whenever
> I moved anything I had implemented as PL/SQL or PL/pgsql into client
> code in Perl or Python.

Hmm... I've had the opposite experience, which I guess is why I've got
strong feelings about this.  I've found that checking for uniqueness
violations without relying on the database doesn't really work due to
concurrency issues, and once I've got to catch that error from the
database side and expose it to the user as a nicely-formatted
complaint (the name you have chosen is already in use; please choose
another) I have found that it seems to make sense to push everything
other than the initial, relatively trivial syntax checking into
PostgreSQL.  Anyway, I think there's probably more than one sensible
design decision there and may come down to personal preference and
toolchain selection more than anything.

Whatever either of us think, though, the complaint at the top of this
thread indicates that people are NOT happy doing this on the client
side and DO isn't convenient enough either.  What do we do about that?
 I'm not extraordinarily attached to any specific proposal but I think
we should be looking for ways to make this better.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Hannu Krosing]

On 10/15/2012 12:41 PM, Greg Stark wrote:

On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs  wrote:

Please can anyone show me the SQL for a rule that cannot be written as
a view or a trigger? I do not believe such a thing exists and I will
provide free beer to the first person that can prove me wrong.

Being written as a view doesn't help you because views use rules. I
repeat, the very fact that we need rules to implement views prove
rules are necessary for some purposes.


We really don't *need* rules to implement views as proved by other
databases which do have views but don't have rules.

Rules were used for implementing views because they were already
there and that by constraining them to standard VIEW syntax we
could nicely cut back the footgunnyness of SELECT rules (by
disallowing the "bad" usage like replacing select by insert or doing
multiple selects instead of one). Some of that is still available when
you place a VIEW over a function, but then it is at least more explicit.

-
Hannu


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] odd alter_generic regression failures

[Tom Lane]

Andrew Dunstan  writes:
> We seem to have an intermittent failure on the alter_generic tests that 
> look like this:

>SET SESSION AUTHORIZATION regtest_alter_user1;
>CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql
>  AS 'SELECT $1 + 1';
> + ERROR:  permission denied for language sql
>CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql
>  AS 'SELECT $1 - 1';
> + ERROR:  permission denied for language sql
>CREATE AGGREGATE alt_agg1 (
>  sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 0
>);

I poked around and realized that the reason for this is that it's run in
parallel with the "privileges" test, which does:

REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
... various stuff ...
GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;

So the current placement of that test is no better than the original :-(
I'll put it someplace else.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Simon Riggs]

On 15 October 2012 14:43, Andrew Dunstan  wrote:
>
> On 10/15/2012 09:07 AM, Simon Riggs wrote:
>>
>> On 15 October 2012 11:41, Greg Stark  wrote:
>>>
>>> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs 
>>> wrote:

 Please can anyone show me the SQL for a rule that cannot be written as
 a view or a trigger? I do not believe such a thing exists and I will
 provide free beer to the first person that can prove me wrong.
>>>
>>> Being written as a view doesn't help you because views use rules. I
>>> repeat, the very fact that we need rules to implement views prove
>>> rules are necessary for some purposes.
>>
>> No, it just means there is some aspect of similar underlying
>> infrastructure.
>>
>> Denial of free beer looks like proof to me...
>
> *sigh*
>
> First, as Tom said, the onus of proof is on you. You can't transfer it away
> with this offer of free beer.

I'm aware that evidence of abstinence is not the same as absence of
evidence  - I was joking.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: forcing object owner in TOC file

[Piotr Gasidło]

2012/10/15 Andrew Dunstan :
> Sorry, but this doesn't strike me as a very good idea at all. Why not just
> alter the table ownership after the restore is done?

Yes, I could restore, wrote later ALTER ... OWNER TO ... - but this
method allowed me to do it quicker.

> If we start allowing
> stuff other than the TOC ID to be specified in the list file the
> modifications will never end.

Understood, sounds reasonably.

> BTW, I realize your patch is small, but it's usually a good idea to discuss
> an idea on the mailing list before sending in a patch.

I've new here, next time I will send idea and wait for response before
sending any patch.

-- 
Piotr Gasidło


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Hannu Krosing]

On 10/15/2012 04:34 PM, Greg Stark wrote:

On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas  wrote:

To be perfectly frank, I think that's exactly where we ought to be
going.  Oracle and Microsoft both did it, so why are we convinced it's
a bad idea?  One of the huge problems with PL/pgsql is that every SQL
expression in there has to be passed to the executor separately, which
is painfully slow.

I'm a bit lost. I would think pl/pgsql is precisely the same as
Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
purely implementation detail. I don't think pl/pgsql is the best
implemented part of Postgres but I don't see how integrating it into
the core is going to automatically make it all wonderful either.

Fwiw my experience has consistently been that life got better whenever
I moved anything I had implemented as PL/SQL or PL/pgsql into client
code in Perl or Python.

Just curious - why did you move it into _client_ code ?

Why not pl/perl or pl/python ?

Was performance not a concern and it was easier (administratively?) to 
manage it on the client side ?


-
Hannu







--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown

[Heikki Linnakangas]

On 15.10.2012 13:13, Heikki Linnakangas wrote:

On 13.10.2012 19:35, Fujii Masao wrote:

ISTM you need to update the protocol.sgml because you added
the field 'replyRequested' to WalSndrMessage and StandbyReplyMessage.


Oh, I didn't remember that we've documented the specific structs that we
pass around. It's quite bogus anyway to explain the messages the way we
do currently, as they are actually dependent on the underlying
architecture's endianess and padding. I think we should refactor the
protocol to not transmit raw structs, but use pq_sentint and friends to
construct the messages. This was discussed earlier (see
http://archives.postgresql.org/message-id/4fe2279c.2070...@enterprisedb.com),
I think there's consensus that 9.3 would be a good time to do that as we
changed the XLogRecPtr format anyway.


This is what I came up with. The replication protocol is now 
architecture-independent. The WAL format itself is still 
architecture-independent, of course, but this is useful if you want to 
e.g use pg_receivexlog to back up a server that runs on a different 
platform.


I chose the int64 format to transmit timestamps, even when compiled with 
--disable-integer-datetimes.


Please review if you have the time..

- Heikki
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 3d72a16..5a32517 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1366,7 +1366,8 @@ The commands accepted in walsender mode are:
   WAL data is sent as a series of CopyData messages.  (This allows
   other information to be intermixed; in particular the server can send
   an ErrorResponse message if it encounters a failure after beginning
-  to stream.)  The payload in each CopyData message follows this format:
+  to stream.)  The payload of each CopyData message from server to the
+  client contains a message of one of the following formats:
  
 
  
@@ -1390,34 +1391,32 @@ The commands accepted in walsender mode are:
   
   
   
-  Byte8
+  Int64
   
   
   
-  The starting point of the WAL data in this message, given in
-  XLogRecPtr format.
+  The starting point of the WAL data in this message.
   
   
   
   
   
-  Byte8
+  Int64
   
   
   
-  The current end of WAL on the server, given in
-  XLogRecPtr format.
+  The current end of WAL on the server.
   
   
   
   
   
-  Byte8
+  Int64
   
   
   
-  The server's system clock at the time of transmission,
-  given in TimestampTz format.
+  The server's system clock at the time of transmission, as
+  microseconds since midnight on 2000-01-01.
   
   
   
@@ -1445,25 +1444,12 @@ The commands accepted in walsender mode are:
continuation records can be sent in different CopyData messages.
  
  
-   Note that all fields within the WAL data and the above-described header
-   will be in the sending server's native format.  Endianness, and the
-   format for the timestamp, are unpredictable unless the receiver has
-   verified that the sender's system identifier matches its own
-   pg_control contents.
- 
- 
If the WAL sender process is terminated normally (during postmaster
shutdown), it will send a CommandComplete message before exiting.
This might not happen during an abnormal shutdown, of course.
  
 
  
-   The receiving process can send replies back to the sender at any time,
-   using one of the following message formats (also in the payload of a
-   CopyData message):
- 
-
- 
   
   
   
@@ -1495,12 +1481,23 @@ The commands accepted in walsender mode are:
   
   
   
-  Byte8
+  Int64
   
   
   
-  The server's system clock at the time of transmission,
-  given in TimestampTz format.
+  The server's system clock at the time of transmission, as
+  microseconds since midnight on 2000-01-01.
+  
+  
+  
+  
+  
+  Byte1
+  
+  
+  
+  1 means that the client should reply to this message as soon as
+  possible, to avoid a timeout disconnect. 0 otherwise.
   
   
   
@@ -1512,6 +1509,12 @@ The commands accepted in walsender mode are:
  
 
  
+   The receiving process can send replies back to the sender at any time,
+   using one of the following message formats (also in the payload of a
+   CopyData message):
+ 
+
+ 
   
   
   
@@ -1532,45 +1535,56 @@ The commands accepted in walsender mode are:
   
   
   
-  Byte8
+  Int64
   
   
   
   The location of the last WAL byte + 1 received and written to disk
-  in the standby, in XLogRecPtr f

Re: [HACKERS] Deparsing DDL command strings

[Dimitri Fontaine]

Robert Haas  writes:
> IMHO, it should be our explicit goal for clients not to need to parse
> any SQL at all.  I think that the communication between the server and
> event triggers should be accomplished using magic variables.  If the

+1 on that. There's a but.

> data is too complex to be structured that way, then I think we should
> use an established serialization format such as XML or (my personal
> preference) JSON for which many parsers already exist.  If we pass
> sanitized SQL, then everyone who wants to do anything complicated will
> need a parser for
> sanitized-SQL-as-generated-by-the-PostgreSQL-event-trigger-mechanism,
> and I think that's likely to be strictly worse than using one of the
> serialization methods that already exists and is well understood.

That's not an easy task by any means, we're talking about inventing a
stable alternative to the parse tree format (nodeToString) so that we
can whack the parsenodes as much as we need and still produce something
compatible for our users. Well at least we've already heard about more
than one use case, e.g. exposing the parser for syntax highlighting…

Now, if all you want to do is replay the exact same DDL on another
PostgreSQL instance, and if you happen to trust the master's server,
then the command string I'm currently spitting out is exactly what you
need. And having the more generalized representation of the parser data
would only mean that the trigger now has to rewrite the command string
itself.

> There's a careful line to be walked here, because in order for event
> triggers to be useful, we're going to have to pass them information -
> and the way we do that becomes part of the API, and might get broken
> by future changes.  That sucks, but there's nothing we can do to

+1 here, again, we're on the same line, just having different use cases
in mind it seems.

> completely prevent it except not have event triggers at all, and I
> want event triggers, and so do many other people.  What we can and I
> think must do is minimize the difficulty of writing and maintaining
> event triggers, and that means making the API as clean as possible.
> Like Tom, I'm very skeptical that this is the right horse to bet on.

I would thing that having one doesn't preclude having the other, and
while I hear Tom and you saying that it's a lot of maintenance work down
the road to have the command string, I'm highly skeptical of being able
to produce that external stable (enough) parser format in a way that
impose less code maintenance down the road.

Also, I'm thinking that publishing the normalized command string is
something that must be maintained in the core code, whereas the external
stable format might be done as a contrib extension, coded in C, working
with the Node *parsetree. Because it needs to ouput a compatible format
when applied to different major versions of PostgreSQL, I think it suits
quite well the model of C coded extensions.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Hash id in pg_stat_statements

[Peter Geoghegan]

On 3 October 2012 19:04, Tom Lane  wrote:
> Daniel Farina  writes:
>> Instead, I think it makes sense to assign a number -- arbitrarily, but
>> uniquely -- to the generation of a new row in pg_stat_statements, and,
>> on the flip side, whenever a row is retired its number should be
>> eliminated, practically, for-ever.  This way re-introductions between
>> two samplings of pg_stat_statements cannot be confused for a
>> contiguously maintained statistic on a query.
>
> This argument seems sensible to me.

Daniel: Could you please submit the patch that you were working on
that does this to the next commitfest?

-- 
Peter Geoghegan   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Greg Stark]

On Mon, Oct 15, 2012 at 3:26 PM, Robert Haas  wrote:
> To be perfectly frank, I think that's exactly where we ought to be
> going.  Oracle and Microsoft both did it, so why are we convinced it's
> a bad idea?  One of the huge problems with PL/pgsql is that every SQL
> expression in there has to be passed to the executor separately, which
> is painfully slow.

I'm a bit lost. I would think pl/pgsql is precisely the same as
Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a
purely implementation detail. I don't think pl/pgsql is the best
implemented part of Postgres but I don't see how integrating it into
the core is going to automatically make it all wonderful either.

Fwiw my experience has consistently been that life got better whenever
I moved anything I had implemented as PL/SQL or PL/pgsql into client
code in Perl or Python.


-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] explain tup_fetched/returned in monitoring-stats

[Robert Haas]

On Fri, Oct 12, 2012 at 2:05 PM, Abhijit Menon-Sen  wrote:
> Yes. I'm sorry. Is there any concise description that applies? I think
> it's worth fixing, seeing that multiple competent people have got the
> wrong idea about what it means.

I don't think there is.  I think we need to replace those counters
with something better.  The status quo is quite bizarre.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Truncate if exists

[Robert Haas]

On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
 wrote:
> Christopher Browne  writes:
>> I suggest the though of embracing statement modifiers in DDL, with
>> some options possible:
>>   a) { DDL STATEMENT } IF CONDITION;
>>   b) { DDL STATEMENT } UNLESS CONDITION;
>
> Just saying. I hate that. Makes it harder to read, that last bit at the
> end of the command changes it all. It's cool for a linguist, I guess,
> but we're not typing sentences at the psql prompt…
>
>> where CONDITION has several possible forms:
>>   i) {IF|UNLESS} ( SQL expression returning T/F )
>>   ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
>> {TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
>
> Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
> us that way, but I couldn't resist comparing. Soon enough you want a
> full programming language there.

To be perfectly frank, I think that's exactly where we ought to be
going.  Oracle and Microsoft both did it, so why are we convinced it's
a bad idea?  One of the huge problems with PL/pgsql is that every SQL
expression in there has to be passed to the executor separately, which
is painfully slow.  It frequently doesn't matter because writing loops
in a procedural language is often the wrong approach anyway, but it is
not always the wrong approach and people sometimes do it even when it
is, and then they end up unhappy.

In the short term this is not a practical outcome for us; what we can
reasonably do is add a few convenience functions to what we already
have to make it easy to test for things like the presence of a table,
the presence of a column, the presence of a schema, etc.  But in the
longer term, this is definitely something that people want.  Being
able to wrap control-flow statements around SQL is fundamentally
useful, which is why every major database supports it.  Being able to
do it without a lot of superfluous syntactic sugar and with good
performance is even more useful.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Andres Freund]

On Sunday, October 14, 2012 03:45:49 PM Simon Riggs wrote:
> As discussed on other threads, Hash Indexes are currently a broken
> feature and bring us into disrepute.
> 
> I describe them as broken because they are neither crash safe, nor
> could they properly be called unlogged indexes (or if so, why just
> hash?). And also because if somebody suggested implementing them the
> way they are now, they would be told they were insane because silent
> data corruption is not something we tolerate anymore. We know why they
> are like that, but its time to remove the rest of the historical
> research legacy. It's hard to say "We respect your data [except if you
> press here]" and be taken seriously.
> 
> Suggested actions are
> 
> * Put WARNINGs in the docs against the use of hash indexes, backpatch
> to 8.3. CREATE INDEX gives no warning currently, though Index Types
> does mention a caution.
> 
> * Mention in the current docs that hash indexes are likely to be
> deprecated completely in future releases. Should anybody ever make
> them work, we can change that advice quickly but I don't think we're
> going to.
> 
> Personally, I would like to see them removed into a contrib module to
> allow people to re-add them if they understand the risks. ISTM better
> to confiscate all foot-guns before they go off and then re-issue them
> to marksmen, at the risk of annoying the people that use them with
> full knowledge but that's likely a contentious issue.
> 
> Alternate views?

I vote for at least logging a wal record when a hash index is modified which 
uses incomplete actions to set 'indisready = false' in case its replayed. That 
should only use a rather minor amount of code and should help users to find 
problems faster.

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating Hash Indexes

[Robert Haas]

On Sun, Oct 14, 2012 at 9:45 AM, Simon Riggs  wrote:
> * Put WARNINGs in the docs against the use of hash indexes, backpatch
> to 8.3. CREATE INDEX gives no warning currently, though Index Types
> does mention a caution.

I'd be in favor of adding such warnings to the documentation if they
are not there already, and possibly even warning on CREATE INDEX ..
USING hash().  I don't think I'd go so far as to say that we should
imply that they'll be removed in a future release.  Given how deeply
intertwined they are with the planner, I doubt that that will happen;
and I think there is enough interest in the technology that it's
likely to eventually be fixed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deparsing DDL command strings

[Robert Haas]

On Fri, Oct 12, 2012 at 12:55 PM, Dimitri Fontaine
 wrote:
> The user of that command string still has to know what to look for and
> maybe should include a proper SQL parser, but at least it doesn't need
> to do much guesswork about how the serial attached sequence will get
> named by the system and such oddities.

IMHO, it should be our explicit goal for clients not to need to parse
any SQL at all.  I think that the communication between the server and
event triggers should be accomplished using magic variables.  If the
data is too complex to be structured that way, then I think we should
use an established serialization format such as XML or (my personal
preference) JSON for which many parsers already exist.  If we pass
sanitized SQL, then everyone who wants to do anything complicated will
need a parser for
sanitized-SQL-as-generated-by-the-PostgreSQL-event-trigger-mechanism,
and I think that's likely to be strictly worse than using one of the
serialization methods that already exists and is well understood.

Consider a hypothetical new feature where a table can be located in
either Paris or Pittsburgh.  We add two new keywords to the grammer:
PARIS and PITTSBURGH.  The syntax is extended to CREATE { PARIS |
PITTSBURGH } {schemaname}.{tablename} etc.  Now, if we use
sanitized-SQL as a way of passing data to triggers, they all
immediately break, because the new key word is in exactly the location
where the table name used to be.  If we use magic variables or JSON or
XML, we'll just add in another magic variable, or another field in the
JSON or XML object, and well-written triggers will ignore it and keep
working.  Now, it may seem like I've chosen this example somewhat
unfairly since most syntax changes are a bit less obtrusive than that,
but we did do something not dissimilar to the above in 9.1, with
UNLOGGED.

There's a careful line to be walked here, because in order for event
triggers to be useful, we're going to have to pass them information -
and the way we do that becomes part of the API, and might get broken
by future changes.  That sucks, but there's nothing we can do to
completely prevent it except not have event triggers at all, and I
want event triggers, and so do many other people.  What we can and I
think must do is minimize the difficulty of writing and maintaining
event triggers, and that means making the API as clean as possible.
Like Tom, I'm very skeptical that this is the right horse to bet on.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Andrew Dunstan]

On 10/15/2012 09:07 AM, Simon Riggs wrote:

On 15 October 2012 11:41, Greg Stark  wrote:

On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs  wrote:

Please can anyone show me the SQL for a rule that cannot be written as
a view or a trigger? I do not believe such a thing exists and I will
provide free beer to the first person that can prove me wrong.

Being written as a view doesn't help you because views use rules. I
repeat, the very fact that we need rules to implement views prove
rules are necessary for some purposes.

No, it just means there is some aspect of similar underlying infrastructure.

Denial of free beer looks like proof to me...




*sigh*

First, as Tom said, the onus of proof is on you. You can't transfer it 
away with this offer of free beer.


Second, he's actually told you one advantage rules can have over 
triggers, but you've pretty much chosen to ignore it:



Triggers necessarily operate on a row-at-a-time basis.  In theory,
for at least some bulk operations, a rule could greatly outperform
a trigger.  It's difficult to walk away from that - unless somebody
can prove that the advantage doesn't ever accrue in practice.


I have seen rules used instead of triggers for precisely this reason. 
Yes, the fact that COPY bypasses rules is something you need to 
remember, but that makes it a limitation of the feature, not an absolute 
reason not to use it. (I rarely if ever use them myself - can't recall 
the last time I did, but there is plenty of legacy use out there.)


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Peter Geoghegan]

On 15 October 2012 00:30, Greg Stark  wrote:
> In fact it's not a very good analogy because the situation is
> *precisely* the same -- rules *are* macros and manipulate the raw sql
> before it's run and the reason they can't be replaced by triggers is
> because, like functions, triggers happen after the code is compiled
> and run.

I quite like this analogy, because it nicely illustrates the problems
with rules.

C, and the C preprocessor, are essential the same now as they were in
the early 1970s. I think that *an emphasis* on a preprocessing stage
of translation is a fairly discredited idea (though there are some
sensible uses, particularly where alternatives are not available). C99
introduced inline functions, probably in no small part because it is
quite obvious that they are often superior to macros. Consider the two
most successful programming languages that were obviously influenced
by C: Java and C++. The first doesn't have a preprocessor, and the
second strongly encourages using numerous alternatives to macros where
possible, which is almost always. Maybe you don't like this analogy,
because you consider C to be a systems programming language, and as
such think it is only right and proper that programmers should be
given enough rope to hang themselves. Perhaps you're right, but the
same surely cannot be said for SQL. The original appeal of SQL was
that it was supposedly possible for non-programmers to write it.

Clearly deprecating rules implies some loss of functionality - there
is no exact, drop-in equivalent to something that magically rewrites
SQL that isn't equally baroque and problematic. If that's the bar,
then detractors of rules should stop wasting their breath, because the
bar has been set impossibly high.

On a *practical* level triggers are complete replacements for
user-defined rules. All that it takes to be able to *always* say that
one language feature is not equivalent to another, and on that basis
the other should not be deprecated, is a sufficient degree of pedantry
(not that I'm implying that you or anyone else was being pedantic, or
that concerns raised should not be heeded).

-- 
Peter Geoghegan   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Andres Freund]

On Monday, October 15, 2012 03:07:21 PM Simon Riggs wrote:
> On 15 October 2012 11:41, Greg Stark  wrote:
> > On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs  wrote:
> >> Please can anyone show me the SQL for a rule that cannot be written as
> >> a view or a trigger? I do not believe such a thing exists and I will
> >> provide free beer to the first person that can prove me wrong.
> > 
> > Being written as a view doesn't help you because views use rules. I
> > repeat, the very fact that we need rules to implement views prove
> > rules are necessary for some purposes.
> 
> No, it just means there is some aspect of similar underlying
> infrastructure.
> 
> Denial of free beer looks like proof to me...

Well, didn't Tom already mention AFTER ... FOR EACH ROW triggers being 
problematic because of the in-memory queue?

Greetings,

Andres
-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Deprecating RULES

[Simon Riggs]

On 15 October 2012 11:41, Greg Stark  wrote:
> On Mon, Oct 15, 2012 at 8:00 AM, Simon Riggs  wrote:
>> Please can anyone show me the SQL for a rule that cannot be written as
>> a view or a trigger? I do not believe such a thing exists and I will
>> provide free beer to the first person that can prove me wrong.
>
> Being written as a view doesn't help you because views use rules. I
> repeat, the very fact that we need rules to implement views prove
> rules are necessary for some purposes.

No, it just means there is some aspect of similar underlying infrastructure.

Denial of free beer looks like proof to me...

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: forcing object owner in TOC file

[Andrew Dunstan]

On 10/15/2012 07:59 AM, Piotr Gasidło wrote:

Hello,

I've created small patch which allows me easily change owner during
pg_restore with backup in custom format (-Fc).
This simplifies migration when users on new server are different that
ones in backup file.

First, I get TOC file:

pg_restore -Fc -l mybackup.custom > mybackup.toc

Then, I can alter owner of any object by adding username after TOC Id,
for ex. changing this:

173; 1259 25139 TABLE public data quaker

into this:

173 quaker1; 1259 25139 TABLE public data quaker

By above line I forced quaker1 to be owner of public.data table after restore.

Then I do normal restore using modified TOC:

$ pg_restore -Fc mybackup.custom -L mybackup.toc -d quaker
pg_restore: [archiver] WARNING: altering owner for TABLE data to quaker1

and have public.data TABLE owned by quaker1 user.




Sorry, but this doesn't strike me as a very good idea at all. Why not 
just alter the table ownership after the restore is done? If we start 
allowing stuff other than the TOC ID to be specified in the list file 
the modifications will never end. And if we do want to do that then it 
needs to be designed properly. For example, one change that seems far 
more important to me than changing the owner is to provide for restoring 
stuff to a different schema.


BTW, I realize your patch is small, but it's usually a good idea to 
discuss an idea on the mailing list before sending in a patch.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal - assign result of query to psql variable

[Pavel Stehule]

2012/10/15 Pavel Stehule :
> 2012/10/15 Shigeru HANADA :
>> Hi Pavel,
>>
>> First of all, I'm sorry that my previous review was rough.  I looked
>> your patch and existing code closely again.
>>
>> On 2012/10/15, at 12:57, Pavel Stehule  wrote:
>>> 2012/10/14 Tom Lane :
 * ExecQueryUsingCursor's concept of how to process command results
  (why? surely we don't need \gset to use a cursor)
>>>
>>> There was two possibilities, but hardly non using cursor is better way
>>
>> +1 for supporting the case when FETCH_COUNT > 0, because user might set
>> so mainly for other queries, and they would want to avoid changing
>> FETCH_COUNT setting during every query followed by \gset.
>>
 * the psql lexer (adding a whole bunch of stuff that probably doesn't
  belong there)
>>>
>>> ??
>>
>> I think that Tom is talking about psql_scan_slash_vars().  It seems too
>> specific to \gset command.  How about to improve
>> psql_scan_slash_options() so that it can handle comma-separated variable
>> list?  Although you might have tried it before.
>> # Unused OT_VARLIST macro gave me the idea.
>
> yes, it is possible - I'll look on it at evening

a reuse of psql_scan_slash_options is not good idea - a interface of
this function is out of my purposes - and I cannot to signalise error
from this procedure. But I can minimize psql_scan_slash_var and I can
move lot of code out of lexer file.

>
>>
 * the core psql settings construct (to store something that is in
  no way a persistent setting)

>>>
>>> ??
>>
>> I thought that having \gset arguments in pset is reasonable, since \g
>> uses pest.gfname to hold its one-shot setting.  Or, we should refactor
>> \g to fit with \gset?  I might be missing Tom's point...
>>
 Surely there is a less ugly and invasive way to do this.  The fact
 that the reviewer keeps finding bizarre bugs like "another backslash
 command on the same line doesn't work" seems to me to be a good
 indication that this is touching things it shouldn't.
>>>
>>> - all these bugs are based on lexer construct. A little modification
>>> of lexer is possible
>>
>> IMHO those issues come from the design rather than the implementation of
>> lexer.  AFAIK we don't have consensus about the case that both of \g and
>> \gset are used for a query like this:
>>
>> postgres=# SELECT 1 \gset var \\ \g foo.txt
>>
>> This seems regal.  Should we store "1" into var and write the result
>> into foo.txt?  Or, only either of them?  It's just an idea and it
>> requires new special character, but how about use \g command for file,
>> pipe, and variable?  In the case we choose '&' for variable prefix:
>>
>> postgres=# SELECT 'hello', 'wonderful', 'world!' \g &var1,,var2
>>
>> Anyway, we've had no psql's meta command which processes query result
>> other than \g.  So, we might have more considerable issues about design.
>
> a current design is rigid - a small implementation can stop parsing
> target list, when other backslash statement is detected
>
>>
>> BTW, what the word "comma_expected" means?  It's in the comment above
>> psql_scan_slash_vars().  It might be a remaining of old implementation.
>
> This identifier is mistaken - etc this comment is wrong and related to
> old implementation - sorry. A first design was replaced by state
> machine described by  VarListParserState
>
>
>
>>
>> Regards,
>> --
>> Shigeru HANADA
>> shigeru.han...@gmail.com
>>
>>
>>
>>
>>


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers