Re: [HACKERS] [9.3] Automatically updatable views vs writable foreign tables
On 13 June 2013 01:11, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: The more I read the spec, the less sense it seems to make, and each time I read it, I seem to reach a different conclusion. On my latest reading, I've almost convinced myself that updatable is meant to imply support for all 3 operations (INSERT, UPDATE and DELETE), at least in the absence of transient tables. The descriptions of all 3 seem to require the table to be updatable. Still, they do admit the possibility of insertable_into being different from is_updatable. So I'm pretty happy with what we've got, at least on the relation level. Columns seem a bit more debatable; though I continue to think that an is_updatable column in a not-is_updatable table isn't contemplated by the spec. Of course if we didn't have rules, this wouldn't be as issue, because then a view that handled one update operation would handle them all. The spec doesn't need to worry about that, so it can define the updatability of a view as a singular concept based on the view's definition; and insertable_into in terms of the properties of the base table. In that context, the possibility of an is_updatable column in a not-is_updatable table doesn't need to be considered. I don't think that any more reading of the spec is going to help, because it's simply not as issue that they had to worry about. If the spec did consider rules, it would probably define rule_insertable, etc., in the same way as triggers. So our problem is in trying to shoe-horn rule-updatability into the spec's idea of updatability, and it doesn't really fit. The more technically correct answer might be to say that rule-updatable doesn't count as updatable any more than trigger-updatable does, but that wouldn't be very useful in practice because there are no columns in the information schema to check for rule-updatability. So really, I think we're trying to come up with the most practically useful definition, and in that context I think we've probably done the right thing at the relation-level, but I still think that a column could be marked as is_updatable, even if the table didn't support DELETEs. That said, I think that this is of such limited interest to anyone that I'm inclined to simply keep the status quo. Regards, Dean -- 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] Parallell Optimizer
On 11 June 2013 15:59, Tatsuo Ishii is...@postgresql.org wrote: I wonder why true synchronous replication nor eager replication are not in the developer TODO list. If we want them in the future, they should be on it. I think you still need to explain what true synchronous replication is. IMHO eager replication is of value only in a very localised sense. It doesn't help the general case where the location of servers isn't known or is known to be distributed, since it causes huge performance drops in those cases. Given sufficient resources (time, money, skill), it would certainly be on the list somewhere. But at present its far enough down the list to not be actively worked on, speaking personally. Please don't read into that some form of opposition. -- Simon Riggs 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] Parallell Optimizer
On 13 June 2013 02:18, Stephen Frost sfr...@snowman.net wrote: * Ants Aasma (a...@cybertec.at) wrote: In a cluster setting you take the CSN value on the master, then before starting execution on a standby you wait until that the standby has replayed enough WAL to reach the CSN point read from the master and you know that after that everything that the snapshot can see is also replayed on the standby. This does make a lot of sense- but to clarify, this would only be for certain isolation levels, right? Or would we implement this for every snapshot taken in a read-committed transaction? That idea is not dependent upon CSNs. It is an option for us to implement snapshot synchronisation now, we just haven't done it yet. I'm currently working on exporting/importing snapshots on standbys, which is a precursor to that idea. None of the above is any easier/harder with CSNs, nor would it delay/accelerate delivery of such features. -- Simon Riggs 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] Parallell Optimizer
On 06/13/2013 02:22 AM, Tatsuo Ishii wrote: On Jun 12, 2013 2:02 AM, Tatsuo Ishii is...@postgresql.org wrote: No, I'm not talking about conflict resolution. From http://www.cs.cmu.edu/~natassa/courses/15-823/F02/papers/replication.pdf: -- Eager or Lazy Replication? Eager replication: keep all replicas synchronized by updating all replicas in a single transaction Lazy replication: asynchronously propagate replica updates to other nodes after replicating transaction commits -- Parallel query execution needs to assume that each node synchronized in a commit, otherwise the summary of each query result executed on each node is meaningless. As far as I can see the lazy-eager terminology is based on a multi-master configuration and doesn't really apply for PostgreSQL streaming replication. Parallel query execution doesn't require commits to synchronize all nodes. Parallel execution needs consistent snapshots across all nodes. In effect this means that nodes need to agree on commit ordering, either total order or a partial order that accounts for causality. Most applications also want the guarantee that once they receive commit confirmation, next snapshot they take will consider their transaction as committed. Coincidentally getting cluster wide consistent snapshots and delaying until some specific point in commit ordering is almost trivial to solve with Commit Sequence Number based snapshot scheme that I proposed. Can you elaborate more on this? Suppose streaming replication primary commits xid = X at time Y. Later on a standy receives WAL including tx X and commit it at time Y + 3 seconds. How can a parallel query execution (which uses snapshot including X) on the standby be delayed until Y + 3 seconds? I do not think that CSN's change anything basic here, as CSN's are still local to each node. What you need is ability to ask for each node to wait until XID is replicated to it. Unless you have some central XID/Snapshot source, there is no global absolute XID order. That is there may be a transaction which is committed on node A and not yet on node B and at the same time a transaction which is committed on node B and not yet on node A. So to get consistent snapshot after X is committed in multimaster you need some coordination and possibly compromises w.r.t. single point in time Time in multimaster replication is relativistic, that is the order of events may depend on where the observer is :) -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] WITH CHECK OPTION for auto-updatable views
On 9 June 2013 11:14, Dean Rasheed dean.a.rash...@gmail.com wrote: Here's a rebased version of the patch implementing WITH CHECK OPTION for auto-updatable views. It now includes documentation, and a clearer description of the patch's limitations --- WITH CHECK OPTION is only supported on auto-updatable views, not trigger-updatable or rule-updatable views. I believe that's compatible with the following features from the SQL standard: F311-04 Schema definition statement CREATE VIEW: WITH CHECK OPTION F751View CHECK enhancements Here's an updated version --- I missed the necessary update to the check_option column of information_schema.views. Regards, Dean with-check-option.patch.gz Description: GNU Zip compressed 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] MD5 aggregate
Hi, Attached is a patch implementing a new aggregate function md5_agg() to compute the aggregate MD5 sum across a number of rows. This is something I've wished for a number of times. I think the primary use case is to do a quick check that 2 tables, possibly on different servers, contain the same data, using a query like SELECT md5_agg(foo.*::text) FROM (SELECT * FROM foo ORDER BY id) foo; or SELECT md5_agg(foo.*::text ORDER BY id) FROM foo; these would be equivalent to SELECT md5(string_agg(foo.*::text, '' ORDER BY id)) FROM foo; but without the excessive memory consumption for the intermediate concatenated string, and the resulting 1GB table size limit. I've added 2 variants: md5_agg(text) and md5_agg(bytea) to match the 2 variants of md5(), so pure binary data can also be checksummed. In passing, I've tidied up and optimised the code in md5.c a bit --- specifically I've removed the malloc()/memcpy()/free() code that was unnecessarily making a copy of the entire input data just to pad it and append the bit count. This reduces the memory consumption of the existing md5() functions for large inputs, and gives a modest performance boost. As a result, the md5() function can no longer throw an out-of-memory error. Regards, Dean md5_agg.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] Parallell Optimizer
On Thu, Jun 13, 2013 at 11:39 AM, Hannu Krosing ha...@2ndquadrant.com wrote: Coincidentally getting cluster wide consistent snapshots and delaying until some specific point in commit ordering is almost trivial to solve with Commit Sequence Number based snapshot scheme that I proposed. Can you elaborate more on this? Suppose streaming replication primary commits xid = X at time Y. Later on a standy receives WAL including tx X and commit it at time Y + 3 seconds. How can a parallel query execution (which uses snapshot including X) on the standby be delayed until Y + 3 seconds? I do not think that CSN's change anything basic here, as CSN's are still local to each node. I was mainly talking about what would be needed to support parallel queries in a single master configuration. What you need is ability to ask for each node to wait until XID is replicated to it. Unless you have some central XID/Snapshot source, there is no global absolute XID order. That is there may be a transaction which is committed on node A and not yet on node B and at the same time a transaction which is committed on node B and not yet on node A. So to get consistent snapshot after X is committed in multimaster you need some coordination and possibly compromises w.r.t. single point in time Time in multimaster replication is relativistic, that is the order of events may depend on where the observer is :) You can get total commit ordering and a non-relativistic database with reasonably low synchronization overhead. You will need a central coordinator that keeps track of latest commit sequence number assigned and largest commit sequence number guaranteed to have finished committing. Snapshots are assigned from the latter number, the value can be cached by nodes as any number less than the actual value is guaranteed consistent. Check out the concurrency control of Google's Spanner database[1] for ideas how this can be done with less consistency and avoiding the single point of failure. A central coordinator won't work for multi-master scenarios where individual masters need to be able to receive commits even with communication failures. In that case a relativistic view is unavoidable. No replication solution is a silver bullet. Some people want simple scale out for performance without having to deal with complexity of an inconsistent view of the database, while others need geographic distribution and resilience to network problems. It's fundamentally impossible to provide both with the same solution. [1] http://static.googleusercontent.com/external_content/untrusted_dlcp/research.google.com/en//archive/spanner-osdi2012.pdf 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] Parallell Optimizer
On Thu, Jun 13, 2013 at 11:24 AM, Simon Riggs si...@2ndquadrant.com wrote: That idea is not dependent upon CSNs. It is an option for us to implement snapshot synchronisation now, we just haven't done it yet. I'm currently working on exporting/importing snapshots on standbys, which is a precursor to that idea. None of the above is any easier/harder with CSNs, nor would it delay/accelerate delivery of such features. I agree that snapshot synchronization can be done with or without CSNs, but surely synchronizing a single monotonically increasing number is easier than synchronizing lists of running transactions. 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
[HACKERS] Passing fdw_private data from PlanForeignScan to PlanForeignModify
I'm currently implementing DML actions in the informix FDW module and trying to understand how UPDATE/DELETE actions interact with the various structures, especially how states between the associated ForeignScan and ForeignModify actions could be transmitted. As far as i understood, with e.g. an UPDATE action on a foreign table you get the following sequence of callbacks: GetForeignRelSize GetForeignPaths GetForeignPlan PlanForeignModify BeginForeignScan BeginForeignModify repeats IterateForeignScan ExecForeignUpdate /repeats EndForeignScan EndForeignModify During the planning phase, the Informix FDW currently plans a server side cursor, which identifier i'd like to pass into the planning phase of the modify action to reuse it in conjunction with UPDATE ... WHERE CURRENT OF. I understand that the ModifyTable structure passed to PlanForeignModify has a list of associated plan nodes, from which i can access the ForeignScan plan node associated with the current modify action, thus having access to the fdw_private data generated during the planning phase of the ForeigScan node. However, it's a list and i currently don't understand on how to reference the associated ForeignScan node reliably, given that there are cases with more than one node in this list. Any pointers, someone? -- Thanks Bernd -- 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] Passing fdw_private data from PlanForeignScan to PlanForeignModify
Bernd Helmle maili...@oopsware.de writes: During the planning phase, the Informix FDW currently plans a server side cursor, which identifier i'd like to pass into the planning phase of the modify action to reuse it in conjunction with UPDATE ... WHERE CURRENT OF. I understand that the ModifyTable structure passed to PlanForeignModify has a list of associated plan nodes, from which i can access the ForeignScan plan node associated with the current modify action, thus having access to the fdw_private data generated during the planning phase of the ForeigScan node. However, it's a list and i currently don't understand on how to reference the associated ForeignScan node reliably, given that there are cases with more than one node in this list. The subplan_index argument to PlanForeignModify is the list index to use, unless I'm misunderstanding something about this. However, in join cases the ForeignScan node will be buried down inside a join nest, so locating it in the plan tree could be rather difficult. My recommendation would be to see if you can't save the relevant info in the RelOptInfo node for the relation, probably during GetForeignPlan, and then get it from there in PlanForeignModify instead of digging in the plan tree. (You can use the fdw_private field of RelOptInfo for whatever you need in this line.) 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] [PATCH] Exorcise zero-dimensional arrays (Was: Re: Should array_length() Return NULL)
On Wed, Jun 12, 2013 at 10:00 PM, Brendan Jurd dire...@gmail.com wrote: On 13 June 2013 04:26, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Jun 12, 2013 at 1:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: On 06/12/2013 11:01 AM, Tom Lane wrote: I'm going to be disappointed if all we can get out of this is a cardinality() function, and nothing is done about the empty-array semantics. I would be disappointed too, but on the other hand, CARDINALITY is required by the spec and anything would be better than nothing. certainly. But, couldn't that be solved by deprecating that function and providing a more sensible alternatively named version? And what would you name that function? array_dims2? I can't think of a name that makes the difference in behaviour apparent. Can you imagine the documentation for that? I don't know the answer to that, but I think it's hard to argue that deprecating and documenting a few functions is a heavier burden on your users than having to sift through older arcane code before upgrading to the latest version of the database. We're not the only ones stuck with lousy old functions (C finally ditched gets() in the 2011 standard). I also happen to think the current array_api function names are not particularly great (especially array_upper/array_lower) so I won't shed too many tears. merlin -- 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] Remove useless USE_PGXS support in contrib
On Jun 12, 2013, at 8:16 PM, Peter Eisentraut pete...@gmx.net wrote: This has served no purpose except to 1. take up space 2. confuse users 3. produce broken external extension modules that take contrib as an example 4. break builds of PostgreSQL when users try to fix 3. by exporting USE_PGXS There is adequate material in the documentation and elsewhere (PGXN) on how to write extensions and their makefiles, so this is not needed. +1 Nice. David -- 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] Exorcise zero-dimensional arrays (Was: Re: Should array_length() Return NULL)
On Jun 12, 2013, at 8:00 PM, Brendan Jurd dire...@gmail.com wrote: array_dims - Returns the dimensions of the array, unless it is empty in which case NULL. array_proper_dims - Returns the dimensions of the array. array_ndims - Returns the number of dimension, unless it is empty in which case NULL. array_proper_ndims - Returns the number of dimensions. ... and so on for _length, _upper and _lower. Similar things should have dissimilar names. I propose: bikeshedding Old |New --+-- array_dims | array_desc array_ndims | array_depth array_length | array_size array_lower | array_start array_upper | array_finish The last two are meh, but it’s a place to start… /bikeshedding David -- 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] Clean switchover
On Wed, Jun 12, 2013 at 12:55 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 12/06/13 13:15, Stephen Frost wrote: * Fujii Masao (masao.fu...@gmail.com) wrote: The attached patch fixes this problem. It just changes walsender so that it waits for all the outstanding WAL records to be replicated to the standby before closing the replication connection. Seems like a good idea to me.. Rather surprised that we're not doing this already, to be honest. Yeah +1 from here too. This would make clean switchovers for (typically) testing scenarios a lot less complex and resource intensive (rebuilding of the old master as a slave when you know it is ok is despairing on a huge db). On the related note (but not actually to do with this patch), clarifying/expanding the docs about the various methods for standby promotion: 1/ trigger file creation 2/ pg_ctl promote 3/ renaming/removing recovery.conf and the differences between them would be great. For instance I only recently realized that method 3) means the promoted standby does not start a new timeline (incidentally - could this be an option to pg_ctl promote) which is very useful for (again) controlled/clean switchovers. In 9.3, you no longer need to worry about the increment of timeline after the promotion because the standby can automatically follow the timeline switch. Regards, -- Fujii Masao -- 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] Clean switchover
On Wed, Jun 12, 2013 at 9:55 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-12 08:48:39 -0400, Stephen Frost wrote: * Magnus Hagander (mag...@hagander.net) wrote: On Wed, Jun 12, 2013 at 1:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-12 07:53:29 +0900, Fujii Masao wrote: The attached patch fixes this problem. It just changes walsender so that it waits for all the outstanding WAL records to be replicated to the standby before closing the replication connection. Imo this is a fix that needs to get backpatched... The code tried to do this but failed, I don't think it really gives grounds for valid *new* concerns. +1 (without having looked at the code itself, it's definitely a behaviour that needs to be fixed) Yea, I was also thinking it would be reasonable to backpatch this; it really looks like a bug that we're allowing this to happen today. So, +1 on a backpatch for me. I've looked at the patch (it's a one-liner, plus some additional comments) but havn't looked through the overall code surrounding it. I've read most of the surrounding code and I think the patch is as sensible as it can be without reworking the whole walsender main loop which seems like a job for another day. I'd personally write if (caughtup !pq_is_send_pending() sentPtr == MyWalSnd-flush) as if (caughtup sentPtr == MyWalSnd-flush !pq_is_send_pending()) Since pq_is_send_pending() basically can only be false if the flush comparison is true. There's the tiny chance that we were sending a message out just before which is why we should include the !pq_is_send_pending() condition at all in that if(). Yep, I updated the patch that way. Thanks for the comment! Regards, -- Fujii Masao switchover_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] Clean switchover
On Wed, Jun 12, 2013 at 9:48 PM, Stephen Frost sfr...@snowman.net wrote: * Magnus Hagander (mag...@hagander.net) wrote: On Wed, Jun 12, 2013 at 1:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-06-12 07:53:29 +0900, Fujii Masao wrote: The attached patch fixes this problem. It just changes walsender so that it waits for all the outstanding WAL records to be replicated to the standby before closing the replication connection. Imo this is a fix that needs to get backpatched... The code tried to do this but failed, I don't think it really gives grounds for valid *new* concerns. +1 (without having looked at the code itself, it's definitely a behaviour that needs to be fixed) Yea, I was also thinking it would be reasonable to backpatch this; it really looks like a bug that we're allowing this to happen today. So, +1 on a backpatch for me. +1. I think that we can backpatch to 9.1, 9.2 and 9.3. In 9.0, the standby doesn't send back any message to the master and there is no way to know whether replication has been done up to the specified location, so I don't think that we can backpatch. One note is, even if we backpatch, controlled switchover may require the backup in order to follow the timeline switch, in 9.1 and 9.2. If we want to avoid the backup in that case, we need to set up the shared archive area between the master and the standby and set recovery_target_timeline to 'latest'. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GIN improvements part 1: additional information
Hackers, Revised version of patch for additional information storage in GIN is attached. Changes are mostly bug fixes. Resemble GIN interface changes that this patch introduce. Patch modifies GIN interface as following: 1) Two arguments are added to extractValue Datum **addInfo, bool **addInfoIsNull 2) Two arguments are added to consistent Datum addInfo[], bool addInfoIsNull[] 3) New method config is introduced which returns datatype oid of addtional information (analogy with SP-GiST config method). Additionally there is another patch which demonstrates benefits from additional information storage itself (because it don't accelerate tsearch itselt). It comes in separate thread. -- With best regards, Alexander Korotkov. ginaddinfo.4.patch.gz Description: GNU Zip compressed 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] Index on regexes
Hackers,
Attached patch contains opclass which demonstrates advantages of GIN
additional information storing itself without other GIN improvements. It
implements inversed task of regex indexing. It works so: you create index
on regexes and search for regexes matched query string. It introduce two
additional operators |~ and *~ for case-sensetive and case-insensetive
regex to string matching, and gin_regexp_trgm_ops opclass.
Let's consider some example.
At first, generate some regexes.
CREATE OR REPLACE FUNCTION generate_string(int, int) RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT chr((97 + random() * 10) :: integer)
FROM generate_series(1,($1 + random()*$2)::int)), '');
$$
LANGUAGE sql;
CREATE TABLE test AS select '(' || generate_string(1,4) || '|' ||
generate_string(1,4) || '|' || generate_string(1,4) || ')' ||
generate_string(1,2) || '(' || generate_string(1,4) || '|' ||
generate_string(1,4) || '|' || generate_string(1,4) || ')' AS s FROM
generate_series(1,100);
I use only 10 characters on alphabet in order to have better chance of
matching. It generate some regexes like so:
postgres=# SELECT * FROM test LIMIT 10;
s
(g|cij|ah)jg(iei|hfc|eef)
(gbfdb|ehbg|akf)ge(bc|jgee|jidd)
(jedc|kgc|c)bc(ii|bji|iebc)
(aa|eie|bgdb)f(fc|he|f)
(b|ijc|ae)ae(eccb|ie|kjf)
(bib|igf|kdibf)fij(gcbh|efi|fidj)
(bkejf|jfdhg|gbfe)bhb(bedj|hh|ggg)
(kfb|egccd|iefce)jf(kj|jbef|kbc)
(bhh|c|cd)cb(h|ed|jg)
(id|j|geg)gc(djif|ai|cjjjc)
(10 rows)
Without index search takes about 10 seconds.
postgres=# explain analyze select * from test where s |~ 'abcdefghijkl';
QUERY PLAN
--
Seq Scan on test (cost=0.00..19929.00 rows=5000 width=28) (actual
time=172.990..97357.312 rows=438 loops=1)
Filter: (s |~ 'abcdefghijkl'::text)
Rows Removed by Filter: 999562
Total runtime: 97357.490 ms
(4 rows)
And with index it takes only 110 milliseconds.
postgres=# explain analyze select * from test where s |~ 'abcdefghijkl';
QUERY PLAN
--
Bitmap Heap Scan on test (cost=182.75..7245.94 rows=5000 width=28)
(actual time=68.143..110.663 rows=438 loops=1)
Recheck Cond: (s |~ 'abcdefghijkl'::text)
- Bitmap Index Scan on test_idx (cost=0.00..181.50 rows=5000 width=0)
(actual time=67.929..67.929 rows=438 loops=1)
Index Cond: (s |~ 'abcdefghijkl'::text)
Total runtime: 110.870 ms
(5 rows)
--
With best regards,
Alexander Korotkov.
index_on_regexes.1.patch.gz
Description: GNU Zip compressed 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] Adjusting elog behavior in bootstrap/standalone mode
Back before Christmas I complained that elog.c's behavior of
prioritizing LOG-level messages differently in standalone mode had
masked at least one significant bug, as well as causing confusion
in some other cases:
http://www.postgresql.org/message-id/6967.1355520...@sss.pgh.pa.us
I promptly forgot to do anything about it, but I'd still like to get
such a change into 9.3. I've now written and tested a patch that
makes that change without creating any new noise during initdb or in a
manually-started standalone backend. Does anyone have an objection to
applying the attached?
regards, tom lane
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 7210ca5fddba177fb1c6949761e938165892bb28..654c9c18d8ba0168c44c81e428b5c26731921a8d 100644
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
*** StartupXLOG(void)
*** 4883,4891
(errmsg(control file contains invalid data)));
if (ControlFile-state == DB_SHUTDOWNED)
! ereport(LOG,
(errmsg(database system was shut down at %s,
str_time(ControlFile-time;
else if (ControlFile-state == DB_SHUTDOWNED_IN_RECOVERY)
ereport(LOG,
(errmsg(database system was shut down in recovery at %s,
--- 4883,4894
(errmsg(control file contains invalid data)));
if (ControlFile-state == DB_SHUTDOWNED)
! {
! /* This is the expected case, so don't be chatty in standalone mode */
! ereport(IsPostmasterEnvironment ? LOG : NOTICE,
(errmsg(database system was shut down at %s,
str_time(ControlFile-time;
+ }
else if (ControlFile-state == DB_SHUTDOWNED_IN_RECOVERY)
ereport(LOG,
(errmsg(database system was shut down in recovery at %s,
*** GetNextXidAndEpoch(TransactionId *xid, u
*** 6590,6596
void
ShutdownXLOG(int code, Datum arg)
{
! ereport(LOG,
(errmsg(shutting down)));
if (RecoveryInProgress())
--- 6593,6600
void
ShutdownXLOG(int code, Datum arg)
{
! /* Don't be chatty in standalone mode */
! ereport(IsPostmasterEnvironment ? LOG : NOTICE,
(errmsg(shutting down)));
if (RecoveryInProgress())
*** ShutdownXLOG(int code, Datum arg)
*** 6612,6618
ShutdownSUBTRANS();
ShutdownMultiXact();
! ereport(LOG,
(errmsg(database system is shut down)));
}
--- 6616,6623
ShutdownSUBTRANS();
ShutdownMultiXact();
! /* Don't be chatty in standalone mode */
! ereport(IsPostmasterEnvironment ? LOG : NOTICE,
(errmsg(database system is shut down)));
}
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index e9eb3d5be8cc8fbb4fc3ba4dc58f1b27e1057e45..7f03f419dead8f7f325a8e24a345d23fb1667192 100644
*** a/src/backend/utils/error/elog.c
--- b/src/backend/utils/error/elog.c
*** errstart(int elevel, const char *filenam
*** 285,295
*/
/* Determine whether message is enabled for server log output */
! if (IsPostmasterEnvironment)
! output_to_server = is_log_level_output(elevel, log_min_messages);
! else
! /* In bootstrap/standalone case, do not sort LOG out-of-order */
! output_to_server = (elevel = log_min_messages);
/* Determine whether message is enabled for client output */
if (whereToSendOutput == DestRemote elevel != COMMERROR)
--- 285,291
*/
/* Determine whether message is enabled for server log output */
! output_to_server = is_log_level_output(elevel, log_min_messages);
/* Determine whether message is enabled for client output */
if (whereToSendOutput == DestRemote elevel != COMMERROR)
--
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] Index on regexes
On Thu, June 13, 2013 22:19, Alexander Korotkov wrote: [index_on_regexes.1.patch.gz ] Hi, Compile of core is OK, but contrib compilation fails: -- [2013.06.13 23:23:14 idxregex] make contrib trgm_gin.c: In function gin_regexp_trgm_config: trgm_gin.c:410:2: error: unknown type name GinConfig GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: error: GinConfig undeclared (first use in this function) GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: note: each undeclared identifier is reported only once for each function it appears in trgm_gin.c:410:37: error: expected expression before ) token GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:412:11: error: request for member addInfoTypeOid in something not a structure or union ginConfig-addInfoTypeOid = BYTEAOID; ^ make[1]: *** [trgm_gin.o] Error 1 make: *** [all-pg_trgm-recurse] Error 2 trgm_gin.c: In function gin_regexp_trgm_config: trgm_gin.c:410:2: error: unknown type name GinConfig GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: error: GinConfig undeclared (first use in this function) GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: note: each undeclared identifier is reported only once for each function it appears in trgm_gin.c:410:37: error: expected expression before ) token GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:412:11: error: request for member addInfoTypeOid in something not a structure or union ginConfig-addInfoTypeOid = BYTEAOID; ^ make[1]: *** [trgm_gin.o] Error 1 make: *** [install-pg_trgm-recurse] Error 2 It is also not entirely clear from your post how this index is created, I suppose the regex index is only in trgm? create index test_gin_idx on test (s gin_regexp_trgm_ops); -- right? thanks, Erik Erijkers -- 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] Index on regexes
On Fri, Jun 14, 2013 at 1:30 AM, Erik Rijkers e...@xs4all.nl wrote: On Thu, June 13, 2013 22:19, Alexander Korotkov wrote: [index_on_regexes.1.patch.gz ] Hi, Compile of core is OK, but contrib compilation fails: -- [2013.06.13 23:23:14 idxregex] make contrib trgm_gin.c: In function ‘gin_regexp_trgm_config’: trgm_gin.c:410:2: error: unknown type name ‘GinConfig’ GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: error: ‘GinConfig’ undeclared (first use in this function) GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: note: each undeclared identifier is reported only once for each function it appears in trgm_gin.c:410:37: error: expected expression before ‘)’ token GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:412:11: error: request for member ‘addInfoTypeOid’ in something not a structure or union ginConfig-addInfoTypeOid = BYTEAOID; ^ make[1]: *** [trgm_gin.o] Error 1 make: *** [all-pg_trgm-recurse] Error 2 trgm_gin.c: In function ‘gin_regexp_trgm_config’: trgm_gin.c:410:2: error: unknown type name ‘GinConfig’ GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: error: ‘GinConfig’ undeclared (first use in this function) GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:410:26: note: each undeclared identifier is reported only once for each function it appears in trgm_gin.c:410:37: error: expected expression before ‘)’ token GinConfig *ginConfig = (GinConfig *)PG_GETARG_POINTER(0); ^ trgm_gin.c:412:11: error: request for member ‘addInfoTypeOid’ in something not a structure or union ginConfig-addInfoTypeOid = BYTEAOID; ^ make[1]: *** [trgm_gin.o] Error 1 make: *** [install-pg_trgm-recurse] Error 2 Likely I wasn't explicit enough. You need to apply this patch first: http://www.postgresql.org/message-id/capphfduxv-il7aedwpw0w5fxrwgakfxijwm63_hzujacrxn...@mail.gmail.com It is also not entirely clear from your post how this index is created, I suppose the regex index is only in trgm? create index test_gin_idx on test (s gin_regexp_trgm_ops); -- right? Oh, it was missed. Right. -- With best regards, Alexander Korotkov.
Re: [HACKERS] MD5 aggregate
On 6/13/13 5:35 AM, Dean Rasheed wrote: Attached is a patch implementing a new aggregate function md5_agg() to compute the aggregate MD5 sum across a number of rows. That seems somewhat useful. In passing, I've tidied up and optimised the code in md5.c a bit --- specifically I've removed the malloc()/memcpy()/free() code that was unnecessarily making a copy of the entire input data just to pad it and append the bit count. This reduces the memory consumption of the existing md5() functions for large inputs, and gives a modest performance boost. As a result, the md5() function can no longer throw an out-of-memory error. I think it would be better if you split this into two separate patches. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On 06/13/2013 05:35 AM, Amit Kapila wrote:
On Friday, June 07, 2013 9:45 AM Amit Kapila wrote:
On Thursday, June 06, 2013 10:22 PM Robert Haas wrote:
On Wed, Jun 5, 2013 at 7:24 AM, Amit Kapila amit.kap...@huawei.com
wrote:
On Monday, May 27, 2013 4:17 PM Amit Kapila wrote:
On Wednesday, April 03, 2013 11:55 AM Amit Kapila wote:
On Tuesday, April 02, 2013 9:49 PM Peter Eisentraut wrote:
There are 2 options to proceed for this patch for 9.4
1. Upload the SET PERSISTENT syntax patch for coming CF by fixing
existing
review comments
2. Implement new syntax ALTER SYSTEM as proposed in below mail
Could you suggest me what could be best way to proceed for this
patch?
I'm still in favor of some syntax involving ALTER, because it's still
true that this behaves more like the existing GUC-setting commands
that use ALTER (which change configuration for future sessions)
rather
the ones that use SET (which change the current settings for some
period of time).
I will change the patch as per below syntax if there are no objections:
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value'};
Modified patch contains:
1. Syntax implemented is
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value' |
DEFAULT};
If user specifies DEFAULT, it will remove entry from auto conf file.
2. File name to store settings set by ALTER SYSTEM command is still
persistent.auto.conf
Why? Shouldn't it just be auto.conf? Or system.auto.conf?
I prefer auto.conf, personally.
--
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] Passing fdw_private data from PlanForeignScan to PlanForeignModify
--On 13. Juni 2013 11:06:31 -0400 Tom Lane t...@sss.pgh.pa.us wrote: My recommendation would be to see if you can't save the relevant info in the RelOptInfo node for the relation, probably during GetForeignPlan, and then get it from there in PlanForeignModify instead of digging in the plan tree. (You can use the fdw_private field of RelOptInfo for whatever you need in this line.) Hmm, I tried this already, but maybe i'm doing something entirely wrong here. What i tried before was to access (in PlanForeignModify) the RelOptInfo structure through PlannerInfo-simple_rel_array, assuming the the resultRelation index points to the right array member. However, that didn't work, the fdw_private List is not the one filled by GetForeignPlan...is there another way to get back the RelOptInfo worked on earlier? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] single-user vs standalone in docs and messages
Some places in the docs and elog hints refer to standalone backends, while the official name as used in app-postgres.html is single-user mode, and in fact standalone does not appear on that page. This tries to standardize the other locations to use single-user. I think I did the right thing with the message translation files, but I can't figure out how to test that. I made no attempt to change code-comments, just the user-facing parts. Cheers, Jeff standalone_single_user_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] Passing fdw_private data from PlanForeignScan to PlanForeignModify
Bernd Helmle maili...@oopsware.de writes:
What i tried before was to access (in PlanForeignModify) the RelOptInfo
structure through PlannerInfo-simple_rel_array, assuming the the
resultRelation index points to the right array member. However, that didn't
work, the fdw_private List is not the one filled by GetForeignPlan...is
there another way to get back the RelOptInfo worked on earlier?
It should work ... *if* there was in fact a RelOptInfo worked on
earlier. There sometimes isn't. You might need to do something like
what make_modifytable() has to do to call you in the first place:
/*
* If possible, we want to get the FdwRoutine from our RelOptInfo for
* the table. But sometimes we don't have a RelOptInfo and must get
* it the hard way. (In INSERT, the target relation is not scanned,
* so it's not a baserel; and there are also corner cases for
* updatable views where the target rel isn't a baserel.)
*/
if (rti root-simple_rel_array_size
root-simple_rel_array[rti] != NULL)
{
RelOptInfo *resultRel = root-simple_rel_array[rti];
fdwroutine = resultRel-fdwroutine;
}
else
{
RangeTblEntry *rte = planner_rt_fetch(rti, root);
Assert(rte-rtekind == RTE_RELATION);
if (rte-relkind == RELKIND_FOREIGN_TABLE)
fdwroutine = GetFdwRoutineByRelId(rte-relid);
else
fdwroutine = NULL;
}
if (fdwroutine != NULL
fdwroutine-PlanForeignModify != NULL)
fdw_private = fdwroutine-PlanForeignModify(root, node, rti, i);
[ jargon alert: baserel here basically means a table the query has
to scan. ]
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] single-user vs standalone in docs and messages
Jeff Janes escribió: I think I did the right thing with the message translation files, but I can't figure out how to test that. Don't do that. These files are automatically generated. Just leave them alone and translators will get to them in time. -- Á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
[HACKERS] postgres user with automate rsync and private/public key pairs
Hi here! Centos 6.4 Postgres 9.1 I want to automate rsync backups from server1 to server2 via ssh using the postgres(linux deamon user) and a pair of private/public keys generated by ssh-keygen I generated the keys in Server1 and copy and added id_rsa.pub to /var/lib/pgsql/.ssh/authorized_keys in Server2. all owned by postgres:postgres and chmod 700 to .ssh/ folder and chmod 600 to authorized_key file. But when try to test it and try to access via ssh always ask for postgres password. Same procedure works properly for others user, but not for postgres user. postgres user is installed without password, I believed that this is the problem and I try to set a password, but the problem persists although postgres user has a password, can connect without avoid the ssh password required prompt. At first it seems that everything is properly configured, I forget something? Can postgres (linux user) login via ssh with private/public pair keys? Last think ... I try to reset the postgres user as initial setup (without password) using passwd -d command but shadow file shows different blank password is not as double exclamation I don't know if it's indiferent for correct postgres work or there are some way to reset original postgres user values. postgres::15849:0:9:7::: instead of: postgres:!!:15646:: Thanks for your comments
Re: [HACKERS] postgres user with automate rsync and private/public key pairs
Daniel, The pgsql-hackers mailing list is for people working on the PostgreSQL database engine itself. Please post your question to one of the following lists instead: pgsql-general pgsql-admin Or you can use our IRC channel: http://www.postgresql.org/community/irc/ Thanks! -- 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)
Jeff Davis pg...@j-davis.com writes: The patch is a bit ugly: I had to copy some code, and copy the entire checksum.c file (minus some Asserts, which don't work in an external program). Suggestions welcome. What I propose we do about this is reduce backend/storage/page/checksum.c to something like #include postgres.h #include storage/checksum.h #include storage/checksum_impl.h moving all the code currently in the file into a new .h file. Then, any external programs such as pg_filedump can use the checksum code by including checksum_impl.h. This is essentially the same thing we did with the CRC support functionality some time ago. Also, we have the cut-point between checksum.c and bufpage.c at the wrong place. IMO we should move PageCalcChecksum16 in toto into checksum.c (or really now into checksum_impl.h), because that and not just checksum_block() is the functionality that is wanted. 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] Branching for 9.4
Barring objections, I will branch off REL9_3_STABLE in the git repo tomorrow afternoon, probably around 1800 UTC. 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] [PATCH] Remove useless USE_PGXS support in contrib
- Original Message - From: Peter Eisentraut pete...@gmx.net To: pgsql-hackers@postgresql.org Cc: Sent: Thursday, 13 June 2013 12:16 PM Subject: [HACKERS] [PATCH] Remove useless USE_PGXS support in contrib T his has served no purpose except to 1. take up space 2. confuse users 3. produce broken external extension modules that take contrib as an example 4. break builds of PostgreSQL when users try to fix 3. by exporting USE_PGXS Agree, only if we consider these contrib module is always gonna deployed with the postgresql. But, what if user going to install such module elsewhere i.e. not from contrib directory of pg source. I this way Makefile should different, right? We need to makefile code should uniform to exclude any confusion to user. if space is not such dominant, we can add code at the top of make file to avoid confusion of user to export USE_PGXS as follow ifndef USE_PGXS top_builddir = ../.. makefile_global = $(top_builddir)/src/Makefile.global ifeq $(wildcard $(makefile_global)) USE_PGXS = 1 endif /* remaining code as it is */* Regards, Amul Sul -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] updated emacs configuration
I think the suggested emacs configuration snippets in src/tools/editors/emacs.samples no longer represent current best practices. I have come up with some newer things that I'd like to propose for review. First, I propose adding a .dir-locals.el file to the top-level directory with basic emacs settings. These get applied automatically. This especially covers the particular tab and indentation settings that PostgreSQL uses. With this, casual developers will not need to modify any of their emacs settings. (In the attachment, .dir-locals.el is called _dir-locals.el so that it doesn't get lost. To clarify, it goes into the same directory that contains configure.in.) With that, emacs.samples can be shrunk significantly. The only real reason to keep is that that c-offsets-alist and (more dubiously) sgml-basic-offset cannot be set from .dir-locals.el because they are not safe. I have also removed many of the redundant examples and settled on a hook-based solution. I think together this setup would be significantly simpler and more practical. ((c-mode . ((c-basic-offset . 4) (fill-column . 79) (indent-tabs-mode . t) (tab-width . 4))) (dsssl-mode . ((indent-tabs-mode . nil))) (nxml-mode . ((indent-tabs-mode . nil))) (perl-mode . ((perl-indent-level . 4) (perl-continued-statement-offset . 4) (perl-continued-brace-offset . 4) (perl-brace-offset . 0) (perl-brace-imaginary-offset . 0) (perl-label-offset . -2) (tab-width . 4))) (sgml-mode . ((fill-column . 79) (indent-tabs-mode . nil ;; -*- mode: emacs-lisp -*- ;; This file contains code to set up Emacs to edit PostgreSQL source ;; code. Copy these snippets into your .emacs file or equivalent, or ;; use load-file to load this file directly. ;; ;; Note also that there is a .dir-locals.el file at the top of the ;; PostgreSQL source tree, which contains many of the settings shown ;; here. So for light editing, you might not need any additional ;; Emacs configuration. ;;; C files ;; Style that matches the formatting used by ;; src/tools/pgindent/pgindent. Many extension projects also use this ;; style. (c-add-style postgresql '(bsd (c-basic-offset . 4) (c-offsets-alist . ((case-label . +))) (fill-column . 79) (indent-tabs-mode . t) (tab-width . 4))) (add-hook 'c-mode-hook (lambda () (when (string-match /postgresql/ buffer-file-name) (c-set-style postgresql ;;; documentation files (add-hook 'sgml-mode-hook (lambda () (when (string-match /postgresql/ buffer-file-name) (setq fill-column 79) (setq indent-tabs-mode nil) (setq sgml-basic-offset 1 ;;; Makefiles ;; use GNU make mode instead of plain make mode (add-to-list 'auto-mode-alist '(/postgresql/.*Makefile.* . makefile-gmake-mode)) (add-to-list 'auto-mode-alist '(/postgresql/.*\\.mk\\' . makefile-gmake-mode)) -- 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] Adjusting elog behavior in bootstrap/standalone mode
* Tom Lane (t...@sss.pgh.pa.us) wrote: I promptly forgot to do anything about it, but I'd still like to get such a change into 9.3. I've now written and tested a patch that makes that change without creating any new noise during initdb or in a manually-started standalone backend. Does anyone have an objection to applying the attached? Looks like a good idea to me, +1. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Clean switchover
On 14/06/13 07:38, Fujii Masao wrote: On Wed, Jun 12, 2013 at 12:55 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 12/06/13 13:15, Stephen Frost wrote: * Fujii Masao (masao.fu...@gmail.com) wrote: The attached patch fixes this problem. It just changes walsender so that it waits for all the outstanding WAL records to be replicated to the standby before closing the replication connection. Seems like a good idea to me.. Rather surprised that we're not doing this already, to be honest. Yeah +1 from here too. This would make clean switchovers for (typically) testing scenarios a lot less complex and resource intensive (rebuilding of the old master as a slave when you know it is ok is despairing on a huge db). On the related note (but not actually to do with this patch), clarifying/expanding the docs about the various methods for standby promotion: 1/ trigger file creation 2/ pg_ctl promote 3/ renaming/removing recovery.conf and the differences between them would be great. For instance I only recently realized that method 3) means the promoted standby does not start a new timeline (incidentally - could this be an option to pg_ctl promote) which is very useful for (again) controlled/clean switchovers. In 9.3, you no longer need to worry about the increment of timeline after the promotion because the standby can automatically follow the timeline switch. Regards, Yes - and that will be awesome. Nice work! However for those systems still on 9.1/9.2for the time being, some clarification of the details/differences uisg promotion via the 1) - 3) would be useful I think. Note I'm not demanding that you do it - I just happened to be thinking about this when I saw your patch, so though I'd mention it (I've seen some brief discussion about this before, but nothing concrete came out of that in terms of documentation additions). If folks are keen, I'm willing to attempt to find a suitable place in the docs to add a discussion about 1) - 3) above. However I was kinda hoping that someone who knows all the details would... fro instance I've skimmed the code and note that the system knows when it is promoted via trigger file vs pg_ctl... but as to what if any differences that makes to the resulting actions (other than removing the trigger file) - I am not clear on. Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Friday, June 14, 2013 3:17 AM Josh Berkus wrote:
On 06/13/2013 05:35 AM, Amit Kapila wrote:
On Friday, June 07, 2013 9:45 AM Amit Kapila wrote:
On Thursday, June 06, 2013 10:22 PM Robert Haas wrote:
On Wed, Jun 5, 2013 at 7:24 AM, Amit Kapila
amit.kap...@huawei.com
wrote:
On Monday, May 27, 2013 4:17 PM Amit Kapila wrote:
On Wednesday, April 03, 2013 11:55 AM Amit Kapila wote:
On Tuesday, April 02, 2013 9:49 PM Peter Eisentraut wrote:
There are 2 options to proceed for this patch for 9.4
1. Upload the SET PERSISTENT syntax patch for coming CF by fixing
existing
review comments
2. Implement new syntax ALTER SYSTEM as proposed in below mail
Could you suggest me what could be best way to proceed for this
patch?
I'm still in favor of some syntax involving ALTER, because it's
still
true that this behaves more like the existing GUC-setting commands
that use ALTER (which change configuration for future sessions)
rather
the ones that use SET (which change the current settings for some
period of time).
I will change the patch as per below syntax if there are no
objections:
ALTER SYSTEM SET configuration_parameter {TO | =} {value, |
'value'};
Modified patch contains:
1. Syntax implemented is
ALTER SYSTEM SET configuration_parameter {TO | =} {value, | 'value' |
DEFAULT};
If user specifies DEFAULT, it will remove entry from auto conf file.
2. File name to store settings set by ALTER SYSTEM command is still
persistent.auto.conf
Why? Shouldn't it just be auto.conf? Or system.auto.conf?
I had kept same name as it was decided for 9.3, but now as command has
changed so it makes more
sense to change name as well. I think it can be one of what you suggested or
postgresql.auto.conf.
I prefer auto.conf, personally.
Thanks. if no body has any other suggestion I will change it.
I think one of system.auto.conf or postgresql.auto.conf is more appropriate
because it either resembles command or existing configuration settings
file.
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
