[HACKERS] database file encryption.

Hi, I am new to this list. I haven't ever contributed code before, and have no idea on how to do this. I have made some changes to my copy of the 9.1.1 code that encrypts/decrypts the database files on the fly using AES256 cypher. It passes all the tests. :-) The changes are limited to :

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 11:14 AM, Robert Haas wrote: > On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane wrote: >> Robert Haas writes: >>> On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane wrote: I don't know why you'd imagine that touching an index is free, or even cheap, CPU-wise.  The whole point

Re: [HACKERS] EXECUTE tab completion

On Thu, Oct 20, 2011 at 5:16 PM, Andreas Karlsson wrote: > A new version is attached. Looks fine. Marking ready for committer (CF 2011-11). Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-

Re: [HACKERS] psql command for bytea output

Andrew Dunstan writes: > I also think this is really just a psql issue. Most other clients (e.g. > a perl DBD::Pg client) will have no trouble handling all this now. Well, that's debatable, and in any case I think you'd find it will be easier to push this into COPY than to invent new psql backsl

Re: [HACKERS] psql \set vs \copy - bug or expected behaviour?

On Fri, Oct 21, 2011 at 7:24 AM, Richard Huxton wrote: > It looks like \copy is just passing the text of the query unadjusted to > "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and > 9.1 > > === test script === > \set x '''HELLO''' > -- Works > \echo :x > -- Works > \o '/tm

Re: [HACKERS] psql command for bytea output

On 10/21/2011 04:43 PM, Pavel Stehule wrote: What I don't like about Andrew's proposal is that it seems rather limited. Why bytea in particular? Text chunks could probably also use a direct output method. And what about input? Could we do anything with a notion of a COPY RAW mode, that woul

Re: [HACKERS] psql command for bytea output

2011/10/21 Tom Lane : > Pavel Stehule writes: >> 2011/10/21 Andrew Dunstan : >>> On 10/21/2011 02:44 PM, Pavel Stehule wrote: isn't better to fix current tools to work well with bytea? > >>> Such as? > >> some like >> \copy ... to foo.bin format binary > > No, because COPY BINARY will emit it

Re: [HACKERS] [PATCH] Log crashed backend's query v3

On Fri, Oct 21, 2011 at 4:37 PM, Tom Lane wrote: > Robert Haas writes: >> I have committed this version.  I'm expecting Tom to try to find a >> scenario in which it's unfixably broken, so we'll see how that turns >> out; but there seems to be significant support for this feature and >> I'm hopefu

Re: [HACKERS] [PATCH] Log crashed backend's query v3

Robert Haas writes: > I have committed this version. I'm expecting Tom to try to find a > scenario in which it's unfixably broken, so we'll see how that turns > out; but there seems to be significant support for this feature and > I'm hopeful that this will pass (or can be made to pass) muster.

Re: [HACKERS] psql command for bytea output

Pavel Stehule writes: > 2011/10/21 Andrew Dunstan : >> On 10/21/2011 02:44 PM, Pavel Stehule wrote: >>> isn't better to fix current tools to work well with bytea? >> Such as? > some like > \copy ... to foo.bin format binary No, because COPY BINARY will emit its own sort of wrappers around the d

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 3:55 PM, Tom Lane wrote: > Robert Haas writes: >> Anyhow, here's the scoop.  On my desktop machine running F14, running >> SELECT sum(1) FROM pgbench_accounts in a tight loop, 60 s worth of >> oprofile data: > >> 176830   13.0801  postgres                 postgres        

Re: [HACKERS] So, is COUNT(*) fast now?

