Re: [HACKERS] Setting visibility map in VACUUM's second phase

On Friday, December 07, 2012 12:06 AM Robert Haas wrote: > On Thu, Dec 6, 2012 at 1:01 PM, Tom Lane wrote: > > I think taking a second whack at setting the visibility bit is a fine > > idea, but let's drop all the rest of this premature optimization. > > +1. > > If there's any optimization neede

Re: [HACKERS] why can't plpgsql return a row-expression?

Thanks. Regards, --Asif On Fri, Dec 7, 2012 at 9:11 AM, Tom Lane wrote: > Asif Rehman writes: > > I have attached the stripped-down version. I will leave the type > coercions > > support for a separate patch. > > Applied with assorted corrections. > > regards, tom lane

Re: [HACKERS] why can't plpgsql return a row-expression?

Asif Rehman writes: > I have attached the stripped-down version. I will leave the type coercions > support for a separate patch. Applied with assorted corrections. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes t

Re: [HACKERS] Setting visibility map in VACUUM's second phase

On Fri, Dec 7, 2012 at 12:05 AM, Robert Haas wrote: > On Thu, Dec 6, 2012 at 1:01 PM, Tom Lane wrote: >> I think taking a second whack at setting the visibility bit is a fine >> idea, but let's drop all the rest of this premature optimization. > > +1. > > If there's any optimization needed here,

Re: [HACKERS] pg_upgrade problem with invalid indexes

Andrew Dunstan wrote: > > On 12/06/2012 09:23 PM, Bruce Momjian wrote: > >As soon as pg_dump stopped dumping the CREATE INDEX, pg_upgrade would > >stop creating creating it in the new cluster, and not transfer the index > >files. > > So we'll lose the index definition and leave some files behind

Re: [HACKERS] [WIP] pg_ping utility

On Thu, Dec 6, 2012 at 8:54 PM, Michael Paquier wrote: > > > On Thu, Dec 6, 2012 at 12:29 AM, Phil Sorber wrote: >> >> On Wed, Dec 5, 2012 at 8:53 AM, Alvaro Herrera >> wrote: >> > No, I think it is the reference docs on the returned value that must be >> > fixed. That is, instead of saying tha

Re: [HACKERS] Setting visibility map in VACUUM's second phase

On Thu, Dec 6, 2012 at 11:31 PM, Tom Lane wrote: > > I think taking a second whack at setting the visibility bit is a fine > idea, but let's drop all the rest of this premature optimization. > Fair enough. I thought about doing it that way but was worried that an additional page scan will raise

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

> From: Simon Riggs [mailto:si...@2ndquadrant.com] > Sent: 07 December 2012 05:44 > To: David Rowley > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Functional dependency in GROUP BY through JOINs > > On 5 December 2012 23:37, David Rowley wrote: > > > Though this plan might not be q

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

> From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: 07 December 2012 06:22 > To: Simon Riggs > Cc: David Rowley; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Functional dependency in GROUP BY through JOINs > > Simon Riggs writes: > > On 5 December 2012 23:37, David Rowley > wrote: > >>

Re: [HACKERS] pg_upgrade problem with invalid indexes

* Bruce Momjian (br...@momjian.us) wrote: > I think we would have have pg_dump --binary-upgrade issue an UPDATE to > the system catalogs to mark it as invalid. That'd work for me too- I'm not particular on if it's done as a direct catalog update or some undocumented feature of CREATE INDEX.

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

* Jeff Davis (pg...@j-davis.com) wrote: > That is documented in the committed patch -- it's a trade, basically > saying that you lose isolation but avoid extra writes. It seems > reasonable that the user gets this behavior if specifically requested. Strictly speaking, it could actually be two diff

Re: [HACKERS] pg_upgrade problem with invalid indexes

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Or preserve it as-is. > > To do that, we would have to add an option to CREATE INDEX to create it > in an invalid state. Which is stupid... Only in a binary-upgrade mode. Thanks, Stephen signature.asc

Re: [HACKERS] pg_upgrade problem with invalid indexes

