Re: [HACKERS] 9.2 Cascading replication after slave promotion
On Tue, 14 Aug 2012 10:50:07 -0700 Josh Berkus j...@agliodbs.com wrote: Yeah, I think there's more people that agree with this use-case than you seem to think.. That said, I appreciate that it's not a trivial thing to support cleanly. Not trivial, no, but not major either. Really what needs to happen is for the timeline change record to get transmitted over the WAL stream. Hmmm. You know, I bet I could get stream-only remastering working in an unsafe way just by disabling the timeline checks. Time to test ... Isn't that, what recovery_target_timeline in the recovery.conf already does? It switches to the next timeline after a master migration. See http://www.postgresql.org/docs/current/static/recovery-target-settings.html for further information. -- 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] proposal - assign result of query to psql variable
Hello 2012/9/19 Shigeru HANADA shigeru.han...@gmail.com: On Fri, Aug 10, 2012 at 3:21 AM, Pavel Stehule pavel.steh...@gmail.com wrote: there is new version of this patch * cleaned var list parser * new regress tests * support FETCH_COUNT 0 Here are my review comments. Submission == The patch is formatted in context diff style, and it could be applied cleanly against latest master. This patch include document and tests, but IMO they need some enhancement. Usability = This patch provides new psql command \gset which sends content of query buffer to server, and stores result of the query into psql variables. The name \gset is mixture of \g, which sends result to file or pipe, and \set, which sets variable to some value, so it would sound natural to psql users. Freature test = Compile completed without warning. Regression tests for \gset passed, but I have some comments on them. - Other regression tests have comment -- ERROR just after queries which should fail. It would be nice to follow this manner. - Typo to few in expected file and source file. - How about adding testing \gset (no variable list) to should fail? - Is it intentional that \gset can set special variables such as AUTOCOMMIT and HOST? I don't see any downside for this behavior, because \set also can do that, but it is not documented nor tested at all. I use a same SetVariable function, so a behave should be same Document - Adding some description of \gset command, especially about limitation of variable list, seems necessary. - In addition to the meta-command section, Advanced features section mentions how to set psql's variables, so we would need some mention there too. - The term target list might not be familiar to users, since it appears in only sections mentioning PG internal relatively. I think that the feature described in the section Retrieving Query Results in ECPG document is similar to this feature. http://www.postgresql.org/docs/devel/static/ecpg-variables.html I invite any proposals about enhancing documentation. Personally I am a PostgreSQL developer, so I don't known any different term other than target list - but any user friendly description is welcome. Coding == The code follows our coding conventions. Here are comments for coding. - Some typo found in comments, please see attached patch. - There is a code path which doesn't print error message even if libpq reported error (PGRES_BAD_RESPONSE, PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR) in StoreQueryResult. Is this intentional? FYI, ecpg prints bad response message for those errors. yes - it is question. I use same pattern like PrintQueryResult, but bad response message should be used. I am sending updated patch Although I'll look the code more closely later, but anyway I marked the patch Waiting on Author for comments above. Regards, -- Shigeru HANADA gset_04.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] proposal - assign result of query to psql variable
Hi Pavel, (2012/09/21 2:01), Pavel Stehule wrote: - Is it intentional that \gset can set special variables such as AUTOCOMMIT and HOST? I don't see any downside for this behavior, because \set also can do that, but it is not documented nor tested at all. I use a same SetVariable function, so a behave should be same It seems reasonable. Document - Adding some description of \gset command, especially about limitation of variable list, seems necessary. - In addition to the meta-command section, Advanced features section mentions how to set psql's variables, so we would need some mention there too. - The term target list might not be familiar to users, since it appears in only sections mentioning PG internal relatively. I think that the feature described in the section Retrieving Query Results in ECPG document is similar to this feature. http://www.postgresql.org/docs/devel/static/ecpg-variables.html I invite any proposals about enhancing documentation. Personally I am a PostgreSQL developer, so I don't known any different term other than target list - but any user friendly description is welcome. How about to say stores the query's result output into variable? Please see attached file for my proposal. I also mentioned about 1-row limit and omit of variable. Coding == The code follows our coding conventions. Here are comments for coding. - Some typo found in comments, please see attached patch. - There is a code path which doesn't print error message even if libpq reported error (PGRES_BAD_RESPONSE, PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR) in StoreQueryResult. Is this intentional? FYI, ecpg prints bad response message for those errors. yes - it is question. I use same pattern like PrintQueryResult, but bad response message should be used. I am sending updated patch It seems ok. BTW, as far as I see, no psql backslash command including \setenv (it was added in 9.2) has regression test in core (I mean src/test/regress). Is there any convention about this issue? If psql backslash commands (or any psql feature else) don't need regression test, we can remove psql.(sql|out). # Of course we need to test new feature by hand. Anyway, IMO the name psql impresses larger area than the patch implements. How about to rename psql to psql_cmd or backslash_cmd than psql as regression test name? -- Shigeru HANADA diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 3693a5a..c4ac674 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1483,8 +1483,8 @@ testdb=gt; way. Use command\i/command for that.) This means that if the query ends with (or contains) a semicolon, it is immediately executed. Otherwise it will merely wait in the -query buffer; type semicolon, literal\g/ or literal\gset/literal to send it, or -literal\r/ to cancel. +query buffer; type semicolon, literal\g/ or +literal\gset/literal to send it, or literal\r/ to cancel. /para para @@ -1621,9 +1621,19 @@ Tue Oct 26 21:40:57 CEST 1999 listitem para -Sends the current query input buffer to the server and stores -the query's target list a corresponding list of psql -variables. + Sends the current query input buffer to the server and stores the + query's output into corresponding replaceable + class=parametervariable/replaceable. The preceding query must + return only one row, and the number of variables must be same as the + number of elements in commandSELECT/command list. If you don't + need any of items in commandSELECT/command list, you can omit + corresponding replaceable class=parametervariable/replaceable. + Example: +programlisting +foo=gt; SELECT 'hello', 'wonderful', 'world!' \gset var1,,var3 +foo=gt; \echo :var1 :var3 +hello world! +/programlisting /para /listitem /varlistentry -- 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_reorg in core?
Hi, I'm sakamoto, maintainer of reorg. What could be also great is to move the project directly into github to facilitate its maintenance and development. No argument from me there, especially as I have my own fork in github, but that's up to the current maintainers. Yup, I am thinking development on CVS(onPgfoundry) is a bit awkward for me and github would be a suitable place. To be honest, we have little available development resources, so no additional features are added recently. But features and fixes to be done piled up, which Josh sums up. In the short term, within this month I'll release minor versionup of reorg to support PostgreSQL 9.2. And I think it's the time to reconsider the way we maintain pg_reorg. It's happy that Josh and Michael are interested in reorg, and I wish you to be a maintainer :) I think we can discuss at reorg list. M.Sakamoto NTT OSS 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] pg_reorg in core?
(2012/09/22 11:01), sakamoto wrote: (2012/09/22 10:02), Christopher Browne wrote: If the present project is having a tough time doing enhancements, I should think it mighty questionable to try to draw it into core, that presses it towards a group of already very busy developers. On the other hand, if the present development efforts can be made more public, by having them take place in a more public repository, that at least has potential to let others in the community see and participate. There are no guarantees, but privacy is liable to hurt. I wouldn't expect any sudden huge influx of developers, but a steady visible stream of development effort would be mighty useful to a merge into core argument. A *lot* of projects are a lot like this. On the Slony project, we have tried hard to maintain this sort of visibility. Steve Singer, Jan Wieck and I do our individual efforts on git repos visible at GitHub to ensure ongoing efforts aren't invisible inside a corporate repo. It hasn't led to any massive of extra developers, but I am always grateful to see Peter Eisentraut's bug reports. Agreed. What reorg project needs first is transparency, including issue traking, bugs, listup todo items, clearfied release schedules, quarity assurance and so force. Only after all that done, the discussion to put them to core can be started. Until now, reorg is developed and maintained behind corporate repository. But now that its activity goes slow, what I should do as a maintainer is to try development process more public and finds someone to corporate with:) I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. As you pointed out, the problem here is that it's difficult to keep enough eyeballs and development resource on tiny projects outside the core. For examples, NTT OSSC has created lots of tools, but they're facing some difficulties to keep them being maintained because of their development resources. There're diffrent code repositories, different web sites, diffirent issus tracking system and different dev mailing lists, for different small projects. My xlogdump as well. Actually, that's the reason why it's difficult to keep enough eyeballs on small third-party projects. And also the reason why some developers want to push their tools into the core, isn't it? :) To solve this problem, I would like to have some umbrella project. It would be called pg dba utils, or something like this. This umbrella project may contain several third-party tools (pg_reorg, pg_rman, pg_filedump, xlogdump, etc, etc...) as its sub-modules. And also it may have single web site, code repository, issue tracking system and developer mailing list in order to share its development resource for testing, maintening and releasing. I think it would help third-party projects keep enough eyeballs even outside the core. Of course, if a third-party project has faster pace on its development and enough eyeballs to maintain, it's ok to be an independent project. However when a tool have already got matured with less eyeballs, it needs to be merged into this umbrella project. Any comments? Sakamoto -- Satoshi Nagayasu sn...@uptime.jp Uptime Technologies, LLC. http://www.uptime.jp -- 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_reorg in core?
2012/9/22 Satoshi Nagayasu sn...@uptime.jp: (2012/09/22 11:01), sakamoto wrote: (2012/09/22 10:02), Christopher Browne wrote: If the present project is having a tough time doing enhancements, I should think it mighty questionable to try to draw it into core, that presses it towards a group of already very busy developers. On the other hand, if the present development efforts can be made more public, by having them take place in a more public repository, that at least has potential to let others in the community see and participate. There are no guarantees, but privacy is liable to hurt. I wouldn't expect any sudden huge influx of developers, but a steady visible stream of development effort would be mighty useful to a merge into core argument. A *lot* of projects are a lot like this. On the Slony project, we have tried hard to maintain this sort of visibility. Steve Singer, Jan Wieck and I do our individual efforts on git repos visible at GitHub to ensure ongoing efforts aren't invisible inside a corporate repo. It hasn't led to any massive of extra developers, but I am always grateful to see Peter Eisentraut's bug reports. Agreed. What reorg project needs first is transparency, including issue traking, bugs, listup todo items, clearfied release schedules, quarity assurance and so force. Only after all that done, the discussion to put them to core can be started. Until now, reorg is developed and maintained behind corporate repository. But now that its activity goes slow, what I should do as a maintainer is to try development process more public and finds someone to corporate with:) I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. As you pointed out, the problem here is that it's difficult to keep enough eyeballs and development resource on tiny projects outside the core. For examples, NTT OSSC has created lots of tools, but they're facing some difficulties to keep them being maintained because of their development resources. There're diffrent code repositories, different web sites, diffirent issus tracking system and different dev mailing lists, for different small projects. My xlogdump as well. Actually, that's the reason why it's difficult to keep enough eyeballs on small third-party projects. And also the reason why some developers want to push their tools into the core, isn't it? :) To solve this problem, I would like to have some umbrella project. It would be called pg dba utils, or something like this. This umbrella project may contain several third-party tools (pg_reorg, pg_rman, pg_filedump, xlogdump, etc, etc...) as its sub-modules. And also it may have single web site, code repository, issue tracking system and developer mailing list in order to share its development resource for testing, maintening and releasing. I think it would help third-party projects keep enough eyeballs even outside the core. Of course, if a third-party project has faster pace on its development and enough eyeballs to maintain, it's ok to be an independent project. However when a tool have already got matured with less eyeballs, it needs to be merged into this umbrella project. Any comments? good idea Pavel Sakamoto -- Satoshi Nagayasu sn...@uptime.jp Uptime Technologies, LLC. http://www.uptime.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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_reorg in core?
On Fri, Sep 21, 2012 at 9:45 AM, M.Sakamoto sakamoto_masahiko...@lab.ntt.co.jp wrote: Hi, I'm sakamoto, maintainer of reorg. What could be also great is to move the project directly into github to facilitate its maintenance and development. No argument from me there, especially as I have my own fork in github, but that's up to the current maintainers. Yup, I am thinking development on CVS(onPgfoundry) is a bit awkward for me and github would be a suitable place. Hello Sakamoto-san I have created a reorg organization on github: https://github.com/reorg/ You are welcome to become one of the owners of the organization. I have already added Itagaki Takahiro as owner because he has a github account. If you open a github account or give me the email of one you own I will invite you as organization owner. Michael is also member of the organization. I have re-converted the original CVS repository as Michael's conversion was missing the commit email info, but I have rebased his commits on the new master. My intention is to track CVS commits into the cvs branch of the repos and merge them into the master, until official development is moved to git. The repository is at https://github.com/reorg/pg_reorg. Because I'm not sure yet about a few details (from the development model to the committers emails) it may be rebased in the near future, until everything has been decided. Thank you very much. -- Daniele -- 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] External Replication
Andres those involved in the 9.3 Command triggers: You may want to consider changing the command TRIGGER into a command FILTER and possibly post processing TRIGGER that is determined to be called INSIDE the FILTER. Or some way to pass information between the FILTER and the post processing trigger. Also, something information as to whether a series of statements was ROLLED BACK would be helpful. How to rebuild the parsetree so external replication could catch a clue that all of the last series of updates was actually rolled back! For example, I want to enforce a policy where all DROP TABLE commands become ALTER TABLE RENAME to SIGy All DROP TABLE Sig commands are not changed so that the SIG tables actually drop. (Almost like a TRASH Can for postgresql!) ALL TRUNCATE TABLE Become ALTER TABLE RENAME to SIGy AND THEN CREATE TABLE SELECT AS SIG This way, there is not possible way to have to enforce a Waiting period for replication to begin its work when errant DROP / TRUNCATES occur. All of this is important for Easy Setup/Initiation, Self-Healing, Fault Tolerant, Scaleable, INEXPENSIVE, External REPLICATION, a larger subject indeed. I want CLOUD behavior without CLOUD prices. Anybody who is working on the 9.3 COMMAND TRIGGER, drop me a note if you wish. marco On 9/21/2012 3:41 PM, Andres Freund wrote: Hi, On Friday, September 21, 2012 11:06:46 PM m...@rpzdesign.com wrote: Thanks for the link on the GIT patch code. I did a big major domo search and found some stuff related to command triggers: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php Look into the new event triggers started recently. - Are these command triggers currently in the 9.2.0 code base or is it in a alpha 9.2.1xxx? Its not in 9.2 and will only be in 9.3 Andres -- 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] [RFC][PATCH] wal decoding, attempt #2 - Design Documents
Hi all, Attached is the .txt and .pdf (both are imo readable and contain the same content) with design documentation about the proposed feature. Christan Kruse, Marko Tiikkaja and Hannu Krosing read the document and told me about my most egregious mistakes. Thanks! I would appreciate some feedback! Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services //-*- mode: adoc -*- = High Level Design for Logical Replication in Postgres = :copyright: PostgreSQL Global Development Group 2012 :author: Andres Freund, 2ndQuadrant Ltd. :email: and...@2ndquadrant.com == Introduction == This document aims to first explain why we think postgres needs another replication solution and what that solution needs to offer in our opinion. Then it sketches out our proposed implementation. In contrast to an earlier version of the design document which talked about the implementation of four parts of replication solutions: 1. Source data generation 1. Transportation of that data 1. Applying the changes 1. Conflict resolution this version only plans to talk about the first part in detail as it is an independent and complex part usable for a wide range of use cases which we want to get included into postgres in a first step. === Previous discussions === There are two rather large threads discussing several parts of the initial prototype and proposed architecture: - http://archives.postgresql.org/message-id/201206131327.24092.and...@2ndquadrant.com[Logical Replication/BDR prototype and architecture] - http://archives.postgresql.org/message-id/201206211341.25322.and...@2ndquadrant.com[Catalog/Metadata consistency during changeset extraction from WAL] Those discussions lead to some fundamental design changes which are presented in this document. === Changes from v1 === * At least a partial decoding step required/possible on the source system * No intermediate (schema only) instances required * DDL handling, without event triggers * A very simple text conversion is provided for debugging/demo purposes * Smaller scope == Existing approaches to replication in Postgres == If any currently used approach to replication can be made to support every use-case/feature we need, it likely is not a good idea to implement something different. Currently three basic approaches are in use in/around postgres today: . Trigger based . Recovery based/Physical footnote:[Often referred to by terms like Hot Standby, Streaming Replication, Point In Time Recovery] . Statement based Statement based replication has obvious and known problems with consistency and correctness making it hard to use in the general case so we will not further discuss it here. Lets have a look at the advantages/disadvantages of the other approaches: === Trigger based Replication === This variant has a multitude of significant advantages: * implementable in userspace * easy to customize * just about everything can be made configurable * cross version support * cross architecture support * can feed into systems other than postgres * no overhead from writes to non-replicated tables * writable standbys * mature solutions * multimaster implementations possible existing But also a number of disadvantages, some of them very hard to solve: * essentially duplicates the amount of writes (or even more!) * synchronous replication hard or impossible to implement * noticeable CPU overhead ** trigger functions ** text conversion of data * complex parts implemented in several solutions * not in core Especially the higher amount of writes might seem easy to solve at a first glance but a solution not using a normal transactional table for its log/queue has to solve a lot of problems. The major ones are: * crash safety, restartability spilling to disk * consistency with the commit status of transactions * only a minimal amount of synchronous work should be done inside individual transactions In our opinion those problems are restricting progress/wider distribution of these class of solutions. It is our aim though that existing solutions in this space - most prominently slony and londiste - can benefit from the work we are doing planning to do by incorporating at least parts of the changeset generation infrastructure. === Recovery based Replication === This type of solution, being built into postgres and of increasing popularity, has and will have its use cases and we do not aim to replace but to complement it. We plan to reuse some of the infrastructure and to make it possible to mix both modes of replication Advantages: * builtin * built on existing infrastructure from crash recovery * efficient ** minimal CPU, memory overhead on primary ** low amount of additional writes * synchronous operation mode * low maintenance once setup * handles DDL Disadvantages: * standbys are read only * no cross version support * no cross architecture support * no replication into foreign
Re: [HACKERS] alter enum add value if not exists
On 09/20/2012 06:34 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 08/23/2012 07:39 AM, Magnus Hagander wrote: It doesn't break, of course ,since it's protected by the unique index. But aren't you at risk of getting the very error message you're trying to avoid? Yeah, looking further this was probably a thinko on my part. Thanks for noticing. I've moved the test down so it's done right after the lock is acquired. Revised patch attached. This patch looks sane as far as it goes. It strikes me though that if we're going to invent an opt_if_not_exists production in the grammar, there are a lot of other places where it should be used too, for consistency if nothing else. However, it would be reasonable to do that mop-up as a separate commit. If you prefer, commit what you've got and then I'll see about the other thing. The enum piece is now committed. I agree cleaning this up would be a good idea. 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] [RFC][PATCH] wal decoding, attempt #2 - Design Documents (really attached)
Andres, nice job on the writeup. I think one aspect you are missing is that there must be some way for the multi-masters to re-stabilize their data sets and quantify any data loss. You cannot do this without some replication intelligence in each row of each table so that no matter how disastrous the hardware/internet failure in the cloud, the system can HEAL itself and keep going, no human beings involved. I am laying down a standard design pattern of columns for each row: MKEY - Primary key guaranteed unique across ALL nodes in the CLOUD with NODE information IN THE KEY. (A876543 vs B876543 or whatever)(network link UP or DOWN) CSTP - create time stamp on unix time stamp USTP - last update time stamp based on unix time stamp UNODE - Node that updated this record Many applications already need the above information, might as well standardize it so external replication logic processing can self heal. Postgresql tables have optional 32 bit int OIDs, you may want consider having a replication version of the ROID, replication object ID and then externalize the primary key generation into a loadable UDF. Of course, ALL the nodes must be in contact with each other not allowing signficant drift on their clocks while operating. (NTP is a starter) I just do not know of any other way to add self healing without the above information, regardless of whether you hold up transactions for synchronous or let them pass thru asynch. Regardless if you are getting your replication data from the WAL stream or thru the client libraries. Also, your replication model does not really discuss busted link replication operations, where is the intelligence for that in the operation diagram? Everytime you package up replication into the core, someone has to tear into that pile to add some extra functionality, so definitely think about providing sensible hooks for that extra bit of customization to override the base function. Cheers, marco On 9/22/2012 11:00 AM, Andres Freund wrote: This time I really attached both...
Re: [HACKERS] Draft release notes complete
Andrew,
Below is the patch that I mentioned at pgOpen. I'm pretty sure my
silly github pull request got screwed up anyway, so probably best to
ignore it. Regardless, please let me know what you think. I'd be
happy to rework it to operate off of a single hash, though I think
that would require having 'one true hash' of all possible steps and
it kind of looked like you were trying to avoid that.
Alvaro, assuming the patch is acceptable to everyone, it adds a
--only-steps option, which would let you simply say:
--only-steps=make-doc
To build the docs using the buildfarm.
Thanks,
Stephen
-- 8 --
Subject: [PATCH] Add --only-steps, improve --help and progress msgs
Adds a new '--only-steps' option, intended to be used for debugging and
for doc building (eg: --only=steps=make-doc). Also improved the --help
message to have more specifics about how to use --skip-steps and
--only-steps. Lastly, modified progress reporting to only report stages
which are actually run, instead of listing all stages even if some
aren't run.
---
PGBuild/Options.pm |6 +++-
run_build.pl | 69 ++--
2 files changed, 49 insertions(+), 26 deletions(-)
diff --git a/PGBuild/Options.pm b/PGBuild/Options.pm
index 64da7fc..05be6d5 100644
--- a/PGBuild/Options.pm
+++ b/PGBuild/Options.pm
@@ -22,7 +22,7 @@ BEGIN
@option_list =qw(
$forcerun $buildconf $keepall $help
$quiet $from_source $from_source_clean $testmode
- $test_mode $skip_steps $find_typedefs
+ $test_mode $skip_steps $only_steps $find_typedefs
$nosend $nostatus $verbose
);
}
@@ -41,7 +41,8 @@ our (
$forcerun, $buildconf, $keepall,
$help, $quiet, $from_source,
$from_source_clean, $testmode,$test_mode, $skip_steps,
-$find_typedefs,$nosend, $nostatus, $verbose,
+$only_steps, $find_typedefs,$nosend, $nostatus,
+$verbose,
);
my (%standard_options);
@@ -60,6 +61,7 @@ my (%standard_options);
'help' = \$help,
'quiet' = \$quiet,
'skip-steps=s' = \$skip_steps,
+'only-steps=s' = \$only_steps,
);
$buildconf = build-farm.conf; # default value
diff --git a/run_build.pl b/run_build.pl
index 1848153..958318b 100755
--- a/run_build.pl
+++ b/run_build.pl
@@ -96,6 +96,13 @@ if ($skip_steps =~ /\S/)
%skip_steps = map {$_ = 1} split(/\s+/,$skip_steps);
}
+my %only_steps;
+$only_steps ||= ;
+if ($only_steps =~ /\S/)
+{
+%only_steps = map {$_ = 1} split(/\s+/,$only_steps);
+}
+
use vars qw($branch);
my $explicit_branch = shift;
$branch = $explicit_branch || 'HEAD';
@@ -598,29 +605,34 @@ configure();
# module configure has to wait until we have built and installed the base
# so see below
-print time_str(),running make ...\n if $verbose;
+print time_str(),running make ...\n
+ if $verbose and !$skip_steps{'make'} and ($only_steps{'make'} or
!$only_steps);
make();
-print time_str(),running make check ...\n if $verbose;
+print time_str(),running make check ...\n
+ if $verbose and !$skip_steps{'check'} and ($only_steps{'check'} or
!$only_steps);
make_check();
unless ($using_msvc)
{
-print time_str(),running make contrib ...\n if $verbose;
+print time_str(),running make contrib ...\n
+ if $verbose and !$skip_steps{'make-contrib'} and
($only_steps{'make-contrib'} or !$only_steps);
make_contrib();
}
if (check_optional_step('build_docs'))
{
-print time_str(),running make doc ...\n if $verbose;
+print time_str(),running make doc ...\n
+ if $verbose and !$skip_steps{'make-doc'} and ($only_steps{'make-doc'}
or !$only_steps);
make_doc();
}
-print time_str(),running make install ...\n if $verbose;
+print time_str(),running make install ...\n
+ if $verbose and !$skip_steps{'install'} and ($only_steps{'install'} or
!$only_steps);
make_install();
@@ -628,7 +640,7 @@ make_install();
unless ($using_msvc)
{
print time_str(),running make contrib install ...\n
- if $verbose;
+ if $verbose and !$skip_steps{'install'} and ($only_steps{'install'} or
!$only_steps);
make_contrib_install();
}
@@ -643,7 +655,7 @@ process_module_hooks('install');
foreach my $locale (@locales)
{
-last if $skip_steps{install};
+last if $skip_steps{'install'} or (!$only_steps{'install'} and
$only_steps);
print time_str(),setting up db cluster ($locale)...\n if $verbose;
@@ -653,7 +665,8 @@ foreach my $locale (@locales)
start_db($locale);
-print time_str(),running make installcheck ($locale)...\n if $verbose;
+print time_str(),running make installcheck ($locale)...\n
+ if $verbose and !$skip_steps{'install-check'} and
($only_steps{'install-check'} or !$only_steps);
make_install_check($locale);
@@ -668,7 +681,8 @@ foreach my $locale (@locales)
stop_db($locale);
start_db($locale);
-print time_str(),running make isolation check ...\n if $verbose;
+
Re: [HACKERS] Caught up
On 3 September 2012 15:20, Bruce Momjian br...@momjian.us wrote: I spent the month of August going through 1.7k emails I had saved from the previous year that required my attention. I want to thank everyone for helping me close many open items, and for tolerating my questions. Ideally I would have dealt with many of these emails earlier, but time constraints caused the delay. On a bright note, August seems to be an ideal time to catch up, and I plan on doing a similar cleaning next August, hopefully with less backlog. On a related note, I apologize that many of these items didn't make it into 9.2, though they are committed for 9.3. The good news is that most of my work was in documentation improvements and clarifications, that could be backpatched to 9.2. I did not bring this up earlier because I didn't want to distract the work of making the improvements. If anyone wants to look at backpatching some of these doc changes into 9.2, I will not object. ;-) I am attaching a partial list of doc changes that might be considered. (I need to improve my commit messages that reference earlier commits by including the old commit tag; my apologies.) No one has mentioned backpatching so perhaps these are all too minor, which is fine. Again, many thanks. I feel much better without the weight of uncompleted items over my head. That's a huge number of amendments. Thanks for giving these some attention Bruce. -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Pushing restrictions down into GROUP BYs?
A while back, I posted a pathological minimal-case query where, in order to select one row from a users table, Postgres needed to scan the whole users table, because the restriction was not visible to the GROUP BY. At the time, Tom wrote: Don't hold your breath waiting for that to change. To do what you're wishing for, we'd have to treat the GROUP BY subquery as if it were an inner indexscan, and push a join condition into it. That's not even possible today. It might be possible after I get done with the parameterized-path stuff I've been speculating about for a couple of years now; but I suspect that even if it is possible, we won't do it for subqueries because of the planner-performance hit we'd take from repeatedly replanning the same subquery. http://archives.postgresql.org/pgsql-performance/2011-11/msg00131.php Given all the work that's been done on parameterized paths and LATERAL, is this something to revisit yet? In 9.3, it's at least possible to manually tweak the SRF, thanks to LATERAL. But it'd be great to allow set-returning functions to remain blissfully unaware of their surroundings. Modular code, Single Responsibility Principle, all that. I guess a more general question is: Are there cases where the planner can *use* LATERAL functionality to push down restrictions like this? (Do LATERAL and pushdown conceptually overlap? I think maybe they do.) Example code below - and before you say but you could just use 'where exists', trust me that the original queries were much more involved :) - drop schema if exists jaytest cascade; create schema jaytest; set search_path to jaytest; create table questions ( id int not null primary key, user_id int not null ); insert into questions select generate_series(1,1100), (random()*200)::int; create table users ( id int not null primary key ); insert into users select generate_series(1, 200); vacuum freeze analyze; create function srf() returns table (user_id int) as $$ select u.id from users as u group by u.id $$ language sql stable; -- Option 1: Use the set-returning function explain analyze select questions.id from questions join srf() on srf.user_id = questions.user_id where questions.id = 1; -- Option 2: Use the equivalent of the set-returning function -- (remove any doubts about function call overhead) explain analyze select questions.id from questions join ( select u.id as user_id from users as u group by u.id ) as srf1 on srf1.user_id = questions.user_id where questions.id = 1; -- Option 3: Now that we have LATERAL, manually -- push the join down into the set-returning function create function srf_lateral(questions_user_id int) returns table (user_id int) as $$ select u.id from users as u where u.id = questions_user_id group by u.id $$ language sql stable; explain analyze select questions.id from questions, lateral srf_lateral(questions.user_id) where questions.id = 1; drop schema jaytest cascade; - On my machine, options 1 and 2 usually have runtimes of 100-400 ms, and option 3 is about 0.04ms. -- 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] lock_timeout and common SIGALRM framework
Boszormenyi Zoltan z...@cybertec.at writes: new version with a lot more cleanup is attached. I looked at this patch, and frankly I'm rather dismayed. It's a mess. To start at the bottom level, the changes to PGSemaphoreLock broke it, and seem probably unnecessary anyway. As coded, calling the condition checker breaks handling of error returns from semop(), unless the checker is careful to preserve errno, which LmgrTimeoutCondition isn't (and really shouldn't need to be anyway). More, if the checker does return true, it causes PGSemaphoreLock to utterly violate its contract: it returns to the caller without having acquired the semaphore, and without even telling the caller so. Worse, if we *did* acquire the semaphore, we might still exit via this path, since the placement of the condition check call ignores the comment a few lines up: * Once we acquire the lock, we do NOT check for an interrupt before * returning. The caller needs to be able to record ownership of the lock * before any interrupt can be accepted. We could possibly fix all this with a redesigned API contract for PGSemaphoreLock, but frankly I do not see a good reason to be tinkering with it at all. We never needed to get it involved with deadlock check handling, and I don't see why that needs to change for lock timeouts. One very good reason why monkeying with PGSemaphoreLock is wrong is that on some platforms a SIGALRM interrupt won't interrupt the semop() call, and thus control would never reach the checker anyway. If we're going to throw an error, it must be thrown from the interrupt handler. The whole lmgrtimeout module seems to me to be far more mechanism than is warranted, for too little added functionality. In the first place, there is nothing on the horizon suggesting that we need to let any plug-in code get control here, and if anything the delicacy of what's going on leads me to not wish to expose such a possibility. In the second place, it isn't adding any actually useful functionality, it's just agglomerating some checks. The minimum thing I would want it to do is avoid calling timeout.c multiple times, which is what would happen right now (leading to four extra syscalls per lock acquisition, which is enough new overhead to constitute a strong objection to committing this patch at all). On the whole I think we could forget lmgrtimeout and just hardwire the lock timeout and deadlock check cases. But in any case we're going to need support in timeout.c for enabling/disabling multiple timeouts at once without extra setitimer calls. I'm also not thrilled about the way in which the existing deadlock checking code has been hacked up. As an example, you added this to DeadLockReport(): + if (!DeadLockTimeoutCondition()) + return; which again causes it to violate its contract, namely to report a deadlock, in the most fundamental way -- existing callers aren't expecting it to return *at all*. Surely we can decouple the deadlock and lock timeout cases better than that; or at least if we can't it's a delusion to propose anything like lmgrtimeout in the first place. There's considerable lack of attention to updating comments, too. For instance in WaitOnLock you only bothered to update the comment immediately adjacent to the changed code, and not the two comment blocks above that, which both have specific references to deadlocks being the reason for failure. Also, the per statement mode for lock timeout doesn't seem to be any such thing, because it's implemented like this: +case LOCK_TIMEOUT_PER_STMT: +enable_timeout_at(LOCK_TIMEOUT, +TimestampTzPlusMilliseconds( +GetCurrentStatementStartTimestamp(), +LockTimeout)); +break; That doesn't provide anything like you can spend at most N milliseconds waiting for locks during a statement. What it is is if you happen to be waiting for a lock N milliseconds after the statement starts, or if you attempt to acquire any lock more than N milliseconds after the statement starts, you lose instantly. I don't think that definition actually adds any useful functionality compared to setting statement_timeout to N milliseconds, and it's certainly wrongly documented. To do what the documentation implies would require tracking and adding up the time spent waiting for locks during a statement. Which might be a good thing to do, especially if the required gettimeofday() calls could be shared with what timeout.c probably has to do anyway at start and stop of a lock wait. But this code doesn't do it. Lastly, I'm not sure where is the best place to be adding the control logic for this, but I'm pretty sure postinit.c is not it. It oughta be somewhere under storage/lmgr/, no? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 18 September 2012 14:23, Amit kapila amit.kap...@huawei.com wrote: Please find the review of the patch. Thanks for the review. Attached is an updated patch, and I've include some responses to specific review comments below. Extra test cases that can be added to regression suite are as below: 1. where clause in view select statement I have modified my original test cases to include WHERE clauses in the view definitions and confirmed using EXPLAIN that they are picked up as expected by DML statements. 2. ORDER BY, FOR, FETCH. The parser turns FETCH FIRST/NEXT into LIMIT before the query reaches the rewriter, so I don't think there is much point having separate tests for those cases. 3. Temp views, views on temp tables. Yes that just works. 4. Target entry JOIN, VALUES, FUNCTION I added a test with VALUES in the rangetable. The JOIN case is already covered by the existing test with multiple base relations, and the FUNCTION case is covered by the ro_view12 test. 5. Toast column I see no reason why that would be a problem. It just works. 6. System view Most system views aren't updatable because they involve multiple base relations, expressions in the target list or functions in the rangetable. This doesn't seem like a particularly useful use-case. 7. Lateral and outer join This is covered by the existing test using multiple base relations. 8. auto increment columns 9. Triggers on tables 10.View with default values I've added these and they appear to work as I would expect. 11.Choosing base relation based on schema. 12.SECURITY DEFINER function execution These also work, but I'm not sure that the tests are proving anything useful. Code Review: 1. In test_auto_update_view function if (var-varattno == 0) return Views that refer to whole rows from the base relation are not updatable; I have a doubt that when the above scenario will cover? And the examples provided for whole row are working. This protects against a whole row reference in the target list (for example CREATE VIEW test_view AS SELECT base_tbl FROM base_tbl). The case that is allowed is a whole row reference in the WHERE clause. 2. In test_auto_update_view function if (base_rte-rtekind != RTE_RELATION) return Views that are not based on tables or views are not updatable; for view on sequences also the query is rewritten and giving error while executing. Is it possible to check for a particular relkind before rewriting query? Updated, so now it raises the error in the rewriter rather than the executor. 3. In function rewriteTargetView if (tle-resjunk || tle-resno = 0) continue; The above scenario is not possible as the junk is already removed in above condition and also the view which is refering to the system columns are not auto update views. OK, I've removed that check. The next test should catch anything unexpected that gets through. 4. In function rewriteTargetView if (view_tle == NULL) elog(ERROR, View column %d not found, tle-resno); The parsetree targetlist is already validated with view targetlist during transformstmt. Giving an ERROR is fine here? Shouldn't it be Assert? I think the elog(ERROR) is correct here, otherwise we'd be crashing. It ought to be impossible but it's not completely obvious that it can't somehow happen. 5. if any derived columns are present on the view, at least UPDATE operation can be allowed for columns other than derived columns. Yes, but I think that's the subject for another patch. In this patch, I'm just aiming to implement the SQL-92 feature. 6. name test_auto_update_view can be changed. The word test can be changed. OK, I've renamed it to is_view_auto_updatable(). 7. From function get_view_query(), error message : invalid _RETURN rule action specification might not make much sense to user who is inserting in a view. This is an internal elog() error, rather than a user-facing error. It should not happen in practice, unless perhaps the user has been messing with their system catalogs. Defects from test --- 1. With a old database and new binaries the following test code results in wrong way. CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl VALUES (1, 'Row 1'); INSERT INTO base_tbl VALUES (2, 'Row 2'); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views where table_name = 'rw_view1'; This will show is_insertable_into as 'no'. However below SQL statement is success INSERT INTO rw_view1 VALUES (3, 'Row 3'); That's because the information_schema needs updating in your old database, which I think means that the catalog version number needs to be bumped when/if it is committed. 2.
Re: [HACKERS] 64-bit API for large object
2012/9/22 Tatsuo Ishii is...@postgresql.org: Tom, Kaigai, Kohei KaiGai kai...@kaigai.gr.jp writes: Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Adding anything inside pqFunctionCall is useless, unless we were to add an int64 variant to PQArgBlock, which isn't a good idea because it will be an ABI break. The functions in fe-lobj.c have to set up the int64 value as if it were pass-by-reference, which means dealing with endianness concerns there. I just want to make sure you guy's point. We do not modify pqFunctionCall. That means PQfn does not accept PQArgBlock.isint != 0 and PQArgBlock.len == 8 case. If a PQfn caller wants to send 64-bit integer, it should set PQArgBlock.isint = 0 and PQArgBlock.len = 8 and set data pass-by-reference. Endianness should be taken care by the PQfn caller. Also we do not modify fe-misc.c because there's no point to add pqPutint64/pqGetint64(they are called from pqFunctionCall in the patch). Yes, it is exactly what I suggested. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] alter enum add value if not exists
Andrew Dunstan and...@dunslane.net writes: The enum piece is now committed. BTW, looking at that a second time ... the other CREATE IF NOT EXISTS options we have issue a NOTICE when skipping the CREATE action. Is there a reason this shouldn't do the same? 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] alter enum add value if not exists
On 09/22/2012 05:39 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The enum piece is now committed. BTW, looking at that a second time ... the other CREATE IF NOT EXISTS options we have issue a NOTICE when skipping the CREATE action. Is there a reason this shouldn't do the same? Not really, I guess we should for the sake of consistency, although TBH I find it just useless noise and rather wish we hadn't started the trend when we did the first DROP IF NOT EXISTS stuff. I'll add it. 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] alter enum add value if not exists
On 09/22/2012 11:49 PM, Andrew Dunstan wrote: On 09/22/2012 05:39 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The enum piece is now committed. BTW, looking at that a second time ... the other CREATE IF NOT EXISTS options we have issue a NOTICE when skipping the CREATE action. Is there a reason this shouldn't do the same? Not really, I guess we should for the sake of consistency, although TBH I find it just useless noise and rather wish we hadn't started the trend when we did the first DROP IF NOT EXISTS stuff. Time for a GUC existence_notice = none | exists | not_exists | all ? Cheers, Hannu Krosing -- 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] alter enum add value if not exists
Andrew Dunstan and...@dunslane.net writes: On 09/22/2012 05:39 PM, Tom Lane wrote: BTW, looking at that a second time ... the other CREATE IF NOT EXISTS options we have issue a NOTICE when skipping the CREATE action. Is there a reason this shouldn't do the same? I'll add it. I'm on it already. 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] alter enum add value if not exists
Hannu Krosing ha...@2ndquadrant.com writes: On 09/22/2012 11:49 PM, Andrew Dunstan wrote: Not really, I guess we should for the sake of consistency, although TBH I find it just useless noise and rather wish we hadn't started the trend when we did the first DROP IF NOT EXISTS stuff. Time for a GUC existence_notice = none | exists | not_exists | all Not another one :-( ... isn't client_min_messages good enough? We sort of had this discussion before w.r.t. the notices about creating primary key indexes etc. I wonder whether we should make a formal effort to split NOTICE message level into, say, NOTICE and NOVICE levels, where the latter contains all the training wheels stuff that experienced users would really rather not see. Or maybe just redefine NOTICE as meaning novice-oriented messages, and push anything that doesn't seem to fit that categorization into another existing message level? 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] alter enum add value if not exists
I wrote: ... It strikes me though that if we're going to invent an opt_if_not_exists production in the grammar, there are a lot of other places where it should be used too, for consistency if nothing else. BTW, I tried to do this and realized that it doesn't work, because IF is not a reserved word. The only way that opt_if_not_exists isn't ambiguous is if it must appear before something that's not an identifier, which is to say it works in ALTER TYPE ADD VALUE ... Sconst and nowhere else. Otherwise you have to spell it out with duplicate productions so that bison doesn't have to make a shift/reduce decision till it's seen the whole phrase. If we're ever forced to make IF reserved for other reasons, we could clean up a lot of both IF EXISTS and IF NOT EXISTS productions. There are other ways we could refactor the productions involved to reduce duplication; for instance I think that we could make it work for CREATE TABLE IF NOT EXISTS by defining a nonterminal that expands to either qualified_name or IF NOT EXISTS qualified_name. But that seems ugly enough to not be much of an improvement, not least because the nonterminal would need to return two separate pieces of info, and that's not terribly easy in bison. 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] alter enum add value if not exists
On 09/22/2012 07:05 PM, Tom Lane wrote: I wrote: ... It strikes me though that if we're going to invent an opt_if_not_exists production in the grammar, there are a lot of other places where it should be used too, for consistency if nothing else. BTW, I tried to do this and realized that it doesn't work, because IF is not a reserved word. The only way that opt_if_not_exists isn't ambiguous is if it must appear before something that's not an identifier, which is to say it works in ALTER TYPE ADD VALUE ... Sconst and nowhere else. Otherwise you have to spell it out with duplicate productions so that bison doesn't have to make a shift/reduce decision till it's seen the whole phrase. If we're ever forced to make IF reserved for other reasons, we could clean up a lot of both IF EXISTS and IF NOT EXISTS productions. There are other ways we could refactor the productions involved to reduce duplication; for instance I think that we could make it work for CREATE TABLE IF NOT EXISTS by defining a nonterminal that expands to either qualified_name or IF NOT EXISTS qualified_name. But that seems ugly enough to not be much of an improvement, not least because the nonterminal would need to return two separate pieces of info, and that's not terribly easy in bison. :-( I remember running into this when I did the DINE stuff. I was actually pleasantly surprised that it worked with the enum command. 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] pg_reorg in core?
On Sat, 2012-09-22 at 16:25 +0900, Satoshi Nagayasu wrote: I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. Well, that was pgfoundry, and it didn't work out. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Is PQexecParams() simply a wrapper function?
Hi All, I'm currently using PQexecParams() as part of a bridge to allow Postgres to be used in another programming language. Not specifying the OIDs simplifies things (for the end user), but bytea params still cause headaches, since they need to be formatted differently and I receive the data as a (char *) internally, but can't really infer a type myself, at least not reliably. It looks like PQprepare(), PQdescribePrepared() and PQexecPrepared() could be used to allow me to check if any of the params represent a BYTEAOID, then escape only those params. This seems like 3 round-trips to the server, however. That said, I believe PQexecParams() is doing a similar thing, in that it internally prepares a statement, then executes it (2 round trips). Or am I needlessly concerning myself over microseconds here? Cheers, Chris -- 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_reorg in core?
On Sat, Sep 22, 2012 at 7:45 PM, Peter Eisentraut pete...@gmx.net wrote: On Sat, 2012-09-22 at 16:25 +0900, Satoshi Nagayasu wrote: I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. Well, that was pgfoundry, and it didn't work out. There seem to be some efforts to update it, but yeah, the software behind it didn't age gracefully, and it seems doubtful to me that people will be flocking back to pgfoundry. The other ongoing attempt at an umbrella is PGXN, and it's different enough in approach that, while it's not obvious that it'll succeed, if it fails, the failure wouldn't involve the same set of issues that made pgfoundry problematic. PGXN notably captures metadata about the project; resources (e.g. - SCM) don't have to be kept there. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: proposal and patch : support INSERT INTO...RETURNING with partitioned table using rule
On 09/20/12 16:34, Tom Lane wrote:
John Lumby johnlu...@hotmail.com writes:
On Fri, 22 Jun 2012 09:55:13, Robert Haas wrote:
I do notice that the RETURNING clause of the INSERT can't reference
NEW, which seems like a restriction that we probably ought to lift,
but it doesn't seem to have much to do with your patch.
The main use of my proposal is to be able to return the value of the
sequence assigned to the NEW.id column, so yes that is a serious
restriction.
I think both of you are confused. What the RETURNING clause can see is
the inserted row's actual values. You can certainly get the assigned
sequence ID out of that. I would argue that being able to see the NEW.*
expressions is at best secondary, because that data doesn't necessarily
have anything to do with what went into the table (consider the
possibility that a BEFORE trigger changed it).
I think this part of the discussion was a bit of a
(probably confused) red herring going off on a tangent.
However, even if that restriction is lifted, it will not help with the
case where the rule is an invocation of a function, which is the case
I need.
What you're requesting seems pretty much nonsensical to me. The point
of being able to write a RETURNING clause in a rule is to emulate what
would happen with RETURNING on a regular table. As an example, suppose
that I have
create table t (id serial, data1 text, data2 text);
and for whatever reason I write
insert into t(data1, data2) values('foo', 'bar') returning id, data2;
I should get back the generated sequence value and the data2 value, but
*not* the data1 value. Anything else is just wrong. Now, if t has a
rule ON INSERT DO INSTEAD SELECT somefunction(), how is that going to
happen? The function doesn't know what the RETURNING clause looks like.
If we had a notional inserted-row-value then the executor could do the
RETURNING computation based on that, but there's no way to make a
connection between whatever the function does internally and the data
for RETURNING to chew on.
Well since you raise the question -- surely the function could return
a tuple of the correct row type and the executor could then pick out
whatever the actual statement requested. This actually seems to
make my proposal more general and useful. And answers the point
you make about doesn't play nice with RETURNING in your next para.
The whole concept of ON INSERT DO [INSTEAD/ALSO] SELECT seems pretty
shaky to me, as it *necessarily* involves a command substitution that
causes an INSERT to act in a strange fashion that the client application
will need special code to cope with. I won't argue to take the feature
out, because people do use it in custom applications --- but it doesn't
play nice with RETURNING, and I don't think it can be made to. It's
pretty much a legacy method of doing business IMO.
It seems to me that instead of lobbying to throw another kluge on top
of that pile, you'd be better off looking for alternative solutions.
Have you tried implementing this as an INSTEAD OF trigger, and not using
rules at all? That mechanism works just fine with RETURNING, and it
seems to me that it would let you do whatever you could do inside a
custom function. It would certainly be enough for the
dynamic-partition-redirection problem.
It took me a little while to realize your implicit suggestion that
I should rename my inheritance-parent (true name 'history')
as 'something_else' and then
CREATE VIEW history as select * from something_else
amd then create the instead trigger on the view.
(This *is* what you are suggesting, right?)
I tried t and yes indeed it does exactly what I want -
for the INSERT.Now I also have to define instead triggers
for update and delete. And are there any other considerations
for changing the table into a view?I mean, any other ways in which
SQL or client interfaces could perceive some difference?
Anyhow, yes, this does indeed serve as a solution to the problem
without needing any kluges or hacks, so thank you.
But it gives me (and anyone else who tries it) more work than
one simple RULE on the table without needing to add the view.
By the way - what is the reason for the restiction
that INSTEAD OF triggers cannot be defined on real tables,
only on views? Could this be lifted?
John Lumby
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] pg_reorg in core?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I think it's time to consider some *umbrella project* for maintaining several small projects outside the core. Well, that was pgfoundry, and it didn't work out. I'm not sure that is quite analogous to what was being proposed. I read it as more of let's package a bunch of these small utilities together into a single project, such that installing one installs them all (e.g. aptitude install pg_tools), and they all have a single bug tracker, etc. That tracker could be github, of course. I'm not convinced of the merit of that plan, but that's an alternative interpretation that doesn't involve our beloved pgfoundry. :) Oh, and -1 for putting it in core. Way too early, and not important enough. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201209222334 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlBeg/AACgkQvJuQZxSWSsjL5ACgimT71B4lSb1ELhgMw5EBzAKs xHIAn08vxGzmM6eSmDfZfxlJDTousq7h =KgXW -END PGP SIGNATURE- -- 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] 64-bit API for large object
2012/9/22 Tatsuo Ishii is...@postgresql.org: Tom, Kaigai, Kohei KaiGai kai...@kaigai.gr.jp writes: Tom, could you give us a suggestion which manner is better approach; whether the PQfn should have responsibility for endian translation of 64bit-integer, or callers (lo_tell64 or lo_seek64)? Adding anything inside pqFunctionCall is useless, unless we were to add an int64 variant to PQArgBlock, which isn't a good idea because it will be an ABI break. The functions in fe-lobj.c have to set up the int64 value as if it were pass-by-reference, which means dealing with endianness concerns there. I just want to make sure you guy's point. We do not modify pqFunctionCall. That means PQfn does not accept PQArgBlock.isint != 0 and PQArgBlock.len == 8 case. If a PQfn caller wants to send 64-bit integer, it should set PQArgBlock.isint = 0 and PQArgBlock.len = 8 and set data pass-by-reference. Endianness should be taken care by the PQfn caller. Also we do not modify fe-misc.c because there's no point to add pqPutint64/pqGetint64(they are called from pqFunctionCall in the patch). Yes, it is exactly what I suggested. Thanks for the confirmation! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Suggestion for --truncate-tables to pg_restore
On 09/21/2012 10:54:05 AM, Karl O. Pinc wrote: On 09/20/2012 12:24:49 PM, Karl O. Pinc wrote: I've had problems using pg_restore --data-only when restoring individual schemas (which contain data which has had bad things done to it). --clean does not work well because of dependent objects in other schemas. Since there wasn't much more to do I've gone ahead and written the patch. Works for me. Against git master. Passes regression tests, but there's no regression tests for pg_restore so this does not say much. Since there's no regression tests I've not written one. Since this is a real patch for application I've given it a new name (it's not a v2). Truncate done right before COPY, since that's what the parallel restores do. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- 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] Suggestion for --truncate-tables to pg_restore
Whoops. Do over. Sent the wrong file.
On 09/23/2012 12:19:07 AM, Karl O. Pinc wrote:
On 09/21/2012 10:54:05 AM, Karl O. Pinc wrote:
On 09/20/2012 12:24:49 PM, Karl O. Pinc wrote:
I've had problems using pg_restore --data-only when
restoring individual schemas (which contain data which
has had bad things done to it). --clean does not work
well because of dependent objects in other schemas.
Since there wasn't much more to do I've gone ahead
and written the patch. Works for me.
Against git master.
Passes regression tests, but there's no regression
tests for pg_restore so this does not say much.
Since there's no regression tests I've not written one.
Since this is a real patch for application I've given
it a new name (it's not a v2).
Truncate done right before COPY, since that's what
the parallel restores do.
Karl k...@meme.com
Free Software: You don't pay back, you pay forward.
-- Robert A. Heinlein
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index b276da6..11cba8e 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -539,6 +539,25 @@
/varlistentry
varlistentry
+ termoption--truncate-tables//term
+ listitem
+ para
+This option is only relevant when performing a data-only
+restore. It instructs applicationpg_restore/application
+to execute commands to truncate the target tables while the
+data is reloaded. Use this when restoring tables or schemas
+and option--clean/clean cannot be used because dependent
+objects would be destroyed.
+ /para
+
+ para
+ The option--disable-triggers/option will almost always
+ always need to be used in conjunction with this option to
+ disable check constraints on foreign keys.
+ /para
+ /varlistentry
+
+ varlistentry
termoption--use-set-session-authorization/option/term
listitem
para
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3b49395..0aaf1d3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -101,6 +101,8 @@ typedef struct _restoreOptions
int noTablespace; /* Don't issue tablespace-related commands */
int disable_triggers; /* disable triggers during data-only
* restore */
+ int truncate_tables; /* truncate tables during data-only
+ * restore */
int use_setsessauth;/* Use SET SESSION AUTHORIZATION commands
* instead of OWNER TO */
int no_security_labels; /* Skip security label entries */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 722b3e9..43b5806 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -311,6 +311,11 @@ RestoreArchive(Archive *AHX)
if (ropt-createDB ropt-dropSchema)
exit_horribly(modulename, -C and -c are incompatible options\n);
+ /* When the schema is dropped and re-created then no point
+ * truncating tables. */
+ if (ropt-dropSchema ropt-truncate_tables)
+ exit_horribly(modulename, -c and --truncate-tables are incompatible options\n);
+
/*
* -C is not compatible with -1, because we can't create a database inside
* a transaction block.
@@ -412,6 +417,10 @@ RestoreArchive(Archive *AHX)
}
}
+ /* Truncate tables only when restoring data. */
+ if (!ropt-dataOnly ropt-truncate_tables)
+ exit_horribly(modulename, --truncate-tables requires the --data-only option\n);
+
/*
* Setup the output file if necessary.
*/
@@ -553,6 +562,7 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te,
int retval = 0;
teReqs reqs;
bool defnDumped;
+ bool truncate;
AH-currentTE = te;
@@ -687,15 +697,22 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te,
* server, so no need to see if we should issue BEGIN.
*/
StartTransaction(AH);
+ truncate = 1;
+ } else
+ /* Truncate the table when asked to. */
+ truncate = ropt-truncate_tables;
+ if (truncate) {
/*
* If the server version is = 8.4, make sure we issue
* TRUNCATE with ONLY so that child tables are not
- * wiped.
+ * wiped. If we don't know the server version
+ * then err on the side of safety.
*/
ahprintf(AH, TRUNCATE TABLE %s%s;\n\n,
- (PQserverVersion(AH-connection) = 80400 ?
- ONLY : ),
+ (!AH-connection
+ || PQserverVersion(AH-connection)
+ = 80400 ? ONLY : ),
fmtId(te-tag));
}
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index f6c835b..c0b0bfc 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -77,6 +77,7 @@ main(int argc, char **argv)
static int disable_triggers = 0;
static int no_data_for_failed_tables = 0;
static int outputNoTablespaces = 0;
+ static int
