Re: [HACKERS] Release note bloat is getting out of hand

2015-02-02 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 On 02/02/2015 07:54 AM, Robert Haas wrote:
 The last 5 branches only takes us back to 9.0, which isn't very far.
 I would want to have at least the 8.x branches in the SGML build, and
 maybe the 7.x branches as well.  I would be happy to drop anything
 pre-7.x from the docs build and just let the people who care look at
 the SGML.  You seem to be assuming that nobody spends much time
 looking at the release notes for older branches, but that is certainly
 false in my own case.

 I was suggesting having a separate historical release notes tarball,
 actually.  If that's in SGML, and can be built using our doc tools, we
 haven't lost anything and we've reduced the size of the distribution
 tarball.

That was pretty much my point as well.  Sure, we can keep all the notes
online somewhere; that doesn't mean they have to be in the standard
distribution tarball, nor in the standard documentation build.

 One of the things I've been tinkering with for a while is a better
 searchable version of the release notes.  The problem I keep running
 into is that it's very difficult to write an error-free importer from
 the present SGML file; there's just too much variation in how certain
 things are recorded, and SGML just isn't a database import format.

The existing release notes are not conveniently searchable, for sure;
they're not in a single file, and they don't show up on a single page
on the Web, and I've never seen a PDF-searching tool that didn't suck.
So I'm bemused by Robert's insistence that he wants that format to support
searches.  As I said, I find it far more convenient to search the output
of git log and/or src/tools/git_changelog --- I keep text files of those
around for exactly that purpose.

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] Release note bloat is getting out of hand

2015-02-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Mon, Feb 2, 2015 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 So I'm bemused by Robert's insistence that he wants that format to support
 searches.  As I said, I find it far more convenient to search the output
 of git log and/or src/tools/git_changelog --- I keep text files of those
 around for exactly that purpose.

 I normally search in one of two ways.  Sometimes a grep the sgml;
 other times, I go to, say,
 http://www.postgresql.org/docs/devel/static/release-9-4.html and then
 edit the URL to take me back to 9.3, 9.2, 9.1, etc.  It's true that
 'git log' is often the place to go searching for stuff, but there are
 times when it's easier to find out what release introduced a feature
 by looking at the release notes, and it's certainly more useful if you
 want to send a link to someone who is not git-aware illustrating the
 results of your search.

 Well, maybe I'm the only one who is doing this and it's not worth
 worrying about it just for me.  But I do it, all the same.

I'm not out to take away a feature you need.  I'm just wondering why it
has to be supported in exactly the way it's done now.  Wouldn't a
separately maintained release-notes-only document serve the purpose fine?

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] Release note bloat is getting out of hand

2015-02-02 Thread Andreas Karlsson

On 02/02/2015 09:38 PM, Robert Haas wrote:

Well, maybe I'm the only one who is doing this and it's not worth
worrying about it just for me.  But I do it, all the same.


I do the later quite often: link people to old release notes. For me it 
would be fine to remove them from tar balls as long as they are still on 
the website.


--
Andreas Karlsson


--
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] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Roger Pack
On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote:
 On 01/31/2015 12:25 AM, Jim Nasby wrote:
 [snip]
 It's a bit more complex than that. First, toast isn't limited to
 bytea; it holds for ALL varlena fields in a table that are allowed to
 store externally. Second, the limit is actually per-table: every table
 gets it's own toast table, and each toast table is limited to 4B
 unique OIDs. Third, the OID counter is actually global, but the code
 should handle conflicts by trying to get another OID. See
 toast_save_datum(), which calls GetNewOidWithIndex().

 Now, the reality is that GetNewOidWithIndex() is going to keep
 incrementing the global OID counter until it finds an OID that isn't
 in the toast table. That means that if you actually get anywhere close
 to using 4B OIDs you're going to become extremely unhappy with the
 performance of toasting new data.

 Indeed ..

 I don't think it would be horrifically hard to change the way toast
 OIDs are assigned (I'm thinking we'd basically switch to creating a
 sequence for every toast table), but I don't think anyone's ever tried
 to push toast hard enough to hit this kind of limit.

 We did. The Billion Table Project, part2 (a.k.a. when does Postgres'
 OID allocator become a bottleneck) The allocator becomes
 essentially unusable at about 2.1B OIDs, where it performed very well at
 quite empty( 100M objects) levels.

 So yes, using one sequence per TOAST table should help.
 Combined with the new SequenceAMs / sequence implementation being
 proposed (specifically: one file for all sequences in a certain
 tablespace) this should scale much better.

But it wouldn't be perfect, right? I mean if you had multiple
deletion/insertions and pass 4B then the one sequence per TOAST
table would still wrap [albeit more slowly], and performance start
degrading the same way.  And there would still be the hard 4B limit.
Perhaps the foreign key to the TOAST table could be changed from oid
(32 bits) to something else (64 bits) [as well the sequence] so that
it never wraps?  What do you think? And would a more aggressive change
like this have a chance of being accepted into the code base?
Thanks.
-roger-


-- 
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] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread José Luis Tallón

On 02/02/2015 09:36 PM, Roger Pack wrote:

On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote:

On 01/31/2015 12:25 AM, Jim Nasby wrote:

[snip]
It's a bit more complex than that. First, toast isn't limited to
bytea; it holds for ALL varlena fields in a table that are allowed to
store externally. Second, the limit is actually per-table: every table
gets it's own toast table, and each toast table is limited to 4B
unique OIDs. Third, the OID counter is actually global, but the code
should handle conflicts by trying to get another OID. See
toast_save_datum(), which calls GetNewOidWithIndex().

Now, the reality is that GetNewOidWithIndex() is going to keep
incrementing the global OID counter until it finds an OID that isn't
in the toast table. That means that if you actually get anywhere close
to using 4B OIDs you're going to become extremely unhappy with the
performance of toasting new data.

Indeed ..


I don't think it would be horrifically hard to change the way toast
OIDs are assigned (I'm thinking we'd basically switch to creating a
sequence for every toast table), but I don't think anyone's ever tried
to push toast hard enough to hit this kind of limit.

We did. The Billion Table Project, part2 (a.k.a. when does Postgres'
OID allocator become a bottleneck) The allocator becomes
essentially unusable at about 2.1B OIDs, where it performed very well at
quite empty( 100M objects) levels.

So yes, using one sequence per TOAST table should help.
Combined with the new SequenceAMs / sequence implementation being
proposed (specifically: one file for all sequences in a certain
tablespace) this should scale much better.

But it wouldn't be perfect, right? I mean if you had multiple
deletion/insertions and pass 4B then the one sequence per TOAST
table would still wrap [albeit more slowly], and performance start
degrading the same way.  And there would still be the hard 4B limit.
Perhaps the foreign key to the TOAST table could be changed from oid
(32 bits) to something else (64 bits) [as well the sequence] so that
it never wraps?


Hmm 2^32 times aprox. 2kB (as per usual heuristics, ~4 rows per heap 
page) is 8796093022208 (~9e13) bytes

 ... which results in 8192 1GB segments :O
Looks like partitioning might be needed much sooner than that (if only 
for index efficiency reasons)... unless access is purely sequential.


The problem with changing the id from 32 to 64 bits is that the storage 
*for everybody else* doubles, making the implementation slower for 
most though this might be actually not that important.
The alternative could be some long LOB (HugeOBject?) using the 
equivalent to serial8 whereas regular LOBs would use serial4.



Anybody actually reaching this limit out there?



Regards,

/ J .L.



--
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] Release note bloat is getting out of hand

2015-02-02 Thread Robert Haas
On Mon, Feb 2, 2015 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 The existing release notes are not conveniently searchable, for sure;
 they're not in a single file, and they don't show up on a single page
 on the Web, and I've never seen a PDF-searching tool that didn't suck.
 So I'm bemused by Robert's insistence that he wants that format to support
 searches.  As I said, I find it far more convenient to search the output
 of git log and/or src/tools/git_changelog --- I keep text files of those
 around for exactly that purpose.

I normally search in one of two ways.  Sometimes a grep the sgml;
other times, I go to, say,
http://www.postgresql.org/docs/devel/static/release-9-4.html and then
edit the URL to take me back to 9.3, 9.2, 9.1, etc.  It's true that
'git log' is often the place to go searching for stuff, but there are
times when it's easier to find out what release introduced a feature
by looking at the release notes, and it's certainly more useful if you
want to send a link to someone who is not git-aware illustrating the
results of your search.

Well, maybe I'm the only one who is doing this and it's not worth
worrying about it just for me.  But I do it, all the same.

-- 
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] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Tom Lane
Roger Pack rogerdpa...@gmail.com writes:
 On 2/2/15, José Luis Tallón jltal...@adv-solutions.net wrote:
 So yes, using one sequence per TOAST table should help.
 Combined with the new SequenceAMs / sequence implementation being
 proposed (specifically: one file for all sequences in a certain
 tablespace) this should scale much better.

 But it wouldn't be perfect, right? I mean if you had multiple
 deletion/insertions and pass 4B then the one sequence per TOAST
 table would still wrap [albeit more slowly], and performance start
 degrading the same way.  And there would still be the hard 4B limit.
 Perhaps the foreign key to the TOAST table could be changed from oid
 (32 bits) to something else (64 bits) [as well the sequence] so that
 it never wraps?  What do you think? And would a more aggressive change
 like this have a chance of being accepted into the code base?

There has been some thought about this, but I have seen no, zero, reports
of anyone actually running into problems *in practice* (as opposed to
contrived cases like can we create a billion tables).  So we probably
aren't going to want to address it until it starts being a real problem.

The reason it's not as significant as you might think is that small field
values (less than a couple KB *after compression*) don't get pushed out
to the TOAST table, so they don't consume OIDs.  And large field values,
like megabytes worth, aren't a problem either because you just aren't
gonna have that many of them.  (Simple arithmetic.)  You could potentially
get into trouble if you had a whole lot of entries that were just a little
over the toasting threshold, because then you'd have a lot of OIDs
consumed but still a manageable total amount of disk space.  But that
doesn't seem to be a very common usage pattern.

Also, partitioning the table largely eliminates the problem because each
partition will have its own TOAST table.  I'm on record as saying that
many people are far too quick to decide that they need partitioning; but
once you get into the volume of data where 4B toast entries starts to
look like a limitation, you will probably have other reasons to think
that you need to partition.

In short, this is something that's theoretically interesting but doesn't
seem worth doing in practice --- yet anyway.

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] Release note bloat is getting out of hand

2015-02-02 Thread Andres Freund
On February 2, 2015 9:38:43 PM CET, Robert Haas robertmh...@gmail.com wrote:
On Mon, Feb 2, 2015 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 The existing release notes are not conveniently searchable, for sure;
 they're not in a single file, and they don't show up on a single page
 on the Web, and I've never seen a PDF-searching tool that didn't
suck.
 So I'm bemused by Robert's insistence that he wants that format to
support
 searches.  As I said, I find it far more convenient to search the
output
 of git log and/or src/tools/git_changelog --- I keep text files of
those
 around for exactly that purpose.

I normally search in one of two ways.  Sometimes a grep the sgml;
other times, I go to, say,
http://www.postgresql.org/docs/devel/static/release-9-4.html and then
edit the URL to take me back to 9.3, 9.2, 9.1, etc.  

FWIW I the same. Git log is great if you want all detail. But often enough the 
more condensed format of the release notes is helpful. Say, a customer has 
problems after migrating to a new version. It's quite a bit faster to read the 
section about incompatibilities than travel through the git log.

There's a reason the release notes exist. Given that they're apparently useful, 
it doesn't seem strange that devs sometimes read them...



--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Josh Berkus
On 02/02/2015 07:54 AM, Robert Haas wrote:
 I could live with keeping the ancient-branch release note SGML files
  around in HEAD --- I'd hoped to reduce the size of tarballs a bit, but the
  savings by that measure would only be a few percent (at present anyway).
  What's more important is to get them out of the main documentation build.
  So how about cutting the main doc build down to last-five-branches,
  and adding a non-default make target that produces a separate document
  consisting of (only) the complete release note history?
 The last 5 branches only takes us back to 9.0, which isn't very far.
 I would want to have at least the 8.x branches in the SGML build, and
 maybe the 7.x branches as well.  I would be happy to drop anything
 pre-7.x from the docs build and just let the people who care look at
 the SGML.  You seem to be assuming that nobody spends much time
 looking at the release notes for older branches, but that is certainly
 false in my own case.

I was suggesting having a separate historical release notes tarball,
actually.  If that's in SGML, and can be built using our doc tools, we
haven't lost anything and we've reduced the size of the distribution
tarball.

One of the things I've been tinkering with for a while is a better
searchable version of the release notes.  The problem I keep running
into is that it's very difficult to write an error-free importer from
the present SGML file; there's just too much variation in how certain
things are recorded, and SGML just isn't a database import format.

-- 
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] File based Incremental backup v9

2015-02-02 Thread Robert Haas
On Sat, Jan 31, 2015 at 6:47 PM, Marco Nenciarini
marco.nenciar...@2ndquadrant.it wrote:
 Il 31/01/15 17:22, Erik Rijkers ha scritto:
 On Sat, January 31, 2015 15:14, Marco Nenciarini wrote:

 0001-public-parse_filename_for_nontemp_relation.patch
 0002-copydir-LSN-v2.patch
 0003-File-based-incremental-backup-v8.patch

 Hi,

 It looks like it only compiles with assert enabled.


 It is due to a typo (assert instead of Assert). You can find the updated
 patch attached to this message.