Robert Haas writes: > Hmm, I guess there is a bit of a hotspot in StoreIndexTuple, which is > probably being folded into IndexOnlyNext in the per-function timings: > ExecClearTuple(slot); > for (i = 0; i < nindexatts; i++) > values[i] = index_getattr(itup, i + 1, itupdesc, &isnull

Re: [HACKERS] So, is COUNT(*) fast now?

Robert Haas writes: > Anyhow, here's the scoop. On my desktop machine running F14, running > SELECT sum(1) FROM pgbench_accounts in a tight loop, 60 s worth of > oprofile data: > 176830 13.0801 postgres postgres > ExecProject Hm, that's weird. In both these

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 3:07 PM, Robert Haas wrote: > [ oprofile results ] *grovels through the line-by-line results* Hmm, I guess there is a bit of a hotspot in StoreIndexTuple, which is probably being folded into IndexOnlyNext in the per-function timings: ExecClearTuple(slot); for (i

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 2:33 PM, Tom Lane wrote: >> I think HeapTupleSatisfiesMVCC is probably being skipped anyway in >> this case, since all the heap pages should be PD_ALL_VISIBLE. > > Proves my point ;-) ... you're comparing a code path that's been beat on > for *years* with one that just got

Re: [HACKERS] psql command for bytea output

On 10/21/2011 02:51 PM, Pavel Stehule wrote: 2011/10/21 Andrew Dunstan: On 10/21/2011 02:44 PM, Pavel Stehule wrote: 2011/10/21 Andrew Dunstan: A few months ago, I blogged about the difficulty of getting psql to put a bytea datum into a file. See

Re: [HACKERS] psql command for bytea output

2011/10/21 Andrew Dunstan : > On 10/21/2011 02:44 PM, Pavel Stehule wrote: >> >> 2011/10/21 Andrew Dunstan: >>> >>> A few months ago, I blogged about the difficulty of getting psql to put a >>> bytea datum into a file. See >>> >>>

Re: [HACKERS] psql command for bytea output

On 10/21/2011 02:44 PM, Pavel Stehule wrote: 2011/10/21 Andrew Dunstan: A few months ago, I blogged about the difficulty of getting psql to put a bytea datum into a file. See . Today I ran into the pro

Re: [HACKERS] psql command for bytea output

2011/10/21 Andrew Dunstan : > > A few months ago, I blogged about the difficulty of getting psql to put a > bytea datum into a file. See > . > Today I ran into the problem again, and it struck me that we

Re: [HACKERS] So, is COUNT(*) fast now?

Robert Haas writes: > On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane wrote: >> What this test case proves is that btree's overhead per index >> tuple touched is significantly more than the cost of the fastest path >> through HeapTupleSatisfiesMVCC, which I don't find surprising >> considering how much

Re: [HACKERS] Synchronized snapshots versus multiple databases

Robert Haas writes: > On Fri, Oct 21, 2011 at 11:36 AM, Tom Lane wrote: >> 1. Restrict exported snapshots to be loaded only by transactions running >> in the same database as the exporter.  This would fix the problem, but >> it cuts out one of the main use-cases for sync snapshots, namely getting

[HACKERS] psql command for bytea output

A few months ago, I blogged about the difficulty of getting psql to put a bytea datum into a file. See . Today I ran into the problem again, and it struck me that we could fairly easily have a new c

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane wrote: >>> I don't know why you'd imagine that touching an index is free, or even >>> cheap, CPU-wise.  The whole point of the index-only optimization is to >>> avoid I/O.  When yo

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 2:06 PM, Florian Pflug wrote: > On Oct21, 2011, at 19:47 , Robert Haas wrote: >> On Fri, Oct 21, 2011 at 1:40 PM, Florian Pflug wrote: >>> AFAIR, the performance hit we'd take by making the vacuum cutoff point >>> (i.e. GetOldestXmin()) global instead of database-local has

Re: [HACKERS] Synchronized snapshots versus multiple databases

Florian Pflug writes: > AFAIR, the performance hit we'd take by making the vacuum cutoff point > (i.e. GetOldestXmin()) global instead of database-local has been repeatedly > used in the past as an against against cross-database queries. I have to > admit that I currently cannot seem to find an en

Re: [HACKERS] So, is COUNT(*) fast now?

Robert Haas writes: > On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane wrote: >> I don't know why you'd imagine that touching an index is free, or even >> cheap, CPU-wise.  The whole point of the index-only optimization is to >> avoid I/O.  When you try it on a case where there's no I/O to be saved, >>

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Oct21, 2011, at 19:47 , Robert Haas wrote: > On Fri, Oct 21, 2011 at 1:40 PM, Florian Pflug wrote: >> AFAIR, the performance hit we'd take by making the vacuum cutoff point >> (i.e. GetOldestXmin()) global instead of database-local has been repeatedly >> used in the past as an against against c

Re: [HACKERS] Synchronized snapshots versus multiple databases

On 10/21/2011 01:06 PM, Tom Lane wrote: Andrew Dunstan writes: On 10/21/2011 12:05 PM, Florian Pflug wrote: On Oct21, 2011, at 17:36 , Tom Lane wrote: 1. Restrict exported snapshots to be loaded only by transactions running in the same database as the exporter. This would fix the problem,

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 1:40 PM, Florian Pflug wrote: > AFAIR, the performance hit we'd take by making the vacuum cutoff point > (i.e. GetOldestXmin()) global instead of database-local has been repeatedly > used in the past as an against against cross-database queries. I have to > admit that I cur

Re: [HACKERS] [PATCH] Log crashed backend's query v3

On Fri, Oct 21, 2011 at 11:45 AM, Marti Raudsepp wrote: >> It's almost making me cry >> thinking about how much time this would have saved me > > Thanks for your review and the generous words. :) I have committed this version. I'm expecting Tom to try to find a scenario in which it's unfixably b

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

On Oct21, 2011, at 16:42 , Phil Sorber wrote: > If you did want to make them immutable, I also like Florian's idea of > a dependency graph. This would make the dumps less readable though. Hm, I kinda reversed my opinion on that, though - i.e., I no longer think that the dependency graph idea has m

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Oct21, 2011, at 19:09 , Tom Lane wrote: > Florian Pflug writes: >> On Oct21, 2011, at 17:36 , Tom Lane wrote: >>> 3. Remove the optimization that lets GetOldestXmin ignore XIDs outside >>> the current database. This sounds bad, but OTOH I don't think there's >>> ever been any proof that this o

Re: [HACKERS] So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane wrote: > Robert Haas writes: >> That's a bit disappointing - it's now more than a third faster to do >> the sequential scan, even though the sequential scan has to touch six >> times as many blocks (at scale factor 20, index is 43 MB, table is 256 >> MB)

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 11:36 AM, Tom Lane wrote: > I've thought of another nasty problem for the sync-snapshots patch. > > 1. Restrict exported snapshots to be loaded only by transactions running > in the same database as the exporter.  This would fix the problem, but > it cuts out one of the mai

Re: [HACKERS] So, is COUNT(*) fast now?

Robert Haas writes: > That's a bit disappointing - it's now more than a third faster to do > the sequential scan, even though the sequential scan has to touch six > times as many blocks (at scale factor 20, index is 43 MB, table is 256 > MB) all of which are in cache. Of course, touching that man

Re: [HACKERS] Synchronized snapshots versus multiple databases

Florian Pflug writes: > On Oct21, 2011, at 17:36 , Tom Lane wrote: >> 3. Remove the optimization that lets GetOldestXmin ignore XIDs outside >> the current database. This sounds bad, but OTOH I don't think there's >> ever been any proof that this optimization is worth much in real-world >> usage.

Re: [HACKERS] Synchronized snapshots versus multiple databases

Andrew Dunstan writes: > On 10/21/2011 12:05 PM, Florian Pflug wrote: >> On Oct21, 2011, at 17:36 , Tom Lane wrote: >>> 1. Restrict exported snapshots to be loaded only by transactions running >>> in the same database as the exporter. This would fix the problem, but >>> it cuts out one of the mai

Re: [HACKERS] [v9.2] Object access hooks with arguments support (v1)

On Fri, Oct 21, 2011 at 12:44 PM, Kohei KaiGai wrote: > I had checked my older implementation based on 8.4.x or 9.0.x that > includes all the features that I want to implement. > At least, it does not require so much different information from ones > needed by DAC model, although SELECT INTO was a

Re: [HACKERS] [v9.2] Object access hooks with arguments support (v1)

> When someone comes along in another year or two and adds materialized > views, will they need to pass some additional data to the object > access hook?  Probably, but I bet you're the only one who can quickly > figure out what it is.  That's no good.  We're not going to make > changes to PostgreS

[HACKERS] So, is COUNT(*) fast now?

Laments at: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F http://wiki.postgresql.org/wiki/Slow_Counting I tried this on my MacBook Pro this morning, using pgbench -i -s 500 to create a database about 7.5GB in size, and then using "SELECT sum(1) FR

Re: [HACKERS] Synchronized snapshots versus multiple databases

On 10/21/2011 12:05 PM, Florian Pflug wrote: On Oct21, 2011, at 17:36 , Tom Lane wrote: 1. Restrict exported snapshots to be loaded only by transactions running in the same database as the exporter. This would fix the problem, but it cuts out one of the main use-cases for sync snapshots, name

Re: [HACKERS] funny lock mode in DropTrigger

Alvaro Herrera writes: > Ah, I also noticed that the ALTER EXTENSION and SECURITY LABEL commands > use ShareUpdateExclusiveLock, and they failed when committed to note > this in the 13.3 chapter of the docs. Not sure how strict we are about > documenting these things. (I note COMMENT already fai

Re: [HACKERS] funny lock mode in DropTrigger

Alvaro Herrera writes: > I just noticed that DropTrigger uses ShareRowExclusiveLock to lock the > relation it's going to drop the trigger on. The comment right above it > says that this should match RemoveTriggerById, but that one uses > AccessExclusiveLock -- so one of them (or the comment) is n

Re: [HACKERS] Synchronized snapshots versus multiple databases

On Oct21, 2011, at 17:36 , Tom Lane wrote: > 1. Restrict exported snapshots to be loaded only by transactions running > in the same database as the exporter. This would fix the problem, but > it cuts out one of the main use-cases for sync snapshots, namely getting > cluster-wide-consistent dumps i

Re: [HACKERS] ProcessStandbyHSFeedbackMessage can make global xmin go backwards

Merlin Moncure writes: > On Wed, Oct 19, 2011 at 6:04 PM, Tom Lane wrote: >> ProcessStandbyHSFeedbackMessage has a race condition: it thinks it can >> call GetOldestXmin and then the result will politely hold still while >> it considers what to do next. > curious: are these bugs in production, a

Re: [HACKERS] pg_comments (was: Allow \dd to show constraint comments)

On Mon, Oct 17, 2011 at 05:04, Robert Haas wrote: > Hearing no cries of "oh, yes, please", I'm marking this Returned with > Feedback for now.  We can always revisit it if we hear that more > people want it. I think this would be an improvement, but it's pretty low on my wishlist. I haven't checke

Re: [HACKERS] ProcessStandbyHSFeedbackMessage can make global xmin go backwards

On Wed, Oct 19, 2011 at 6:04 PM, Tom Lane wrote: > ProcessStandbyHSFeedbackMessage has a race condition: it thinks it can > call GetOldestXmin and then the result will politely hold still while > it considers what to do next.  But in fact, whoever has the oldest xmin > could exit their transaction

Re: [HACKERS] [PATCH] Log crashed backend's query v3

Hi, here's version 4 of the patch. On Wed, Oct 19, 2011 at 19:34, Robert Haas wrote: > I think it would be safer to write this so that > pgstat_get_crashed_backend_activity writes its answer into a > statically allocated buffer and returns a pointer to that buffer, > rather than using palloc. I t

[HACKERS] Synchronized snapshots versus multiple databases

I've thought of another nasty problem for the sync-snapshots patch. Consider the following sequence of events: 1. Transaction A, which is about to export a snapshot, is running in database X. 2. Transaction B is making some changes in database Y. 3. A takes and exports a snapshot showing B's xi

Re: [HACKERS] WIP: Join push-down for foreign tables

How about the current status of this patch, although it is still "Waiting on author". If Hanada-san would propose contrib/pgsql_fdw as a basis of join-pushdown feature, I'll likely volunteer to review the patch. I'm also interested in this feature. Hopefully, I'd like to try other kind of pushing

Re: [HACKERS] pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

On Wed, Oct 19, 2011 at 7:46 PM, Florian Pflug wrote: > On Oct20, 2011, at 01:19 , Tom Lane wrote: >> Florian Pflug writes: >>> Taking this even further, why do we bother with non-immutable (i.e., >>> depending on the database's contents) checks during ALTER ROLE/DATABASET SET >>> at all? >> >> Y

Re: [HACKERS] [v9.2] Fix Leaky View Problem

On Fri, Oct 21, 2011 at 10:36 AM, Kohei KaiGai wrote: > So, I will split the patch into two parts as follows, in the next commit fest. > > Part-1) Views with security_barrier reloption > > The part-1 portion provides views "security_barrier" reloption; that enables > to keep sub-queries unflatten

Re: [HACKERS] pg_upgrade if 'postgres' database is dropped

On Tue, Oct 4, 2011 at 12:11 PM, Heikki Linnakangas wrote: > pg_upgrade doesn't work if the 'postgres' database has been dropped in the > old cluster: > > ~/pgsql.master$ bin/pg_upgrade -b ~/pgsql.91stable/bin -B bin/ -d > ~/pgsql.91stable/data -D data-upgraded/ > Performing Consistency Checks > -

Re: [HACKERS] [v9.2] Fix Leaky View Problem

So, I will split the patch into two parts as follows, in the next commit fest. Part-1) Views with security_barrier reloption The part-1 portion provides views "security_barrier" reloption; that enables to keep sub-queries unflatten in the prepjoin.c stage. In addition, these sub-queries (that ori

Re: [HACKERS] [v9.2] DROP statement reworks

On Fri, Oct 21, 2011 at 5:08 AM, Kohei KaiGai wrote: > It seems to me v9.0 implementation is correct. It might be enbugged > when OpFamilyCacheLookup() get missing_ok argument. :-( Yep, looks that way. Will fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

[HACKERS] psql \set vs \copy - bug or expected behaviour?

It looks like \copy is just passing the text of the query unadjusted to "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and 9.1 === test script === \set x '''HELLO''' -- Works \echo :x -- Works \o '/tmp/test1.txt' COPY (SELECT :x) TO STDOUT; -- Doesn't work \copy (SELECT :

Re: [HACKERS] ProcessStandbyHSFeedbackMessage can make global xmin go backwards

Excerpts from Tom Lane's message of jue oct 20 19:20:19 -0300 2011: > So I've concluded that there's just no point in the GetOldestXmin > clamping, and we should just apply the xmin value we get from the > standby if it passes basic sanity checks (the epoch test), and hope that > we're not too la

[HACKERS] funny lock mode in DropTrigger

I just noticed that DropTrigger uses ShareRowExclusiveLock to lock the relation it's going to drop the trigger on. The comment right above it says that this should match RemoveTriggerById, but that one uses AccessExclusiveLock -- so one of them (or the comment) is not right. Besides, the docs st

Re: [HACKERS] funny lock mode in DropTrigger

Excerpts from Alvaro Herrera's message of vie oct 21 00:40:26 -0300 2011: > Besides, the docs state that no backend code uses ShareRowExclusiveLock > anyway (13.3 Explicit locking). I guess that if the patch to reduce > lock strength in alter table goes in again, it'll need to update this > chap

Re: [HACKERS] [v9.2] DROP statement reworks

2011/10/20 Robert Haas : > On Thu, Oct 20, 2011 at 10:49 AM, Kohei KaiGai wrote: part-3: drop statement reworks for other object classes >>> >>> This is going to need some rebasing. >>> >> OK, I rebased it. >> >> This patch includes bug-fix when we tried to drop non-existence >> operator fami