* Bruce Momjian (br...@momjian.us) wrote: > On Thu, Dec 6, 2012 at 09:45:11PM -0500, Stephen Frost wrote: > > Or preserve it as-is. I don't really like the 'make them fix it' > > option, as a user could run into that in the middle of a planned upgrade > > that had been tested and never had that c

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

> From: Kevin Grittner [mailto:kgri...@mail.com] > > I think I'm right in thinking that if a unique index exists to match > > the group by clause, and the join condition is equality (probably > > using the same operator class as the unique btree index?), then the > > grouping could be pushed up to

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 10:06:13PM -0500, Tom Lane wrote: > Stephen Frost writes: > > * Andrew Dunstan (and...@dunslane.net) wrote: > >> Making the user fix it seems much more sensible to me. Otherwise I > >> suspect we'll find users who get strangely surprised when they can > >> no longer find an

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On Thu, 2012-12-06 at 20:49 -0500, Stephen Frost wrote: > I'm less concerned about the hint bits and more concerned about the > implications of the FrozenXID being used, which would make the rows > visible to other transactions even if they began before the rows were > loaded. That is documented i

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 09:45:11PM -0500, Stephen Frost wrote: > * Andrew Dunstan (and...@dunslane.net) wrote: > > So we'll lose the index definition and leave some files behind? This > > sounds a bit messy to say the least. > > Agreed. > > > Making the user fix it seems much more sensible to me.

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 09:41:00PM -0500, Andrew Dunstan wrote: > > On 12/06/2012 09:23 PM, Bruce Momjian wrote: > >On Thu, Dec 6, 2012 at 09:10:21PM -0500, Tom Lane wrote: > >>Bruce Momjian writes: > >>>On Thu, Dec 6, 2012 at 07:53:57PM -0500, Tom Lane wrote: > Because CREATE INDEX CONCURR

Re: [HACKERS] pg_upgrade problem with invalid indexes

Stephen Frost writes: > * Andrew Dunstan (and...@dunslane.net) wrote: >> Making the user fix it seems much more sensible to me. Otherwise I >> suspect we'll find users who get strangely surprised when they can >> no longer find any trace of an expected index in their upgraded >> database. > Or pr

Re: [HACKERS] pg_upgrade problem with invalid indexes

* Andrew Dunstan (and...@dunslane.net) wrote: > So we'll lose the index definition and leave some files behind? This > sounds a bit messy to say the least. Agreed. > Making the user fix it seems much more sensible to me. Otherwise I > suspect we'll find users who get strangely surprised when they

Re: [HACKERS] pg_upgrade problem with invalid indexes

On 12/06/2012 09:23 PM, Bruce Momjian wrote: On Thu, Dec 6, 2012 at 09:10:21PM -0500, Tom Lane wrote: Bruce Momjian writes: On Thu, Dec 6, 2012 at 07:53:57PM -0500, Tom Lane wrote: Because CREATE INDEX CONCURRENTLY can't drop the index if it's already failed. It's not because we want to d

Re: [HACKERS] How to check whether the row was modified by this transaction before?

On 12/07/2012 02:53 AM, Tom Lane wrote: Vlad Arkhipov writes: In a BEFORE UPDATE trigger I need to know whether the row was previously modified by this transaction. Is it safe to use xmin and txid_current() for this purpose (xmin is 32-bit txid type but txid_current() returns 64-bit bigint).

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 09:10:21PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Thu, Dec 6, 2012 at 07:53:57PM -0500, Tom Lane wrote: > >> Because CREATE INDEX CONCURRENTLY can't drop the index if it's already > >> failed. It's not because we want to do that, it's an implementation > >>

Re: [HACKERS] pg_upgrade problem with invalid indexes

Bruce Momjian writes: > On Thu, Dec 6, 2012 at 07:53:57PM -0500, Tom Lane wrote: >> Because CREATE INDEX CONCURRENTLY can't drop the index if it's already >> failed. It's not because we want to do that, it's an implementation >> restriction of the horrid kluge that is CREATE/DROP INDEX CONCURREN

Re: [HACKERS] [WIP] pg_ping utility