I would sure like it if you would avoid changing the subject line
every time you post a new version of this patch.  It breaks the
threading for me.

It seems to have also broken it for the CommitFest app, which thinks
v3 is the last version.  I was not able to attach the new version.
When I clicked on attach thread without having logged in, it took me
to a bad URL.  When I clicked on it after having logged in, it
purported to work, but AFAICS, it didn't actually do anything.

-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Josh Berkus
On 02/02/2015 05:39 PM, Peter Eisentraut wrote:
 On 2/1/15 11:10 PM, Tom Lane wrote:
 I think it's time we changed the policy of including all release notes
 back to the beginning in Appendix E.
 
 I share the sentiment that the release notes *seem* too big, but the
 subsequent discussion shows that it's not clear why that's really a
 problem.  Exactly what problem are we trying to fix?

At a rough count of lines, the release notes for unsupported versions
are about 18% of documentation overall (47K out of 265K lines).  So
they're not insubstantial.  Compared to the total size of the tarball,
though ...

-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Joshua D. Drake


On 02/02/2015 07:54 AM, Robert Haas wrote:


The last 5 branches only takes us back to 9.0, which isn't very far.
I would want to have at least the 8.x branches in the SGML build, and
maybe the 7.x branches as well.  I would be happy to drop anything
pre-7.x from the docs build and just let the people who care look at
the SGML.  You seem to be assuming that nobody spends much time
looking at the release notes for older branches, but that is certainly
false in my own case.


It seems to me that the docs that are shipped should only contain 
information in regards to supported versions. Frankly there is no reason 
to ship any release notes except for the version that they are shipping 
with (e.g; there is no reason for 9.0 to be in 9.1). It is just bloat at 
that point when we can point everyone to the website or ftp site.


JD




--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc
If we send our children to Caesar for their education, we should
 not be surprised when they come back as Romans.


--
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] Release note bloat is getting out of hand

2015-02-02 Thread David G Johnston
On Mon, Feb 2, 2015 at 6:40 PM, Peter Eisentraut-2 [via PostgreSQL] 
ml-node+s1045698n5836471...@n5.nabble.com wrote:

 On 2/1/15 11:10 PM, Tom Lane wrote:
  I think it's time we changed the policy of including all release notes
  back to the beginning in Appendix E.

 I share the sentiment that the release notes *seem* too big, but the
 subsequent discussion shows that it's not clear why that's really a
 problem.  Exactly what problem are we trying to fix?


​We'd get a lines-of-code decrease which would translate into a improvement
in the make process time; most noticeable for someone doing a doc-only
build, multiple times, to see how a doc change looks.  No time percentage
has been provided yet but the goal seems reasonable in theory.

David J.​




--
View this message in context: 
http://postgresql.nabble.com/Release-note-bloat-is-getting-out-of-hand-tp5836330p5836473.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] Release note bloat is getting out of hand

2015-02-02 Thread Peter Eisentraut
On 2/1/15 11:10 PM, Tom Lane wrote:
 I think it's time we changed the policy of including all release notes
 back to the beginning in Appendix E.

I share the sentiment that the release notes *seem* too big, but the
subsequent discussion shows that it's not clear why that's really a
problem.  Exactly what problem are we trying to fix?



-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Robert Haas
On Sun, Feb 1, 2015 at 11:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I think it's time we changed the policy of including all release notes
 back to the beginning in Appendix E.  I seem to recall we debated this
 once before, and decided that we liked having all that project history
 visible.  But Release 6.0 is old enough to vote as of last week, so really
 we no longer need to prove anything about project stability/longevity.

 I propose that we go over to a policy of keeping in HEAD only release
 notes for actively maintained branches, and that each back branch should
 retain notes only for branches that were actively maintained when it split
 off from HEAD.  This would keep about five years worth of history in
 Appendix E, which should be a roughly stable amount of text.

-1.  I find it very useful to be able to go back through all the
release notes using grep, and have done so on multiple occasions.  It
sounds like this policy would make that harder, and I don't see what
we get out of of it.  It doesn't bother me that the SGML documentation
of the release notes is big; disk space is cheap.

-- 
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] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
Hello

Six years ago we did discuss about global temporary tables - persistent
schema, ephemeral data.

http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html

I am thinking so some reasons why implement this feature are valid:

* we can get some performance benefit against current temp tables - less
the catalogue bloating,

* we can simplify a static validation of plpgsql functions when temp tables
are used,
  more global temp tables are little bit comfortable for developers,

* we can simplify migration from some other databases, where global temp
tables are default.

Some was changed from 2009:

* We have updatable CTE

* We have unlogged tables

Opened questions:

1. Name and conformance with standard of this feature - because we doesn't
support modules, a mapping ANSI - PG should not be trivial

2. Implementation

I see three possible ways how to implement it:

2.a - using on demand created temp tables - most simple solution, but
doesn't help with catalogue bloating

2.b - using unlogged tables for holding statistics, relfilenode, and all
necessary data

3.c - store ephemeral metadata only in memory without MVCC

Is there still interest about this feature?

Comments, notes?

Pavel


Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma
 Some was changed from 2009:

 * We have updatable CTE

 * We have unlogged tables

 Opened questions:

 1. Name and conformance with standard of this feature - because we doesn't
 support modules, a mapping ANSI - PG should not be trivial

 2. Implementation

 I see three possible ways how to implement it:

 2.a - using on demand created temp tables - most simple solution, but
 doesn't help with catalogue bloating

 2.b - using unlogged tables for holding statistics, relfilenode, and all
 necessary data

 3.c - store ephemeral metadata only in memory without MVCC


With 2.a, essentially, we are defining a global definition of a temp table,
but the actual per session objects still follow the same rules are our
current temp tables do?

2.b seems like a lot of new data, and if we are defining new paths for e.g.
statistics for global temp tables, we might end up adding new logic in
planner to use those tables. I am not seeing how this will work.

Could you elaborate a bit on 3.c please?

Something that really bothers me here, on a different note, is the catalog
churn this could cause. Of course, you mentioned in as a point in your
email, but I feel that 2.a's showstopper could be the massive catalog churn
it causes. Maybe have a way to manage such tables without getting pg_class
to bloat pretty quickly (I am assuming you do not mean metadata as the
catalog metadata in 3.c).

Regards,

Atri


Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 11:51 GMT+01:00 Atri Sharma atri.j...@gmail.com:


 Some was changed from 2009:

 * We have updatable CTE

 * We have unlogged tables

 Opened questions:

 1. Name and conformance with standard of this feature - because we
 doesn't support modules, a mapping ANSI - PG should not be trivial

 2. Implementation

 I see three possible ways how to implement it:

 2.a - using on demand created temp tables - most simple solution, but
 doesn't help with catalogue bloating

 2.b - using unlogged tables for holding statistics, relfilenode, and all
 necessary data

 3.c - store ephemeral metadata only in memory without MVCC


 With 2.a, essentially, we are defining a global definition of a temp
 table, but the actual per session objects still follow the same rules are
 our current temp tables do?


yes .. it means global temp table is template for local temp table


 2.b seems like a lot of new data, and if we are defining new paths for
 e.g. statistics for global temp tables, we might end up adding new logic in
 planner to use those tables. I am not seeing how this will work.


The advantages of this method is transactional behave and moving some
bloating content to specific smaller and unlogged tables.



 Could you elaborate a bit on 3.c please?

 Something that really bothers me here, on a different note, is the catalog
 churn this could cause. Of course, you mentioned in as a point in your
 email, but I feel that 2.a's showstopper could be the massive catalog churn
 it causes. Maybe have a way to manage such tables without getting pg_class
 to bloat pretty quickly (I am assuming you do not mean metadata as the
 catalog metadata in 3.c).


3.c is good protection against catalog bloating - on second hand -
implementation will be probably more complex.



 Regards,

 Atri



Re: [HACKERS] Proposal : REINDEX xxx VERBOSE

2015-02-02 Thread Michael Paquier
On Mon, Feb 2, 2015 at 8:31 PM, Sawada Masahiko sawada.m...@gmail.com wrote:
 Attached patch adds VERBOSE option to REINDEX commands.
 Please give me feedbacks.
This could provide useful feedback to users. Now, I think that it may
be better to provide the keyword VERBOSE before the type of object
reindexed as REINDEX [ VERBOSE ] object. In any case, at quick sight,
the table completion for REINDEX is broken with your patch because by
typing REINDEX VERBOSE you would show the list of objects and once
again VERBOSE.
-- 
Michael


-- 
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] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma


  1. Main catalogue will be stable.
  2. There is not necessary to implement new storage and it can helps with
  transaction support.

 The amount of complexity that'd be involved to store catalog data in a
 separate relation around the caches and accesses would be significant. I
 don't think that's a realistic option.


Not to mention the problems we might end up in. We still have corner cases
in our cache code, and a new heap on top of it all might be just too
painful.


3.c - store ephemeral metadata only in memory without MVCC
  
   I think that's not an option. That'd end up being a massive amount of
   duplication at a low rate of functionality.
  
 
  I don't plan to implement a storage - I expect only few functions for
  store/read data from session memory context

 What does it have to do with temp tables then?


I think what Pavel means here is that we do not need a full fledged heap
layer and rather only a minimal API from a per session memory context.
However, that might be still as painful because we will eventually end up
inventing mechanisms for syscache and typcache to work with this storage,
which IMO is the biggest pain point around this idea.


Regards,

Atri

Regards,

Atri
*l'apprenant*


Re: [HACKERS] jsonb, unicode escapes and escaped backslashes

2015-02-02 Thread Robert Haas
On Sat, Jan 31, 2015 at 8:25 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I understand Andrew to be saying that if you take a 6-character string
 and convert it to a JSON string and then back to text, you will
 *usually* get back the same 6 characters you started with ... unless
 the first character was \, the second u, and the remainder hexadecimal
 digits.  Then you'll get back a one-character string or an error
 instead.  It's not hard to imagine that leading to surprising
 behavior, or even security vulnerabilities in applications that aren't
 expecting such a translation to happen under them.

 That *was* the case, with the now-reverted patch that changed the escaping
 rules.  It's not anymore:

 regression=# select to_json('\u1234'::text);
   to_json
 ---
  \\u1234
 (1 row)

 When you convert that back to text, you'll get \u1234, no more and no
 less.  For example:

 regression=# select array_to_json(array['\u1234'::text]);
  array_to_json
 ---
  [\\u1234]
 (1 row)

 regression=# select array_to_json(array['\u1234'::text])-0;
  ?column?
 ---
  \\u1234
 (1 row)

 regression=# select array_to_json(array['\u1234'::text])-0;
  ?column?
 --
  \u1234
 (1 row)

 Now, if you put in '\u1234'::jsonb and extract that string as text,
 you get some Unicode character or other.  But I'd say that a JSON user
 who is surprised by that doesn't understand JSON, and definitely that they
 hadn't read more than about one paragraph of our description of the JSON
 types.

Totally agree.  That's why I think reverting the patch was the right
thing to do.

-- 
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] Redesigning checkpoint_segments

2015-02-02 Thread Robert Haas
On Fri, Jan 30, 2015 at 3:58 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 During my tests, I did not observe the significance of
 min_recycle_wal_size
 parameter yet. Ofcourse, i had sufficient disk space for pg_xlog.

 I would like to understand more about min_recycle_wal_size parameter. In
 theory, i only understand from the note in the patch that if the disk
 space
 usage falls below certain threshold, min_recycle_wal_size number of WALs
 will be removed to accommodate future pg_xlog segments. I will try to test
 this out. Please let me know if there is any specific test to understand
 min_recycle_wal_size behaviour.

 min_recycle_wal_size comes into play when you have only light load, so that
 checkpoints are triggered by checkpoint_timeout rather than
 checkpoint_wal_size. In that scenario, the WAL usage will shrink down to
 min_recycle_wal_size, but not below that. Did that explanation help? Can you
 suggest changes to the docs to make it more clear?

First, as a general comment, I think we could do little that would
improve the experience of tuning PostgreSQL as much as getting this
patch committed with some reasonable default values for the settings
in question.  Shipping with checkpoint_segments=3 is a huge obstacle
to good performance.  It might be a reasonable value for
min_recycle_wal_size, but it's not a remotely reasonable upper bound
on WAL generated between checkpoints.  We haven't increased that limit
even once in the 14 years we've had it (cf.
4d14fe0048cf80052a3ba2053560f8aab1bb1b22) and typical disk sizes have
grown by an order of magnitude since then.

Second, I *think* that these settings are symmetric and, if that's
right, then I suggest that they ought to be named symmetrically.
Basically, I think you've got min_checkpoint_segments (the number of
recycled segments we keep around always) and max_checkpoint_segments
(the maximum number of segments we can have between checkpoints),
essentially splitting the current role of checkpoint_segments in half.
I'd go so far as to suggest we use exactly that naming.  It would be
reasonable to allow the value to be specified in MB rather than in
16MB units, and to specify it that way by default, but maybe a
unit-less value should have the old interpretation since everybody's
used to it.  That would require adding GUC_UNIT_XSEG or similar, but
that seems OK.

