Re: [HACKERS] Statistics and selectivity estimation for ranges

[Matthias]

Having statistics on ranges was really missing! The planner was doing
some really, really bad choices on bigger tables regarding seq/random
scans, nested loop/other joins etc.

Is there any chance this makes it into 9.2 final? It would really
round-off the introduction of range types and maybe avoid problems
like "the new range types are slow" (just due to the bad row
estimates).

Thanks for implementing this feature,
-Matthias

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

[HACKERS] Git diff patch in context diff format

[Qi Huang]

Hi, hackersI was exporting my project to a patch file. As the patch review 
requires, the patch needs to be in context diff format 
(http://wiki.postgresql.org/wiki/Reviewing_a_Patch). But the git diff exports 
in a format similar to unified format. What is everyone doing with patching 
now? Is there any standard way?Thanks.

Best RegardsHuang Qi VictorComputer Science of National University of Singapore 
  

Re: [HACKERS] WIP: pg_pretty_query

[Pavel Stehule]

2012/8/7 Peter Geoghegan :
> On 7 August 2012 20:01, Andrew Dunstan  wrote:
>> On 08/07/2012 02:14 PM, Tom Lane wrote:
>>> * As per some of the complaints already registered in this thread,
>>> ruleutils.c is not designed with the goal of being a pretty-printer.
>>> Its primary charter is to support pg_dump by regurgitating rules/views
>>> in an unambiguous form, which does not necessarily look very similar to
>>> what was entered.  An example of a transformation that probably nobody
>>> would want in a typical pretty-printing context is expansion of
>>> "SELECT *" lists.  But again, there is really no way to turn that off.
>>> Another aspect that seems pretty undesirable for pretty-printing is
>>> loss of any comments embedded in the query text.
>>>
>>> I'm very much not in favor of trying to make ruleutils serve two
>>> masters, but that's the game we will be playing if we accept this patch.
>
> +1. A pretty-printer that makes the query to be cleaned-up actually
> undergo parse-analysis seems misconceived to me. This is a tool that
> begs to be written in something like Python, as a satellite project,
> with much greater flexibility in the format of the SQL that it
> outputs.
>
>> One of the challenges is to have a pretty printer that is kept in sync with
>> the dialect that's supported. Anything that doesn't use the backend's parser
>> seems to me to be guaranteed to get out of sync very quickly.
>
> I'm not convinced of that. Consider the example of cscope, a popular
> tool for browsing C code. Its parser was written to be "fuzzy", so
> it's actually perfectly usable for C++ and Java, even though that
> isn't actually supported, IIRC. Now, I'll grant you that that isn't a
> perfectly analogous situation, but it is similar in some ways. If we
> add a new clause to select and that bit is generically pretty-printed,
> is that really so bad? I have a hard time believing that a well
> written fuzzy pretty-printer for Postgres wouldn't deliver the vast
> majority of the benefits of an in-core approach, at a small fraction
> of the effort. You'd also be able to pretty-print plpgsql function
> definitions (a particularly compelling case for this kind of tool),
> which any approach based on the backends grammar will never be able to
> do (except, perhaps, if you were to do something even more
> complicated).

I disagree - simply pretty printer based on technique that we know
from ecpg can be very cheep and it cannot be "fuzzy" because it
integrate PostgreSQL parser.

Pavel

>
> --
> 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] WIP: pg_pretty_query

[Pavel Stehule]

2012/8/7 Tom Lane :
> Andrew Dunstan  writes:
>> On 08/07/2012 02:14 PM, Tom Lane wrote:
>>> In short, the only redeeming value of this patch is that it's short.
>
>> One of the challenges is to have a pretty printer that is kept in sync
>> with the dialect that's supported. Anything that doesn't use the
>> backend's parser seems to me to be guaranteed to get out of sync very
>> quickly.
>
> Sure.  I think if we wanted an actually engineered solution, rather than
> a half-baked one, ecpg provides a good source of inspiration.  One could
> imagine a standalone program that reads a query on stdin and emits a
> pretty-printed version to stdout, using a parser that is automatically
> generated from the backend's grammar with much the same technology used
> in recent ecpg releases.  I think that would address most of the
> complaints I raised: it would be relatively painless to make use of from
> contexts that don't have a live database connection, it wouldn't impose
> any constraints related to having suitable database content available,
> it wouldn't apply any of the multitude of implementation-dependent
> transformations that the backend's parser does, and it could be built
> (I think) to do something more with comments than just throw them away.

+1

it is better, and it is allow more space for possible styling

Pavel


>
> 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] Inserting heap tuples in bulk in COPY

[Jeff Janes]

On Tue, Aug 7, 2012 at 1:52 PM, Simon Riggs  wrote:
> On 7 August 2012 20:58, Jeff Janes  wrote:
>> Hi Heikki,
>>
>> Is the bulk index insert still an active area for you?
>>
>> If not, is there some kind of summary of design or analysis work
>> already done, which would be a useful for someone else interested in
>> picking it up?
>
> The main cost comes from repeated re-seeking down the index tree to
> find the insertion point, but repeated lock and pin operations on
> index buffers are also high. That is optimisable if the index inserts
> are grouped, as they will be with monotonic indexes, (e.g. SERIAL), or
> with partial monotonic (i.e. with Parent/Child relationship, on Child
> table many inserts will be of the form (x,1), (x,2), (x, 3) etc, e.g.
> Order/OrderLine).
>
> All we need do is buffer the inserts in the inserts, before inserting
> them into the main index. As long as we flush the buffer before end of
> transaction, we're good.
>
> Incidentally, we can also optimise repeated inserts within a normal
> transaction using this method, by implementing deferred unique
> constraints. At present we say that unique constraints aren't
> deferrable, but there's no reason they can't be, if we allow buffering
> in the index. (Implementing a deferred constraint that won't fail if
> we do UPDATE foo SET pk = pk+1 requires a buffer the size of foo,
> which is probably a bad plan, plus you'd need to sort the inputs, so
> that particular nut is another issue altogether, AFAICS).
>
> Suggested implementation is to buffer index tuples at the generic
> index API, then implement a bulk insert index API call that can then
> be implemented for each AM separately. Suggested buffer size is
> work_mem. Note we must extract index tuple from heap tuples -
> refetching heap blocks to get rows is too costly.

OK, thanks for the summary.  It looks like your plans are to improve
the case where the index maintenance is already CPU limited.  I was
more interested in cases where the regions of the index(es) undergoing
active insertion do not fit into usable RAM, so the limit is the
random IO needed to fetch the leaf pages in order to update them or to
write them out once dirtied.  Here too buffering is probably the
answer, but the size of the buffer needed to turn that IO from
effectively random to effectively sequential is probably much larger
than the size of the buffer you are contemplating.

> I think we need to implement buffering both to end of statement or end
> of transaction, not just one or the other.

With the planner deciding which would be better, or explicit user action?

Thanks,

Jeff

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

[HACKERS] Possible bug in PostgreSQL 9.2 stable: TwoPhaseGetDummyBackendId()

[Robert Ross]

I have looked at the Postgres 9.2 stable and Postgres 9.2 beta 3 git  
archives and this bug still appears to be present.


TwoPhaseGetDummyProc returns a PGPROC*. In 9.0, it was safe for  
TwoPhaseGetDummyBackendId() to cast this to a GlobalTransaction  
because the GlobalTransactionData structure's first element was always  
a PGPROC structure. However, in 9.2 this is no longer true. Despite  
the mismatch that now exists between the structures,  
TwoPhaseGetDummyBackendId() still attempts to cast a PGPROC* to a  
GlobalTransaction to extract the member 'dummyBackendId'. Rewriting  
this function to use the GlobalTransaction structure that is found in  
TwoPhaseState->prepXacts[] appears to fix the problem. In practice  
this appears to result in TwoPhaseGetDummyBackendId() returning  
invalid values (such as 0) instead of the expected range above  
MaxBackends. I'm not sure why this hasn't caused problems with  
multixact.c's use of this function.