On Thu, Dec 6, 2012 at 12:29 AM, Phil Sorber wrote: > On Wed, Dec 5, 2012 at 8:53 AM, Alvaro Herrera > wrote: > > No, I think it is the reference docs on the returned value that must be > > fixed. That is, instead of saying that the return value correspond to > > the enum values, you should be

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

Jeff, * Jeff Davis (pg...@j-davis.com) wrote: > On Thu, 2012-12-06 at 20:12 -0500, Stephen Frost wrote: > > The command is 'FREEZE', which sounded to me like the transaction ID > > would be set to FrozenXID, meaning that we wouldn't be able to tell if > > the inserting transaction was before or af

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, 2012-12-06 at 20:05 -0500, Andrew Dunstan wrote: > I think I prefer the first suggestion. If they are trying to upgrade > when there's an invalid index presumably they aren't aware of the > invalidity (or they would have done something about it). It would be > better to fail and make the

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On Thu, 2012-12-06 at 20:12 -0500, Stephen Frost wrote: > The command is 'FREEZE', which sounded to me like the transaction ID > would be set to FrozenXID, meaning that we wouldn't be able to tell if > the inserting transaction was before or after ours... Freezing does lose information, but I thou

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 07:53:57PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > Yes, I thought of not dumping it. The problem is that we don't delete > > the index when it fails, so I assumed we didn't want to lose the index > > creation information. I need to understand why we did that. >

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

* Jeff Davis (pg...@j-davis.com) wrote: > However, the rows will *not* be visible, because the serializable > snapshot doesn't contain the inserting transaction. That's what we've got now and what would be expected, however... > Think about the current behavior: right after the commit, another se

Re: [HACKERS] pg_upgrade problem with invalid indexes

On 12/06/2012 07:58 PM, Jeff Davis wrote: On Thu, 2012-12-06 at 16:31 -0800, Josh Berkus wrote: There are a few possible fixes. The first would be to have pg_upgrade throw an error on any invalid index in the old cluster. Another option would be to preserve the invalid state in pg_dump --bina

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, 2012-12-06 at 16:31 -0800, Josh Berkus wrote: > > There are a few possible fixes. The first would be to have pg_upgrade > > throw an error on any invalid index in the old cluster. Another option > > would be to preserve the invalid state in pg_dump --binary-upgrade. > > Or to not dump in

Re: [HACKERS] pg_upgrade problem with invalid indexes

Bruce Momjian writes: > Yes, I thought of not dumping it. The problem is that we don't delete > the index when it fails, so I assumed we didn't want to lose the index > creation information. I need to understand why we did that. Because CREATE INDEX CONCURRENTLY can't drop the index if it's alr

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On Thu, 2012-12-06 at 14:18 -0500, Stephen Frost wrote: > begin; > You need to do a SELECT here to actually get a snapshot. > session b > - > begi

Re: [HACKERS] pg_upgrade problem with invalid indexes

On Thu, Dec 6, 2012 at 09:35:19PM -0300, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > There are a few possible fixes. The first would be to have pg_upgrade > > throw an error on any invalid index in the old cluster. Another option > > would be to preserve the invalid state in pg_dump --bi

Re: [HACKERS] pg_upgrade problem with invalid indexes

Bruce Momjian wrote: > There are a few possible fixes. The first would be to have pg_upgrade > throw an error on any invalid index in the old cluster. Another option > would be to preserve the invalid state in pg_dump --binary-upgrade. Yet another option would be for pg_dump --binary-upgrade to

Re: [HACKERS] pg_upgrade problem with invalid indexes

> There are a few possible fixes. The first would be to have pg_upgrade > throw an error on any invalid index in the old cluster. Another option > would be to preserve the invalid state in pg_dump --binary-upgrade. Or to not dump invalid indexes at all in --binary-upgrade mode. > I also need h

[HACKERS] pg_upgrade problem with invalid indexes

I got a report today on the IRC channel about a pg_upgrade problem with upgrading clusters with indexes that exist but are invalid. For example, if you use CREATE INDEX CONCURRENTLY, then shut down the server while it is running, the index will be left as INVALID; from our CREATE INDEX docs:

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

