Re: [HACKERS] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
2012/10/21 Albert Cervera i Areny alb...@nan-tic.com: A Dimecres, 17 d'octubre de 2012 19:13:47, Merlin Moncure va escriure: On Wed, Oct 17, 2012 at 9:29 AM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 17 October 2012 14:53, Merlin Moncure mmonc...@gmail.com wrote: Is that defined in the standard? RETURNING isn't even defined in the standard. Right: Point being, assumptions based on implementation ordering are generally to be avoided unless they are explicitly defined in the standard or elsewhere. I don't see how one could use RETURNING if result is not ensured to be in the same order as the tuples supplied. What's the use of RETURNING supplying data in random order? you don't need a ORDER, you need data - and if you need a order, then you can use CTE and ORDER BY clause. Proposed feature can be too limited in future - when some better partitioning can be used or when paralel query processing will be supported Pavel -- Albert Cervera i Areny http://www.NaN-tic.com Tel: +34 93 553 18 03 http://twitter.com/albertnan http://www.nan-tic.com/blog -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gistchoose vs. bloat
On Thu, 2012-10-18 at 15:09 -0300, Alvaro Herrera wrote: Jeff, do you think we need more review of this patch? In the patch, it refers to rd_options without checking for NULL first, which needs to be fixed. There's actually still one place where it says id rather than is. Just a nitpick. Regarding my point 4 from the previous email, I mildly disagree with the style, but I don't see a correctness problem there. If the first two items are fixed, then the patch is fine with me. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SP-GiST for ranges based on 2d-mapping and quad-tree
On Tue, 2012-09-04 at 17:45 +0400, Alexander Korotkov wrote: On Mon, Aug 20, 2012 at 12:25 AM, Jeff Davis pg...@j-davis.com wrote: I am taking a look at this patch now. A few quick comments: * It looks like bounds_adjacent modifies it's by-reference arguments, which is a little worrying to me. The lower/upper labels are flipped back, but the inclusivities are not. Maybe just pass by value instead? * Bounds_adjacent is sensitive to the argument order. Can't it just take bound1 and bound2? Fixed. Patch is attached. It looks like this is basically the same diff as v0.1. Did something get mixed up? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deprecations in authentication
On Thu, Oct 18, 2012 at 5:59 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 18, 2012 at 7:20 AM, Magnus Hagander mag...@hagander.net wrote: Since Simon stirred up a hornets nest suggesting deprecation of a number of features, I figured I'd take it one step further and suggest removal of some previously deprecated features :) In particular, we made a couple of changes over sveral releases back in the authentication config, that we should perhaps consider finishing by removing the old stuff now? 1. krb5 authentication. We've had gssapi since 8.3 (which means in all supported versions). krb5 has been deprecated, also since 8.3. Time to remove it? That seems like a sufficiently long deprecation window, but is gssapi a full substitute for krb5? I don't really have a strong opinion on this, not being a user myself. I'm pretty sure that it is. Stephen, you usually have comments about the Kerberos stuff - want to comment on this one? :) -- 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] Successor of MD5 authentication, let's use SCRAM
On Mon, Oct 15, 2012 at 1:21 PM, Will Crawford billcrawford1...@gmail.com wrote: On 14 October 2012 22:17, Daniel Farina dan...@heroku.com wrote: The problem there is that it's a pain to get signed certs in, say, a test environment, so don't check certs will make its way into the default configuration, and now you have all pain and no gain. This is precisely the issue that Debian deals with in providing the default Snake Oil certificate; software development teams - especially small shops with one or two developers - don't want to spend time learning about CAs and creating their own, etc, and often their managers would see this as wasted time for setting up development environments and staging systems. Not saying they're right, of course; but it can be an uphill struggle, and as long as you get a real certificate for your production environment, it's hard to see what harm this (providing the snake oil certificate) actually causes. I don't see a problem at all with providing the snakeoil cert. In fact, it's quite useful. I see a problem with enabling it by default. Because it makes people think they are more secure than they are. In a browser, they will get a big fat warning every time, so they will know it. There is no such warning in psql. Actually, maybe we should *add* such a warning. We could do it in psql. We can't do it in libpq for everyone, but we can do it in our own tools... Particularly since we do print the SSL information already - we could just add a warning: cert not verified or something like that to the same piece of information. -- 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] [WIP PATCH] for Performance Improvement in Buffer Management
On Saturday, October 20, 2012 11:03 PM Jeff Janes wrote: On Fri, Sep 7, 2012 at 6:14 AM, Amit kapila amit.kap...@huawei.com wrote: On Thursday, September 06, 2012 2:38 PM Amit kapila wrote: On Tuesday, September 04, 2012 6:55 PM Amit kapila wrote: On Tuesday, September 04, 2012 12:42 AM Jeff Janes wrote: On Mon, Sep 3, 2012 at 7:15 AM, Amit kapila amit.kap...@huawei.com wrote: This patch is based on below Todo Item: Consider adding buffers the background writer finds reusable to the free list The results for the updated code is attached with this mail. The scenario is same as in original mail. 1. Load all the files in to OS buffers (using pg_prewarm with 'read' operation) of all tables and indexes. 2. Try to load all buffers with pgbench_accounts table and pgbench_accounts_pkey pages (using pg_prewarm with 'buffers' operation). 3. Run the pgbench with select only for 20 minutes. Platform details: Operating System: Suse-Linux 10.2 x86_64 Hardware : 4 core (Intel(R) Xeon(R) CPU L5408 @ 2.13GHz) RAM : 24GB Server Configuration: shared_buffers = 5GB (1/4 th of RAM size) Total data size = 16GB Pgbench configuration: transaction type: SELECT only scaling factor: 1200 query mode: simple number of clients: varying from 8 to 64 number of threads: varying from 8 to 64 duration: 1200 s I shall take further readings for following configurations and post the same: 1. The intention for taking with below configuration is that, with the defined testcase, there will be some cases where I/O can happen. So I wanted to check the impact of it. Shared_buffers - 7 GB number of clients: varying from 8 to 64 number of threads: varying from 8 to 64 transaction type: SELECT only The data for shared_buffers = 7GB is attached with this mail. I have also attached scripts used to take this data. Is this result reproducible? Did you monitor IO (with something like vmstat) to make sure there was no IO going on during the runs? Yes, I have reproduced it 2 times. However I shall reproduce once more and use vmstat as well. I have not observed with vmstat but it is observable in the data. When I have kept shared buffers = 5G, the tps is more and when I increased it to 7G, the tps is reduced which shows there is some I/O started happening. When I increased to 10G, the tps reduced drastically which shows there is lot of I/O. Tommorow I will post 10G shared buffers data as well. Run the modes in reciprocating order? Sorry, I didn't understood this, What do you mean by modes in reciprocating order? If you have 7GB of shared_buffers and 16GB of database, that comes out to 23GB of data to be held in 24GB of RAM. In my experience it is hard to get that much data cached by simple prewarm. the newer data will drive out the older data even if technically there is room. So then when you start running the benchmark, you still have to read in some of the data which dramatically slows down the benchmark. Yes with 7G, the chances of doing I/O is high but with 5G, chances are less which is observed in the data as well(TPS in 7G data is less than in 5G). Please see the results of 5G shared buffers in mail below: http://archives.postgresql.org/pgsql-hackers/2012-09/msg00318.php In 7G case, you can see in the data that without this patch, the tps with original code is quite less as compare to 5G data. I am sorry, there is one typo error in 7G shared buffers data, it is mentioned wrongly 5G in heading of data. I haven't been able to detect any reliable difference in performance with this patch. I've been testing with 150 scale factor with 4GB of ram and 4 cores, over a variety of shared_buffers and concurrencies. I think the main reason for this is that when shared buffers are less, then there is no performance gain, even the same is observed by me when I ran this test with shared buffers=2G, there is no performance gain. Please see the results of shared buffers=2G in below mail: http://archives.postgresql.org/pgsql-hackers/2012-09/msg00422.php The reason I can think of is because when shared buffers are less then clock sweep runs very fast and there is no bottleneck. Only when shared buffers increase above some threshhold, it spends reasonable time in clock sweep. I shall once run with the same configuration as mentioned by you, but I think it will not give any performance gain due to reason mentioned above. Is it feasible for you to run with higher shared buffers and also somewhat large data and RAM. Basically I want to know if you can mimic the situation mentioned by tests I have posted. In anycase I shall run the tests once again and post the data. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] enhanced error fields
Hello 2012/10/20 Peter Geoghegan pe...@2ndquadrant.com: I think that we're both going to be busy next week, since we're both attending pgconf.eu. For that reason, I would like to spend some time tomorrow to get something in shape, that I can mark ready for committer. I'd like to get this patch committed during this commitfest. You are welcome to do this work instead. I want to avoid a redundant effort. I invite a materialization of your ideas :) - and I have to work on preparing presentation for pgconf.eu :( Regards Pavel Let me know if you think that that's a good idea. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
At 2012-10-17 09:56:22 -0400, t...@sss.pgh.pa.us wrote: Clarify that in the documentation, and also write a test case that will prevent us from breaking the rule in the future. I don't believe this is a good idea in the slightest. Yeah, the current implementation happens to act like that, but there is no reason that we should make it guaranteed behavior. I always thought it *was* guaranteed, and I've encountered code written by other people who were obviously under the same impression: take some strings (e.g. flag names), use insert … returning id, map the ids back to the names, and use the values in further inserts into other tables (flag_id foreign key references flags). I know one could say returning id, name, but there's certainly code out there that doesn't do this. I personally think the return order should be guaranteed; and if not, then the documentation urgently needs some prominent warnings to tell people that they should not assume this (for any variant of RETURNING). -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM
On Sun, Oct 21, 2012 at 09:55:50AM +0200, Magnus Hagander wrote: I don't see a problem at all with providing the snakeoil cert. In fact, it's quite useful. I see a problem with enabling it by default. Because it makes people think they are more secure than they are. So, what you're suggesting is that any use of ssl to a remote machine without the sslrootcert option should generate a warning. Something along the lines of remote server not verified? For completeness it should also show this for any non-SSL connection. libpq should export a serververified flag which would be false always unless the connection is SSL and the CA is verified . In a browser, they will get a big fat warning every time, so they will know it. There is no such warning in psql. Actually, maybe we should *add* such a warning. We could do it in psql. We can't do it in libpq for everyone, but we can do it in our own tools... Particularly since we do print the SSL information already - we could just add a warning: cert not verified or something like that to the same piece of information. It bugs me every time you have to jump through hoops and get red warnings for an unknown CA, whereas no encryption whatsoever is treated as fine while being actually even worse. Transport encryption is a *good thing*, we should be encouraging it wherever possible. If it wern't for the performance issues I'd suggest defaulting to SSL everywhere transparently with ephemeral certs. It would protect against any number of passive attacks. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Abhijit Menon-Sen Sent: Sunday, October 21, 2012 5:45 AM To: Tom Lane Cc: P. Christeas; pgsql-hackers@postgresql.org Subject: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows At 2012-10-17 09:56:22 -0400, t...@sss.pgh.pa.us wrote: Clarify that in the documentation, and also write a test case that will prevent us from breaking the rule in the future. I don't believe this is a good idea in the slightest. Yeah, the current implementation happens to act like that, but there is no reason that we should make it guaranteed behavior. I always thought it *was* guaranteed, and I've encountered code written by other people who were obviously under the same impression: take some strings (e.g. flag names), use insert … returning id, map the ids back to the names, and use the values in further inserts into other tables (flag_id foreign key references flags). I know one could say returning id, name, but there's certainly code out there that doesn't do this. I personally think the return order should be guaranteed; and if not, then the documentation urgently needs some prominent warnings to tell people that they should not assume this (for any variant of RETURNING). -- Abhijit Order is never guaranteed unless an ORDER BY clause is involved in processing the data immediately prior to its use. I could see this being in a Rules that you must always remember listing but to include it in every location where people might be inclined to rely upon ordering is just going to clutter the documentation. That said, I'm not personally opposed to this documentation suggestion. But while the idea is acceptable the actual changes proposed by someone's patch is what needs to be approved and applied. As to the order of RETURNING I do not see an overly compelling reason to enforce such a limitation; and in general implicit guarantees like this are undesirable since there is no way to turn them off. For sorting in particular the action itself can be expensive and not always needed. While we are not talking strictly sorting here (just maintained order) the concept still applies. David J. -- Sent 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] Enforce that INSERT...RETURNING preserves the order of multi rows
I agree that it seems inappropriate to preserve order. That seems an inappropriate imposition, inconsistent with what SQL does elsewhere. If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. I suppose it is troublesome if there is no such natural sequence, but I wouldn't think it too meaningful to expect order without some visible source of order.
[HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote: At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. No, but you can wrap the INSERT .. RETURNING in a CTE and order that. 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] No, pg_size_pretty(numeric) was not such a hot idea
Robert Haas wrote: You know, if we implemented what Tom proposed here: http://archives.postgresql.org/pgsql-hackers/2012-08/msg01055.php ...then we probably get away with removing pg_size_pretty(bigint) and then this would Just Work. pg_size_pretty(numeric) is doubtless a little slower than pg_size_pretty(bigint), but I think in practice nobody's going to care. The worst case I was able to generate in some testing on an older (over five year old) desktop machine, was 4000ns for the numeric form versus 500ns for the bigint form. So one way of looking at it is that it can be up to eight times slower. The other way of looking at it is that it can take up to 3500ns extra to generate a string intended for human consumption -- this is not a format you generate for maching parsing. I rarely run a query that generates more than a few thousand of these values; to it would be rare for it to cost me more than about 15ms on a query run which was intended for visual review. The difference is probably going to be much smaller on most machines purchased for database server usage within, say, the last three years. I don't know about anyone else, but I could live with that. -Kevin -- Sent 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] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote: On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote: At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. No, but you can wrap the INSERT .. RETURNING in a CTE and order that. Personally I find that a not very practical suggestion. It means you need the ability to sort the data equivalently on the clientside which isn't always easy if you consider platform/locale and whatever differences. Suggesting nextval() doesn't strike me as very practical either because it means that you either need a separate roundtrip to the server to get a bunch of new ids which you then can assign to the to-be-inserted rows or you need the ability to match the returned rows to the inserted rows somehow. Thats not always easy. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday 21 October 2012, Abhijit Menon-Sen wrote: At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. Exactly. And IMHO it should never have. The real trouble is when you insert some arbitrary values, which have no implicit order or primary key /before/ the insert will assign them one. Then, you need to map them to the SERIAL they got. Or else, you can't use the multi-row INSERT and must just do many INSERTs. -- Say NO to spam and viruses. Stop using Microsoft Windows! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM
Magnus Hagander mag...@hagander.net writes: I don't see a problem at all with providing the snakeoil cert. In fact, it's quite useful. I see a problem with enabling it by default. Because it makes people think they are more secure than they are. I am far from an SSL expert, but I had the idea that the only problem with a self-signed cert is that the client can't trace it to a trusted cert --- so if the user took the further step of copying the cert to the client machines' ~/.postgresql/root.crt files, wouldn't things be just fine? In a browser, they will get a big fat warning every time, so they will know it. There is no such warning in psql. Actually, maybe we should *add* such a warning. We could do it in psql. We can't do it in libpq for everyone, but we can do it in our own tools... Particularly since we do print the SSL information already - we could just add a warning: cert not verified or something like that to the same piece of information. No objection to that. I do have an objection to trying to force people to use SSL, which is how I read some of the other proposals in this thread --- but if they are already choosing to use SSL, and it's not as secure as it could be, some sort of notice seems reasonable. What happens in the other direction, ie if a client presents a self-signed cert that the server can't verify? 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] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 10/21/2012 12:36 PM, Andres Freund wrote: On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote: On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote: At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. No, but you can wrap the INSERT .. RETURNING in a CTE and order that. Personally I find that a not very practical suggestion. It means you need the ability to sort the data equivalently on the clientside which isn't always easy if you consider platform/locale and whatever differences. Er, what? with orig_inserts as ( insert into table_1 ... returning * ), ordered_inserts as ( select * from orig_inserts order by ... ) insert into table_2 select * from ordered_inserts ...; why does the client have to be involved, exactly? 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] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
Andrew Dunstan and...@dunslane.net writes: Er, what? with orig_inserts as ( insert into table_1 ... returning * ), ordered_inserts as ( select * from orig_inserts order by ... ) insert into table_2 select * from ordered_inserts ...; I'm not exactly following what that proves? It seems like this is still making a not-guaranteed assumption, which is that the outer INSERT isn't going to choose to rearrange the order of the rows coming from the CTE. Strictly speaking, even SELECT * FROM ordered_inserts isn't promising anything about row order. 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] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 10/21/2012 01:39 PM, Tom Lane wrote: I'm not exactly following what that proves? It seems like this is still making a not-guaranteed assumption, which is that the outer INSERT isn't going to choose to rearrange the order of the rows coming from the CTE. Strictly speaking, even SELECT * FROM ordered_inserts isn't promising anything about row order. Hmm. If we do INSERT INTO foo SELECT ... ORDER BY is that not guaranteed to insert in the desired order? We used to suggest that in the old CLUSTER docs. (I realize that's not what I suggested, but it seems relevant nevertheless.) 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] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote:
On 10/21/2012 12:36 PM, Andres Freund wrote:
On Sunday, October 21, 2012 06:30:14 PM Andrew Dunstan wrote:
On 10/21/2012 12:20 PM, Abhijit Menon-Sen wrote:
At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote:
If there is a natural sequence (e.g. - a value assigned by nextval()),
that offers a natural place to apply the usual order-imposing ORDER BY
that we are expected to use elsewhere.
Note: INSERT … RETURNING doesn't accept an ORDER BY clause.
No, but you can wrap the INSERT .. RETURNING in a CTE and order that.
Personally I find that a not very practical suggestion. It means you need
the ability to sort the data equivalently on the clientside which isn't
always easy if you consider platform/locale and whatever differences.
Er, what?
with orig_inserts as
(
insert into table_1
...
returning *
),
ordered_inserts as
(
select * from orig_inserts
order by ...
)
insert into table_2
select * from ordered_inserts ...;
I am not sure I get the point of this.
why does the client have to be involved, exactly?
Suppose you have something like
CREATE TABLE positionlog(
id serial primary key,
timestamp timestamptz DEFAULT NOW(),
position geometry
);
And you want to insert multiple values in one roundtrip *and* know their ids
in your application.
INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;
If you want to correlate re returned ids with data in your application without
relying on the ordering of INSERT ... VALUES... RETURNING you would need to
sort a postgis type in the same way the server does it.
Am I missing something here?
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
Andrew Dunstan and...@dunslane.net writes: Hmm. If we do INSERT INTO foo SELECT ... ORDER BY is that not guaranteed to insert in the desired order? Well, what do you mean by insert in the desired order? Not that the rows are guaranteed to wind up physically stored in that order, I hope --- heap_insert has always felt free to use available free space opportunistically. I think it's reasonable to guarantee that default expressions with side effects (serial nextval()s for instance) are applied to the rows in the order they come out of the SELECT ... ORDER BY, because otherwise the user would have no way to control that at all. But beyond that particular interaction, a multi-row INSERT is a bulk operation, and SQL has always viewed the results of bulk operations as unordered sets. The other issue, which is probably more relevant to the original question, is what is the ordering of the rows produced by RETURNING. Let's try a thought experiment here. Currently, RETURNING clauses are implemented by computing the RETURNING list on-the-fly as each row is processed by the Insert, Update, or Delete plan node. But for bulk operations that were touching most or all of a table, it's conceivable that it'd make more sense to produce the RETURNING output by rescanning the table after-the-fact, looking for rows with the correct XID/CID for the operation. In that case the output would come out in stored ctid order, not the order the rows were processed in. Is that fundamentally an illegitimate optimization, and if so why? 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] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday 21 October 2012, Andres Freund wrote:
On Sunday, October 21, 2012 07:24:52 PM Andrew Dunstan wrote:
why does the client have to be involved, exactly?
Suppose you have something like
CREATE TABLE positionlog(
...
And you want to insert multiple values in one roundtrip *and* know their
ids in your application.
INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;
If you want to correlate re returned ids with data in your application
without relying on the ordering of INSERT ... VALUES... RETURNING you
would need to sort a postgis type in the same way the server does it.
Am I missing something here?
That's close enough to my case: you would have to guess from (timestamp,
position) the order they have with respect to your [(timestamp, pos),...]
input array. That's not always trivial to do client-side (what about duplicate
pairs? ), let alone the CPU needed to sort and match again.
--
Say NO to spam and viruses. Stop using Microsoft Windows!
--
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: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
At 2012-10-21 14:27:39 -0400, t...@sss.pgh.pa.us wrote: Is that fundamentally an illegitimate optimization, and if so why? I wouldn't say it's illegitimate. It's a bit less convenient for the application programmer, and will surprise some people (even some who know better than to expect SELECT to produce a particular row order). That's all. -- Abhijit -- 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: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 10/21/2012 01:40 PM, Andres Freund wrote:
Suppose you have something like
CREATE TABLE positionlog(
id serial primary key,
timestamp timestamptz DEFAULT NOW(),
position geometry
);
And you want to insert multiple values in one roundtrip *and* know their ids
in your application.
INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;
If you want to correlate re returned ids with data in your application without
relying on the ordering of INSERT ... VALUES... RETURNING you would need to
sort a postgis type in the same way the server does it.
I see. Sorry, I should not have joined the thread late in the piece
while I'm multitasking.
I guess in such a case I'd be inclined to precompute the id values and
then supply them in the values clause. That means two round trips rather
than one.
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] [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday, October 21, 2012 08:45:31 PM Andrew Dunstan wrote:
On 10/21/2012 01:40 PM, Andres Freund wrote:
Suppose you have something like
CREATE TABLE positionlog(
id serial primary key,
timestamp timestamptz DEFAULT NOW(),
position geometry
);
And you want to insert multiple values in one roundtrip *and* know their
ids in your application.
INSERT INTO positionlog(position)
VALUES
('POINT(..., ...)'),
('POINT(..., ...)')
RETURNING id, timestamp, position
;
If you want to correlate re returned ids with data in your application
without relying on the ordering of INSERT ... VALUES... RETURNING you
would need to sort a postgis type in the same way the server does it.
I see. Sorry, I should not have joined the thread late in the piece
while I'm multitasking.
I guess in such a case I'd be inclined to precompute the id values and
then supply them in the values clause. That means two round trips rather
than one.
Which will fail should we get upsert one day...
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On 10/21/2012 02:47 PM, Andres Freund wrote: On Sunday, October 21, 2012 08:45:31 PM Andrew Dunstan wrote: I guess in such a case I'd be inclined to precompute the id values and then supply them in the values clause. That means two round trips rather than one. Which will fail should we get upsert one day... Sufficient unto the day is the evil thereof. It seems premature to worry about it now. 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] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
Andrew Dunstan and...@dunslane.net writes: Sufficient unto the day is the evil thereof. It seems premature to worry about it now. Um, well, this whole thread is about how many potential optimizations we're willing to toss aside to guarantee a particular behavior that the current implementation has. So I think it's all about worrying about the future. One issue that just came to mind is what effect such a promise would have on attempts to multi-thread the backend. I'm on record as being dubious about the pain-to-reward ratio of any such attempt. But if we ever do try it, the more constraints we've put on the order of row processing, the less potential benefit there will be. 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] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sun, Oct 21, 2012 at 6:20 PM, Abhijit Menon-Sen a...@2ndquadrant.comwrote: At 2012-10-21 11:49:26 -0400, cbbro...@gmail.com wrote: If there is a natural sequence (e.g. - a value assigned by nextval()), that offers a natural place to apply the usual order-imposing ORDER BY that we are expected to use elsewhere. Note: INSERT … RETURNING doesn't accept an ORDER BY clause. Would anyone be opposed to somebody - say, me - writing a patch to allow that? It would take me a lot longer than an experienced hacker to do it, but I'm willing to try.
Re: [HACKERS] [WIP] pg_ping utility
On Mon, Oct 15, 2012 at 9:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Phil Sorber p...@omniti.com writes: On Mon, Oct 15, 2012 at 7:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, I know a whole new executable is kind of a pain, and the amount of infrastructure and added maintenance seems a bit high compared to what this does. But a lot of the programs in src/bin/scripts are not much bigger. (In fact that might be the best place for this.) I considered src/bin/scripts but all those are for maintenance tasks on the DB. createdb/vacuumdb/reindexdb etc. It doesn't need any of the bits in common.h/common.c, nor does it need some of the includes that the build process has. Well, we classify all those programs as client-side tools in the documentation, so I don't see that pg_ping doesn't belong there. The alternative is to give it its very own subdirectory under src/bin/; which increases the infrastructure burden *significantly* (eg, now it needs its own NLS message catalog) for not a lot of value IMO. I would also like it to have a regression test which none of those seem to have. [ shrug... ] There is nothing in the current regression infrastructure that would work for this, so that desire is pie-in-the-sky regardless of where you put it in the source tree. Also, PQping itself is exercised in every buildfarm run as part of pg_ctl start, so I don't feel a real strong need to test pg_ping separately. regards, tom lane Here is the new patch. I renamed the utility from pg_ping to pingdb to go along with the naming convention of src/bin/scripts. Updated docs and made some other minor improvements. pingdb-bin.diff Description: Binary data pingdb-doc.diff Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [PATCH] Enforce that INSERT...RETURNING preserves the order of multi rows
On Sunday 21 October 2012, Vik Reykja wrote: On Sun, Oct 21, 2012 at 6:20 PM, Abhijit Menon-Sen a...@2ndquadrant.comwrote: Note: INSERT … RETURNING doesn't accept an ORDER BY clause. Would anyone be opposed to somebody - say, me - writing a patch to allow that? It would take me a lot longer than an experienced hacker to do it, but I'm willing to try. I would oppose, for one. Please, don't waste your time. Reordering the INSERT .. RETURNING results is already possible today, with some nested syntax. At the same time, bloating the INSERT syntax with SELECT semantics would be negative IMO. And I would see little use in having such a feature. At a worst case scenario, you could do (in client pseydocode): ids = query(INSERT INTO tableA (col1, col2) VALUES (...), (...) RETURNING id) ordered_ids = query(SELECT id FROM tableA WHERE id IN %s ORDER BY col1, ids) which would be minimally more roundtrip than a RETURNING id ORDER BY col1 . -- Say NO to spam and viruses. Stop using Microsoft Windows! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Very minor feature suggestion
On Thu, 2012-10-18 at 16:31 +, Murphy, Kevin wrote: It might be nice for psql to have a 'htmlcaption' boolean pset option that would wrap the provided title/caption, if any, in a caption tag in the HTML report output, when using html format. I'm not following. It does do that already: = \H Output format is html. = \C 'Some Title' Title is Some Title. = select 1; table border=1 captionSome Title/caption tr th align=center?column?/th /tr tr valign=top td align=right1/td /tr /table p(1 row)br / /p What do you wish to change? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
Kevin Grittner wrote: Will apply tomorrow if there are no further objections. Done. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
Kevin Grittner kgri...@mail.com writes: Kevin Grittner wrote: Will apply tomorrow if there are no further objections. Done. This needs to be back-patched, no? 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] [WIP] pg_ping utility
Phil Sorber p...@omniti.com writes: Here is the new patch. I renamed the utility from pg_ping to pingdb to go along with the naming convention of src/bin/scripts. Uh, no, that's not a step forward. Leaving out a pg prefix from those script names is universally agreed to have been a mistake. We've not felt that changing the legacy names is worth the amount of pain it'd cause, but that doesn't mean that we should propagate the mistake into brand new executable names. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
Tom Lane wrote: This needs to be back-patched, no? Looking at that now. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] No, pg_size_pretty(numeric) was not such a hot idea
On 21 October 2012 16:59, Kevin Grittner kgri...@mail.com wrote: I don't know about anyone else, but I could live with that. Me too. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
Kevin Grittner wrote: Tom Lane wrote: This needs to be back-patched, no? Looking at that now. Back-patched to 9.2. I don't know how I got it in my head that this was a pending 9.3 feature. I'll check next time, even if I think I know. Thanks to both Andres and Tom for pointing that out. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] assertion failure w/extended query protocol
Sorry It might be late here, but just wanted to share the patch I came up with. Actually with Robert told he reported issues to pgsql-hacker, I thought he might have also submitted patch. PFA I came up with, but seems like issue has been already committed. Thanks, On Sat, Oct 20, 2012 at 9:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@2ndquadrant.com writes: On Saturday, October 20, 2012 12:05:15 AM Tom Lane wrote: (such as the current query showing up in pg_cursors --- maybe we should prevent that?) I don't really see an argument for preventing that. Well, the reason it seems peculiar to me is that the current query is in no way a cursor --- it's just a SELECT in the cases that showed regression test differences. I didn't go looking in the code yet, but I suspect the pg_cursors view is displaying all Portals. Arguably, it should only display those that were created by actual cursor commands. 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 -- Rushabh Lathia assertion_pg.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