Is this a known bug? Would it be helpful to submit a patch?

Regards,

Robert Ross




--
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 fix proposal for bug #6123

[Bruce Momjian]

Did we ever decide on this?  Is it a TODO?

---

On Fri, Jul 22, 2011 at 04:01:20PM -0500, Kevin Grittner wrote:
> Robert Haas  wrote:
> > On Wed, Jul 20, 2011 at 2:58 PM, Kevin Grittner
> >  wrote:
> >> So basically, the goal of this patch is to ensure that a BEFORE
> >> DELETE trigger doesn't silently fail to delete a row because that
> >> row was updated during the BEFORE DELETE trigger firing (in our
> >> case by secondary trigger firing).
> > 
> > I've run across this problem before while writing application code
> > and have found it surprising, unfortunate, and difficult to work
> > around. It's not so bad when it only bites you once, but as things
> > get more complicated and you have more and more triggers floating
> > around, it gets pretty darn horrible.  One of the things I've done
> > to mitigate the impact of this is to write as many triggers as
> > possible as AFTER triggers
>  
> Yeah, this is not an issue in AFTER triggers, so moving any updating
> to those is a solution.  In most cases that's where you want
> triggered modifications to take place anyway.  The cascading delete
> situation is the most obvious exception, although there certainly
> could be others.
>  
> > but that's sometimes difficult to accomplish.
>  
> Yeah, sometimes.
>  
> > Your scenario is a BEFORE DELETE trigger that does an UPDATE on
> > the same row, but I think this problem also occurs if you have a
> > BEFORE UPDATE trigger that does an UPDATE on the same row.  I
> > believe the second update gets silently ignored.
>  
> My testing shows that the primary update gets ignored, while all the
> triggered effects of that update are persisted.  Yuck.  :-(  It
> certainly seems possible to turn that around, but that hardly seems
> better.  In asking application programmers here what they would
> *expect* to happen, they all seem to think that it is surprising
> that the BEFORE trigger functions *return a record*, rather than a
> boolean to say whether to proceed with the operation.  They feel it
> would be less confusing if a value set into NEW was effective if the
> operation does take effect, and the boolean controls whether or not
> that happens.  They rather expect that if an update happens from the
> same transaction while a before trigger is running, that the NEW
> record will reflect the change.
>  
> I recognize how hard it would be to create that expected behavior,
> and how unlikely it is that the community would accept such a change
> at this point.  But current behavior is to silently do something
> really dumb, so I think some change should be considered -- even if
> that change is to throw an error where we now allow nonsense.
>  
> INSERT is not a problem -- if a BEFORE INSERT trigger inserts a row
> with a conflicting primary key (or other unique index key), the
> operation will be rolled back.  That's fine.
>  
> I think DELETE can be cleanly fixed with a patch similar to what I
> posted earlier in the thread.  I found one more value that looks
> like it should be set, and it could use some comments, but I believe
> that we can get DELETE behavior which is every bit as sensible as
> INSERT behavior with a very small change.
>  
> The worms do come crawling out of the can on BEFORE UPDATE triggers,
> though.  When faced with an UPDATE which hasn't yet been applied,
> and other UPDATEs triggering from within the BEFORE UPDATE trigger
> which touch the same row, it doesn't seem like you can honor both
> the original UPDATE which was requested *and* the triggered UPDATEs.
> Of course, if you discard the original UPDATE, you can't very well
> do anything with the record returned from the BEFORE UPDATE trigger
> for that update.  Since it seems equally evil to allow the update
> while ignoring some of the work caused by its trigger functions as
> to show the work of the triggered updates while suppressing the
> original update, I think the right thing is to throw an error if the
> old row for a BEFORE UPDATE is updated by the same transaction and
> the trigger function ultimately returns a non-NULL value.
>  
> Thoughts?
>  
> -Kevin
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  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] WIP patch for LATERAL subqueries

[Tom Lane]

I wrote:
> What I'd like to do next, barring objections, is to band-aid the places
> where the planner could crash on a LATERAL query (probably just make it
> throw FEATURE_NOT_SUPPORTED errors), write some documentation, and
> then commit what I've got.  After that I can go back to improve the
> planner and work on the parser refactoring issues as separate patches.

... and done (though the pgsql-committers message seems to have got hung
up for moderation).  I put some simplistic examples into section 7.2.1.5
and the SELECT reference page ... if anybody has ideas for
more-compelling small examples, please speak up.

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_pretty_query

[Tom Lane]

Peter Geoghegan  writes:
> On 7 August 2012 20:01, Andrew Dunstan  wrote:
>> One of the challenges is to have a pretty printer that is kept in sync with
>> the dialect that's supported. Anything that doesn't use the backend's parser
>> seems to me to be guaranteed to get out of sync very quickly.

> I'm not convinced of that. Consider the example of cscope, a popular
> tool for browsing C code. Its parser was written to be "fuzzy", so
> it's actually perfectly usable for C++ and Java, even though that
> isn't actually supported, IIRC. Now, I'll grant you that that isn't a
> perfectly analogous situation, but it is similar in some ways.

Yeah.  A related question here is whether you want a pretty printer that
is entirely unforgiving of (what it thinks are) syntax errors in the
input.  It might be a lot more useful if it didn't spit up on that, but
just did the best it could.

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] Inserting heap tuples in bulk in COPY

[Simon Riggs]

On 7 August 2012 20:58, Jeff Janes  wrote:
> On Fri, Aug 12, 2011 at 2:59 PM, Heikki Linnakangas
>  wrote:
>> On 13.08.2011 00:17, Simon Riggs wrote:
>>>
>>> Also, we discussed that you would work on buffering the index inserts,
>>> which is where the main problem lies. The main heap is only a small
>>> part of the overhead if we have multiple indexes already built on a
>>> table - which is the use case that causes the most problem.
>>
>>
>> Sure, if you have indexes on the table already, then the overhead of
>> updating them is more significant. I am actually working on that too, I will
>> make a separate post about that.
>
> Hi Heikki,
>
> Is the bulk index insert still an active area for you?
>
> If not, is there some kind of summary of design or analysis work
> already done, which would be a useful for someone else interested in
> picking it up?

The main cost comes from repeated re-seeking down the index tree to
find the insertion point, but repeated lock and pin operations on
index buffers are also high. That is optimisable if the index inserts
are grouped, as they will be with monotonic indexes, (e.g. SERIAL), or
with partial monotonic (i.e. with Parent/Child relationship, on Child
table many inserts will be of the form (x,1), (x,2), (x, 3) etc, e.g.
Order/OrderLine).

All we need do is buffer the inserts in the inserts, before inserting
them into the main index. As long as we flush the buffer before end of
transaction, we're good.

Incidentally, we can also optimise repeated inserts within a normal
transaction using this method, by implementing deferred unique
constraints. At present we say that unique constraints aren't
deferrable, but there's no reason they can't be, if we allow buffering
in the index. (Implementing a deferred constraint that won't fail if
we do UPDATE foo SET pk = pk+1 requires a buffer the size of foo,
which is probably a bad plan, plus you'd need to sort the inputs, so
that particular nut is another issue altogether, AFAICS).