On Thu, Dec 6, 2012 at 9:33 AM, Tom Lane wrote: > "MauMau" writes: >> I'm using PostgreSQL 9.1.6 on Linux. I encountered a serious problem that >> media recovery failed showing the following message: >> FATAL: archive file "000100800028" has wrong size: 7340032 >> instead of 1677721

Re: [HACKERS] -DCLOBBER_CACHE_ALWAYS shows COPY FREEZE regression problem

On 7 December 2012 00:06, Andres Freund wrote: > Apparently the magic to preserve those values across cache resets isn't > strong enough for this. Seems bad, because that seems to mean a sinval > overflow leads to this and related optimizations being lost? Which seems to back up the case for it

Re: [HACKERS] -DCLOBBER_CACHE_ALWAYS shows COPY FREEZE regression problem

Andrew Dunstan writes: > On a new buildfarm member friarbird > , > > configured with _DCLOBBER_CACHE_ALWAYS: >BEGIN; >TRUNCATE vistest; >COPY vistest FROM stdin CSV FREEZE; > +

Re: [HACKERS] -DCLOBBER_CACHE_ALWAYS shows COPY FREEZE regression problem

On 2012-12-06 23:59:06 +, Simon Riggs wrote: > On 6 December 2012 23:28, Andrew Dunstan wrote: > > On a new buildfarm member friarbird > > , > > configured with _DCLOBBER_CACHE_ALWAYS: > > > > BEGIN

Re: [HACKERS] -DCLOBBER_CACHE_ALWAYS shows COPY FREEZE regression problem

On 6 December 2012 23:28, Andrew Dunstan wrote: > On a new buildfarm member friarbird > , > configured with _DCLOBBER_CACHE_ALWAYS: > > BEGIN; > TRUNCATE vistest; > COPY vistest FROM stdin C

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

Hi, On 6.12.2012 23:45, MauMau wrote: > From: "Tom Lane" >> Well, that's unfortunate, but it's not clear that automatic recovery is >> possible. The only way out of it would be if an undamaged copy of the >> segment was in pg_xlog/ ... but if I recall the logic correctly, we'd >> not even be try

[HACKERS] -DCLOBBER_CACHE_ALWAYS shows COPY FREEZE regression problem

On a new buildfarm member friarbird , configured with _DCLOBBER_CACHE_ALWAYS: BEGIN; TRUNCATE vistest; COPY vistest FROM stdin CSV FREEZE; + NOTICE: FREEZE option specified but pre-co

Re: [HACKERS] Re: How to check whether the row was modified by this transaction before?

On 2012-12-06 16:26:50 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2012-12-06 13:59:32 -0500, Tom Lane wrote: > >> Andres Freund writes: > >>> On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: > >>> Vlad Arkhipov writes: > In a BEFORE UPDATE trigger I need to know whether the ro

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

From: "Tom Lane" Well, that's unfortunate, but it's not clear that automatic recovery is possible. The only way out of it would be if an undamaged copy of the segment was in pg_xlog/ ... but if I recall the logic correctly, we'd not even be trying to fetch from the archive if we had a local cop

Re: [HACKERS] PATCH: optimized DROP of multiple tables within a transaction

On 6.12.2012 05:47, Shigeru Hanada wrote: > On Mon, Nov 12, 2012 at 4:36 AM, Tomas Vondra wrote: >> Hi, >> >> I've prepared a slightly updated patch, based on the previous review. >> See it attached. > > All changes in v3 patch seem good, however I found some places which requires > cosmetic chan

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

From: "Kevin Grittner" If you are attempting a PITR-style recovery and you want to include WAL entries from the partially-copied file, pad a copy of it with NUL bytes to the expected length. I'm afraid This is unacceptably difficult, or almost impossible, for many PG users. How do you do the

Re: [HACKERS] why can't plpgsql return a row-expression?

Asif Rehman writes: > I have attached the stripped-down version. I will leave the type coercions > support for a separate patch. OK, I'll take a look at this one. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] [COMMITTERS] pgsql: Background worker processes

Alvaro Herrera wrote: > Background worker processes I had tested this on EXEC_BACKEND some time ago, and it worked fine, but I had neglected since then, and now I find it fails with a pretty strange message on startup. Also, Andres and I have been talking about other possible problems in that sce

