Re: [HACKERS] Immediate standby promotion
On Thu, Aug 14, 2014 at 1:49 PM, Fujii Masao masao.fu...@gmail.com wrote:
Hi,
I'd like to propose to add new option --immediate to pg_ctl promote.
When this option is set, recovery ignores any WAL data which have not
been replayed yet and exits immediately. Patch attached.
This promotion is faster than normal one but can cause data loss.
I think there is one downside as well for this proposal that
apart from data loss, it can lead to uncommitted data occupying
space in database which needs to be later cleaned by vacuum.
This can happen with non-immediate promote as well, but the
chances with immediate are more. So the gain we got by doing
immediate promotion can lead to slow down of operations in some
cases. It might be useful if we mention this in docs.
Few comments about patch:
1.
On standby we will see below message:
LOG: received promote request
User will always see above message irrespective of whether it
is immediate promote or any other mode of promote. I think it will
be better to distinguish between different modes and display the
appropriate message.
2.
StartupXLOG()
{
..
+ if (immediate_promote)
+ break;
..
}
Why are you doing this check after pause
(recoveryApplyDelay/recoveryPausesHere) for recovery?
Why can't we do it after ReadRecord()?
3.
! * of promote and immediate_promote
shouldn't in above sentence 'or' is more appropriate?
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
[HACKERS] Better support of exported snapshots with pg_dump
Hi all, Currently pg_dump does not allow a user to specify an exported snapshot name that he would like to use for a dump using SET TRANSACTION SNAPSHOT (now pg_export_snapshot is only used for parallel pg_dump within it). I imagine that this would be handy to take a consistent dump of a given database after creating a logical replication slot on it. Thoughts? Regards, -- Michael
Re: [HACKERS] What in the world is happening with castoroides and protosciurus?
On Sat, Aug 30, 2014 at 11:32 PM, Noah Misch n...@leadboat.com wrote: On Tue, Aug 26, 2014 at 10:17:05AM +0100, Dave Page wrote: On Tue, Aug 26, 2014 at 1:46 AM, Tom Lane t...@sss.pgh.pa.us wrote: For the last month or so, these two buildfarm animals (which I believe are the same physical machine) have been erratically failing with errors that reflect low-order differences in floating-point calculations. A recent example is at http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=protosciurusdt=2014-08-25%2010%3A39%3A52 where the only regression diff is *** /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/expected/hash_index.out Mon Aug 25 11:41:00 2014 --- /export/home/dpage/pgbuildfarm/protosciurus/HEAD/pgsql.22860/src/test/regress/results/hash_index.out Mon Aug 25 11:57:26 2014 *** *** 171,179 SELECT h.seqno AS i8096, h.random AS f1234_1234 FROM hash_f8_heap h WHERE h.random = '-1234.1234'::float8; ! i8096 | f1234_1234 ! ---+ ! 8906 | -1234.1234 (1 row) UPDATE hash_f8_heap --- 171,179 SELECT h.seqno AS i8096, h.random AS f1234_1234 FROM hash_f8_heap h WHERE h.random = '-1234.1234'::float8; ! i8096 |f1234_1234 ! ---+--- ! 8906 | -1234.12356777216 (1 row) UPDATE hash_f8_heap ... a result that certainly makes no sense. The results are not repeatable, failing in equally odd ways in different tests on different runs. This is happening in all the back branches too, not just HEAD. I have no idea what is causing the current issue - the machine is stable software-wise, and only has private builds of dependency libraries update periodically (which are not used for the buildfarm). If I had to hazard a guess, I'd suggest this is an early symptom of an old machine which is starting to give up. Agreed. Rerunning each animal against older commits would test that theory. Say, run against the last 6 months of REL9_0_STABLE commits. If those runs show today's failure frequencies instead of historic failure frequencies, it's not a PostgreSQL regression. Not that I see a commit back-patched near the time of the failure uptick (2014-08-06) that looks remotely likely to have introduced such a regression. It would be sad to lose our only buildfarm coverage of plain Solaris and of the Sun Studio compiler, but having buildfarm members this unstable is a pain. Perhaps have those animals retry the unreliable steps up to, say, 7 times? That would require changes to the buildfarm client. I'll see if I can find some alternate resources we can use. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Better support of exported snapshots with pg_dump
--On 1. September 2014 17:00:32 +0900 Michael Paquier michael.paqu...@gmail.com wrote: Currently pg_dump does not allow a user to specify an exported snapshot name that he would like to use for a dump using SET TRANSACTION SNAPSHOT (now pg_export_snapshot is only used for parallel pg_dump within it). I imagine that this would be handy to take a consistent dump of a given database after creating a logical replication slot on it. Thoughts? There was a discussion of this kind of feature some time ago here: http://www.postgresql.org/message-id/ca+u5nmk9+ttcff_-4mfdxwhnastauhuq7u7uedd57vay28a...@mail.gmail.com Not sure if all the arguments holds still true with the appearance of MVCC catalog scans. -- 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] implement subject alternative names support for SSL connections
On Fri, Aug 29, 2014 at 11:22 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/28/2014 07:28 PM, Alexey Klyukin wrote: On Mon, Aug 25, 2014 at 12:02 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/24/2014 03:11 PM, Alexey Klyukin wrote: On Wed, Aug 20, 2014 at 11:53 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: The patch doesn't seem to support wildcards in alternative names. Is that on purpose? Not really, we just did not have any use case for them, but it seems that RFC 5280 does not disallow them: Finally, the semantics of subject alternative names that include wildcard characters (e.g., as a placeholder for a set of names) are not addressed by this specification. Applications with specific requirements MAY use such names, but they must define the semantics. I've added support for them in the next iteration of the patch attached to this email. Hmm. So wildcards MAY be supported, but should we? I think we should follow the example of common browsers here, or OpenSSL or other SSL libraries; what do they do? Yes, they seem to be supported. The function you've mentioned above (X509_check_host) specifically mentions wildcards in SANs at https://www.openssl.org/docs/crypto/X509_check_host.html: 'X509_check_host() checks if the certificate Subject Alternative Name (SAN) or Subject CommonName (CN) matches the specified host name, which must be encoded in the preferred name syntax described in section 3.5 of RFC 1034. By default, wildcards are supported and they match only in the left-most label; but they may match part of that label with an explicit prefix or suffix. For example, by default, the host name ``www.example.com'' would match a certificate with a SAN or CN value of ``*.example.com'', ``w*.example.com'' or ``*w.example.com''.' RFC 6125 section 6.4.4 Checking of Common Names says: As noted, a client MUST NOT seek a match for a reference identifier of CN-ID if the presented identifiers include a DNS-ID, SRV-ID, URI-ID, or any application-specific identifier types supported by the client. So, to conform to that we shouldn't check the Common Name at all, if an alternative subject field is present. While the RFC indeed says so, the OpenSSL implementation includes X509_CHECK_FLAG_ALWAYS_CHECK_SUBJECT flag (which, as far as I can see, is set to 1 by default), which makes it check for the CN even if DNS names in SAN are present. I'm not sure what is the reason behind section 6.4.4, and I think it makes sense to check CN as well, since it does not lead to the case of false matches. Yeah, I think a certificate without CN should be supported. See also RFC 6125, section 4.1. Rules [for issuers of certificates]: 5. Even though many deployed clients still check for the CN-ID within the certificate subject field, certification authorities are encouraged to migrate away from issuing certificates that represent the server's fully qualified DNS domain name in a CN-ID. Therefore, the certificate SHOULD NOT include a CN-ID unless the certification authority issues the certificate in accordance with a specification that reuses this one and that explicitly encourages continued support for the CN-ID identifier type in the context of a given application technology. Certificates without a CN-ID are probably rare today, but they might start to appear in the future. Ok, I will change a patch to add support for this clause. BTW, should we also support alternative subject names in the server, in client certificates? And if there are multiple names, which one takes effect? Perhaps better to leave that for a separate patch. Good question. The RFC 5280 section 4.2.1.6 does not restrict the certificates to be used only server-side, so the same rules should be applicable to the client certs. I'm wondering if there is an equivalent of RFC 6125 for the clients? PostgreSQL, however, checks different things on the backends and the clients, the formers are checked against the DNS name, while on the clients only the user name is checked. For this, I think, a SAN section with some custom identity type should be issued (the 4.2.1.6 does not list user names as a pre-defined identify type). Note that PostgreSQL can already support clients with multiple names via the user maps, so support for SAN is not that urgent there. On the other hand, should we, in addition to verification of client user names, verify the client names supplied during connections against the DNS entries in their certificates? Are there use cases for this? I do agree this should be the subject of a separate patch. Regards, Alexey -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PL/pgSQL 2
Hi, For those of you who use PL/pgSQL every day, I'm quite certain you all feel there are a number of things you would like to change in the language, but realize it cannot be achieved without possibly breaking compatibility, at least in theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors These are small changes, probably possible with just a few hundred lines of code in total, which also should be the ambition, as larger changes would never survive during time as it would require too much efforts to keep up with the main project. Secondly, I trust plpgsql mainly because it's being used by a lot of people in a lot of production systems, the same would not hold true for plpgsql2 for the first years of existence, so we who would use it in production systems must understand every single line of code changed and feel the risk of possible bugs and their impact are within acceptable boundaries. I can probably think of a few more things, but these are the major annoyances. Please share your wish list of things you would want in plpgsql2 which are not possible to implement in plpgsql because they could possibly break compatibility. Regards, Joel
Re: [HACKERS] PL/PgSQL: EXIT USING ROLLBACK
On Sat, Jul 26, 2014 at 8:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Basically my point is that this just seems like inventing another way to do what one can already do with RAISE, and it doesn't have much redeeming social value to justify the cognitive load of inventing another construct. The main difference is with RAISE EXCEPTION 'OK'; you cannot know if it was *your* line of code which throw the 'OK'-exception or if it came from some other function which was called in the block of code. This means with the current way you have to inspect all lines of code in all functions in the entire call graph for the block of code for which you want to capture the 'OK'-exception (or whatever name one wishes to use), alternatively to use a name which is guaranteed to be unique, such as a UUID or something which no other line of code could possibly emmit as an exception. Both approaches are ugly and hackish. I think the language should provide a documented and safe way of exiting from a begin block without having to worry about other code raising exceptions of the same name. -- 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] PL/pgSQL 2
On 2014-09-01 11:04:53 +0200, Joel Jacobson wrote: For those of you who use PL/pgSQL every day, I'm quite certain you all feel there are a number of things you would like to change in the language, but realize it cannot be achieved without possibly breaking compatibility, at least in theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors -many. Look at the *disaster* the few minor changes in python3 were. It's now, years after, only starting to get used again. You're going to have to find a more gradual way of doing this. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Better support of exported snapshots with pg_dump
Hi, On 2014-09-01 10:25:58 +0200, Bernd Helmle wrote: --On 1. September 2014 17:00:32 +0900 Michael Paquier michael.paqu...@gmail.com wrote: Currently pg_dump does not allow a user to specify an exported snapshot name that he would like to use for a dump using SET TRANSACTION SNAPSHOT (now pg_export_snapshot is only used for parallel pg_dump within it). I imagine that this would be handy to take a consistent dump of a given database after creating a logical replication slot on it. Thoughts? Yes, I always wanted that option. There was a discussion of this kind of feature some time ago here: http://www.postgresql.org/message-id/ca+u5nmk9+ttcff_-4mfdxwhnastauhuq7u7uedd57vay28a...@mail.gmail.com I was never convinced of the reasoning in that thread. Possibly things have changed enough now that logical decoding is in core... Not sure if all the arguments holds still true with the appearance of MVCC catalog scans. I don't think they change anything here. The problem is the, pretty fundamental, problem that you need to know a relation exists before executing a LOCK ...; on it. During that time somebody can change the schema. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]
On Thu, Aug 28, 2014 at 9:34 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2014-08-28 14:22 GMT+02:00 Fujii Masao masao.fu...@gmail.com: On Thu, Aug 28, 2014 at 5:48 AM, Pavel Stehule pavel.steh...@gmail.com wrote: comments? +fprintf(output, _( ECHO control what input is written to standard output [all, queries]\n)); The valid values in the help messages should be consistent with the values that the tab-completion displays. So in the case of ECHO, errors and none also should be added in the message. Thought? In the help messages of some psql variables like ECHO_HIDDEN, valid values are not explained. Why not? it is based on http://www.postgresql.org/docs/9.4/static/app-psql.html ECHO_HIDDEN When this variable is set and a backslash command queries the database, the query is first shown. This way you can study the PostgreSQL internals and provide similar functionality in your own programs. (To select this behavior on program start-up, use the switch -E.) If you set the variable to the value noexec, the queries are just shown but are not actually sent to the server and executed. There are no clear a set of valid values :( .. When I found a known fields in doc, I used it. At least noexec seems to be documented as a valid value. Of course, it's better to document other valid values. 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] PL/pgSQL 2
On 09/01/2014 11:24 AM, Andres Freund wrote: On 2014-09-01 11:04:53 +0200, Joel Jacobson wrote: For those of you who use PL/pgSQL every day, I'm quite certain you all feel there are a number of things you would like to change in the language, but realize it cannot be achieved without possibly breaking compatibility, at least in theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors -many. Look at the *disaster* the few minor changes in python3 were. It's now, years after, only starting to get used again. You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. Cheers -- 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] Make LWLockAcquireCommon() inline?
On 2014-08-30 14:22:40 +0200, Andres Freund wrote: Hi, when profiling optimized builds (linux, gcc 4.9) it's currently LWLockAcquireCommon() showing up, not it's callers. Instruction level profiles show that the tests for valptr show up in profiles to some extent. Since most callers don't need the valptr logic it seems prudent to mark the function inline which will then eliminate the superflous branches. Arguments against? Done. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 9/1/14 11:53 AM, Hannu Krosing wrote: On 09/01/2014 11:24 AM, Andres Freund wrote: Look at the *disaster* the few minor changes in python3 were. It's now, years after, only starting to get used again. You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. I can't imagine how that would work for anyone who has thousands of functions. I've tried my best over the past ~year or so, but any attempts at breaking backwards compatibility have been rejected. I really don't see any gradual way of doing this. We either break things, live with what we have right now, or create a new language. .marko -- 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 11:24 AM, Andres Freund and...@2ndquadrant.com wrote: -many. Look at the *disaster* the few minor changes in python3 were. It's now, years after, only starting to get used again. You're going to have to find a more gradual way of doing this. I agree this is a valid concern, and is also something I've taken into account already. The things I would like to see change, won't break any of my own code, and probably not much of others either. Let's examine my list and see what impact if would have to modern plpgsql code written according to best practices: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. If you application already aims to modify 1 row exactly at most places in the code where you have UPDATE/INSERT/DELETE, then making that the default behaviour of the default syntax would be an improvement, as you wouldn't need special syntax for the default use-case. Instead, by providing a way to do the less common use-case of modifying 1 row, using an alternative way, we make it possible to rewrite any existing code. The amount of code we need to rewrite is minimized by optimizing the syntax for the most common use-case. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. This wouldn't have any impact for most applications, except when things go wrong, and if the query *would* return more than 1 row you probably want an error to be thrown, that's the most common use-case. By making it necessary to explicity say you are OK with more than 1 row, we again optimize the syntax for the most common use-case, but provide an alternative way of allowing the corner-case. + Change all warnings into errors The warnings should of course be errors, if we wouldn't need to worry about legacy code, which we don't with plpgsql2. In summary, if you write new code, it will work without any changes in most of the cases, and in the cases where you need to change it, there is a very good valid reason why you want to do that anyway, as the alternative is either insecure or ugly. Also, a stricter language with fewer alternative obsolete syntax variations and optional settings means the lines of code and the complexity can be reduced. Compared with the changes in python3, the changes between python2 and python3 *did* break compatibility and all code had to be rewritten. In contrast, most plpgsql functions won't need to be rewritten, only a few of them will need to be rewritten, which is acceptable. Another difference when comparing it to python3 is the possibility to mix plpgsql and plpgsql2 functions within the same system. With python3, you can't use existing python2 modules, but new plpgsql2 functions can of course call any existing plpgsql functions. The gradual way of doing this is to add plpgsql2, and then write all new code in the language, and then port function by function from plpgsql to plpgsql2, without any deadlines. -- 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] PL/pgSQL 2
On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote: On 9/1/14 11:53 AM, Hannu Krosing wrote: You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. I can't imagine how that would work for anyone who has thousands of functions. How's that fundamentally different from changing languages? If we had a way to *add* such attributes to *existing* functions I don't see the fundamental problem? I've tried my best over the past ~year or so, but any attempts at breaking backwards compatibility have been rejected. I really don't see any gradual way of doing this. We either break things, live with what we have right now, or create a new language. I think to some degree that was also influenced by the approach you took. Several of the changes didn't really have a meaningful explanation why they'd be helpful in the field. I.e. the change was explained, but not the reasoning *leading* to the change and which other solutions you thought about. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER SYSTEM RESET?
On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila amit.kapil...@gmail.com wrote:
On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao masao.fu...@gmail.com wrote:
The patch looks good to me. One minor comment is; probably you need to
update the tab-completion code.
Thanks for the review. I have updated the patch to support
tab-completion.
As this is a relatively minor change, I will mark it as
Ready For Committer rather than Needs Review.
Thanks for updating the patch!
One more minor comment is; what about applying the following change
for the tab-completion for RESET ALL? This causes the tab-completion of
even ALTER SYSTEM SET to display all and that's strange. But
the tab-completion of SET has already had the same problem. So
I think that we can live with that. Attached is the patch that I added
the following change onto your patch. Barring any objection, I will commit
the patch.
@@ -545,7 +545,8 @@ static const SchemaQuery Query_for_list_of_matviews = {
SELECT name FROM \
(SELECT pg_catalog.lower(name) AS name FROM pg_catalog.pg_settings \
WHERE context != 'internal') ss \
- WHERE substring(name,1,%d)='%s'
+ WHERE substring(name,1,%d)='%s'\
+ UNION ALL SELECT 'all' ss
Regards,
--
Fujii Masao
*** a/doc/src/sgml/ref/alter_system.sgml
--- b/doc/src/sgml/ref/alter_system.sgml
***
*** 22,27 PostgreSQL documentation
--- 22,30
refsynopsisdiv
synopsis
ALTER SYSTEM SET replaceable class=PARAMETERconfiguration_parameter/replaceable { TO | = } { replaceable class=PARAMETERvalue/replaceable | 'replaceable class=PARAMETERvalue/replaceable' | DEFAULT }
+
+ ALTER SYSTEM RESET replaceable class=PARAMETERconfiguration_parameter/replaceable
+ ALTER SYSTEM RESET ALL
/synopsis
/refsynopsisdiv
***
*** 30,39 ALTER SYSTEM SET replaceable class=PARAMETERconfiguration_parameter/replace
para
commandALTER SYSTEM/command writes the configuration parameter
!values to the filenamepostgresql.auto.conf/filename file. With
!literalDEFAULT/literal, it removes a configuration entry from
!filenamepostgresql.auto.conf/filename file. The values will be
!effective after reload of server configuration (SIGHUP) or in next
server start based on the type of configuration parameter modified.
/para
--- 33,44
para
commandALTER SYSTEM/command writes the configuration parameter
!values to the filenamepostgresql.auto.conf/filename file.
!Setting the parameter to literalDEFAULT/literal, or using the
!commandRESET/command variant, removes the configuration entry from
!filenamepostgresql.auto.conf/filename file. Use literalRESET
!ALL/literal to clear all configuration entries. The values will
!be effective after reload of server configuration (SIGHUP) or in next
server start based on the type of configuration parameter modified.
/para
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***
*** 411,417 static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type istmt insert_rest
! %type vsetstmt generic_set set_rest set_rest_more SetResetClause FunctionSetResetClause
%type node TableElement TypedTableElement ConstraintElem TableFuncElement
%type node columnDef columnOptions
--- 411,418
%type istmt insert_rest
! %type vsetstmt generic_set set_rest set_rest_more generic_reset reset_rest
! SetResetClause FunctionSetResetClause
%type node TableElement TypedTableElement ConstraintElem TableFuncElement
%type node columnDef columnOptions
***
*** 1579,1617 NonReservedWord_or_Sconst:
;
VariableResetStmt:
! RESET var_name
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET;
! n-name = $2;
! $$ = (Node *) n;
}
! | RESET TIME ZONE
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET;
! n-name = timezone;
! $$ = (Node *) n;
}
! | RESET TRANSACTION ISOLATION LEVEL
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET;
! n-name = transaction_isolation;
! $$ = (Node *) n;
}
! | RESET SESSION AUTHORIZATION
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET;
! n-name = session_authorization;
! $$ = (Node *) n;
}
! | RESET ALL
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET_ALL;
! $$ = (Node *) n;
}
;
--- 1580,1626
;
VariableResetStmt:
! RESET reset_rest { $$ = (Node *) $2; }
! ;
!
! reset_rest:
! generic_reset { $$ = $1; }
! | TIME ZONE
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind = VAR_RESET;
! n-name = timezone;
! $$ = n;
}
! | TRANSACTION ISOLATION LEVEL
{
VariableSetStmt *n = makeNode(VariableSetStmt);
n-kind
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 12:00 PM, Marko Tiikkaja wrote:
On 9/1/14 11:53 AM, Hannu Krosing wrote:
On 09/01/2014 11:24 AM, Andres Freund wrote:
Look at the *disaster* the few minor changes in python3 were. It's now,
years after, only starting to get used again.
You're going to have to find a more gradual way of doing this.
Probably a better way (and there has been some talk of it) is
having some kind of PRAGMA functionality, or pl/pgsql specific
LOCAL SET to affect just this function and not spill to nested
functions as is the case for SETs now.
I can't imagine how that would work for anyone who has thousands of
functions.
I've tried my best over the past ~year or so, but any attempts at
breaking backwards compatibility have been rejected. I really don't
see any gradual way of doing this. We either break things, live with
what we have right now, or create a new language.
.marko
My approach would be to add optional LOCAL modifier to WITH, so instead
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = }
default_expr ] [, ...] ] )
...
[ WITH ( attribute [, ...] ) ]
it would be
...
[ WITH ( [LOCAL] attribute [, ...] ) ]
where LOCAL attributes are _not_ inherited by nested functions
but the LOCALs would shadow globals in the function definitions
that have them.
I know it is easier said than done, but from the user perspective
this could be a nice clean approach.
Cheers
--
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] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel, Patch does look good to me. And found no issues as such. However here are my optional suggestions: 1. Frankly, I did not like name of the function row_to_json_pretty_choosy. Something like row_to_json_pretty_ignore_nulls seems better to me. 2. To use ignore nulls feature, I have to always pass pretty flag. Which seems weired. Since we do support named argument, can we avoid that? No idea how much difficult it is. If we have a default arguments to this function then we do not need one and two argument variations for this function as well. And we can use named argument for omitting the required one. Just a thought. Rest looks good to me. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] PL/pgSQL 2
On 9/1/14 12:12 PM, Andres Freund wrote: On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote: On 9/1/14 11:53 AM, Hannu Krosing wrote: You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. I can't imagine how that would work for anyone who has thousands of functions. How's that fundamentally different from changing languages? If we had a way to *add* such attributes to *existing* functions I don't see the fundamental problem? Adding 5-10 of these for every function you create seems significantly more painful than saying this function uses plpgsql2. Though perhaps what's being suggested is a *single* option which changes everything at once? Then there wouldn't be a huge difference. I've tried my best over the past ~year or so, but any attempts at breaking backwards compatibility have been rejected. I really don't see any gradual way of doing this. We either break things, live with what we have right now, or create a new language. I think to some degree that was also influenced by the approach you took. Several of the changes didn't really have a meaningful explanation why they'd be helpful in the field. I.e. the change was explained, but not the reasoning *leading* to the change and which other solutions you thought about. Yes, I agree I didn't always do a terrific job (see: EXIT USING ROLLBACK), but some of them have been outright rejected even though people clearly saw the value (I would put ASSERT into that category, and the change to SELECT .. INTO obviously belongs here). .marko -- 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 12:32 PM, Hannu Krosing ha...@2ndquadrant.com wrote: it would be ... [ WITH ( [LOCAL] attribute [, ...] ) ] where LOCAL attributes are _not_ inherited by nested functions but the LOCALs would shadow globals in the function definitions that have them. I know it is easier said than done, but from the user perspective this could be a nice clean approach. Drawbacks of that approach are mainly you would need to specify a lot of attributes for newly written functions to get the behaviour you want, and it also won't reduce the complexity of the language, rather the contrary, the more settings and the more alternative ways of doing things, the more complex will the implementation of the language become. This is why postgresql is surperiour to for instance mysql, as with mysql you need to set a lot of obscure settings to get the desired behaviour, like making 2014-03-31 an error instead of allow the invalid value. I wish plpgsql was just like postgresql, a very strict language. -- 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] PL/pgSQL 2
On 09/01/2014 05:24 PM, Andres Freund wrote: Look at the *disaster* the few minor changes in python3 were. It's now, years after, only starting to get used again. While that's valid, I'd like to point out that Python2 and Python3 don't share a runtime and can't easily use each others' modules and functions. The difference with any plpgsql2 is that it'd live within the same runtime - PostgreSQL - as plpgsql and the other languages, and would share the same interfaces. Python2 - Python3 would've been a lot less painful if you could mark, on a module-by-module basis, whether a module was python2 or python3 code. It wasn't very practical for Python because python code can reach deep into the guts of unrelated objects discovered at runtime - it can add/replace member functions, even hot-patch bytecode. That's not something we allow in PL/PgSQL, though; from the outside a PL/PgSQL function is pretty opaque to callers. That's not to say I'm keen on a plpgsql2 unless there's no other way; I'd be rather happier with language version pragmas or similar. But I don't think it's analogous to Python2 vs Python3. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 2014-09-01 12:49:22 +0200, Marko Tiikkaja wrote: On 9/1/14 12:12 PM, Andres Freund wrote: On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote: On 9/1/14 11:53 AM, Hannu Krosing wrote: You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. I can't imagine how that would work for anyone who has thousands of functions. How's that fundamentally different from changing languages? If we had a way to *add* such attributes to *existing* functions I don't see the fundamental problem? Adding 5-10 of these for every function you create seems significantly more painful than saying this function uses plpgsql2. Though perhaps what's being suggested is a *single* option which changes everything at once? Then there wouldn't be a huge difference. The likelihood of us now knowing all the things that we want to break rigth now seems about zero. There *will* be further ones. If we go with the approach of creating new language versions for all of them we'll end up with a completely unmaintainable mess. For PG devs, application dev and DBAs. Since what you seemingly want - sensibly so imo - is to set the default errors for *new* functions, but leave the old set of errors for preexisting ones, I suggest adding a GUC that defines the set of warnings/errors *new* functions get. There'd need to be some syntax to opt out for pg_dump and similar, but that sounds unproblematic. One question here imo is whether we design something for plpgsql or more generic... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Adding 'crosstab' variants returning refcursor?
Hi all Before I have a go at hacking it together I wanted to check: Has anyone explored modifying crosstab to return a refcursor, so you can FETCH the results w/o having to specify an explicit result type/descriptor? Consuming the input in another query is more of a pain, but it'd be infinitely nicer when you just want the results in a client. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrently option for reindexdb
On Wed, Aug 27, 2014 at 11:02 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Aug 26, 2014 at 5:12 PM, Andres Freund and...@anarazel.de wrote: On 2014-08-26 12:44:43 +0900, Michael Paquier wrote: I always was of the opinion that a exclusive lock is still *MUCH* better than what we have today. Well, if somebody has some interest in that, here is a rebased patch with the approach using low-level locks: http://www.postgresql.org/message-id/CAB7nPqRkwKFgn4BFUybqU-Oo-=gcbq0k-8h93gr6fx-ggrp...@mail.gmail.com My patch need to be improved doc and to be renamed option name (--minimum-locks?) Also I need to test, e.g., foreign key and primary key. Anyway, If REINDEX CONCURRENTLY patch Michael submitted is committed then I might need to rebase the patch (rather it's not necessary..?) So I will see how it goes for a while. Regards, --- Sawada Masahiko -- 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] Immediate standby promotion
On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila amit.kapil...@gmail.com wrote:
On Thu, Aug 14, 2014 at 1:49 PM, Fujii Masao masao.fu...@gmail.com wrote:
Hi,
I'd like to propose to add new option --immediate to pg_ctl promote.
When this option is set, recovery ignores any WAL data which have not
been replayed yet and exits immediately. Patch attached.
This promotion is faster than normal one but can cause data loss.
Thanks for reviewing the patch!
I think there is one downside as well for this proposal that
apart from data loss, it can lead to uncommitted data occupying
space in database which needs to be later cleaned by vacuum.
This can happen with non-immediate promote as well, but the
chances with immediate are more. So the gain we got by doing
immediate promotion can lead to slow down of operations in some
cases. It might be useful if we mention this in docs.
Yep, the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data of VACUUM. But, OTOH,
I think that the immediate promotion might be more likely to cause
the recovery to end before replaying WAL data which will generate
garbage data. So I'm not sure if it's worth adding that note to the doc.
Few comments about patch:
1.
On standby we will see below message:
LOG: received promote request
User will always see above message irrespective of whether it
is immediate promote or any other mode of promote. I think it will
be better to distinguish between different modes and display the
appropriate message.
Agreed. So I'm thinking to change the code as follows.
if (immediate_promote)
ereport(LOG, (errmsg(received immediate promote request)));
else
ereport(LOG, (errmsg(received promote request)));
Or we should name the normal promotion?
2.
StartupXLOG()
{
..
+ if (immediate_promote)
+ break;
..
}
Why are you doing this check after pause
(recoveryApplyDelay/recoveryPausesHere) for recovery?
Why can't we do it after ReadRecord()?
We can do that check either after ReadRecord() or after pause.
I preferred to add the check after pause because immediate promotion
would be likely to be requested while recovery is being paused.
In this case, if we do that check after ReadRecord(), we need to read
one more WAL record that actually we don't need.
BTW, in the current patch, when immediate promotion is requested while
recovery is being paused, the recovery keeps being paused until it's
manually resumed. But immediate promotion should cause even paused
recovery to end immediately?
3.
! * of promote and immediate_promote
shouldn't in above sentence 'or' is more appropriate?
Yep.
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] postgres_fdw behaves oddly
While working on [1], I've found that postgres_fdw behaves oddly:
postgres=# create foreign table ft (a int) server loopback options
(table_name 't');
CREATE FOREIGN TABLE
postgres=# select tableoid, * from ft;
tableoid | a
--+---
16400 | 1
(1 row)
postgres=# select tableoid, * from ft where tableoid = 16400;
tableoid | a
--+---
(0 rows)
I think that this is because (a) the qual that contains tableoid can be
sent to the remote as shown in the EXPLAIN output:
postgres=# explain verbose select tableoid, * from ft where tableoid =
16400;
QUERY PLAN
--
Foreign Scan on public.ft (cost=100.00..193.20 rows=2560 width=8)
Output: tableoid, a
Remote SQL: SELECT a FROM public.t WHERE ((tableoid = 16400::oid))
Planning time: 0.110 ms
(4 rows)
and because (b) the tableoid value can be differs between the local and
the remote. I think that one simple way of fixing such issues would be
to consider unsafe to send to the remote a qual that contains any system
columns (though we should probably give special treatment to quals
containing only ctid). With the modification of postgres_fdw, we get
the right result:
postgres=# select tableoid, * from ft where tableoid = 16400;
tableoid | a
--+---
16400 | 1
(1 row)
However, it's not complete enough. Here is another surising result
(note no tableoid column in the select list):
postgres=# select * from ft where tableoid = 16400;
a
---
(0 rows)
I think that this is because create_foreignscan_plan doesn't refer to
rel-baserestrictinfo when detecting whether any system columns are
requested. By the additional modification of create_foreignscan_plan,
we get the right result:
postgres=# select * from ft where tableoid = 16400;
a
---
1
(1 row)
I'd also like to propose to change the function so as to make reference
to rel-reltargetlist, not to attr_needed, to match the code with other
places. Please find attached a patch.
Thanks,
[1] https://commitfest.postgresql.org/action/patch_view?id=1386
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***
*** 243,248 foreign_expr_walker(Node *node,
--- 243,254
Var *var = (Var *) node;
/*
+ * System columns can't be sent to remote.
+ */
+ if (var-varattno 0)
+ return false;
+
+ /*
* If the Var is from the foreign table, we consider its
* collation (if any) safe to use. If it is from another
* table, we treat its collation the same way as we would a
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***
*** 20,25
--- 20,26
#include math.h
#include access/skey.h
+ #include access/sysattr.h
#include catalog/pg_class.h
#include foreign/fdwapi.h
#include miscadmin.h
***
*** 1945,1950 create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
--- 1946,1953
RelOptInfo *rel = best_path-path.parent;
Index scan_relid = rel-relid;
RangeTblEntry *rte;
+ Bitmapset *attrs_used = NULL;
+ ListCell *lc;
int i;
/* it should be a base rel... */
***
*** 1993,2008 create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
* bit of a kluge and might go away someday, so we intentionally leave it
* out of the API presented to FDWs.
*/
scan_plan-fsSystemCol = false;
for (i = rel-min_attr; i 0; i++)
{
! if (!bms_is_empty(rel-attr_needed[i - rel-min_attr]))
{
scan_plan-fsSystemCol = true;
break;
}
}
return scan_plan;
}
--- 1996,2030
* bit of a kluge and might go away someday, so we intentionally leave it
* out of the API presented to FDWs.
*/
+
+ /*
+ * Add all the attributes needed for joins or final output. Note: we must
+ * look at reltargetlist, not the attr_needed data, because attr_needed
+ * isn't computed for inheritance child rels.
+ */
+ pull_varattnos((Node *) rel-reltargetlist, rel-relid, attrs_used);
+
+ /* Add all the attributes used by restriction clauses. */
+ foreach(lc, rel-baserestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+ pull_varattnos((Node *) rinfo-clause, rel-relid, attrs_used);
+ }
+
+ /* Are any system columns requested from rel? */
scan_plan-fsSystemCol = false;
for (i = rel-min_attr; i 0; i++)
{
! if (bms_is_member(i - FirstLowInvalidHeapAttributeNumber, attrs_used))
{
scan_plan-fsSystemCol = true;
break;
}
}
+ bms_free(attrs_used);
+
return scan_plan;
}
--
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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 12:55 PM, Andres Freund and...@2ndquadrant.com wrote: The likelihood of us now knowing all the things that we want to break rigth now seems about zero. There *will* be further ones. If we go with the approach of creating new language versions for all of them we'll end up with a completely unmaintainable mess. For PG devs, application dev and DBAs. PL/pgSQL was added in 1998 (16 years ago). Compared this with again Python: 1994 Python 1.0 2000 Python 2.0 (6 years later) 2008 Python 3.0 (8 years later) Of course we don't know all the things we want to break in the *future*, but there is a good chance all users of PL/pgSQL know what they want to change *today*, thanks to the 16 years of active development in the language. In 16 years from now, maybe there is a need for PL/pgSQL 3, or maybe not, who knows. -- 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] PL/pgSQL 2
Hi 2014-09-01 11:04 GMT+02:00 Joel Jacobson j...@trustly.com: Hi, For those of you who use PL/pgSQL every day, I'm quite certain you all feel there are a number of things you would like to change in the language, but realize it cannot be achieved without possibly breaking compatibility, at least in theory. Even though you own code would survive the change, there might be code somewhere in the world which would break. This is of course not acceptable and that's why we have the current status quo of development, or at least not far away from a status quo. So instead of continue to adding optional settings to the config file, and instead of killing discussions around what can be done by bringing up the backwards-compatibility argument, let's instead fork the language and call it plpgsql2. Since no code is yet written in plpgsql2, we can start of from a clean sheet, and no good ideas need to be killed due to backwards-compatibility concerns. The interest for such a project is probably limited to a small number of companies/people around the world, as most users are probably perfectly happy with the current version of plpgsql, as they only use it occasionally and not every day like we do at my company. Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. From the top of my head, these are Things I personally would want to see in plpgsql2: + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1 row, as that's the most common use-case, and provide alternative syntax to modify multiple or zero rows. + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO STRICT only works if no rows should be an error, but there is currently no nice way if no rows OR exactly 1 row should be found by the query. + Change all warnings into errors These are small changes, probably possible with just a few hundred lines of code in total, which also should be the ambition, as larger changes would never survive during time as it would require too much efforts to keep up with the main project. Secondly, I trust plpgsql mainly because it's being used by a lot of people in a lot of production systems, the same would not hold true for plpgsql2 for the first years of existence, so we who would use it in production systems must understand every single line of code changed and feel the risk of possible bugs and their impact are within acceptable boundaries. I can probably think of a few more things, but these are the major annoyances. Please share your wish list of things you would want in plpgsql2 which are not possible to implement in plpgsql because they could possibly break compatibility. I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. Almost all from your mentioned issue can be solved by some extensions with some new hooks. I don't agree, so UPDATE/INSERT/DELETE should to work only with one row. What I dislike on plpgsql: * manipulation with expressions * supply lot of SPI API in plpgsql * inconsistent internal casting to target / returned values * missing internal API for more stricter / smarted validation * strange week implementation of left part of assign statement * sometimes strange work with composite types * late IO casting on second hand it is fast practical language. Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. My plan .. maybe too long enhancing SPI to better expression support new PL API for support variables stack and handling variables new API with communication with gdb implementation of SQL/PSM .. it is new language .. based on relative good ANSI SQL specification without compatibility issues reimplementation plpgsql based on new API .. it should to significantly reduce size otherwise plpgsql2 is wrong name .. with respect to your goals it should be stricter plpgsql Regards Pavel Regards, Joel
Re: [HACKERS] PL/pgSQL 2
2014-09-01 13:30 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 12:55 PM, Andres Freund and...@2ndquadrant.com wrote: The likelihood of us now knowing all the things that we want to break rigth now seems about zero. There *will* be further ones. If we go with the approach of creating new language versions for all of them we'll end up with a completely unmaintainable mess. For PG devs, application dev and DBAs. PL/pgSQL was added in 1998 (16 years ago). Compared this with again Python: 1994 Python 1.0 2000 Python 2.0 (6 years later) 2008 Python 3.0 (8 years later) Of course we don't know all the things we want to break in the *future*, but there is a good chance all users of PL/pgSQL know what they want to change *today*, thanks to the 16 years of active development in the language. In 16 years from now, maybe there is a need for PL/pgSQL 3, or maybe not, who knows. For lot of people is Python3 big fail - and it can be much more dangerous for Postgres than for much more larger Python community. I don't see a necessity to do again. I have very good knowledge about users in Czech, and probably only I know a limits of plpgsql. I am thinking so some enhancing of plpgsql (extensions, extra errors, extra warnings) is possible. Regards Pavel -- 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] Adding 'crosstab' variants returning refcursor?
Hi 2014-09-01 13:08 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: Hi all Before I have a go at hacking it together I wanted to check: Has anyone explored modifying crosstab to return a refcursor, so you can FETCH the results w/o having to specify an explicit result type/descriptor? +1 Consuming the input in another query is more of a pain, but it'd be infinitely nicer when you just want the results in a client. I wrote proof concept http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html Regards Pavel -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alter user set local_preload_libraries.
Hello,
I found this issue when trying per-pg_user (role) loading of
auto_analyze and some tweaking tool. It is not necessarily set by
the user by own, but the function to decide whether to load some
module by the session-user would be usable, at least, as for me:)
I think we could just set local_preload_libraries to PGC_USERSET and
document that subsequent changes won't take effect. That's the same way
session_preload_libraries works. That would avoid inventing another
very specialized GUC context.
It is enough for me. Since the only advantage of
PGC_BACKEND_USERSET is the capability to inhibit in-session
modification and I don't see another use case for it, I have no
objection for your opinion.
If you're interested in improving this area, I also suggest you read the
thread of
http://www.postgresql.org/message-id/1349829917.29682.5.ca...@vanquo.pezone.net.
Although I don't understand even after reading this why
local_preload_libraries was PGC_SUSET, there seems to be no
reason it should be so.
The attached patch simply changes the context for local_... to
PGC_USERSET and edits the doc.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 49547ee..8803709 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -6052,14 +6052,16 @@ SET XML OPTION { DOCUMENT | CONTENT };
listitem
para
This variable specifies one or more shared libraries that are to be
-preloaded at connection start. This parameter cannot be changed after
-the start of a particular session. If a specified library is not
+preloaded at connection start. This option is effective only when it
+is set at session start via commandALTER USER ... SET/ command (or
+postgresq.conf) so changing this variable after the start of a
+particular session has no effect. If a specified library is not
found, the connection attempt will fail.
/para
para
-This option can be set by any user. Because of that, the libraries
-that can be loaded are restricted to those appearing in the
+Since non-supersers are allowed to set it, the libraries that can be
+loaded are restricted to those appearing in the
filenameplugins/ subdirectory of the installation's
standard library directory. (It is the database administrator's
responsibility to ensure that only quotesafe/ libraries
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a8a17c2..f128f32 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2895,7 +2895,7 @@ static struct config_string ConfigureNamesString[] =
},
{
- {local_preload_libraries, PGC_BACKEND, CLIENT_CONN_PRELOAD,
+ {local_preload_libraries, PGC_USERSET, CLIENT_CONN_PRELOAD,
gettext_noop(Lists unprivileged shared libraries to preload into each backend.),
NULL,
GUC_LIST_INPUT | GUC_LIST_QUOTE
--
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_receivexlog and replication slots
On Sun, Aug 31, 2014 at 10:45 PM, Magnus Hagander mag...@hagander.net wrote:
As this is a number of patches rolled into one - do you happen to keep
them separate in your local repo? If so can you send them as separate
ones (refactor identify_system should be quite unrelated to supporting
replication slots, right?), for easier review? (if not, I'll just
split them apart mentally, but it's easier to review separately)
Thanks for your review!
OK, here are 2 patches, the 2nd needing the 1st one:
1) Refactor IDENTIFY_SYSTEM and replslot create/drop APIs
2) Support for --create and --drop in pg_receivexlog
On the identify_system part - my understanding of the code is that
what you pass in as num_cols is the number of columns required for it
to work, right?
The argument is I would say cross-version compatibility and
consistency with the existing 9.4 code, but... (see below for the rest
of the story).
We probably need to adjust the error message as well
in that case, because it's no longer what's expected, it's what's
required?
OK, changed this way.
And we might want to include a hint about the reason (wrong version)?
I am not sure about that, a simple error message looks fine IMO, and
there is no notion of error hinting in the other client utilities as
well.
There's also a note get LSN start position if necessary, but it
tries to do it unconditionally. What is the if necessary supposed to
refer to?
That's remnant of some old code, so I removed it. Thanks for spotting that.
Actually - why do we even care about the 3 vs 4 in RunIdentifySystem,
as it never actually looks at the 4th column anyway? If we do
specifically want it to fail in the case of pg_recvlogical, we really
need to think up a better error message for it, and perhaps a
different way of specifying it?
Hm. I'd vote to simplify the code a bit based on the argument that the
current API only looks at the 3 first columns and does not care about
the 4th which is the plugin name.
Do we really want those Asserts? There is not a single Assert in
bin/pg_basebackup today - as is the case for most things in bin/. We
typically use regular if statements for things that can happen, and
just ignore the others I think - since the callers are fairly simple
to trace.
OK, removed.
Regards,
--
Michael
From fdca8988480cac602157c3ae24ae61311bdaf960 Mon Sep 17 00:00:00 2001
From: Michael Paquier mich...@otacoo.com
Date: Mon, 1 Sep 2014 20:48:43 +0900
Subject: [PATCH 1/2] Refactoring of pg_basebackup utilities
Code duplication is reduced with the introduction of new APIs for each
individual replication command:
- IDENTIFY_SYSTEM
- CREATE_REPLICATION_SLOT
- DROP_REPLICATION_SLOT
A couple of variables used to identify a timeline ID are changed as well
to be more consistent with core code.
---
src/bin/pg_basebackup/pg_basebackup.c | 21 +
src/bin/pg_basebackup/pg_receivexlog.c | 49 +++-
src/bin/pg_basebackup/pg_recvlogical.c | 119 +--
src/bin/pg_basebackup/streamutil.c | 142 +
src/bin/pg_basebackup/streamutil.h | 9 +++
5 files changed, 183 insertions(+), 157 deletions(-)
diff --git a/src/bin/pg_basebackup/pg_basebackup.c b/src/bin/pg_basebackup/pg_basebackup.c
index 8b9acea..49675cf 100644
--- a/src/bin/pg_basebackup/pg_basebackup.c
+++ b/src/bin/pg_basebackup/pg_basebackup.c
@@ -1569,8 +1569,8 @@ BaseBackup(void)
{
PGresult *res;
char *sysidentifier;
- uint32 latesttli;
- uint32 starttli;
+ TimeLineID latesttli;
+ TimeLineID starttli;
char *basebkp;
char escaped_label[MAXPGPATH];
char *maxrate_clause = NULL;
@@ -1624,23 +1624,8 @@ BaseBackup(void)
/*
* Run IDENTIFY_SYSTEM so we can get the timeline
*/
- res = PQexec(conn, IDENTIFY_SYSTEM);
- if (PQresultStatus(res) != PGRES_TUPLES_OK)
- {
- fprintf(stderr, _(%s: could not send replication command \%s\: %s),
-progname, IDENTIFY_SYSTEM, PQerrorMessage(conn));
+ if (!RunIdentifySystem(conn, sysidentifier, latesttli, NULL))
disconnect_and_exit(1);
- }
- if (PQntuples(res) != 1 || PQnfields(res) 3)
- {
- fprintf(stderr,
-_(%s: could not identify system: got %d rows and %d fields, expected %d rows and %d or more fields\n),
-progname, PQntuples(res), PQnfields(res), 1, 3);
- disconnect_and_exit(1);
- }
- sysidentifier = pg_strdup(PQgetvalue(res, 0, 0));
- latesttli = atoi(PQgetvalue(res, 0, 1));
- PQclear(res);
/*
* Start the actual backup
diff --git a/src/bin/pg_basebackup/pg_receivexlog.c b/src/bin/pg_basebackup/pg_receivexlog.c
index a8b9ad3..f722374 100644
--- a/src/bin/pg_basebackup/pg_receivexlog.c
+++ b/src/bin/pg_basebackup/pg_receivexlog.c
@@ -253,21 +253,10 @@ FindStreamingStart(uint32 *tli)
static void
StreamLog(void)
{
- PGresult *res;
XLogRecPtr startpos;
- uint32 starttli;
+ TimeLineID starttli;
XLogRecPtr serverpos;
- uint32 servertli;
- uint32 hi,
-lo;
-
- /*
- * Connect in replication mode to the server
- */
- conn =
Re: [HACKERS] pg_receivexlog and replication slots
On 2014-09-01 20:58:29 +0900, Michael Paquier wrote: On Sun, Aug 31, 2014 at 10:45 PM, Magnus Hagander mag...@hagander.net wrote: As this is a number of patches rolled into one - do you happen to keep them separate in your local repo? If so can you send them as separate ones (refactor identify_system should be quite unrelated to supporting replication slots, right?), for easier review? (if not, I'll just split them apart mentally, but it's easier to review separately) Thanks for your review! OK, here are 2 patches, the 2nd needing the 1st one: 1) Refactor IDENTIFY_SYSTEM and replslot create/drop APIs 2) Support for --create and --drop in pg_receivexlog On the identify_system part - my understanding of the code is that what you pass in as num_cols is the number of columns required for it to work, right? The argument is I would say cross-version compatibility and consistency with the existing 9.4 code, but... (see below for the rest of the story). I don't really see a need to that for slot specific code. The locations where we more lenient are ones where 9.4 is ok, or a better message is following shortly afterwards. We probably need to adjust the error message as well in that case, because it's no longer what's expected, it's what's required? OK, changed this way. The reason for the formulation of the current error message is that it's the same across all callsites emitting it to make it easier for translators. It used to be more specific at some point and then was changed. Since these aren't expected to be hit much I don't really see a need to be very detailed. And we might want to include a hint about the reason (wrong version)? I am not sure about that, a simple error message looks fine IMO, and there is no notion of error hinting in the other client utilities as well. Agreed. Actually - why do we even care about the 3 vs 4 in RunIdentifySystem, as it never actually looks at the 4th column anyway? If we do specifically want it to fail in the case of pg_recvlogical, we really need to think up a better error message for it, and perhaps a different way of specifying it? Hm. I'd vote to simplify the code a bit based on the argument that the current API only looks at the 3 first columns and does not care about the 4th which is the plugin name. Why not return all four columns from RunIdentifySystem(), setting plugin to NULL if not available. Then the caller can error out. Greetings, Andres Freund -- 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] LIMIT for UPDATE and DELETE
On 8/29/14 4:33 PM, Tom Lane wrote: So either it has to be inside ModifyTable or the ModifyTable has to somehow pass something to a Limit node on top of it ... or we add a LockRows node below the Limit node. Yeah, that would make UPDATE/LIMIT a tad slower, but I think that might be preferable to what you're proposing anyway. Raw speed of what is fundamentally a fringe feature ought not trump every other concern. I don't consider this a fringe feature, but in any case, the main use case for LIMIT without ORDER BY in UPDATE and DELETE is to split up large transactions into smaller batches. And considering that, I think raw speed should be a concern (though it shouldn't trump every other concern, obviously). More to the point, personally, I think the changes to nodeModifyTable.c are very reasonable so it's not clear to me that the extra LockRows+Limit nodes approach would be inherently better (even ignoring performance concerns). This is just my personal opinion, but what I think should happen is: 2) We allow ORDER BY on tables with no inheritance children using something similar to Rukh's previous patch. 3) Someone rewrites how UPDATE works based on Tom's suggestion here: http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us, I still think we should skip #2 and go directly to work on #3. Getting rid of the unholy mess that is inheritance_planner would be a very nice thing. Ideally? Yeah, that would be great. But I don't see anyone volunteering to do that work, and I think holding back a useful feature (ORDER BY with UPDATE/DELETE) in hopes of getting someone to volunteer to do it is insane. Now, you're free to argue that ORDER BY with UPDATE/DELETE isn't that useful, of course, but I'm sure there are lots of people who agree with me. .marko -- 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. ... Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. Do you argue the introduction of plpgsql2 would hurt the users of plpgsql in some way? How? If you have X% who continue to happily use plpgsql, and (100-X%) who find they can use plpgsql2 in their project, for new functions or all functions (for a new project), then you have made (100-X)% of the users more happy, than they would be if they were forced to use plpgsql and suffer from its problems. It *would* be a problem if you had to choose between writing all functions in their plpgsql or plpgsql2, but thanks to postgres support for different pl-languages and mixing different languages in the same project, I cannot see the problem. implementation of SQL/PSM .. it is new language .. based on relative good ANSI SQL specification without compatibility issues reimplementation plpgsql based on new API .. it should to significantly reduce size A new language like SQL/PSM would be helpful for new projects, but personally I have a huge code base written in plpgsql which I would at some point want to port to plpgsql2, and the least time consuming way of doing so would be to make sure most existing plpgsql-functions require no modifications at all to work with plpgsql2. A new language would mean I would have to rewrite all functions, which is much worse than doing no or minor modifications to existing functions. otherwise plpgsql2 is wrong name .. with respect to your goals it should be stricter plpgsql I think plpgsql2 is a perfect name for it, because it is a new version of plpgsql, based on all the empirical knowledge gained from the 16 years of development in plpgsql. And while most improvements fall in the stricter category, there are probably other things which we would want to change when having the possibility of breaking compatibility. I think the main difference in what is possible with plpgsql2 compared to improvements of plpgsql, boil down to not having to evaluate any proposed change against could this break compatibility in theory? but instead will this most certainly break compatilibity for most users?. Today, if a proposed code change in plpgsql would have an impact 0%, the change is rejected. With plpgsql2, maybe we could allow an impact of X% of lines of code. If greater than X%, users will think it's unrealistic to port all their code from plpgsql to plpgsql2, which might be a long-term realistic requirement for some users, especially for the project, as in Y years from now, maybe the development of plpgsql can be put to halt, to avoid having to maintain both code bases, which *is* undoubtably an increased workload for the project. Also, all your work and effort with plpgsql_check_function() would be a natural fit for plpgsql2, the problems it detect should of course be errors by default in the language. -- 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] PL/pgSQL 2
2014-09-01 14:27 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. ... Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. Do you argue the introduction of plpgsql2 would hurt the users of plpgsql in some way? How? yes, anybody who has thousands lines in plpgsql will be messy, when we publish so there will be new not fully compatible plpgsql2. If you have X% who continue to happily use plpgsql, and (100-X%) who find they can use plpgsql2 in their project, for new functions or all functions (for a new project), then you have made (100-X)% of the users more happy, than they would be if they were forced to use plpgsql and suffer from its problems. It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? It *would* be a problem if you had to choose between writing all functions in their plpgsql or plpgsql2, but thanks to postgres support for different pl-languages and mixing different languages in the same project, I cannot see the problem. implementation of SQL/PSM .. it is new language .. based on relative good ANSI SQL specification without compatibility issues reimplementation plpgsql based on new API .. it should to significantly reduce size A new language like SQL/PSM would be helpful for new projects, but personally I have a huge code base written in plpgsql which I would at some point want to port to plpgsql2, and the least time consuming way of doing so would be to make sure most existing plpgsql-functions require no modifications at all to work with plpgsql2. I understand - just I don't would to repeat a issues of Python3 or Perl6 or .. I don't believe so people understand different casting rules in almost all same language plpgsql and plpgsql2. So it is one reason why start from zero with less know syntax. More I don't feel a real request from users. A new language would mean I would have to rewrite all functions, which is much worse than doing no or minor modifications to existing functions. otherwise plpgsql2 is wrong name .. with respect to your goals it should be stricter plpgsql I think plpgsql2 is a perfect name for it, because it is a new version of plpgsql, based on all the empirical knowledge gained from the 16 years of development in plpgsql. And while most improvements fall in the stricter category, there are probably other things which we would want to change when having the possibility of breaking compatibility. you can do it - but will be better as independent project. There is big space for improvement in plpgsql - but almost all can be done without some stronger incompatibility. Or this incompatibility (or stronger restrictivity) can be introduced in longer time window. I think the main difference in what is possible with plpgsql2 compared to improvements of plpgsql, boil down to not having to evaluate any proposed change against could this break compatibility in theory? but instead will this most certainly break compatilibity for most users?. Today, if a proposed code change in plpgsql would have an impact 0%, the change is rejected. because it was useless - it was not any new value. With plpgsql2, maybe we could allow an impact of X% of lines of code. If greater than X%, users will think it's unrealistic to port all their code from plpgsql to plpgsql2, which might be a long-term realistic requirement for some users, especially for the project, as in Y years from now, maybe the development of plpgsql can be put to halt, to avoid having to maintain both code bases, which *is* undoubtably an increased workload for the project. Also, all your work and effort with plpgsql_check_function() would be a natural fit for plpgsql2, the problems it detect should of course be errors by default in the language. plpgsql_check is necessary because we don't would to introduce strong dependency between functions and database schema. It is 70% motivation. Next 30% can be integrated to language well. And I believe if PL engine was more friendly to extensions, it can be 80% less code. Pavel
Re: [HACKERS] Better support of exported snapshots with pg_dump
On Mon, Sep 1, 2014 at 6:30 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-09-01 10:25:58 +0200, Bernd Helmle wrote: There was a discussion of this kind of feature some time ago here: http://www.postgresql.org/message-id/ca+u5nmk9+ttcff_-4mfdxwhnastauhuq7u7uedd57vay28a...@mail.gmail.com Thanks. It is not surprising to see similar threads. I was never convinced of the reasoning in that thread. Possibly things have changed enough now that logical decoding is in core... Well, the test case I got in mind is only for taking a dump using the latest state of a replication slot and not the snapshot export itself. So what about the following: we let the user specify a slot name with pg_dump, and take a dump using the latest snapshot that this replication slot has reported to a user. We could track the name of the latest snapshot reported to user by adding a new field in MyReplicationSlot, field updated in walsender.c when calling SnapBuildExportSnapshot. Then we could expose that in pg_replication_slots or with a separate SQL function that pg_dump could use. That's just a rough idea, but something like that would greatly help users writing online upgrade scripts. Not sure if all the arguments holds still true with the appearance of MVCC catalog scans. I don't think they change anything here. The problem is the, pretty fundamental, problem that you need to know a relation exists before executing a LOCK ...; on it. During that time somebody can change the schema. Doesn't this window exist as well with parallel pg_dump? Looking at the code snapshot export is taken before any locks on tables are taken. This window is smaller, but still... -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 12:55 PM, Andres Freund wrote: On 2014-09-01 12:49:22 +0200, Marko Tiikkaja wrote: On 9/1/14 12:12 PM, Andres Freund wrote: On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote: On 9/1/14 11:53 AM, Hannu Krosing wrote: You're going to have to find a more gradual way of doing this. Probably a better way (and there has been some talk of it) is having some kind of PRAGMA functionality, or pl/pgsql specific LOCAL SET to affect just this function and not spill to nested functions as is the case for SETs now. I can't imagine how that would work for anyone who has thousands of functions. How's that fundamentally different from changing languages? If we had a way to *add* such attributes to *existing* functions I don't see the fundamental problem? Adding 5-10 of these for every function you create seems significantly more painful than saying this function uses plpgsql2. You could package up these 5-10 SET LOCAL options as a separate language handler (called plpgsql2) which really does nothing more than set the local options and call the base handler :) this will still leave you with flexibility of adding/removing features for single functions For extra convenience you could even create a CREATE META LANGUAGE ... option for defining such language handlers. Though perhaps what's being suggested is a *single* option which changes everything at once? Then there wouldn't be a huge difference. The likelihood of us now knowing all the things that we want to break rigth now seems about zero. There *will* be further ones. If we go with the approach of creating new language versions for all of them we'll end up with a completely unmaintainable mess. For PG devs, application dev and DBAs. Since what you seemingly want - sensibly so imo - is to set the default errors for *new* functions, but leave the old set of errors for preexisting ones, I suggest adding a GUC that defines the set of warnings/errors *new* functions get. There'd need to be some syntax to opt out for pg_dump and similar, but that sounds unproblematic. One question here imo is whether we design something for plpgsql or more generic... I for one would like to have a generic SET LOCAL feature so it could also be used for pl/pythonu or pl/v8 Cheers -- 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] Better support of exported snapshots with pg_dump
On 2014-09-01 21:54:24 +0900, Michael Paquier wrote: On Mon, Sep 1, 2014 at 6:30 PM, Andres Freund and...@2ndquadrant.com wrote: I was never convinced of the reasoning in that thread. Possibly things have changed enough now that logical decoding is in core... Well, the test case I got in mind is only for taking a dump using the latest state of a replication slot and not the snapshot export itself. I don't think what you're proposing is really possible. Could you describe it in a bit more detail? So what about the following: we let the user specify a slot name with pg_dump, and take a dump using the latest snapshot that this replication slot has reported to a user. There exists no snapshot sufficient for user data after slot creation. I don't think they change anything here. The problem is the, pretty fundamental, problem that you need to know a relation exists before executing a LOCK ...; on it. During that time somebody can change the schema. Doesn't this window exist as well with parallel pg_dump? Yes. I didn't say those reasons were convincing. The window is quite a bit smaller though. With the exported snapshot from CREATE REPLICATION SLOT it could convinceably be hours. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 9/1/14 2:53 PM, Pavel Stehule wrote: 2014-09-01 14:27 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. ... Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. Do you argue the introduction of plpgsql2 would hurt the users of plpgsql in some way? How? yes, anybody who has thousands lines in plpgsql will be messy, when we publish so there will be new not fully compatible plpgsql2. That's a good thing. PL/PgSQL is broken in various subtle ways. If you have X% who continue to happily use plpgsql, and (100-X%) who find they can use plpgsql2 in their project, for new functions or all functions (for a new project), then you have made (100-X)% of the users more happy, than they would be if they were forced to use plpgsql and suffer from its problems. It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? I think what should happen is that we stop adding features to plpgsql. We should design plpgsql2 in such a way that it's easier to add new features to it in the future (to the extent that that's possible), and then add the new stuff only to that one. A new language like SQL/PSM would be helpful for new projects, but personally I have a huge code base written in plpgsql which I would at some point want to port to plpgsql2, and the least time consuming way of doing so would be to make sure most existing plpgsql-functions require no modifications at all to work with plpgsql2. I understand - just I don't would to repeat a issues of Python3 or Perl6 or .. I don't believe so people understand different casting rules in almost all same language plpgsql and plpgsql2. So it is one reason why start from zero with less know syntax. I'm not convinced. Seems to me that it would be better in every way to just fix the familiar syntax. More I don't feel a real request from users. Yeah, that's the problem with subtle problems: only people who use the language a lot and pay attention are going to notice them. A new language would mean I would have to rewrite all functions, which is much worse than doing no or minor modifications to existing functions. otherwise plpgsql2 is wrong name .. with respect to your goals it should be stricter plpgsql I think plpgsql2 is a perfect name for it, because it is a new version of plpgsql, based on all the empirical knowledge gained from the 16 years of development in plpgsql. And while most improvements fall in the stricter category, there are probably other things which we would want to change when having the possibility of breaking compatibility. you can do it - but will be better as independent project. There is big space for improvement in plpgsql - but almost all can be done without some stronger incompatibility. That's very very general and it would depend on the details, but I still disagree in general. Or this incompatibility (or stronger restrictivity) can be introduced in longer time window. I'd think that that would be worse for the current users of PL/PgSQL, not better. If greater than X%, users will think it's unrealistic to port all their code from plpgsql to plpgsql2, which might be a long-term realistic requirement for some users, especially for the project, as in Y years from now, maybe the development of plpgsql can be put to halt, to avoid having to maintain both code bases, which *is* undoubtably an increased workload for the project. Also, all your work and effort with plpgsql_check_function() would be a natural fit for plpgsql2, the problems it detect should of course be errors by default in the language. plpgsql_check is necessary because we don't would to introduce strong dependency between functions and database schema. It is 70% motivation. Next 30% can be integrated to language well. And I believe if PL engine was more friendly to extensions, it can be 80% less code. Yeah, PL/PgSQL is a bit hostile to to extensions like plpgsql_check. But that doesn't mean that we have to bin everything we have and start from scratch. .marko -- 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? Depends on how you define development. Bugfixes of plpgsql? Yes, of course. New features? No, but that's a non-issue since we all know it's more or less impossible to introduce new features without breaking compatibility, I think you will agree on that, no? A new language like SQL/PSM would be helpful for new projects, but personally I have a huge code base written in plpgsql which I would at some point want to port to plpgsql2, and the least time consuming way of doing so would be to make sure most existing plpgsql-functions require no modifications at all to work with plpgsql2. I understand - just I don't would to repeat a issues of Python3 or Perl6 or The fatal problems with Python3 and Perl6 was the inability to mix code between Python2/3 and Perl5/6. We don't have that problem with pl-languages in postgres, so please don't make that comparison, as it's incorrect. I think plpgsql2 is a perfect name for it, because it is a new version of plpgsql, based on all the empirical knowledge gained from the 16 years of development in plpgsql. And while most improvements fall in the stricter category, there are probably other things which we would want to change when having the possibility of breaking compatibility. you can do it - but will be better as independent project. There is big space for improvement in plpgsql - but almost all can be done without some stronger incompatibility. Or this incompatibility (or stronger restrictivity) can be introduced in longer time window. With can be done you have to take into account what kind of changes the project accepts into the plpgsql-code Looking back a few years of efforts from people (including yourself), it looks like much of the energy and hours invested would have made a much better pay-off in a new language. -- 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] PL/pgSQL 2
On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? Depends on how you define development. Bugfixes of plpgsql? Yes, of course. New features? No, but that's a non-issue since we all know it's more or less impossible to introduce new features without breaking compatibility, I think you will agree on that, no? Sorry, but that's just plain wrong. There've been plenty of new features for plpgsql. You're not very convincing if you use bogus arguments like this. EOD for me. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \watch versus \timing
On Fri, Aug 29, 2014 at 6:33 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/28/2014 02:46 PM, Fujii Masao wrote: On Tue, Aug 26, 2014 at 4:55 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/25/2014 10:48 PM, Heikki Linnakangas wrote: Actually, perhaps it would be better to just copy-paste PSQLexec, and modify the copy to suite \watch's needs. (PSQLexecWatch? SendWatchQuery?). PSQLexec doesn't do much, and there isn't very much overlap between what \watch wants and what other PSQLexec callers want. \watch wants timing output, others don't. \watch doesn't want transaction handling. Agreed. Attached is the revised version of the patch. I implemented PSQLexecWatch() which sends the query, prints the results and outputs the query execution time (if \timing is enabled). This patch was marked as ready for committer, but since I revised the code very much, I marked this as needs review again. This comment: ... We use PSQLexecWatch, !* which is kind of cheating, but SendQuery doesn't let us suppress !* autocommit behavior. is a bit strange now. PSQLexecWatch isn't cheating like reusing PSQLexec was; it's whole purpose is to run \watch queries. /* * Set up cancellation of 'watch' via SIGINT. We redo this each time * through the loop since it's conceivable something inside PSQLexec * could change sigint_interrupt_jmp. */ This should now say PSQLexecWatch. Other than that, looks good to me. I just tested the patch and this feature works as expected if timing is on and it displays the individual run time of each query kicked by \watch. Note that --echo-hidden does not display the query run during each loop and that this is contrary to the behavior in HEAD so it breaks backward compatibility, but are there really people relying in the existing behavior? -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrently option for reindexdb
On 08/25/2014 02:36 PM, Sawada Masahiko wrote: Hi all, Attached WIP patch adds -C (--concurrently) option for reindexdb command for concurrently reindexing. If we specify -C option with any table then reindexdb do reindexing concurrently with minimum lock necessary. Note that we cannot use '-s' option (for system catalog) and '-C' option at the same time. This patch use simple method as follows. 1. Do CREATE INDEX CONCURRENTLY new index which has same definition as target index 2. Aquire ACCESS EXCLUSIVE LOCK to target table( and transaction starts) 3. Swap old and new index 4. Drop old index 5. COMMIT How do you handle indexes tied to constraints - PRIMARY KEY, UNIQUE, or EXCLUSION constraint indexes? My understanding was that this currently required some less than lovely catalog hacks. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 05:04 PM, Joel Jacobson wrote: Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. You're not proposing to copy plpgsql's runtime though, right? Just add conditional paths where v1 and v2 differ. Personally I'd rather look at adding language version pragmas or a new function attribute, but that's mostly bikeshedding. From the top of my head, these are Things I personally would want to see in plpgsql2: - Accept RECORD input, dynamic access to fields of records without resorting to hstore hacks. This is certainly my #1. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 05:04 PM, Joel Jacobson wrote: From the top of my head, these are Things I personally would want to see in plpgsql2: Oh, also, I'd *love* to improve how non-plannable statements with PL/PgSQL variable subsitutions behave. *I* understand why the following is wrong: DO $$ DECLARE tablename text; BEGIN tablename := 'sometable'; DROP TABLE tablename; END; $$; and produces: ERROR: table tablename does not exist CONTEXT: SQL statement DROP TABLE tablename PL/pgSQL function inline_code_block line 6 at SQL statement but going by the number of questions I see about this, and similar issues with ALTER USER and so on, it's not obvious to new users. It's not clear how to make this friendly without making it dangerous though. If you have: DROP TABLE x; then someone declares a variable x := 'customers'; you don't want to suddenly be issuing a DROP TABLE customers; ... which is why I suspect this might need to be not 100% backward compatible, perhaps requiring a variable-marker prefix. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER SYSTEM RESET?
On Mon, Sep 1, 2014 at 3:57 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Aug 30, 2014 at 12:27 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Aug 27, 2014 at 7:16 PM, Fujii Masao masao.fu...@gmail.com wrote: The patch looks good to me. One minor comment is; probably you need to update the tab-completion code. Thanks for the review. I have updated the patch to support tab-completion. As this is a relatively minor change, I will mark it as Ready For Committer rather than Needs Review. Thanks for updating the patch! One more minor comment is; what about applying the following change for the tab-completion for RESET ALL? This causes the tab-completion of even ALTER SYSTEM SET to display all and that's strange. But the tab-completion of SET has already had the same problem. So I think that we can live with that. Right and I have checked that behaviour is same for other similar statements like Alter Database database_name SET config_var or Alter User user_name SET config_var. So, the change made by you is on similar lines. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] PL/pgSQL 2
2014-09-01 15:12 GMT+02:00 Marko Tiikkaja ma...@joh.to: On 9/1/14 2:53 PM, Pavel Stehule wrote: 2014-09-01 14:27 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. ... Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. Do you argue the introduction of plpgsql2 would hurt the users of plpgsql in some way? How? yes, anybody who has thousands lines in plpgsql will be messy, when we publish so there will be new not fully compatible plpgsql2. That's a good thing. PL/PgSQL is broken in various subtle ways. If you have X% who continue to happily use plpgsql, and (100-X%) who find they can use plpgsql2 in their project, for new functions or all functions (for a new project), then you have made (100-X)% of the users more happy, than they would be if they were forced to use plpgsql and suffer from its problems. It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? I think what should happen is that we stop adding features to plpgsql. We should design plpgsql2 in such a way that it's easier to add new features to it in the future (to the extent that that's possible), and then add the new stuff only to that one. A new language like SQL/PSM would be helpful for new projects, but personally I have a huge code base written in plpgsql which I would at some point want to port to plpgsql2, and the least time consuming way of doing so would be to make sure most existing plpgsql-functions require no modifications at all to work with plpgsql2. I understand - just I don't would to repeat a issues of Python3 or Perl6 or .. I don't believe so people understand different casting rules in almost all same language plpgsql and plpgsql2. So it is one reason why start from zero with less know syntax. I'm not convinced. Seems to me that it would be better in every way to just fix the familiar syntax. More I don't feel a real request from users. Yeah, that's the problem with subtle problems: only people who use the language a lot and pay attention are going to notice them. A new language would mean I would have to rewrite all functions, which is much worse than doing no or minor modifications to existing functions. otherwise plpgsql2 is wrong name .. with respect to your goals it should be stricter plpgsql I think plpgsql2 is a perfect name for it, because it is a new version of plpgsql, based on all the empirical knowledge gained from the 16 years of development in plpgsql. And while most improvements fall in the stricter category, there are probably other things which we would want to change when having the possibility of breaking compatibility. you can do it - but will be better as independent project. There is big space for improvement in plpgsql - but almost all can be done without some stronger incompatibility. That's very very general and it would depend on the details, but I still disagree in general. Or this incompatibility (or stronger restrictivity) can be introduced in longer time window. I'd think that that would be worse for the current users of PL/PgSQL, not better. I am sorry. Users around me are allergic on any +X language, so I am careful. I understand to you, understand to your motivation, but I disagree with your proposal. We can talk about possibility to design a extensions, what you need and we can redesign plpgsql engine to allow to different setup for any specific usage (with extensions, some config). But still I would to respect some relation to PL/SQL and ADA (not necessary compatibility). Regards Pavel If greater than X%, users will think it's unrealistic to port all their code from plpgsql to plpgsql2, which might be a long-term realistic requirement for some users, especially for the project, as in Y years from now, maybe the development of plpgsql can be put to halt, to avoid having to maintain both code bases, which *is* undoubtably an increased workload for the project. Also, all your work and effort with plpgsql_check_function() would be a natural fit for plpgsql2, the problems it detect should of course be errors by default in the language. plpgsql_check is necessary because we don't would to introduce strong dependency between functions and database schema. It is 70% motivation. Next 30% can be integrated to language well. And I believe if PL engine was more friendly to extensions, it can be 80% less code. Yeah, PL/PgSQL is a bit hostile to to extensions like plpgsql_check. But that doesn't mean that we have to bin everything we have and start from scratch. .marko
Re: [HACKERS] PL/pgSQL 2
2014-09-01 15:52 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 09/01/2014 05:04 PM, Joel Jacobson wrote: From the top of my head, these are Things I personally would want to see in plpgsql2: Oh, also, I'd *love* to improve how non-plannable statements with PL/PgSQL variable subsitutions behave. *I* understand why the following is wrong: DO $$ DECLARE tablename text; BEGIN tablename := 'sometable'; DROP TABLE tablename; END; $$; and produces: ERROR: table tablename does not exist CONTEXT: SQL statement DROP TABLE tablename PL/pgSQL function inline_code_block line 6 at SQL statement but going by the number of questions I see about this, and similar issues with ALTER USER and so on, it's not obvious to new users. It's not clear how to make this friendly without making it dangerous though. If you have: DROP TABLE x; then someone declares a variable x := 'customers'; you don't want to suddenly be issuing a DROP TABLE customers; ... which is why I suspect this might need to be not 100% backward compatible, perhaps requiring a variable-marker prefix. It is in ToDo - allow parametrization for COMMANDs. But this is one point, when I am not sure if we would it. Now - situation is very simply. Variables should not be used as table or column name. With your proposal, the situation will by much more complex, and probably variables should be used in SELECT * FROM x; but it can be ambiguous SELECT x FROM table ... so maybe better to don't allow it Regards Pavel -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 09:58 PM, Pavel Stehule wrote: It is in ToDo - allow parametrization for COMMANDs. But this is one point, when I am not sure if we would it. Now - situation is very simply. Variables should not be used as table or column name. With your proposal, the situation will by much more complex, and probably variables should be used in SELECT * FROM x; but it can be ambiguous SELECT x FROM table ... so maybe better to don't allow it We would have to define a specific parameter marker, as used by things like JDBC. SELECT x FROM ?tablename DROP TABLE ?tablename or whatever your favourite place-holder syntax is. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 16:01 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 09/01/2014 09:58 PM, Pavel Stehule wrote: It is in ToDo - allow parametrization for COMMANDs. But this is one point, when I am not sure if we would it. Now - situation is very simply. Variables should not be used as table or column name. With your proposal, the situation will by much more complex, and probably variables should be used in SELECT * FROM x; but it can be ambiguous SELECT x FROM table ... so maybe better to don't allow it We would have to define a specific parameter marker, as used by things like JDBC. SELECT x FROM ?tablename DROP TABLE ?tablename or whatever your favourite place-holder syntax is. It can be solution, but I dislike it .. It increase a language complexity .. vars with or without prefix .. and more, hidden dynamic SQL Nothing what I like - I have a mental barrier to this concept. Pavel -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL 2
On 2014-09-01 22:01:33 +0800, Craig Ringer wrote: On 09/01/2014 09:58 PM, Pavel Stehule wrote: It is in ToDo - allow parametrization for COMMANDs. But this is one point, when I am not sure if we would it. Now - situation is very simply. Variables should not be used as table or column name. With your proposal, the situation will by much more complex, and probably variables should be used in SELECT * FROM x; but it can be ambiguous SELECT x FROM table ... so maybe better to don't allow it We would have to define a specific parameter marker, as used by things like JDBC. SELECT x FROM ?tablename DROP TABLE ?tablename or whatever your favourite place-holder syntax is. Imo this is still something that's more dynamic SQL (i.e. EXECUTE's remit) than something that shouldn't be doable implicitly. So perhaps the solution is to extend EXECUTE to allow specifying tablenames as variables more conveniently? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 10:11 PM, Pavel Stehule wrote: It can be solution, but I dislike it .. It increase a language complexity .. vars with or without prefix .. and more, hidden dynamic SQL Nothing what I like - I have a mental barrier to this concept. Yeah - the question is whether it's better than using EXECUTE. I'm not convinced it is, since it doesn't make things any more discoverable for new users, and existing users already know how to do it right. It'd only really improve things if PL/PgSQL had started off using $variable notation, or something that wasn't otherwise legal as an identifier. I don't love how it works now, but I don't have a better answer really. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 10:17 PM, Andres Freund wrote: Imo this is still something that's more dynamic SQL (i.e. EXECUTE's remit) than something that shouldn't be doable implicitly. So perhaps the solution is to extend EXECUTE to allow specifying tablenames as variables more conveniently? With format(...) it's pretty easy, really. Perhaps just changing the docs to remove all the quote_ident based examples in favour of format(...) would be enough. Pavel's points are pretty good - what exists now isn't ideal from a usability and friendliness PoV, but changing it would require making a big mess elsewhere that's not worth doing. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 16:18 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 09/01/2014 10:11 PM, Pavel Stehule wrote: It can be solution, but I dislike it .. It increase a language complexity .. vars with or without prefix .. and more, hidden dynamic SQL Nothing what I like - I have a mental barrier to this concept. Yeah - the question is whether it's better than using EXECUTE. I'm not convinced it is, since it doesn't make things any more discoverable for new users, and existing users already know how to do it right. It'd only really improve things if PL/PgSQL had started off using $variable notation, or something that wasn't otherwise legal as an identifier. yes .. dynamic SQL is terrible but it is good to understand to this concept quickly - because it is core of integration SQL to PL/pgSQL. I don't love how it works now, but I don't have a better answer really. I have same opinion. It is not ideal now, but I don't any better ideal -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL 2
On 2014-09-01 22:20:37 +0800, Craig Ringer wrote: On 09/01/2014 10:17 PM, Andres Freund wrote: Imo this is still something that's more dynamic SQL (i.e. EXECUTE's remit) than something that shouldn't be doable implicitly. So perhaps the solution is to extend EXECUTE to allow specifying tablenames as variables more conveniently? With format(...) it's pretty easy, really. I know of format(), but it doesn't allow you to pass parameters as actual query variables unfortunately. I'm wondering if there's a way to marry USING and format()... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 10:24 PM, Andres Freund wrote: I know of format(), but it doesn't allow you to pass parameters as actual query variables unfortunately. I'm wondering if there's a way to marry USING and format()... Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? Depends on how you define development. Bugfixes of plpgsql? Yes, of course. New features? No, but that's a non-issue since we all know it's more or less impossible to introduce new features without breaking compatibility, I think you will agree on that, no? Sorry, but that's just plain wrong. There've been plenty of new features for plpgsql. You're not very convincing if you use bogus arguments like this. You misunderstood, what I said was it's more or less impossible, that's different from impossible. *If* a feature can be added to plpgsql, it sure can be added to plpgsql2 too, so of course it should be added to both. I'm just saying it's much less probable you can add new features to plpgsql than to plpgsql2, as you have to take into account the risk of breaking compatibility. -- 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] PL/pgSQL 2
+1 I use underscore for *all* variables and input parameters in all functions. Making that a requirement in plpgsql2 wouldn't break any of my code. On Mon, Sep 1, 2014 at 3:52 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 09/01/2014 05:04 PM, Joel Jacobson wrote: From the top of my head, these are Things I personally would want to see in plpgsql2: Oh, also, I'd *love* to improve how non-plannable statements with PL/PgSQL variable subsitutions behave. *I* understand why the following is wrong: DO $$ DECLARE tablename text; BEGIN tablename := 'sometable'; DROP TABLE tablename; END; $$; and produces: ERROR: table tablename does not exist CONTEXT: SQL statement DROP TABLE tablename PL/pgSQL function inline_code_block line 6 at SQL statement but going by the number of questions I see about this, and similar issues with ALTER USER and so on, it's not obvious to new users. It's not clear how to make this friendly without making it dangerous though. If you have: DROP TABLE x; then someone declares a variable x := 'customers'; you don't want to suddenly be issuing a DROP TABLE customers; ... which is why I suspect this might need to be not 100% backward compatible, perhaps requiring a variable-marker prefix. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 16:26 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 09/01/2014 10:24 PM, Andres Freund wrote: I know of format(), but it doesn't allow you to pass parameters as actual query variables unfortunately. I'm wondering if there's a way to marry USING and format()... Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. It is not intuitive, but It is well descriptive about PL/pgSQL. I cannot to imagine some different - it is mix of two independent worlds. And at end it is elegant. If we start new language from scratch, then we don't need to introduce concept of embedded SQL or dynamic SQL. But why? If it is too clumsy for you, use PLPerl or PLPython. Everytime will be problem how to merge two different namespaces together. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 03:45 PM, Craig Ringer wrote: On 09/01/2014 05:04 PM, Joel Jacobson wrote: Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. You're not proposing to copy plpgsql's runtime though, right? Just add conditional paths where v1 and v2 differ. Personally I'd rather look at adding language version pragmas or a new function attribute, but that's mostly bikeshedding. From the top of my head, these are Things I personally would want to see in plpgsql2: - Accept RECORD input, dynamic access to fields of records without resorting to hstore hacks. This is certainly my #1. Also, an easy way to tell pl/pgsql to *not* cache plans without resorting to EXECUT'ins trings would nice Cheers Hannu -- 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer cr...@2ndquadrant.com wrote: Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. -- 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] PL/pgSQL 2
On 2014-09-01 16:29:18 +0200, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? Depends on how you define development. Bugfixes of plpgsql? Yes, of course. New features? No, but that's a non-issue since we all know it's more or less impossible to introduce new features without breaking compatibility, I think you will agree on that, no? Sorry, but that's just plain wrong. There've been plenty of new features for plpgsql. You're not very convincing if you use bogus arguments like this. You misunderstood, what I said was it's more or less impossible, that's different from impossible. It's still bullshit. It's very hard to change *existing* semantics and thus existing code. Which isn't something plpgsql specifically has difficulties with. It's a far more general concern affecting pretty much all released software; more so software with a large existing user base. Remember all the flak postgres got for the cast issues with 8.3? It's about has hard to add additional features, that don't break existing code, to plpgsql as to most of the rest of postgres. Which isn't to say it's easy. Believe me, I know that. I'm just saying it's much less probable you can add new features to plpgsql than to plpgsql2, as you have to take into account the risk of breaking compatibility. That's just a difference of one release. The release after the set of problems is nearly identical. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 05:41 PM, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer cr...@2ndquadrant.com wrote: Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. This is exactly why we need a new language. We could certainly improve that syntax in PL/pgSQL. No need to start from scratch for that.. Got a suggestion what the syntax should look like? - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
2014-09-01 16:39 GMT+02:00 Hannu Krosing ha...@2ndquadrant.com: On 09/01/2014 03:45 PM, Craig Ringer wrote: On 09/01/2014 05:04 PM, Joel Jacobson wrote: Just like with plpgsql, once released, plpgsql2 cannot break compatibility with future versions, so we only have one chance to carefully think though what we would like to change in the language. You're not proposing to copy plpgsql's runtime though, right? Just add conditional paths where v1 and v2 differ. Personally I'd rather look at adding language version pragmas or a new function attribute, but that's mostly bikeshedding. From the top of my head, these are Things I personally would want to see in plpgsql2: - Accept RECORD input, dynamic access to fields of records without resorting to hstore hacks. This is certainly my #1. Also, an easy way to tell pl/pgsql to *not* cache plans without resorting to EXECUT'ins trings would nice It is good request maybe #option noplancache or SELECT /* NOPLANCACHE */ Pavel Cheers Hannu -- 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] PL/pgSQL 2
2014-09-01 16:41 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer cr...@2ndquadrant.com wrote: Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. when you fix it, then you designed totally new language with thin relation to plpgsql Pavel
Re: [HACKERS] psql \watch versus \timing
Michael Paquier michael.paqu...@gmail.com writes: I just tested the patch and this feature works as expected if timing is on and it displays the individual run time of each query kicked by \watch. Note that --echo-hidden does not display the query run during each loop and that this is contrary to the behavior in HEAD so it breaks backward compatibility, but are there really people relying in the existing behavior? ISTM that's an anti-feature anyway, and changing that behavior is a good thing. 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] PL/pgSQL 2
On Mon, Sep 1, 2014 at 4:41 PM, Andres Freund and...@2ndquadrant.com wrote: I'm just saying it's much less probable you can add new features to plpgsql than to plpgsql2, as you have to take into account the risk of breaking compatibility. That's just a difference of one release. The release after the set of problems is nearly identical. That's not true. The first release (plpgsql - plpgsql2) will be a major release. After that, we can do minor releases for the following X years, until we possible need for a new major version. Each minor release would be guaranteed not to break any backwards compatibility. plpgsql - plpgsql2 would be the single giant leap we take into the future. I think this reasoning is quite compatible with the versioning policy of the project in general, where we distinguish between major and minor releases. -- 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] PL/pgSQL 2
On 09/01/2014 10:41 PM, Joel Jacobson wrote: This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. Given how much bike shedding occurs around trivial features, can you imagine how long that'd take? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 09/01/2014 10:41 PM, Joel Jacobson wrote: This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. Given how much bike shedding occurs around trivial features, can you imagine how long that'd take? I wasn't aware of the expression bike shedding so I had to look it up. It apparently means spend the majority of its time on relatively unimportant but easy-to-grasp issues. If you feel the development of plpgsql falls into this category, that most time is spent on the smaller unimportant things, isn't that a clear sign we need plpgsql2, for there to be any hope of progress on the important things? -- 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] PL/pgSQL 2
Joel Jacobson-2 wrote On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer lt; craig@ gt; wrote: Well, the idiom: EXECUTE format(SELECT %I FROM %I WHERE $1, col, tbl) USING val; is not lovely. It works, but it's clumsy. This is exactly why we need a new language. All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with the most beautiful syntax we can come up with. I guess it's a question if we want to support things like this. If we want to, then we also want a new language. Regardless of how they are ultimately implemented - a new language, PRAGMA infrastructure, or adding syntax to plpgsql - improving upon the current 6-item ToDo on the Wiki would be a lot more valuable at this point in time. I do think better organization than the current ToDo format can be had but even just moving many of these ideas there would be a start. The goal isn't to introduce a new language version - that would simply be a means to achieving an end that is not reasonably achievable in any other way. Ideally, for each feature and behavior, we'd be able to provide suggestions on how it could be implemented in the various cases and the pros/cons of doing so in each. If there are enough items with beautiful syntax in version 2 of the language then that concrete evidence and action plan would have a much better chance of gaining commiter support than generalities and theories. Adding a whole new language to avoid 2 pet-peeves and an introduce a behavior (syntax to allow multi-row DDL) that is controversial is not likely to seem worth it to those you are asking to write and maintain this new language. I get the goal of the post was to generate ideas but at the same time the idea generation can occur without you putting forth the conclusion that a new language is required - conclusions like that are not useful at the start of a research project (even if that conclusion motivated the project in the first place). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817199.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for psql History Display on MacOSX
Hi everbody, My first mail to this one, so please be mild. I fired up the debugger to get this item going, which is also on the Todo List. Attached is a very trivial patch as a basis for discussion that at least makes \s (show history) work in psql on Macs. Macs uses libedit, which has a libreadline interface. A short investigation showed that the way psql iterates over the history does not work with libedit. I changed the iteration scheme to an index based loop (see code and comments), which seemed to be the only working option for both readline and libedit. In any case, i have tested and compiled this on MacOX 10.9.3 and Linux. Windows doesn’t have the pager in the first place. As noted in the todo I have made this code pay attention to the pager configuration from psql. The odd part is when your history opens in less you see the top part rather then the bottom part, but the bottom is just a single keystroke away. If pager is disabled history is just printed fine. Please note that this didn’t work at all on Mac before. Could this go into …./regress/sql/psql.sql at all? I am not sure on that one. Regards, Stepan psql_pager_history_libedit_and_readline.patch Description: Binary data smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] PL/pgSQL 2
Andres Freund and...@2ndquadrant.com writes: On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule pavel.steh...@gmail.com wrote: It bad signal to have two languages plpgsql and plpgsql2. Who will believe to us so we will continue development of plpgsql? Depends on how you define development. Bugfixes of plpgsql? Yes, of course. New features? No, but that's a non-issue since we all know it's more or less impossible to introduce new features without breaking compatibility, I think you will agree on that, no? Sorry, but that's just plain wrong. There've been plenty of new features for plpgsql. You're not very convincing if you use bogus arguments like this. And even more to the point: once plpgsql2 is released, the *exact same* compatibility arguments will limit further development of it. Perhaps, if you were very smart and designed a language from scratch without worrying about whether it looked anything like plpgsql, you could come up with something that would be easier to extend without creating compatibility issues. But that's not what's being proposed here. What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it's one-shot or not. 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] On partitioning
On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Aside from costing planning time, most likely that would forever prevent us from pushing some types of intelligence about partitioning into the executor. How would it affect this calculus if there were partitioned indexes which were created on the overall table and guaranteed to exist on each partition that the planner could use -- and then possibly also per-partition indexes that might exist in addition to those? So the planner could make deductions and leave some intelligence about partitions to the executor as long as they only depend on partitioned indexes but might be able to take advantage of a per-partition index if it's an unusual situation. I'm imagining for example a partitioned table where only the current partition is read-write and OLTP queries restrict themselves to working only with the current partition. Having excluded the other partitions the planner is free to use any of the indexes liberally. That said, I think the typical approach to this is to only allow indexes that are defined for the whole table. If the user wants to have different indexes for the current time period they would have a separate table with all the indexes on it that is only moved into the partitioned table once it's finished being used for for the atypical queries. Oracle supports local partitioned indexes (which are partitioned like the table) and global indexes (which span partitions) but afaik it doesn't support indexes on only some partitions. Furthermore, we have partial indexes. Partial indexes mean you can always create a partial index on just one partition's range of keys. The index will exist for all partitions but just be empty for all but the partitions that matter. The planner can plan based on the partial index's where clause which would accomplish the same thing, I think. -- greg -- 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] On partitioning
On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote: It would need to replace plain tid (pagenr, tupnr) with triple of (partid, pagenr, tupnr). Cross-partition indexes are especially needed if we want to allow putting UNIQUE constraints on non-partition-key columns. I actually don't think this is necessary. I'm pretty sure that you can build an efficient and correct version of unique constraints with several underlying indexes in different partitions each. The way exclusion constraints are implemented imo is a good guide. I personally think that implementing cross partition indexes has a low enough cost/benefit ratio that I doubt it's wise to tackle it anytime soon. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On partitioning
Greg Stark st...@mit.edu writes: On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Aside from costing planning time, most likely that would forever prevent us from pushing some types of intelligence about partitioning into the executor. How would it affect this calculus if there were partitioned indexes which were created on the overall table and guaranteed to exist on each partition that the planner could use -- and then possibly also per-partition indexes that might exist in addition to those? That doesn't actually fix the planning-time issue at all. Either the planner considers each partition individually to create a custom plan for it, or it doesn't. The push into executor idea I was alluding to is that we might invent plan constructs like a ModifyTable node that applies to a whole inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be done at runtime. You're not going to get a plan structure like that if the planner is building a separate plan subtree for each partition. 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] [BUGS] BUG #9652: inet types don't support min/max
On Fri, Aug 29, 2014 at 12:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Haribabu Kommi kommi.harib...@gmail.com writes: Thanks for your review. Please find the rebased patch to latest HEAD. Committed with minor (mostly cosmetic) alterations. Thanks. Regards, Hari Babu Fujitsu Australia -- 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] On partitioning
On 2014-08-31 16:03:30 -0400, Tom Lane wrote: Another thought about this general topic: Alvaro Herrera alvhe...@2ndquadrant.com writes: ... Allowed actions on a RELKIND_PARTITION: * CREATE INDEX .. ON PARTITION n ON TABLE xyz ... Still To Be Designed * Are indexes/constraints inherited from the parent rel? I think one of the key design decisions we have to make is whether partitions are all constrained to have exactly the same set of indexes. If we don't insist on that it will greatly complicate planning compared to what we'll get if we do insist on it, because then the planner will need to generate a separate customized plan subtree for each partition. Aside from costing planning time, most likely that would forever prevent us from pushing some types of intelligence about partitioning into the executor. Now, in the current model, it's up to the user what indexes to create on each partition, and sometimes one might feel that maintaining a particular index is unnecessary in some partitions. But the flip side of that is it's awfully easy to screw yourself by forgetting to add some index when you add a new partition. So I'm not real sure which approach is superior from a purely user-oriented perspective. I think we're likely to end up with both. In many cases it'll be far superior from a usability and planning perspective to have indices on the 'toplevel table' (do we have a good name for that?). But on the flip side, one of the significant use cases for partitioning is dealing with historical data. In many cases old data has to be saved for years but is barely ever queried. It'd be a shame to inflict all indexes on all partitions for that kind of data. It'd surely be a useful step to add sane partitioning without that capability, but we shouldn't base the design on that decision. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On partitioning
On Mon, Sep 1, 2014 at 4:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: The push into executor idea I was alluding to is that we might invent plan constructs like a ModifyTable node that applies to a whole inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be done at runtime. You're not going to get a plan structure like that if the planner is building a separate plan subtree for each partition. Well my message was assuming that in that case it would only consider the partitioned indexes. It would only consider the isolated indexes if the planner was able to identify a specific partition. That's probably the only type of query where such indexes are likely to be useful. -- greg -- 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] On partitioning
On 2014-09-01 11:59:37 -0400, Tom Lane wrote: Greg Stark st...@mit.edu writes: On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Aside from costing planning time, most likely that would forever prevent us from pushing some types of intelligence about partitioning into the executor. How would it affect this calculus if there were partitioned indexes which were created on the overall table and guaranteed to exist on each partition that the planner could use -- and then possibly also per-partition indexes that might exist in addition to those? That doesn't actually fix the planning-time issue at all. Either the planner considers each partition individually to create a custom plan for it, or it doesn't. We could have a information about the indexing situation in child partitions on the toplevel table. I.e. note whether child partitions have individual indexes. And possibly constraints. The push into executor idea I was alluding to is that we might invent plan constructs like a ModifyTable node that applies to a whole inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be done at runtime. You're not going to get a plan structure like that if the planner is building a separate plan subtree for each partition. It doesn't sound impossible to evaluate at plan time whether to use nodes covering several partitions or use a separate subplan for individual partitions. We're going to need information which partitions to scan in those nodes anyway. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On partitioning
On 09/01/2014 06:59 PM, Tom Lane wrote: Greg Stark st...@mit.edu writes: On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Aside from costing planning time, most likely that would forever prevent us from pushing some types of intelligence about partitioning into the executor. How would it affect this calculus if there were partitioned indexes which were created on the overall table and guaranteed to exist on each partition that the planner could use -- and then possibly also per-partition indexes that might exist in addition to those? That doesn't actually fix the planning-time issue at all. Either the planner considers each partition individually to create a custom plan for it, or it doesn't. Hmm. Couldn't you plan together all partitions that do have the same indexes? In other words, create a custom plan for each group of partitions, rather than each partition? - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tips/advice for implementing integrated RESTful HTTP API
Hmm yes I am learning that the BG worker system isn't as helpful as I had hoped due to the single database restriction. As for a writing a frontend this might be the best solution. A java frontend would be easy but pointless because the whole point here is to provide a lightweight access method to the database for environments that don't have the ability to use the jdbc or libpq libraries. Deploying a java setup would be too much trouble. I do see now that PG uses one worker per connection rather than a worker pool as I had thought before. So there's nothing already in there to help me dispatch requests and making my own worker pool that distributes requests using sockets wouldn't be any better than connecting back using libpq. A C frontend using libevent would be easy enough to make and deploy for this I guess. But... Maybe nobody really wants this thing anyway, there seem to be some other options out there already. Thanks for the feedback. On Aug 31, 2014 8:46 PM, Craig Ringer cr...@2ndquadrant.com wrote: On 08/31/2014 12:40 PM, Dobes Vandermeer wrote: 1. Connecting to multiple databases The background workers can apparently only connect to a single database at a time, but I want to expose all the databases via the API. bgworkers are assigned a database at launch time (if SPI is enabled), and this database may not change during the worker's lifetime, same as a normal backend. Sometimes frustrating, but that's how it is. I think I could use libpq to connect to PostgreSQL on localhost but this might have weird side-effects in terms of authentication, pid use, stuff like that. If you're going to do that, why use a bgworker at all? In general, what do you gain from trying to do this within the database server its self, not as an app in front of the DB? I could probably manage a pool of dynamic workers (as of 9.4), one per user/database combination or something along those lines. Even one per request? Is there some kind of IPC system in place to help shuttle the requests and responses between dynamic workers? Or do I need to come up with my own? The dynamic shmem code apparently has some queuing functionality. I haven't used it yet. It seems like PostgreSQL itself has a way to shuttle requests out to workers, is it possible to tap into that system instead? Basically some way to send the requests to a PostgreSQL backend from the background worker? It does? It's not the SPI, that executes work directly within the bgworker, making it behave like a normal backend for the purpose of query execution. Or perhaps I shouldn't do this as a worker but rather modify PostgreSQL itself and do it in a more integrated/destructive manner? Or just write a front-end. The problem you'd have attempting to modify PostgreSQL its self for this is that connection dispatch occurs via the postmaster, which is a single-threaded process that already needs to do a bit of work to keep an eye on how things are running. You don't want it constantly busy processing and dispatching millions of tiny HTTP requests. It can't just hand a connection off to a back-end immediately after accepting it, either; it'd have to read the HTTP headers to determine what database to connect to. Then launch a new backend for the connection, which is horribly inefficient when doing tiny short-lived connections. The postmaster has no concept of a pool of backends (unfortunately, IMO) to re-use. I imagine (it's not something I've investigated, really) that you'd want a connection accepter process that watched the listening http request socket. It'd hand connections off to dispatcher processes that read the message content to get the target DB and dispatch the request to a worker backend for the appropriate user/db combo, then collect the results and return them on the connection. Hopefully at this point you're thinking that sounds a lot like a connection pool... because it is. An awfully complicated one, probably, as you'd have to manage everything using shared memory segments and latches. In my view it's unwise to try to do this in the DB with PostgreSQL's architecture. Hack PgBouncer or PgPool to do what you want. Or write a server with Tomcat/Jetty using JAX-RS and PgJDBC and the built in connection pool facilities - you won't *believe* how easy it is. 3. Parallelism The regular PostgreSQL server can run many queries in parallel Well, one PostgreSQL instance (postmaster) may have many backends, each of which may run queries in series but not in parallel. Any given process may only run one query at once. but it seems like if I am using SPI I could only run one query at a time - it's not an asynchronous API. Correct. Any help, sage advice, tips, and suggestions how to move forward in these areas would be muchly appreciated! Don't do it with bgworkers. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL
Re: [HACKERS] delta relations in AFTER triggers
On 08/30/2014 12:15 AM, Kevin Grittner wrote:
Heikki Linnakangas hlinnakan...@vmware.com wrote:
On 08/28/2014 12:03 AM, Kevin Grittner wrote:
Heikki Linnakangas hlinnakan...@vmware.com wrote:
I suggest adding a new hook to the ParseState struct, (p_rangevar_hook
?). The planner calls it whenever it sees a reference to a table, and
the hook function returns back some sort of placeholder reference to the
tuplestore. With variables, the hook returns a Param node, and at
execution time, the executor calls the paramFetch hook to fetch the
value of the param. For relations/tuplestores, I guess we'll need to
invent something like a Param node, but for holding information about
the relation. Like your TsrData struct, but without the pointer to the
tuplestore. At execution time, in the SPI_execute call, you pass the
pointer to the tuplestore in the ParamListInfo struct, like you pass
parameter values.
Does this make sense?
I see your point, but SPI first has to be made aware of the
tuplestores and their corresponding names and TupleDesc structures.
Does it make sense to keep the SPI_register_tuplestore() and
SPI_unregister_tuplestore() functions for the client side of the
API, and pass things along to the parse analysis through execution
phases using the techniques you describe?
Sorry, I didn't understand that. What do you mean by first, and the
client side of the API? I don't see any need for the
SPI_register_tuplestore() and and SPI_unregister_tuplestore() functions
if you use the hooks.
If we were to go with the hooks as you propose, we would still need
to take the information from TriggerData and put it somewhere else
for the hook to reference.
Sure.
The hooks are generalized for plpgsql,
not just for triggers, and it doesn't seem appropriate for them to
be fishing around in the TriggerData structure.
PLpgSQL_execstate seems like the appropriate place.
And what if we add other sources for tuplestores?
What about it?
The lookup during parse analysis
each time an apparent relation name is encountered must be simple
and fast.
We already use hooks for ColumnRefs, which are called even more often,
and we haven't had a problem making that fast enough.
I want named tuplestores to be easy to use from *all* PLs (for
trigger usage) as well as useful for other purposes people may want
to develop.
I'm not sure other PLs would even want to resolve the old/new relations
like PL/pgSQL does. It might be more natural to access the new/old
tuplestores as perl or python hashes or arrays, for example. But if they
do, it's not that difficult to write the hooks.
I had to change the hashkey for plpgsql's plan
caching, but that needs to be done regardless of the API (to
prevent problems in the obscure case that someone attaches the same
trigger function to the same table for the same events more than
once with different trigger names and different transition table
names). If you ignore that, the *entire* change to use this in
plpgsql is to add these lines to plpgsql_exec_trigger():
/*
* Capture the NEW and OLD transition TABLE tuplestores (if specified for
* this trigger).
*/
if (trigdata-tg_newtable)
{
Tsr tsr = palloc(sizeof(TsrData));
tsr-name = trigdata-tg_trigger-tgnewtable;
tsr-tstate = trigdata-tg_newtable;
tsr-tupdesc = trigdata-tg_relation-rd_att;
tsr-relid = trigdata-tg_relation-rd_id;
SPI_register_tuplestore(tsr);
}
if (trigdata-tg_oldtable)
{
Tsr tsr = palloc(sizeof(TsrData));
tsr-name = trigdata-tg_trigger-tgoldtable;
tsr-tstate = trigdata-tg_oldtable;
tsr-tupdesc = trigdata-tg_relation-rd_att;
tsr-relid = trigdata-tg_relation-rd_id;
SPI_register_tuplestore(tsr);
}
With the new SPI functions, the code to implement this in each
other PL should be about the same (possibly identical), and areas
using SPI only need similar code to make tuplestores visible to the
planner and usable in the executor if someone has another use for
this. You just do the above once you have run SPI_connect() and
before preparing or executing any query that references the named
tuplestore.
With hooks, the code to implement them in other PLs would be about the
same too, if they want the same behavior.
It remains available on that SPI connection until
SPI_finish() is called or you explicitly unregister it (by name).
Yeah, I don't like that. The SPI interface is currently stateless. Well,
except for cursors and plans explicitly saved with SPI_keepplan. But the
way queries are parsed is stateless - you pass all the necessary
information as part of the SPI_execute call (or similar), using direct
arguments and the ParamListInfo struct.
If you don't want to use hooks, I nevertheless feel that the old/new
relations should be passed as part of the ParamListInfo struct, one way
or another. With hooks, you would set the parserSetup hook,
Re: [HACKERS] On partitioning
On 09/01/2014 05:52 PM, Andres Freund wrote: On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote: It would need to replace plain tid (pagenr, tupnr) with triple of (partid, pagenr, tupnr). Cross-partition indexes are especially needed if we want to allow putting UNIQUE constraints on non-partition-key columns. I actually don't think this is necessary. I'm pretty sure that you can build an efficient and correct version of unique constraints with several underlying indexes in different partitions each. The way exclusion constraints are implemented imo is a good guide. I personally think that implementing cross partition indexes has a low enough cost/benefit ratio that I doubt it's wise to tackle it anytime soon. Also it has the downside of (possibly) making DROP PARTITION either slow or wasting space until next VACUUM. So if building composite unique indexes over multiple per-partition indexes is doable, I would much prefer this. Cheers -- 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] Patch for psql History Display on MacOSX
Stepan Rutz stepan.r...@gmx.de writes: Attached is a very trivial patch as a basis for discussion that at least makes \s (show history) work in psql on Macs. Macs uses libedit, which has a libreadline interface. Hm. The $64 question here is whether we can assume that history_get() exists and works compatibly in every interesting version of libreadline and libedit. I poked into the oldest version of GNU readline I could find, 4.0 (released in 1999), and that has it. The oldest libedit I have around is the one that came with OSX 10.4 (the CVS marker in readline.h from that says 2004/01/17). That has it too. So that looks pretty good. The readline code says that the argument ranges from history_base up, not from 1 up as this patch assumes. And it looks like history_base can change once the max number of stored lines is exceeded, so we can't assume that 1 is good enough. Fortunately, the global variable history_base also exists in both libraries (though it looks like it never changes from 1 in libedit). Functionally this seems like a clear win over what we had, especially since it supports using the pager. I'm inclined to think we should not only apply this change but back-patch it. One thing worth thinking about: should we use a history_get() loop like this for *all* \s commands, even when the target file is a regular file not /dev/tty? libedit's version of write_history does not write the history in the clear exactly, which you would think is the behavior wanted when saving a command history for any purpose other than updating ~/.psql_history. Such a change would break a workflow that involves doing \s to some random file and then copying that file to ~/.psql_history, but I find it hard to fathom why anyone would do that. There are a couple other minor bugs and some cosmetic things I don't like in this patch, but I'm willing to fix it up and commit it if there are not objections. 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] delta relations in AFTER triggers
Heikki Linnakangas hlinnakan...@vmware.com writes: On 08/30/2014 12:15 AM, Kevin Grittner wrote: If we were to go with the hooks as you propose, we would still need to take the information from TriggerData and put it somewhere else for the hook to reference. Sure. FWIW, I agree with Heikki on this point. It makes a lot more sense for the parser to provide hooks comparable to the existing hooks for resolving column refs, and it's not apparent that loading such functionality into SPI is sane at all. OTOH, I agree with Kevin that the things we're talking about are lightweight relations not variables. 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] implement subject alternative names support for SSL connections
On Mon, Sep 1, 2014 at 10:39 AM, Alexey Klyukin al...@hintbits.com wrote:
On Fri, Aug 29, 2014 at 11:22 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Yeah, I think a certificate without CN should be supported. See also RFC
6125, section 4.1. Rules [for issuers of certificates]:
5. Even though many deployed clients still check for the CN-ID
within the certificate subject field, certification authorities
are encouraged to migrate away from issuing certificates that
represent the server's fully qualified DNS domain name in a
CN-ID. Therefore, the certificate SHOULD NOT include a CN-ID
unless the certification authority issues the certificate in
accordance with a specification that reuses this one and that
explicitly encourages continued support for the CN-ID identifier
type in the context of a given application technology.
Certificates without a CN-ID are probably rare today, but they might start
to appear in the future.
Ok, I will change a patch to add support for this clause.
Attached is a new version. I've changed the logic to check for the SAN
names first, and only check the common name if there is no match. The
error when the common name is missing is only shown if SAN section
does not contain any DNS names as well. The tricky part is the error
message if no match was found: initially, it only listed a single
common name, but now tracking all DNS names just for the sake of the
error message makes the code more bloated, so I'm wondering if simply
stating that there was no match, as implemented in the attached patch,
would be good enough?
--
Regards,
Alexey Klyukin
diff --git a/src/interfaces/libpq/fe-secure-openssl.c
b/src/interfaces/libpq/fe-secure-openssl.c
new file mode 100644
index f950fc3..a4e3fc6
*** a/src/interfaces/libpq/fe-secure-openssl.c
--- b/src/interfaces/libpq/fe-secure-openssl.c
***
*** 60,68
--- 60,73
#ifdef USE_SSL_ENGINE
#include openssl/engine.h
#endif
+ #include openssl/x509v3.h
static bool verify_peer_name_matches_certificate(PGconn *);
static intverify_cb(int ok, X509_STORE_CTX *ctx);
+ static intcertificate_name_entry_validate_match(PGconn *conn,
+
char *name,
+
unsigned int len,
+
bool *match);
static void destroy_ssl_system(void);
static intinitialize_SSL(PGconn *conn);
static PostgresPollingStatusType open_client_SSL(PGconn *);
*** wildcard_certificate_match(const char *p
*** 471,487
return 1;
}
/*
! *Verify that common name resolves to peer.
*/
static bool
verify_peer_name_matches_certificate(PGconn *conn)
{
! char *peer_cn;
! int r;
! int len;
! boolresult;
/*
* If told not to verify the peer name, don't do it. Return true
--- 476,525
return 1;
}
+ /*
+ * Validate a single certificate name entry and match it against the pghost.
+ * Returns 0 if the certificate name is invalid (contains embedded NULLs), 1
otherwise.
+ */
+ static int
+ certificate_name_entry_validate_match(PGconn *conn, char *name, unsigned int
len, bool *match)
+ {
+ /* There is no guarantee the string returned from the certificate is
NULL-terminated */
+ name[len] = '\0';
+ *match = false;
+ /*
+* Reject embedded NULLs in certificate common or alternative name to
prevent attacks
+* like CVE-2009-4034.
+*/
+ if (len != strlen(name))
+ {
+ printfPQExpBuffer(conn-errorMessage,
+ libpq_gettext(SSL certificate's
common name contains embedded null\n));
+ return 0;
+ }
+ if (pg_strcasecmp(name, conn-pghost) == 0)
+ /* Exact name match */
+ *match = true;
+ else if (wildcard_certificate_match(name, conn-pghost))
+ /* Matched wildcard certificate */
+ *match = true;
+ else
+ *match = false;
+ return 1;
+ }
+
/*
! *Verify that common name or any of the alternative DNS names resolves to
peer.
! *Names in Subject Alternative Names and Common Name if present are
considered.
*/
static bool
verify_peer_name_matches_certificate(PGconn *conn)
{
! inti;
! intsan_len;
! bool result;
! bool san_has_dns_names;
! STACK_OF(GENERAL_NAME) *peer_san;
/*
* If told not to verify the peer name, don't do it. Return true
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it's one-shot or not. I'm glad to hear you think it *might* be worth doing. A one-shot is exactly what it is, like a new major version of postgres itself (but a new major version of postgres has a much longer release note of changes :). Once released, there is obviously no way to include new non-backwards compatible code in future minor versions. I guess it boils down to if the project can agree on if there are any significant *important* changes worth doing that are *not* possible or feasible to implement in plpgsql. I see two possible approaches of a plpgsql2 project, both aiming to require minimal/no changes of most existing best-practice plpgsql code: a) fork plpgsql code base and implement changes with as few lines of code as possible, making it easier to understand the changes, verify their correctness and apply future patches of the plpgsql code. b) fork plpgsql code and remove as much code as possible thanks to the reduced complexity possible thanks to the stricter behaviour achieved by removing settings and enforcing a stricter coding convention and killing obsolete quirks. Given plpgsql2 is a one-shot, the time window to gather input of what non-compatible changes to include probably needs to be at least a year. During that period, the mostly-compatible changes discussed could be implemented, which are the ones I'm personally most interested in anyway, but if we are creating a new language, then naturally we should take the chance to include all important changes we wish we could do but cannot with plpgsql. -- 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] Re: proposal: ignore null fields in not relation type composite type based constructors
2014-09-01 12:33 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com: Hi Pavel, Patch does look good to me. And found no issues as such. However here are my optional suggestions: 1. Frankly, I did not like name of the function row_to_json_pretty_choosy. Something like row_to_json_pretty_ignore_nulls seems better to me. should be - I have no better name 2. To use ignore nulls feature, I have to always pass pretty flag. Which seems weired. Since we do support named argument, can we avoid that? No idea how much difficult it is. If we have a default arguments to this function then we do not need one and two argument variations for this function as well. And we can use named argument for omitting the required one. Just a thought. it needs a redesign of original implementation, we should to change API to use default values with named parameters but it doesn't help too much (although it can be readable little bit more) instead row_to_json(x, false, true) be row_ro_json(x, ignore_null := true) it is not too much work, but I need a names for parameters Regards Pavel Rest looks good to me. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] PL/pgSQL 2
On 01/09/14 14:27, Joel Jacobson wrote: On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I agree with Andres - it is not a good for plpgsql and for plpgsql users. The benefit must be significant for 90% of users. ... Official implementation of plpgsql2 can be very wrong and dangerous signal - so we should not to do. Do you argue the introduction of plpgsql2 would hurt the users of plpgsql in some way? How? If you have X% who continue to happily use plpgsql, and (100-X%) who find they can use plpgsql2 in their project, for new functions or all functions (for a new project), then you have made (100-X)% of the users more happy, than they would be if they were forced to use plpgsql and suffer from its problems. It *would* be a problem if you had to choose between writing all functions in their plpgsql or plpgsql2, but thanks to postgres support for different pl-languages and mixing different languages in the same project, I cannot see the problem. What it's clear from my non-hacker, casual hackers ml reader opinion here, is that there is room for new language features or a new in-core language at once. I find Joel's reasoning quite clear about the general concepts of improving on plpgsql, although the precise changes may not be big enough to justify just a new version. But if there are enough changes, and breaking compatibility with the current plpgsql is a major concern, I fail to buy other arguments of why doing plpgsql2 is a bad thing. The comparisons with Python/Perl are very misleading, as they have nothing to do with Postgres, and the case is obviously different. What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: - Is it feasible (resources, time, interest) to implement PL/SQL in Postgres? - Does it support all the requested new features Joel and others mentioned in this thread as desires for the new language? - If the answer to the previous question is no, could those unsupported features be implemented as a compatible superset of PL/SQL? Sorry if this sounds too unconventional for this list, but this is what IMVHO many users would be more pleased with. My 2 cents, Álvaro -- 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] PL/pgSQL 2
2014-09-01 20:23 GMT+02:00 Joel Jacobson j...@trustly.com: On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it's one-shot or not. I'm glad to hear you think it *might* be worth doing. A one-shot is exactly what it is, like a new major version of postgres itself (but a new major version of postgres has a much longer release note of changes :). Once released, there is obviously no way to include new non-backwards compatible code in future minor versions. I guess it boils down to if the project can agree on if there are any significant *important* changes worth doing that are *not* possible or feasible to implement in plpgsql. I see two possible approaches of a plpgsql2 project, both aiming to require minimal/no changes of most existing best-practice plpgsql code: a) fork plpgsql code base and implement changes with as few lines of code as possible, making it easier to understand the changes, verify their correctness and apply future patches of the plpgsql code. b) fork plpgsql code and remove as much code as possible thanks to the reduced complexity possible thanks to the stricter behaviour achieved by removing settings and enforcing a stricter coding convention and killing obsolete quirks. I don't like a idea so we will have plpgsql 2x without significant redesign you don't throw too much lines. If you really need to design new language, then redesign engine first. Given plpgsql2 is a one-shot, the time window to gather input of what non-compatible changes to include probably needs to be at least a year. During that period, the mostly-compatible changes discussed could be implemented, which are the ones I'm personally most interested in anyway, but if we are creating a new language, then naturally we should take the chance to include all important changes we wish we could do but cannot with plpgsql.
Re: [HACKERS] PL/pgSQL 2
Joel Jacobson j...@trustly.com writes: I see two possible approaches of a plpgsql2 project, both aiming to require minimal/no changes of most existing best-practice plpgsql code: a) fork plpgsql code base and implement changes with as few lines of code as possible, making it easier to understand the changes, verify their correctness and apply future patches of the plpgsql code. b) fork plpgsql code and remove as much code as possible thanks to the reduced complexity possible thanks to the stricter behaviour achieved by removing settings and enforcing a stricter coding convention and killing obsolete quirks. You lost me at fork. Reality is that plpgsql development is not going to stop; wherever possible we are going to want to put new features into both languages, and bug fixes even more so. Moreover, the size of the changes you've suggested hardly merits a fork in the first place, and the notion that we're going to be able to radically simplify the code base after making them is laughable. What I would think about is c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. We could alternatively get the result by inventing a bunch of pragma declarations, or some similar notation, that control the behavioral changes one-at-a-time. That might even be worth doing anyway, in case somebody likes some of the ideas and others not so much. But I'd see the language version as a convenient shorthand for enabling a specified collection of pretty-localized incompatible behavior changes. If they're not pretty localized, there's going to be a barrier to uptake, very comparable to the python3 analogy mentioned upthread. 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] PL/pgSQL 2
=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. 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] PL/pgSQL 2
On 01/09/14 20:42, Tom Lane wrote: =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane It's true that some of the incompatibilities are the core engine, internal functions and so on, and that the plpgsql design goal was to achieve similarity. But similarity is not code compatibility, and afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility, if possible, is a very well first step, only followed by the core functionalities you mention. If postgres were going for a new language, why not implement one which, having the other suggested functionality, also has 1:1 PL/SQL code compatibility? I'm sure it's no trivial task, but one highly desirable. Regards, Álvaro -- 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] PL/pgSQL 2
2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa a...@nosys.es: On 01/09/14 20:42, Tom Lane wrote: =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= a...@nosys.es writes: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. By that I suppose you mean I wish it would act just like Oracle. The problem with such a wish is that a lot of the incompatibilities with Oracle are functions of the core SQL engine, not of the PL. plpgsql already is about as close to PL/SQL as it's possible to get without changing core Postgres behavior --- or at least, that was the original design desire, and I don't think that it's failed in any large degree. regards, tom lane It's true that some of the incompatibilities are the core engine, internal functions and so on, and that the plpgsql design goal was to achieve similarity. But similarity is not code compatibility, and afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility, if possible, is a very well first step, only followed by the core functionalities you mention. If postgres were going for a new language, why not implement one which, having the other suggested functionality, also has 1:1 PL/SQL code compatibility? I'm sure it's no trivial task, but one highly desirable. It is false expectation - language is only one part .. and plpgsql isn't to far. There are different system of modules, different system of custom aggregates, mainly with PL/SQL is very complex library dbms_. This library is maybe more complex than current Postgres base. It is task for commercial project --- not all Postgres users need a Oracle compatibility layer. Next, I am sure, so it is in contradiction to Joel proposal. Regards Pavel Regards, Álvaro -- 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] Built-in binning functions
On 01/09/14 01:42, Tom Lane wrote:
BTW, was there a reason for not noticing the case of exact match in
the search loop, and falling out early? As it stands the code will
reliably choose the leftmost match if there are multiple equal items
in the search array, but do we care about such cases?
I am not sure if we care, probably not.
Anyway I attached patch that I am happy with. I am not yet sure what to
do with naming.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index daa56e9..bda8386 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -901,25 +901,36 @@
indexterm
primarywidth_bucket/primary
/indexterm
-literalfunctionwidth_bucket(parameterop/parameter typenumeric/type, parameterb1/parameter typenumeric/type, parameterb2/parameter typenumeric/type, parametercount/parameter typeint/type)/function/literal
+literalfunctionwidth_bucket(parameteroperand/parameter typenumeric/type, parameterb1/parameter typenumeric/type, parameterb2/parameter typenumeric/type, parametercount/parameter typeint/type)/function/literal
/entry
entrytypeint/type/entry
entryreturn the bucket to which parameteroperand/ would
be assigned in an equidepth histogram with parametercount/
- buckets, in the range parameterb1/ to parameterb2//entry
+ buckets spanning the range parameterb1/ to parameterb2//entry
entryliteralwidth_bucket(5.35, 0.024, 10.06, 5)/literal/entry
entryliteral3/literal/entry
/row
row
- entryliteralfunctionwidth_bucket(parameterop/parameter typedp/type, parameterb1/parameter typedp/type, parameterb2/parameter typedp/type, parametercount/parameter typeint/type)/function/literal/entry
+ entryliteralfunctionwidth_bucket(parameteroperand/parameter typedp/type, parameterb1/parameter typedp/type, parameterb2/parameter typedp/type, parametercount/parameter typeint/type)/function/literal/entry
entrytypeint/type/entry
entryreturn the bucket to which parameteroperand/ would
be assigned in an equidepth histogram with parametercount/
- buckets, in the range parameterb1/ to parameterb2//entry
+ buckets spanning the range parameterb1/ to parameterb2//entry
entryliteralwidth_bucket(5.35, 0.024, 10.06, 5)/literal/entry
entryliteral3/literal/entry
/row
+
+ row
+ entryliteralfunctionwidth_bucket(parameteroperand/parameter typeanyelement/type, parameterthresholds/parameter typeanyarray/type)/function/literal/entry
+ entrytypeint/type/entry
+ entryreturn the bucket to which parameteroperand/ would
+ be assigned given an array listing the upper bounds of the buckets;
+ the parameterthresholds/ array emphasismust be sorted/,
+ smallest first, or unexpected results will be obtained/entry
+ entryliteralwidth_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])/literal/entry
+ entryliteral2/literal/entry
+ /row
/tbody
/tgroup
/table
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index f8e94ec..57376ea 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -15,8 +15,10 @@
#include postgres.h
#include ctype.h
+#include math.h
#include access/htup_details.h
+#include catalog/pg_type.h
#include funcapi.h
#include libpq/pqformat.h
#include utils/array.h
@@ -130,6 +132,15 @@ static ArrayType *array_replace_internal(ArrayType *array,
Datum replace, bool replace_isnull,
bool remove, Oid collation,
FunctionCallInfo fcinfo);
+static int width_bucket_fixed(Datum operand,
+ ArrayType *thresholds,
+ Oid collation,
+ TypeCacheEntry *typentry);
+static int width_bucket_fixed_float8(Datum operand, ArrayType *thresholds);
+static int width_bucket_variable(Datum operand,
+ ArrayType *thresholds,
+ Oid collation,
+ TypeCacheEntry *typentry);
/*
@@ -5502,3 +5513,219 @@ array_replace(PG_FUNCTION_ARGS)
fcinfo);
PG_RETURN_ARRAYTYPE_P(array);
}
+
+/*
+ * Implements width_bucket(anyelement, anyarray).
+ *
+ * 'thresholds' is an array containing upper bound values for each bucket;
+ * these must be sorted from smallest to largest, or bogus results will be
+ * produced. If N thresholds are supplied, the output is from 0 to N:
+ * 0 is for inputs first threshold, N is for inputs = last threshold.
+ */
+Datum
+width_bucket_generic(PG_FUNCTION_ARGS)
+{
+ Datum operand = PG_GETARG_DATUM(0);
+ ArrayType *thresholds = PG_GETARG_ARRAYTYPE_P(1);
+ Oid element_type = ARR_ELEMTYPE(thresholds);
+ int result;
+
+ /* Check input */
+ if (ARR_NDIM(thresholds) 1)
+ ereport(ERROR,
+(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg(thresholds must be one-dimensional
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 8:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: c) plpgsql and plpgsql2 are the same code base, with a small number of places that act differently depending on the language version. +1 to the idea -- 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 for psql History Display on MacOSX
Thanks Tom. This would help the poor mac-osx guys like me. I guess this is not that important because no one runs a production server on OS-X. Back patching to 9.3 won’t work as is, some minor conflict was there. Anyway, I am sure the iteration used in encode_history and decode_history in input.c does not work on libedit. Regards from cologne, Stepan Am 01.09.2014 um 20:05 schrieb Tom Lane t...@sss.pgh.pa.us: Stepan Rutz stepan.r...@gmx.de writes: Attached is a very trivial patch as a basis for discussion that at least makes \s (show history) work in psql on Macs. Macs uses libedit, which has a libreadline interface. Hm. The $64 question here is whether we can assume that history_get() exists and works compatibly in every interesting version of libreadline and libedit. I poked into the oldest version of GNU readline I could find, 4.0 (released in 1999), and that has it. The oldest libedit I have around is the one that came with OSX 10.4 (the CVS marker in readline.h from that says 2004/01/17). That has it too. So that looks pretty good. The readline code says that the argument ranges from history_base up, not from 1 up as this patch assumes. And it looks like history_base can change once the max number of stored lines is exceeded, so we can't assume that 1 is good enough. Fortunately, the global variable history_base also exists in both libraries (though it looks like it never changes from 1 in libedit). Functionally this seems like a clear win over what we had, especially since it supports using the pager. I'm inclined to think we should not only apply this change but back-patch it. One thing worth thinking about: should we use a history_get() loop like this for *all* \s commands, even when the target file is a regular file not /dev/tty? libedit's version of write_history does not write the history in the clear exactly, which you would think is the behavior wanted when saving a command history for any purpose other than updating ~/.psql_history. Such a change would break a workflow that involves doing \s to some random file and then copying that file to ~/.psql_history, but I find it hard to fathom why anyone would do that. There are a couple other minor bugs and some cosmetic things I don't like in this patch, but I'm willing to fix it up and commit it if there are not objections. 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 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] PL/pgSQL 2
On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa a...@nosys.es wrote: What I can add is that, if Postgres is to devote resources to a new language, I would plan it with a broader scope. What would attract most users? Would it bring non postgres users to Postgres? What could be one of the killer features of any next version? My trivial answer to most of these questions is: PL/SQL. I don't know with detail how complex this is to get in Postgres (well, EDB probably knows), but if I had to chose a new language, this is it. So my questions would rather be: Interesting visionary ideas. For me personally, I have no Oracle functions to port to Postgres, so all differences between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work in the process of porting existing code into a new language, be it PL/SQL or PL/pgSQL 2. That said, if PL/SQL wouldn't suffer from the problems I have with PL/pgSQL today, I wouldn't see a problem writing new code in the language, but then I would probably never manage to port all existing code to PL/SQL and I would be stuck with a mix of code in two languages instead of one. With PL/pgSQL 2 on the other hand, it would be feasible to eventually port all my existing code, as most of it would be compatible without changes and the rest would easy to make compatible. I guess it's a question of if it's more important to recruit Oracle users, or if it's more important to improve the life of existing PL/pgSQL Postgres users. -- 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] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns
On Sun, Aug 31, 2014 at 02:10:33PM -0400, Tom Lane wrote:
David G Johnston david.g.johns...@gmail.com writes:
Would it be proper to issue an additional top-level warning with the column
moved notification? Thus there would be NOTICE, NOTICE, WARNING in the
above example? Or, more generically, columns reordered to match inherited
column order to avoid multiple warnings if more than one column is moved.
That's a good point: if this message fires at all, it will probably fire
more than once; do we want that? If we do it as you suggest here, we'll
lose the information as to exactly which columns got relocated, which
perhaps is bad, or maybe it doesn't matter. Also, I don't remember the
exact code structure in that area, but it might be a bit painful to
arrange that we get only one such warning even when inheriting from
multiple parents.
If we do want the specific moved columns to be identified, I'd still go
with errdetail on the NOTICE rather than two separate messages. I think
calling it a WARNING is a bit extreme anyway.
OK, here is the updated output based on the comments:
CREATE TABLE B(a int, c int);
CREATE TABLE a5 (
a integer,
b integer,
c integer
)
INHERITS (b);
NOTICE: merging column a with inherited definition
NOTICE: moving and merging column c with inherited definition
DETAIL: user-specified column moved to the location of the inherited
column
I think we have to mention move in the error message because
mentioning move only in the detail means that the detail actually has
new information, not more detailed information.
Patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
new file mode 100644
index 3720a0f..b88b664
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*** MergeAttributes(List *schema, List *supe
*** 1756,1767
--- 1756,1771
*/
if (inhSchema != NIL)
{
+ int schema_attno = 0;
+
foreach(entry, schema)
{
ColumnDef *newdef = lfirst(entry);
char *attributeName = newdef-colname;
int exist_attno;
+ schema_attno++;
+
/*
* Does it conflict with some previously inherited column?
*/
*** MergeAttributes(List *schema, List *supe
*** 1780,1788
* Yes, try to merge the two column definitions. They must
* have the same type, typmod, and collation.
*/
! ereport(NOTICE,
! (errmsg(merging column \%s\ with inherited definition,
! attributeName)));
def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
typenameTypeIdAndMod(NULL, def-typeName, defTypeId, deftypmod);
typenameTypeIdAndMod(NULL, newdef-typeName, newTypeId, newtypmod);
--- 1784,1797
* Yes, try to merge the two column definitions. They must
* have the same type, typmod, and collation.
*/
! if (exist_attno == schema_attno)
! ereport(NOTICE,
! (errmsg(merging column \%s\ with inherited definition,
! attributeName)));
! else
! ereport(NOTICE,
! (errmsg(moving and merging column \%s\ with inherited definition, attributeName),
! errdetail(user-specified column moved to the location of the inherited column)));
def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
typenameTypeIdAndMod(NULL, def-typeName, defTypeId, deftypmod);
typenameTypeIdAndMod(NULL, newdef-typeName, newTypeId, newtypmod);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