Suggested implementation is to buffer index tuples at the generic
index API, then implement a bulk insert index API call that can then
be implemented for each AM separately. Suggested buffer size is
work_mem. Note we must extract index tuple from heap tuples -
refetching heap blocks to get rows is too costly.

I think we need to implement buffering both to end of statement or end
of transaction, not just one or the other.

-- 
 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] Pg_ctl promote -- wait for slave to be promoted fully ?

[Robert Haas]

On Tue, Aug 7, 2012 at 1:09 PM, Fujii Masao  wrote:
> On Tue, Aug 7, 2012 at 10:22 PM, Magnus Hagander  wrote:
>> Should we consider if we can make "pg_ctl -w" work for "promote" as well?
>
> +1
>
>> The main problem is, I guess, that it can't log in - so wed' need
>> something like "PQping()" that actually checked if it was master or
>> slave?
>
> Yes.

Is there a security concern with exposing this information to people
who haven't authenticated?

-- 
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] WIP: pg_pretty_query

[Peter Geoghegan]

On 7 August 2012 20:01, Andrew Dunstan  wrote:
> On 08/07/2012 02:14 PM, Tom Lane wrote:
>> * As per some of the complaints already registered in this thread,
>> ruleutils.c is not designed with the goal of being a pretty-printer.
>> Its primary charter is to support pg_dump by regurgitating rules/views
>> in an unambiguous form, which does not necessarily look very similar to
>> what was entered.  An example of a transformation that probably nobody
>> would want in a typical pretty-printing context is expansion of
>> "SELECT *" lists.  But again, there is really no way to turn that off.
>> Another aspect that seems pretty undesirable for pretty-printing is
>> loss of any comments embedded in the query text.
>>
>> I'm very much not in favor of trying to make ruleutils serve two
>> masters, but that's the game we will be playing if we accept this patch.

+1. A pretty-printer that makes the query to be cleaned-up actually
undergo parse-analysis seems misconceived to me. This is a tool that
begs to be written in something like Python, as a satellite project,
with much greater flexibility in the format of the SQL that it
outputs.

> One of the challenges is to have a pretty printer that is kept in sync with
> the dialect that's supported. Anything that doesn't use the backend's parser
> seems to me to be guaranteed to get out of sync very quickly.

I'm not convinced of that. Consider the example of cscope, a popular
tool for browsing C code. Its parser was written to be "fuzzy", so
it's actually perfectly usable for C++ and Java, even though that
isn't actually supported, IIRC. Now, I'll grant you that that isn't a
perfectly analogous situation, but it is similar in some ways. If we
add a new clause to select and that bit is generically pretty-printed,
is that really so bad? I have a hard time believing that a well
written fuzzy pretty-printer for Postgres wouldn't deliver the vast
majority of the benefits of an in-core approach, at a small fraction
of the effort. You'd also be able to pretty-print plpgsql function
definitions (a particularly compelling case for this kind of tool),
which any approach based on the backends grammar will never be able to
do (except, perhaps, if you were to do something even more
complicated).

-- 
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] Inserting heap tuples in bulk in COPY

[Jeff Janes]

On Fri, Aug 12, 2011 at 2:59 PM, Heikki Linnakangas
 wrote:
> On 13.08.2011 00:17, Simon Riggs wrote:
>>
>> Also, we discussed that you would work on buffering the index inserts,
>> which is where the main problem lies. The main heap is only a small
>> part of the overhead if we have multiple indexes already built on a
>> table - which is the use case that causes the most problem.
>
>
> Sure, if you have indexes on the table already, then the overhead of
> updating them is more significant. I am actually working on that too, I will
> make a separate post about that.

Hi Heikki,

Is the bulk index insert still an active area for you?

If not, is there some kind of summary of design or analysis work
already done, which would be a useful for someone else interested in
picking it up?

Thanks,

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] WIP: pg_pretty_query

[Tom Lane]

Andrew Dunstan  writes:
> On 08/07/2012 02:14 PM, Tom Lane wrote:
>> In short, the only redeeming value of this patch is that it's short.

> One of the challenges is to have a pretty printer that is kept in sync 
> with the dialect that's supported. Anything that doesn't use the 
> backend's parser seems to me to be guaranteed to get out of sync very 
> quickly.

Sure.  I think if we wanted an actually engineered solution, rather than
a half-baked one, ecpg provides a good source of inspiration.  One could
imagine a standalone program that reads a query on stdin and emits a
pretty-printed version to stdout, using a parser that is automatically
generated from the backend's grammar with much the same technology used
in recent ecpg releases.  I think that would address most of the
complaints I raised: it would be relatively painless to make use of from
contexts that don't have a live database connection, it wouldn't impose
any constraints related to having suitable database content available,
it wouldn't apply any of the multitude of implementation-dependent
transformations that the backend's parser does, and it could be built
(I think) to do something more with comments than just throw them away.

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_pretty_query

[Andrew Dunstan]

On 08/07/2012 02:14 PM, Tom Lane wrote:


* As per some of the complaints already registered in this thread,
ruleutils.c is not designed with the goal of being a pretty-printer.
Its primary charter is to support pg_dump by regurgitating rules/views
in an unambiguous form, which does not necessarily look very similar to
what was entered.  An example of a transformation that probably nobody
would want in a typical pretty-printing context is expansion of
"SELECT *" lists.  But again, there is really no way to turn that off.
Another aspect that seems pretty undesirable for pretty-printing is
loss of any comments embedded in the query text.

I'm very much not in favor of trying to make ruleutils serve two
masters, but that's the game we will be playing if we accept this patch.


I think this horse has probably bolted. If you wanted to segregate off 
this functionality we shouldn't have used things like pg_get_viewdef in 
psql, ISTM.





In short, the only redeeming value of this patch is that it's short.
The functionality it provides is not something that anyone would come
up with in a green-field design for a pretty-printer, and if we take
it we are going to be faced with a whole lot of redesign requests that
will be painful to implement and will carry heavy risks of breaking
pg_dump and/or EXPLAIN.




One of the challenges is to have a pretty printer that is kept in sync 
with the dialect that's supported. Anything that doesn't use the 
backend's parser seems to me to be guaranteed to get out of sync very 
quickly.


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

[HACKERS] bug of pg_trgm?

[Fujii Masao]

Hi,

When I used pg_trgm, I encountered the problem that the search result of
SeqScan was the different from that of BitmapScan even if the search
keyword was the same. Is this a bug? Here is the test case:

---
CREATE EXTENSION pg_trgm;
CREATE TABLE tbl (col text);
CREATE INDEX idx ON tbl USING gin (col gin_trgm_ops);
INSERT INTO tbl VALUES ('abc'), ('ab c');

SET enable_seqscan TO off;
SET enable_bitmapscan TO on;
SELECT * FROM tbl WHERE col LIKE E'%\\c%';
 col
--
 ab c
(1 row)

SET enable_seqscan TO on;
SET enable_bitmapscan TO off;
SELECT * FROM tbl WHERE col LIKE E'%\\c%';
 col
--
 abc
 ab c
(2 rows)
---

The cause is ISTM that pg_trgm wrongly ignores the heading wildcard
character (i.e., %) when an escape (i.e., \\) follows the wildcard character.
Attached patch fixes this.

The patch fixes another problem: pg_trgm wrongly ignores the backslash \\
following the escape, i.e., . This problem might be harmless when
KEEPONLYALNUM is enabled because any characters other than
alphabets and digits are ignored. But, when KEEPONLYALNUM is disabled,
 should be interpreted as a backslash character itself, but
pg_trgm does not.

Regards,

-- 
Fujii Masao