Re: [HACKERS] Re: How to check whether the row was modified by this transaction before?

Andres Freund writes: > On 2012-12-06 13:59:32 -0500, Tom Lane wrote: >> Andres Freund writes: >>> On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: >>> Vlad Arkhipov writes: In a BEFORE UPDATE trigger I need to know whether the row was previously modified by this transaction. Is it

Re: [HACKERS] why can't plpgsql return a row-expression?

Hi, I have attached the stripped-down version. I will leave the type coercions support for a separate patch. Regards, --Asif On Fri, Dec 7, 2012 at 1:14 AM, Tom Lane wrote: > Robert Haas writes: > > Are you going to commit a stripped-down version of the patch? > > I set it back to "waiting

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

Andres Freund writes: > On 2012-12-06 18:42:22 +, Simon Riggs wrote: >> "in-between state" means what? And what danger do you see? > > For example during table rewrites we have a temporary pg_class entry > thats a full copy of the table, with a separate oid, relfilenode and > everything. That

Re: [HACKERS] in-catalog Extension Scripts and Control parameters (templates?)

Tom Lane writes: > Only for these new-style thingies. I am not suggesting breaking the > existing file-based implementation, only offering a parallel > catalog-based implementation too. We'd have to think about what to do > for name collisions --- probably having the catalog entry take > precede

Re: [HACKERS] why can't plpgsql return a row-expression?

Robert Haas writes: > Are you going to commit a stripped-down version of the patch? I set it back to "waiting on author" --- don't know if he wants to produce a stripped-down version with no type coercions, or try to use cast-based coercions. regards, tom lane -- Sent

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

> -Original Message- > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > ow...@postgresql.org] On Behalf Of Andres Freund > Sent: 06 December 2012 20:44 > To: Petr Jelinek > Cc: 'Simon Riggs'; 'Robert Haas'; 'Dimitri Fontaine'; 'Josh Berkus'; pgsql- > hack...@postgresql.org

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 2012-12-06 20:27:33 +0100, Petr Jelinek wrote: > > -Original Message- > > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > > ow...@postgresql.org] On Behalf Of Andres Freund > > Sent: 06 December 2012 20:04 > > To: Simon Riggs > > Cc: Robert Haas; Dimitri Fontaine; Josh

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

> -Original Message- > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > ow...@postgresql.org] On Behalf Of Andres Freund > Sent: 06 December 2012 20:04 > To: Simon Riggs > Cc: Robert Haas; Dimitri Fontaine; Josh Berkus; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS]

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

