Re: [HACKERS] crash-safe visibility map, take three

On 01.12.2010 03:35, Bruce Momjian wrote: Heikki Linnakangas wrote: Let's recap what happens when a VM bit is set: You set the PD_ALL_VISIBLE flag on the heap page (assuming it's not set already, it usually isn't), and then set the bit in the VM while keeping the heap page locked. What if we s

Re: [HACKERS] Idle git question: how come so many "objects"?

On Wed, Dec 01, 2010 at 01:03:26AM -0500, Tom Lane wrote: > So I just made a commit that touched four files in all six active > branches, and I see: > > $ git push > Counting objects: 172, done. > Compressing objects: 100% (89/89), done. > Writing objects: 100% (89/89), 17.07 KiB, done. > Total

[HACKERS] Improved JDBC driver part 2

Hello, Maybe you are interested about this what I done with JDBC === Original driver (Text mode) === * Memory * 1. Memory usage improvments when using result set input streams (no uneeded memory copy) - needs few touches for bigger performance. 2. Memory usage improvments for large data, should

[HACKERS] Idle git question: how come so many "objects"?

So I just made a commit that touched four files in all six active branches, and I see: $ git push Counting objects: 172, done. Compressing objects: 100% (89/89), done. Writing objects: 100% (89/89), 17.07 KiB, done. Total 89 (delta 80), reused 0 (delta 0) To ssh://g...@gitmaster.postgresql.org/

Re: [HACKERS] SQL/MED - core functionality

On Wed, 1 Dec 2010 12:30:46 +0900 Hitoshi Harada wrote: > This is another topic, but it would be useful if CREATE FOREIGN TABLE > can omit column definitions since fdw usually knows what should be > there in the definitions. I some times mistyped the column names > between remote and local and res

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

Josh Berkus writes: > On 11/30/10 7:09 PM, Tom Lane wrote: >> Josh Berkus writes: >>> Apparently, testing for O_DIRECT at compile time isn't adequate. Ideas? >> >> We should wait for the outcome of the discussion about whether to change >> the default wal_sync_method before worrying about this.

Re: [HACKERS] SQL/MED - core functionality

2010/12/1 Itagaki Takahiro : > On Wed, Dec 1, 2010 at 12:30, Hitoshi Harada wrote: >> From a user's view, this is very long way to see a simplest foreign >> table. I know it is based on the standard, but I really want a >> shortcut. Especially, I don't understand why CREATE USER MAPPING FOR >> cur

Re: [HACKERS] profiling connection overhead

On tis, 2010-11-30 at 15:49 -0500, Tom Lane wrote: > Peter Eisentraut writes: > > On mån, 2010-11-29 at 13:10 -0500, Tom Lane wrote: > >> Rolling in calloc in place of > >> malloc/memset made no particular difference either, which says that > >> Fedora 13's glibc does not have any optimization for

Re: [HACKERS] SQL/MED - core functionality

On Wed, Dec 1, 2010 at 12:30, Hitoshi Harada wrote: > From a user's view, this is very long way to see a simplest foreign > table. I know it is based on the standard, but I really want a > shortcut. Especially, I don't understand why CREATE USER MAPPING FOR > current_user SERVER is needed for def

Re: [HACKERS] profiling connection overhead

On 11/28/10, Robert Haas wrote: > > In a close race, I don't think we should get bogged down in > micro-optimization here, both because micro-optimizations may not gain > much and because what works well on one platform may not do much at > all on another. The more general issue here is what to d

Re: [HACKERS] Spread checkpoint sync

Jeff Janes wrote: Have you tested out this "absorb during syncing phase" code without the sleep between the syncs? I.e. so that it still a tight loop, but the loop alternates between sync and absorb, with no intentional pause? Yes; that's how it was developed. It helped to have just the ext

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

Andrew Dunstan writes: > On 11/30/2010 10:09 PM, Tom Lane wrote: >> We should wait for the outcome of the discussion about whether to change >> the default wal_sync_method before worrying about this. > we've just had a significant PGX customer encounter this with the latest > Postgres on Redhat'

Re: [HACKERS] [GENERAL] column-level update privs + lock table

(2010/11/30 21:26), Simon Riggs wrote: > On Mon, 2010-11-29 at 21:37 -0500, Josh Kupershmidt wrote: > >> I still see little reason to make LOCK TABLE permissions different for >> column-level vs. table-level UPDATE privileges > > Agreed. > > This is the crux of the debate. Why should this incons