trgm_bugfix_v1.patch
Description: Binary data

-- 
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_pretty_query

[Tom Lane]

Bruce Momjian  writes:
> On Tue, Aug  7, 2012 at 04:14:34PM +0200, Pavel Stehule wrote:
>> last year we are spoke about reusing pretty print view code for some queries.
>> 
>> Here is patch:

> I can see this as very useful for people reporting badly-formatted
> queries to our email lists.  Great!

Allow me to express a contrary opinion: I think this is a bad idea.

* First off, packaging it as a SQL function that takes and returns text
seems rather awkward to use.  A lot of places where you might wish for
a SQL pretty-printer aren't going to have a database connection handy
(think "emacs SQL mode").

* The functionality provided is not merely a pretty-printer but sort
of a query validator as well: the function will fail if the query refers
to any tables, columns, functions, etc you don't have in your database.
For some applications that's fine, but others not so much --- in
particular I suspect it's nigh useless for the use-case you mention of
quickly turning an emailed query into something legible.  And there's
no way to separate out the reformatting functionality from that.

* As per some of the complaints already registered in this thread,
ruleutils.c is not designed with the goal of being a pretty-printer.
Its primary charter is to support pg_dump by regurgitating rules/views
in an unambiguous form, which does not necessarily look very similar to
what was entered.  An example of a transformation that probably nobody
would want in a typical pretty-printing context is expansion of
"SELECT *" lists.  But again, there is really no way to turn that off.
Another aspect that seems pretty undesirable for pretty-printing is
loss of any comments embedded in the query text.

I'm very much not in favor of trying to make ruleutils serve two
masters, but that's the game we will be playing if we accept this patch.

In short, the only redeeming value of this patch is that it's short.
The functionality it provides is not something that anyone would come
up with in a green-field design for a pretty-printer, and if we take
it we are going to be faced with a whole lot of redesign requests that
will be painful to implement and will carry heavy risks of breaking
pg_dump and/or EXPLAIN.

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] avoid unnecessary failure to open restored WAL files

[Simon Riggs]

On 2 August 2012 17:18, Fujii Masao  wrote:
> Hi,
>
> In HEAD and 9.2, the following scenario happens in archive recovery.
>
> 1. The archived WAL file is restored onto the temporary file name
> "RECOVERYXLOG".
> 2. The restored WAL file is renamed to the correct file name like
> 00010002.
> 3. The startup process tries to open the temporary file even though
> it's already been renamed
> and doesn't exist. This always fails.
> 4. The startup process retries to open the correct file as a WAL file
> in pg_xlog directory instead
> of the archived file. This succeeds.
>
> The above failure of file open is unnecessary, so I think we can avoid
> that. Attached patch
> changes the startup process so that it opens the correct restored WAL
> file after restoring the
> archived WAL file.

Looks to me that the strncpy is backwards and will still fail.  Please
double check.

-- 
 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 patch for LATERAL subqueries

[Tom Lane]

Here's an updated version of my LATERAL patch.

* Accepts "LATERAL func_name(args)".

* Handles LATERAL in JOIN nests now.  I rewrote the way
transformFromClause manages visibility of previously-parsed FROM items.
Rather than my previous idea of adding more namespace lists to a
ParseState, I changed p_relnamespace and p_varnamespace to be lists of
ParseNamespaceItem structs, which carry an RTE pointer plus visibility
flags.  This seemed to work out fairly well, and allowed me to implement
the RIGHT/FULL JOIN case exactly as per SQL spec, that is the left side
of a "RIGHT JOIN LATERAL" is visible to the right side but throws error
if used.  (I'm a bit tempted now to collapse p_relnamespace and
p_varnamespace into just one list, by adding two more booleans to
ParseNamespaceItem to show whether the item is visible for qualified or
unqualified references.  But that would affect code that the current
patch doesn't need to touch, so it seems better to postpone it to a
separate refactoring patch.)  This results in some changes in the error
messages output for improper-column-reference errors, as shown in the
regression test deltas.  It also fixes the pre-existing spec-conformance
issue about lateral versus parent references, as per Andrew Gierth in
http://archives.postgresql.org/message-id/87ocpjscpa@news-spur.riddles.org.uk

* Throws error if LATERAL is used to create an aggregate that belongs to
its aggregation query's FROM clause.  I did this in a slightly grotty
way: transformAggregateCall looks at the p_lateral_active field of the
appropriate pstate level, relying on the fact that the only way the
case can happen is via LATERAL.  As I mentioned earlier, I think it'd
be better to add a ParseState field showing exactly which query part
we're parsing at any instant.  However, again that seems better done as
part of a separate refactoring patch.

I have not done anything yet about the planner shortcomings.

What I'd like to do next, barring objections, is to band-aid the places
where the planner could crash on a LATERAL query (probably just make it
throw FEATURE_NOT_SUPPORTED errors), write some documentation, and
then commit what I've got.  After that I can go back to improve the
planner and work on the parser refactoring issues as separate patches.

Comments, better ideas?

regards, tom lane



binLPTyx5cE7r.bin
Description: lateral-2.patch.gz

-- 
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] Re: BUG #6742: pg_dump doesn't convert encoding of DB object names to OS encoding

[Robert Haas]

On Wed, Jul 25, 2012 at 7:54 AM, Alexander Law  wrote:
> Hello,
> I would like to fix this bug, but it looks like it would be not one-line
> patch.
> Looking at the pg_dump code I see that the object names come through the
> following chain:
> 1. pg_dump executes 'SELECT c.tableoid, c.oid, c.relname, ... ' and gets the
> object_name with the encoding chosen for db connection/dump.
> 2. it invokes write_msg function or alike:
>
> write_msg(NULL, "finding the columns and types of table \"%s\"\n",
> tbinfo->dobj.name);
> 3. vwrite_msg localizes text message, but not the argument(s):
> vfprintf(stderr, _(fmt), ap);
> Here gettext (_) internally translates fmt to OS encoding (if it's different
> from UTF-8 - encoding of a localized strings).
>
> And I can see only a few solutions of the problem:
> 1. To convert the object name at the back-end, i.e. to modify all the
> similar SELECT's as:
> 'SELECT c.tableoid, c.oid, c.relname, convert_to(c.relname, 'OS_ENCODING')
> AS locrelname, ...'
> and then do write_msg(NULL, "finding the columns and types of table
> \"%s\"\n", tbinfo->dobj.local_name);
> The downside of this approach is that it requires rewriting all the SELECT's
> for all the object. And it doesn't help us to write out any other text from
> backend, such as localized backend error.
>
> 2. To setup another connection to backend with the OS encoding, and to get
> all the object names through it. It looks insane too. And we have the same
> problem with the localized backend errors coming on "main" connection.
>
> 3. To make convert_to_os_encoding(text, encoding) function for a frontend
> utilities. Unfortunately frontend can't use internal PostgreSQL conversion
> functions, and modifying them to use through libpq looks unfeasible.
> So the only way to implement such function is to use another encoding
> conversion framework (library).
> And my question is - is it possible to include libiconv (add this
> dependency) to the frontend utilities code?
>
> 4. To force users to use OS encoding as the Database encoding. Or to not use
> non-ASCII characters in an db object names and to disable nls on Windows
> completely. It doesn't look like a solution at all.

I think if you're going to try to do something about this, #1 is
probably the best option.

It does sound like a lot of work, though.

-- 
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] [PATCH] Docs: Make notes on sequences and rollback more obvious

[David Fetter]

