Re: [HACKERS] NULL input for array_agg()?
2009/11/15 Andrew Gierth and...@tao11.riddles.org.uk: Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi Hi, During reviewing aggregates ORDER BY, I was reading spec Hitoshi and found description like: Hitoshi == snip == Hitoshi Of the rows in the aggregation, the following do not qualify: Hitoshi — If DISTINCT is specified, then redundant duplicates. Hitoshi — Every row in which the value expression evaluates to the null value. Hitoshi == /snip == Where did you find that? In 4.15.4 Aggregate functions. But your snip clarified array aggregate function is special case. NOTE 267 -- Null values are not eliminated when computing array aggregate function. This, plus the optional sort specification list, sets array aggregate function apart from general set functions. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Summary and Plan for Hot Standby
After some time thinking about the best way forward for Hot Standby, I have some observations and proposals. First, the project is very large. We have agreed ways to trim the patch, yet it remains large. Trying to do everything in one lump is almost always a bad plan, so we need to phase things. Second, everybody is keen that HS hits the tree, so we can have alpha code etc.. There are a few remaining issues that should *not* be rushed. The only way to remove this dependency is to decouple parts of the project. Third, testing the patch is difficult and continuous change makes it harder to guarantee everything is working. There are two remaining areas of significant thought/effort: * Issues relating to handling of prepared transactions * How fast Hot Standby mode is enabled in the standby I propose that we stabilise and eventually commit a version of HS that circumvents/defers those issues and then address the issues with separate patches afterwards. This approach will allow us to isolate the areas of further change so we can have a test blitz to remove silly mistakes, then follow it with a commit to CVS, and then release as Alpha to allow further testing. Let's look at the two areas of difficulty in more detail * Issues relating to handling of prepared transactions There are some delicate issues surrounding what happens at the end of recovery if there is a prepared transaction still holding an access exclusive lock. It is straightforward to say, as an interim measure, Hot Standby will not work with max_prepared_transactions 0. I see that this has a fiddly, yet fairly clear solution. * How fast Hot Standby mode is enabled in the standby We need to have full snapshot information on the standby before we can allow connections and queries. There are two basic approaches: i) we wait until we *know* we have full info or ii) we try to collect data and inject a correct starting condition. Waiting (i) may take a while, but is clean and requires only a few lines of code. Injecting the starting condition (ii) requires boatloads of hectic code and we have been unable to agree a way forwards. If we did have that code, all it would give us is a faster/more reliable starting point for connections on the standby. Until we can make approach (ii) work, we should just rely on the easy approach (i). In many cases, the starting point is very similar. (In some cases we can actually make (i) faster because the overhead of data collection forces us to derive the starting conditions minutes apart.) Phasing the commit seems like the only way. Please can we agree a way forwards? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 09:06, Simon Riggs si...@2ndquadrant.com wrote: * Issues relating to handling of prepared transactions There are some delicate issues surrounding what happens at the end of recovery if there is a prepared transaction still holding an access exclusive lock. It is straightforward to say, as an interim measure, Hot Standby will not work with max_prepared_transactions 0. I see that this has a fiddly, yet fairly clear solution. If that restriction will solve issues we have now, I find that a perfectly reasonable restriction. Even if it were to still be there past release, and only get fixed in a future release. The vast majority of our users don't use 2PC at all. Most cases where people had it enalbed used to be because it was enabled by default, and the large majority of cases where I've seen people increase it has actually been because they thought it meant prepared statements, not prepared transactions. So definitely +1. * How fast Hot Standby mode is enabled in the standby We need to have full snapshot information on the standby before we can allow connections and queries. There are two basic approaches: i) we wait until we *know* we have full info or ii) we try to collect data and inject a correct starting condition. Waiting (i) may take a while, but is clean and requires only a few lines of code. Injecting the starting condition (ii) requires boatloads of hectic code and we have been unable to agree a way forwards. If we did have that code, all it would give us is a faster/more reliable starting point for connections on the standby. Until we can make approach (ii) work, we should just rely on the easy approach (i). In many cases, the starting point is very similar. (In some cases we can actually make (i) faster because the overhead of data collection forces us to derive the starting conditions minutes apart.) That also seems perfectly reasonable, depending on how long the waiting on (i) will be :-) What does the time depend on? Phasing the commit seems like the only way. Yeah, we usually recommend that in other cases, so I don't see why it shouldn't apply to HS. Seems like a good way forward. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] commitfest patch move unavailable
After consulting with some other members of the community, I tried to post my fk error string patch to the current commitfest, but mistakenly posted it to the current commitfest, not the open one. When I tried to correct this by moving the patch to the open 2010-01 commitfest, I could not submit the form to do so because the open commitfest does not contain any topics. Seems like a lot of pain for a casual commit... -George -- 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] Postgres and likewise authentication
On Sat, Nov 14, 2009 at 21:07, u235sentinel u235senti...@gmail.com wrote: I'm curious if anyone has tried to link postgres authentication with a product called likewise. Likesoft software will allow a linux/unix system to authenticate against a windows domain. I have it working on several flavors of linux and working on getting it tied into several samba shares. I've heard there is a way to make it work with postgres but couldn't find any details. I'm curious if anyone has tried to do this and would love any tips :D I've never heard of likewise, but PostgreSQL will natively authenticate to a Windows domain using either LDAP or GSSAPI. (Unless you're using a pre-windows2000 windows domain, but for your own sake I really hope you don't...) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 10:00 +0100, Magnus Hagander wrote: What does the time depend on? We need to wait for all current transactions to complete. (i.e. any backend that has (or could) take an xid or an AccessExclusiveLock before it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY. The standby already performs this wait in the case where we overflow the snapshot, so we have 64 subtransactions on *any* current transaction on the master. The reason for that is (again) performance on master: we choose not to WAL log new subtransactions. There are various ways around this and I'm certain we'll come up with something ingenious but my main point is that we don't need to wait for this issue to be solved in order for HS to be usable. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sunday, November 15, 2009, Simon Riggs si...@2ndquadrant.com wrote: On Sun, 2009-11-15 at 10:00 +0100, Magnus Hagander wrote: What does the time depend on? We need to wait for all current transactions to complete. (i.e. any backend that has (or could) take an xid or an AccessExclusiveLock before it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY. The standby already performs this wait in the case where we overflow the snapshot, so we have 64 subtransactions on *any* current transaction on the master. The reason for that is (again) performance on master: we choose not to WAL log new subtransactions. There are various ways around this and I'm certain we'll come up with something ingenious but my main point is that we don't need to wait for this issue to be solved in order for HS to be usable. Yeah, with that explanation (thanks for clearing it up) I agree - it will definitely still be hugely useful even with this restriction, so we realy don't need to delay an initial (or the alpha at least) commit. Thus, +1 on the second one as well :) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, overflowed snapshots, testing
On Sat, 2009-11-14 at 08:43 -0800, Robert Hodges wrote: I can help set up automated basic tests for hot standby using 1+1 setups on Amazon. I¹m already working on tests for warm standby for our commercial Tungsten implementation and need to solve the problem of creating tests that adapt flexibly across different replication mechanisms. I didn't leap immediately to say yes for a couple of reasons. More than 50% of the bugs found on HS now have been theoretical-ish issues that would very difficult to observe, let alone isolate with black box testing. In many cases they are unlikely to happen, but that is not our approach to quality. This shows there isn't a good substitute for very long explanatory comments which are then read and challenged by a reviewer, though I would note Heikki's particular skill in doing that. The second most frequent class of bugs have been unit test bugs, where the modules themselves need better unit testing. Block box testing only works to address this when there is an exhaustive test-coverage driven approach, but even then it's hard to inject real/appropriate conditions into many deeply buried routines. Best way seems to be just multiple debugger sessions and lots of time. HS is characterised by a very low additional feature profile. It leverages many existing modules to create something on the standby that already exists on the primary. So in many ways it is a very different sort of patch to many others. There have been a few dumb-ass bugs and I hold my hand up to those, though the reason is to do with timing of patch delivery and testing. I don't see any long term issues, just unfortunate short term circumstance because of patch churn. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Aggregate ORDER BY patch
Here's my first review.
The patch was in context diff format and applied cleanly with a little
3 hunks in parse_expr.c. make succeeded without any warnings and make
check passed all 121 tests.
It implements as advertised, following SQL spec with extension of both
DISTINCT clause and ORDER BY clause are available in any aggregate
functions including user defined ones. It supports VIEWs by adding
code in ruleutils.c.
Questions here:
- agglevelsup?
We have aggregate capability that all arguments from upper level query
in downer level aggregate makes aggregate call itself to upper level
call, as a constant value in downer level. What if ORDER BY clause has
downer level Vars? For example:
regression=# select (select count(t1.four order by unique1) from tenk1
limit 1) from tenk1 t1 where unique1 10;
?column?
--
1
1
1
1
1
1
1
1
1
1
(10 rows)
regression=# select (select count(t1.four order by t1.unique1) from
tenk1 limit 1) from tenk1 t1 where unique1 10;
?column?
--
10
(1 row)
Is it sane? The result is consistent but surprised me a little. No
need to raise an error?
- order by 1?
Normal ORDER BY clause accepts constant integer as TargetEntry's
resno. The patch seems not to support it.
regression=# select array_agg(four order by 1) from tenk1 where unique1 10;
array_agg
---
{0,2,1,2,1,0,1,3,3,0}
(1 row)
Shouldn't it be the same as normal ORDER BY?
Performance doesn't seem slowing down, though I don't have
quantitative test result.
Coding, almost all sane. Since its syntax and semantics are similar to
existing DISTINCT and ORDER BY features, parsing and transformation
code are derived from those area. The executor has few issues:
- #include in nodeAgg.c
executor/tuptable.h is added in the patch but required really?
I removed that line but still build without any warnings.
- process_ordered_aggregate_(single|multi)
It seems that the patch left process_sorted_aggregate() function as
process_ordered_aggregate_single() and added
process_ordered_aggregate_multi() for more than one input arguments
(actually target entries) case. Why have those two? Could we combine
them? Or I couldn't find convincing reason in comments.
And ParseFuncOrColumn() in parse_func.c now gets more complicated.
Since feature / semantics are similar, I bet we may share some code to
transform DISTINCT and ORDER BY with traditional code in
parse_clause.c, though I'm not sure nor don't have clear idea.
Especially, code around here
save_next_resno = pstate-p_next_resno;
pstate-p_next_resno = attno + 1;
cheats pstate to transform clauses and I felt a bit fear.
- SortGroupClause.implicit
implicit member was added in SortGroupClause. I didn't find clear
reason to add this. Could you show a case to clarify this?
That's it for now.
Regards,
--
Hitoshi Harada
--
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] Hot standby, race condition between recovery snapshot and commit
Simon Riggs wrote: On Sat, 2009-11-14 at 14:59 +0200, Heikki Linnakangas wrote: I can't see any obvious way around that. Huh? We're only doing this strict locking approach because you insisted that the looser approach was not acceptable. Take it easy, Simon. By obvious, I meant trivial or easy. I believe you're referring to this (http://archives.postgresql.org/message-id/4a8ce561.4000...@enterprisedb.com): If there's a way, I would prefer a solution where the RunningXacts snapshot represents the situation the moment it appears in WAL, not some moment before it. It makes the logic easier to understand. or did we have further discussion on that since? Have you forgotten that discussion so completely that you can't even remember the existence of other options? I do remember that. I've been thinking about the looser approach a lot since yesterday. So, if we drop the notion that the running-xacts record represents the situation at the exact moment it appears in WAL, what do we have to change? Creating the running-xacts snapshot becomes easier, but when we replay it, we must take the snapshot with a grain of salt. 1. the snapshot can contain xids that have already finished (= we've already seen the commit/abort record) 2. the snapshot can lack xids belonging to transactions that have just started, between the window when the running-xacts snapshot is taken in the master and it's written to WAL. Problem 1 is quite easy to handle: just check every xid in clog. If it's marked there as finished already, it can be ignored. For problem 2, if a transaction hasn't written any WAL yet, we might as well treat it as not-yet-started in the standby, so we're concerned about transactions that have written a WAL record between when the running-xacts snapshot was taken and written to WAL. Assuming the snapshot was taken after the REDO pointer of the checkpoint record, the standby has seen the WAL record and therefore has all the information it needs. Currently, the standby doesn't add xids to known-assigned list until it sees the running-xacts record, but we could change that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] commitfest patch move unavailable
On Sun, Nov 15, 2009 at 4:21 AM, George Gensure wer...@gmail.com wrote: After consulting with some other members of the community, I tried to post my fk error string patch to the current commitfest, but mistakenly posted it to the current commitfest, not the open one. When I tried to correct this by moving the patch to the open 2010-01 commitfest, I could not submit the form to do so because the open commitfest does not contain any topics. Seems like a lot of pain for a casual commit... Sorry you had trouble. Feel free to suggest improvements. (Maybe I should automatically create a Miscellaneous topic when each new CF is added?) Anyway, it's easy to add topics, so I just went and did that for you. Have at it... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, race condition between recovery snapshot and commit
Oh, forgot to mention another thing that I've been pondering: Currently, the running-xacts record is written to the WAL after the checkpoint record. There's a small chance that you get an xlog switch in between. If that happens, it might take a long time after the checkpoint record until the standby sees the running-xacts record, so it might take a long time until the standby can open up for connections. In general, I'd like to remove as many as possible of those cases where the standby starts up, and can't open up for connections. It makes the standby a lot less useful if you can't rely on it being open. So I'd like to make it so that the standby can *always* open up. There's currently three cases where that can happen: 1. If the subxid cache has overflown. 2. If there's no running-xacts record after the checkpoint record for some reason. For example, one was written but not archive yet, or because the master crashed before it was written. 3. If too many AccessExclusiveLocks was being held. Case 3 should be pretty easy to handle. Just need to WAL log all the AccessExclusiveLocks, perhaps as separate WAL records (we already have a new WAL record type for logging locks) if we're worried about the running-xacts record growing too large. I think we could handle case 2 if we wrote the running-xacts record *before* the checkpoint record. Then it would be always between the REDO pointer of the checkpoint record, and the checkpoint record itself, so it would always be seen by the WAL recovery. To handle case 1, we could scan pg_subtrans. It would add some amount of code and would add some more work to taking the running-xacts snapshot, but it could be done. This isn't absolutely necessary for the first version, but it's something to keep in mind... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Python 3.1 support
On fre, 2009-11-13 at 11:27 -0700, James Pye wrote: Some are TODOs, so in part by other people. Some were briefly touched on in the recent past discussions(around the time that I announced the WIP). Native typing vs conversion, function fragments vs function modules. I'm of course only one user, but these two features don't excite me at all, and certainly not enough to go through the pain of dealing with a second implementation. -- 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] Summary and Plan for Hot Standby
Simon Riggs wrote: We need to wait for all current transactions to complete. (i.e. any backend that has (or could) take an xid or an AccessExclusiveLock before it commits.). Similar-ish to the wait for a CREATE INDEX CONCURRENTLY. The standby already performs this wait in the case where we overflow the snapshot, so we have 64 subtransactions on *any* current transaction on the master. The reason for that is (again) performance on master: we choose not to WAL log new subtransactions. WAL-logging every new subtransaction wouldn't actually help. The problem with subtransactions is that if the subxid cache overflows in the proc array in the master, the information about the parent-child relationshiop is only stored in pg_subtrans, not in proc array. So when we take the running-xacts snapshot, we can't include that information, because there's no easy and fast way to scan pg_subtrans for it. Because that information is not included in the snapshot, the standby doesn't have all the information it needs until after it has seen that all the transactions that had an overflowed xid cache have finished. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: * Issues relating to handling of prepared transactions There are some delicate issues surrounding what happens at the end of recovery if there is a prepared transaction still holding an access exclusive lock. Can you describe in more detail what problem this is again? We had various problems with prepared transactions, but I believe what's in the git repository now handles all those cases (although I just noticed and fixed a bug in it, so it's not very well tested or reviewed yet). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 3:06 AM, Simon Riggs si...@2ndquadrant.com wrote: Please can we agree a way forwards? I don't have a strong position on the technical issues, but I am very much in favor of getting something committed, even something with limitations, as soon as we practically can. Getting this feature into the tree will get a lot more eyeballs on it, and it's much better to do that now, while we still have several months remaining before beta, so those eyeballs can be looking at it for longer - and testing it as part of the regular alpha release process. It will also eliminate the need to repeatedly merge with the main tree, etc. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: There are two remaining areas of significant thought/effort: Here's a list of other TODO items I've collected so far. Some of them are just improvements or nice-to-have stuff, but some are more serious: - If WAL recovery runs out of lock space while acquiring an AccessExclusiveLock on behalf of a transaction that ran in the master, it will FATAL and abort recovery, bringing down the standby. Seems like it should wait/cancel queries instead. - When switching from standby mode to normal operation, we momentarily hold all AccessExclusiveLocks held by prepared xacts twice, needing twice the lock space. You can run out of lock space at that point, causing failover to fail. - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. - The assumption that b-tree vacuum records don't need conflict resolution because we did that with the additional cleanup-info record works ATM, but it hinges on the fact that we don't delete any tuples marked as killed while we do the vacuum. That seems like a low-hanging fruit that I'd actually like to do now that I spotted it, but will then need to fix b-tree vacuum records accordingly. We'd probably need to do something about the previous item first to keep performance acceptable. - There's the optimization to replay of b-tree vacuum records that we discussed earlier: Replay has to touch all leaf pages because of the interlock between heap scans, to ensure that we don't vacuum away a heap tuple that a concurrent index scan is about to visit. Instead of actually reading in and pinning all pages, during replay we could just check that the pages that don't need any other work to be done are not currently pinned in the buffer cache. - Do we do the b-tree page pinning explained in previous point correctly at the end of index vacuum? ISTM we're not visiting any pages after the last page that had dead tuples on it. - code structure. I moved much of the added code to a new standby.c module that now takes care of replaying standby related WAL records. But there's code elsewhere too. I'm not sure if this is a good division but seems better than the original ad hoc arrangement where e.g lock-related WAL handling was in inval.c - The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. - ResolveRecoveryConflictWithVirtualXIDs polls until the victim transactions have ended. It would be much nicer to sleep. We'd need a version of LockAcquire with a timeout. Hmm, IIRC someone submitted a patch for lock timeouts recently. Maybe we could borrow code from that? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Re: Hot standby, race condition between recovery snapshot and commit
On Sun, 2009-11-15 at 14:43 +0200, Heikki Linnakangas wrote: This isn't absolutely necessary for the first version, but it's something to keep in mind... Do I take that as agreement to the phased plan? In general, I'd like to remove as many as possible of those cases where the standby starts up, and can't open up for connections. It makes the standby a lot less useful if you can't rely on it being open. So I'd like to make it so that the standby can *always* open up. Yes, of course. The only reason for restrictions being acceptable is that we have 99% of what we want, yet may lose everything if we play for 100% too quickly. The standby will open quickly in many cases, as is. There are also a range of other ways of doing this. There's currently three cases where that can happen: 1. If the subxid cache has overflown. 2. If there's no running-xacts record after the checkpoint record for some reason. For example, one was written but not archive yet, or because the master crashed before it was written. 3. If too many AccessExclusiveLocks was being held. Case 3 should be pretty easy to handle. Just need to WAL log all the AccessExclusiveLocks, perhaps as separate WAL records (we already have a new WAL record type for logging locks) if we're worried about the running-xacts record growing too large. I think we could handle case 2 if we wrote the running-xacts record *before* the checkpoint record. Then it would be always between the REDO pointer of the checkpoint record, and the checkpoint record itself, so it would always be seen by the WAL recovery. To handle case 1, we could scan pg_subtrans. It would add some amount of code and would add some more work to taking the running-xacts snapshot, but it could be done. Some amount of code requires some amount of thought, followed by some amount of review which takes some amount of time. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: There are two remaining areas of significant thought/effort: Here's a list of other TODO items I've collected so far. Some of them are just improvements or nice-to-have stuff, but some are more serious: - If WAL recovery runs out of lock space while acquiring an AccessExclusiveLock on behalf of a transaction that ran in the master, it will FATAL and abort recovery, bringing down the standby. Seems like it should wait/cancel queries instead. Hard resources will always be an issue. If the standby has less than it needs, then there will be problems. All of those can be corrected by increasing the resources on the standby and restarting. This effects max_connections, max_prepared_transactions, max_locks_per_transaction, as documented. - When switching from standby mode to normal operation, we momentarily hold all AccessExclusiveLocks held by prepared xacts twice, needing twice the lock space. You can run out of lock space at that point, causing failover to fail. That was the issue I mentioned. - When replaying b-tree deletions, we currently wait out/cancel all running (read-only) transactions. We take the ultra-conservative stance because we don't know how recent the tuples being deleted are. If we could store a better estimate for latestRemovedXid in the WAL record, we could make that less conservative. Exactly my point. There are already parts of the patch that may cause usage problems and need further thought. The earlier we get this to people the earlier we will find out what they all are and begin doing something about them. - The assumption that b-tree vacuum records don't need conflict resolution because we did that with the additional cleanup-info record works ATM, but it hinges on the fact that we don't delete any tuples marked as killed while we do the vacuum. That seems like a low-hanging fruit that I'd actually like to do now that I spotted it, but will then need to fix b-tree vacuum records accordingly. We'd probably need to do something about the previous item first to keep performance acceptable. - There's the optimization to replay of b-tree vacuum records that we discussed earlier: Replay has to touch all leaf pages because of the interlock between heap scans, to ensure that we don't vacuum away a heap tuple that a concurrent index scan is about to visit. Instead of actually reading in and pinning all pages, during replay we could just check that the pages that don't need any other work to be done are not currently pinned in the buffer cache. Yes, its an optimization. Not one I consider critical, yet cool and interesting. - Do we do the b-tree page pinning explained in previous point correctly at the end of index vacuum? ISTM we're not visiting any pages after the last page that had dead tuples on it. Looks like a new bug, not previously mentioned. - code structure. I moved much of the added code to a new standby.c module that now takes care of replaying standby related WAL records. But there's code elsewhere too. I'm not sure if this is a good division but seems better than the original ad hoc arrangement where e.g lock-related WAL handling was in inval.c - The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. An issue that will be easily fixable with streaming, since it effectively needs a heartbeat to listen to. Adding a regular stream of WAL records is also possible, but there is no need, unless streaming is somehow in doubt. Again, there is work to do once both are in. - ResolveRecoveryConflictWithVirtualXIDs polls until the victim transactions have ended. It would be much nicer to sleep. We'd need a version of LockAcquire with a timeout. Hmm, IIRC someone submitted a patch for lock timeouts recently. Maybe we could borrow code from that? Nice? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 2:32 PM, Simon Riggs si...@2ndquadrant.com wrote: - The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. An issue that will be easily fixable with streaming, since it effectively needs a heartbeat to listen to. Adding a regular stream of WAL records is also possible, but there is no need, unless streaming is somehow in doubt. Again, there is work to do once both are in. I don't think you need a heartbeat to solve this particular case. You just need to define the standby delay to be current timestamp - timestamp of the conflicting candidate commit record. -- 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] Summary and Plan for Hot Standby
Simon Riggs wrote: On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: - If WAL recovery runs out of lock space while acquiring an AccessExclusiveLock on behalf of a transaction that ran in the master, it will FATAL and abort recovery, bringing down the standby. Seems like it should wait/cancel queries instead. Hard resources will always be an issue. If the standby has less than it needs, then there will be problems. All of those can be corrected by increasing the resources on the standby and restarting. This effects max_connections, max_prepared_transactions, max_locks_per_transaction, as documented. There's no safe setting for those that would let you avoid the issue. No matter how high you set them, it will be possible for read-only backends to consume all the lock space, causing recovery to abort and bring down the standby. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 14:47 +, Greg Stark wrote: On Sun, Nov 15, 2009 at 2:32 PM, Simon Riggs si...@2ndquadrant.com wrote: - The standby delay is measured as current timestamp - timestamp of last replayed commit record. If there's little activity in the master, that can lead to surprising results. For example, imagine that max_standby_delay is set to 8 hours. The standby is fully up-to-date with the master, and there's no write activity in master. After 10 hours, a long reporting query is started in the standby. Ten minutes later, a small transaction is executed in the master that conflicts with the reporting query. I would expect the reporting query to be canceled 8 hours after the conflicting transaction began, but it is in fact canceled immediately, because it's over 8 hours since the last commit record was replayed. An issue that will be easily fixable with streaming, since it effectively needs a heartbeat to listen to. Adding a regular stream of WAL records is also possible, but there is no need, unless streaming is somehow in doubt. Again, there is work to do once both are in. I don't think you need a heartbeat to solve this particular case. You just need to define the standby delay to be current timestamp - timestamp of the conflicting candidate commit record. That's not possible unfortunately. We only have times for commits and aborts. So there could be untimed WAL records ahead of the last timed record. The times of events we know from the log records give us no clue as to when the last non-commit/abort record arrived. We can only do that by (i) specifically augmenting the log with regular, timed WAL records, or (ii) asking WALreceiver directly when it last spoke with the master (ii) is the obvious way to do this when we have streaming replication, and HS assumes this will be available. It need not, and we can do (i) Heikki's case is close to one I would expect to see in many cases: a database that is only active during day feeds a system that runs queries 24x7. Run a VACUUM on the master at night and you could get conflicts that follow the pattern described. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 16:50 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: - If WAL recovery runs out of lock space while acquiring an AccessExclusiveLock on behalf of a transaction that ran in the master, it will FATAL and abort recovery, bringing down the standby. Seems like it should wait/cancel queries instead. Hard resources will always be an issue. If the standby has less than it needs, then there will be problems. All of those can be corrected by increasing the resources on the standby and restarting. This effects max_connections, max_prepared_transactions, max_locks_per_transaction, as documented. There's no safe setting for those that would let you avoid the issue. No matter how high you set them, it will be possible for read-only backends to consume all the lock space, causing recovery to abort and bring down the standby. It can still fail even after we kick everybody off. So why bother? Most people run nowhere near the size limit of their lock tables, and on the standby we only track AccessExclusiveLocks in the Startup process. We gain little by spending time on partial protection against an unlikely issue. (BTW, I'm not suggesting you commit HS immediately. Only that we split into phases, stabilise and test pase 1 soon, then fix the remaining issues later.) -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 9:50 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: - If WAL recovery runs out of lock space while acquiring an AccessExclusiveLock on behalf of a transaction that ran in the master, it will FATAL and abort recovery, bringing down the standby. Seems like it should wait/cancel queries instead. Hard resources will always be an issue. If the standby has less than it needs, then there will be problems. All of those can be corrected by increasing the resources on the standby and restarting. This effects max_connections, max_prepared_transactions, max_locks_per_transaction, as documented. There's no safe setting for those that would let you avoid the issue. No matter how high you set them, it will be possible for read-only backends to consume all the lock space, causing recovery to abort and bring down the standby. OK, but... there will always be things that will bring down the stand-by, just as there will always be things that can bring down the primary. A bucket of ice-water will probably do it, for example. I mean, it would be great to make it better, but is it so bad that we can't postpone that improvement to a follow-on patch? It's not clear to me that it is. I think we should really focus in on things that are likely to make this (1) give wrong answers or (2) won't be able to be fixed in a follow-on patch if they're not right in the original one. Only one or two of the items on your list of additional TODOs seem like they might fall into category (2), and none of them appear to fall into category (1). I predict that if we commit a basic version of this with some annoying limitations for 8.5, people who need the feature will start writing patches to address some of the limitations. No one else is going to undertake any serious development work as long as this remains outside the main tree, for fear of everything changing under them and all their work being wasted. I would like this feature to be as good as possible, but I would like to have it at all more. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
On Thu, 12 Nov 2009 11:22:32 -0500 Andrew Chernow a...@esilo.com wrote: However I share Greg's concerns that people are trying to use NOTIFY as a message queue which it is not designed to be. When you have an established libpq connection waiting for notifies it is not unreasonable to expect/desire a payload. ISTM, the problem is that the initial design was half-baked. NOTIFY is event-driven, ie. no polling! I agree. Wouldn't it make sense to allow the user to pass libpq a callback function which is executed when NOTIFY events happen? Currently we are forced to poll the connection, which means that we'll be checking for a NOTIFY every time we have new data. That just doesn't make sense. -- Alex -- 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 - Report the schema along table name in a referential failure error message
George Gensure wer...@gmail.com writes: I've put together a small patch to provide a schema name in an fk violation in deference to the todo item Report the schema along table name in a referential failure error message This is not the way forward; if it were, we would have done it years ago. Despite the poor wording of the TODO item, nobody is particularly interested in solving this problem one error message at a time. Furthermore, inserting the schema name into the text as you have done it is 100% wrong --- in an example like ... table non_searched_schema.fknsref violates ... the reader could be excused for thinking that the report is showing an unqualified name that happens to include a dot, because that's what double quotes imply in SQL. And it certainly does not help client-side tools that want to extract the full table name, which is the real subtext behind many of the requests for this. The direction that we really want to move in is to include the table and schema names as well as other elements of the standard diagnostics area as separate fields in error reports. That will be a great deal of work unfortunately :-( which is why it hasn't been tackled yet. 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] Summary and Plan for Hot Standby
Robert Haas wrote: OK, but... there will always be things that will bring down the stand-by, just as there will always be things that can bring down the primary. A bucket of ice-water will probably do it, for example. I mean, it would be great to make it better, but is it so bad that we can't postpone that improvement to a follow-on patch? We're not talking about a bucket of ice-water. We're talking about issuing SELECTs to a lot of different tables in a single transaction. Only one or two of the items on your list of additional TODOs seem like they might fall into category (2), and none of them appear to fall into category (1). At least the b-tree vacuum bug could cause incorrect answers, even though it would be extremely hard to run into it in practice. I predict that if we commit a basic version of this with some annoying limitations for 8.5, people who need the feature will start writing patches to address some of the limitations. No one else is going to undertake any serious development work as long as this remains outside the main tree, for fear of everything changing under them and all their work being wasted. I would like this feature to be as good as possible, but I would like to have it at all more. Agreed. Believe me, I'd like to have this committed as much as everyone else. But once I do that, I'm also committing myself to fix all the remaining issues before the release. The criteria for committing is: is it good enough that we could release it tomorrow with no further changes? Nothing more, nothing less. We have *already* postponed a lot of nice-to-have stuff like the functions to control recovery. And yes, many of the things I listed in the TODO are not must-haves and we could well release without them. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: The assumption that b-tree vacuum records don't need conflict resolution because we did that with the additional cleanup-info record works ATM, but it hinges on the fact that we don't delete any tuples marked as killed while we do the vacuum. That seems like a low-hanging fruit that I'd actually like to do now that I spotted it, but will then need to fix b-tree vacuum records accordingly. We'd probably need to do something about the previous item first to keep performance acceptable. We can optimise that by using the xlog pointer of the HeapInfo record. Any blocks cleaned that haven't been further updated can avoid generating further btree deletion records. If you do this the straightforward way then it will just generate a stream of btree deletion records that will ruin usability. You spotted this issue only this morning?? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: The assumption that b-tree vacuum records don't need conflict resolution because we did that with the additional cleanup-info record works ATM, but it hinges on the fact that we don't delete any tuples marked as killed while we do the vacuum. That seems like a low-hanging fruit that I'd actually like to do now that I spotted it, but will then need to fix b-tree vacuum records accordingly. We'd probably need to do something about the previous item first to keep performance acceptable. We can optimise that by using the xlog pointer of the HeapInfo record. Any blocks cleaned that haven't been further updated can avoid generating further btree deletion records. Sorry, I don't understand that. (Remember that marking index tuples as killed is not WAL-logged.) You spotted this issue only this morning?? Yesterday evening. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] named parameters in SQL functions
At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. Would this be limited to sql functions? I only ask because for non-sql functions we currently prefix parameter names with an underscore, but a built-in special marker would be much more desirable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Dunstan and...@dunslane.net: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? what about $name ? Personally I prefer :name, but this colidates with psql local variables :( Pavel cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 19:36 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote: The assumption that b-tree vacuum records don't need conflict resolution because we did that with the additional cleanup-info record works ATM, but it hinges on the fact that we don't delete any tuples marked as killed while we do the vacuum. That seems like a low-hanging fruit that I'd actually like to do now that I spotted it, but will then need to fix b-tree vacuum records accordingly. We'd probably need to do something about the previous item first to keep performance acceptable. We can optimise that by using the xlog pointer of the HeapInfo record. Any blocks cleaned that haven't been further updated can avoid generating further btree deletion records. Sorry, I don't understand that. (Remember that marking index tuples as killed is not WAL-logged.) Remember that blocks are marked with an LSN? When we insert a WAL record it has an LSN also. So we can tell which btree blocks might have had been written to after the HeapInfo record is generated. So if a block hasn't been recently updated or it doesn't have any killed tuples then we need not generate a record to handle a possible conflict. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Chernow a...@esilo.com: Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. no, it should be safe (if you don't use for dollar quoting some like $variablename$) Pavel Would this be limited to sql functions? I only ask because for non-sql functions we currently prefix parameter names with an underscore, but a built-in special marker would be much more desirable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan and...@dunslane.net wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? We could also just throw an error if there is any ambiguity. I kind of like the idea of a special marker for both SQL and PL/pgsql, but Tom has been negative on that idea in the past. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - Report the schema along table name in a referential failure error message
On Sun, Nov 15, 2009 at 11:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: George Gensure wer...@gmail.com writes: I've put together a small patch to provide a schema name in an fk violation in deference to the todo item Report the schema along table name in a referential failure error message This is not the way forward; if it were, we would have done it years ago. Despite the poor wording of the TODO item, nobody is particularly interested in solving this problem one error message at a time. Furthermore, inserting the schema name into the text as you have done it is 100% wrong --- in an example like ... table non_searched_schema.fknsref violates ... the reader could be excused for thinking that the report is showing an unqualified name that happens to include a dot, because that's what double quotes imply in SQL. And it certainly does not help client-side tools that want to extract the full table name, which is the real subtext behind many of the requests for this. The direction that we really want to move in is to include the table and schema names as well as other elements of the standard diagnostics area as separate fields in error reports. That will be a great deal of work unfortunately :-( which is why it hasn't been tackled yet. regards, tom lane Fair enough, and I hadn't even considered that dots could be valid chars in table names. I noted your post in the chain attached to this todo request in which you said this is a much bigger problem, but didn't think that you would have left it marked as easy if you thought there should be something done that makes the original error string modification pointless. This begs a bigger question: what's *really* easy or low barrier to entry for very light contributors like myself? - I've got time, I like the product, I need to know what's going to get you a win, I may not be gunning particularly for the feature myself. Its fascinating that this item also included a mention of straw polling in its thread. Thanks, -George -- 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] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 11:49 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Agreed. Believe me, I'd like to have this committed as much as everyone else. But once I do that, I'm also committing myself to fix all the remaining issues before the release. The criteria for committing is: is it good enough that we could release it tomorrow with no further changes? Nothing more, nothing less. I agree with the criteria but I think their application to the present set of facts is debatable. If the b-tree vacuum bug can cause incorrect answers, then it is a bug and we have to fix it. But a query getting canceled because it touches a lot of tables sounds more like a limitation than an outright bug, and I'm not sure you should feel like you're on the hook for that, especially if the problem can be mitigated by adjusting settings. Of course, on the flip side, if the problem is likely to occur frequently enough to make the whole system unusable in practice, then maybe it does need to be fixed. I don't know. It's not my place and I don't intend to question your technical judgment on what does or does not need to be fixed, the moreso since I haven't read or thought deeply about the latest patch. I'm just throwing it out there. The other problem is that we have another big patch sitting right behind this one waiting for your attention as soon as you get this one off your chest. I know Simon has said that he feels that the effort to finish the HS and SR patches for 9/15 was somewhat of an artificial deadline, but ISTM that with only 3 months remaining until the close of the final CommitFest for this release, and two major patches to merged, we're starting to get tight on time. Presumably there will be problems with both patches that are discovered only after committing them, and we need some time for those to shake out. If not enough of that shaking out happens during the regular development cycle, it will either prolong beta and therefore delay the release, or the release will be buggy. All that having been said, the possibility that I'm a pessimistic worry-wort certainly can't be ruled out. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow a...@esilo.com wrote: Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. I think that would be a big break with everything else and very non-sql-ish. We don't use these in plpgsql and we don't use them anywhere else in sql. Moreover you would still have conflicts possible because sql can quote identifiers so people can have columns named $foo. You would have a weird syntactic detail where $foo would mean something different than $foo even though they're both valid identifiers. I'm not sure it wouldn't conflict with some drivers either. DBI uses :foo and ? but I have a vague recollection some drivers did use $foo. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Listen / Notify - what to do when the queue is full
We still need to decide what to do with queue full situations in the proposed listen/notify implementation. I have a new version of the patch to allow for a variable payload size. However, the whole notification must fit into one page so the payload needs to be less than 8K. I have also added the XID, so that we can write to the queue before committing to clog which allows for rollback if we encounter write errors (disk full for example). Especially the implications of this change make the patch a lot more complicated. The queue is slru-based, slru uses int page numbers, so we can use up to 2147483647 (INT_MAX) pages with some small changes in slru.c. When do we have a full queue? Well, the idea is that notifications are written to the queue and that they are read as soon as the notifying transaction commits. Only if a listening backend is busy, it won't read the notifications and so it won't update its pointer for some time. With the current space we can acommodate at least 2147483647 notifications or more, depending on the payload length. That gives us something in between of 214 GB (100 Bytes per notification) and 17 TB (8000 Bytes per notification). So in order to have a full queue, we need to generate that amount of notifications while one backend is still busy and is not reading the accumulating notifications. In general chances are not too high that anyone will ever have a full notification queue, but we need to define the behavior anyway... These are the solutions that I currently see: 1) drop new notifications if the queue is full (silently or with rollback) 2) block until readers catch up (what if the backend that tries to write the notifications actually is the lazy reader that everybody is waiting for to proceed?) 3) invent a new signal reason and send SIGUSR1 to the lazy readers, they need to interrupt whatever they are doing and copy the notifications into their own address space (without delivering the notifications since they are in a transaction at that moment). For 1) there can be warnings way ahead of when the queue is actually full, like one when it is 50% full, another one when it is 75% full and so on and they could point to the backend that is most behind in reading notifications... I think that 2) is the least practical approach. If there is a pile of at least 2,147,483,647 notifications, then a backend hasn't read the notifications for a long long time... Chances are low that it will read them within the next few seconds. In a sense 2) implies 3) for the special case that the writing backend is the one that everybody is waiting for to proceed reading notifications, in the end this backend is waiting for itself. For 3) the question is if we can just invent a new signal reason PROCSIG_NOTIFYCOPY_INTERRUPT or similar and upon reception the backend copies the notification data to its private address space? Would this function be called by every backend after at most a few seconds even if it is processing a long running query? Admittedly, once 3) is in place we can also put a smaller queue into shared memory and remove the slru thing alltogether but we need to be sure that we can interrupt the backends at any time since the queue size would be a lot smaller than 200 GB... Joachim -- 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] named parameters in SQL functions
On Nov 15, 2009, at 10:19 AM, Greg Stark wrote: I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. I think that would be a big break with everything else and very non-sql-ish. We don't use these in plpgsql and we don't use them anywhere else in sql. *ahem* $1 *ahem* Moreover you would still have conflicts possible because sql can quote identifiers so people can have columns named $foo. You would have a weird syntactic detail where $foo would mean something different than $foo even though they're both valid identifiers. Same with Foo and Foo, no? I'm not sure it wouldn't conflict with some drivers either. DBI uses :foo and ? but I have a vague recollection some drivers did use $foo. I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call. Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote:
On Sun, 2009-11-15 at 19:36 +0200, Heikki Linnakangas wrote:
Simon Riggs wrote:
On Sun, 2009-11-15 at 16:07 +0200, Heikki Linnakangas wrote:
The assumption that b-tree vacuum records don't need conflict
resolution because we did that with the additional cleanup-info record
works ATM, but it hinges on the fact that we don't delete any tuples
marked as killed while we do the vacuum.
That seems like a low-hanging
fruit that I'd actually like to do now that I spotted it, but will
then need to fix b-tree vacuum records accordingly. We'd probably need
to do something about the previous item first to keep performance
acceptable.
We can optimise that by using the xlog pointer of the HeapInfo record.
Any blocks cleaned that haven't been further updated can avoid
generating further btree deletion records.
Sorry, I don't understand that. (Remember that marking index tuples as
killed is not WAL-logged.)
Remember that blocks are marked with an LSN? When we insert a WAL record
it has an LSN also. So we can tell which btree blocks might have had
been written to after the HeapInfo record is generated. So if a block
hasn't been recently updated or it doesn't have any killed tuples then
we need not generate a record to handle a possible conflict.
Hmm, perhaps we're talking about the same thing. What I'm seeing is that
we could easily do this:
*** a/src/backend/access/nbtree/nbtree.c
--- b/src/backend/access/nbtree/nbtree.c
***
*** 843,855 restart:
offnum = maxoff;
offnum = OffsetNumberNext(offnum))
{
IndexTuple itup;
ItemPointer htup;
! itup = (IndexTuple) PageGetItem(page,
!
PageGetItemId(page, offnum));
htup = (itup-t_tid);
! if (callback(htup, callback_state))
deletable[ndeletable++] = offnum;
}
}
--- 843,856
offnum = maxoff;
offnum = OffsetNumberNext(offnum))
{
+ ItemId itemid;
IndexTuple itup;
ItemPointer htup;
! itemid = PageGetItemId(page, offnum);
! itup = (IndexTuple) PageGetItem(page, itemid);
htup = (itup-t_tid);
! if (callback(htup, callback_state) ||
ItemIdIsDead(itemid))
deletable[ndeletable++] = offnum;
}
}
But if we do that, b-tree vacuum records are going to need conflict
resolution, just like the b-tree non-vacuum deletion records. The LSN
doesn't help there, because when an itemid is marked as dead, the LSN is
not updated.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote: 3. Every distinct notification is delivered. Regarding performance, the slru-queue is not fsync-ed to disk These two statements seem to be in opposition. How do you know a notification will be delivered if the queue is non-recoverable? Surely the idea is to send information externally to the database, so why should that stream of info be altered depending upon whether the database crashes? You couldn't use it to reliably update an external cache for example. Why do we need this as well as PgQ? For me, I would need a good reason why this shouldn't be implemented using a normal table, plus bells and whistles. If normal tables don't do what you need, perhaps that's a place to add value. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Robert Haas wrote: But a query getting canceled because it touches a lot of tables sounds more like a limitation than an outright bug, It's not that the query might get canceled. It will abort WAL recovery, kill all backends, and bring the whole standby down. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 David E. Wheeler da...@kineticode.com: On Nov 15, 2009, at 10:19 AM, Greg Stark wrote: I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. I think that would be a big break with everything else and very non-sql-ish. We don't use these in plpgsql and we don't use them anywhere else in sql. *ahem* $1 *ahem* Moreover you would still have conflicts possible because sql can quote identifiers so people can have columns named $foo. You would have a weird syntactic detail where $foo would mean something different than $foo even though they're both valid identifiers. Same with Foo and Foo, no? I'm not sure it wouldn't conflict with some drivers either. DBI uses :foo and ? but I have a vague recollection some drivers did use $foo. I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call. Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. @var or @@var should be a break for people from MySQL. @var are r/w in MySQL and @@var are global in T-SQL. So people could be confused. Regards Pavel Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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 - Report the schema along table name in a referential failure error message
George Gensure wrote: This begs a bigger question: what's *really* easy or low barrier to entry for very light contributors like myself? - I've got time, I like the product, I need to know what's going to get you a win, I may not be gunning particularly for the feature myself. The TODO list at http://wiki.postgresql.org/wiki/Todo doesn't seem to have a huge number or [E] items. Maybe we need a bit of a brainstorm to come up with a few more. The one I just started talking about (using param names in SQL functions) might not be terribly hard, depending on your coding skills, since it would be making use of the new parser hooks feature that Tom has just done the heavy lifting on. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 13:15 -0500, Robert Haas wrote: I know Simon has said that he feels that the effort to finish the HS and SR patches for 9/15 was somewhat of an artificial deadline, but ISTM that with only 3 months remaining until the close of the final CommitFest for this release, and two major patches to merged, we're starting to get tight on time. As of further concerns about initial snapshot conditions, I agree we are now tight on time. Presumably there will be problems with both patches that are discovered only after committing them, and we need some time for those to shake out. If not enough of that shaking out happens during the regular development cycle, it will either prolong beta and therefore delay the release, or the release will be buggy. I'm not worried about bugs. Fixes for those can go in anytime. Missing features and small usability enhancements will be forced to wait another year and cause upgrades for early adopters. That worries me. REL8_0 shipped with an unusable bgwriter implementation and I've always been wary of the need for minor tweaks late in a release since then. I've not asked for an immediate commit, but we do need an agreed period patch stability to allow testing, prior to a commit. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. @var or @@var should be a break for people from MySQL. @var are r/w in MySQL and @@var are global in T-SQL. So people could be confused. Besides, do we think MySQL and T-SQL are the heights of good language design? -- 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] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler da...@kineticode.com wrote: Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. I'm japh too -- but that doesn't mean grabbing one little aesthetic from Perl without copying the whole concept behind it makes any sense. Perl sigils are an important part of the language and are a basic part of the syntax. They aren't just a this is a variable marker. Dropping one use of them into a language that doesn't use them anywhere else just makes the language into a mishmash. I don't see any purpose to using such markers anyways. We have a parser, we have a symbol table, we should use them; these identifiers are just like other identifiers. -- 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] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote: The LSN doesn't help there, because when an itemid is marked as dead, the LSN is not updated. I was thinking we could update the index block LSN without writing WAL using the LSN of the heap block that leads to the killed tuple. Pretending that the block might need flushing won't do much harm. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/16 Andrew Dunstan and...@dunslane.net: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? Sorry if I'm missing something important here, but why not just resolve the parameter names in whatever way PL/PgSQL has been doing it? It seems to work well. FWIW I always prefix my parameter names with _ to differentiate them from columns. Cheers, BJ -- 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] named parameters in SQL functions
2009/11/15 Greg Stark gsst...@mit.edu: On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. @var or @@var should be a break for people from MySQL. @var are r/w in MySQL and @@var are global in T-SQL. So people could be confused. Besides, do we think MySQL and T-SQL are the heights of good language design? sure no. But same arguments against to :var should be used to @var. pgscript use it. I don't know the best semantic. But I am not happy from this proposals. I don't see any consistency. Pavel -- 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] named parameters in SQL functions
On Nov 15, 2009, at 10:54 AM, Greg Stark wrote: I'm japh too -- but that doesn't mean grabbing one little aesthetic from Perl without copying the whole concept behind it makes any sense. Perl sigils are an important part of the language and are a basic part of the syntax. They aren't just a this is a variable marker. Dropping one use of them into a language that doesn't use them anywhere else just makes the language into a mishmash. Well, no, just because we're talking about adopting $var doesn't mean we're trying to turn SQL or PL/pgSQL into Perl. It means that we want to signify that a token is a variable, as opposed to something else (hence “sigil”). That doesn't make it a mishmash unless you think you suddenly have Perl (or shell) semantics, which would be a pretty weird expectation. I don't see any purpose to using such markers anyways. We have a parser, we have a symbol table, we should use them; these identifiers are just like other identifiers. See the discussion of conflicts with column names in the recent thread. A sigil would eliminate that problem -- and we already have $1 and friends, so this is just an extension of that in my view. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. no, it should be safe (if you don't use for dollar quoting some like $variablename$) Actually, I was thinking of something like $abc$def, where abc and def are variables. Although, this is much less likely than column name conflicts. Other possibles are: $(var), @var@, or %var%. I'd perfer a single character marker but that may not fly. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Chernow a...@esilo.com: I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. no, it should be safe (if you don't use for dollar quoting some like $variablename$) Actually, I was thinking of something like $abc$def, where abc and def are variables. Although, this is much less likely than column name conflicts. Other possibles are: $(var), @var@, or %var%. I'd perfer a single character marker but that may not fly. single character is my preference too. Pavel -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote: The LSN doesn't help there, because when an itemid is marked as dead, the LSN is not updated. I was thinking we could update the index block LSN without writing WAL using the LSN of the heap block that leads to the killed tuple. That can be before the cleanup record we write before we start the index vacuum. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler da...@kineticode.com wrote: Moreover you would still have conflicts possible because sql can quote identifiers so people can have columns named $foo. You would have a weird syntactic detail where $foo would mean something different than $foo even though they're both valid identifiers. Same with Foo and Foo, no? No, that's not the same. The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then $foo would refer to the same identifier. You're introducing a meaning for $foo but saying there's no valid way to quote the identifier to get the same thing. And worse, if you do quote it you get something else entirely different. -- 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] named parameters in SQL functions
On Nov 15, 2009, at 11:21 AM, Greg Stark wrote: No, that's not the same. The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then $foo would refer to the same identifier. You're introducing a meaning for $foo but saying there's no valid way to quote the identifier to get the same thing. And worse, if you do quote it you get something else entirely different. $foo should be killed off as a valid identifier, IMNSHO. But failing that, some other sigil would be most welcome. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 20:37 +0200, Heikki Linnakangas wrote: Robert Haas wrote: But a query getting canceled because it touches a lot of tables sounds more like a limitation than an outright bug, It's not that the query might get canceled. It will abort WAL recovery, kill all backends, and bring the whole standby down. Hmm, I think the incredible exploding Hot Standby is overstating this somewhat. We can improve the error handling for this rare case for which a simple workaround exists, but it seems like we should punt to phase 2. You agree there should be two phases? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER ROLE/DATABASE RESET ALL versus security
Tom Lane wrote: It looks to me like the code in AlterSetting() will allow an ordinary user to blow away all settings for himself. Even those that are for SUSET variables and were presumably set for him by a superuser. Isn't this a security hole? I would expect that an unprivileged user should not be able to change such settings, not even to the extent of reverting to the installation-wide default. Yes, I completely overlooked the fact that users should not be able to blow away GUCs set by superuser. I can't handle this right now though, as I'm leaving in a couple of days and won't return until cca. Dec. 1st. If this can wait (and I think it does) then I'll handle it then; otherwise I'd appreciate if someone else could take a look and fix it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] named parameters in SQL functions
On Sun, Nov 15, 2009 at 7:25 PM, David E. Wheeler da...@kineticode.com wrote: On Nov 15, 2009, at 11:21 AM, Greg Stark wrote: $foo should be killed off as a valid identifier, IMNSHO. But failing that, some other sigil would be most welcome. I don't think SQL is the height of language design either. But trying to turn it into another language piece by piece is not gong to make it any nicer. A sigil here doesn't accomplish anything. The identifiers in question are *just* like other identifiers. They can be used in expressions just like other columns, they have various types, they have the same syntax as other columns, the sigil doesn't mean anything. I think what may be making this tempting is that they look vaguely like ODBC/JDBC/DBI placeholders like :foo. However they're very very different. In those cases the sigil is marking the sigil outside the SQL syntax. They will be replaced textually without parsing the SQL at all. It's actually very confusing having $foo indicate something within SQL since it makes it look like it's some external thing from another layer like the placeholders. -- 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] Summary and Plan for Hot Standby
On Sun, Nov 15, 2009 at 7:29 PM, Simon Riggs si...@2ndquadrant.com wrote: You agree there should be two phases? I don't understand this repeated suggestion of phases. Nobody's every suggested that we would refuse to add new features to HS after the initial commit or the 8.5 release. Of course there should be later features if you or anyone else is interested in working on them. Or are asking whether we should commit it before it's a usable subset of the functionality? Personally I am in favour of earlier more fine-grained commits but I think the horse has left the stable on that one. We have a usable subset of the functionality in this patch already, don't we? -- 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] named parameters in SQL functions
David E. Wheeler wrote:
$foo should be killed off as a valid identifier, IMNSHO.
It's only legal when quoted. Unquoted indetifiers can't begin with $.
see scan.l:
ident_start [A-Za-z\200-\377_]
ident_cont [A-Za-z\200-\377_0-9\$]
identifier {ident_start}{ident_cont}*
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 21:20 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote: The LSN doesn't help there, because when an itemid is marked as dead, the LSN is not updated. I was thinking we could update the index block LSN without writing WAL using the LSN of the heap block that leads to the killed tuple. That can be before the cleanup record we write before we start the index vacuum. Oh well. Strike 1. But the technique sounds OK, we just need to get the LSN of a HeapInfo record from somewhere, say, index metapage. Sounds like we need to do something similar with the xid. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: You agree there should be two phases? I'm hesitant to say 'yes', because then you will harass me with but you said that you would be OK with fixing X, Y, Z later! Why don't you commit already!. Of course there should be several phases! We've *already* punted a lot of stuff from this first increment we're currently working on. The criteria for getting this first phase committed is: could we release with no further changes? If you actually want to help, can you please focus on fixing the must-fix bugs we know about? We can then discuss which of the remaining known issues we're willing to live with. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then $foo would refer to the same identifier. This case already exists via $1 and $1. Making '$' a marker for parameters wouldn't introduce it. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Aggregate ORDER BY patch
Hitoshi == Hitoshi Harada umi.tan...@gmail.com writes: Hitoshi Questions here: Hitoshi - agglevelsup? Hitoshi We have aggregate capability that all arguments from upper Hitoshi level query in downer level aggregate makes aggregate call Hitoshi itself to upper level call, as a constant value in downer Hitoshi level. What if ORDER BY clause has downer level Vars? For determining what query level the aggregate belongs to, the expressions in the ORDER BY clause are counted along with the actual argument expressions. Hitoshi Is it sane? The result is consistent but surprised me a Hitoshi little. No need to raise an error? What case exactly would you consider an error? When an order by expression references a lower (more deeply nested) query level than any of the actual arguments? Hitoshi - order by 1? Hitoshi Normal ORDER BY clause accepts constant integer as Hitoshi TargetEntry's resno. The patch seems not to support it. Hitoshi Shouldn't it be the same as normal ORDER BY? Specifically documented. The SQL spec doesn't allow ordinal positions in ORDER BY any more (those are a holdover from SQL92) and we don't support them in, for example, window ORDER BY clauses. Hitoshi Performance doesn't seem slowing down, though I don't have Hitoshi quantitative test result. The performance is intended to be no worse than DISTINCT already was, though it's also no better. Hitoshi Coding, almost all sane. Since its syntax and semantics are Hitoshi similar to existing DISTINCT and ORDER BY features, parsing Hitoshi and transformation code are derived from those area. The Hitoshi executor has few issues: Hitoshi - #include in nodeAgg.c Hitoshi executor/tuptable.h is added in the patch but required really? Hitoshi I removed that line but still build without any warnings. The code is making explicit use of various Slot calls declared in tuptable.h. The only reason why it builds without error when you remove that is that utils/tuplesort.h happens to include tuptable.h indirectly. Hitoshi - process_ordered_aggregate_(single|multi) Hitoshi It seems that the patch left process_sorted_aggregate() Hitoshi function as process_ordered_aggregate_single() and added Hitoshi process_ordered_aggregate_multi() for more than one input Hitoshi arguments (actually target entries) case. Why have those Hitoshi two? Could we combine them? Or I couldn't find convincing Hitoshi reason in comments. Performance. tuplesort_getdatum etc. seems to be substantially faster than tuplesort_gettupleslot especially for the case where you're sorting a pass-by-value datum such as an integer (since the datum is then stored only in the sort tuple header and doesn't require a separate space allocation for itself). Using a slot in all cases would have slowed down some common cases like count(distinct id) by a measurable amount. Cases like array_agg(x order by x) benefit from the faster code path too. The memory management between the two cases is sufficiently different that combining them into one function while still maintaining the slot vs. datum distinction would be ugly and probably error-prone. The relatively minor duplication of logic seemed much clearer to me. Hitoshi And ParseFuncOrColumn() in parse_func.c now gets more Hitoshi complicated. I thought very hard about breaking some of that out into a separate function, but it wasn't initially clear which parts might have needed access to the original raw parsetree. I'm open to opinions on this. Hitoshi Since feature / semantics are similar, I bet we may share Hitoshi some code to transform DISTINCT and ORDER BY with Hitoshi traditional code in parse_clause.c, though I'm not sure nor Hitoshi don't have clear idea. Especially, code around here Hitoshi save_next_resno = pstate-p_next_resno; Hitoshi pstate-p_next_resno = attno + 1; Hitoshi cheats pstate to transform clauses and I felt a bit fear. The code that transforms RETURNING clauses does something similar with p_next_resno. Almost all the work of transforming the ORDER BY clause is actually done via the existing transformSortClause (which is the reason why p_next_resno needs to be saved and restored), the additional logic is only for the DISTINCT case, to validate the correspondance between DISTINCT args and ORDER BY args and to generate implicit ordering clauses (which provide comparison function info to the executor) when needed. Hitoshi - SortGroupClause.implicit Hitoshi implicit member was added in SortGroupClause. I didn't Hitoshi find clear reason to add this. Could you show a case to Hitoshi clarify this? Without that flag or something like it, when you do create view foo as select count(distinct x) from table; and then display the view definition, you would get back the query as select count(distinct x order by x) from table which would be confusing and unnecessarily backwards- and forwards-incompatible. So the code sets implicit for any SortGroupClause that is added for
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow a...@esilo.com wrote: The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then $foo would refer to the same identifier. This case already exists via $1 and $1. Making '$' a marker for parameters wouldn't introduce it. True, $1 etc were already very non-sqlish, but that doesn't mean we have to compound things. So here are some examples where you can see what having this wart would introduce: 1) Error messages which mention column names are supposed to quote the column name to set it apart from the error string. This also guarantees that weird column names are referenced correctly as foo bar or $foo so the reference in the error string is unambiguous and can be pasted into queries. This won't work for $foo which would have to be embedded in the error text without quotes. 2) What would the default names for columns be if you did something like create function f(foo) as 'select $foo' If I then use this in another function create function g(foo) as 'select $foo+$foo from f()' I have to quote the column? The point here is that these sigils will leak out, they don't mean much to begin with except to indicate that this identifier is immune to the regular scoping rules but things get more confusing when they leak out and they start appearing in places that are subject to the regular scoping rules. 3) If I have a report generator which takes a list of columns to include in the report, or an ORM which tries to generate queries the usual way to write such things is to just routinely quote every identifier. This is less error-prone and simpler to code than trying to identify which identifiers need quoting and which don't. However in if the query is then dropped into a function the ORM or query generator would have to know which columns cannot be quoted based on syntactic information it can't really deduce. -- 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] Summary and Plan for Hot Standby
Simon Riggs wrote: On Sun, 2009-11-15 at 21:20 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 20:30 +0200, Heikki Linnakangas wrote: The LSN doesn't help there, because when an itemid is marked as dead, the LSN is not updated. I was thinking we could update the index block LSN without writing WAL using the LSN of the heap block that leads to the killed tuple. That can be before the cleanup record we write before we start the index vacuum. Oh well. Strike 1. But the technique sounds OK, we just need to get the LSN of a HeapInfo record from somewhere, say, index metapage. Sounds like we need to do something similar with the xid. I'm thinking that we should address the general issue, not just with vacuum-related deletion records. For the vacuum-related deletion records, we can just leave the code as it is. I think we talked about various approaches about a year ago when we first realized that killed index tuples are a problem, though I don't think we carved out a full solution. We could for example stored the xmax (or xmin if it was inserted by an aborted transaction) of the killed tuple in the b-tree page header whenever we mark an index tuple as dead. We could then include that in the WAL record. The trick is how to make that crash-safe. (but this whole thing is certainly something we can defer until later) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Re: Hot standby, race condition between recovery snapshot and commit
On Sun, 2009-11-15 at 21:37 +0200, Heikki Linnakangas wrote: Am I missing anything? Will review. I also experimented with including the running-xacts information in the checkpoint record itself. That somehow feels more straightforward to me, but it wasn't really any less code, and it wouldn't allow us to do the running-xacts snapshot as multiple WAL records, so the current approach with separate running-xacts record is better. Agreed, more modular. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Nov 15, 2009, at 12:09 PM, Greg Stark wrote: 1) Error messages which mention column names are supposed to quote the column name to set it apart from the error string. This also guarantees that weird column names are referenced correctly as foo bar or $foo so the reference in the error string is unambiguous and can be pasted into queries. This won't work for $foo which would have to be embedded in the error text without quotes. What? You can't have a column named $foo without the quotes. 2) What would the default names for columns be if you did something like create function f(foo) as 'select $foo' It would be f (without the quotes), just like now: try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql; CREATE FUNCTION try=# select f(1); f --- 1 (1 row) If I then use this in another function create function g(foo) as 'select $foo+$foo from f()' I have to quote the column? No, that's a syntax error. It would be `SELECT f + $foo from f();` 3) If I have a report generator which takes a list of columns to include in the report, or an ORM which tries to generate queries the usual way to write such things is to just routinely quote every identifier. This is less error-prone and simpler to code than trying to identify which identifiers need quoting and which don't. However in if the query is then dropped into a function the ORM or query generator would have to know which columns cannot be quoted based on syntactic information it can't really deduce. You already have to quote everything, because $foo isn't a valid column name. And functions use the function name as the default column name, not a variable name. The same is true of set-returning functions, BTW: try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE sql; CREATE FUNCTION try=# select b(1); b --- 1 1 (2 rows) So there is no leaking out. The variables are scoped within the function. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Nov 15, 2009, at 11:35 AM, Greg Stark wrote: I don't think SQL is the height of language design either. But trying to turn it into another language piece by piece is not gong to make it any nicer. I don't know of anyone suggesting such a thing. A sigil here doesn't accomplish anything. The identifiers in question are *just* like other identifiers. They can be used in expressions just like other columns, they have various types, they have the same syntax as other columns, the sigil doesn't mean anything. So what is the $ for in $1, $2, etc.? I think what may be making this tempting is that they look vaguely like ODBC/JDBC/DBI placeholders like :foo. However they're very very different. In those cases the sigil is marking the sigil outside the SQL syntax. They will be replaced textually without parsing the SQL at all. It's actually very confusing having $foo indicate something within SQL since it makes it look like it's some external thing from another layer like the placeholders. It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major database vendors all use some sort of character to identify variables within functions. It's proven, avoids conflicts (you can't have an identifier named $foo, as Andrew just pointed out), and just generally makes maintenance easier. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, race condition between recovery snapshot and commit
Simon Riggs wrote: On Sun, 2009-11-15 at 21:37 +0200, Heikki Linnakangas wrote: Am I missing anything? Will review. Thanks! Please use the head of git branch, I already found one major oversight in what I posted that's fixed there... I should go to bed already. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 21:56 +0200, Heikki Linnakangas wrote: If you actually want to help, can you please focus on fixing the must-fix bugs we know about? We can then discuss which of the remaining known issues we're willing to live with. I intend to work on all of the issues, so not sure what you mean by help. When the role of author and reviewer becomes blurred it gets harder to work together, for certain. Since we are short of time and some issues will take time, the priority order of further work is important. Right now, I don't know which you consider to be the must-fix issues, hence the thread. I also don't know what you consider to be appropriate fixes to them, so unfortunately there will be more talking until it is time for action. I prefer coding, just like you. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] commitfest patch move unavailable
Robert Haas wrote: (Maybe I should automatically create a Miscellaneous topic when each new CF is added?) I'm surprised you're populating each one from scratch every time, that seems like duplicated effort begging to be automated. Couldn't you just come up with a stock list of the most common topics and fill then all in when the CF is created? -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. That's all? You sure? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
On 11/15/09 12:58 PM, Simon Riggs wrote: On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. That's all? You sure? Just speaking from a user/tester perspective, a HS with known caveats and failure conditions would be acceptable in Alpha3. It would be better than waiting for Alpha4. Not only would getting some form of HS into Alpha3 get people testing HS and finding failure conditions we didn't think of eariler, it will also inspire people to compile and test the Alphas, period. Right now the whole Alpha testing program seems to have only attracted The Usual Contributors, despite efforts to publicize it. So I'm in favor of committing part of the HS code even if there are known failure conditions, as long as those conditions are well-defined. (and applause to Simon and Heikki for continuing to put noses to grinstones on this, and Robert for keeping an eye on the schedule) --Josh Berkus -- 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] Summary and Plan for Hot Standby
On Sun, 2009-11-15 at 23:14 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. That's all? You sure? For starters. If you think you'll get that done quickly, please take a look at the bucket of ice-water issue next. Sure, I'll see if I can reach for the bucket. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Writeable CTE patch
I wrote:
Attached is the latest version of this patch.
Here's that same patch in context diff format. Sorry for the noise.
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1499,1505 SELECT 3, 'three';
synopsis
SELECT replaceableselect_list/replaceable FROM
replaceabletable_expression/replaceable
/synopsis
!and can appear anywhere a literalSELECT/ can. For example, you can
use it as part of a literalUNION/, or attach a
replaceablesort_specification/replaceable (literalORDER BY/,
literalLIMIT/, and/or literalOFFSET/) to it. literalVALUES/
--- 1499,1505
synopsis
SELECT replaceableselect_list/replaceable FROM
replaceabletable_expression/replaceable
/synopsis
!and can appear anywhere a literalSELECT/literal can. For example, you
can
use it as part of a literalUNION/, or attach a
replaceablesort_specification/replaceable (literalORDER BY/,
literalLIMIT/, and/or literalOFFSET/) to it. literalVALUES/
***
*** 1529,1538 SELECT replaceableselect_list/replaceable FROM
replaceabletable_expression
/indexterm
para
!literalWITH/ provides a way to write subqueries for use in a larger
!literalSELECT/ query. The subqueries can be thought of as defining
!temporary tables that exist just for this query. One use of this feature
!is to break down complicated queries into simpler parts. An example is:
programlisting
WITH regional_sales AS (
--- 1529,1539
/indexterm
para
!literalWITH/ provides a way to write subqueries for use in a
!larger query. The subqueries can be thought of as defining
!temporary tables that exist just for this query. One use of this
!feature is to break down complicated queries into simpler parts.
!An example is:
programlisting
WITH regional_sales AS (
***
*** 1560,1565 GROUP BY region, product;
--- 1561,1590
/para
para
+ A literalWITH/literal clause can also have an
+ literalINSERT/literal, literalUPDATE/literal or
+ literalDELETE/literal (each optionally with a
+ literalRETURNING/literal clause) statement in it. The example below
+ moves rows from the main table, foo_log into a partition,
+ foo_log_200910.
+
+ programlisting
+ WITH rows AS (
+ DELETE FROM ONLY foo_log
+ WHERE
+foo_date gt;= '2009-10-01' AND
+foo_date lt; '2009-11-01'
+RETURNING *
+ ), t AS (
+INSERT INTO foo_log_200910
+SELECT * FROM rows
+ )
+ VALUES(true);
+ /programlisting
+
+ /para
+
+ para
The optional literalRECURSIVE/ modifier changes literalWITH/
from a mere syntactic convenience into a feature that accomplishes
things not otherwise possible in standard SQL. Using
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***
*** 58,64 SELECT [ ALL | DISTINCT [ ON ( replaceable
class=parameterexpression/replac
phraseand replaceable class=parameterwith_query/replaceable
is:/phrase
! replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable )
TABLE { [ ONLY ] replaceable class=parametertable_name/replaceable [ *
] | replaceable class=parameterwith_query_name/replaceable }
/synopsis
--- 58,64
phraseand replaceable class=parameterwith_query/replaceable
is:/phrase
! replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable | (replaceable
class=parameterinsert/replaceable | replaceable
class=parameterupdate/replaceable | replaceable
class=parameterdelete/replaceable [ RETURNING...]))
TABLE { [ ONLY ] replaceable class=parametertable_name/replaceable [ *
] | replaceable class=parameterwith_query_name/replaceable }
/synopsis
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***
*** 2160,2166 CopyFrom(CopyState cstate)
heap_insert(cstate-rel, tuple, mycid, hi_options,
bistate);
if (resultRelInfo-ri_NumIndices 0)
! recheckIndexes = ExecInsertIndexTuples(slot,
(tuple-t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
--- 2160,2167
heap_insert(cstate-rel, tuple, mycid, hi_options,
bistate);
if (resultRelInfo-ri_NumIndices 0)
! recheckIndexes =
ExecInsertIndexTuples(resultRelInfo,
!
slot, (tuple-t_self),
[HACKERS] CommitFest 2009-11 Closed; Initial assignments
With some lazy Sunday slack, the 2009-11 CommitFest is now officially closed. Due to a bumper crop of review volunteers, almost all patches are already assigned an initial reviewer. Here are the notable exceptions: SE-PostgreSQL/Lite: It's hard to find a reviewer willing to take on a patch this large. The work to review here has dropped considerably since the last rev of this, which is good progress. But much like Hot Standby and Streaming Replication, I fear we may be at the point where this patch needs a more dedicated long-term reviewer attached to it, rather than presuming we can grab one from the RRR pool. Listen / Notify rewrite: I feel this one has attached enough review on this list already to qualify as returned with feedback. Once the design for payload and memory allocation settles down, I encourage Joachim to resubmit an updated version during this CF. If that's within the next two weeks, we do have some fresh reviewers who aren't available yet but are lined up for a second round in December if necessary. We can certainly ask one of them to do a deeper dive into the patch at that point. This feature has enough pent up demand for it (and modest complexity) such that I don't expect a problem finding people to review it and eventually get it committed during 8.5, even if that bounces to the next CF. Memory management probes and SLRU/executor probes: With these coming just before the deadline I wasn't able to nail down someone who had the ability to test DTrace code in the first round. I'd welcome a review volunteer who is looking to play with DTrace to take a look at either or both patches. If that doesn't happen, eventually I'll just review them myself. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan and...@dunslane.net writes: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Throw error if ambiguous. We already resolved this in the context of plpgsql. 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] Summary and Plan for Hot Standby
Simon Riggs wrote: On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. That's all? You sure? For starters. If you think you'll get that done quickly, please take a look at the bucket of ice-water issue next. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote: 3. Every distinct notification is delivered. Regarding performance, the slru-queue is not fsync-ed to disk These two statements seem to be in opposition. How do you know a notification will be delivered if the queue is non-recoverable? You misunderstand the requirements. LISTEN notifications are *not* meant to survive a database crash, and never have been. However, so long as both client and server stay up, they must be reliable. If the client has to poll database state because it might have missed a notification, the feature is just a waste of time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - Report the schema along table name in a referential failure error message
Andrew Dunstan and...@dunslane.net writes: George Gensure wrote: This begs a bigger question: what's *really* easy or low barrier to entry for very light contributors like myself? The TODO list at http://wiki.postgresql.org/wiki/Todo doesn't seem to have a huge number or [E] items. Maybe we need a bit of a brainstorm to come up with a few more. The real problem with the entry that George picked up on was that it was misdescribed and mislabeled as easy because whoever put it in ignored the fact that there was not a consensus to do a half-baked fix ... this is a problem with a wiki TODO list :-( The one I just started talking about (using param names in SQL functions) might not be terribly hard, depending on your coding skills, since it would be making use of the new parser hooks feature that Tom has just done the heavy lifting on. It is easy ... as long as you don't move the goalposts by insisting on inventing some nonstandard syntax. I would envision that given create function f (x int) you should be able to refer to the parameter as x or f.x if you need to qualify it. This matches plpgsql practice and won't surprise anybody, and can be implemented with a couple hours' hacking I'd guess. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal: using PQexecParams in psql (using variables as real params)
Hello
I propose to add possibility to use psql variables as real query
parameters. The goal of this proposal is simplification of creating
psql based commands. Current using of psql variables based on
substitution has large area of using, but has some risks. a) there are
possible sql injection, b) we have to have to do some special (not too
much readable quoting) - see Bruce's book, psql chapter.
I checked, so this doesn't need much work. Attachment contains a prototype.
[pa...@nemesis ~]$ echo select upper(:message) | psql -r -v
message=Pavel's cat postgres
upper
─
PAVEL'S CAT
(1 row)
[pa...@nemesis ~]$ psql -v message=Pavel's cat postgres
psql (8.5devel)
Type help for help.
postgres=# \pexec
Separately passing parameters is on.
postgres=# select upper(:message);
upper
─
PAVEL'S CAT
(1 row)
This small feature simplify integration psql to shell environment.
comments, notes??
Regards
Pavel Stehule
*** ./command.c.orig 2009-10-13 23:04:01.0 +0200
--- ./command.c 2009-11-15 21:53:25.418639611 +0100
***
*** 1127,1132
--- 1127,1152
free(pattern);
}
+ /* \pexec -- pass parameters separately */
+ else if (strcmp(cmd, pexec) == 0)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (opt)
+ pset.use_parameters = ParseVariableBool(opt);
+ else
+ pset.use_parameters = !pset.use_parameters;
+ if (!pset.quiet)
+ {
+ if (pset.use_parameters)
+ puts(_(Separately passing parameters is on.));
+ else
+ puts(_(Separately passing parameters is off.));
+ }
+ free(opt);
+ }
+
/* \! -- shell escape */
else if (strcmp(cmd, !) == 0)
{
*** ./common.c.orig 2009-04-11 20:38:54.0 +0200
--- ./common.c 2009-11-15 22:28:25.036648416 +0100
***
*** 852,858
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
--- 852,876
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! if (!pset.use_parameters)
! results = PQexec(pset.db, query);
! else
! {
! /* use PQexecParams function instead */
! results = PQexecParams(pset.db, query,
! pset.nparameters,
! NULL,
! pset.parameters,
! NULL,
! NULL,
! 0);
! if (pset.nparameters)
! {
! pset.nparameters = 0;
! pset.maxparameters = 0;
! free(pset.parameters);
! }
! }
/* these operations are included in the timing result: */
ResetCancelConn();
*** ./psqlscan.l.orig 2009-11-15 21:28:55.0 +0100
--- ./psqlscan.l 2009-11-15 22:06:04.814641928 +0100
***
*** 693,701
if (value)
{
! /* It is a variable, perform substitution */
! push_new_buffer(value);
! /* yy_scan_string already made buffer active */
}
else
{
--- 693,716
if (value)
{
! if (pset.use_parameters)
! {
! char buffer[10];
!
! /* add new parameter */
! if (pset.nparameters == pset.maxparameters)
! {
! pset.maxparameters += 100;
! pset.parameters = malloc(sizeof(char *) * pset.maxparameters);
! }
! pset.parameters[pset.nparameters++] = value;
! sprintf(buffer, $%d, pset.nparameters);
! push_new_buffer(buffer);
! }
! else
! /* It is a variable, perform substitution */
! push_new_buffer(value);
! /* yy_scan_string already made buffer active */
}
else
{
*** ./settings.h.orig 2009-02-26 17:02:38.0 +0100
--- ./settings.h 2009-11-15 21:54:23.321640498 +0100
***
*** 111,116
--- 111,120
const char *prompt2;
const char *prompt3;
PGVerbosity verbosity; /* current error verbosity level */
+ bool use_parameters;
+ int nparameters;
+ intmaxparameters;
+ const char **parameters;
} PsqlSettings;
extern PsqlSettings pset;
*** ./startup.c.orig 2009-04-05 06:19:58.0 +0200
--- ./startup.c 2009-11-15 22:45:02.654643678 +0100
***
*** 122,127
--- 122,131
pset.queryFoutPipe = false;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.use_parameters = false;
+ pset.parameters = NULL;
+ pset.maxparameters = 0;
+ pset.nparameters = 0;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
***
*** 322,327
--- 326,332
{port, required_argument, NULL, 'p'},
{pset, required_argument, NULL, 'P'},
{quiet, no_argument, NULL, 'q'},
+ {pexec, no_argument, NULL, 'r'},
{record-separator, required_argument, NULL, 'R'},
{single-step, no_argument, NULL, 's'},
{single-line, no_argument, NULL, 'S'},
***
*** 346,352
Re: [HACKERS] [COMMITTERS] pgsql: /home/peter/commit-msg
David Fetter da...@fetter.org writes: On Sun, Nov 15, 2009 at 10:09:14AM -0500, Andrew Dunstan wrote: Peter Eisentraut wrote: /home/peter/commit-msg er, what? I'm suspecting a misfired script somewhere. No doubt cvs commit -m ~/commit-msg instead of cvs commit -F ~/commit-msg ... I think I've made that mistake too. For the sake of the archives: it was the previously proposed fix for bug #5075, see http://archives.postgresql.org/pgsql-bugs/2009-11/msg00131.php 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] Summary and Plan for Hot Standby
Josh Berkus j...@agliodbs.com writes: So I'm in favor of committing part of the HS code even if there are known failure conditions, as long as those conditions are well-defined. If we're thinking of committing something that is known broken, I would want to have a clearly defined and trust-inspiring escape strategy. We can always revert the patch later inspires absolutely zero confidence here, because in a patch this large there are always going to be overlaps with other later patches. If it gets to be February and HS is still unshippable, reverting is going to be a tricky and risky affair. I agree with Heikki that it would be better not to commit as long as any clear showstoppers remain unresolved. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - Report the schema along table name in a referential failure error message
2009/11/16 Tom Lane t...@sss.pgh.pa.us: The real problem with the entry that George picked up on was that it was misdescribed and mislabeled as easy because whoever put it in ignored the fact that there was not a consensus to do a half-baked fix ... this is a problem with a wiki TODO list :-( Wouldn't it be more accurate to say that it's a problem with *any* TODO list? I don't see what the wiki has to do with it. Garbage in, garbage out. A poorly described item will always be trouble regardless of what form it is in. However, I'm not sure how productive the [E]asy marker can really be. Items end up on the TODO generally because a) we couldn't settle on a way forward, or b) nobody was keen to do it right away. There just aren't many genuinely easy items in there, easy ones usually get done right away. Cheers, BJ -- 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] Listen / Notify rewrite
On Sun, 2009-11-15 at 16:48 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-11-11 at 22:25 +0100, Joachim Wieland wrote: 3. Every distinct notification is delivered. Regarding performance, the slru-queue is not fsync-ed to disk These two statements seem to be in opposition. How do you know a notification will be delivered if the queue is non-recoverable? You misunderstand the requirements. LISTEN notifications are *not* meant to survive a database crash, and never have been. However, so long as both client and server stay up, they must be reliable. If the client has to poll database state because it might have missed a notification, the feature is just a waste of time. Why would it be so important for messages to be reliable if the database is up, yet its OK to lose messages if it crashes? The application must still allow for the case that messages are lost. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - Report the schema along table name in a referential failure error message
Brendan Jurd dire...@gmail.com writes: However, I'm not sure how productive the [E]asy marker can really be. Items end up on the TODO generally because a) we couldn't settle on a way forward, or b) nobody was keen to do it right away. There just aren't many genuinely easy items in there, easy ones usually get done right away. Yeah, that is a real problem for new would-be contributors --- there simply isn't that much low-hanging fruit waiting for them, unless they focus on areas that no one else has taken much interest in; and even then there are few really small tasks. 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] Listen / Notify rewrite
Simon Riggs si...@2ndquadrant.com writes: On Sun, 2009-11-15 at 16:48 -0500, Tom Lane wrote: You misunderstand the requirements. LISTEN notifications are *not* meant to survive a database crash, and never have been. However, so long as both client and server stay up, they must be reliable. If the client has to poll database state because it might have missed a notification, the feature is just a waste of time. Why would it be so important for messages to be reliable if the database is up, yet its OK to lose messages if it crashes? The application must still allow for the case that messages are lost. No, that's the point. The design center for LISTEN is that you have a client that needs to respond to changes in the DB state. When it first connects it will issue LISTEN and then (order is important) it will examine the current state of the database. After that it can just wait for NOTIFY to tell it that something interesting has happened. If it crashes, or sees a disconnect indicating that the server has crashed, it goes back to the startup point. No problem. But if it can't be sure that it will get a NOTIFY every time something happens to the DB state, then it has to do active polling of the state instead, and the NOTIFY feature is really worthless to it. This is an entirely useful and reliable feature within these parameters --- the first application I ever wrote using PG relied on NOTIFY to work this way. (In fact it wouldn't be overstating the case to say that I wouldn't be a PG hacker today if it weren't for LISTEN/NOTIFY.) 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] Aggregate ORDER BY patch
Andrew == Andrew Gierth and...@tao11.riddles.org.uk writes: Andrew Performance. Andrew tuplesort_getdatum etc. seems to be substantially faster than Andrew tuplesort_gettupleslot especially for the case where you're Andrew sorting a pass-by-value datum such as an integer (since the Andrew datum is then stored only in the sort tuple header and Andrew doesn't require a separate space allocation for Andrew itself). Using a slot in all cases would have slowed down Andrew some common cases like count(distinct id) by a measurable Andrew amount. Andrew Cases like array_agg(x order by x) benefit from the faster Andrew code path too. Andrew The memory management between the two cases is sufficiently Andrew different that combining them into one function while still Andrew maintaining the slot vs. datum distinction would be ugly and Andrew probably error-prone. The relatively minor duplication of Andrew logic seemed much clearer to me. Just to quantify this, using a production-quality build (optimized and without assertions), it turns out that the fast code path (process_ordered_aggregate_single) is faster by 300% for pass-by-value types, and by approximately 20% for short values of pass-by-reference types, as compared to disabling that code path and forcing even the one-arg case to use the slot interface. So using the slot interface for everything would have constituted a 300% slowdown over the older code for count(distinct id), obviously undesirable. As it stands, I can't detect any performance regression over the previous code. This means that agg(x order by y) is rather noticably slower than agg(x order by x), but this is pretty much unavoidable given how the sorting code works. Future performance enhancements (which I have no particular plans to tackle) would involve having the planner consult the desired aggregate orderings and estimating the cost of sorting as opposed to the cost of producing a plan with the input already ordered. Also combining the sort step for aggregates that share a single ordering. -- Andrew (irc:RhodiumToad) -- 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] Summary and Plan for Hot Standby
On Nov 15, 2009, at 2:17 PM, Tom Lane wrote: So I'm in favor of committing part of the HS code even if there are known failure conditions, as long as those conditions are well-defined. If we're thinking of committing something that is known broken, I would want to have a clearly defined and trust-inspiring escape strategy. We can always revert the patch later inspires absolutely zero confidence here, because in a patch this large there are always going to be overlaps with other later patches. If it gets to be February and HS is still unshippable, reverting is going to be a tricky and risky affair. I agree with Heikki that it would be better not to commit as long as any clear showstoppers remain unresolved. If ever there were an argument for topic branches, *this is it*. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
David E. Wheeler da...@kineticode.com writes: On Nov 15, 2009, at 2:17 PM, Tom Lane wrote: I agree with Heikki that it would be better not to commit as long as any clear showstoppers remain unresolved. If ever there were an argument for topic branches, *this is it*. How so? They've got a perfectly good topic branch, ie, the external git repository they're already working in. If the branch were within core CVS it would accomplish exactly nothing more as far as easing the eventual merge. 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] Summary and Plan for Hot Standby
On Nov 15, 2009, at 4:19 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, 2009-11-15 at 23:14 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Sun, 2009-11-15 at 22:45 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Right now, I don't know which you consider to be the must-fix issues, hence the thread. Ok, could you tackle the b-tree vacuum bug, where we neglect to pin the index pages after the last b-tree vacuum record? Thanks. That's all? You sure? For starters. If you think you'll get that done quickly, please take a look at the bucket of ice-water issue next. Sure, I'll see if I can reach for the bucket. Me and my big fat mouth... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby, overflowed snapshots, testing
On 11/15/09 2:25 AM PST, Simon Riggs si...@2ndquadrant.com wrote: On Sat, 2009-11-14 at 08:43 -0800, Robert Hodges wrote: I can help set up automated basic tests for hot standby using 1+1 setups on Amazon. I¹m already working on tests for warm standby for our commercial Tungsten implementation and need to solve the problem of creating tests that adapt flexibly across different replication mechanisms. I didn't leap immediately to say yes for a couple of reasons. I'm easy on this. We are going to find some hot standby problems no matter what from our own testing. At least I hope so. It does sound to me as if there is a class of errors that would be easiest to find by putting up a long running test that throws a lot of different queries at the server over time. We have such tests already written in our Bristlecone tools. Cheers, Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
On Sun, Nov 15, 2009 at 12:50:14AM +, Roger Leigh wrote:
On Sat, Nov 14, 2009 at 01:31:29PM -0500, Tom Lane wrote:
Roger Leigh rle...@codelibre.net writes:
The side effect from this change is that some of the testsuite
expected data will need updating due to the extra pad spaces
No, we are *not* doing that. Somebody made a change to the print.c
logic last year that started adding harmless white space to the
last column, and it was a complete disaster for tracking whether
anything important had changed in regression test output. Please
undo that part of your patch.
No problem, done as requested. I've attached an updated patch that
takes care to exactly match the trailing whitespace the existing
psql outputs. This fixes most of the changes between observed and
expected test results.
Attached is an updated patch with a couple of tweaks to ensure output
is formatted and spaced correctly when border=0, which was off in the
last patch.
Regards,
Roger
--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`-GPG Public Key: 0x25BFB848 Please GPG sign your mail.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7f03802..4b3fe71 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1765,18 +1765,40 @@ lo_import 152801
listitem
para
Sets the border line drawing style to one
- of literalascii/literal or literalunicode/literal.
- Unique abbreviations are allowed. (That would mean one
- letter is enough.)
+ of literalascii/literal, literalascii-old/literal
+ or literalunicode/literal. Unique abbreviations are
+ allowed. (That would mean one letter is enough.)
/para
para
- quoteASCII/quote uses plain acronymASCII/acronym characters.
+ quoteASCII/quote uses plain acronymASCII/acronym
+ characters. Newlines in data are shown using
+ a literal+/literal symbol in the right-hand margin,
+ while wrapped data uses a literal./literal symbol in the
+ right-hand margin of a wrapped line, and in the left-hand
+ margin of the following continuation line.
/para
para
+ quoteASCII-old/quote uses plain acronymASCII/acronym
+ characters, using the formatting style used
+ for productnamePostgreSQL/productname 8.4 and earlier.
+ Newlines in data are shown using a literal:/literal
+ symbol in place of the left-hand column separator, while
+ wrapped data uses a literal;/literal symbol. Newlines
+ in column headings are indicated by a literal+/literal
+ symbol in the left-hand margin of additional lines.
+ /para
+
+ para
quoteUnicode/quote uses Unicode box-drawing characters.
- /para
+ Newlines in data are shown using a carriage return symbol
+ (literal#8629;/literal) in the right-hand margin.
+ Wrapped data uses an ellipsis symbol
+ (literal#8230;/literal) in the right-hand margin of a
+ wrapped line, and in the left-hand margin of the following
+ continuation line.
+ /para
para
When the selected output format is one that draws lines or boxes
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 190a8d3..544a677 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1795,11 +1795,13 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
;
else if (pg_strncasecmp(ascii, value, vallen) == 0)
popt-topt.line_style = pg_asciiformat;
+ else if (pg_strncasecmp(ascii-old, value, vallen) == 0)
+ popt-topt.line_style = pg_asciiformat_old;
else if (pg_strncasecmp(unicode, value, vallen) == 0)
popt-topt.line_style = pg_utf8format;
else
{
- psql_error(\\pset: allowed line styles are ascii, unicode\n);
+ psql_error(\\pset: allowed line styles are ascii, ascii-old, unicode\n);
return false;
}
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index 026e043..5d1c8d4 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -45,9 +45,9 @@ static char *grouping;
static char *thousands_sep;
/* Line style control structures */
-const printTextFormat pg_asciiformat =
+const printTextFormat pg_asciiformat_old =
{
- ascii,
+ ascii-old,
{
{ -, +, +, + },
{ -, +, +, + },
@@ -56,7 +56,36 @@ const printTextFormat pg_asciiformat =
},
:,
;,
-
+ ,
+ +,
+ ,
+ ,
+ ,
+ ,
+ ,
+ false
+};
+
+/* Line style control structures */
+const printTextFormat pg_asciiformat =
+{
+ ascii,
+ {
+ { -, +, +, + },
+ { -, +, +, + },
+ { -, +, +, + },
+ { , |, |, | }
+ },
+ |,
+ |,
+ |,
+ ,
+ +,
+ ,
+ +,
+ .,
+ .,
+ true
};
const printTextFormat pg_utf8format =
@@ -72,12 +101,23 @@ const
Re: [HACKERS] Aggregate ORDER BY patch
On Sun, Nov 15, 2009 at 11:23 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Future performance enhancements (which I have no particular plans to tackle) would involve having the planner consult the desired aggregate orderings and estimating the cost of sorting as opposed to the cost of producing a plan with the input already ordered. Also combining the sort step for aggregates that share a single ordering. Those both seem like pretty important things. Do you have an idea how to go about doing them? -- 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] Patch committers
Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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 - Report the schema along table name in a referential failure error message
Tom Lane wrote: Yeah, that is a real problem for new would-be contributors --- there simply isn't that much low-hanging fruit waiting for them, unless they focus on areas that no one else has taken much interest in; and even then there are few really small tasks. Then I think we need to start being more creative about ways to ease the path for people who want to get people involved. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