[HACKERS] unlogged tables

I have played around a little more, and think I found a problem. If given enough time, an unlogged table makes it to disk, and a restart wont clear the data. If I insert a bunch of stuff, commit, and quickly restart PG, it table is cleared. If I let it sit for a while, it stays. Based on tha

Re: [HACKERS] SQL/MED - core functionality

2010/11/25 Shigeru HANADA : > Hi hackers, > > Attached is a patch that adds core functionality of SQL/MED.  This > patch provides: > >    "SQL/MED - file_fdw"       : FDW for external PostgreSQL >    "SEL/MED - postgresql_fdw" : FDW for server-side file (CSV, TEXT) > I've tried SQL/MED with postgr

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

On 11/30/2010 10:09 PM, Tom Lane wrote: Josh Berkus writes: Apparently, testing for O_DIRECT at compile time isn't adequate. Ideas? We should wait for the outcome of the discussion about whether to change the default wal_sync_method before worrying about this. To

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

On 11/30/10 7:09 PM, Tom Lane wrote: > Josh Berkus writes: >> Apparently, testing for O_DIRECT at compile time isn't adequate. Ideas? > > We should wait for the outcome of the discussion about whether to change > the default wal_sync_method before worrying about this. Are we considering backpor

Re: [HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

Josh Berkus writes: > Apparently, testing for O_DIRECT at compile time isn't adequate. Ideas? We should wait for the outcome of the discussion about whether to change the default wal_sync_method before worrying about this. regards, tom lane -- Sent via pgsql-hackers ma

Re: [HACKERS] Where are we on Standby Promotion?

On Tue, Nov 30, 2010 at 9:03 PM, Bruce Momjian wrote: > Josh Berkus wrote: >> Fujii, Simon, Greg, etc.: >> >> Has anyone submitted or committed a patch to make Standby Promotion* >> easier, at this point?  We discussed it earlier in the dev cycle, but I >> can't find anything which has actually be

[HACKERS] We really ought to do something about O_DIRECT and data=journalled on ext4

Hackers, Some of you might already be aware that this combination produces a fatal startup crash in PostgreSQL: 1. Create an Ext3 or Ext4 partition and mount it with data=journal on a server with linux kernel 2.6.30 or later. 2. Initdb a PGDATA on that partition 3. Start PostgreSQL with the defau

Re: [HACKERS] GiST insert algorithm rewrite

On Tue, Nov 30, 2010 at 10:26 AM, Heikki Linnakangas wrote: >> Does the current code cope with the corruption? > > It's not corruption, but "intended degradation". Yes, the current code copes > with it, that's how GiST survives a crash. However, even with the current > code, VACUUM will nag if it

Re: [HACKERS] GiST insert algorithm rewrite

Heikki Linnakangas wrote: > > Does the current code cope with the corruption? > > It's not corruption, but "intended degradation". Yes, the current code > copes with it, that's how GiST survives a crash. However, even with the > current code, VACUUM will nag if it finds any invalid tuples with t

Re: [HACKERS] Where are we on Standby Promotion?

Josh Berkus wrote: > Fujii, Simon, Greg, etc.: > > Has anyone submitted or committed a patch to make Standby Promotion* > easier, at this point? We discussed it earlier in the dev cycle, but I > can't find anything which has actually been submitted. > > * that is, promotion to be a new master of

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Daniel Loureiro wrote: > > 3. This doesn't work tremendously well for inheritance trees, where > > ModifyTable acts as sort of an implicit Append node. You can't just > > funnel all the tuples through one Sort or Limit node because they aren't > > all the same rowtype. (Limit might perhaps not ca

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas wrote: > On 30.11.2010 18:33, Tom Lane wrote: > > Robert Haas writes: > >> Oh, but it's worse than that. When you XLOG a WAL record for each of > >> those pages, you're going to trigger full-page writes for all of them. > >> So now you've turned 1GB of data to write into 2+ G

Re: [HACKERS] Spread checkpoint sync

On Sun, Nov 14, 2010 at 3:48 PM, Greg Smith wrote: ... > One change that turned out be necessary rather than optional--to get good > performance from the system under tuning--was to make regular background > writer activity, including fsync absorb checks, happen during these sync > pauses.  The

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, 2010-11-30 at 15:52 -0500, Robert Haas wrote: > On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis wrote: > > On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: > >> > >> Could you possibly have ModifyTable -> Limit -> MergeAppend? > > > > Before MergeAppend knows which tuple to produce, it ne

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Excerpts from Daniel Loureiro's message of mar nov 30 15:04:17 -0300 2010: > So I guess that I have choose the wrong hack to start. So it seems :-D -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Robert Haas writes: > You seem to be imagining the MergeAppend node on top, but I had it in > the other order in my mind. The ModifyTable node would be the > outermost plan node, pulling from the Limit, which would deliver the > first n table rows from the MergeAppend, which would be reponsible f

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Andres Freund writes: > On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: >> I don't buy the argument either; why would you put a LIMIT there and >> delete one row by accident when you could put a BEGIN; in front and not >> do any damage at all? > Because the delete of the whole table may

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Marko Tiikkaja writes: > While reading this thread, I thought of two things I think we could do > if this feature was implemented: > 1. Sort large UPDATE/DELETEs so it is done in heap order > This is actually a TODO item. I imagine it would be possible to do > something like: > DELETE FROM

Re: [HACKERS] KNNGIST next step: adjusting indexAM API

On Tue, Nov 30, 2010 at 2:50 PM, Tom Lane wrote: > In the current KNNGIST patch, the indexable ORDER BY clauses are > transmitted to the executor by cramming them in with the index qual > conditions (the IndexScan plan node's indexqual list), from whence > they become part of the ScanKey array pas

Re: [HACKERS] Spread checkpoint sync

> Maybe, but it's hard to argue that the current implementation--just > doing all of the sync calls as fast as possible, one after the other--is > going to produce worst-case behavior in a lot of situations. Given that > it's not a huge amount of code to do better, I'd rather do some work in > th

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 2:45 PM, Jeff Davis wrote: > On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: >> > 3. This doesn't work tremendously well for inheritance trees, where >> > ModifyTable acts as sort of an implicit Append node.  You can't just >> > funnel all the tuples through one Sort

Re: [HACKERS] profiling connection overhead

Peter Eisentraut writes: > On mån, 2010-11-29 at 13:10 -0500, Tom Lane wrote: >> Rolling in calloc in place of >> malloc/memset made no particular difference either, which says that >> Fedora 13's glibc does not have any optimization for that case as I'd >> hoped. > glibc's calloc is either mmap

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On 11/30/2010 03:16 PM, Andres Freund wrote: On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirowrote: to me the key its security - its a anti-DBA-with-lack-of-attention feature. Well, it seems pretty weak to me for th

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 9:24 PM, Marko Tiikkaja wrote: >> On 11/30/2010 02:12 PM, Kevin Grittner wrote: >>> >>> Daniel Loureiro   wrote: >>> to me the key its security - its a anti-DBA-with-lack-of-attention feature. >>> >>> Well, it seems pretty weak to me for that purpose.  You still t

Re: [HACKERS] Spread checkpoint sync

Ron Mayer wrote: Might smoother checkpoints be better solved by talking to the OS vendors & virtual-memory-tunning-knob-authors to work with them on exposing the ideal knobs; rather than saying that our only tool is a hammer(fsync) so the problem must be handled as a nail. Maybe, but it's ha

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tuesday 30 November 2010 20:24:52 Marko Tiikkaja wrote: > > On 11/30/2010 02:12 PM, Kevin Grittner wrote: > >> Daniel Loureiro wrote: > >>> to me the key its security - its a anti-DBA-with-lack-of-attention > >>> feature. > >> > >> Well, it seems pretty weak to me for that purpose. You still

Re: [HACKERS] Instrument checkpoint sync calls

On Tue, Nov 30, 2010 at 8:38 AM, Greg Smith wrote: Hi Greg, Thanks for the update. > This might be ready for some proper review now.  I know there's at least one > blatant bug still in here I haven't found yet, related to how the averages > are computed. Yes, the blatant bug: average_sync_

[HACKERS] Where are we on Standby Promotion?

Fujii, Simon, Greg, etc.: Has anyone submitted or committed a patch to make Standby Promotion* easier, at this point? We discussed it earlier in the dev cycle, but I can't find anything which has actually been submitted. -- -- Josh Berkus

Re: [HACKERS] Another proposal for table synonyms

Alexey, > Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add > synonyms > for relations (tables, views, sequences) and an infrastructure to allow > synonyms > for other database objects in the future. Can you explain, for our benefit, the use case for this? Specificall

Re: [HACKERS] Fix for seg picksplit function

On 2010-11-16 09:57, Alexander Korotkov wrote: On Tue, Nov 16, 2010 at 3:07 AM, Robert Haas > wrote: The loop that begins here: for (i = 0; i < maxoff; i++) { /* First half of segs goes to the left datum. */ if (i < seed_2)

[HACKERS] KNNGIST next step: adjusting indexAM API

In the current KNNGIST patch, the indexable ORDER BY clauses are transmitted to the executor by cramming them in with the index qual conditions (the IndexScan plan node's indexqual list), from whence they become part of the ScanKey array passed to the index AM. Robert complained that this was an in

Re: [HACKERS] DELETE with LIMIT (or my first hack)

While reading this thread, I thought of two things I think we could do if this feature was implemented: 1. Sort large UPDATE/DELETEs so it is done in heap order This is actually a TODO item. I imagine it would be possible to do something like: DELETE FROM foo USING (...) ORDER BY ctid; wi

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, 2010-11-30 at 11:12 -0500, Robert Haas wrote: > > 3. This doesn't work tremendously well for inheritance trees, where > > ModifyTable acts as sort of an implicit Append node. You can't just > > funnel all the tuples through one Sort or Limit node because they aren't > > all the same rowtyp

Re: [HACKERS] profiling connection overhead

On mån, 2010-11-29 at 13:10 -0500, Tom Lane wrote: > Rolling in calloc in place of > malloc/memset made no particular difference either, which says that > Fedora 13's glibc does not have any optimization for that case as I'd > hoped. glibc's calloc is either mmap of /dev/zero or malloc followed by

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureiro wrote: to me the key its security - its a anti-DBA-with-lack-of-attention feature. Well, it seems pretty weak to me for that purpose. You still trash data, and you don't have any immediate clue as to what. I agree, that argume

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureiro wrote: to me the key its security - its a anti-DBA-with-lack-of-attention feature. Well, it seems pretty weak to me for that purpose. You still trash data, and you don't have any immediate clue as to what. I agree, that argum

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Daniel Loureiro wrote: > to me the key its security - its a anti-DBA-with-lack-of-attention > feature. Well, it seems pretty weak to me for that purpose. You still trash data, and you don't have any immediate clue as to what. If you wanted protection from that you'd want more of an "assert l

Re: [HACKERS] [GENERAL] column-level update privs + lock table

Robert Haas writes: > Well, a user with full-table UPDATE privileges can trash the whole > thing, so, from a security perspective, letting them lock is only > allowing them to deny access to data they could have just as easily > trashed. A user with only single-column UPDATE privileges cannot > t

Re: [HACKERS] Instrument checkpoint sync calls

2010/11/30 Greg Smith : > Jeff Janes wrote: >> >> For the individual file sync times emitted under debug1, it would be >> very handy if the file being synced was identified, for example >> "relation base/16384/16523". Rather than being numbered sequentially >> within a given checkpoint. >> > > I wa

Re: [HACKERS] DELETE with LIMIT (or my first hack)

to me the key its security - its a anti-DBA-with-lack-of-attention feature. If i forget the "WHERE" statement, I will delete some valid tuples and messed up the bd, but its less-than-worst that exclude all the table. A DBA who never forgot an "WHERE" in an "DELETE" is not an DBA. Just kidding, but

Re: [HACKERS] [GENERAL] column-level update privs + lock table

On Tue, Nov 30, 2010 at 7:26 AM, Simon Riggs wrote: > On Mon, 2010-11-29 at 21:37 -0500, Josh Kupershmidt wrote: > >> I still see little reason to make LOCK TABLE permissions different for >> column-level vs. table-level UPDATE privileges > > Agreed. > > This is the crux of the debate. Why should

Re: [HACKERS] DELETE with LIMIT (or my first hack)

> 3. This doesn't work tremendously well for inheritance trees, where > ModifyTable acts as sort of an implicit Append node. You can't just > funnel all the tuples through one Sort or Limit node because they aren't > all the same rowtype. (Limit might perhaps not care, but Sort will.) > But you c

[HACKERS]

Re: [HACKERS] [GENERAL] column-level update privs + lock table

On Mon, 2010-11-29 at 21:37 -0500, Josh Kupershmidt wrote: > I still see little reason to make LOCK TABLE permissions different for > column-level vs. table-level UPDATE privileges Agreed. This is the crux of the debate. Why should this inconsistency be allowed to continue? Are there covert ch

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > On 30.11.2010 19:22, Tom Lane wrote: >> But having said that, I wonder whether we need a full-page image for >> a WAL-logged action that is known to involve only setting a single bit >> and updating LSN. > You have to write a full-page image if you update the LSN, bec

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 12:25 PM, Robert Haas wrote: > On Tue, Nov 30, 2010 at 12:22 PM, Tom Lane wrote: >> But having said that, I wonder whether we need a full-page image for >> a WAL-logged action that is known to involve only setting a single bit >> and updating LSN.  Would omitting the FPI b

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 12:22 PM, Tom Lane wrote: > But having said that, I wonder whether we need a full-page image for > a WAL-logged action that is known to involve only setting a single bit > and updating LSN.  Would omitting the FPI be any more risky than what > happens now (ie, the page does

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 19:22, Tom Lane wrote: But having said that, I wonder whether we need a full-page image for a WAL-logged action that is known to involve only setting a single bit and updating LSN. Would omitting the FPI be any more risky than what happens now (ie, the page does get written back to

Re: [HACKERS] crash-safe visibility map, take three

Robert Haas writes: > On Tue, Nov 30, 2010 at 12:10 PM, Tom Lane wrote: >> It's ridiculous to claim that that "doubles the cost of VACUUM".  In the >> worst case, it will add 25% to the cost of setting an all-visible bit on >> a page where there is no other work to do.  (You already are writing o

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 12:10 PM, Tom Lane wrote: > Robert Haas writes: >> We're not going to double the cost of VACUUM to get index-only scans. >> And that's exactly what will happen if you do full-page writes of >> every heap page to set a single bit. > > It's ridiculous to claim that that "dou

Re: [HACKERS] crash-safe visibility map, take three

Robert Haas writes: > We're not going to double the cost of VACUUM to get index-only scans. > And that's exactly what will happen if you do full-page writes of > every heap page to set a single bit. It's ridiculous to claim that that "doubles the cost of VACUUM". In the worst case, it will add 2

Re: [HACKERS] Another proposal for table synonyms

Alexey Klyukin writes: > On Nov 30, 2010, at 6:28 PM, Tom Lane wrote: >> This is not going to work, at least not without making every type of >> lookup consult pg_synonym too, which I think can be considered DOA >> because of its performance impact on people who aren't even using the >> feature.

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:59 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Nov 30, 2010 at 11:40 AM, Tom Lane wrote: >>> Ouch.  That seems like it could shoot down all these proposals.  There >>> definitely isn't any way to make VM crash-safe if there is no WAL-driven >>> mechanism for s

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:55 AM, Tom Lane wrote: > Heikki Linnakangas writes: >> Can we get away with not setting the LSN on the heap page, even though >> we set the PD_ALL_VISIBLE flag? If we don't set the LSN, the heap page >> can be flushed to disk before the WAL record, but I think that's fi

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:49 AM, Heikki Linnakangas wrote: > On 30.11.2010 18:33, Tom Lane wrote: >> >> Robert Haas  writes: >>> >>> Oh, but it's worse than that.  When you XLOG a WAL record for each of >>> those pages, you're going to trigger full-page writes for all of them. >>>  So now you've

Re: [HACKERS] crash-safe visibility map, take three

Robert Haas writes: > On Tue, Nov 30, 2010 at 11:40 AM, Tom Lane wrote: >> Ouch.  That seems like it could shoot down all these proposals.  There >> definitely isn't any way to make VM crash-safe if there is no WAL-driven >> mechanism for setting the bits. > Heikki's intent method works fine, be

Re: [HACKERS] Another proposal for table synonyms

On Nov 30, 2010, at 6:28 PM, Tom Lane wrote: > Alexey Klyukin writes: >> To support addition of new database objects types that can be referenced by >> synonyms a new system catalog, pg_synonym, is to be added, with an oid to >> support comments on synonym, and the following schema: > > This is

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > Can we get away with not setting the LSN on the heap page, even though > we set the PD_ALL_VISIBLE flag? If we don't set the LSN, the heap page > can be flushed to disk before the WAL record, but I think that's fine > because it's OK to have the flag set in the heap

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:40 AM, Tom Lane wrote: > Robert Haas writes: >> That's definitely sucky, but in some ways it would be more complicated >> if they did, because I don't think all-visible on the master implies >> all-visible on the standby. > > Ouch.  That seems like it could shoot down a

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 18:33, Tom Lane wrote: Robert Haas writes: Oh, but it's worse than that. When you XLOG a WAL record for each of those pages, you're going to trigger full-page writes for all of them. So now you've turned 1GB of data to write into 2+ GB of data to write. No, because only the f

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 18:40, Tom Lane wrote: Robert Haas writes: That's definitely sucky, but in some ways it would be more complicated if they did, because I don't think all-visible on the master implies all-visible on the standby. Ouch. That seems like it could shoot down all these proposals. The

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:33 AM, Tom Lane wrote: > Robert Haas writes: >> Oh, but it's worse than that.  When you XLOG a WAL record for each of >> those pages, you're going to trigger full-page writes for all of them. >>  So now you've turned 1GB of data to write into 2+ GB of data to >> write.

Re: [HACKERS] crash-safe visibility map, take three

Robert Haas writes: > That's definitely sucky, but in some ways it would be more complicated > if they did, because I don't think all-visible on the master implies > all-visible on the standby. Ouch. That seems like it could shoot down all these proposals. There definitely isn't any way to make

Re: [HACKERS] Instrument checkpoint sync calls

Jeff Janes wrote: For the individual file sync times emitted under debug1, it would be very handy if the file being synced was identified, for example "relation base/16384/16523". Rather than being numbered sequentially within a given checkpoint. I was numbering them sequentially so that it'

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > On 30.11.2010 18:10, Tom Lane wrote: >> I'm not convinced it works at all. Consider write intent record, >> checkpoint, set bit, crash before completing vacuum. There will be >> no second intent record at which you could clean up if things are >> inconsistent. > Tha

Re: [HACKERS] crash-safe visibility map, take three

Robert Haas writes: > Oh, but it's worse than that. When you XLOG a WAL record for each of > those pages, you're going to trigger full-page writes for all of them. > So now you've turned 1GB of data to write into 2+ GB of data to > write. No, because only the first mod of each VM page would tri

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 18:10, Tom Lane wrote: Heikki Linnakangas writes: Yeah, I'm not terribly excited about any of these schemes. The "intent" record seems like the simplest one, but even that is quite different from the traditional WAL-logging we do that it makes me slightly nervous. I'm not convin

Re: [HACKERS] Another proposal for table synonyms

Alexey Klyukin writes: > To support addition of new database objects types that can be referenced by > synonyms a new system catalog, pg_synonym, is to be added, with an oid to > support comments on synonym, and the following schema: This is not going to work, at least not without making every ty

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 18:22, Robert Haas wrote: On Tue, Nov 30, 2010 at 11:16 AM, Tom Lane wrote: How much is "quite a lot"? Do we have any real reason to think that this solution is unacceptable performance-wise? Well, let's imagine a 1GB insert-only table. It has 128K pages. If you XLOG setting

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:22 AM, Robert Haas wrote: > On Tue, Nov 30, 2010 at 11:16 AM, Tom Lane wrote: >> How much is "quite a lot"?  Do we have any real reason to think that >> this solution is unacceptable performance-wise? > > Well, let's imagine a 1GB insert-only table.  It has 128K pages.

Re: [HACKERS] [GENERAL] column-level update privs + lock table

On Tue, Nov 30, 2010 at 9:07 AM, Josh Kupershmidt wrote: > On Mon, Nov 29, 2010 at 10:06 PM, Robert Haas wrote: >> On Mon, Nov 29, 2010 at 9:37 PM, Josh Kupershmidt wrote: >>> I actually hadn't thought of that, for some reason. >>> >>> We used to similarly recommend that people handle TRUNCATE p

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 11:16 AM, Tom Lane wrote: > How much is "quite a lot"?  Do we have any real reason to think that > this solution is unacceptable performance-wise? Well, let's imagine a 1GB insert-only table. It has 128K pages. If you XLOG setting the bit on each page, you'll need to wri

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > The trivial solution to this is to WAL-log setting the visibility map > bit, like we WAL-log any other operation. Lock the heap page, lock the > visibility map page, write WAL-record, and release locks. That works, > but the problem is that it creates quite a lot of

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 11:04 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane wrote: >>> Unfortunately, the UPDATE >>> case would be an order of magnitude harder (think inheritance trees >>> where the children aren't all alike). > >> I don't understand why

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > On 30.11.2010 17:38, Tom Lane wrote: >> Wouldn't it be easier and more robust to just consider VM bit changes to >> be part of the WAL-logged actions? That would include updating LSNs on >> VM pages and flushing VM pages to disk during checkpoint based on their >> LSN

[HACKERS] Another proposal for table synonyms

Hello, Here is the proposal to add synonyms to PostgreSQL. Initial goal is to add synonyms for relations (tables, views, sequences) and an infrastructure to allow synonyms for other database objects in the future. A thread with discussion of an old proposal by Jonah Harris is here: http://arch

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Hi Robert, On Tue, 2010-11-30 at 09:19 -0500, Robert Haas wrote: > That's a very elegant hack, but not exactly obvious to a novice user > or, say, me. So I think it'd be nicer to have the obvious syntax > work. I fully agree - but you first have to convince core hackers that this is not just a f

Re: [HACKERS] DELETE with LIMIT (or my first hack)

Robert Haas writes: > On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane wrote: >> Unfortunately, the UPDATE >> case would be an order of magnitude harder (think inheritance trees >> where the children aren't all alike). > I don't understand why there's anything more to this than sticking a > Limit node

Re: [HACKERS] crash-safe visibility map, take three

Here's one more idea: The trivial solution to this is to WAL-log setting the visibility map bit, like we WAL-log any other operation. Lock the heap page, lock the visibility map page, write WAL-record, and release locks. That works, but the problem is that it creates quite a lot of new WAL tra

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 10:43 AM, Heikki Linnakangas wrote: >> It seems like you'll need to hold some kind of lock between the time >> you examine RedoRecPtr and the time you actually examine the bit. >> WALInsertLock in shared mode, maybe? > > It's enough to hold an exclusive lock on the visibili

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 17:38, Tom Lane wrote: Heikki Linnakangas writes: On 30.11.2010 06:57, Robert Haas wrote: I can't say I'm totally in love with any of these designs. Anyone else have any ideas, or any opinions about which one is best? Well, the design I've been pondering goes like this: Wou

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 10:38 AM, Tom Lane wrote: > Heikki Linnakangas writes: >> On 30.11.2010 06:57, Robert Haas wrote: >>> I can't say I'm totally in love with any of these designs.  Anyone >>> else have any ideas, or any opinions about which one is best? > >> Well, the design I've been ponder

Re: [HACKERS] crash-safe visibility map, take three

On 30.11.2010 17:32, Robert Haas wrote: On Tue, Nov 30, 2010 at 2:34 AM, Heikki Linnakangas wrote: Some care is needed with checkpoints. Setting visibility map bits in step 2 is safe because crash recovery will replay the intent XLOG record and clear any incorrectly set bits. But if a checkpoi

Re: [HACKERS] DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane wrote: > Robert Haas writes: >> On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan wrote: >>> I can't say I'd be excited by this feature. In quite a few years of writing >>> SQL I don't recall ever wanting such a gadget. > >> It's something I've wanted pe

Re: [HACKERS] crash-safe visibility map, take three

Heikki Linnakangas writes: > On 30.11.2010 06:57, Robert Haas wrote: >> I can't say I'm totally in love with any of these designs. Anyone >> else have any ideas, or any opinions about which one is best? > Well, the design I've been pondering goes like this: Wouldn't it be easier and more robust

Re: [HACKERS] crash-safe visibility map, take three

On Tue, Nov 30, 2010 at 2:34 AM, Heikki Linnakangas wrote: > Some care is needed with checkpoints. Setting visibility map bits in step 2 > is safe because crash recovery will replay the intent XLOG record and clear > any incorrectly set bits. But if a checkpoint has happened after the intent > XLO

Re: [HACKERS] GiST insert algorithm rewrite

On 30.11.2010 16:23, Robert Haas wrote: On Tue, Nov 30, 2010 at 5:02 AM, Heikki Linnakangas wrote: On 30.11.2010 11:55, Heikki Linnakangas wrote: On 27.11.2010 21:31, Bruce Momjian wrote: Heikki Linnakangas wrote: There's no on-disk format changes, except for the additional flag in the p

  1   2   >