Also, I'd like to propose that we set the default value of
max_checkpoint_segments/checkpoint_wal_size to something at least an
order of magnitude larger than the current default setting.  I'll open
the bidding at 1600MB (aka 100).  I expect some pushback here, but I
don't think this is unreasonable; some people will need to raise it
further.  If you're generating 1600MB of WAL in 5 minutes, you're
either making the database bigger very quickly (in which case the
extra disk space that is consumed by the WAL will quickly blend into
the background) or you are updating the data already in the database
at a tremendous rate (in which case you are probably willing to burn
some disk space to have that go fast).  Right now, it's impractical to
ship something like checkpoint_segments=100 because we'd eat all that
space even on tiny databases with no activity.  But this patch fixes
that, so we might as well try to ship a default that's large enough to
use the database as something other than a toy.

-- 
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] Redesigning checkpoint_segments

2015-02-02 Thread Andres Freund
Hi,

On 2015-02-02 08:36:41 -0500, Robert Haas wrote:
 Also, I'd like to propose that we set the default value of
 max_checkpoint_segments/checkpoint_wal_size to something at least an
 order of magnitude larger than the current default setting.

+1

I think we need to increase checkpoint_timeout too - that's actually
just as important for the default experience from my pov. 5 minutes
often just unnecessarily generates FPWs en masse.

 I'll open the bidding at 1600MB (aka 100).

Fine with me.

Greetings,

Andres Freund

-- 
 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] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 13:36 GMT+01:00 Atri Sharma atri.j...@gmail.com:


  1. Main catalogue will be stable.
  2. There is not necessary to implement new storage and it can helps with
  transaction support.

 The amount of complexity that'd be involved to store catalog data in a
 separate relation around the caches and accesses would be significant. I
 don't think that's a realistic option.


 Not to mention the problems we might end up in. We still have corner cases
 in our cache code, and a new heap on top of it all might be just too
 painful.


3.c - store ephemeral metadata only in memory without MVCC
  
   I think that's not an option. That'd end up being a massive amount of
   duplication at a low rate of functionality.
  
 
  I don't plan to implement a storage - I expect only few functions for
  store/read data from session memory context

 What does it have to do with temp tables then?


 I think what Pavel means here is that we do not need a full fledged heap
 layer and rather only a minimal API from a per session memory context.
 However, that might be still as painful because we will eventually end up
 inventing mechanisms for syscache and typcache to work with this storage,
 which IMO is the biggest pain point around this idea.


It should be solvable - I see another risk - if we accelerate a work with
temp tables, then 4 byte oid should not be enough.




 Regards,

 Atri

 Regards,

 Atri
 *l'apprenant*



Re: [HACKERS] Comment patch for bgworker.c

2015-02-02 Thread Robert Haas
On Fri, Oct 24, 2014 at 8:51 PM, Jim Nasby jim.na...@bluetreble.com wrote:
 The comment for the BackgroundWorkerSlot structure tripped me up reviewing
 Robert's background worker patch; it made it clear that you need to use a
 memory barrier before setting in_use, but normally you'd never need to worry
 about that because RegisterDynamicBackgroundWorker() handles it for you.
 Patch adds a comment to that effect.

I vote to reject this patch.  I think it's explaining something that
doesn't really need to be explained, and shouldn't be explained like
this even if it does.  It adds a comment that reads Note that
RegisterDynamicBackgroundWorker() handles in_use correctly for you.
But the long block comment of which it is a part is entirely devoted
to explaining concerns internal to bgworker.c, from which I think it
should be inferred that all of the public APIs in that file handle all
of the things in that paragraph correctly (or are intended to,
anyway).

-- 
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] documentation update for doc/src/sgml/func.sgml

2015-02-02 Thread Robert Haas
On Tue, Jan 20, 2015 at 4:01 AM, Fabien COELHO coe...@cri.ensmp.fr wrote:
 I had a look at this patch.  This patch adds some text below a table
 of functions.  Immediately above that table, there is this existing
 language:

   The functions working with typedouble precision/type data are mostly
   implemented on top of the host system's C library; accuracy and behavior
 in
   boundary cases can therefore vary depending on the host system.

 This seems to me to substantially duplicate the information added by the
 patch.

 I would rather say that it explicites the potential issues. Taking that into
 account, maybe the part about floating point could be moved up after the
 above sentence, or the above sentence moved down as an introduction, with
 some pruning so that it fits in?

Possibly.  If anyone still cares about this patch, then they should
try revising it along those lines and submit an updated version.  If
no one is excited enough about this to do that, we should just flag
this as rejected and move on.  Since this patch has been kicking
around since August, my reading is nobody's very excited about it, but
maybe I'm misinterpreting the situation.

 The second paragraph about bitwise ops is not related to these.

Yeah, I'm not quite sure how that got in here; I don't see discussion
of it upthread.  It looks like it ought to be a completely separate
patch with its own discussion, FWICS.

-- 
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] Implementation of global temporary tables?

2015-02-02 Thread Atri Sharma
On Mon, Feb 2, 2015 at 6:34 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:



 2015-02-02 13:36 GMT+01:00 Atri Sharma atri.j...@gmail.com:


  1. Main catalogue will be stable.
  2. There is not necessary to implement new storage and it can helps
 with
  transaction support.

 The amount of complexity that'd be involved to store catalog data in a
 separate relation around the caches and accesses would be significant. I
 don't think that's a realistic option.


 Not to mention the problems we might end up in. We still have corner
 cases in our cache code, and a new heap on top of it all might be just too
 painful.


3.c - store ephemeral metadata only in memory without MVCC
  
   I think that's not an option. That'd end up being a massive amount of
   duplication at a low rate of functionality.
  
 
  I don't plan to implement a storage - I expect only few functions for
  store/read data from session memory context

 What does it have to do with temp tables then?


 I think what Pavel means here is that we do not need a full fledged heap
 layer and rather only a minimal API from a per session memory context.
 However, that might be still as painful because we will eventually end up
 inventing mechanisms for syscache and typcache to work with this storage,
 which IMO is the biggest pain point around this idea.


 It should be solvable - I see another risk - if we accelerate a work with
 temp tables, then 4 byte oid should not be enough.




Hrm, that might well be true. It might be worth the effort to find a better
way to materialize global temp tables then, like having a single OID and
only materializing a relfilenode for a session when the session inserts
into the temp table. Not sure here at all...



-- 
Regards,

Atri
*l'apprenant*


Re: [HACKERS] Perl coding error in msvc build system?

2015-02-02 Thread Robert Haas
On Fri, Jan 23, 2015 at 4:17 PM, Brar Piening b...@gmx.de wrote:
 Am 23.01.2015 um 09:17 schrieb Abhijit Menon-Sen:
 At 2014-06-03 22:30:50 -0400, pete...@gmx.net wrote:
 I'm not sure whether the following coding actually detects any errors:

 Solution.pm:

  open(P, cl /? 21 |) || die cl command not found;

 Since nobody with a Windows system has commented, I'm just writing to
 say that from a Perl perspective, I agree with your analysis and the
 patch looks perfectly sensible.


 I can confirm it on my Windows system.

 Calling build from a console without nmake in the path I always get:
 Unable to determine Visual Studio version: The nmake version could not be
 determined. at src/tools/msvc/Mkvcbuild.pm line 63.

 This means that the following construct in VSObjectFactory.pm doesn't have
 the desired effect.
 open(P, nmake /? 21 |)
   || croak
 Unable to determine Visual Studio version: The nmake command wasn't
 found.;

 On the other hand complicacy  is in the eye of the beholder.
 Perl constructs like the following get quite a few wtf's
 (http://www.osnews.com/story/19266/WTFs_m) from a simple-minded person like
 me.
 $?  8 == 0 or die cl command not found;

 However as it fixes a confirmed problem and as maintainance of perl code is
 an issue of its own, please go ahead.

This patch been reviewed by 4 people, resulting in 2 minor suggestions
for changes (adding an m modifier, and removing a bogus last).

It has 2 clear upvotes and 0 downvotes.

I think it should be revised along the lines suggested and committed
without further ado.

-- 
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] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 13:15 GMT+01:00 Andres Freund and...@2ndquadrant.com:

 On 2015-02-02 12:24:44 +0100, Pavel Stehule wrote:
  2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com:
2.b - using unlogged tables for holding statistics, relfilenode, and
 all
necessary data
  
   I can't follow why that'd achieve anything?
  
 
  1. Main catalogue will be stable.
  2. There is not necessary to implement new storage and it can helps with
  transaction support.

 The amount of complexity that'd be involved to store catalog data in a
 separate relation around the caches and accesses would be significant. I
 don't think that's a realistic option.

3.c - store ephemeral metadata only in memory without MVCC
  
   I think that's not an option. That'd end up being a massive amount of
   duplication at a low rate of functionality.
  
 
  I don't plan to implement a storage - I expect only few functions for
  store/read data from session memory context

 What does it have to do with temp tables then?


it is mechanism how to store a session metadata related to global temp
tables



   I think it's more realistic way to implement is to have a separate
   'relpersistence' setting for global temp tables. The first access to
   such one in a session (or xact if truncate on commit) copies the table
   from the _init fork. By having the backend id in all filenames (besides
   the init fork) they're unique between sessions.
  
  
  If I understand well, it is similar to my fast implementation from 2008.
 It
  works partially,  because it doesn't solve other (session) property -
 like
  relpages, reltuples and related data from pg_statistics

 I'm honestly not particularly concerned about that problem. For one, we
 don't auto-analyze/vacuum temp tables. For another, it'd be
 comparatively easy to gather reltuples/relpages/stats from session local
 state if necessary. Those are all only accessed from a few places.


so I don't see a big differences from 3.c - all session metadata will
stored in session memory.

I didn't write code, so I have not a knowledge about details.


 Greetings,

 Andres Freund

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



Re: [HACKERS] pg_basebackup fails with long tablespace paths

2015-02-02 Thread Robert Haas
On Fri, Nov 7, 2014 at 9:03 PM, Peter Eisentraut pete...@gmx.net wrote:
 On 11/4/14 3:52 PM, Peter Eisentraut wrote:
 Here are patches to address that.  First, it reports errors when
 attempting to create a tar header that would truncate file or symlink
 names.  Second, it works around the problem in the tests by creating a
 symlink from the short-name tempdir that we had set up for the
 Unix-socket directory case.

 I ended up splitting this up differently.  I applied to part of the
 second patch that works around the length issue in tablespaces.  So the
 tests now pass in 9.4 and up even in working directories with long
 names.  This clears up the regression in 9.4.

 The remaining, not applied patch is attached.  It errors when the file
 name is too long and adds tests for that.  This could be applied to 9.5
 and backpatched, if we so choose.  It might become obsolete if
 https://commitfest.postgresql.org/action/patch_view?id=1512 is accepted.
  If that patch doesn't get accepted, I might add my patch to a future
 commit fest.

I think we should commit this, where by this I mean your patch to
error-check the length of filenames and symlinks instead of truncating
them.  I don't know what will become of Amit's patch, but I think this
is a good idea anyway.  We should perhaps even consider back-patching
it, because silently eating people's data is generally not cool.  It's
possible that there are people out there who know that their filenames
and links are being truncated and don't care, and those people would
be unhappy to see this back-patched.  However, it's also possible that
there are people who don't know that this is happening and do care,
and those people would be happy about a back-patch.  I don't know
which group is larger.  At the least, I think we should apply it to
master; because whatever we end up doing about Amit's patch, adding
error checks for conditions where we're chewing up somebody's
filenames and spitting out what's left over has got to be a good
thing.

-- 
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] Proposal : REINDEX xxx VERBOSE

2015-02-02 Thread Sawada Masahiko
On Mon, Feb 2, 2015 at 9:21 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
 On Mon, Feb 2, 2015 at 8:31 PM, Sawada Masahiko sawada.m...@gmail.com wrote:
 Attached patch adds VERBOSE option to REINDEX commands.
 Please give me feedbacks.
 This could provide useful feedback to users.

Thanks.

 Now, I think that it may
 be better to provide the keyword VERBOSE before the type of object
 reindexed as REINDEX [ VERBOSE ] object.

Actually, my first WIP version of patch added VERBOSE word at before
type of object.
I'm feeling difficult about that the position of VERBOSE word in
REINDEX statement.

 In any case, at quick sight,
 the table completion for REINDEX is broken with your patch because by
 typing REINDEX VERBOSE you would show the list of objects and once
 again VERBOSE.

I have also rebased the tab-completion source, I think it's not happen.
In my environment, it does not show list of object and VERBOSE again
after typing REINDEX VERBOSE.

Regards,

---
Sawada Masahiko


-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Michael Paquier
On Mon, Feb 2, 2015 at 9:57 PM, Robert Haas robertmh...@gmail.com wrote:
 On Sun, Feb 1, 2015 at 11:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I propose that we go over to a policy of keeping in HEAD only release
 notes for actively maintained branches, and that each back branch should
 retain notes only for branches that were actively maintained when it split
 off from HEAD.  This would keep about five years worth of history in
 Appendix E, which should be a roughly stable amount of text.

 -1.  I find it very useful to be able to go back through all the
 release notes using grep, and have done so on multiple occasions.  It
 sounds like this policy would make that harder, and I don't see what
 we get out of of it.  It doesn't bother me that the SGML documentation
 of the release notes is big; disk space is cheap.
FWIW, -0.5. I think that we should keep documentation down to the
oldest version supported by binary tools, I am referring particularly
to pg_dump that supports servers down to 7.0. Such information may be
useful for a dump/restore upgrade.
-- 
Michael


-- 
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] REINDEX CONCURRENTLY 2.0