On Tue, Aug 07, 2012 at 03:59:42PM +0800, Craig Ringer wrote:
> On 08/07/2012 02:27 AM, Robert Haas wrote:
> >I did not commit the advanced.sgml changes.
> 
> That's arguably the most important point to raise this. The most
> recent question came from someone who actually bothered to RTFM and
> believed based on the advanced-transactions page that rollback rolls
> *everything* back.

Perhaps we should see about correcting that misapprehension.  When
PostgreSQL does any irreversible process
 such as
incrementing a sequence, writing a file, sending an email, etc., it
can't be rolled back.  Might it be useful to find those irreversible
operations we document and mark same?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

[HACKERS] pg_upgrade bug in Windows on PG 9.2

[Bruce Momjian]

I just got a bug report from EnterpriseDB saying pg_upgrade generates a
file share violation on PG 9.2.  I was initially confused because I know
we fixed this in a May commit.  Well, it turns out that this commit
re-added the same failure:

commit 4741e9afb93f0d769655b2d18c2b73b86f281010
Author: Alvaro Herrera 
Date:   Thu Jun 28 23:27:00 2012 -0400

Make the pg_upgrade log files contain actual commands

Now the log file not only contains the output from commands 
executed by
system(), but also what command it was in the first place.  This
arrangement makes debugging a lot simpler.

The commit was certainly a good idea, but exec_prog's log_file
specification was designed as for use in an error string, meaning that
the creation on an error string at one call sight would cause pg_upgrade
to erroneously create a log file named "file1 or file2" on Windows.  The
bigger problem is that the log file was opened at the start of the
function (with no error return check), and kept open until the end,
causing the file to be opened while the command string was run --- this
was causing the share violation.

I have applied the attached patch to head and 9.2 to fix all these
issues.

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

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 71d8f75..aa896b5
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** issue_warnings(char *sequence_script_fil
*** 183,189 
  		if (sequence_script_file_name)
  		{
  			prep_status("Adjusting sequences");
! 			exec_prog(true, true, UTILITY_LOG_FILE,
  	  SYSTEMQUOTE "\"%s/psql\" --echo-queries "
  	  "--set ON_ERROR_STOP=on "
  	  "--no-psqlrc --port %d --username \"%s\" "
--- 183,189 
  		if (sequence_script_file_name)
  		{
  			prep_status("Adjusting sequences");
! 			exec_prog(true, true, UTILITY_LOG_FILE, NULL,
  	  SYSTEMQUOTE "\"%s/psql\" --echo-queries "
  	  "--set ON_ERROR_STOP=on "
  	  "--no-psqlrc --port %d --username \"%s\" "
diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c
new file mode 100644
index 571792b..07a3b54
*** a/contrib/pg_upgrade/dump.c
--- b/contrib/pg_upgrade/dump.c
*** generate_old_dump(void)
*** 23,29 
  	 * --binary-upgrade records the width of dropped columns in pg_class, and
  	 * restores the frozenid's for databases and relations.
  	 */
! 	exec_prog(true, true, UTILITY_LOG_FILE,
  			  SYSTEMQUOTE "\"%s/pg_dumpall\" --port %d --username \"%s\" "
  			  "--schema-only --binary-upgrade %s > \"%s\" 2>> \"%s\""
  			  SYSTEMQUOTE, new_cluster.bindir, old_cluster.port, os_info.user,
--- 23,29 
  	 * --binary-upgrade records the width of dropped columns in pg_class, and
  	 * restores the frozenid's for databases and relations.
  	 */
! 	exec_prog(true, true, UTILITY_LOG_FILE, NULL,
  			  SYSTEMQUOTE "\"%s/pg_dumpall\" --port %d --username \"%s\" "
  			  "--schema-only --binary-upgrade %s > \"%s\" 2>> \"%s\""
  			  SYSTEMQUOTE, new_cluster.bindir, old_cluster.port, os_info.user,
diff --git a/contrib/pg_upgrade/exec.c b/contrib/pg_upgrade/exec.c
new file mode 100644
index 0d6fb8d..6f993df
*** a/contrib/pg_upgrade/exec.c
--- b/contrib/pg_upgrade/exec.c
*** static int	win32_check_directory_write_p
*** 33,50 
   *	line to be executed is saved to the specified log file.
   *
   *	If throw_error is TRUE, this function will throw a PG_FATAL error
!  *	instead of returning should an error occur.
   */
  int
! exec_prog(bool throw_error, bool is_priv,
! 		  const char *log_file, const char *fmt,...)
  {
  	va_list		args;
  	int			result;
  	int			retval;
  	char		cmd[MAXPGPATH];
  	mode_t		old_umask = 0;
! 	FILE	   *log = fopen(log_file, "a+");
  
  	if (is_priv)
  		old_umask = umask(S_IRWXG | S_IRWXO);
--- 33,51 
   *	line to be executed is saved to the specified log file.
   *
   *	If throw_error is TRUE, this function will throw a PG_FATAL error
!  *	instead of returning should an error occur.  The command it appended
!  *	to log_file;  opt_log_file is used in error messages.
   */
  int
! exec_prog(bool throw_error, bool is_priv, const char *log_file,
! 		  const char *opt_log_file, const char *fmt,...)
  {
  	va_list		args;
  	int			result;
  	int			retval;
  	char		cmd[MAXPGPATH];
  	mode_t		old_umask = 0;
! 	FILE	   *log;
  
  	if (is_priv)
  		old_umask = umask(S_IRWXG | S_IRWXO);
*** exec_prog(bool throw_error, bool is_priv
*** 53,61 
  	vsnprintf(cmd, MAXPGPATH, fmt, args);
  	va_end(args);
  
  	pg_log(PG_VERBOSE, "%s\n", cmd);
  	fprintf(log, "command: %s\n", cmd);
! 	fflush(log);
  
  	result = system(cmd);
  
--- 54,68 
  	vsnprintf(cmd, MAXPGPATH, fmt, args);
  	va_end(args);
  
+ 	if ((log = fopen_priv(log_file, "a+")) == N

Re: [HACKERS] Pg_ctl promote -- wait for slave to be promoted fully ?

[Fujii Masao]

On Tue, Aug 7, 2012 at 10:22 PM, Magnus Hagander  wrote:
> Should we consider if we can make "pg_ctl -w" work for "promote" as well?

+1

> The main problem is, I guess, that it can't log in - so wed' need
> something like "PQping()" that actually checked if it was master or
> slave?

Yes.

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] WIP: pg_pretty_query

[David Fetter]

On Tue, Aug 07, 2012 at 04:54:12PM +0200, Pavel Stehule wrote:
> 2012/8/7 Thom Brown :
> > On 7 August 2012 15:14, Pavel Stehule  wrote:
> >> Hello
> >>
> >> last year we are spoke about reusing pretty print view code for some 
> >> queries.
> >>
> >> Here is patch:
> >>
> >> this patch is really short - it is nice. But - it works only with
> >> known database objects (probably we would it) and it doesn't format
> >> subqueries well
> >>
> >>
> >> postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
> >> z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
> >> z.a)', true, false);
> >>  pg_pretty_query
> >> --
> >>   SELECT x.a, z.a+
> >> FROM foo, foo x, x z +
> >>WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
> >> FROM foo +
> >>WHERE foo.a = z.a))
> >> (1 row)
> >
> > This looks odd:
> >
> > postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
> > greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
> > generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING < NULLS FIRST',
> > true, false);
> >  pg_pretty_query
> > --
> >   SELECT 1,  +
> >  ( SELECT max(a.x) + GREATEST(2, 3)  +
> > FROM generate_series(4, 10, 2) a(x)) +
> > FROM generate_series(1, 100) generate_series(generate_series)+
> >GROUP BY 1::integer   +
> >ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3)   +
> > FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
> > (1 row)
> >
> > USING < is removed completely (or if I used DESC, NULLS FIRST is then
> > removed instead), "2" in the order by is expanded to its full query,
> > and generate_series when used in FROM is repeated with its own name as
> > a parameter.  I'm also not sure about the spacing before each line.
> > SELECT, FROM and GROUP BY all appear out of alignment from one
> > another.
> 
> it is issue - probably we can start deserialization just from parser
> stage, not from rewriter stage - but then code will be significantly
> longer and we cannot reuse current code for pretty print view.
> 
> >
> > Plus it would be nice if we could support something like the following 
> > style:
> >
> > SELECT
> > field_one,
> > field_two + field_three
> > FROM
> > my_table
> > INNER JOIN
> > another_table
> > ON
> > my_table.field_one = another_table.another_field
> > AND
> > another_table.valid = true
> > WHERE
> > field_one > 3
> > AND
> > field_two < 10;
> >
> 
> it is second issue - probably there are more lovely styles - CELKO,
> your and other. I am not sure if we can support more styles in core
> (contrib should be better maybe).

Would it be better to have output plugins and not privilege one?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] Beta 3

[Robert Haas]

On Tue, Aug 7, 2012 at 8:51 AM, Magnus Hagander  wrote:
> Agreed. Anybody up for writing the text though? There was some text
> earlier (see 
> http://git.postgresql.org/gitweb/?p=pgweb.git;a=commitdiff;h=bd02e36141bb99e9ee4e0b80fd69464e0e6d91b7#patch18
> or the corresponding one in the old pgweb repo). That can probably be
> adapted to be put in the new one - seems it was genereally just
> removed because there was no beta available, and the intentino wasn't
> that the page should remain useless once a beta was out..

How about we start by adding link(s) to the download page(s)?

-- 
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] WIP: pg_pretty_query

[Andrew Dunstan]

On 08/07/2012 10:14 AM, Pavel Stehule wrote:

Hello

last year we are spoke about reusing pretty print view code for some queries.

Here is patch:

this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well


postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
  pg_pretty_query
--
   SELECT x.a, z.a+
 FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
 FROM foo +
WHERE foo.a = z.a))
(1 row)


Good stuff. That's one less item on my TODO list :-)

I think we should have a version that lets you specify the wrap column, 
like pg_get_viewdef does. Possibly for this case we should even default 
it to 0 (wrap after each item) instead of 79 which it is for views.



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] WIP: pg_pretty_query