Jeff, * Jeff Davis (pg...@j-davis.com) wrote: > That isn't a problem, because the other session won't see the tuple in > pg_class until the creating transaction commits, at which point the rows > have committed, too (because this would only kick in when the rows are > loaded in the same transactio

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On Thu, 2012-12-06 at 18:16 +, Simon Riggs wrote: > > I tend to agree with Andres on this one. This feels a bit like > > accepting a command but then not actually following-through on it > > if it turns out we can't actually do it. If it's truely an optimization > > (and I suspect my other em

[HACKERS] Re: How to check whether the row was modified by this transaction before?

On 2012-12-06 13:59:32 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: > >> Vlad Arkhipov writes: > >>> In a BEFORE UPDATE trigger I need to know whether the row was previously > >>> modified by this transaction. Is it safe to use xmin and tx

Re: [HACKERS] why can't plpgsql return a row-expression?

On Thu, Dec 6, 2012 at 1:36 PM, Tom Lane wrote: > I'm against putting I/O coercion semantics into tupconvert, period. Ever. > If plpgsql wants that behavior rather than something more consistent > with the rest of the system, it needs to implement it for itself. I'm sure that can be done. I don

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 2012-12-06 18:42:22 +, Simon Riggs wrote: > On 6 December 2012 18:31, Andres Freund wrote: > > On 2012-12-06 18:21:09 +, Simon Riggs wrote: > >> On 6 December 2012 00:46, Robert Haas wrote: > >> > On Wed, Dec 5, 2012 at 6:45 PM, Simon Riggs > >> > wrote: > >> >> Yes, but it is also t

Re: [HACKERS] How to check whether the row was modified by this transaction before?

Andres Freund writes: > On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: >> Vlad Arkhipov writes: >>> In a BEFORE UPDATE trigger I need to know whether the row was previously >>> modified by this transaction. Is it safe to use xmin and txid_current() >>> for this purpose (xmin is 32-bit txid t

Re: [HACKERS] Setting visibility map in VACUUM's second phase

Robert Haas writes: > One other thought: I'm wondering if we shouldn't try to push the work > of setting the all-visible bit into heap_page_prune(). Hm, maybe ... > But it seems to me that a page can't be all-visible unless there are > no dead line pointers and no HOT chains of length != 1, and

Re: [HACKERS] Fix for pg_upgrade status display

On Thu, Dec 6, 2012 at 02:53:44PM -0300, Alvaro Herrera wrote: > Robert Haas escribió: > > On Wed, Dec 5, 2012 at 10:04 PM, Bruce Momjian wrote: > > > Pg_upgrade displays file names during copy and database names during > > > dump/restore. Andrew Dunstan identified three bugs: > > > > > > * lon

Re: [HACKERS] Fix for pg_upgrade status display

On Thu, Dec 6, 2012 at 12:43:53PM -0500, Robert Haas wrote: > On Wed, Dec 5, 2012 at 10:04 PM, Bruce Momjian wrote: > > Pg_upgrade displays file names during copy and database names during > > dump/restore. Andrew Dunstan identified three bugs: > > > > * long file names were being truncated to

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 6 December 2012 18:31, Andres Freund wrote: > On 2012-12-06 18:21:09 +, Simon Riggs wrote: >> On 6 December 2012 00:46, Robert Haas wrote: >> > On Wed, Dec 5, 2012 at 6:45 PM, Simon Riggs wrote: >> >> Yes, but it is also the trigger writers problem. >> > >> > Maybe to some degree. I don'

[HACKERS] Re: How to check whether the row was modified by this transaction before?

On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: > Tom Lane wrote: > > Vlad Arkhipov writes: > > > In a BEFORE UPDATE trigger I need to know whether the row was previously > > > modified by this transaction. Is it safe to use xmin and txid_current() > > > for this purpose (xmin is 32-bit txid t

Re: [HACKERS] why can't plpgsql return a row-expression?

Robert Haas writes: > On Thu, Dec 6, 2012 at 12:31 AM, Pavel Stehule > wrote: >> but we can limit a implicit coercion in tupconvert via new parameter - >> because we would to forward plpgsql behave just from this direction. >> Then when this parameter - maybe "allowIOCoercion" will be false, the

Re: [HACKERS] Setting visibility map in VACUUM's second phase

On Thu, Dec 6, 2012 at 1:01 PM, Tom Lane wrote: > I think taking a second whack at setting the visibility bit is a fine > idea, but let's drop all the rest of this premature optimization. +1. If there's any optimization needed here, we should try to do it by remembering relevant details from the

Re: [HACKERS] autovacuum truncate exclusive lock round two

Kevin and Robert are well aware of most of the below. I just want to put this out here so other people, who haven't followed the discussion too closely, may chime in. Some details on the problem: First of all, there is a minimum number of 1000 pages that the vacuum scan must detect as possibl

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 2012-12-06 18:21:09 +, Simon Riggs wrote: > On 6 December 2012 00:46, Robert Haas wrote: > > On Wed, Dec 5, 2012 at 6:45 PM, Simon Riggs wrote: > >> Yes, but it is also the trigger writers problem. > > > > Maybe to some degree. I don't think that a server crash or something > > like a blo

Re: [HACKERS] How to check whether the row was modified by this transaction before?

Alvaro Herrera writes: > I think we should be setting the initial epoch to something other than > zero. That way, some quick testing would have revealed this problem > immediately. Yeah, having initdb start the epoch at 1 doesn't seem unreasonable. regards, tom lane --

Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 6 December 2012 00:46, Robert Haas wrote: > On Wed, Dec 5, 2012 at 6:45 PM, Simon Riggs wrote: >> Yes, but it is also the trigger writers problem. > > Maybe to some degree. I don't think that a server crash or something > like a block-read error is ever tolerable though, no matter how silly >

Re: [HACKERS] in-catalog Extension Scripts and Control parameters (templates?)

Dimitri Fontaine writes: > Tom Lane writes: >> I think a separate kind of "extension template" object would make a lot >> more sense. > I'm on board now. We still have some questions to answer, and here's a > worked out design proposal for implementing my understanding of your > "extension's tem

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On 6 December 2012 17:02, Stephen Frost wrote: > * Simon Riggs (si...@2ndquadrant.com) wrote: >> It's not a bug. Requesting a useful, but not critical optimisation is >> just a hint. The preconditions are not easy to understand, so I see no >> reason to punish people that misunderstand, or cause p

Re: [HACKERS] How to check whether the row was modified by this transaction before?

Tom Lane wrote: > Vlad Arkhipov writes: > > In a BEFORE UPDATE trigger I need to know whether the row was previously > > modified by this transaction. Is it safe to use xmin and txid_current() > > for this purpose (xmin is 32-bit txid type but txid_current() returns > > 64-bit bigint). > > >

Re: [HACKERS] Setting visibility map in VACUUM's second phase

Pavan Deolasee writes: > So the idea that the patch implements is this. When we scan pages in > the first phase of vacuum, if we find a page that has all-visible > tuples but also has one or more dead tuples that we know the second > phase of vacuum will remove, we mark such page with a special fl

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

Tom Lane wrote: > In the case being presented here, it's not apparent to me that > there's any advantage to be had at all. The OP reported a different plan which was twice as fast, although showing EXPLAIN ANALYZE results for both would be nice confirmation of that. > You still need to aggregate

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

On 6 December 2012 17:21, Tom Lane wrote: > Simon Riggs writes: >> On 5 December 2012 23:37, David Rowley wrote: >>> Though this plan might not be quite as optimal as it could be as it performs >>> the grouping after the join. > >> PostgreSQL always calculates aggregation as the last step. > >>

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

On Thu, 2012-12-06 at 11:55 -0500, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: > When I last recall this discussion (likely in some bar in Europe), the > problem was also that an independent session would be able to: > > a) see that the table exists (due to SnapshotNow bein

Re: [HACKERS] Fix for pg_upgrade status display

Robert Haas escribió: > On Wed, Dec 5, 2012 at 10:04 PM, Bruce Momjian wrote: > > Pg_upgrade displays file names during copy and database names during > > dump/restore. Andrew Dunstan identified three bugs: > > > > * long file names were being truncated to 60 _leading_ characters, which > > ofte

Re: [HACKERS] How to check whether the row was modified by this transaction before?

Vlad Arkhipov writes: > In a BEFORE UPDATE trigger I need to know whether the row was previously > modified by this transaction. Is it safe to use xmin and txid_current() > for this purpose (xmin is 32-bit txid type but txid_current() returns > 64-bit bigint). >IF OLD.xmin = txid_current()

Re: [HACKERS] How to check whether the row was modified by this transaction before?

On Thu, Dec 6, 2012 at 3:58 AM, Vlad Arkhipov wrote: > In a BEFORE UPDATE trigger I need to know whether the row was previously > modified by this transaction. Is it safe to use xmin and txid_current() for > this purpose (xmin is 32-bit txid type but txid_current() returns 64-bit > bigint). > > CR

Re: [HACKERS] why can't plpgsql return a row-expression?

On Thu, Dec 6, 2012 at 12:31 AM, Pavel Stehule wrote: > 2012/12/5 Tom Lane : >> Asif Rehman writes: >>> Here is the updated patch. I overlooked the loop, checking to free the >>> conversions map. Here are the results now. >> >> I looked at this patch briefly. It seems to me to be completely >> s

Re: [HACKERS] autovacuum truncate exclusive lock round two

On Wed, Dec 5, 2012 at 10:16 PM, Jan Wieck wrote: > On 12/5/2012 2:00 PM, Robert Haas wrote: >> >> Many it'd be sensible to relate the retry time to the time spend >> vacuuming the table. Say, if the amount of time spent retrying >> exceeds 10% of the time spend vacuuming the table, with a minimu

Re: [HACKERS] Fix for pg_upgrade status display

On Wed, Dec 5, 2012 at 10:04 PM, Bruce Momjian wrote: > Pg_upgrade displays file names during copy and database names during > dump/restore. Andrew Dunstan identified three bugs: > > * long file names were being truncated to 60 _leading_ characters, which > often do not change for long file name

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

"MauMau" writes: > I'm using PostgreSQL 9.1.6 on Linux. I encountered a serious problem that > media recovery failed showing the following message: > FATAL: archive file "000100800028" has wrong size: 7340032 > instead of 16777216 Well, that's unfortunate, but it's not clear that

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

Simon Riggs writes: > On 5 December 2012 23:37, David Rowley wrote: >> Though this plan might not be quite as optimal as it could be as it performs >> the grouping after the join. > PostgreSQL always calculates aggregation as the last step. > It's a well known optimisation to push-down GROUP BY

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

* Simon Riggs (si...@2ndquadrant.com) wrote: > It's not a bug. Requesting a useful, but not critical optimisation is > just a hint. The preconditions are not easy to understand, so I see no > reason to punish people that misunderstand, or cause programs to fail > in ways that need detailed understa

Re: [HACKERS] Commits 8de72b and 5457a1 (COPY FREEZE)

* Robert Haas (robertmh...@gmail.com) wrote: > I haven't looked at the committed patch - which seemed a bit Disclaimer- neither have I, but.. When I last recall this discussion (likely in some bar in Europe), the problem was also that an independent session would be able to: a) see that the tabl

Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash

MauMau wrote: > [Problem] > I'm using PostgreSQL 9.1.6 on Linux. I encountered a serious > problem that media recovery failed showing the following message: > > FATAL: archive file "000100800028" has wrong size: > 7340032 instead of 16777216 > > I'm using normal cp command to archive

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

On 5 December 2012 23:37, David Rowley wrote: > Though this plan might not be quite as optimal as it could be as it performs > the grouping after the join. PostgreSQL always calculates aggregation as the last step. It's a well known optimisation to push-down GROUP BY clauses to the lowest level

Re: [HACKERS] [PATCH 03/14] Add simple xlogdump tool

Hi, I tried to address most (all?) your comments in the version from http://archives.postgresql.org/message-id/20121204175212.GB12055%40awork2.anarazel.de . On 2012-11-15 11:31:55 -0500, Peter Eisentraut wrote: > > +xlogdump: $(OBJS) $(shell find ../../backend ../../timezone -name > > objfiles.t

Re: [HACKERS] Enabling Checksums

Robert Haas wrote: > Jeff Davis wrote: >> Or, I could write up a test framework in ruby or python, using >> the appropriate pg driver, and some not-so-portable shell >> commands to start and stop the server. Then, I can publish that >> on this list, and that would at least make it easier to test >

Re: [HACKERS] strange isolation test buildfarm failure on guaibasaurus

Christian Ullrich writes: > * Tom Lane wrote: >> We really need to scare up another buildfarm member to run with >> -DCLOBBER_CACHE_ALWAYS, now that jaguar has stopped doing so. > I would be happy to do that on jaguarundi, in exchange for dialing down > the build frequency from hourly to somethi

Re: [HACKERS] Functional dependency in GROUP BY through JOINs

David Rowley wrote: > If we wanted to see the sales per product we could write > something like this: > SELECT p.product_code,SUM(s.quantity) > FROM products p > INNER JOIN bigsalestable s ON p.productid = s.productid > GROUP BY p.product_code; > Though this plan might not be quite as optimal as

Re: [HACKERS] WIP patch for hint bit i/o mitigation

On Thu, Dec 6, 2012 at 3:59 AM, Amit Kapila wrote: > On Thursday, November 22, 2012 3:00 AM Greg Smith wrote: >> On 11/16/12 9:03 AM, Merlin Moncure wrote: >> > Atri ran some quick n dirty tests to see if there >> > were any regressions. He benched a large scan followed by vacuum. So >> > far, r

  1   2   >