2015-02-02 Thread Andres Freund
On 2014-11-12 16:11:58 -0500, Robert Haas wrote:
 On Wed, Nov 12, 2014 at 4:10 PM, Robert Haas robertmh...@gmail.com wrote:
  On Thu, Nov 6, 2014 at 9:50 AM, Peter Eisentraut pete...@gmx.net wrote:
  If REINDEX cannot work without an exclusive lock, we should invent some
  other qualifier, like WITH FEWER LOCKS.
 
  What he said.
 
 But more to the point  why, precisely, can't this work without an
 AccessExclusiveLock?  And can't we fix that instead of setting for
 something clearly inferior?

So, here's an alternative approach of how to get rid of the AEL
locks. They're required because we want to switch the relfilenodes
around. I've pretty much no confidence in any of the schemes anybody has
come up to avoid that.

So, let's not switch relfilenodes around.

I think if we should instead just use the new index, repoint the
dependencies onto the new oid, and then afterwards, when dropping,
rename the new index one onto the old one. That means the oid of the
index will change and some less than pretty grovelling around
dependencies, but it still seems preferrable to what we're discussing
here otherwise.

Does anybody see a fundamental problem with that approach?

Greetings,

Andres Freund

-- 
 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] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com:

 Hi,

 On 2015-02-02 11:15:22 +0100, Pavel Stehule wrote:
  Six years ago we did discuss about global temporary tables - persistent
  schema, ephemeral data.
 
  http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html
 
  I am thinking so some reasons why implement this feature are valid:
 
  * we can get some performance benefit against current temp tables - less
  the catalogue bloating,
 
  * we can simplify a static validation of plpgsql functions when temp
 tables
  are used,
more global temp tables are little bit comfortable for developers,
 
  * we can simplify migration from some other databases, where global temp
  tables are default.

 I agree that the feature would be interesting.

  2. Implementation
 
  I see three possible ways how to implement it:
 
  2.a - using on demand created temp tables - most simple solution, but
  doesn't help with catalogue bloating

 Yea, that's no good.

  2.b - using unlogged tables for holding statistics, relfilenode, and all
  necessary data

 I can't follow why that'd achieve anything?


1. Main catalogue will be stable.
2. There is not necessary to implement new storage and it can helps with
transaction support.



  3.c - store ephemeral metadata only in memory without MVCC

 I think that's not an option. That'd end up being a massive amount of
 duplication at a low rate of functionality.


I don't plan to implement a storage - I expect only few functions for
store/read data from session memory context



 I think it's more realistic way to implement is to have a separate
 'relpersistence' setting for global temp tables. The first access to
 such one in a session (or xact if truncate on commit) copies the table
 from the _init fork. By having the backend id in all filenames (besides
 the init fork) they're unique between sessions.


If I understand well, it is similar to my fast implementation from 2008. It
works partially,  because it doesn't solve other (session) property - like
relpages, reltuples and related data from pg_statistics


 Or something roughly like that.

 Greetings,

 Andres Freund

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



Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Andres Freund
On 2015-02-02 12:24:44 +0100, Pavel Stehule wrote:
 2015-02-02 12:04 GMT+01:00 Andres Freund and...@2ndquadrant.com:
   2.b - using unlogged tables for holding statistics, relfilenode, and all
   necessary data
 
  I can't follow why that'd achieve anything?
 
 
 1. Main catalogue will be stable.
 2. There is not necessary to implement new storage and it can helps with
 transaction support.

The amount of complexity that'd be involved to store catalog data in a
separate relation around the caches and accesses would be significant. I
don't think that's a realistic option.

   3.c - store ephemeral metadata only in memory without MVCC
 
  I think that's not an option. That'd end up being a massive amount of
  duplication at a low rate of functionality.
 
 
 I don't plan to implement a storage - I expect only few functions for
 store/read data from session memory context

What does it have to do with temp tables then?

  I think it's more realistic way to implement is to have a separate
  'relpersistence' setting for global temp tables. The first access to
  such one in a session (or xact if truncate on commit) copies the table
  from the _init fork. By having the backend id in all filenames (besides
  the init fork) they're unique between sessions.
 
 
 If I understand well, it is similar to my fast implementation from 2008. It
 works partially,  because it doesn't solve other (session) property - like
 relpages, reltuples and related data from pg_statistics

I'm honestly not particularly concerned about that problem. For one, we
don't auto-analyze/vacuum temp tables. For another, it'd be
comparatively easy to gather reltuples/relpages/stats from session local
state if necessary. Those are all only accessed from a few places.

Greetings,

Andres Freund

-- 
 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] Implementation of global temporary tables?

2015-02-02 Thread Pavel Stehule
2015-02-02 11:15 GMT+01:00 Pavel Stehule pavel.steh...@gmail.com:

 Hello

 Six years ago we did discuss about global temporary tables - persistent
 schema, ephemeral data.

 http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html

 I am thinking so some reasons why implement this feature are valid:

 * we can get some performance benefit against current temp tables - less
 the catalogue bloating,

 * we can simplify a static validation of plpgsql functions when temp
 tables are used,
   more global temp tables are little bit comfortable for developers,

 * we can simplify migration from some other databases, where global temp
 tables are default.


I forgot other possible benefit:

* using temp tables on slaves - (needs 3c implementation)



 Some was changed from 2009:

 * We have updatable CTE

 * We have unlogged tables

 Opened questions:

 1. Name and conformance with standard of this feature - because we doesn't
 support modules, a mapping ANSI - PG should not be trivial

 2. Implementation

 I see three possible ways how to implement it:

 2.a - using on demand created temp tables - most simple solution, but
 doesn't help with catalogue bloating

 2.b - using unlogged tables for holding statistics, relfilenode, and all
 necessary data

 3.c - store ephemeral metadata only in memory without MVCC

 Is there still interest about this feature?

 Comments, notes?

 Pavel



Re: [HACKERS] Implementation of global temporary tables?

2015-02-02 Thread Andres Freund
Hi,

On 2015-02-02 11:15:22 +0100, Pavel Stehule wrote:
 Six years ago we did discuss about global temporary tables - persistent
 schema, ephemeral data.
 
 http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html
 
 I am thinking so some reasons why implement this feature are valid:
 
 * we can get some performance benefit against current temp tables - less
 the catalogue bloating,
 
 * we can simplify a static validation of plpgsql functions when temp tables
 are used,
   more global temp tables are little bit comfortable for developers,
 
 * we can simplify migration from some other databases, where global temp
 tables are default.

I agree that the feature would be interesting.

 2. Implementation
 
 I see three possible ways how to implement it:
 
 2.a - using on demand created temp tables - most simple solution, but
 doesn't help with catalogue bloating

Yea, that's no good.

 2.b - using unlogged tables for holding statistics, relfilenode, and all
 necessary data

I can't follow why that'd achieve anything?

 3.c - store ephemeral metadata only in memory without MVCC

I think that's not an option. That'd end up being a massive amount of
duplication at a low rate of functionality.


I think it's more realistic way to implement is to have a separate
'relpersistence' setting for global temp tables. The first access to
such one in a session (or xact if truncate on commit) copies the table
from the _init fork. By having the backend id in all filenames (besides
the init fork) they're unique between sessions.

Or something roughly like that.

Greetings,

Andres Freund

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


[HACKERS] Proposal : REINDEX xxx VERBOSE

2015-02-02 Thread Sawada Masahiko
Hi all,

Attached patch adds VERBOSE option to REINDEX commands.
The another maintaining commands(VACUUM FULL, CLUSTER) has VERBOSE option,
but REINDEX has not been had it.

Examples is following,

- REINDEX TABLE
[postgres][5432](1)=# REINDEX TABLE VERBOSE hoge;
INFO:  index hoge_idx was reindexed.
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index hoge2_idx was reindexed.
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
REINDEX

- REINDEX SCHEMA
[postgres][5432](1)=# REINDEX SCHEMA VERBOSE s;
INFO:  index hoge_idx was reindexed.
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index hoge2_idx was reindexed.
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  indexes of whole table s.hoge were reindexed
REINDEX

Please give me feedbacks.

Regards,

---
Sawada Masahiko


000_reindex_verbose_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] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Jim Nasby

On 2/2/15 3:50 PM, José Luis Tallón wrote:

Hmm 2^32 times aprox. 2kB (as per usual heuristics, ~4 rows per heap
page) is 8796093022208 (~9e13) bytes
  ... which results in 8192 1GB segments :O
Looks like partitioning might be needed much sooner than that (if only
for index efficiency reasons)... unless access is purely sequential.

The problem with changing the id from 32 to 64 bits is that the storage
*for everybody else* doubles, making the implementation slower for
most though this might be actually not that important.
The alternative could be some long LOB (HugeOBject?) using the
equivalent to serial8 whereas regular LOBs would use serial4.


Well, it depends on how we did this. We could (for example) add a field 
to pg_class that determines what type to use for toast pointers; OID, 
int, or bigint. That could then be taken into account in the *toast* 
functions.


But as others have pointed out, we haven't even had any real complaints 
about toast using OIDs as being an issue until now, so I think it's 
premature to start messing with this. At most it's just something to 
keep in mind so we don't preclude doing this in the future.


BTW, regarding the size of what gets toasted; I've often thought it 
would be useful to allow a custom size limit on columns so that you 
could easily force data to be toasted if you knew you were very unlikely 
to access it. Basically, a cheap form of vertical partitioning.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


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


[HACKERS] Missing markup in pg_receivexlog.sgml

2015-02-02 Thread Michael Paquier
Hi all,

Per $subject, I noticed that a markup was missing in the description
of the option --synchronous.
Patch is attached.
Regards,
-- 
Michael
diff --git a/doc/src/sgml/ref/pg_receivexlog.sgml b/doc/src/sgml/ref/pg_receivexlog.sgml
index be321b5..9405f0f 100644
--- a/doc/src/sgml/ref/pg_receivexlog.sgml
+++ b/doc/src/sgml/ref/pg_receivexlog.sgml
@@ -139,9 +139,10 @@ PostgreSQL documentation
   termoption--synchronous/option/term
   listitem
para
-Issue sync commands as soon as there is WAL data which has not been
-flushed yet. Also status packets are sent back to the server just after
-WAL data is flushed whatever literal--status-interval/ is set to.
+Issue commandsync/ commands as soon as there is WAL data which has
+not been flushed yet. Also status packets are sent back to the server
+just after WAL data is flushed whatever literal--status-interval/
+is set to.
/para
   /listitem
  /varlistentry

-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 On 02/02/2015 05:39 PM, Peter Eisentraut wrote:
 I share the sentiment that the release notes *seem* too big, but the
 subsequent discussion shows that it's not clear why that's really a
 problem.  Exactly what problem are we trying to fix?

 At a rough count of lines, the release notes for unsupported versions
 are about 18% of documentation overall (47K out of 265K lines).  So
 they're not insubstantial.  Compared to the total size of the tarball,
 though ...

It would not make that much of a difference in tarball size, agreed.
It *would* make a difference in the build time and output size of the
SGML docs --- as I mentioned at the outset, the release notes currently
account for 25% of the SGML source linecount.

Now, that's probably still only marginally a problem, but my real
point is that this is not sustainable.  The release notes are growing
faster than the rest of the docs.  This isn't so obvious if you compare
adjacent release branches, but over a slightly longer timescale it is.
A quick wc -l in my current git checkouts gives

Release release-*.sgml  all .sgml   Percent

8.3 37770   204060  18.5
9.0 59318   250493  23.7
HEAD85672   336874  25.4

We can stick our heads in the sand for awhile longer yet, but
eventually this is going to have to be dealt with.

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] pgbench -f and vacuum

2015-02-02 Thread Michael Paquier
On Wed, Dec 24, 2014 at 12:42 AM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Although that might be taking this thread rather far off-topic.
 Not really sure about that, because the only outstanding objection to
 this discussion is what happens in the startup stage if you specify -f.
 Right now vacuum is attempted on the standard tables, which is probably
 not the right thing in the vast majority of cases.  But if we turn that
 off, how do we reinstate it for the rare cases that want it?  Personally
 I would just leave it turned off and be done with it, but if we want to
 provide some way to re-enable it, this --startup-script=FILE gadget
 sounds like a pretty decent idea.
(Catching up with this thread)
Yes I think that it would be more simple to simply turn off the
internal VACUUM if -f is specified. For the cases where we still need
to vacuum the tables pgbench_*, we could simply document to run a
VACUUM on them.
-- 
Michael


-- 
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] Release note bloat is getting out of hand

2015-02-02 Thread Jim Nasby

On 2/2/15 3:10 PM, Andres Freund wrote:

On February 2, 2015 9:38:43 PM CET, Robert Haas robertmh...@gmail.com wrote:

On Mon, Feb 2, 2015 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:

The existing release notes are not conveniently searchable, for sure;
they're not in a single file, and they don't show up on a single page
on the Web, and I've never seen a PDF-searching tool that didn't

suck.

So I'm bemused by Robert's insistence that he wants that format to

support

searches.  As I said, I find it far more convenient to search the

output

of git log and/or src/tools/git_changelog --- I keep text files of

those

around for exactly that purpose.


I normally search in one of two ways.  Sometimes a grep the sgml;
other times, I go to, say,
http://www.postgresql.org/docs/devel/static/release-9-4.html and then
edit the URL to take me back to 9.3, 9.2, 9.1, etc.


FWIW I the same. Git log is great if you want all detail. But often enough the 
more condensed format of the release notes is helpful. Say, a customer has 
problems after migrating to a new version. It's quite a bit faster to read the 
section about incompatibilities than travel through the git log.