[Bruce Momjian]

On Tue, Aug  7, 2012 at 04:14:34PM +0200, Pavel Stehule wrote:
> Hello
> 
> last year we are spoke about reusing pretty print view code for some queries.
> 
> Here is patch:
> 
> this patch is really short - it is nice. But - it works only with
> known database objects (probably we would it) and it doesn't format
> subqueries well
> 
> 
> postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
> z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
> z.a)', true, false);
>  pg_pretty_query
> --
>   SELECT x.a, z.a+
> FROM foo, foo x, x z +
>WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
> FROM foo +
>WHERE foo.a = z.a))
> (1 row)

I can see this as very useful for people reporting badly-formatted
queries to our email lists.  Great!

-- 
  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] [PATCH] Docs: Make notes on sequences and rollback more obvious

[Jeff MacDonald]

On Tuesday, August 07, 2012 09:45:35 AM Kevin Grittner wrote:
[...snipped...]
> I also think it's a problem that one can get through the entire
> "Concurrency Control" chapter (mvcc.sgml) without a clue that
> sequences aren't transactional.  I think maybe a mention in the
> Introduction section of that chapter with a  would be
> appropriate.
> 

+1

> -Kevin

-- 
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] -Wformat-zero-length

[Bruce Momjian]

On Tue, Aug  7, 2012 at 10:38:52AM -0400, Alvaro Herrera wrote:
> Excerpts from Bruce Momjian's message of vie ago 03 16:02:28 -0400 2012:
> > On Fri, Aug  3, 2012 at 04:01:18PM -0400, Robert Haas wrote:
> > > On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian  wrote:
> > > >> I don't disagree with pg_upgrade being operationally complex, but I
> > > >> don't see how this relates to contrib vs. non-contrib at all.  Are we
> > > >> supposed to only have "simple" programs in src/bin?  That seems a
> > > >> strange policy.
> > > >
> > > > Well, perhaps we need to re-open the discussion then.
> > > 
> > > I feel like putting it in src/bin would carry an implication of
> > > robustness that I'm not sanguine about.  Granted, putting it in
> > > contrib has already pushed the envelope in that direction further than
> > > is perhaps warranted.  But ISTM that if we ever want to put this in
> > > src/bin someone needs to devote some serious engineering time to
> > > filing down the rough edges.
> > 
> > I don't know how to file down any of the existing rough edges.
> 
> So do you have a list of rough edges?

Yes, the list of rough edges is the 14-steps you have to perform to run
pg_upgrade, as documented in the pg_upgrade manual page:

http://www.postgresql.org/docs/9.2/static/pgupgrade.html

The unknown is how to reduce the number of steps in a way the community
would find acceptable.

-- 
  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] WIP: pg_pretty_query

[Pavel Stehule]

2012/8/7 Thom Brown :
> On 7 August 2012 15:14, Pavel Stehule  wrote:
>> Hello
>>
>> last year we are spoke about reusing pretty print view code for some queries.
>>
>> Here is patch:
>>
>> this patch is really short - it is nice. But - it works only with
>> known database objects (probably we would it) and it doesn't format
>> subqueries well
>>
>>
>> postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
>> z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
>> z.a)', true, false);
>>  pg_pretty_query
>> --
>>   SELECT x.a, z.a+
>> FROM foo, foo x, x z +
>>WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
>> FROM foo +
>>WHERE foo.a = z.a))
>> (1 row)
>
> This looks odd:
>
> postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
> greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
> generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING < NULLS FIRST',
> true, false);
>  pg_pretty_query
> --
>   SELECT 1,  +
>  ( SELECT max(a.x) + GREATEST(2, 3)  +
> FROM generate_series(4, 10, 2) a(x)) +
> FROM generate_series(1, 100) generate_series(generate_series)+
>GROUP BY 1::integer   +
>ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3)   +
> FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
> (1 row)
>
> USING < is removed completely (or if I used DESC, NULLS FIRST is then
> removed instead), "2" in the order by is expanded to its full query,
> and generate_series when used in FROM is repeated with its own name as
> a parameter.  I'm also not sure about the spacing before each line.
> SELECT, FROM and GROUP BY all appear out of alignment from one
> another.

it is issue - probably we can start deserialization just from parser
stage, not from rewriter stage - but then code will be significantly
longer and we cannot reuse current code for pretty print view.

>
> Plus it would be nice if we could support something like the following style:
>
> SELECT
> field_one,
> field_two + field_three
> FROM
> my_table
> INNER JOIN
> another_table
> ON
> my_table.field_one = another_table.another_field
> AND
> another_table.valid = true
> WHERE
> field_one > 3
> AND
> field_two < 10;
>

it is second issue - probably there are more lovely styles - CELKO,
your and other. I am not sure if we can support more styles in core
(contrib should be better maybe).

Regards

Pavel

> But that's just a nice-to-have.
> --
> 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] -Wformat-zero-length

[Alvaro Herrera]

