Re: [HACKERS] How to implement Gin method?
Hi.
Ok, ok thanks.
My problem is to shorten time of searching full text stored in text field.
The table definition is like following:
CREATE TABLE xxx
(
...
title character varying,
...
fts1body text,
...
)
If user requests keywords, we use a kind of stristr that is targeting
Japanese text encoded in UTF-8.
aaa bbb ccc [Click here to search!]
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'aaa') OR ddstrike(fts1body,'aaa') OR ...)
AND (ddstrike(title,'bbb') OR ddstrike(fts1body,'bbb') OR ...)
AND ...
As you can imagine easily, yes, it is very slow!
So I need trial and error for speeding up.
My trial is Insert a light weight filter done by integer key, before text
searching!
For example,
filter('A') - 1
filter('B') - 2
filter('C') - 4
filter('AAABBC') - 7 or {1,2,4}
It may fit to inverse index like GIN!
So I began to study GIN.
I'm sorry to say. Today I found I could apply int array GIN support at
contrib/_int.sql.
I made GIN index.
CREATE INDEX xxx_idx_filter ON xxx USING GIN (filter(fts1body) gist__int_ops);
The following sample query is very very fast! 11065 hits in 22 milli secs
(total 215,278 records).
SELECT COUNT(*) FROM xxx WHERE filter(fts1body) @ filter('ABC');
However the following query is very slow! 9,400ms. It uses Seq Scan lol.
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'ABC') OR (filter(fts1body) @ filter('AAA') AND
ddstrike(fts1body,'AAA')))
Apply filter to title column too.
The best query result costs 3,700ms. 18 hits. It surely uses expected query
plan: two Bitmap index scan - Bitmap Or - Bitmap Heap Scan.
SELECT * FROM xxx
WHERE TRUE
AND (filter(title) @ filter('ABC') OR filter(fts1body) @ filter('ABC'))
AND (ddstrike(title,'ABC') OR ddstrike(fts1body,'ABC'))
The pure query costs 3,800ms. 18 hits. Single Seq Scan.
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'ABC') OR ddstrike(fts1body,'ABC'))
Finally I noticed I had spent useless time, and need to find another good one.
Sorry.
However, I may think good idea which uses inverted index.
So I want to know...
- the actual work of extractQuery and consistant.
- the detail interface of extractValue/extractQuery/consistant. It may help
understanding.
I looked at contrib/_int.sql of PG8.2.22
There are definitions of int[] GIN support.
---
CREATE OPERATOR CLASS gin__int_ops
FOR TYPE _int4 USING gin
AS
OPERATOR3 ,
OPERATOR6 = (anyarray, anyarray) RECHECK,
OPERATOR7 @,
OPERATOR8 @ RECHECK,
OPERATOR13 @,
OPERATOR14 ~ RECHECK,
OPERATOR20 @@ (_int4, query_int),
FUNCTION1 btint4cmp (int4, int4),
FUNCTION2 ginarrayextract (anyarray, internal),
FUNCTION3 ginint4_queryextract (internal, internal, int2),
FUNCTION4 ginint4_consistent (internal, int2, internal),
STORAGE int4;
---
I checked the PG8.2.22 source code.
Both ginint4_queryextract and ginint4_consistent assume that query argument
is a PGARRAY (ArrayType *). Where is it decided? Is it array of STORAGE type?
Both extractQuery(ginint4_queryextract) and extractValue(ginarrayextract) seem
to return similar value type. They return Datum array of int4. Is it array of
STORAGE type?
I want to understand the overview of GIN extension.
Thanks
kenji uno
--
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] Review: extension template
On 08.07.2013 00:48, Markus Wanner wrote: On 07/07/2013 09:51 PM, Dimitri Fontaine wrote: The design we found to address that is called Extension Templates and is implemented in the current patch. I placed my concerns with the proposed implementation. It's certainly not the only way how Postgres can manage its extensions. And I still hope we can come up with something that's simpler to use and easier to understand. I'm just now dabbling back to this thread after skipping a lot of discussion, and I'm disappointed to see that this still seems to be running in circles on the same basic question: What exactly is the patch trying to accomplish. The whole point of extensions, as they were originally implemented, is to allow them to be managed *outside* the database. In particular, they are not included in pg_dump. If you do want them to be included in pg_dump, why create it as an extension in the first place? Why not just run the create script and create the functions, datatypes etc. directly, like you always did before extensions were even invented. I think the reason is that extensions provide some handy packaging of the functions etc, so that you can just do DROP EXTENSION foo to get rid of all of them. Also, pg_extension table keeps track of the currently installed version. Perhaps we need to step back and invent another concept that is totally separate from extensions, to provide those features. Let's call them modules. A module is like an extension, in that all the objects in the module can be dropped with a simple DROP MODULE foo command. To create a module, you run CREATE MODULE foo AS SQL script to create the objects in the module. I believe that would be pretty much exactly what Dimitri's original inline extension patches did, except that it's not called an extension, but a module. I think it's largely been the naming that has been the problem with this patch from the very beginning. We came up with the concept of templates after we had decided that the originally proposed behavior was not what we want from something called extensions. But if you rewind to the very beginning, the problem was just with the name. The concept was useful, but not something we want to call an extension, because the distinguishing feature of an extension is that it lives outside the database and is *not* included in pg_dump. - 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] Review: extension template
On 07/08/2013 09:26 AM, Heikki Linnakangas wrote: On 08.07.2013 00:48, Markus Wanner wrote: On 07/07/2013 09:51 PM, Dimitri Fontaine wrote: The design we found to address that is called Extension Templates and is implemented in the current patch. I placed my concerns with the proposed implementation. It's certainly not the only way how Postgres can manage its extensions. And I still hope we can come up with something that's simpler to use and easier to understand. I'm just now dabbling back to this thread after skipping a lot of discussion, and I'm disappointed to see that this still seems to be running in circles on the same basic question: What exactly is the patch trying to accomplish. The whole point of extensions, as they were originally implemented, is to allow them to be managed *outside* the database. In particular, they are not included in pg_dump. If you do want them to be included in pg_dump, why create it as an extension in the first place? Why not just run the create script and create the functions, datatypes etc. directly, like you always did before extensions were even invented. I think the reason is that extensions provide some handy packaging of the functions etc, so that you can just do DROP EXTENSION foo to get rid of all of them. Also, pg_extension table keeps track of the currently installed version. Perhaps we need to step back and invent another concept that is totally separate from extensions, to provide those features. Let's call them modules. A module is like an extension, in that all the objects in the module can be dropped with a simple DROP MODULE foo command. To create a module, you run CREATE MODULE foo AS SQL script to create the objects in the module. I believe that would be pretty much exactly what Dimitri's original inline extension patches did, except that it's not called an extension, but a module. I think it's largely been the naming that has been the problem with this patch from the very beginning. We came up with the concept of templates after we had decided that the originally proposed behavior was not what we want from something called extensions. But if you rewind to the very beginning, the problem was just with the name. The concept was useful, but not something we want to call an extension, because the distinguishing feature of an extension is that it lives outside the database and is *not* included in pg_dump. Either MODULE or PACKAGE would be good name candidates. Still, getting this functionality in seems more important than exact naming, though naming them right would be nice. - 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] XLogInsert scaling, revisited
On 01.07.2013 16:40, Andres Freund wrote: On 2013-06-26 18:52:30 +0300, Heikki Linnakangas wrote: * Could you document the way slots prevent checkpoints from occurring when XLogInsert rechecks for full page writes? I think it's correct - but not very obvious on a glance. There's this in the comment near the top of the file: * To update RedoRecPtr or fullPageWrites, one has to make sure that all * subsequent inserters see the new value. This is done by reserving all the * insertion slots before changing the value. XLogInsert reads RedoRecPtr and * fullPageWrites after grabbing a slot, so by holding all the slots * simultaneously, you can ensure that all subsequent inserts see the new * value. Those fields change very seldom, so we prefer to be fast and * non-contended when they need to be read, and slow when they're changed. Does that explain it well enough? XLogInsert holds onto a slot while it rechecks for full page writes. I am a bit worried about that logic. We're basically reverting to the old logic whe xlog writing is an exlusive affair. We will have to wait for all the other queued inserters before we're finished. I am afraid that that will show up latencywise. A single stall of the xlog-insertion pipeline at a checkpoint is hardly going to be a problem. I wish PostgreSQL was real-time enough for that to matter, but I think we're very very far from that. - 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] Add visibility map information to pg_freespace.
Hello, - How about pageinspect? I proposed a simple representation format as a basis for discussion. Nevertheless, the VM pages has no more structure than a simple bit string. Given the VM info in pg_freespacemap, I've come in doubt of the necessity of vm_page_contnets() for the reason besides the orthogonality in the this extension's interface (which paid no attention before:-). I don't think that will be needed, now I understand. Ok, I'll drop it from the list. - How about pgstattuple? It could even be said to be meaningful to add the number of not-all-visible pages or the ratio of it in the total pages.. .. | free_percent | 37.88 + not_all_visible_page_percent | 23.54 # This column name looks too long, though. Yes, please. But name should be all_visible_percent. Anybody that wants not_all_visible_percent can do the math. You're quite right, plus, negative terms are a bit confusing. I'll come again with the first implementation of it. And as for pg_freespacemap, I'll keep the current direction - adding column to present output records format of pg_freespace(). And documentation, if possible. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- 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] Review: extension template
On 07/08/2013 09:26 AM, Heikki Linnakangas wrote: I'm just now dabbling back to this thread after skipping a lot of discussion, and I'm disappointed to see that this still seems to be running in circles on the same basic question: What exactly is the patch trying to accomplish. Bypassing the file system entirely in order to install an extension. As soon as I figure out how to, including C-coded extensions. I think the reason is that extensions provide some handy packaging of the functions etc, so that you can just do DROP EXTENSION foo to get rid of all of them. Also, pg_extension table keeps track of the currently installed version. Perhaps we need to step back and invent another concept that is totally separate from extensions, to provide The main feature of the extensions system is its ability to have a clean pg_restore process even when you use some extensions. That has been the only goal of the whole feature development. Let me stress that the most important value in that behavior is to be able to pg_restore using a newer version of the extension, the one that works with the target major version. When upgrading from 9.2 to 9.3 if you depend on keywords that now are reserved you need to install the newer version of the extension at pg_restore time. The main features I'm interested into beside a clean pg_restore are UPDATE scripts for extensions and dependency management, even if that still needs improvements. Those improvements will be relevant for both ways to make extensions available for your system. those features. Let's call them modules. A module is like an extension, in that all the objects in the module can be dropped with a simple DROP MODULE foo command. To create a module, you run CREATE MODULE foo AS SQL script to create the objects in the module. Not again the naming. A module is already documented as a shared object library (.so, .dll or .dylib) that PostgreSQL will LOAD for you. A patch has already been proposed to track which module is loaded in a session and offer that in a new system's view, pg_module. We can not use the name module for anything else, IMNSHO. just with the name. The concept was useful, but not something we want to call an extension, because the distinguishing feature of an extension is that it lives outside the database and is *not* included in pg_dump. The main goal here is not to have the extension live inside the database but rather to be able to bypass using the server's filesystem in order to be able to CREATE EXTENSION foo; and then to still have pg_restore do the right thing on its own. If you want to scratch the new catalogs part, then just say that it's expected to be really complex to pg_restore a database using extensions, back to exactly how it was before 9.1: create the new database, create the extensions your dump depends on in that new database, now pg_restore your backup manually filtering away the extensions' objects or ignoring the errors when pg_restore tries to duplicate functions you already installed in the previous step. No fun. Hannu Krosing ha...@krosing.net writes: Either MODULE or PACKAGE would be good name candidates. The name package is even worse than the module one because lots of people think they know exactly what is a package for having been using a closed source product that you might have heard of: they are trying to cope with our ability to implement new features on a yearly basis while not breaking anything we already have. Still, getting this functionality in seems more important than exact naming, though naming them right would be nice. Of course we want to do it right™. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add session_preload_libraries configuration parameter
Peter Eisentraut pete...@gmx.net writes: local_preload_libraries allows unprivileged users to preload whitelisted libraries. session_preload_libraries allows superusers to preload any library. It's hard to see how to consolidate those, at least without adding another setting that whitelists the libraries somehow, at which point you haven't really gained anything in terms of complexity. Good point. I don't know of any actual legitimate uses of local_preload_libraries. I recall that the plpgsql debugger was meant to use it, but doesn't anymore. So it's hard to judge what to do about this, without any actual use cases. Well there's my preprepare thing at https://github.com/dimitri/preprepare I don't think that the whitelisting is actually used in a way to allow for non superusers to load modules in the field, because the only way to do that with local_preload_libraries that I know of is to edit the postgresql.conf file and reload. alter role dim set local_preload_libraries = 'auto_explain'; ERROR: 55P02: parameter local_preload_libraries cannot be set after connection start That said, I think we could go with your current patch and revise the local_preload_libraries down the road in the 9.4 development cycle. Having both for a while is a good way to better understand if they are useful to have together. Going to mark your patch ready for commiter, thanks! Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Improvement of checkpoint IO scheduler for stable transaction responses
I create fsync v2 patch. There's not much time, so I try to focus fsync patch in
this commit festa as adviced by Heikki. And I'm sorry that it is not good that
diverging from main discussion in this commit festa... Of course, I continue to
try another improvement.
* Changes
- Add ckpt_flag in mdsync() etc with reference by Heikki's patch. It will be
more controllable mdsync() in checkpoint.
- Too long sleep in fsync phase is not good for checkpoint schedule. So I set
limited sleep time which is always less than 10 seconds(MAX_FSYNC_SLEEP).
I think that 10 seconds sleep time is a suitable value in various situations.
And I also considered limited sleep time by checkpoint progress,
however, I thought md.c should be simple and remain robust. So I have remained
simple.
- Maximum checkpoint_fsync_sleep_ratio in guc.c is changed 1 to 2. Because I
set limited sleep time 10 secounds. We can more flexibly change it and be more
safety.
And I considered abbreviation of parameters in my fsync patch.
* checkpoint_fsync_delay_threshold
In general, I think that it is suitable about 1 second in various
environments.
If we want to adjust sleep time in fsync phase, we can change
checkpoint_fsync_sleep_ratio.
* checkpoint_fsync_sleep_ratio
I don't want to omit this parameter, because it can only regulate sleep time
in fsync phase and checkpoint time.
* Benchmark Result(DBT-2)
| NOTPMAverage 90%tile Maximum
+
original_0.7 (baseline) | 3610.42 4.55610.9180 23.1326
fsync v1| 3685.51 4.036 9.2017 17.5594
fsync v2| 3748.80 3.562 8.1871 17.5101
I'm not sure about this result. Fsync v2 patch was too good. Of cource I didn't
do anything in executing benchmark.
Please see checkpoint_time.txt which is written detail checkpoint in each
checkpoint. Fsync v2 patch seems to be short in each checkpoint time.
* Benchmark Setting
[postgresql.conf]
archive_mode = on
archive_command = '/bin/cp %p /pgdata/pgarch/arc_dbt2/%f'
synchronous_commit = on
max_connections = 300
shared_buffers = 2458MB
work_mem = 1MB
fsync = on
wal_sync_method = fdatasync
full_page_writes = on
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.7
segsize=1GB(default)
[patched postgresql.conf (add)]
checkpointer_fsync_delay_ratio = 1
checkpointer_fsync_delay_threshold = 1000ms
[DBT-2 driver settings]
SESSION:250
WH:340
TPW:10
PRETEST_DURATION: 1800
TEST_DURATION: 1800
* Test Server
Server: HP Proliant DL360 G7
CPU:Xeon E5640 2.66GHz (1P/4C)
Memory: 18GB(PC3-10600R-9)
Disk: 146GB(15k)*4 RAID1+0
RAID controller: P410i/256MB
(Add) Set off energy efficient function in BIOS and OS.
Best regards,
--
Mitsumasa KONDO
NTT Open Sorce Software Center
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index fdf6625..2b223e9 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -143,14 +143,16 @@ static CheckpointerShmemStruct *CheckpointerShmem;
*/
int CheckPointTimeout = 300;
int CheckPointWarning = 30;
+int CheckPointerFsyncDelayThreshold = -1;
double CheckPointCompletionTarget = 0.5;
+double CheckPointerFsyncDelayRatio = 0.0;
/*
* Flags set by interrupt handlers for later service in the main loop.
*/
static volatile sig_atomic_t got_SIGHUP = false;
-static volatile sig_atomic_t checkpoint_requested = false;
-static volatile sig_atomic_t shutdown_requested = false;
+extern volatile sig_atomic_t checkpoint_requested = false;
+extern volatile sig_atomic_t shutdown_requested = false;
/*
* Private state
@@ -169,7 +171,6 @@ static pg_time_t last_xlog_switch_time;
static void CheckArchiveTimeout(void);
static bool IsCheckpointOnSchedule(double progress);
-static bool ImmediateCheckpointRequested(void);
static bool CompactCheckpointerRequestQueue(void);
static void UpdateSharedMemoryConfig(void);
@@ -643,7 +644,7 @@ CheckArchiveTimeout(void)
* this does not check the *current* checkpoint's IMMEDIATE flag, but whether
* there is one pending behind it.)
*/
-static bool
+extern bool
ImmediateCheckpointRequested(void)
{
if (checkpoint_requested)
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 8079226..3f02d0b 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -1828,7 +1828,7 @@ CheckPointBuffers(int flags)
BufferSync(flags);
CheckpointStats.ckpt_sync_t = GetCurrentTimestamp();
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_SYNC_START();
- smgrsync();
+ smgrsync(flags);
CheckpointStats.ckpt_sync_end_t = GetCurrentTimestamp();
TRACE_POSTGRESQL_BUFFER_CHECKPOINT_DONE();
}
diff --git a/src/backend/storage/smgr/md.c b/src/backend/storage/smgr/md.c
index e629181..d762511 100644
---
Re: [HACKERS] Review: extension template
On 06/10/2013 09:43 PM, Hannu Krosing wrote: On 07/08/2013 09:26 AM, Heikki Linnakangas wrote: The concept was useful, but not something we want to call an extension, because the distinguishing feature of an extension is that it lives outside the database and is *not* included in pg_dump. Either MODULE or PACKAGE would be good name candidates. Still, getting this functionality in seems more important than exact naming, though naming them right would be nice. Remember that we already have quite a lot of extensions. And PGXN. Are we really so wedded to the idea of extensions living outside of the database that we need to come up with something different and incompatible? Or do you envision modules or packages to be compatible with extensions? Just putting another label on it so we can still claim extensions are strictly external to the database? Sorry, I don't get the idea, there. From a users perspective, I want extensions, modules, or packages to be managed somehow. Including upgrades, migrations (i.e. dump restore) and removal. The approach of letting the distributors handle that packaging clearly has its limitations. What's so terribly wrong with Postgres itself providing better tools to manage those? Inventing yet another type of extension, module or package (compatible or not) doesn't help, but increases confusion even further. Or how do you explain to an author of an existing extension, whether or not he should convert his extension to a module (if you want those to be incompatible)? If it's the same thing, just with different loading mechanisms, please keep calling it the same: an extension. (And maintain compatibility between the different ways to load it.) I fully agree with the fundamental direction of Dimitri's patch. I think Postgres needs to better manage its extensions itself. Including dump and restore cycles. However, I think the implementation isn't optimal, yet. I pointed out a few usability issues and gave reasons why template is a misnomer (with the proposed implementation). Extension is not. (I still think template would be a good mental model. See my other thread... http://archives.postgresql.org/message-id/51d72c1d.7010...@bluegap.ch) Regards Markus Wanner -- 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] in-catalog Extension Scripts and Control parameters (templates?)
Jaime Casanova ja...@2ndquadrant.com writes: the name). I'm talking about get_ext_ver_list_from_catalog() which is a different function. Oh. I see it now. Sorry about that. It's blindly fixed in my git repo and I'm going to send an updated patch soon now™ which will include the fix. Thanks for insisting here… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Improving avg performance for numeric
2013/7/8 Hadi Moshayedi h...@moshayedi.net: I am attaching the updated the patch, which also fixes a bug which caused one of the regression tests failed. I'll subscribe this patch to the commitfest in the next hour. Can you please review the patch? sure, :) Pavel Thanks, -- Hadi -- 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] XLogInsert scaling, revisited
Ok, I've committed this patch now. Finally, phew! I think I've addressed all your comments about the comments. I moved some of the comments around: I split up the large one near the top of the file, moving its paragraphs closer to the code where they apply. Regarding your performance-related worries: you have good thoughts on how to improve things, but let's wait until we see some evidence that there is a problem, before any further optimizations. I fixed one bug related to aligning the WAL buffers. The patch assumes WAL buffers to be aligned at a full XLOG_BLCKSZ boundary, but did not enforce it. That was already happening on platforms with O_DIRECT, which is why I didn't notice that in testing, but it would've failed on others. I just remembered one detail that I'm not sure has been mentioned on the mailing list yet. Per the commit message: This has one user-visible change: switching to a new WAL segment with pg_switch_xlog() now fills the remaining unused portion of the segment with zeros. This potentially adds some overhead, but it has been a very common practice by DBA's to clear the tail of the segment with an external pg_clearxlogtail utility anyway, to make the WAL files compress better. With this patch, it's no longer necessary to do that. I simplified the handling of xlogInsertingAt per discussion, and added the memory barrier to GetXLogBuffer(). I ran again the pgbench tests I did earlier with the now-committed version of the patch (except for some comment changes). The results are here: http://hlinnaka.iki.fi/xloginsert-scaling/xloginsert-scale-26/ I tested three different workloads. with different numbers of slots, ranging from 1 to 1000. The tests were run on a 32-core machine, in a VM. As the baseline, I used a fresh checkout from master branch, with this one-line patch: http://www.postgresql.org/message-id/519a938a.1070...@vmware.com. That patch adjusts the spinlock delay loop, which happens to make a big difference on this box. We'll have to revisit and apply that patch separately, but I think that's the correct baseline to test this xloginsert scaling patch against. nobranch This is the pgbench -N workload. Throughput is mainly limited by flushing the WAL at commits. The patch makes no big difference here, which is good. The point of the test is to demonstrate that the patch doesn't make WAL flushing noticeably more expensive. nobranch-sync-commit-off Same as above, but with synchronous_commit=off. Here the patch somewhat. WAL insertion doesn't seem to be the biggest limiting factor in this test, but it's nice to see some benefit. xlogtest The workload in this test is a single INSERT statement that inserts a lot of rows: INSERT INTO foo:client_id SELECT 1 FROM generate_series(1,100) a, generate_series(1,100) b. Each client inserts to a separate table, to eliminate as much lock contention as possible, making the WAL insertion bottleneck as serious as possible (although I'm not sure how much difference that makes). This pretty much a best case scenario for this patch. This test shows a big gain from the patch, as it should. The peak performance goes from about 35 TPS to 100 TPS. With the patch, I suspect the test saturates the I/O subsystem at that point. I think it could go higher with better I/O hardware. All in all, I'm satisfied enough with this to commit. The default number of insertion slots, 8, seems to work fine for all the workloads on this box. We may have to adjust that or other details later, but what it needs now is more testing by people with different hardware. Thanks to everyone involved for the review and testing! And if you can, please review the patch as committed once more. - 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] XLogInsert scaling, revisited
On 08.07.2013 12:16, Heikki Linnakangas wrote: I just remembered one detail that I'm not sure has been mentioned on the mailing list yet. Per the commit message: This has one user-visible change: switching to a new WAL segment with pg_switch_xlog() now fills the remaining unused portion of the segment with zeros. This potentially adds some overhead, but it has been a very common practice by DBA's to clear the tail of the segment with an external pg_clearxlogtail utility anyway, to make the WAL files compress better. With this patch, it's no longer necessary to do that. Magnus just pointed out over IM that the above also applies to xlog-switches caused by archive_timeout, not just pg_switch_xlog(). IOW, all xlog-switch WAL records. - 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] XLogInsert scaling, revisited
On 27.06.2013 20:36, Andres Freund wrote: On 2013-06-26 18:52:30 +0300, Heikki Linnakangas wrote: There's this in the comment near the top of the file: Btw, I find the 'you' used in the comment somewhat irritating. Not badly so, but reading something like: * When you are about to write * out WAL, it is important to call WaitXLogInsertionsToFinish() *before* * acquiring WALWriteLock, to avoid deadlocks. Otherwise you might get stuck * waiting for an insertion to finish (or at least advance to next * uninitialized page), while you're holding WALWriteLock. just seems strange to me. If this directed at plugin authors, maybe, but it really isn't... Agreed, that was bad wording. Fixed. - 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] Review: extension template
On 07/08/2013 10:20 AM, Dimitri Fontaine wrote: Bypassing the file system entirely in order to install an extension. As soon as I figure out how to, including C-coded extensions. Do I understand correctly that you want to keep the extensions (or their templates) out of the dump and require the user to upload it via libpq prior to the restor; instead of him having to install them via .deb or .rpm? This would explain why you keep the CREATE TEMPLATE FOR EXTENSION as a separate step from CREATE EXTENSION. And why you, too, insist on wanting templates, and not just a way to create an extension via libpq. However, why don't you follow the template model more closely? Why should the user be unable to create a template, if there already exists an extension of the same name? That's an unneeded and disturbing limitation, IMO. My wish: Please drop the pg_depend link between template and extension and make the templates shared across databases. So I also have to install the template only once per cluster. Keep calling them templates, then. (However, mind that file-system extension templates are templates as well. In-line vs. out-of-line templates, if you want.) I think you could then safely allow an upgrade of an extension that has been created from an out-of-line template by an upgrade script that lives in-line. And vice-versa. Just as an example. It all gets nicer and cleaner, if the in-line thing better matches the out-of-line one, IMO. An extension should look and behave exactly the same, independent of what kind of template it has been created from. And as we obviously cannot add a pg_depend link to a file on the file system, we better don't do that for the in-line variant, either, to maintain the symmetry. The main feature of the extensions system is its ability to have a clean pg_restore process even when you use some extensions. That has been the only goal of the whole feature development. Great! Very much appreciated. Let me stress that the most important value in that behavior is to be able to pg_restore using a newer version of the extension, the one that works with the target major version. When upgrading from 9.2 to 9.3 if you depend on keywords that now are reserved you need to install the newer version of the extension at pg_restore time. The main features I'm interested into beside a clean pg_restore are UPDATE scripts for extensions and dependency management, even if that still needs improvements. Those improvements will be relevant for both ways to make extensions available for your system. +1 We can not use the name module for anything else, IMNSHO. Agreed. The main goal here is not to have the extension live inside the database but rather to be able to bypass using the server's filesystem in order to be able to CREATE EXTENSION foo; and then to still have pg_restore do the right thing on its own. Note that with the current, out-of-line approach, the *extension* already lives inside the database. It's just the *template*, that doesn't. (Modulo DSO, but the patch doesn't handle those either, yet. So we're still kind of excluding those.) Allowing for templates to live inside the database as well is a good thing, IMO. If you want to scratch the new catalogs part, then just say that it's expected to be really complex to pg_restore a database using extensions, back to exactly how it was before 9.1: create the new database, create the extensions your dump depends on in that new database, now pg_restore your backup manually filtering away the extensions' objects or ignoring the errors when pg_restore tries to duplicate functions you already installed in the previous step. No fun. Definitely not. Nobody wants to go back there. (And as Heikki pointed out, if you absolutely want to, you can even punish yourself that way.) Regards Markus Wanner -- 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] Review: extension template
Hi, Please find attached to this mail version 9 of the Extension Templates patch with fixes for the review up to now. Markus Wanner mar...@bluegap.ch writes: I still think that we shouldn't allow creating a template for an extension that is already installed, though. Do you have any suggestions for a better error message? If we go for the template model, I beg to differ. In that mind-set, you should be free to create (or delete) any kind of template without affecting pre-existing extensions. Then what happens at pg_restore time? the CREATE EXTENSION in the backup script will suddenly install the other extension's that happen to have the same name? I think erroring out is the only safe way here. However, in case we follow the ancestor-derivate or link model with the pg_depend connection, the error message seems fine. It's all about pg_restore really, but it's easy to forget about that and get started into other views of the world. I'll try to be better at not following those tracks and just hammer my answers with pg_restore now. In any case, I'm arguing this template renaming behavior (and the subsequent error) are the wrong thing to do, anyways. Because an extension being linked to a parent of a different name is weird and IMO not an acceptable state. Yes, you're right on spot here. So I've amended the patch to implement the following behavior (and have added a new regression test): # CREATE TEMPLATE FOR EXTENSION foo VERSION 'v' AS ''; # CREATE EXTENSION foo; # ALTER TEMPLATE FOR EXTENSION foo RENAME TO bar; ERROR: 55006: template for extension foo is in use DETAIL: extension foo already exists LOCATION: AlterExtensionTemplateRename, template.c:1040 STATEMENT: ALTER TEMPLATE FOR EXTENSION foo RENAME TO bar; I bet that's because people have different mental models in mind. But I probably stressed that point enough by now... FWIW, I do agree. But my understanding is that the community consensus is not going that way. Specifically, I request to either follow the template model more closely (accompanied by a separate patch to adjust binary, out-of-line templates) or follow the link model more closely. The current naming doesn't match any of the two, so renaming seems inevitable. I think we need to follow the link model more closely because that's the consensus, and I will fix all the remaning discrepancies in between the two models that we can find. Please continue showing them to me! src/backend/commands/event_trigger.c, definition of event_trigger_support: several unnecessary whitespaces added. These make it hard(er) than necessary to review the patch. Fixed in the attached version 9 of the patch. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support templates.v9.patch.gz 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
[HACKERS] LogSwitch
what is log switch and when it occur ?
Re: [HACKERS] XLogInsert scaling, revisited
On Mon, Jul 8, 2013 at 12:16 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Ok, I've committed this patch now. Finally, phew! Fantastic work! I simplified the handling of xlogInsertingAt per discussion, and added the memory barrier to GetXLogBuffer(). I ran again the pgbench tests I did earlier with the now-committed version of the patch (except for some comment changes). The results are here: I can't see a reason why a full memory barrier is needed at GetXLogBuffer, we just need to ensure that we read the content of the page after we check the end pointer from xlblocks. A read barrier is enough here unless there is some other undocumented interaction. I don't think it matters for performance, but it seems like good practice to have the barriers exactly matching the documentation. Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] Reduce maximum error in tuples estimation after vacuum.
On Wednesday, July 03, 2013 1:21 PM Kyotaro HORIGUCHI wrote: Hello, I could see the same output with your latest script, also I could reproduce the test if I run the test with individual sql statements. One of the main point for reproducing individual test was to keep autovacuum = off. I see. Autovacuum's nap time is 60 sconds for the default settings. Your operation might help it to snipe the window between the last massive delete and the next explict vacuum in store_result().. Anyway setting autovacuum to off should aid to make clean environment fot this issue. Now I can look into it further, I have still not gone through in detail about your new approach to calculate the reltuples, but I am wondering whether there can be anyway with which estimates can be improved with different calculation in vac_estimate_reltuples(). I'll explain this in other words alghough It might be repetitious. It is tough to decide how to modify there. Currently I decided to preserve vac_estimate_reltuples as possible as it is. For that objective, I picked up old_rel_tuples as intermediate variable for the aid to 'deceive' the function. This can be different form deciding to separate this estimation function from that for analyze. As I described before, vac_estimates_reltuples has a presumption that the tuple density in skipped pages is not so different from that in whole table before vacuuming. Since the density is calculated without using any hint about the skipped pages, and it cannot tell how much tuples aganst pg_class.reltuples is already dead, the value can be far different from the true one and cannot be verified. Given that we canot use pg_stat_user_tables.n_dead_tup, reading all pages can fix it but the penalty should be intolerable. Using FSM to know the used bytes in skipped pages (which is all visible by the definition) seems to give good estimations of the tuples in the skipped pages to some extent assuming the uniformity of tuple length. Of course strong deviation in length can deceive the algorithm. Does it make sense for you? I understood your patch's algorithm, but still I have doubt in my mind that if the next analyze can correct the estimates, Why would that be not sufficient. Please refer my last mail for analysis of same http://www.postgresql.org/message-id/000601ce77ad$7d3388e0$779a9aa0$@kapila@ huawei.com Performance Data -- I have checked few cases where FSM is not updated accurately, this patch seems to give much worse results than current code. Test with Patch 1. Test given by you where tuple density is non-uniform postgres=# drop table if exists t; DROP TABLE postgres=# create table t (a int, b int, c int, d int default 0, e int default 0 , f int default 0); CREATE TABLE postgres=# insert into t (select a, (random() * 10)::int from generate_serie s((select count(*) from t) + 1, 100) a); INSERT 0 100 postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7; UPDATE 69 postgres=# vacuum t; VACUUM postgres=# delete from t where a (select count(*) from t) * 0.99; DELETE 98 postgres=# vacuum t; VACUUM postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t ) as tuples, reltuples::float / (select count(*) from t) as ratio from pg_stat_ user_tables s, pg_class c where s.relname = 't' and c.relname = 't'; relpages | n_live_tup | reltuples | tuples | ratio --++---++-- 6370 | 13596 | 13596 | 10001 | 1.35946405359464 (1 row) 2. Test where tuple density is non-uniform and FSM updates before calculation in Vacuum are not accurate. I have created index on table to simulate this test postgres=# drop table if exists t; DROP TABLE postgres=# create table t (a int, b int, c int, d int default 0, e int default 0 , f int default 0); CREATE TABLE postgres=# create index on t(a); CREATE INDEX postgres=# insert into t (select a, (random() * 10)::int from generate_serie s((select count(*) from t) + 1, 100) a); INSERT 0 100 postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7; UPDATE 69 postgres=# vacuum t; VACUUM postgres=# delete from t where a (select count(*) from t) * 0.99; DELETE 98 postgres=# vacuum t; VACUUM postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t ) as tuples, reltuples::float / (select count(*) from t) as ratio from pg_stat_ user_tables s, pg_class c where s.relname = 't' and c.relname = 't'; relpages | n_live_tup | reltuples | tuples | ratio --++--++-- 6370 |1001327 | 1.00133e+006 | 10001 | 100.122687731227 (1 row) Now this result in tuple estimation worse than current code. I think we need to have more tests to show that new calculation is better in all cases than
Re: [HACKERS] XLogInsert scaling, revisited
On 08.07.2013 13:21, Ants Aasma wrote: On Mon, Jul 8, 2013 at 12:16 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Ok, I've committed this patch now. Finally, phew! Fantastic work! I simplified the handling of xlogInsertingAt per discussion, and added the memory barrier to GetXLogBuffer(). I ran again the pgbench tests I did earlier with the now-committed version of the patch (except for some comment changes). The results are here: I can't see a reason why a full memory barrier is needed at GetXLogBuffer, we just need to ensure that we read the content of the page after we check the end pointer from xlblocks. A read barrier is enough here unless there is some other undocumented interaction. GetXLogBuffer() doesn't read the content of the page - it writes to it (or rather, the caller of GetXLogBarrier() does). The barrier is needed between reading xlblocks (to check that the buffer contains the right page), and writing the WAL data to the buffer. README.barrier says that you need a full memory barrier to separate a load and a store. - 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] XLogInsert scaling, revisited
On 2013-07-08 10:45:41 +0300, Heikki Linnakangas wrote: On 01.07.2013 16:40, Andres Freund wrote: On 2013-06-26 18:52:30 +0300, Heikki Linnakangas wrote: * Could you document the way slots prevent checkpoints from occurring when XLogInsert rechecks for full page writes? I think it's correct - but not very obvious on a glance. There's this in the comment near the top of the file: * To update RedoRecPtr or fullPageWrites, one has to make sure that all * subsequent inserters see the new value. This is done by reserving all the * insertion slots before changing the value. XLogInsert reads RedoRecPtr and * fullPageWrites after grabbing a slot, so by holding all the slots * simultaneously, you can ensure that all subsequent inserts see the new * value. Those fields change very seldom, so we prefer to be fast and * non-contended when they need to be read, and slow when they're changed. Does that explain it well enough? XLogInsert holds onto a slot while it rechecks for full page writes. I am a bit worried about that logic. We're basically reverting to the old logic whe xlog writing is an exlusive affair. We will have to wait for all the other queued inserters before we're finished. I am afraid that that will show up latencywise. A single stall of the xlog-insertion pipeline at a checkpoint is hardly going to be a problem. I wish PostgreSQL was real-time enough for that to matter, but I think we're very very far from that. Well, the stall won't necessarily be that short. There might be several backends piling on every insertion slot and waiting - and thus put to sleep by the kerenl. I am pretty sure it's easy enough to get stalls in the second range that way. Sure, there are lots of reasons we don't have all that reliable response times, but IME the amount of response time jitter is one of the bigger pain points of postgres. And this feature has a good chance of reducing that pain noticeably... 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] Review: Patch to compute Max LSN of Data Pages
On Friday, July 05, 2013 6:48 PM Hari Babu wrote: On Thursday, July 04, 2013 11:19 PM Robert Haas wrote: The patch is updated with the following changes. 1.If the input data is data directory, all the errors occurred are displayed at once instead of one error at a time. 2.Fixed the problem of replacing result of the previous file or directory result with new one. 3.Update the docs. Thanks for the review, please find the updated patch attached in the mail. Regards, Hari babu. pg_computemaxlsn_v9.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] Review: Patch to compute Max LSN of Data Pages
On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? People falsely thinking that it can be used for this is the primary reason for me objecting the patch... 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] [9.3 bug fix] backends emit hundreds of messages when statistics file is inaccessible
Hello, I've found a bug in PostgreSQL 9.3 beta 2 which is related to statistics collection which emits excesssive amount of error messages. Please find attached the patch to fix this. I confirmed this problem occurs in PostgreSQL 9.2, too. Could you backport this? [Problem] If the directory specified by stats_temp_directory becomes inaccessible to postgres, enormous amount of WARNING messages are output. For example, when I set wrong permission on the directory by mistake while the database server is running and then started psql, more than 600 messages were emitted in a few seconds like this: WARNING: could not open statistics file /work/maumau/stats_tmp/pgstat.stat: Permission denied LOG: could not open temporary statistics file /work/maumau/stats_tmp/pgstat.tmp: Permission denied WARNING: could not open statistics file /work/maumau/stats_tmp/pgstat.stat: Permission denied WARNING: could not open statistics file /work/maumau/stats_tmp/pgstat.stat: Permission denied WARNING: could not open statistics file /work/maumau/stats_tmp/pgstat.stat: Permission denied ... Even when there's no client, these messages are output regularly. I guess this is because autovacuum runs. [Cause] In backend_read_statsfile(), the backend loops PGSTAT_POLL_LOOP_COUNT (=1000) times trying to open the statistics file. The backend emits a WARNING message if it fails to open the file in every iteration of the loop. [How to fix] Emit the message upon the first failed attempt. For some diagnostics purposes, emit the message with DEBUG2 level on the subsequent attempts. DEBUG2 is arbitrary. Regards MauMau reduce_statsfile_error.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] Review: Patch to compute Max LSN of Data Pages
On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? No, you are not missing anything. It can be only used to find max LSN in database which can avoid further corruption People falsely thinking that it can be used for this is the primary reason for me objecting the patch... With Regards, Amit Kapila. -- 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] Review: Patch to compute Max LSN of Data Pages
On 2013-07-08 17:10:43 +0530, Amit Kapila wrote: On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? No, you are not missing anything. It can be only used to find max LSN in database which can avoid further corruption Why is the patch submitted documenting it as a use-case then? I find it rather scary if the *patch authors* document a known unsafe use case as one of the known use-cases. 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] Review: Patch to compute Max LSN of Data Pages
On Monday, July 08, 2013 5:16 PM Andres Freund wrote: On 2013-07-08 17:10:43 +0530, Amit Kapila wrote: On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? No, you are not missing anything. It can be only used to find max LSN in database which can avoid further corruption Why is the patch submitted documenting it as a use-case then? I find it rather scary if the *patch authors* document a known unsafe use case as one of the known use-cases. I got the problem which can occur with the specified use case. Removed the wrong use case specified above. Thanks for the review, please find the updated patch attached in the mail. Regards, Hari babu. pg_computemaxlsn_v10.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
[HACKERS] psql tab completion for updatable foreign tables
Recently i got annoyed that psql doesn't tab complete to updatable foreign tables. Attached is a patch to address this. I'm using the new pg_relation_is_updatable() function to accomplish this. The function could also be used for the trigger based stuff in the query, but i haven't touched this part of the query. The patch ist against HEAD, but maybe it's worth to apply this to REL9_3_STABLE, too, but not sure what our policy is at this state... -- Thanks Bernd psql_tab_complete_updatable_fdw.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] XLogInsert scaling, revisited
On Mon, Jul 8, 2013 at 1:38 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: GetXLogBuffer() doesn't read the content of the page - it writes to it (or rather, the caller of GetXLogBarrier() does). The barrier is needed between reading xlblocks (to check that the buffer contains the right page), and writing the WAL data to the buffer. README.barrier says that you need a full memory barrier to separate a load and a store. Indeed you are right. My bad. I somehow thought that every location in the WAL buffer is written once while it's actually done twice due to AdvanceXLInsertBuffer() zeroing the page out. Now thinking about it, if that memset or the full memory barrier in GetXLogBuffer() ever prove to be a significant overhead, the initialization could be optimized to avoid zeroing the page. AdvanceXLInsertBuffer() would only write the header fields, CopyXLogRecordToWAL() needs to take care to write out zeroes for alignment padding and xlog switches and always write out xlp_rem_len and XLP_FIRST_IS_CONTRECORD bit of xlp_info. xlp_info needs to be split into two 8bit variables so XLP_FIRST_IS_CONTRECORD and XLP_LONG_HEADER/XLP_BKP_REMOVABLE can be written into disjoint memory locations. This way all memory locations in xlog buffer page are stored exactly once and there is no data race between writes making it possible to omit the barrier from GetXLogBuffer. WaitXLogInsertionsToFinish() takes care of the memory barrier for XlogWrite(). Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] [9.3 bug fix] backends emit hundreds of messages when statistics file is inaccessible
MauMau maumau...@gmail.com writes: I've found a bug in PostgreSQL 9.3 beta 2 which is related to statistics collection which emits excesssive amount of error messages. Please find attached the patch to fix this. I confirmed this problem occurs in PostgreSQL 9.2, too. Could you backport this? It's not apparent that this is a good idea, let alone a bug fix. If the directory specified by stats_temp_directory becomes inaccessible to postgres, enormous amount of WARNING messages are output. Well, yeah, because all of that functionality just broke. Not warning about it doesn't seem like a good idea. AFAICT, your patch would result in the situation not being remarked on anywhere except in the postmaster log. 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] Review: Patch to compute Max LSN of Data Pages
On Monday, July 08, 2013 5:16 PM Andres Freund wrote: On 2013-07-08 17:10:43 +0530, Amit Kapila wrote: On Monday, July 08, 2013 4:26 PM Andres Freund wrote: On 2013-07-08 16:17:54 +0530, Hari Babu wrote: +This utility can also be used to decide whether backup is required or not when the data page +in old-master precedes the last applied LSN in old-standby (i.e., new-master) at the +moment of the failover. + /para + /refsect1 I don't think this is safe in any interesting set of cases. Am I missing something? No, you are not missing anything. It can be only used to find max LSN in database which can avoid further corruption Why is the patch submitted documenting it as a use-case then? This is my mistake, I was not able to catch. I am really sorry for it and in future will make sure such mistake doesn't happen again I find it rather scary if the *patch authors* document a known unsafe use case as one of the known use-cases. With Regards, Amit Kapila. -- 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] sepgsql and materialized views
Tom Lane t...@sss.pgh.pa.us wrote: Noah Misch n...@leadboat.com writes: On Fri, Feb 08, 2013 at 02:51:40PM +0100, Kohei KaiGai wrote: I'll have a discussion about new materialized_view object class on selinux list soon, then I'll submit a patch towards contrib/sepgsql according to the consensus here. Has this progressed? Should we consider this a 9.3 release blocker? sepgsql already has a red box warning about its limitations, so adding the limitation that materialized views are unrestricted wouldn't be out of the question. Definitely -1 for considering it a release blocker. If KaiGai-san can come up with a fix before we otherwise would release 9.3, that's great, but there's no way that sepgsql has a large enough user community to justify letting it determine the release schedule. Agreed. I posted (many months ago) a proposed version which treated them as being subject to the same security labels as tables, and another which created new security lables for materialized views. I'm not aware of any third option, but I sure don't feel like I'm in a position to determine which is better (or whether someone has a third idea), and I don't think we can hold up the PostgreSQL release waiting for the security community to choose. -- Kevin Grittner 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] psql tab completion for updatable foreign tables
On 8 July 2013 12:46, Bernd Helmle maili...@oopsware.de wrote: Recently i got annoyed that psql doesn't tab complete to updatable foreign tables. Attached is a patch to address this. I'm using the new pg_relation_is_updatable() function to accomplish this. The function could also be used for the trigger based stuff in the query, but i haven't touched this part of the query. The patch ist against HEAD, but maybe it's worth to apply this to REL9_3_STABLE, too, but not sure what our policy is at this state... +1 A couple of points though: * pg_relation_is_updatable is only available in 9.3, whereas psql may connect to older servers, so it needs to guard against that. * If we're doing this, I think we should do it for auto-updatable views at the same time. There was concern that pg_relation_is_updatable() would end up opening every relation in the database, hammering performance. I now realise that these tab-complete queries have a limit (1000 by default) so I don't think this is such an issue. I tested it by creating 10,000 randomly named auto-updatable views on top of a table, and didn't see any performance problems. Here's an updated patch based on the above points. Regards, Dean tab_complete.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] Millisecond-precision connect_timeout for libpq
On 5 July 2013 23:47, ivan babrou ibob...@gmail.com wrote: On 5 July 2013 23:26, Tom Lane t...@sss.pgh.pa.us wrote: ivan babrou ibob...@gmail.com writes: If you can figure out that postgresql is overloaded then you may decide what to do faster. In our app we have very strict limit for connect time to mysql, redis and other services, but postgresql has minimum of 2 seconds. When processing time for request is under 100ms on average sub-second timeouts matter. If you are issuing a fresh connection for each sub-100ms query, you're doing it wrong anyway ... regards, tom lane In php you cannot persist connection between requests without worrying about transaction state. We don't use postgresql for every sub-100ms query because it can block the whole request for 2 seconds. Usually it takes 1.5ms to connect, btw. Can you tell me why having ability to specify more accurate connect timeout is a bad idea? -- Regards, Ian Babrou http://bobrik.name http://twitter.com/ibobrik skype:i.babrou Nobody answered my question yet. -- Regards, Ian Babrou http://bobrik.name http://twitter.com/ibobrik skype:i.babrou -- 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] Millisecond-precision connect_timeout for libpq
On Jul 8, 2013, at 7:44 AM, ivan babrou ibob...@gmail.com wrote: Can you tell me why having ability to specify more accurate connect timeout is a bad idea? Nobody answered my question yet. From an earlier post by Tom: What exactly is the use case for that? It seems like extra complication for something with little if any real-world usefulness. So the answer is: extra complication. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [9.4 CF 1] Week 3 report
All, Normally the CommitFest would be ending in one week. Our current status for the commitfest is: Needs Review: 24 Waiting on Author: 17, Ready for Committer: 13, Committed: 35, Returned with Feedback: 18, Rejected: 2. At current rates, CF1 will not conclude for four weeks. Of the patches needing review, one has not had any review at all, or a reviewer assigned: * Performance Improvement by reducing WAL for Update Operation Please someone pick this up! Also, two other notes for patch authors: A) Please do not attempt to submit patches to the June Commitfest -- new patches go in the September commitfest. B) Please do not submit patches without posting them to the -hackers list. -- 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] [PERFORM] In progress INSERT wrecks plans on table
On 06/23/2013 09:43 PM, Abhijit Menon-Sen wrote: (Cc: to pgsql-performance dropped, pgsql-hackers added.) At 2013-05-06 09:14:01 +0100, si...@2ndquadrant.com wrote: New version of patch attached which fixes a few bugs. I read the patch, but only skimmed the earlier discussion about it. In isolation, I can say that the patch applies cleanly and looks sensible for what it does (i.e., cache pgprocno to speed up repeated calls to TransactionIdIsInProgress(somexid)). In that sense, it's ready for committer, but I don't know if there's a better/more complete/etc. way to address the original problem. Has this patch had performance testing? Because of the list crossover I don't have any information on that. -- 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] Millisecond-precision connect_timeout for libpq
Le lundi 8 juillet 2013 18:40:29, David E. Wheeler a écrit : On Jul 8, 2013, at 7:44 AM, ivan babrou ibob...@gmail.com wrote: Can you tell me why having ability to specify more accurate connect timeout is a bad idea? Nobody answered my question yet. From an earlier post by Tom: What exactly is the use case for that? It seems like extra complication for something with little if any real-world usefulness. So the answer is: extra complication. for something that must go through a pooler anyway. You can have a look at pgbouncer: query_wait_timeout parameter for example. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Improving avg performance for numeric
On 07/07/2013 09:14 PM, Hadi Moshayedi wrote: I am attaching the updated the patch, which also fixes a bug which caused one of the regression tests failed. I'll subscribe this patch to the commitfest in the next hour. Can you please review the patch? I'm afraid that, since this patch wasn't included anywhere near the first week of the CommitFest, I can't possibly include it in the June commitfest now. Accordingly, I have moved it to the September commitfest. Hopefully someone can look at it before then. Sorry for missing this in my patch sweep at the beginning of the CF. Searching email for patches is, at best, inexact. -- 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] pg_filedump 9.3: checksums (and a few other fixes)
On Fri, 2013-07-05 at 22:43 -0700, Jeff Davis wrote: On Sat, 2013-07-06 at 10:30 +0900, Satoshi Nagayasu wrote: Hi, It looks fine, but I have one question here. When I run pg_filedump with -k against a database cluster which does not support checksums, pg_filedump produced checksum error as following. Is this expected or acceptable? Thank you for taking a look. That is expected, because there is not a good way to determine whether the file was created with checksums or not. So, we rely on the user to supply (or not) the -k flag. I see this patch is still waiting on author in the CF. Is there something else needed from me, or should we move this to ready for committer? Regards, Jeff Davis -- 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] Improving avg performance for numeric
Hello 2013/7/8 Josh Berkus j...@agliodbs.com: On 07/07/2013 09:14 PM, Hadi Moshayedi wrote: I am attaching the updated the patch, which also fixes a bug which caused one of the regression tests failed. I'll subscribe this patch to the commitfest in the next hour. Can you please review the patch? I'm afraid that, since this patch wasn't included anywhere near the first week of the CommitFest, I can't possibly include it in the June commitfest now. Accordingly, I have moved it to the September commitfest. Hopefully someone can look at it before then. Sorry for missing this in my patch sweep at the beginning of the CF. Searching email for patches is, at best, inexact. sure, it should be in September CF. It is relative simple patch without global impacts. But I like it, it increase speed for sum(numeric) about 25% and avg(numeric) about 50% Regards Pavel -- 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] pg_filedump 9.3: checksums (and a few other fixes)
On Mon, Jul 8, 2013 at 10:28 AM, Jeff Davis pg...@j-davis.com wrote: I see this patch is still waiting on author in the CF. Is there something else needed from me, or should we move this to ready for committer? Well, obviously someone still needs to think through the handling of the infoMask bits. Alvaro? -- 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] Improving avg performance for numeric
Pavel, sure, it should be in September CF. It is relative simple patch without global impacts. But I like it, it increase speed for sum(numeric) about 25% and avg(numeric) about 50% Do you think you could give this a review after CF1 ends, but before September? I hate to make Hadi wait just because I didn't see his patch. -- 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] Improving avg performance for numeric
2013/7/8 Josh Berkus j...@agliodbs.com: Pavel, sure, it should be in September CF. It is relative simple patch without global impacts. But I like it, it increase speed for sum(numeric) about 25% and avg(numeric) about 50% Do you think you could give this a review after CF1 ends, but before September? I hate to make Hadi wait just because I didn't see his patch. yes, I can. Regards Pavel -- 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] preserving forensic information when we freeze
On 07/03/2013 11:59 AM, Robert Haas wrote: Yeah. I think the system columns that we have today are pretty much crap. I wonder if we couldn't throw them out and replace them with some kind of functions that you can pass a row to. That would allow us to expose a lot more detail without adding a bajillion hidden columns, and for a bonus we'd save substantially on catalog bloat. Where are we on this patch? Should I mark it Returned and you'll work on it for September? -- 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] patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap [Review]
On 06/26/2013 07:05 AM, Jamie Martin wrote: FYI I submitted a slightly modified patch since Amit's measurements that is slightly faster. Yes. My perspective is that this is a worthwhile optimization for a minority, but fairly well-known, use case, provided that it doesn't negatively impact any other, more common use case. Potential cases where I can see negative impact are: A) normal table with a few, mostly non-null columns (recent pgbench testing seems to have validated no measurable impact). B) table with many (400+) mostly non-null columns C) table with many (400+) mostly null columns, where column #390 was null and gets updated to a not null value I don't *think* that Jamie's performance tests have really addressed the above cases. However, do people agree that if performance on the patch passes for all of A, B and C, then it's OK to apply? -- 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] Redesigning checkpoint_segments
On 07/03/2013 11:28 AM, Peter Eisentraut wrote: On 6/6/13 4:09 PM, Heikki Linnakangas wrote: I don't understand what this patch, by itself, will accomplish in terms of the originally stated goals of making checkpoint_segments easier to tune, and controlling disk space used. To some degree, it makes both of these things worse, because you can no longer use checkpoint_segments to control the disk space. Instead, it is replaced by magic. What sort of behavior are you expecting to come out of this? In testing, I didn't see much of a difference. Although I'd expect that this would actually preallocate fewer segments than the old formula. Since I haven't seen a reply to Peter's comments from Heikki, I'm marking this patch returned with feedback. I know, it's a very busy CF, and I'm sure that you just couldn't get back to this one. We'll address it in September? -- 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] Add visibility map information to pg_freespace.
On 07/08/2013 12:59 AM, Kyotaro HORIGUCHI wrote: I'll come again with the first implementation of it. And as for pg_freespacemap, I'll keep the current direction - adding column to present output records format of pg_freespace(). And documentation, if possible. Do you think you'll be fixing these things in the next couple days? Otherwise, I would like to mark this returned with feedback, and you can submit an updated patch to the next CommitFest. -- 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] [PATCH] pgbench --throttle (submission 7 - with lag measurement)
On 06/29/2013 04:11 PM, Greg Smith wrote: I need to catch up with revisions done to this feature since I started instrumenting my copy more heavily. I hope I can get this ready for commit by Monday. I've certainly beaten on the feature for long enough now. Greg, any progress? Haven't seen an update on this in 10 days. -- 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] pg_filedump 9.3: checksums (and a few other fixes)
Peter Geoghegan escribió: On Mon, Jul 8, 2013 at 10:28 AM, Jeff Davis pg...@j-davis.com wrote: I see this patch is still waiting on author in the CF. Is there something else needed from me, or should we move this to ready for committer? Well, obviously someone still needs to think through the handling of the infoMask bits. Well, Tom opined in http://www.postgresql.org/message-id/23249.1370878...@sss.pgh.pa.us that the current patch is okay. I have a mild opinion that it should instead print only SHR_LOCK when both bits are set, and one of the others when only one of them is set. But I don't have a strong opinion about this, and since Tom disagrees with me, feel free to exercise your own (Jeff's) judgement. Tom's the only available committer in this case anyway. [Actually, pgfoundry.org says it's a zero-people team in the pgfiledump project right now, but I'm hoping that's just a temporary glitch.] -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.4] row level security
With the current HEAD and v3 patch I'm seeing the following error with make check. -- == creating temporary installation== == initializing database system == pg_regress: initdb failed Examine /usr/local/src/postgres.patched.v3/src/test/regress/log/initdb.log for the reason. Command was: /usr/local/src/postgres.patched.v3/src/test/regress/./tmp_check/install//usr/local/pgsql/bin/initdb -D /usr/local/src/postgres.patched.v3/src/test/regress/./tmp_check/data -L /usr/local/src/postgres.patched.v3/src/test/regress/./tmp_check/install//usr/local/pgsql/share --noclean --nosync /usr/local/src/postgres.patched.v3/src/test/regress/log/initdb.log 21 make[1]: *** [check] Error 2 make[1]: Leaving directory `/usr/local/src/postgres.patched.v3/src/test/regress' make: *** [check] Error 2 __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com*
Re: [HACKERS] [PATCH] Add transforms feature
Peter, On 07/07/2013 12:06 PM, Peter Eisentraut wrote: Good point. My original patch allowed func(sql_type) - internal, but I took that out because people had security concerns. I'd be OK with restricting transform creation to superusers in the first cut. Have we added the ability of non-superusers to create extensions yet? Until we have that, there's no point in allowing them to load transforms. Once we have that, we'll need to let them do it. We need the dependencies, because otherwise dropping a transform would break or silently alter the behavior of functions that depend on it. That sounds like my worst nightmare, thinking of some applications that would be affected by that. But your point is a good one. I think this could be addressed by prohibiting the creation of a transform that affects functions that already exist. However I don't see this prohibition of create transform if there is already such function. You are not planning to address this issue? I had planned to implement that, but talking to some people most didn't think it was useful or desirable. It's still open for debate. I don't think it's desirable. It would be hard to do, and at some level we need to make a presumption of competence on the part of the DBA. We should put a warning in the docs, though. (b) we can expect maybe a dozen to 18 of them in core based on the data types there, and I hate to clutter up /contrib, and Well, that's a matter of opinion. I'd be more happy with 250 contribs all on the same level versus a bunch of subdirectories structured based on personal preferences. But hey, we disagreed on config.sgml for similar reasons, IIRC. ;-) Yeah, I'd like to see some other opinions on this. (c) I'd like to do a future feature which supports install all transforms functionality, which would be helped by having them in their own directory. Installing all transforms by itself is not a sensible operation, because you only want the transforms for the types and languages that you actually use or have previously selected for installation. Give me some credit. I'm talking about a script for install all transforms for which the dependancies are already installed. That's certainly entirely doable, and would be made easier by putting the transforms in their own directory or otherwise flagging them to identify them as transforms. -- 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] pg_filedump 9.3: checksums (and a few other fixes)
On Mon, Jul 8, 2013 at 11:52 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Well, Tom opined in http://www.postgresql.org/message-id/23249.1370878...@sss.pgh.pa.us that the current patch is okay. I have a mild opinion that it should instead print only SHR_LOCK when both bits are set, and one of the others when only one of them is set. But I don't have a strong opinion about this, and since Tom disagrees with me, feel free to exercise your own (Jeff's) judgement. I'm inclined to agree with you here, but I suppose it isn't all that important. -- 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] Changing recovery.conf parameters into GUCs
On 07/05/2013 10:09 PM, Michael Paquier wrote: Yeah, it would be good to revive this thread now, which is the beginning of the development cycle. As of now, just to recall everybody, an agreement on this patch still needs to be found... Simon is concerned with backward compatibility. Greg presented a nice spec some time ago (Robert and I liked it) which would break backward compatibility but allow to begin with a fresh infrastructure. As folks know, I favor Smith's approach. However, as far as I can find, GSmith never posted a patch for his version. -- 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: [Review] Re: [HACKERS] minor patch submission: CREATE CAST ... AS EXPLICIT
On 06/24/2013 06:55 AM, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: What about simply not using a keyword at that location at all? Something like the attached hack? Generally speaking, I agree with Robert's objection. The patch in itself adds only one unnecessary keyword, which probably wouldn't be noticeable, but the argument for it implies that we should be willing to add a lot more equally-unnecessary keywords, which I'm not. gram.o is already about 10% of the entire postgres executable, which probably goes far towards explaining why its inner loop always shows up high in profiling: cache misses are routine. And the size of those tables is at least linear in the number of keywords --- perhaps worse than linear, I'm not sure. Adding a bunch of keywords *will* cost us in performance. I'm not willing to pay that cost for something that adds neither features nor spec compliance. Where are we with this patch? Fabien, are you going to submit an updated version which addresses the objections, or should I mark it Returned With Feedback? -- 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] Reduce maximum error in tuples estimation after vacuum.
On 07/03/2013 12:51 AM, Kyotaro HORIGUCHI wrote: It is tough to decide how to modify there. Currently I decided to preserve vac_estimate_reltuples as possible as it is. For that objective, I picked up old_rel_tuples as intermediate variable for the aid to 'deceive' the function. This can be different form deciding to separate this estimation function from that for analyze. Kyotaro, do you think you'll be revising this patch in the next 2 days, or should I bounce it, and you can resubmit it for the next commitfest? -- 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] Review: extension template
Hello Dimitri, On 07/08/2013 11:49 AM, Dimitri Fontaine wrote: Please find attached to this mail version 9 of the Extension Templates patch with fixes for the review up to now. Thanks, cool. Markus Wanner mar...@bluegap.ch writes: I still think that we shouldn't allow creating a template for an extension that is already installed, though. Do you have any suggestions for a better error message? If we go for the template model, I beg to differ. In that mind-set, you should be free to create (or delete) any kind of template without affecting pre-existing extensions. Then what happens at pg_restore time? the CREATE EXTENSION in the backup script will suddenly install the other extension's that happen to have the same name? I think erroring out is the only safe way here. Extensions are commonly identified by name (installed ones as well as available ones, i.e. templates). Thus I think if a user renames a template, he might have good reasons to do so. He likely *wants* it to be a template for a different extension. Likewise when (re-)creating a template with the very same name of a pre-existing, installed extension. Maybe the user just wanted to make a backup of the template prior to modifying it. If he then gives the new template the same name as the old one had, it very likely is similar, compatible or otherwise intended to replace the former one. The file-system templates work exactly that way (modulo DSOs). If you create an extension, then modify (or rename and re-create under the same name) the template on disk, then dump and restore, you end up with the new version of it. That's how it worked so far. It's simple to understand and use. It's all about pg_restore really, but it's easy to forget about that and get started into other views of the world. I'll try to be better at not following those tracks and just hammer my answers with pg_restore now. That's unlikely to be of much help. It's not like pg_restore would stop to work. It would just work differently. More like the file-system templates. More like the users already knows and (likely) expects it. And more like the template model that you advertise. Or how do you think would pg_restore fail, if you followed the mental model of the template? In any case, I'm arguing this template renaming behavior (and the subsequent error) are the wrong thing to do, anyways. Because an extension being linked to a parent of a different name is weird and IMO not an acceptable state. Yes, you're right on spot here. So I've amended the patch to implement the following behavior (and have added a new regression test): # CREATE TEMPLATE FOR EXTENSION foo VERSION 'v' AS ''; # CREATE EXTENSION foo; # ALTER TEMPLATE FOR EXTENSION foo RENAME TO bar; ERROR: 55006: template for extension foo is in use DETAIL: extension foo already exists LOCATION: AlterExtensionTemplateRename, template.c:1040 STATEMENT: ALTER TEMPLATE FOR EXTENSION foo RENAME TO bar; Okay, good, this prevents the strange state. However, this also means you restrict the user even further... How can he save a copy of an existing template, before (re-)creating it with CREATE TEMPLATE FOR EXTENSION? On the file system, a simple cp or mv is sufficient before (re)installing the package from your distribution, for example. I bet that's because people have different mental models in mind. But I probably stressed that point enough by now... FWIW, I do agree. Good. Why do you continue to propose the link model? But my understanding is that the community consensus is not going that way. What way? And what community consensus? Re-reading some of the past discussions, I didn't find anybody voting for a dependency between the template and the created extension. And at least Tom pretty clearly had the template model in mind, when he wrote [1]: We don't want it to look like manipulating a template has anything to do with altering an extension of the same name (which might or might not even be installed). or [2]: But conflating this functionality [i.e. extension templates] with installed extensions is just going to create headaches. The closest I found was Robert Haas mentioning [3], that [he doesn't] see a problem having more than one kind of extensions. However, please mind the context. He doesn't really sound enthusiastic, either. I'm puzzled about some of your words in that thread. In the very message Robert responded to, you wrote [4]: Having more than one way to ship an extension is good, having two different animals with two different incompatible behaviors named the same thing is bad. With the link-model, you are now proposing to create exactly that. Two different kinds of extensions that are not compatible with each other. One that is independent and one that depends on the template it got created from. Specifically, I request to either follow the template model more closely (accompanied by a separate patch to adjust binary, out-of-line
Re: [HACKERS] Millisecond-precision connect_timeout for libpq
On 8 July 2013 20:40, David E. Wheeler da...@justatheory.com wrote: On Jul 8, 2013, at 7:44 AM, ivan babrou ibob...@gmail.com wrote: Can you tell me why having ability to specify more accurate connect timeout is a bad idea? Nobody answered my question yet. From an earlier post by Tom: What exactly is the use case for that? It seems like extra complication for something with little if any real-world usefulness. So the answer is: extra complication. Best, David I don't see any extra complication in backwards-compatible patch that removes more lines that adds. Can you tell me, what exactly is extra complicated? About pooling connections: we have 150 applications servers and 10 postgresql servers. Each app connects to each server - 150 connections per server if I run pooler on each application server. That's more than default setting and now we usually have not more than 10 connections per server. What would happen if we have 300 app servers? I thought connections consume some memory. Running pooler not on every app server gives no advantage — I still may get network blackhole and 2 seconds delay. Moreover, now I can guess that postgresql is overloaded if it does not accept connections, with pooler I can simply blow up disks with heavy io. Seriously, I don't get why running 150 poolers is easier. And my problem is still here: server (pooler is this case) is down — 2 seconds delay. 2000% slower. Where am I wrong? -- Regards, Ian Babrou http://bobrik.name http://twitter.com/ibobrik skype:i.babrou -- 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] Bugfix and new feature for PGXS
On 07/04/2013 06:18 AM, Andrew Dunstan wrote: On 07/04/2013 09:14 AM, Cédric Villemain wrote: ah yes, good catch, I though .control file were unique per contrib, but there aren't. It's already been fixed. Does it look like this patch will get into committable shape in the next couple of days? -- 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] Bugfix and new feature for PGXS
On 07/08/2013 03:40 PM, Josh Berkus wrote: On 07/04/2013 06:18 AM, Andrew Dunstan wrote: On 07/04/2013 09:14 AM, Cédric Villemain wrote: ah yes, good catch, I though .control file were unique per contrib, but there aren't. It's already been fixed. Does it look like this patch will get into committable shape in the next couple of days? I think everything has been committed - as I think the CF app shows. The only thing left in this srea from Cédric is the insallation of headers, which Peter is down to review and is in Waiting on Author status. We are owed a docco patch though. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugfix and new feature for PGXS
I think everything has been committed - as I think the CF app shows. The only thing left in this srea from Cédric is the insallation of headers, which Peter is down to review and is in Waiting on Author status. Yeah, that's the one I'm asking about. is that going to get done in the next few days, or does it bounce? -- 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] How to implement Gin method?
On Mon, Jul 08, 2013 at 03:21:09PM +0900, kenji uno wrote:
Hi.
Ok, ok thanks.
My problem is to shorten time of searching full text stored in text field.
Ok, your explanation of your problem really helps, thanks.
However the following query is very slow! 9,400ms. It uses Seq Scan lol.
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'ABC') OR (filter(fts1body) @ filter('AAA') AND
ddstrike(fts1body,'AAA')))
Well, in this case it still needs to scan the whole table to search the
title obviously.
Apply filter to title column too.
The best query result costs 3,700ms. 18 hits. It surely uses expected query
plan: two Bitmap index scan - Bitmap Or - Bitmap Heap Scan.
SELECT * FROM xxx
WHERE TRUE
AND (filter(title) @ filter('ABC') OR filter(fts1body) @ filter('ABC'))
AND (ddstrike(title,'ABC') OR ddstrike(fts1body,'ABC'))
It would be useful to see the explain analyze of this query. Note that
looking up 11,000 entries in an index could very take as long as
sequentially scanning the whole table.
However, I may think good idea which uses inverted index.
I think your above idea is a good one, but you need to work out why
your above implementation didn't work out and why you think
implementing it directly will be better.
So I want to know...
- the actual work of extractQuery and consistant.
- the detail interface of extractValue/extractQuery/consistant. It may help
understanding.
I looked at contrib/_int.sql of PG8.2.22
Whoa, very old version, please look at something newer. For example the
RECHECK flag below is no longer used.
There are definitions of int[] GIN support.
---
CREATE OPERATOR CLASS gin__int_ops
FOR TYPE _int4 USING gin
AS
OPERATOR3 ,
OPERATOR6 = (anyarray, anyarray) RECHECK,
OPERATOR7 @,
OPERATOR8 @ RECHECK,
OPERATOR13 @,
OPERATOR14 ~ RECHECK,
OPERATOR20 @@ (_int4, query_int),
FUNCTION1 btint4cmp (int4, int4),
FUNCTION2 ginarrayextract (anyarray, internal),
FUNCTION3 ginint4_queryextract (internal, internal, int2),
FUNCTION4 ginint4_consistent (internal, int2, internal),
STORAGE int4;
---
Both ginint4_queryextract and ginint4_consistent assume that query argument
is a PGARRAY (ArrayType *). Where is it decided? Is it array of STORAGE type?
Remember the above uses operators which are what is indexed. The left
hand side is the array. The right hand side is whatever is defined.
intarray defines the operator (int[], int[]) hence the query
argument is int[] in that case. Apparently intarray accepts many kinds
of queries, it is the operators that define what actually happens.
Both extractQuery(ginint4_queryextract) and extractValue(ginarrayextract)
seem to return similar value type. They return Datum array of int4. Is it
array of STORAGE type?
From my reading of
http://www.postgresql.org/docs/9.2/static/gin-extensibility.html, yes
they must return an array of the STORAGE type. The last paragraph on
that page says:
The actual data types of the various Datum values mentioned above
vary depending on the operator class. The item values passed to
extractValue are always of the operator class's input type, and all
key values must be of the class's STORAGE type. The type of the
query argument passed to extractQuery and consistent is whatever is
specified as the right-hand input type of the class member operator
identified by the strategy number. This need not be the same as
the item type, so long as key values of the correct type can be
extracted from it.
I want to understand the overview of GIN extension.
Please let us know what the documentation is missing so it can be
improved.
Have a nice day,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.
-- Arthur Schopenhauer
signature.asc
Description: Digital signature
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello updated patch + more precious validity check Regards Pavel 2013/7/3 Pavel Stehule pavel.steh...@gmail.com: Hello So my vote is for make_time(hour int, min int, sec float8). so here is a patch Regards Pavel regards, tom lane make_date-v2.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] XLogInsert scaling, revisited
On Jul 8, 2013, at 4:16 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Ok, I've committed this patch now. Finally, phew! Woohoo! ...Robert -- 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] preserving forensic information when we freeze
On Jul 8, 2013, at 1:34 PM, Josh Berkus j...@agliodbs.com wrote: On 07/03/2013 11:59 AM, Robert Haas wrote: Yeah. I think the system columns that we have today are pretty much crap. I wonder if we couldn't throw them out and replace them with some kind of functions that you can pass a row to. That would allow us to expose a lot more detail without adding a bajillion hidden columns, and for a bonus we'd save substantially on catalog bloat. Where are we on this patch? Should I mark it Returned and you'll work on it for September? Sure. ...Robert -- 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] Changing recovery.conf parameters into GUCs
On 2013/07/09, at 4:09, Josh Berkus j...@agliodbs.com wrote: On 07/05/2013 10:09 PM, Michael Paquier wrote: Yeah, it would be good to revive this thread now, which is the beginning of the development cycle. As of now, just to recall everybody, an agreement on this patch still needs to be found... Simon is concerned with backward compatibility. Greg presented a nice spec some time ago (Robert and I liked it) which would break backward compatibility but allow to begin with a fresh infrastructure. As folks know, I favor Smith's approach. However, as far as I can find, GSmith never posted a patch for his version. Actually I did. http://www.postgresql.org/message-id/CAB7nPqR+fpopEDMoecK+AfZB5a8kUUvxpU=1a2JiX5d9s=0...@mail.gmail.com -- Michael (Sent from my mobile phone)
[HACKERS] pageinspect documentation for 9.3
For 9.3 and 9.4, all the interesting stuff was moved from htup.h to htup_details.h (c219d9b0a55bcdf81b00da6b) , but the docs for the pageinspect extension were not updated correspondingly. Attached patch to change the docs. Cheers, Jeff pageinspect_doc_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] pageinspect documentation for 9.3
Jeff Janes escribió: For 9.3 and 9.4, all the interesting stuff was moved from htup.h to htup_details.h (c219d9b0a55bcdf81b00da6b) , but the docs for the pageinspect extension were not updated correspondingly. Attached patch to change the docs. Applied, thanks. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.3 bug fix] backends emit hundreds of messages when statistics file is inaccessible
From: Tom Lane t...@sss.pgh.pa.us MauMau maumau...@gmail.com writes: If the directory specified by stats_temp_directory becomes inaccessible to postgres, enormous amount of WARNING messages are output. Well, yeah, because all of that functionality just broke. Not warning about it doesn't seem like a good idea. AFAICT, your patch would result in the situation not being remarked on anywhere except in the postmaster log. No, my patch makes backend_read_statsfile_timestamp() emit just one warning message instead of outputing the same message 1,000 times in one call. So your concern does not exist. Is this OK? Regards MauMau -- 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] Performance Improvement by reducing WAL for Update Operation
I can't comment on further direction for the patch, but since it was marked as Needs Review in the CF app I took a quick look at it. It patches and compiles clean against the current Git HEAD, and 'make check' runs successfully. Does it need documentation for the GUC variable 'wal_update_compression_ratio'? __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com http://www.rrdonnelley.com/ * mike.blackw...@rrd.com* On Tue, Jul 2, 2013 at 2:26 AM, Hari Babu haribabu.ko...@huawei.com wrote: On Friday, June 07, 2013 5:07 PM Amit Kapila wrote: On Wednesday, March 06, 2013 2:57 AM Heikki Linnakangas wrote: On 04.03.2013 06:39, Amit Kapila wrote: On Sunday, March 03, 2013 8:19 PM Craig Ringer wrote: On 02/05/2013 11:53 PM, Amit Kapila wrote: Performance data for the patch is attached with this mail. Conclusions from the readings (these are same as my previous patch): The attached patch also just adds overhead in most cases, but the overhead is much smaller in the worst case. I think that's the right tradeoff here - we want to avoid scenarios where performance falls off the cliff. That said, if you usually just get a slowdown, we certainly can't make this the default, and if we can't turn it on by default, this probably just isn't worth it. The attached patch contains the variable-hash-size changes I posted in the Optimizing pglz compressor. But in the delta encoding function, it goes further than that, and contains some further micro- optimizations: the hash is calculated in a rolling fashion, and it uses a specialized version of the pglz_hist_add macro that knows that the input can't exceed 4096 bytes. Those changes shaved off some cycles, but you could probably do more. One idea is to only add every 10 bytes or so to the history lookup table; that would sacrifice some compressibility for speed. If you could squeeze pglz_delta_encode function to be cheap enough that we could enable this by default, this would be pretty cool patch. Or at least, the overhead in the cases that you get no compression needs to be brought down, to about 2-5 % at most I think. If it can't be done easily, I feel that this probably needs to be dropped. After trying some more on optimizing pglz_delta_encode(), I found that if we use new data also in history, then the results of compression and cpu utilization are much better. In addition to the pg lz micro optimization changes, following changes are done in modified patch 1. The unmatched new data is also added to the history which can be referenced later. 2. To incorporate this change in the lZ algorithm, 1 extra control bit is needed to indicate if data is from old or new tuple The patch is rebased to use the new PG LZ algorithm optimization changes which got committed recently. Performance Data - Head code: testname | wal_generated | duration -+---+-- two short fields, no change |1232911016 | 35.1784930229187 two short fields, one changed |1240322016 | 35.0436308383942 two short fields, both changed |1235318352 | 35.4989421367645 one short and one long field, no change |1042332336 | 23.4457180500031 ten tiny fields, all changed|1395194136 | 41.9023628234863 hundred tiny fields, first 10 changed | 626725984 | 21.2999589443207 hundred tiny fields, all changed| 621899224 | 21.6676609516144 hundred tiny fields, half changed | 623998272 | 21.2745981216431 hundred tiny fields, half nulled| 557714088 | 19.5902800559998 pglz-with-micro-optimization-compress-using-newdata-2: testname | wal_generated | duration -+---+-- two short fields, no change |1232903384 | 35.0115969181061 two short fields, one changed |1232906960 | 34.759307861 two short fields, both changed |1232903520 | 35.7665238380432 one short and one long field, no change | 649647992 | 19.4671010971069 ten tiny fields, all changed|1314957136 | 39.9727990627289 hundred tiny fields, first 10 changed | 458684024 | 17.8197758197784 hundred tiny fields, all changed| 461028464 | 17.3083391189575 hundred tiny fields, half changed | 456528696 | 17.1769199371338 hundred tiny fields, half nulled| 480548936 | 18.81720495224 Observation --- 1. It yielded compression in more cases (refer all cases of hundred tiny fields) 2.
Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation
On 07/08/2013 02:21 PM, Mike Blackwell wrote: I can't comment on further direction for the patch, but since it was marked as Needs Review in the CF app I took a quick look at it. It patches and compiles clean against the current Git HEAD, and 'make check' runs successfully. Does it need documentation for the GUC variable 'wal_update_compression_ratio'? Yes. -- 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] XLogInsert scaling, revisited
On Mon, Jul 8, 2013 at 6:16 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Ok, I've committed this patch now. Finally, phew! +1. Great patch! -- 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] pg_filedump 9.3: checksums (and a few other fixes)
Alvaro Herrera alvhe...@2ndquadrant.com writes: Well, Tom opined in http://www.postgresql.org/message-id/23249.1370878...@sss.pgh.pa.us that the current patch is okay. I have a mild opinion that it should instead print only SHR_LOCK when both bits are set, and one of the others when only one of them is set. But I don't have a strong opinion about this, and since Tom disagrees with me, feel free to exercise your own (Jeff's) judgement. FWIW, I think that's exactly what I did in the preliminary 9.3 patch that I committed to pg_filedump a few weeks ago. Could you take a look at what's there now and see if that's what you meant? 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
[HACKERS] [9.4 CF] Free VMs for Reviewers Testers
Reviewer, Testers: As part of an ongoing effort to encourage patch review for the PostgreSQL project, we will be funding cloud servers for patch reviewers and testers who need them for CommitFests. That is, if you want to help with reviewing or testing a patch for a CommitFest, and don't have your own test server to use, the PostgreSQL project (via our funds at Software In the Public Interest) will pay for you to use a cloud server for the duration of your review/testing. So, if I don't have anywhere to test it was your excuse for not reviewing in the past, time to stop making excuses and start reviewing! Since these are cloud servers, they won't work well for performance testing. However, they will be excellent for testing replication. If you need one of these, please contact me to allocate a VM for you. -- 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
[HACKERS] Should we automatically run duplicate_oids?
When rebasing a patch that I'm working on, I occasionally forget to update the oid of any pg_proc.h entries I may have created. Of course this isn't a real problem; when I go to initdb, I immediately recognize what has happened. All the same, it seems like there is a case to be made for having this run automatically at build time, and having the build fail on the basis of there being a duplicate - this is something that fails reliably, but only when someone has added another pg_proc.h entry, and only when that other person happened to choose an oid in a range of free-in-git-tip oids that I myself fancied. Sure, I ought to remember to check this anyway, but it seems preferable to make this process more mechanical. I can point to commit 55c1687a as a kind of precedent, where the process of running check_keywords.pl was made to run automatically any time gram.c is rebuilt. Granted, that's a more subtle problem than the one I'm proposing to solve, but I still see this as a modest improvement. -- 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] [PATCH] Add an ldapoption to disable chasing LDAP referrals
Hey, New patch attached. I've moved from using a boolean to an enum trivalue. Let me know what you think. Cheers, James James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St, Melbourne, VIC, 3000 P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com On Thu, Jul 4, 2013 at 8:23 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Jul 4, 2013 at 2:30 AM, James Sewell james.sew...@lisasoft.comwrote: Heya, I see what you are saying, the problem as I see it is that the action we are taking here is disable chasing ldap referrals. If the name is ldapreferrals and we use a boolean then setting it to 1 reads in a counter intuitive manner: That assumes that the default in the ldap library is always going to be to chase them. Does the standard somehow mandate that it should be? set ldapreferals=true to disable chasing LDAP referrals. You'd obviously reverse the meaning as well. set ldapreferals=false to disable chasing LDAP referrals. Perhaps you are fine with this though if it's documented? It does work in the inverse way to pam_ldap, where setting to true enables referral chasing. pam_ldap works like so: not set : library default set to 0 : disable referral chasing set to 1 : enable referral chasing That is exactly what I'm suggesting it should do, and I'm pretty sure that's what Peter suggested as well. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence. image001.png pgsql_ldapnochaseref_v1.2.diff 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] Should we automatically run duplicate_oids?
Peter Geoghegan p...@heroku.com writes: ... All the same, it seems like there is a case to be made for having this run automatically at build time, and having the build fail on the basis of there being a duplicate This would require a rather higher standard of portability than duplicate_oids has heretofore been held to. Sure, I ought to remember to check this anyway, but it seems preferable to make this process more mechanical. I can point to commit 55c1687a as a kind of precedent, where the process of running check_keywords.pl was made to run automatically any time gram.c is rebuilt. Note that any time gram.c is rebuilt is not every build. In fact, for non-developers (tarball consumers), it's not *any* build. I'm not necessarily opposed to making this happen, but there's more legwork involved than just adding a call of the existing script in some random place in the makefiles. 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] [9.4 CF] Free VMs for Reviewers Testers
On 07/09/2013 08:35 AM, Josh Berkus wrote: Since these are cloud servers, they won't work well for performance testing. I did some work on that a while ago, and found that I was able to get _astonishingly_ stable performance results out of EC2 EBS instances using provisioned IOPS volumes. Running them as EBS Optimized didn't seem to be required for the workloads I was testing on. What isn't made very clear in the EBS provisioned I/O docs is that for PIOPS volumes the provisioned I/O count is both a minimum and a limit; it's a *target*, not just a guaranteed minimum. This is really handy for testing. For a 500 iops volume I consistently got 500 +- 5 in pg_test_fsync. Real world performance was a bit more variable - my pgbench runs were stable within 5-10% over 5 minute runs with a forced CHECKPOINT first. They settled considerably over longer runs and bigger checkpoint_segments, though, and I see the same kind of short-term jitter in pgbench on real hardware. So - don't write cloud hosts off for benchmarking. With proper QoS they're actually really very good. While they're somewhat costly, being able to spawn them for a day's runs and destroy them at the end is quite handy, as is being able to easily automate their setup. For testing of logical changeset streaming replication / BDR performance I was using scripted sets of EC2 c1.medium instances with 500 piops 'io1' EBS volumes and found it to be an exceedingly useful way of measuring relative performance. Results were also pretty consistent across multiple launches of the same VM, so multiple people should be able to compare results obtained with different launches of the same VM type and configuration. These VMs aren't well suited to vertical scaling performance tests and pushing extremes, but they're really, really good for what impact does this patch have on regular real-world workloads. Just remember to run pgbench from a different vm within the same availability zone (with appropriate security group setup) if you try to use EC2 for this sort of thing. Use of instance store or regular EBS volumes will get you performance that's absolutely all over the place, of course; only the provisioned I/O feature seems to be any good for perf testing. -- Craig Ringer 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] Should we automatically run duplicate_oids?
On Mon, Jul 8, 2013 at 6:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: This would require a rather higher standard of portability than duplicate_oids has heretofore been held to. Ah, yes. I suppose that making this happen would necessitate rewriting the script in highly portable Perl. Unfortunately, I'm not a likely candidate for that task. -- 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] Millisecond-precision connect_timeout for libpq
On Jul 8, 2013, at 1:31 PM, ivan babrou ibob...@gmail.com wrote: On 8 July 2013 20:40, David E. Wheeler da...@justatheory.com wrote: On Jul 8, 2013, at 7:44 AM, ivan babrou ibob...@gmail.com wrote: Can you tell me why having ability to specify more accurate connect timeout is a bad idea? Nobody answered my question yet. From an earlier post by Tom: What exactly is the use case for that? It seems like extra complication for something with little if any real-world usefulness. So the answer is: extra complication. Best, David I don't see any extra complication in backwards-compatible patch that removes more lines that adds. Can you tell me, what exactly is extra complicated? About pooling connections: we have 150 applications servers and 10 postgresql servers. Each app connects to each server - 150 connections per server if I run pooler on each application server. That's more than default setting and now we usually have not more than 10 connections per server. What would happen if we have 300 app servers? I thought connections consume some memory. Running pooler not on every app server gives no advantage — I still may get network blackhole and 2 seconds delay. Moreover, now I can guess that postgresql is overloaded if it does not accept connections, with pooler I can simply blow up disks with heavy io. Seriously, I don't get why running 150 poolers is easier. And my problem is still here: server (pooler is this case) is down — 2 seconds delay. 2000% slower. Where am I wrong? I agree with you. ...Robert -- 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] Should we automatically run duplicate_oids?
On Mon, 2013-07-08 at 19:38 -0700, Peter Geoghegan wrote: On Mon, Jul 8, 2013 at 6:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: This would require a rather higher standard of portability than duplicate_oids has heretofore been held to. Ah, yes. I suppose that making this happen would necessitate rewriting the script in highly portable Perl. Unfortunately, I'm not a likely candidate for that task. I don't think rewriting it in Perl is necessary or even desirable. I don't see anything particularly unportable in that script as it is. (Hmm, perhaps egrep should be replaced by grep.) -- 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] Should we automatically run duplicate_oids?
On Mon, Jul 8, 2013 at 7:59 PM, Peter Eisentraut pete...@gmx.net wrote: I don't think rewriting it in Perl is necessary or even desirable. I don't see anything particularly unportable in that script as it is. I was under the impression that the final patch ought to work on Windows too. However, I suppose that since the number of people that use windows as an everyday development machine is probably zero, we could reasonably forgo doing anything on that platform. -- 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] Performance Improvement by reducing WAL for Update Operation
On Tuesday, July 09, 2013 2:52 AM Mike Blackwell wrote: I can't comment on further direction for the patch, but since it was marked as Needs Review in the CF app I took a quick look at it. Thanks for looking into it. Last time Heikki has found test scenario's where the original patch was not performing good. He has also proposed a different approach for WAL encoding and sent the modified patch which has comparatively less negative performance impact and asked to check if the patch can reduce the performance impact for the scenario's mentioned by him. After that I found that with some modification's (use new tuple data for encoding) in his approach, it eliminates the negative performance impact and have WAL reduction for more number of cases. I think the first thing to verify is whether the results posted can be validated in some other environment setup by another person. The testcase used is posted at below link: http://www.postgresql.org/message-id/51366323.8070...@vmware.com It patches and compiles clean against the current Git HEAD, and 'make check' runs successfully. Does it need documentation for the GUC variable 'wal_update_compression_ratio'? This variable has been added to test the patch for different compression_ratio during development test. It was not decided to have this variable permanently as part of this patch, so currently there is no documentation for it. However if the decision comes out to be that it needs to be part of patch, then documentation for same can be updated. With Regards, Amit Kapila. -- 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] Should we automatically run duplicate_oids?
On 07/09/2013 11:03 AM, Peter Geoghegan wrote: On Mon, Jul 8, 2013 at 7:59 PM, Peter Eisentraut pete...@gmx.net wrote: I don't think rewriting it in Perl is necessary or even desirable. I don't see anything particularly unportable in that script as it is. I was under the impression that the final patch ought to work on Windows too. However, I suppose that since the number of people that use windows as an everyday development machine is probably zero, we could reasonably forgo doing anything on that platform. I'd say that the number who use Windows *and the unix-like build chain* day to day is going to be zero. If you're using the Visual Studio based toolchain with vcbuild.pl, vcregress.pl, etc you're not going to notice or care about this change. -- Craig Ringer 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] Should we automatically run duplicate_oids?
On Tue, Jul 9, 2013 at 6:55 AM, Peter Geoghegan p...@heroku.com wrote: When rebasing a patch that I'm working on, I occasionally forget to update the oid of any pg_proc.h entries I may have created. Of course this isn't a real problem; when I go to initdb, I immediately recognize what has happened. All the same, it seems like there is a case to be made for having this run automatically at build time, and having the build fail on the basis of there being a duplicate - this is something that fails reliably, but only when someone has added another pg_proc.h entry, and only when that other person happened to choose an oid in a range of free-in-git-tip oids that I myself fancied. Sure, I ought to remember to check this anyway, but it seems preferable to make this process more mechanical. I can point to commit 55c1687a as a kind of precedent, where the process of running check_keywords.pl was made to run automatically any time gram.c is rebuilt. Granted, that's a more subtle problem than the one I'm proposing to solve, but I still see this as a modest improvement. I completely agree with the idea. Doing these checks early in the build chain would be much more helpful than seeing the logs when initdb fails. Regards, Atri -- Regards, Atri l'apprenant -- 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] [9.4 CF] Free VMs for Reviewers Testers
On Mon, Jul 8, 2013 at 7:25 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 07/09/2013 08:35 AM, Josh Berkus wrote: Since these are cloud servers, they won't work well for performance testing. I did some work on that a while ago, and found that I was able to get _astonishingly_ stable performance results out of EC2 EBS instances using provisioned IOPS volumes. Running them as EBS Optimized didn't seem to be required for the workloads I was testing on. My colleague, Greg Burek, has done similar measurements and has assessed an overall similar conclusion: the EBS PIOPS product delivers exactly what it says on the tin...even under random access. They can be striped with software-RAID. These VMs aren't well suited to vertical scaling performance tests and pushing extremes, but they're really, really good for what impact does this patch have on regular real-world workloads. The really, really big ones are useful even for pushing limits, such as cr1.8xlarge, with 32 CPUs and 244GiB memory. Current spot instance price (the heavily discounted can die at any time one) is $0.343/hr. Otherwise, it's 3.500/hr. Another instance offering that -- unlike the former -- I have yet to experience in any way at all is the high-storage ones, also the hs1.8xlarge, with 16CPU, 117GB RAM, and 24 instance-local rotational media of 2TB apiece. This is enough to deliver sequential reads measured in a couple of GB/s. I think this is a workhorse behind the AWS Redshift data warehousing offering. I don't think this one has spot pricing either: my guess is availability is low. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