This wouldn't prevent that; you could still point them to 
http://www.postgresql.org/docs/7.1/static/release-0-01.html



There's a reason the release notes exist. Given that they're apparently useful, 
it doesn't seem strange that devs sometimes read them...


Sure, but dev's have any number of other ways to get at this info, and 
in a fashion that's actually *more* useful to them. Several people have 
asked for a single grep-able file, for example. ISTM that keeping such a 
file around in the source (and perhaps in /src instead of /doc) should 
be easy.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.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] Hot Standby WAL reply uses heavyweight session locks, but doesn't have enough infrastructure set up

2015-02-02 Thread Michael Paquier
On Sat, Jan 31, 2015 at 5:34 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2015-01-29 11:01:51 -0500, Robert Haas wrote:
 On Wed, Jan 28, 2015 at 2:41 AM, Michael Paquier
 michael.paqu...@gmail.com wrote:
  Andres Freund wrote:
  I think this isn't particularly pretty, but it seems to be working well
  enough, and changing it would be pretty invasive. So keeping in line
  with all that code seems to be easier.
  OK, I'm convinced with this part to remove the call of
  LockSharedObjectForSession that uses dontWait and replace it by a loop
  in ResolveRecoveryConflictWithDatabase.

 That seems right to me, too.

 It's slightly more complicated than that. The lock conflict should
 actually be resolved using ResolveRecoveryConflictWithLock()... That,
 combined with the race of connecting a actually already deleted database
 (see the XXXs I removed) seem to make the approach in here.

 Attached are two patches:
 1) Infrastructure for attaching more kinds of locks on the startup
process.
 2) Use that infrastructure for database locks during replay.

 I'm not sure 2) alone would be sufficient justification for 1), but the
 nearby thread about basebackups also require similar infrastructure...

Some comments about patch 1:
-* No locking is required here because we already acquired
-* AccessExclusiveLock. Anybody trying to connect while we do this will
-* block during InitPostgres() and then disconnect when they see the
-* database has been removed.
+* No locking is required here because we already acquired a
+* AccessExclusiveLock on the database in dbase_redo().
Anybody trying to
+* connect while we do this will block during InitPostgres() and then
+* disconnect when they see the database has been removed.
 */
This change looks unnecessary, I'd rather let it as-is.

-  RecoveryLockList contains entry for lock no longer recorded by
lock manager: xid %u database %u relation %u,
-  lock-xid, lock-dbOid, lock-relOid);
+RecoveryLockList contains entry for lock no longer recorded by
lock manager: xid %u,
+ lock-xid);
This patch is making the information provided less verbose, and I
think that it is useful to have some information not only about the
lock held, but as well about the database and the relation.
Also, ISTM that StandbyAcquireLock should still use a database OID and
a relation OID instead of a only LOCKTAG, and SET_LOCKTAG_RELATION
should be set in StandbyAcquireLock while
ResolveRecoveryConflictWithLock is extended only with the lock mode as
new argument. (Patch 2 adds many calls to SET_LOCKTAG_RELATION btw
justidying to keep he API changes minimal).

There are some typos in the commit message:
s/shanges/changes
s/exlusive/exclusive

In patch 2, isn't it necessary to bump XLOG_PAGE_MAGIC?
-- 
Michael


-- 
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] Comment patch for bgworker.c

2015-02-02 Thread Jim Nasby

On 2/2/15 7:49 AM, Robert Haas wrote:

On Fri, Oct 24, 2014 at 8:51 PM, Jim Nasby jim.na...@bluetreble.com wrote:

The comment for the BackgroundWorkerSlot structure tripped me up reviewing
Robert's background worker patch; it made it clear that you need to use a
memory barrier before setting in_use, but normally you'd never need to worry
about that because RegisterDynamicBackgroundWorker() handles it for you.
Patch adds a comment to that effect.


I vote to reject this patch.  I think it's explaining something that
doesn't really need to be explained, and shouldn't be explained like
this even if it does.  It adds a comment that reads Note that
RegisterDynamicBackgroundWorker() handles in_use correctly for you.
But the long block comment of which it is a part is entirely devoted
to explaining concerns internal to bgworker.c, from which I think it
should be inferred that all of the public APIs in that file handle all
of the things in that paragraph correctly (or are intended to,
anyway).


At this point I don't remember what it was in your patch that tripped me 
up on this, so I'm marking the patch rejected.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.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] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-02-02 Thread Peter Geoghegan
On Mon, Feb 02, 2015 at 4:48 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 I think that the fundamental, unfixable race condition here is the
 disconnect between index tuple insertion and checking for would-be
 exclusion violations that exclusion constraints naturally have here,
 that unique indexes naturally don't have [1] (note that I'm talking
 only about approach #2 to value locking here; approach #1 isn't in
 V2.0). I suspect that the feature is not technically feasible to make
 work correctly with exclusion constraints, end of story. VACUUM
 interlocking is probably also involved here, but the unfixable race
 condition seems like our fundamental problem.

 It's not a fundamental, unfixable race condition. In [1], I gave you
 three ideas straight off the top of my head on how that could be fixed.

That was different - I tried to make it work by fixing some bugs
there. However, I'm now finding myself up against these new bugs. I
think that the underlying cause is the lack of any real locking
(unlike with the B-Tree AM) in *both* cases, but I don't even know
that for sure. The error messages you see are quite odd - why should a
btree_gist-based exclusion constraint cause a violation when
non-conflicting values are inserted? There is some other race
condition here. This wasn't a livelock (or a deadlock), which is what
your comments in early January apply to. I think that this has
something to do with VACUUM interlocking. But with the B-Tree AM
(which we're handling differently, by re-using infrastructure used for
deferred unique constraints), things work quite well. The patch stands
up to Jeff's vigorous stress-tests.

I'm not fundamentally in disagreement with you about any of this. All
I'm saying is that we should cut scope today. We're not precluding
picking up an IGNORE feature that does support exclusion constraints
in the future. Why should we insist upon having that in the first cut?
It's both significantly harder, and significantly less useful to
users, and so cutting that makes perfect sense AFAICT. As I've said
many times, exclusion constraint support was only ever going to be
useful to the IGNORE variant (I've tested exclusion constraints by
contriving a case to make them do UPSERTs, but this is only for the
benefit of the stress-test).

Thanks
-- 
Peter Geoghegan


-- 
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] SSL information view

2015-02-02 Thread Michael Paquier
Where are we on this patch? No new version has been provided and there
have been comments provided by Heikki here
(5491e547.4040...@vmware.com) and by Alexei here
(87ppbqz00h@commandprompt.com).
-- 
Michael


-- 
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] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-02-02 Thread Geoff Winkless
On 30 January 2015 at 21:58, Peter Geoghegan p...@heroku.com wrote:
 On Fri, Jan 30, 2015 at 6:59 AM, Geoff Winkless pgsqlad...@geoff.dj wrote:
 I suppose there's no reason why we couldn't use a no-op ON CONFLICT
 UPDATE anyway

 Right. IGNORE isn't really all that compelling for that reason. Note
 that this will still lock the unmodified row, though.

Mmmf. So I would have to make sure that my source tuples were unique
before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
tuple would block any other)? That's potentially very slow :(

When you say that you can't add exclusion constraints later, do you
mean from a coding point of view or just because people would get
confused whether exclusion constraints could be IGNOREd or not?

Geoff


-- 
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 : REINDEX xxx VERBOSE

2015-02-02 Thread Tom Lane
Sawada Masahiko sawada.m...@gmail.com writes:
 On Mon, Feb 2, 2015 at 9:21 PM, Michael Paquier
 michael.paqu...@gmail.com wrote:
 Now, I think that it may
 be better to provide the keyword VERBOSE before the type of object
 reindexed as REINDEX [ VERBOSE ] object.

 Actually, my first WIP version of patch added VERBOSE word at before
 type of object.
 I'm feeling difficult about that the position of VERBOSE word in
 REINDEX statement.

The way that FORCE was added to REINDEX was poorly thought out; let's not
double down on that with another option added without any consideration
for future expansion.  I'd be happier if we adopted something similar to
the modern syntax for VACUUM and EXPLAIN, ie, comma-separated options in
parentheses.

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] Release note bloat is getting out of hand

2015-02-02 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 Yeah, the PDF size is definitely someting to consider in this context. And
 the limits.

 But if we can find some good way to archive or preserve them *outside the
 main docs* that should solve this problem, no? We could keep them in SGML
 even, but make sure they are not actually included in the build? Would
 still be useful for developers there...

 Or if we could find a way to do like Josh says - archive them separately
 and publish a separate download. We could even keep it in a separate git
 repo if we have to, with a migrate job to run on a major release?

Yeah, seems like this and Josh's request could both be addressed fine
with a separate document.

I could live with keeping the ancient-branch release note SGML files
around in HEAD --- I'd hoped to reduce the size of tarballs a bit, but the
savings by that measure would only be a few percent (at present anyway).
What's more important is to get them out of the main documentation build.
So how about cutting the main doc build down to last-five-branches,
and adding a non-default make target that produces a separate document
consisting of (only) the complete release note history?

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] Release note bloat is getting out of hand

2015-02-02 Thread Robert Haas
On Mon, Feb 2, 2015 at 10:43 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Magnus Hagander mag...@hagander.net writes:
 Yeah, the PDF size is definitely someting to consider in this context. And
 the limits.

 But if we can find some good way to archive or preserve them *outside the
 main docs* that should solve this problem, no? We could keep them in SGML
 even, but make sure they are not actually included in the build? Would
 still be useful for developers there...

 Or if we could find a way to do like Josh says - archive them separately
 and publish a separate download. We could even keep it in a separate git
 repo if we have to, with a migrate job to run on a major release?

 Yeah, seems like this and Josh's request could both be addressed fine
 with a separate document.

 I could live with keeping the ancient-branch release note SGML files
 around in HEAD --- I'd hoped to reduce the size of tarballs a bit, but the
 savings by that measure would only be a few percent (at present anyway).
 What's more important is to get them out of the main documentation build.
 So how about cutting the main doc build down to last-five-branches,
 and adding a non-default make target that produces a separate document
 consisting of (only) the complete release note history?

The last 5 branches only takes us back to 9.0, which isn't very far.
I would want to have at least the 8.x branches in the SGML build, and
maybe the 7.x branches as well.  I would be happy to drop anything
pre-7.x from the docs build and just let the people who care look at
the SGML.  You seem to be assuming that nobody spends much time
looking at the release notes for older branches, but that is certainly
false in my own case.

-- 
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] POC: Cache data in GetSnapshotData()

2015-02-02 Thread Andres Freund
Hi,

I've, for a while, pondered whether we couldn't find a easier way than
CSN to make snapshots cheaper as GetSnapshotData() very frequently is
one of the top profile entries. Especially on bigger servers, where the
pretty much guaranteed cachemisses are quite visibile.

My idea is based on the observation that even in very write heavy
environments the frequency of relevant PGXACT changes is noticeably
lower than GetSnapshotData() calls.

My idea is to simply cache the results of a GetSnapshotData() result in
shared memory and invalidate it everytime something happens that affects
the results. Then GetSnapshotData() can do a couple of memcpy() calls to
get the snapshot - which will be significantly faster in a large number
of cases. For one often enough there's many transactions without an xid
assigned (and thus xip/subxip are small), for another, even if that's
not the case it's linear copies instead of unpredicable random accesses
through PGXACT/PGPROC.

Now, that idea is pretty handwavy. After talking about it with a couple
of people I've decided to write a quick POC to check whether it's
actually beneficial. That POC isn't anything close to being ready or
complete. I just wanted to evaluate whether the idea has some merit or
not. That said, it survives make installcheck-parallel.

Some very preliminary performance results indicate a growth of between
25% (pgbench -cj 796 -m prepared -f 'SELECT 1'), 15% (pgbench -s 300 -S
-cj 796), 2% (pgbench -cj 96 -s 300) on a 4 x E5-4620 system. Even on my
laptop I can measure benefits in a readonly, highly concurrent,
workload; although unsurprisingly much smaller.

Now, these are all somewhat extreme workloads, but still. It's a nice
improvement for a quick POC.

So far the implemented idea is to just completely wipe the cached
snapshot everytime somebody commits. I've afterwards not been able to
see GetSnapshotData() in the profile at all - so that possibly is
actually sufficient?

This implementation probably has major holes. Like it probably ends up
not really increasing the xmin horizon when a longrunning readonly
transaction without an xid commits...

Comments about the idea?

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services
From 3f800e9363909d2fcf80cb5f9b4f68579a3cb328 Mon Sep 17 00:00:00 2001
From: Andres Freund and...@anarazel.de
Date: Sun, 1 Feb 2015 21:04:42 +0100
Subject: [PATCH] Heavily-WIP: Cache snapshots in GetSnapshotData()

---
 src/backend/commands/cluster.c  |  1 +
 src/backend/storage/ipc/procarray.c | 67 -
 src/backend/storage/lmgr/proc.c | 13 +++
 src/include/storage/proc.h  |  6 
 4 files changed, 78 insertions(+), 9 deletions(-)

diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index dc1b37c..3def86a 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1558,6 +1558,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
 			elog(ERROR, cache lookup failed for relation %u, OIDOldHeap);
 		relform = (Form_pg_class) GETSTRUCT(reltup);
 
+		Assert(TransactionIdIsNormal(frozenXid));
 		relform-relfrozenxid = frozenXid;
 		relform-relminmxid = cutoffMulti;
 
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index a1ebc72..66be489 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -421,6 +421,8 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
   latestXid))
 			ShmemVariableCache-latestCompletedXid = latestXid;
 