Excerpts from Bruce Momjian's message of vie ago 03 16:02:28 -0400 2012:
> On Fri, Aug  3, 2012 at 04:01:18PM -0400, Robert Haas wrote:
> > On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian  wrote:
> > >> I don't disagree with pg_upgrade being operationally complex, but I
> > >> don't see how this relates to contrib vs. non-contrib at all.  Are we
> > >> supposed to only have "simple" programs in src/bin?  That seems a
> > >> strange policy.
> > >
> > > Well, perhaps we need to re-open the discussion then.
> > 
> > I feel like putting it in src/bin would carry an implication of
> > robustness that I'm not sanguine about.  Granted, putting it in
> > contrib has already pushed the envelope in that direction further than
> > is perhaps warranted.  But ISTM that if we ever want to put this in
> > src/bin someone needs to devote some serious engineering time to
> > filing down the rough edges.
> 
> I don't know how to file down any of the existing rough edges.

So do you have a list of rough edges?

-- 
Álvaro Herrerahttp://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_pretty_query

[Thom Brown]

On 7 August 2012 15:14, Pavel Stehule  wrote:
> Hello
>
> last year we are spoke about reusing pretty print view code for some queries.
>
> Here is patch:
>
> this patch is really short - it is nice. But - it works only with
> known database objects (probably we would it) and it doesn't format
> subqueries well
>
>
> postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
> z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
> z.a)', true, false);
>  pg_pretty_query
> --
>   SELECT x.a, z.a+
> FROM foo, foo x, x z +
>WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
> FROM foo +
>WHERE foo.a = z.a))
> (1 row)

This looks odd:

postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING < NULLS FIRST',
true, false);
 pg_pretty_query
--
  SELECT 1,  +
 ( SELECT max(a.x) + GREATEST(2, 3)  +
FROM generate_series(4, 10, 2) a(x)) +
FROM generate_series(1, 100) generate_series(generate_series)+
   GROUP BY 1::integer   +
   ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3)   +
FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
(1 row)

USING < is removed completely (or if I used DESC, NULLS FIRST is then
removed instead), "2" in the order by is expanded to its full query,
and generate_series when used in FROM is repeated with its own name as
a parameter.  I'm also not sure about the spacing before each line.
SELECT, FROM and GROUP BY all appear out of alignment from one
another.

Plus it would be nice if we could support something like the following style:

SELECT
field_one,
field_two + field_three
FROM
my_table
INNER JOIN
another_table
ON
my_table.field_one = another_table.another_field
AND
another_table.valid = true
WHERE
field_one > 3
AND
field_two < 10;

But that's just a nice-to-have.
-- 
Thom

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

[HACKERS] WIP: pg_pretty_query

[Pavel Stehule]

Hello

last year we are spoke about reusing pretty print view code for some queries.

Here is patch:

this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well


postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z  where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
 pg_pretty_query
--
  SELECT x.a, z.a+
FROM foo, foo x, x z +
   WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
   WHERE foo.a = z.a))
(1 row)

Regards

Pavel


pg_pretty_query.patch
Description: Binary data

-- 
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] -Wformat-zero-length

[Bruce Momjian]

On Tue, Aug  7, 2012 at 03:06:23PM +0200, Magnus Hagander wrote:
> On Fri, Aug 3, 2012 at 10:02 PM, Bruce Momjian  wrote:
> > On Fri, Aug  3, 2012 at 04:01:18PM -0400, Robert Haas wrote:
> >> On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian  wrote:
> >> >> I don't disagree with pg_upgrade being operationally complex, but I
> >> >> don't see how this relates to contrib vs. non-contrib at all.  Are we
> >> >> supposed to only have "simple" programs in src/bin?  That seems a
> >> >> strange policy.
> >> >
> >> > Well, perhaps we need to re-open the discussion then.
> >>
> >> I feel like putting it in src/bin would carry an implication of
> >> robustness that I'm not sanguine about.  Granted, putting it in
> >> contrib has already pushed the envelope in that direction further than
> >> is perhaps warranted.  But ISTM that if we ever want to put this in
> >> src/bin someone needs to devote some serious engineering time to
> >> filing down the rough edges.
> >
> > I don't know how to file down any of the existing rough edges.
> 
> That would be the "serious engineering time" Robert is referring to,
> no? If you knew how to do it already it wouldn't require serious
> engineering time, just SMOP.

Oh, I read "serious _engineering_ time" to say that it is just a matter
of coding, while I don't even have a design idea of how to improve this,
meaning it is a lot farther away than just coding it.  I equiated
engineering with coding, which I guess was wrong.

-- 
  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] [PATCH] Docs: Make notes on sequences and rollback more obvious

[Kevin Grittner]

Robert Haas  wrote:
> Craig Ringer  wrote:
>> On 08/07/2012 02:27 AM, Robert Haas wrote:
>>>
>>> I did not commit the advanced.sgml changes.
>>
>> That's arguably the most important point to raise this. The most
>> recent question came from someone who actually bothered to RTFM
>> and believed based on the advanced-transactions page that
>> rollback rolls *everything* back.
>>
>> Some kind of hint that there are execptions is IMO very
>> important. I'm not sure what the best form for it to take is.
> 
> I'm not sure, either.  Maybe we should avoid blanket statements
> and just say something like:
> 
> Note: Some operations on sequences are non-transactional and will
> not be rolled back on transaction abort.  See .
 
I also think it's a problem that one can get through the entire
"Concurrency Control" chapter (mvcc.sgml) without a clue that
sequences aren't transactional.  I think maybe a mention in the
Introduction section of that chapter with a  would be
appropriate.
 
-Kevin

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

[HACKERS] Pg_ctl promote -- wait for slave to be promoted fully ?

[Magnus Hagander]

Should we consider if we can make "pg_ctl -w" work for "promote" as well?

The main problem is, I guess, that it can't log in - so wed' need
something like "PQping()" that actually checked if it was master or
slave?

//Magnus


-- Forwarded message --
From: Manoj Govindassamy 
Date: Fri, Jul 20, 2012 at 9:28 PM
Subject: [GENERAL] Pg_ctl promote -- wait for slave to be promoted fully ?
To: pgsql-gene...@postgresql.org


Hi team,

I am using PG 9.1.2 and I am promoting a slave to master with the
following command.

pg_ctl promote -D /pat/to/data

Command does return back faster with code 0 ( = success). I assumed
the slave is now the master and issued write operations. But the
statement failed as the DB complained that it can do only read
operation. Postgres.log showed "ready to accept connection" a second
later after I issued the statement. So, my understanding is "pg_ctl
promote" is triggering a slave promote and is "asynchornous".

-- Anyway I can query the state of DB to know its status slave or
master ?? So, that i can issue write statements only after I know that
the DB is the new master now

-- Or anyway to make pg_ctl promote to wait till the slave is
completely promoted ?


Any help is appreciated.

--
thanks,
Manoj



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


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] -Wformat-zero-length

[Magnus Hagander]

On Fri, Aug 3, 2012 at 10:02 PM, Bruce Momjian  wrote:
> On Fri, Aug  3, 2012 at 04:01:18PM -0400, Robert Haas wrote:
>> On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian  wrote:
>> >> I don't disagree with pg_upgrade being operationally complex, but I
>> >> don't see how this relates to contrib vs. non-contrib at all.  Are we
>> >> supposed to only have "simple" programs in src/bin?  That seems a
>> >> strange policy.
>> >
>> > Well, perhaps we need to re-open the discussion then.
>>
>> I feel like putting it in src/bin would carry an implication of
>> robustness that I'm not sanguine about.  Granted, putting it in
>> contrib has already pushed the envelope in that direction further than
>> is perhaps warranted.  But ISTM that if we ever want to put this in
>> src/bin someone needs to devote some serious engineering time to
>> filing down the rough edges.
>
> I don't know how to file down any of the existing rough edges.