+		ProcGlobal-cached_snapshot_valid = false;
+
 		LWLockRelease(ProcArrayLock);
 	}
 	else
@@ -1403,6 +1405,8 @@ GetSnapshotData(Snapshot snapshot)
 	 errmsg(out of memory)));
 	}
 
+	snapshot-takenDuringRecovery = RecoveryInProgress();
+
 	/*
 	 * It is sufficient to get shared lock on ProcArrayLock, even if we are
 	 * going to set MyPgXact-xmin.
@@ -1417,9 +1421,32 @@ GetSnapshotData(Snapshot snapshot)
 	/* initialize xmin calculation with xmax */
 	globalxmin = xmin = xmax;
 
-	snapshot-takenDuringRecovery = RecoveryInProgress();
+	if (!snapshot-takenDuringRecovery  ProcGlobal-cached_snapshot_valid)
+	{
+		Snapshot csnap = ProcGlobal-cached_snapshot;
+		TransactionId *saved_xip;
+		TransactionId *saved_subxip;
+
+		saved_xip = snapshot-xip;
+		saved_subxip = snapshot-subxip;
+
+		memcpy(snapshot, csnap, sizeof(SnapshotData));
+
+		snapshot-xip = saved_xip;
+		snapshot-subxip = saved_subxip;
+
+		memcpy(snapshot-xip, csnap-xip,
+			   sizeof(TransactionId) * csnap-xcnt);
+		memcpy(snapshot-subxip, csnap-subxip,
+			   sizeof(TransactionId) * csnap-subxcnt);
 
-	if (!snapshot-takenDuringRecovery)
+		globalxmin = ProcGlobal-cached_snapshot_globalxmin;
+		xmin = csnap-xmin;
+
+		Assert(TransactionIdIsValid(globalxmin));
+		Assert(TransactionIdIsValid(xmin));
+	}
+	else if (!snapshot-takenDuringRecovery)
 	{
 		

Re: [HACKERS] Release note bloat is getting out of hand

2015-02-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Sun, Feb 1, 2015 at 11:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I propose that we go over to a policy of keeping in HEAD only release
 notes for actively maintained branches, and that each back branch should
 retain notes only for branches that were actively maintained when it split
 off from HEAD.  This would keep about five years worth of history in
 Appendix E, which should be a roughly stable amount of text.

 -1.  I find it very useful to be able to go back through all the
 release notes using grep, and have done so on multiple occasions.  It
 sounds like this policy would make that harder, and I don't see what
 we get out of of it.  It doesn't bother me that the SGML documentation
 of the release notes is big; disk space is cheap.

Disk space isn't the only consideration here; if it were I'd not be
concerned about this.  Processing time is an issue, and so is distribution
size, and so is the length of the manual if someone decides to print it
on dead trees.  I also live in fear of the day that we hit some hard-to-
change internal limit in TeX.

Personally, what I grep when I'm looking for historical info is git log
output, which will certainly not be getting any shorter.

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] Release note bloat is getting out of hand

2015-02-02 Thread Magnus Hagander
On Mon, Feb 2, 2015 at 3:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Robert Haas robertmh...@gmail.com writes:
  On Sun, Feb 1, 2015 at 11:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
  I propose that we go over to a policy of keeping in HEAD only release
  notes for actively maintained branches, and that each back branch should
  retain notes only for branches that were actively maintained when it
 split
  off from HEAD.  This would keep about five years worth of history in
  Appendix E, which should be a roughly stable amount of text.

  -1.  I find it very useful to be able to go back through all the
  release notes using grep, and have done so on multiple occasions.  It
  sounds like this policy would make that harder, and I don't see what
  we get out of of it.  It doesn't bother me that the SGML documentation
  of the release notes is big; disk space is cheap.

 Disk space isn't the only consideration here; if it were I'd not be
 concerned about this.  Processing time is an issue, and so is distribution
 size, and so is the length of the manual if someone decides to print it
 on dead trees.  I also live in fear of the day that we hit some hard-to-
 change internal limit in TeX.


Yeah, the PDF size is definitely someting to consider in this context. And
the limits.

But if we can find some good way to archive or preserve them *outside the
main docs* that should solve this problem, no? We could keep them in SGML
even, but make sure they are not actually included in the build? Would
still be useful for developers there...

Or if we could find a way to do like Josh says - archive them separately
and publish a separate download. We could even keep it in a separate git
repo if we have to, with a migrate job to run on a major release?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: [HACKERS] Small doc patch about pg_service.conf

2015-02-02 Thread Magnus Hagander
On Sun, Feb 1, 2015 at 9:14 PM, Noah Misch n...@leadboat.com wrote:

 On Sun, Feb 01, 2015 at 10:50:24AM -0500, Tom Lane wrote:
  Peter Eisentraut pete...@gmx.net writes:
   At least writing `pg_config --sysconfdir` indicates that it's in an
   installation-specific location, whereas hardcoding /etc will create
   confusion when it's not actually there.  (Incidentally, we use
   /usr/local/pgsql/etc elsewhere in the documentation as a sample
 location.)
 
   I propose the attached patch.
 
  Works for me --- it's at least an improvement over what's there.

 +1


+1 here as well (if a bit late..)


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: [HACKERS] Missing markup in pg_receivexlog.sgml

2015-02-02 Thread Heikki Linnakangas

On 02/03/2015 05:59 AM, Michael Paquier wrote:

Hi all,

Per $subject, I noticed that a markup was missing in the description
of the option --synchronous.



+Issue commandsync/ commands as soon as there is WAL data which has
+not been flushed yet. Also status packets are sent back to the server
+just after WAL data is flushed whatever literal--status-interval/
+is set to.


Hmm. That would imply that pg_receivexlog calls /bin/sync. sync 
command was confusing before, but putting it in command tags makes it 
even more so.


I think that should be rewritten:

Flush the WAL data to disk immediately after it's being received. Also 
send a status packet back to the server immediately after flushing, 
regardless of literal--status-interval/


- Heikki



--
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] Some dead code in metaphone() of fuzzystrmatch.c

2015-02-02 Thread Heikki Linnakangas

On 02/02/2015 03:39 AM, Michael Paquier wrote:

In metaphone() we do the following:
 /* return an empty string if we receive one */
 if (!(str_i_len  0))
 PG_RETURN_TEXT_P(cstring_to_text());

 if (str_i_len  MAX_METAPHONE_STRLEN)
 ereport(ERROR,
 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
  errmsg(argument exceeds the maximum
length of %d bytes,
 MAX_METAPHONE_STRLEN)));

 if (!(str_i_len  0))
 ereport(ERROR,
 (errcode(ERRCODE_ZERO_LENGTH_CHARACTER_STRING),
  errmsg(argument is empty string)));
As we already return an empty string if the first condition is
satisfied, the third condition will never be satisfied. Returning an
empty string when output string is NULL has been introduced in commit
13629df of 2004, so I think that we should simply remove the code
block that will never be crossed, as in the patch attached.


Applied, thanks.

- Heikki



--
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] A minor comment typo in parse_utilcmd.c

2015-02-02 Thread Heikki Linnakangas

On 02/02/2015 06:03 AM, Amit Langote wrote:

Attached does the following:

-* stmt-relation-relpersistence if the select namespace is temporary.
+* stmt-relation-relpersistence if the selected namespace is temporary.


Fixed, thanks.

- Heikki



--
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] EvalPlanQual behaves oddly for FDW queries involving system columns

2015-02-02 Thread Ashutosh Bapat
Hi Fujita-san,
I am having some minor problems running this repro


On Thu, Jan 15, 2015 at 12:45 PM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp
 wrote:

 Here is an example using postgres_fdw.

 [Terminal 1]
 postgres=# create table t (a int, b int);
 CREATE TABLE
 postgres=# insert into t values (1, 1);
 INSERT 0 1
 postgres=# begin;
 BEGIN
 postgres=# update t set b = b * 2;
 UPDATE 1

 [Terminal 2]
 postgres=# create foreign table ft (a int) server loopback options
 (table_name 'lbt');


There isn't any table lbt mentioned here. Do you mean t here?


 CREATE FOREIGN TABLE
 postgres=# insert into ft values (1);
 INSERT 0 1
 postgres=# select tableoid, ctid, * from ft;
  tableoid | ctid  | a
 --+---+---
 25092 | (0,1) | 1
 (1 row)


Shouldn't we see two values here one inserted in 't' and one in ft


 postgres=# select ft.tableoid, ft.ctid, ft.* from t, ft where t.a = ft.a
 for update;

 [Terminal 1]
 postgres=# commit;
 COMMIT

 [Terminal 2]
 postgres=# select ft.tableoid, ft.ctid, ft.* from t, ft where t.a = ft.a
 for update;
  tableoid |  ctid  | a
 --++---
 0 | (4294967295,0) | 1
 (1 row)


Instead of this result, I got following error
ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: SELECT a, ctid FROM public.t FOR UPDATE

Am I missing something while reproducing the problem?


 Note that tableoid and ctid have been changed!

 I think the reason for that is because EvalPlanQualFetchRowMarks doesn't
 properly set tableoid and ctid for foreign tables, IIUC.  I think this
 should be fixed.  Please find attached a patch.  The patch slightly
 relates to [1], so if it is reasonable, I'll update [1] on top of this.

 [1] https://commitfest.postgresql.org/action/patch_view?id=1386

 Best regards,
 Etsuro Fujita


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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


[HACKERS] Table description in the data file (Re: pg_rawdump)

2015-02-02 Thread Stephen R. van den Berg
In relation to the talk and discussions at FOSDEM regarding
helping data recovery, I searched the archives for the
old thread after I performed my last recovery; for reference:

http://www.postgresql.org/message-id/20101019201223.ga15...@cuci.nl

I haven't checked yet if the proposed space there is still
available in the current disk format, but it might serve as
a starter and reminder to get the discussion going once more.
-- 
Stephen.


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


[HACKERS] Small memory leak in execute.c of ECPG driver

2015-02-02 Thread Michael Paquier
Hi all,

In exactly 3 places of the ECPG driver (for numeric, for interval and
for date), we do something as follows:
/* Allocation of mallocedval */
if (!(mallocedval = ecpg_strdup(array [, lineno)))
return false;

for (element = 0; element  var-arrsize; element++)
{
int result;

ptr = stuff_alloc();
if (!ptr)
return false; = Leak here of mallocedval

It happens that if the allocation done within this for loop fails we
leak mallocedval that was previously allocated. Attached is a patch to
fix this issue spotted by Coverity.
Regards
-- 
Michael
From 5911fadddbf78d6d98f1d679e7ff2e78f9728185 Mon Sep 17 00:00:00 2001
From: Michael Paquier michael@otacoo.com
Date: Tue, 3 Feb 2015 15:48:16 +0900
Subject: [PATCH] Fix memory leak in ecpg driver

Issue pointed out by Coverity.
---
 src/interfaces/ecpg/ecpglib/execute.c | 9 +
 1 file changed, 9 insertions(+)

diff --git a/src/interfaces/ecpg/ecpglib/execute.c b/src/interfaces/ecpg/ecpglib/execute.c
index 8a3dd75..abe60a5 100644
--- a/src/interfaces/ecpg/ecpglib/execute.c
+++ b/src/interfaces/ecpg/ecpglib/execute.c
@@ -859,7 +859,10 @@ ecpg_store_input(const int lineno, const bool force_indicator, const struct vari
 
 			nval = PGTYPESnumeric_new();
 			if (!nval)
+			{
+ecpg_free(mallocedval);
 return false;
+			}
 
 			if (var-type == ECPGt_numeric)
 result = PGTYPESnumeric_copy((numeric *) ((var + var-offset * element)-value), nval);
@@ -940,7 +943,10 @@ ecpg_store_input(const int lineno, const bool force_indicator, const struct vari
 		{
 			str = quote_postgres(PGTYPESinterval_to_asc((interval *) ((var + var-offset * element)-value)), quote, lineno);
 			if (!str)
+			{
+ecpg_free(mallocedval);
 return false;
+			}
 			slen = strlen(str);
 
 			if (!(mallocedval = ecpg_realloc(mallocedval, strlen(mallocedval) + slen + 2, lineno)))
@@ -991,7 +997,10 @@ ecpg_store_input(const int lineno, const bool force_indicator, const struct vari
 		{
 			str = quote_postgres(PGTYPESdate_to_asc(*(date *) ((var + var-offset * element)-value)), quote, lineno);
 			if (!str)
+			{
+ecpg_free(mallocedval);
 return false;
+			}
 			slen = strlen(str);
 
 			if (!(mallocedval = ecpg_realloc(mallocedval, strlen(mallocedval) + slen + 2, lineno)))
-- 
2.2.2


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


[HACKERS] Unlikely-to-happen crash in ecpg driver caused by NULL-pointer check not done

2015-02-02 Thread Michael Paquier
Hi all,

In ecpg_add_mem of memory.c, we use ecpg_alloc but there is actually
no NULL-pointer check. If an OOM shows up exactly at this point, this
is likely to cause a crash. Attached patch adds some extra processing
to ecpg_add_mem to check if the allocation fails, and to fail properly
if an OOM appears.
This issue has been pointed out by Coverity, and I guessed the legwork
needed by myself.
Regards,
-- 
Michael
From 250349c31f86028284bb94f896916a0bb449d299 Mon Sep 17 00:00:00 2001
From: Michael Paquier michael@otacoo.com
Date: Tue, 3 Feb 2015 16:21:50 +0900
Subject: [PATCH] Fix unlikely-to-happen crash in ecpg_add_mem

This routine was called ecpg_alloc to allocate to memory but did not
actually check the returned pointer allocated, potentially NULL which
is actually the result of a malloc call.

Issue noted by Coverity, though I guessed the legwork needed here.
---
 src/interfaces/ecpg/ecpglib/descriptor.c | 14 --
 src/interfaces/ecpg/ecpglib/execute.c| 12 ++--
 src/interfaces/ecpg/ecpglib/extern.h |  2 +-
 src/interfaces/ecpg/ecpglib/memory.c |  9 -
 4 files changed, 31 insertions(+), 6 deletions(-)

diff --git a/src/interfaces/ecpg/ecpglib/descriptor.c b/src/interfaces/ecpg/ecpglib/descriptor.c
index b2990ca..bfa3e287 100644
--- a/src/interfaces/ecpg/ecpglib/descriptor.c
+++ b/src/interfaces/ecpg/ecpglib/descriptor.c
@@ -440,7 +440,12 @@ ECPGget_desc(int lineno, const char *desc_name, int index,...)
 		return false;
 	}
 	*(void **) var = mem;
-	ecpg_add_mem(mem, lineno);
+	if (!ecpg_add_mem(mem, lineno))
+	{
+		ecpg_free(mem);
+		va_end(args);
+		return false;
+	}
 	var = mem;
 }
 
@@ -518,7 +523,12 @@ ECPGget_desc(int lineno, const char *desc_name, int index,...)
 return false;
 			}
 			*(void **) data_var.ind_pointer = mem;
-			ecpg_add_mem(mem, lineno);
+			if (!ecpg_add_mem(mem, lineno))
+			{
+ecpg_free(mem);
+va_end(args);
+return false;
+			}
 			data_var.ind_value = mem;
 		}
 
diff --git a/src/interfaces/ecpg/ecpglib/execute.c b/src/interfaces/ecpg/ecpglib/execute.c
index abe60a5..912e75c 100644
--- a/src/interfaces/ecpg/ecpglib/execute.c
+++ b/src/interfaces/ecpg/ecpglib/execute.c
@@ -402,7 +402,11 @@ ecpg_store_result(const PGresult *results, int act_field,
 		if (!var-value)
 			return false;
 		*((char **) var-pointer) = var-value;
-		ecpg_add_mem(var-value, stmt-lineno);
+		if (!ecpg_add_mem(var-value, stmt-lineno))
+		{
+			ecpg_free(var-value);
+			return false;
+		}
 	}
 
 	/* allocate indicator variable if needed */
@@ -414,7 +418,11 @@ ecpg_store_result(const PGresult *results, int act_field,
 		if (!var-ind_value)
 			return false;
 		*((char **) var-ind_pointer) = var-ind_value;
-		ecpg_add_mem(var-ind_value, stmt-lineno);
+		if (!ecpg_add_mem(var-ind_value, stmt-lineno))
+		{
+			ecpg_free(var-ind_value);
+			return false;
+		}
 	}
 
 	/* fill the variable with the tuple(s) */
diff --git a/src/interfaces/ecpg/ecpglib/extern.h b/src/interfaces/ecpg/ecpglib/extern.h
index 3836007..3e93b53 100644
--- a/src/interfaces/ecpg/ecpglib/extern.h
+++ b/src/interfaces/ecpg/ecpglib/extern.h
@@ -137,7 +137,7 @@ extern struct var_list *ivlist;
 /* Here are some methods used by the lib. */
 
 /* Returns a pointer to a string containing a simple type name. */
-void		ecpg_add_mem(void *ptr, int lineno);
+bool		ecpg_add_mem(void *ptr, int lineno);
 
 bool ecpg_get_data(const PGresult *, int, int, int, enum ECPGttype type,
 			  enum ECPGttype, char *, char *, long, long, long,
diff --git a/src/interfaces/ecpg/ecpglib/memory.c b/src/interfaces/ecpg/ecpglib/memory.c
index a09cd26..1ffe3e1 100644
--- a/src/interfaces/ecpg/ecpglib/memory.c
+++ b/src/interfaces/ecpg/ecpglib/memory.c
@@ -104,14 +104,21 @@ static struct auto_mem *auto_allocs = NULL;
 #define set_auto_allocs(am)		do { auto_allocs = (am); } while(0)
 #endif
 
-void
+bool
 ecpg_add_mem(void *ptr, int lineno)
 {
 	struct auto_mem *am = (struct auto_mem *) ecpg_alloc(sizeof(struct auto_mem), lineno);
 
+	if (!am)
+	{
+		ecpg_raise(lineno, ECPG_OUT_OF_MEMORY, ECPG_SQLSTATE_ECPG_OUT_OF_MEMORY, NULL);
+		return false;
+	}
+
 	am-pointer = ptr;
 	am-next = get_auto_allocs();
 	set_auto_allocs(am);
+	return true;
 }
 
 void
-- 
2.2.2


-- 
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] A minor comment typo in parse_utilcmd.c

2015-02-02 Thread Amit Langote
On 03-02-2015 PM 04:49, Heikki Linnakangas wrote:
 
 Fixed, thanks.

Thanks!

Amit




-- 
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] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-02-02 Thread Geoff Winkless
On 2 February 2015 at 14:32, Geoff Winkless pgsqlad...@geoff.dj wrote:
 Mmmf. So I would have to make sure that my source tuples were unique
 before doing the INSERT (otherwise the first ON CONFLICT UPDATE for a
 tuple would block any other)? That's potentially very slow :(

Replying to my own message, because it occurs to me I might be being
stupid (surely not :) )

When you say this will still lock the unmodified row did you mean
just that it's locked to _other_ processes until commit? That would be
much less impactful.

Geoff


-- 
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 behavior of updatable security barrier views on foreign tables

2015-02-02 Thread Robert Haas
On Fri, Jan 30, 2015 at 5:20 AM, Etsuro Fujita
fujita.ets...@lab.ntt.co.jp wrote:
 I noticed that when updating security barrier views on foreign tables,
 we fail to give FOR UPDATE to selection queries issued at ForeignScan.
 Here is an example.

 postgres=# create foreign table base_ftbl (person text, visibility text)
 server loopback options (table_name 'base_tbl');
 CREATE FOREIGN TABLE
 postgres=# create view rw_view as select person from base_ftbl where
 visibility = 'public';
 CREATE VIEW
 postgres=# explain verbose delete from rw_view;
   QUERY PLAN
 ---
  Delete on public.base_ftbl  (cost=100.00..144.40 rows=14 width=6)
Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1
-  Foreign Scan on public.base_ftbl  (cost=100.00..144.40 rows=14
 width=6)
  Output: base_ftbl.ctid
  Remote SQL: SELECT ctid FROM public.base_tbl WHERE ((visibility
 = 'public'::text)) FOR UPDATE
 (5 rows)

 postgres=# alter view rw_view set (security_barrier = true);
 ALTER VIEW
 postgres=# explain verbose delete from rw_view;
 QUERY PLAN
 --
  Delete on public.base_ftbl base_ftbl_1  (cost=100.00..144.54 rows=14
 width=6)
Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1
-  Subquery Scan on base_ftbl  (cost=100.00..144.54 rows=14 width=6)
  Output: base_ftbl.ctid
  -  Foreign Scan on public.base_ftbl base_ftbl_2
 (cost=100.00..144.40 rows=14 width=6)
Output: base_ftbl_2.ctid
Remote SQL: SELECT ctid FROM public.base_tbl WHERE
 ((visibility = 'public'::text))
 (7 rows)

 Correct me if I am wrong.

That looks like a bug to me.

-- 
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] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-02-02 Thread Heikki Linnakangas

On 01/18/2015 04:48 AM, Peter Geoghegan wrote:

I think that the fundamental, unfixable race condition here is the
disconnect between index tuple insertion and checking for would-be
exclusion violations that exclusion constraints naturally have here,
that unique indexes naturally don't have [1] (note that I'm talking
only about approach #2 to value locking here; approach #1 isn't in
V2.0). I suspect that the feature is not technically feasible to make
work correctly with exclusion constraints, end of story. VACUUM
interlocking is probably also involved here, but the unfixable race
condition seems like our fundamental problem.


It's not a fundamental, unfixable race condition. In [1], I gave you 
three ideas straight off the top of my head on how that could be fixed.



Please work with me towards a committable patch.


I'm trying...


[1] http://www.postgresql.org/message-id/54a7c76d.3070...@vmware.com


- Heikki



--
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] INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

2015-02-02 Thread Heikki Linnakangas

On 01/30/2015 01:38 AM, Peter Geoghegan wrote:

I have not addressed the recently described problems with exclusion
constraints. I hope we can do so shortly. Simply removing IGNORE
support until such time as we straighten that all out (9.6?) seems
like the simplest solution. No need to block the progress of UPSERT,
since exclusion constraint support was only ever going to be useful
for the less compelling IGNORE variant. What do other people think? Do
you agree with my view that we should shelve IGNORE support for now,
Heikki?


No, I don't agree. Let's fix it.

- Heikki



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


[HACKERS] Add LINE: hint when schemaname.typename is a non-existent schema

2015-02-02 Thread Ryan Kelly
The attached patch adds a LINE: ... hint when schemaname.typename
results in a schema which does not exist. I came across this when a
missing comma in a SELECT list resulted in an error without a location
in a query a few thousand lines long.

Before:

(postgres@[local]:5432 14:41:25) [postgres] select test.id 'all' as
example from test;
ERROR:  3F000: schema test does not exist
LOCATION:  get_namespace_oid, namespace.c:2826

After:

(postgres@[local]:5433 14:42:32) [postgres] select test.id 'all' as
example from test;
ERROR:  3F000: schema test does not exist
LINE 1: select test.id 'all' as example from test;
   ^
LOCATION:  LookupTypeName, parse_type.c:171

-Ryan Kelly


missing_type_schema_hint.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] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread Roger Pack
On 1/30/15, Jim Nasby jim.na...@bluetreble.com wrote:
 On 1/30/15 11:54 AM, Roger Pack wrote:
 On 1/29/15, Roger Pack rogerdpa...@gmail.com wrote:
 Hello.  I see on this page a mention of basically a 4B row limit for
 tables that have BLOB's

 Oops I meant for BYTEA or TEXT columns, but it's possible the
 reasoning is the same...

 It only applies to large objects, not bytea or text.

 OK I think I figured out possibly why the wiki says this.  I guess
 BYTEA entries  2KB will be autostored via TOAST, which uses an OID in
 its backend.  So BYTEA has a same limitation.  It appears that
 disabling TOAST is not an option [1].
 So I guess if the number of BYTEA entries (in the sum all tables?
 partitioning doesn't help?) with size  2KB is  4 billion then there
 is actually no option there?  If this occurred it might cause all
 sorts of things to break? [2]

 It's a bit more complex than that. First, toast isn't limited to bytea;
 it holds for ALL varlena fields in a table that are allowed to store
 externally. Second, the limit is actually per-table: every table gets
 it's own toast table, and each toast table is limited to 4B unique OIDs.
 Third, the OID counter is actually global, but the code should handle
 conflicts by trying to get another OID. See toast_save_datum(), which
 calls GetNewOidWithIndex().

 Now, the reality is that GetNewOidWithIndex() is going to keep
 incrementing the global OID counter until it finds an OID that isn't in
 the toast table. That means that if you actually get anywhere close to
 using 4B OIDs you're going to become extremely unhappy with the
 performance of toasting new data.

OK so system stability doesn't degrade per se when it wraps, good to know.

So basically when it gets near 4B rows it may have to wrap that
counter multiple times, and for each entry it's searching if it's
already used, etc.

So I guess partitioning tables for now is an acceptable work around,
good to know.

Thanks much for your response, good to know the details before we dive
into postgres with our 8B row table with BYTEA's in it :)


-- 
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_check_dir comments and implementation mismatch

2015-02-02 Thread Robert Haas
On Sat, Jan 31, 2015 at 8:28 AM, Marco Nenciarini
marco.nenciar...@2ndquadrant.it wrote:
 Il 30/01/15 03:54, Michael Paquier ha scritto:
 On Fri, Jan 30, 2015 at 2:49 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 There is at least one other bug in that function now that I look at it:
 in event of a readdir() failure, it neglects to execute closedir().
 Perhaps not too significant since all existing callers will just exit()
 anyway after a failure, but still ...
 I would imagine that code scanners like coverity or similar would not
 be happy about that. ISTM that it is better to closedir()
 appropriately in all the code paths.


 I've attached a new version of the patch fixing the missing closedir on
 readdir error.

If readir() fails and closedir() succeeds, the return will be -1 but
errno will be 0.

-- 
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] pgaudit - an auditing extension for PostgreSQL

2015-02-02 Thread David Steele
On 1/27/15 4:08 AM, Abhijit Menon-Sen wrote:

 Anyway, I think it's reasonably clear now that pgaudit is unlikely to
 make it into 9.5 in any form, so I'll find something else to do.

That's unfortunate.  I've been following this thread for a while with
some interest (and anticipation).

The role-base approach being considered may strike some as a misuse of
the role system, but to my eye it is syntactically very close to how
Oracle does auditing prior to 12c.  Say you wanted to audit selects on
the table hr.employee:

Oracle: AUDIT SELECT ON hr.employee;
pgaudit: GRANT SELECT ON hr.employee TO audit; (assuming audit is the
role defined by pgaudit.roles)