That would be the "serious engineering time" Robert is referring to,
no? If you knew how to do it already it wouldn't require serious
engineering time, just SMOP.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Beta 3

[Magnus Hagander]

On Tue, Aug 7, 2012 at 2:44 PM, Robert Haas  wrote:
> On Mon, Aug 6, 2012 at 11:22 PM, Craig Ringer  wrote:
>> Heya all
>>
>> It seems like it's a bit trickier to find beta downloads than might be
>> ideal.
>>
>> The beta info page simply reads:
>>
>> " PostgreSQL 9.2 beta 3 was released on August 6, 2012."
>>
>> with no information about how to obtain a build or the sources, not even a
>> link to the downloads page, despite being linked to with the text:
>>
>> "More information on how to test and report issues:
>> http://www.postgresql.org/developer/beta";
>>
>> in the announcement email.
>>
>> It should at least link to:
>>
>>   http://www.postgresql.org/download/snapshots/
>>
>> and the -bugs form or guide to reporting problems page.
>>
>> The beta announcement email links to the main downloads page from which the
>> snapshots/beta downloads page can be reached, but the main beta page is a
>> dead-end. Not ideal for encouraging feedback and testing.
>
> Yeah, this is pretty bad.

Agreed. Anybody up for writing the text though? There was some text
earlier (see 
http://git.postgresql.org/gitweb/?p=pgweb.git;a=commitdiff;h=bd02e36141bb99e9ee4e0b80fd69464e0e6d91b7#patch18
or the corresponding one in the old pgweb repo). That can probably be
adapted to be put in the new one - seems it was genereally just
removed because there was no beta available, and the intentino wasn't
that the page should remain useless once a beta was out..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Beta 3

[Robert Haas]

On Mon, Aug 6, 2012 at 11:22 PM, Craig Ringer  wrote:
> Heya all
>
> It seems like it's a bit trickier to find beta downloads than might be
> ideal.
>
> The beta info page simply reads:
>
> " PostgreSQL 9.2 beta 3 was released on August 6, 2012."
>
> with no information about how to obtain a build or the sources, not even a
> link to the downloads page, despite being linked to with the text:
>
> "More information on how to test and report issues:
> http://www.postgresql.org/developer/beta";
>
> in the announcement email.
>
> It should at least link to:
>
>   http://www.postgresql.org/download/snapshots/
>
> and the -bugs form or guide to reporting problems page.
>
> The beta announcement email links to the main downloads page from which the
> snapshots/beta downloads page can be reached, but the main beta page is a
> dead-end. Not ideal for encouraging feedback and testing.

Yeah, this is pretty bad.

-- 
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] WIP Patch: Use sortedness of CSV foreign tables for query planning

[Robert Haas]

On Tue, Aug 7, 2012 at 2:02 AM, Etsuro Fujita
 wrote:
>> I think that optimizations like this are going to be essential for
>> things like pgsql_fdw (or other_rdms_fdw).  Despite the thorny
>> semantic issues, we're just not going to be able to get around it.
>> There will even be people who want SELECT * FROM ft ORDER BY 1 to
>> order by the remote side's notion of ordering rather than ours,
>> despite the fact that the remote side has some insane-by-PG-standards
>> definition of ordering.  People are going to find ways to do that kind
>> of thing whether we condone it or not, so we might as well start
>> thinking now about how we're going to live with it.  But that doesn't
>> answer the question of whether or not we ought to support it for
>> file_fdw in particular, which seems like a more arguable point.
>
> For file_fdw, I feel inclined to simply implement file_fdw (1) to verify the 
> key
> column is sorted in the specified way at the execution phase ie, at the 
> (first)
> scan of a data file, only when pathkeys are set, and (2) to abort the
> transaction if it detects the data file is not sorted.

That seems like an even worse idea.  People who want to access data
repeatedly should load it into tables.

Mind you, if you want to publish a version of file_fdw on PGXN that
does this, that's fine with me.  But I don't think it belongs in core,
at least not without a lot more evidence that there is a real demand
for this than we have so far.

-- 
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] is prefix pg_ reservated ?

[Robert Haas]

On Tue, Aug 7, 2012 at 7:16 AM, Pavel Stehule  wrote:
> Hello all
>
> I found strange behave of postgresql. I would to use name
> pg_stat_get_some for custom function. Probably it is not smart from
> me, because I found so functions that has prefix pg are not searched
> via search_path.
>
> postgres=# select public.pg_stat_get_creation_time('xxx'::regclass);
>pg_stat_get_creation_time
> ---
>  2012-08-07 12:43:25.592137+02
> (1 row)
>
> postgres=# select pg_stat_get_creation_time('xxx'::regclass);
> ERROR:  internal function "pg_stat_get_creation_time" is not in
> internal lookup table
>
> It is expected and desired behave?

That error message has nothing to do with a pg_ prefix, as you would
find out yourself if you grepped for it.  It means you created the
functional using language "internal", when you probably should have
used "c".

-- 
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] [PATCH] Docs: Make notes on sequences and rollback more obvious

[Robert Haas]

On Tue, Aug 7, 2012 at 3:59 AM, Craig Ringer  wrote:
> On 08/07/2012 02:27 AM, Robert Haas wrote:
>>
>> I did not commit the advanced.sgml changes.
>
> That's arguably the most important point to raise this. The most recent
> question came from someone who actually bothered to RTFM and believed based
> on the advanced-transactions page that rollback rolls *everything* back.
>
> Some kind of hint that there are execptions is IMO very important. I'm not
> sure what the best form for it to take is.

I'm not sure, either.  Maybe we should avoid blanket statements and
just say something like:

Note: Some operations on sequences are non-transactional and will not
be rolled back on transaction abort.  See .

-- 
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

[HACKERS] is prefix pg_ reservated ?

[Pavel Stehule]

Hello all

I found strange behave of postgresql. I would to use name
pg_stat_get_some for custom function. Probably it is not smart from
me, because I found so functions that has prefix pg are not searched
via search_path.

postgres=# select public.pg_stat_get_creation_time('xxx'::regclass);
   pg_stat_get_creation_time
---
 2012-08-07 12:43:25.592137+02
(1 row)

postgres=# select pg_stat_get_creation_time('xxx'::regclass);
ERROR:  internal function "pg_stat_get_creation_time" is not in
internal lookup table

It is expected and desired behave?

Regards

Pavel Stehule

-- 
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] Docs: Make notes on sequences and rollback more obvious

[Craig Ringer]

On 08/07/2012 02:27 AM, Robert Haas wrote:

I did not commit the advanced.sgml changes.


That's arguably the most important point to raise this. The most recent 
question came from someone who actually bothered to RTFM and believed 
based on the advanced-transactions page that rollback rolls *everything* 
back.


Some kind of hint that there are execptions is IMO very important. I'm 
not sure what the best form for it to take is.



 I am not sure I believe
the assertion that any function or type with special transactional
behavior will include a documentation mention.


It absolutely should, but I guess that doesn't mean it's guaranteed to.


It doesn't seem like a
terribly future-proof assertion at any rate.  With respect to the
mention of autocommit, I think it would be good to add something
there, but maybe it should cross-reference our existing documentation
mentions of autocommit.  Also, it's a bit ambiguous the way it's
worded whether you get the automatic BEGIN/COMMIT with autocommit=on
or with autocommit=off; somehow we should try to clarify what we mean
a little more there.


Yeah. I should've kept that separate, as it was something I noticed in 
passing, rather than central to the changes.


--
Craig Ringer


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