Object-based auditing in Oracle would be very easy to migrate to the
grants needed for pgaudit.  In addition, if an AUDIT command were
introduced later in core, it would be easy to migrate from pgaudit to
AUDIT assuming the syntax was similar to grant, which seems plausible.

Unified auditing in 12c brings together the AUDIT command and DBMS_FGA
under the concept of audit polices.  That type of granularity might be
something to shoot for eventually, but I think having a way to do
auditing similar to what is available in pre-12c covers most use cases
and would certainly be a big step forward for Postgres.

-- 
- David Steele
da...@pgmasters.net




signature.asc
Description: OpenPGP digital signature


Re: [HACKERS] Release note bloat is getting out of hand

2015-02-02 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Robert Haas wrote:
 but there are times when it's easier to find out what release 
 introduced a feature by looking at the release notes, and it's 
 certainly more useful if you want to send a link to someone who 
 is not git-aware illustrating the results of your search.

 Well, maybe I'm the only one who is doing this and it's not worth
 worrying about it just for me.  But I do it, all the same.

I do this *all the time*. Please don't mess with the release notes.
Except to put them all on one page for easy searching. That would 
be awesome.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201502021555
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlTP5EQACgkQvJuQZxSWSsj13QCfTrKBKDlOm0E5K4+2ib7F8Tjl
w5QAoOY3vX9tUb1KUxk3VaW+k71vrW7m
=y+SU
-END PGP SIGNATURE-




-- 
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 behavior of updatable security barrier views on foreign tables

2015-02-02 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote:
 On Fri, Jan 30, 2015 at 5:20 AM, Etsuro Fujita
 fujita.ets...@lab.ntt.co.jp wrote:
  I noticed that when updating security barrier views on foreign tables,
  we fail to give FOR UPDATE to selection queries issued at ForeignScan.
  Here is an example.
[...]
  postgres=# alter view rw_view set (security_barrier = true);
  ALTER VIEW
  postgres=# explain verbose delete from rw_view;
  QUERY PLAN
  --
   Delete on public.base_ftbl base_ftbl_1  (cost=100.00..144.54 rows=14
  width=6)
 Remote SQL: DELETE FROM public.base_tbl WHERE ctid = $1
 -  Subquery Scan on base_ftbl  (cost=100.00..144.54 rows=14 width=6)
   Output: base_ftbl.ctid
   -  Foreign Scan on public.base_ftbl base_ftbl_2
  (cost=100.00..144.40 rows=14 width=6)
 Output: base_ftbl_2.ctid
 Remote SQL: SELECT ctid FROM public.base_tbl WHERE
  ((visibility = 'public'::text))
  (7 rows)
 
  Correct me if I am wrong.
 
 That looks like a bug to me.

Agreed.  I've been looking at this and I suspect it's related to the
discussion around prepsecurity.c and generating the security barrier
subquery that I've been having with Dean.  An initial look, at least,
shows that GetForeignPlan is looking at the subquery instead of the base
relation (as it expects to be).

I'll continue digging into it.

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] File based Incremental backup v9

2015-02-02 Thread Magnus Hagander
On Mon, Feb 2, 2015 at 10:06 PM, Robert Haas robertmh...@gmail.com wrote:

 On Sat, Jan 31, 2015 at 6:47 PM, Marco Nenciarini
 marco.nenciar...@2ndquadrant.it wrote:
  Il 31/01/15 17:22, Erik Rijkers ha scritto:
  On Sat, January 31, 2015 15:14, Marco Nenciarini wrote:
 
  0001-public-parse_filename_for_nontemp_relation.patch
  0002-copydir-LSN-v2.patch
  0003-File-based-incremental-backup-v8.patch
 
  Hi,
 
  It looks like it only compiles with assert enabled.
 
 
  It is due to a typo (assert instead of Assert). You can find the updated
  patch attached to this message.

 I would sure like it if you would avoid changing the subject line
 every time you post a new version of this patch.  It breaks the
 threading for me.


+1 - it does break gmail.



It seems to have also broken it for the CommitFest app, which thinks
 v3 is the last version.  I was not able to attach the new version.


The CF app has detected that it's the same thread, because of the headers
(gmail is the buggy one here - the headers of the email are perfectly
correct).

It does not, however, pick up and show the change of subject there (but you
can see if if you click the link for the latest version into the archives -
the link under latest or latest attachment both go to the v9 patch).



 When I clicked on attach thread without having logged in, it took me
 to a bad URL.  When I clicked on it after having logged in, it


Clearly a bug.



 purported to work, but AFAICS, it didn't actually do anything.


That's because the thread is already there, and you're adding it again. Of
course, it wouldn't hurt if it actually told you that :)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


[HACKERS] Re: Problems with approach #2 to value locking (INSERT ... ON CONFLICT UPDATE/IGNORE patch)

2015-02-02 Thread Heikki Linnakangas

On 01/03/2015 10:42 PM, Peter Geoghegan wrote:

On Sat, Jan 3, 2015 at 2:41 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:

A-ha, I see. And this can happen without INSERT ON CONFLICT, too? In that
case, one of the transactions is bound to error and roll back anyway, but
you get a deadlock error instead of the constraint violation error, which is
not as nice.


Agreed.

I haven't experimentally verified that this can happen with exclusion
constraints without the ON CONFLICT patch being involved, but I would
be very surprised if it didn't. How could it possibly not happen? It's
not all that bad since, as you say, one or the other xact was going to
be aborted anyway. Since the insertions would have to occur at exactly
the same time, even if one backend were to get an exclusion violation
rather than being killed by the deadlock detector, the choice of which
backend to raise an error within would be essentially random anyway.


Yep. I just tested this, and I can confirm that it does happen with 
vanilla exclusion constraints. If two backends insert the same value at 
the same time, you usually get the error conflicting key value violates 
exclusion constraint, but if the timing is right, you get deadlock 
detected instead.


Let's focus on fixing that case first. I wouldn't care otherwise, but it 
allows us to work on the locking, the super-deletion etc. without all 
the rest of the patch. That will provide you a way to split the patch 
into two: 1. Avoid deadlock errors with regular exclusion constraints, 
with all the locking etc. that's needed to solve that, and 2. Upsert.



1. On conflict, mark the inserted tuple as killed, and retry. But before
retrying, acquire a new kind of lock on the table, let's call it
SpeculativeInsertionLock. This fixes the deadlock, by retrying instead of
sleeping, and avoids the livelock because the new lock ensures that only one
backend retries at a time.


We super delete the tuple on retry already. However, we wait for the
other xact with our broken promise tuple still physically inserted
into the heap. We can't super delete the tuple before
XactLockTableWait()/SpeculativeInsertionWait() lock acquisition,
because doing so risks livelock. I think you already agree with me up
to here.

However, if we're not sleep waiting on the other xact (rather, we're
retrying [with a new class of exclusive table-level lock] instead of
sleeping), why should our xact be able to do useful work on retry?
Why won't it just spin/busy wait? More to the point, why wouldn't it
deadlock when it is obliged to wait on a third inserter's tuple?
AFAICT, you've just moved the problem around, because now we're
obliged to get a shared lock on the xid or speculative token
(XactLockTableWait()/SpeculativeInsertionWait()) of a session that
itself wants this new table level lock that only we have.


Sorry, I was very terse in my previous explanation. Let me try again. 
Let's begin with a simpler, poor-performance version of the scheme:


1. Acquire the new SpeculativeInsertionLock on the table
2. Insert tuple to heap and index.
3. Scan the index to see if there is any other conflicting tuple. (If 
there isn't, or the conflicting tuple is already committed, we're done)

4. Super-delete the tuple we already inserted
5. Release SpeculativeInsertionLock
6. XactLockTableWait() on the other guy

Note that we don't try to acquire any other locks while holding 
SpeculativeInsertionLock.


Compare this with the way unique-checks in b-tree work today. The B-tree 
check is free of race conditions because we hold the lock on the b-tree 
page while we check the visibility of the page, and we don't insert the 
index tuple if we have to wait. The SpeculativeInsertionLock 
accomplishes the same. It makes steps 3 and 4 atomic.


Compared to your patch as it stands, this fixes the deadlock because 
when A inserts a tuple and scans the index, any conflicting tuples it 
finds must have completed the insertion before A. The other inserters 
won't later try to wait on the tuple that A inserts.


We could do the above without the new lock, but as you've said, that 
would risk a livelock. Two concurrent inserters might repeatedly insert, 
scan, find each other, super-delete, and retry and not make progress. 
The lock fixes that by ensuring that there is only one inserter is doing 
the above at a time.


(With the above procedure, we could also first scan the index for 
conflicts, and only insert after that, because the 
SpeculativeInsertionLock prevents anyone else from inserting a 
conflicting row. But of course, we're not going to hold an exclusive 
table-level lock under normal circumstances anyway; the above was just a 
prelude to the real scheme below.)


The full procedure is this:

1. Insert tuple to heap and index
2. Scan the index to see if there is any other conflicting tuple. (If 
there isn't, or the conflicting tuple is already committed, we're done)

3. Super-delete the tuple we already inserted

4. Acquire 

Re: [HACKERS] ExplainModifyTarget doesn't work as expected

2015-02-02 Thread Ashutosh Bapat
Hi Fujita-san,
I agree that it's a problem, and it looks more severe when there are
multiple children
postgres=# create table parent (a int check (a  0) no inherit);
CREATE TABLE
postgres=# create table child1 (a int check (a = 0));
CREATE TABLE
postgres=# create table child2 (a int check (a = 0));
CREATE TABLE
postgres=# create table child3 (a int check (a = 0));
CREATE TABLE
postgres=# alter table child1 inherit parent;
ALTER TABLE
postgres=# alter table child2 inherit parent;
ALTER TABLE
postgres=# alter table child3 inherit parent;
ALTER TABLE
postgres=# explain update parent set a = a * 2 where a = 0;
   QUERY PLAN

 Update on child1  (cost=0.00..126.00 rows=2400 width=10)
   -  Seq Scan on child1  (cost=0.00..42.00 rows=800 width=10)
 Filter: (a = 0)
   -  Seq Scan on child2  (cost=0.00..42.00 rows=800 width=10)
 Filter: (a = 0)
   -  Seq Scan on child3  (cost=0.00..42.00 rows=800 width=10)
 Filter: (a = 0)
(7 rows)

It's certainly confusing why would an update on child1 cause scan on child*.

But I also think that showing parent's name with Upate would be misleading
esp. when user expects it to get filtered because of constraint exclusion.

Instead, can we show all the relations that are being modified e.g Update
on child1, child2, child3. That will disambiguate everything.

On Mon, Dec 22, 2014 at 12:20 PM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp
 wrote:

 Hi,

 I think ExplainModifyTarget should show the parent of the inheritance
 tree in multi-target-table cases, as described there, but noticed that
 it doesn't always work like that.  Here is an example.

 postgres=# create table parent (a int check (a  0) no inherit);
 CREATE TABLE
 postgres=# create table child (a int check (a = 0));
 CREATE TABLE
 postgres=# alter table child inherit parent;
 ALTER TABLE
 postgres=# explain update parent set a = a * 2 where a = 0;
   QUERY PLAN
 ---
  Update on child  (cost=0.00..42.00 rows=800 width=10)
-  Seq Scan on child  (cost=0.00..42.00 rows=800 width=10)
  Filter: (a = 0)
 (3 rows)

 IIUC, I think this is because ExplainModifyTarget doesn't take into
 account that the parent *can* be excluded by constraint exclusion.  So,
 I added a field to ModifyTable to record the parent, apart from
 resultRelations.  (More precisely, the parent in its role as a simple
 member of the inheritance tree is recorded so that appending digits to
 refname in select_rtable_names_for_explain works as before.)  Attached
 is a proposed patch for that.

 Thanks,

 Best regards,
 Etsuro Fujita


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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


Re: [HACKERS] Fwd: [GENERAL] 4B row limit for CLOB tables

2015-02-02 Thread José Luis Tallón

On 01/31/2015 12:25 AM, Jim Nasby wrote:

[snip]
It's a bit more complex than that. First, toast isn't limited to 
bytea; it holds for ALL varlena fields in a table that are allowed to 
store externally. Second, the limit is actually per-table: every table 
gets it's own toast table, and each toast table is limited to 4B 
unique OIDs. Third, the OID counter is actually global, but the code 
should handle conflicts by trying to get another OID. See 
toast_save_datum(), which calls GetNewOidWithIndex().


Now, the reality is that GetNewOidWithIndex() is going to keep 
incrementing the global OID counter until it finds an OID that isn't 
in the toast table. That means that if you actually get anywhere close 
to using 4B OIDs you're going to become extremely unhappy with the 
performance of toasting new data.


Indeed ..

I don't think it would be horrifically hard to change the way toast 
OIDs are assigned (I'm thinking we'd basically switch to creating a 
sequence for every toast table), but I don't think anyone's ever tried 
to push toast hard enough to hit this kind of limit.


We did. The Billion Table Project, part2 (a.k.a. when does Postgres' 
OID allocator become a bottleneck) The allocator becomes 
essentially unusable at about 2.1B OIDs, where it performed very well at 
quite empty( 100M objects) levels.


So yes, using one sequence per TOAST table should help.
Combined with the new SequenceAMs / sequence implementation being 
proposed (specifically: one file for all sequences in a certain 
tablespace) this should scale much better.



My 2c.


Regards,

/ J.L.




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