Re: [HACKERS] GROUPING
Dean == Dean Rasheed dean.a.rash...@gmail.com writes: Maybe INT8 would be a better choice than INT4? But I'm not sure there's any practical use-case for more than 30 grouping sets anyway. Keep in mind the actual output volume probably grows like 2^N. Dean Actually using ROLLUP the output volume only grows linearly with Dean N. I tend to think that having such a large number of grouping Dean sets would be unlikely, however, it seems wrong to be putting an Dean arbitrary limit on it that's significantly smaller than the Dean number of columns allowed in a table. Limit on what exactly? Consider that in both MSSQL 2014 and Oracle 12 the limit on the number of arguments in a GROUPING() expression is ... 1. -- 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] GROUPING
On 20 May 2015 at 19:41, Tom Lane t...@sss.pgh.pa.us wrote: David Fetter da...@fetter.org writes: While kicking the tires on the new GROUPING() feature, I noticed that NUMERIC has no cast to bit(n). GROUPING() produces essentially a bitmap, although the standard mandates for some reason that it be a numeric type. I was thinking it should produce NUMERIC rather than int4 as it does now in order to accommodate large numbers of columns, but the usefulness of the bitmap is greatly increased if there's a simple CAST to bit(n). Maybe INT8 would be a better choice than INT4? But I'm not sure there's any practical use-case for more than 30 grouping sets anyway. Keep in mind the actual output volume probably grows like 2^N. Actually using ROLLUP the output volume only grows linearly with N. I tend to think that having such a large number of grouping sets would be unlikely, however, it seems wrong to be putting an arbitrary limit on it that's significantly smaller than the number of columns allowed in a table. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Redesigning checkpoint_segments
On Mon, Mar 16, 2015 at 11:05 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Mon, Feb 23, 2015 at 8:56 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Everyone seems to be happy with the names and behaviour of the GUCs, so
committed.
The docs suggest that max_wal_size will be respected during archive
recovery (causing restartpoints and recycling), but I'm not seeing that
happening. Is this a doc bug or an implementation bug?
I think the old behavior, where restartpoints were driven only by time and
not by volume, was a misfeature. But not a bug, because it was documented.
One of the points of max_wal_size and its predecessor is to limit how big
pg_xlog can grow. But running out of disk space on pg_xlog is no more fun
during archive recovery than it is during normal operations. So why
shouldn't max_wal_size be active during recovery?
It seems to be a trivial change to implement that, although I might be
overlooking something subtle (pasted below, also attached)
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -10946,7 +10946,7 @@ XLogPageRead(XLogReaderState *xlogreader,
XLogRecPtr targetPagePtr, int reqLen,
* Request a restartpoint if we've replayed too much xlog
since the
* last one.
*/
- if (StandbyModeRequested bgwriterLaunched)
+ if (bgwriterLaunched)
{
if (XLogCheckpointNeeded(readSegNo))
{
This keeps pg_xlog at about 67% of max_wal_size during archive recovery
(because checkpoint_completion_target is accounted for but goes unused)
Or, if we do not wish to make this change in behavior, then we should fix
the docs to re-instate this distinction between archive recovery and
standby.
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index f4083c3..ebc8baa 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -589,7 +589,8 @@
master because restartpoints can only be performed at checkpoint
records.
A restartpoint is triggered when a checkpoint record is reached if at
least varnamecheckpoint_timeout/ seconds have passed since the last
- restartpoint, or if WAL size is about to exceed
+ restartpoint. In standby mode, a restartpoint is also triggered if
+ WAL size is about to exceed
varnamemax_wal_size/.
/para
Cheers,
Jeff
recovery_max_wal_size.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disabling trust/ident authentication configure option
On Wed, May 20, 2015 at 5:21 PM, Robert Haas robertmh...@gmail.com wrote: Please don't be discouraged here. Contributing to the PostgreSQL community can be frustrating when you don't get what you want, and even though I have been a member of this community for about 7 years now and am a major contributor and committer, I still very often do not get what I want. But please don't view that as a personal rejection. I stand by what I said: disallowing trust authentication in pg_hba.conf will not slow down an attacker who wants to create a backdoor. I believe that to be true, and I can tell you why, but regardless of anything I say, you can still believe it to be false. I'm OK with that, and I hope you're OK with me having a different belief. It doesn't mean that I don't want you to continue reading this mailing list or suggesting things; in fact, I hope you will. The fact that I (and others) don't like this particular idea doesn't mean we won't like your next one, or the one after that. If this discussing has come across as bruising, I apologize for that. One of the things that sometimes happens is that somebody submits a patch and it goes for a long time without receiving any meaningful feedback. Then eventually, sometimes after a lot of work has been put into it, it gets rejected. That's not fun. So another approach is for people to respond right away when somebody posts a patch that they think is a bad idea and say: hey, wait, let's not do this, I think it's a bad idea. But then you can have a situation (which I think may have happened in this case) where a contributor feels that other people are jumping all over them. That's not fun, either. I don't know the answer to this problem. I'm not the world's greatest diplomat, and tone is even harder to read over email than it is in person. But I can tell you that I'm not mad at you personally, and I didn't spend time replying to this email thread just to get rid of you. If it came across that way, I'm sorry. Yes I guess discussing via mail always lends itself to misinterpretations, and people tend to read the worst possible interpretation :) So I am not offended and also did not intend to offend you in my reply. I likely just viewed this too much through a security lens - you see a possible attack scenario, a way to turn it off, and only minor downsides, so just go for it - but this is not how you can work in a huge open source project. I guess as a developer you would have to take many other issues (like maintainability, user confusion because of the change, edge use cases) into account. And as it seems to cause too much trouble for official inclusion I am fine with patching it during our package build. And yes once someone has write access to your pg_hba.conf you are very likely doomed. This would just prevent an attack happening through a careless trust entry left there, which is just a very quick win for an attacker, and may be a bit less likely through this patch. To answer to Tom: I see a restricted audience for this patch, but also no impact for anyone not wanting to use it. The group of users I see would be as follows: * People who package PostgreSQL as part of their product and want to provide their customers with a restricted more secure functionality set only to reduce training and support effort. (my use case) * People with large Postgres deployments who build their own packages and want to enforce a certain security policy (e.g. services are not allowed to offer authentication-less access over the network) - specifically a good security plan would be to only allow a safe subset of methods and ensure that these are well documented and perhaps audited automatically - this would also allow ensuring there is only one documented / audited way to reset passwords (modulo single user mode, that is an additional problem which won't be easily fixable) * Distributions which want to provide a more secure package and want to ensure each available method can be configured securely and documented clearly for their specific setup. It does not apply to (or would have a negative effect for) the following groups: * PostgreSQL users on Windows (disabling trust should not work or should show a very prominent warning) * Users of default builds (they simply won't be affected) * People with a specific use case requiring trust, ident or for the more generic patch other specific auth methods. They will be affected if they happen to be using a build with this method turned off. * People who are used to resetting passwords using trust and are surprised this suddenly does not work on some specific system My guess is the group who actually profit is relatively small, but the group of people actively affected would also be relatively small. I have no idea about actual usage so I am not qualified to judge here :)
Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On 21 May 2015 at 14:25, Peter Geoghegan p...@heroku.com wrote: If I have two constraints and I think about it, I would want to be able to specify this... INSERT ON CONFLICT (col1) DO UPDATE... (handle it one way) ON CONFLICT (col2) DO UPDATE... (handle it 2nd way) but I cannot with the current syntax. It seems strange to force the user to think about constraint handling and then not offer them any choices once they have done the thinking. What if both constraints are violated? Won't the update end up in trouble? Great question. We don't handle that at the moment. So how do we handle that? If the update is the same no matter which constraint is violated, why would I need to specify the constraint? We're forcing the developer to make an arbitrary choice between two constraints. Why would the update be the same, though? *If* is the keyword there. How could that make sense? It wouldn't, that is the point. So why does the current syntax force that? You're still going to have to update both unique-indexed columns with something, and that could fail. ISTM clear that you might want to handle each kind of violation differently, but we cannot. We will see many people ask why they have to specify constraints explicitly. I'm not sure that we will, actually, but as I said, go ahead and propose removing the restriction if you think it's important (maybe start a thread on it). I am. I have. Many times. What is wrong with this thread or all of the other times I said it? Please look at the $SUBJECT of this thread. We're here now. As I've pointed out, if the underlying model changes then you now have to explicitly recode all the SQL as well AND time that exactly so you roll out the new code at the same time you add/change constraints. That makes it much harder to use this feature than I would like. If the underlying model changes, then it's good that your queries break, because they're predicated on the original model. I don't think that happens very often at all. If it seldom happens, then why do we need to specify the conflict-target? If I know there is only one unique constraint, why can I not rely upon that knowledge? What is much more routine - adding redundant indexes to reindex using CREATE INDEX CONCURRENTLY, or changing the predicate on whatever partial unique indexes happen to be defined on the table - is handled gracefully. What has CREATE INDEX CONCURRENTLY got to do with this? If you don't specify the conflict-target at all, it wouldn't matter what the indexes are. If you have two indexes the same then it clearly wouldn't matter which one was checked first. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] GROUPING
On Thu, May 21, 2015 at 12:24:03PM -0400, Robert Haas wrote:
On Thu, May 21, 2015 at 12:21 PM, Andrew Gierth
and...@tao11.riddles.org.uk wrote:
David == David Fetter da...@fetter.org writes:
David How about a more sensible data structure as a PG-specific addon.
David GROUPING_JSON() seems like just the thing.
What exactly do you think it should return?
I vote for { rube : goldberg }.
Your point is well taken. I had { target_list_name : false, ... }
How about GROUPING_BYTEA()?
Also is there a really great reason that bitwise operations don't work
on NUMERIC? Lack of tuits is a good reason, but not, it seems to me,
a great one.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Float/Double cast to int
Feng Tian ft...@vitessedata.com writes: Here is a query, server was built witch GCC on Linux, AMD64. ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int, 314.5::double precision::int; int4 | int4 | int4 | int4 --+--+--+-- 2 |2 | 315 | 314 (1 row) I believe this is because rint is broken -- can some expert on IEEE754 please help confirm that this is a bug? rint() is doing what the IEEE spec says, ie round to nearest even. Your third expression is doing numeric-to-int, and that code doesn't obey the IEEE spec. We've had discussions (not too long ago) about making these behaviors more consistent, but people seem to be too afraid of backwards-compatibility problems if we change it. 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On Thu, May 21, 2015 at 9:51 AM, Simon Riggs si...@2ndquadrant.com wrote: No not all, but we can evaluate the constraints one at a time in a consistent order. We do so currently. Now, you point out that that might not be the most useful ordering, and as it happens I agree. But changing that ordering to not just be OID-ordering, but to put the PK first (and so on) isn't going to fundamentally change anything. FWIW, I think that that much (PK first) will usually accidentally be true anyway, because of the way that create table statement is originally executed. My point is this: We do not need to explicitly specify the constraint we wish to test to ensure that we get deterministic behaviour. So it is possible to avoid specifying a constraint/conflict target and still get deterministic behaviour (which is essential). It is deterministic, but omitting an inference specification still risks taking the wrong path. You seem not be acknowledging that you can still take the wrong path due to a dup violation in the wrong constraint. So being guaranteed to have observed or not observed a would-be dup violation in the PK does not buy much. If I have two constraints and I think about it, I would want to be able to specify this... INSERT ON CONFLICT (col1) DO UPDATE... (handle it one way) ON CONFLICT (col2) DO UPDATE... (handle it 2nd way) but I cannot with the current syntax. It seems strange to force the user to think about constraint handling and then not offer them any choices once they have done the thinking. What if both constraints are violated? Won't the update end up in trouble? If the update is the same no matter which constraint is violated, why would I need to specify the constraint? We're forcing the developer to make an arbitrary choice between two constraints. Why would the update be the same, though? How could that make sense? You're still going to have to update both unique-indexed columns with something, and that could fail. We will see many people ask why they have to specify constraints explicitly. I'm not sure that we will, actually, but as I said, go ahead and propose removing the restriction if you think it's important (maybe start a thread on it). As I've pointed out, if the underlying model changes then you now have to explicitly recode all the SQL as well AND time that exactly so you roll out the new code at the same time you add/change constraints. That makes it much harder to use this feature than I would like. If the underlying model changes, then it's good that your queries break, because they're predicated on the original model. I don't think that happens very often at all. What is much more routine - adding redundant indexes to reindex using CREATE INDEX CONCURRENTLY, or changing the predicate on whatever partial unique indexes happen to be defined on the table - is handled gracefully. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Float/Double cast to int
Hi, Hackers, Here is a query, server was built witch GCC on Linux, AMD64. ftian=# ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int, 314.5::double precision::int; int4 | int4 | int4 | int4 --+--+--+-- 2 |2 | 315 | 314 (1 row) I believe this is because rint is broken -- can some expert on IEEE754 please help confirm that this is a bug? Thanks, Feng
[HACKERS] Postgres and TLSv1.2
G'Day guys, after exploiting all the other sources, I've reached the point where I need to use this final option to get some help. We are trying to setup Postgres with TLSv1.2 (undergoing PA:DSS audit), but getting a bit stuck there with Postgres reporting “could not accept SSL connection: no shared cipher”. This is obviously an internal OpenSSL message, but worrying part is that we've had this setup running with the other encryptions and the same certificates without any problems. We've been trying to follow documentation from here: http://www.postgresql.org/docs/9.3/static/ssl-tcp.html. making changes in /etc/postgresql/9.3/main/postgresql.conf: before ssl = true #ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' #ssl_renegotiation_limit = 512MB ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' #ssl_ca_file = '' #ssl_crl_file = '' #password_encryption = on #db_user_namespace = off /before after ssl = true ssl_ciphers = 'TLSv1.2:!aNULL' #ssl_renegotiation_limit = 512MB ssl_cert_file = '/var/lib/postgresql/9.3/main/server.crt' ssl_key_file = '/var/lib/postgresql/9.3/main/server.key' ssl_ca_file = '/var/lib/postgresql/9.3/main/root.crt' #ssl_crl_file = '' #password_encryption = on #db_user_namespace = off /after logon string: postgresql://bp-node@172.27.72.45/bp-node?sslmode=require latest OpenSSL available. $ openssl ciphers -v 'TLSv1.2:!aNULL' returns all cyphers Once again - Certificates should be fine as this seem to work for any other encryptions. Can I have your advice please? Kind Regards, Jan Jan Bilek CTO, EFTlab Pty Ltd email:jan.bi...@eftlab.co.uk mob: +61 (0) 498 103 179 Note: When we've been doing the SSL/TLS implementation for our product, we've encountered same problem when SSL context was initialised after the SSL socket, so socket creation was done on empty CTX. But that was for all encryptions. -- 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
On May 20, 2015, at 9:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@anarazel.de writes: You realize there's other instances of this in the same damn function? Not to mention that several places in libpq/fe-exec.c should be taught about this new tag. And who-knows-what in other client-side libraries. I am not really sure that it was a good idea to invent this command tag. In fact, I'm pretty sure it was a *bad* idea --- what will happen if we ever create a statement actually named UPSERT? I think we should fix this by ripping out the variant tag, not trying to propagate it everywhere it would need to go. +1 ...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] Parallel Seq Scan
On Tue, May 19, 2015 at 8:45 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Mon, May 11, 2015 at 3:00 AM, Robert Haas robertmh...@gmail.com wrote: I think it might be better to try to solve this problem in a more localized way. Can we arrange for planstate-instrumentation to point directory into the DSM, instead of copying the data over later? Yes, we can do that but I am not sure we can do that for pgBufferUsage which is a separate information we need to pass back to master backend. One way could be to change pgBufferUsage to a pointer and then allocate the memory for same at backend startup time and for parallel workers, it should point to DSM. Do you see any simple way to handle it? No, that seems problematic. Another way could be that master backend waits for parallel workers to finish before collecting the instrumentation information and buffer usage stats. It seems to me that we need this information (stats) after execution in master backend is over, so I think we can safely assume that it is okay to finish the execution of parallel workers if they are not already finished the execution. I'm not sure exactly where you plan to insert the wait. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Archiving last incomplete segment as .partial issues
I noticed that my patch to archive the last incomplete segment from old
timeline at promotion with the .partial suffix (de768844) was a few
bricks shy of a load. It makes a copy of the segment with the .partial
suffix, and it gets archived correctly, but it still leaves the segment
lying in pg_xlog. After enough time has passed that the segment becomes
old enough to be recycled, it will still be archived, without the
.partial suffix, which has all the same problems as before.
To fix, the old segment should be renamed rather than copied, to have
the .partial suffix. And that needs to be done later in the startup
sequence, after the end-of-recovery record has been written, because if
the server crashes before that, it still needs the partial segment to
recover.
Attached is a patch to do that.
Another option would be to create a .done file for the last partial
segment immediately after the .partial copy has been made, so that it
won't get archived, but I think it's weird to have a .done file for a
segment that hasn't in fact been archived.
In the original commit, I refactored XLogFileCopy() to not call
InstallXLogFileSegment(), leaving that to the caller. But with the
attached patch, that refactoring is no longer needed, and could be
reverted. I think it still makes sense, from a code readability point of
view, although I wouldn't have bothered if it wasn't needed by the
original patch. Thoughts? I'm inclined to not revert the XLogFileCopy()
changes, although reverting might make backporting future patches
slightly easier.
- Heikki
From a6dab4c8db9f077ecd4b784f390ac161961c90c6 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas heikki.linnakangas@iki.fi
Date: Thu, 21 May 2015 15:28:22 +0300
Subject: [PATCH 1/1] At promotion, don't leave behind a partial segment on the
old timeline.
With commit de768844, a copy of the partial segment was archived with the
.partial suffix, but the original file was still left in pg_xlog, so it
didn't actually solve the problems with archiving the partial segment that
it was supposed to solve. With this patch, the partial segment is renamed
rather than copied, so we only archive it with the .partial suffix.
The old segment is needed until we're fully committed to the new timeline,
i.e. until we've written the end-of-recovery WAL record and updated the
min recovery point and timeline in the control file. So move the renaming
later in the startup sequence, after all that's been done.
---
src/backend/access/transam/xlog.c | 138 +++---
1 file changed, 84 insertions(+), 54 deletions(-)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index b203b82..2c94007 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5224,31 +5224,6 @@ exitArchiveRecovery(TimeLineID endTLI, XLogRecPtr endOfLog)
* happens in the middle of a segment, copy data from the last WAL segment
* of the old timeline up to the switch point, to the starting WAL segment
* on the new timeline.
- *
- * What to do with the partial segment on the old timeline? If we don't
- * archive it, and the server that created the WAL never archives it
- * either (e.g. because it was hit by a meteor), it will never make it to
- * the archive. That's OK from our point of view, because the new segment
- * that we created with the new TLI contains all the WAL from the old
- * timeline up to the switch point. But if you later try to do PITR to the
- * missing WAL on the old timeline, recovery won't find it in the
- * archive. It's physically present in the new file with new TLI, but
- * recovery won't look there when it's recovering to the older timeline.
- * On the other hand, if we archive the partial segment, and the original
- * server on that timeline is still running and archives the completed
- * version of the same segment later, it will fail. (We used to do that in
- * 9.4 and below, and it caused such problems).
- *
- * As a compromise, we archive the last segment with the .partial suffix.
- * Archive recovery will never try to read .partial segments, so they will
- * normally go unused. But in the odd PITR case, the administrator can
- * copy them manually to the pg_xlog directory (removing the suffix). They
- * can be useful in debugging, too.
- *
- * If a .done file already exists for the old timeline, however, there is
- * already a complete copy of the file in the archive, and there is no
- * need to archive the partial one. (In particular, if it was restored
- * from the archive to begin with, it's expected to have .done file).
*/
if (endLogSegNo == startLogSegNo)
{
@@ -5266,31 +5241,6 @@ exitArchiveRecovery(TimeLineID endTLI, XLogRecPtr endOfLog)
tmpfname = XLogFileCopy(NULL, xlogfname, endOfLog % XLOG_SEG_SIZE);
if (!InstallXLogFileSegment(endLogSegNo, tmpfname, false, 0, false))
elog(ERROR, InstallXLogFileSegment should not have failed);
Re: [HACKERS] Change pg_cancel_*() to ignore current backend
On Wed, May 20, 2015 at 8:46 PM, Andres Freund and...@anarazel.de wrote: I've a hard time believing it's actually a good idea to change this. It pretty much seems to only be useful if you're doing unqualified SELECT pg_cancel_backend(pid) FROM pg_stat_activity; type queries. I don't see that as something we need to address. +1. I'm not saying this isn't annoying - I've been annoyed by it myself - but IMHO it's really not worth having two functions that do 99% the same thing. Then, instead of having to remember to exclude your own backend using the same SQL syntax you use for everything else, you have to remember which of two similarly-named functions to call if you don't want to kill your own backend. That might be better for some people, but it won't be better for everyone. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING
On 21 May 2015 at 09:20, Andrew Gierth and...@tao11.riddles.org.uk wrote: Dean == Dean Rasheed dean.a.rash...@gmail.com writes: Maybe INT8 would be a better choice than INT4? But I'm not sure there's any practical use-case for more than 30 grouping sets anyway. Keep in mind the actual output volume probably grows like 2^N. Dean Actually using ROLLUP the output volume only grows linearly with Dean N. I tend to think that having such a large number of grouping Dean sets would be unlikely, however, it seems wrong to be putting an Dean arbitrary limit on it that's significantly smaller than the Dean number of columns allowed in a table. Limit on what exactly? Consider that in both MSSQL 2014 and Oracle 12 the limit on the number of arguments in a GROUPING() expression is ... 1. Actually Oracle haven't quite followed the standard. They have 2 separate functions: GROUPING() which only allows 1 parameter, and GROUPING_ID() which allows multiple parameters, and returns a bitmask like our GROUPING() function. However, their GROUPING_ID() function seems to return an arbitrary precision number and allows an arbitrary number of parameters (well, I tested it up 70 to prove it wasn't a 64-bit number). Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Support for N synchronous standby servers - take 2
On Mon, May 18, 2015 at 9:40 AM, Beena Emerson memissemer...@gmail.com wrote: Er, I am not sure I follow here. The idea proposed was to define a string formatted with some infra-language within the existing GUC s_s_names. I am sorry, I misunderstood. I thought the language approach meant use of hooks and module. As you mentioned the first step would be to reach the consensus on the method. If I understand correctly, s_s_names should be able to define: - a count of sync rep from a given group of names ex : 2 from A,B,C. - AND condition: Multiple groups and count can be defined. Ex: 1 from X,Y AND 2 from A,B,C. In this case, we can give the same priority to all the names specified in a group. The standby_names cannot be repeated across groups. Robert had also talked about a little more complex scenarios of choosing either A or both B and C. Additionally, preference for a standby could also be specified. Ex: among A,B and C, A can have higher priority and would be selected if an standby with name A is connected. This can make the language very complicated. Should all these scenarios be covered in the n-sync selection or can we start with the basic 2 and then update later? If it were me, I'd just go implement a scanner using flex and a parser using bison and use that to parse the format I suggested before, or some similar one. This may sound hard, but it's really not: I put together the patch that became commit 878fdcb843e087cc1cdeadc987d6ef55202ddd04 in just a few hours. I don't see why this would be particularly harder. Then instead of arguing about whether some stop-gap implementation is good enough until we do the real thing, we can just have the real thing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add support for interface/ipaddress binding to libpq
Hello! I'm willing to implement the 'Add support for interface/ipaddress binding to libpq' feature from the ToDo list. Actually, the 'ipaddress' part. For this I'm planning to do the following: - Introduce 'sourceaddr' parameter keyword and 'PGSOURCEADDR' env var which can be used to specify local IPv4 or IPv6 ipaddress to bind to - Silently ignore the parameter's value in case of Unix-domain communication with database - Use BLOCKING bind() call to bind communication socket to specified local ipaddress in two functions: src/interfaces/libpq/fe-connect.c@internal_cancel() src/interfaces/libpq/fe-connect.c@PQconnectPoll() Is this enough or I missed something? WBR, Grigory.
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
On 05/21/2015 05:08 AM, Peter Geoghegan wrote: On Wed, May 20, 2015 at 6:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: I am not really sure that it was a good idea to invent this command tag. In fact, I'm pretty sure it was a *bad* idea --- what will happen if we ever create a statement actually named UPSERT? Why would we invent a statement actually named UPSERT? And if we did, surely it would do some sort of an upsert operation, we could use the UPSERT command tag for that too. That said, I'm also not sure adding the UPSERT command tag is worth the trouble. I'm OK with ripping it out. The row count returned in the command tag is handy in the simple cases, but it gets complicated as soon as you have rules or triggers, so you can't rely much on it anyway. So as long as we document what the count means for an INSERT ... ON CONFLICT, it should be OK to use the INSERT tag. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_basebackup and replication slots
I wonder why pg_basebackup doesn't have any support for replication slots. When relying on replication slots to hang on to WAL data, there is a gap between when pg_basebackup finishes and streaming replication is started where WAL data could be thrown away by the primary. Looking at the code, the -X stream method could easily specify a replication slot. (Might be nice if it could also create it in the same run.) -- 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] Fix misaligned access of ItemPointerData on ARM
I wrote: But BlockIdData is laid out and accessed as two 16-bit fields, so there should be no problem. On what platform exactly do you see a failure? Ah, after reading the gcc manual a bit more closely, I get the point. For some reason I think we assumed that packed would not result in misaligning the struct overall, but it clearly could do so, with possible efficiency consequences on hardware that doesn't like misaligned accesses. If the compiler accepts __attribute__((aligned)) then what you've done is clearly better. It's not clear to me whether all compilers that accept packed also accept aligned, but there are enough ARM machines in the buildfarm that we could hope that we'll find out if this isn't portable. I wonder whether we should drop the ARM assumption and instead write #if defined(pg_attribute_packed) defined(pg_attribute_aligned) pg_attribute_packed() pg_attribute_aligned(2) #endif so that the annotations are applied on every compiler that accepts them. 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On Thu, May 21, 2015 at 1:15 PM, Simon Riggs si...@2ndquadrant.com wrote: OK, let me summarise. First, thanks for putting time into this feature; we all wish to see it work and work well. You're welcome. The current ON CONFLICT syntax requires us to specify one-and-only-one conflict_target/conflict_action pair. I would like to be able to specify 0, 1 or more conflict_targets, as the developer desires. Well, multiple unique indexes (that represent essentially the same business rule) can be inferred at the same time, for edge-cases around migrations and so on. It is very desirable to be able to specify DO UPDATE without any conflict_target, relying instead on our ability to infer a conflict_target deterministically. That is the way other systems work and we should be aiming to provide similar ease of use. Having said that, we all recognize that MySQL is broken for multiple constraints and we have done well to come up with a design that allows us to specify finer grained control when we have multiple constraints. (Ideally, we would use the identical syntax to MySQL, but that is secondary to simply avoiding specifying a conflict_target). Okay. No real argument here so far. If we do have multiple constraints then we should be allowed to specify multiple conflict_target/conflict_action pairs (or similar), since few people believe that one conflict_action would cover the various permutations that occur with multiple potential constraint failures. In summary, the current design seeks to overcome the problems of having multiple constraints, but doesn't yet do so in a flexible (0) or complete (1) way. My difficulty with this (which seems distinct to the concern about not mandating an inference specification, a concern which seems to only be about laziness and/or MySQL compatibility) is that I think you'll have a very hard time finding a case where the update naturally applies to the path when either constraint is taken, and applies indifferently. After all, and as I said, why should you not fail when updating the *other* constrained column in the update? Also, why should you not have to worry about *both* constraints failing at once (from the insert)? I think that if we try and address these cases, we'll end up with something unusable, complicated, and no better than simply writing two statements. As the patch author I hope and expect that you will listen to this and consider how you will resolve these problems, just as any of us has done when they are the patch author, even after commit. I would like to see this happen now before we get hit with usage questions similar to OP's. If both requests cannot happen now, if we can at least agree a path for future enhancement we can refer people to what will happen in later releases when they ask. That's reasonable. I only ask that you describe a plausible use case. Let's start with that. Try and convince me. -- Peter Geoghegan -- 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On 21 May 2015 at 15:44, Peter Geoghegan p...@heroku.com wrote: Please look at the $SUBJECT of this thread. We're here now. What do you want me to do about it? I've said that I think that what you say about not mandating the inference clause in the parser could be okay. If you want to change it, obviously you're going to need to get some buy in, and this thread could easily be missed. I'm not willing to defend mandating it, and I'm not willing to argue for removing it (to be clear, I think being able to infer a unique index is very important, but that doesn't mean that I'm attached to mandating it for UPDATE). That's all. OK, let me summarise. First, thanks for putting time into this feature; we all wish to see it work and work well. The current ON CONFLICT syntax requires us to specify one-and-only-one conflict_target/conflict_action pair. I would like to be able to specify 0, 1 or more conflict_targets, as the developer desires. It is very desirable to be able to specify DO UPDATE without any conflict_target, relying instead on our ability to infer a conflict_target deterministically. That is the way other systems work and we should be aiming to provide similar ease of use. Having said that, we all recognize that MySQL is broken for multiple constraints and we have done well to come up with a design that allows us to specify finer grained control when we have multiple constraints. (Ideally, we would use the identical syntax to MySQL, but that is secondary to simply avoiding specifying a conflict_target). If we do have multiple constraints then we should be allowed to specify multiple conflict_target/conflict_action pairs (or similar), since few people believe that one conflict_action would cover the various permutations that occur with multiple potential constraint failures. In summary, the current design seeks to overcome the problems of having multiple constraints, but doesn't yet do so in a flexible (0) or complete (1) way. As the patch author I hope and expect that you will listen to this and consider how you will resolve these problems, just as any of us has done when they are the patch author, even after commit. I would like to see this happen now before we get hit with usage questions similar to OP's. If both requests cannot happen now, if we can at least agree a path for future enhancement we can refer people to what will happen in later releases when they ask. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On Thu, May 21, 2015 at 1:50 PM, Simon Riggs si...@2ndquadrant.com wrote:
(There is no try)
CREATE TABLE customers
(username TEXT PRIMARY KEY
,email TEXT UNIQUE
,billing NUMERIC(11,2)
);
1. INSERT INTO customers VALUES ('sriggs', 'si...@2ndquadrant.com', 10.0);
2. INSERT INTO customers VALUES ('sriggs', 'si...@2ndquadrant.com', 10.0);
3. INSERT INTO customers VALUES ('sriggs2', 'si...@2ndquadrant.com', 10.0);
4. INSERT INTO customers VALUES ('sriggs', 'simon.ri...@2ndquadrant.com',
10.0);
Presumably you meant to indicate that these were upserts that lacked
an explicit inference specification.
How should we choose to handle the above data?
I don't know.
We might choose these rules:
If no constraints violate, accept insert
If both constraints violate, treat it as a repeat record and just set the
billing to the new value.
If first constraint violates but not second, treat it as an email address
update AND increment the billing, if any
If second constraint violates, reject the row since we only allow one userid
per person
With 2 constraints we have 4 permutations, i.e. O(2^N) permutations. If we
are claiming to handle multiple constraints, I don't think we should just
assume that they can all use the same UPDATE. I might point out that the
MERGE syntax allowed us to handle that fully, but you'll probably scream.
Well, MERGE doesn't, because it doesn't know anything about unique
indexes or concurrency. And in practice the join condition is almost
always an equi-join, with SQL server for example strongly advising
against putting much of anything in the join.
Anyway, I think that I might get what you're saying now. ISTM that
this could almost be accomplished without having multiple unique
constraints inferred in the way I thought you meant.
You'd be using all available unique indexes as arbiters, say. Any one
could force us to take the alternative path. You wouldn't have any way
to be directly introspect which unique index forced the update path to
be taken, but maybe that doesn't matter - you can figure it out
another way.
In this future version of upsert (this future version that I think is
workable), you can chain together multiple DO UPDATE WHERE ELSE
DO DELETE WHERE ... style handlers. You can handle each case of
yours at that level, by referencing the EXCLUDED.* and target alias in
each WHERE clause. This is closer to SQL MERGE (but unlike SQL MERGE,
you can't avoid an insert sometimes -- we always need that to
terminate the loop to maintain the useful upsert guarantees that MERGE
lacks).
This gets you most of the way there. Once you heap_lock_tuple() the
row (before going on to consider an update), you can be sure that
*all* values appearing in the existing target tuple are also locked,
just because the row is locked. You can't be sure that the update
changing (say) the e-mail field within the update won't then get a
duplicate violation, so I think this isn't 100% of what you're looking
for, but not too far off.
However, I have a hard time believing that really ensuring no
constraint violation on either of *both* constraints from the update
(or doing anything to avoid dup violations from an update) will ever
become a sensible user-visible feature. I'm already playing games with
MVCC. It's just too complicated. When you get an dup violation from
(say) updating the e-mail address is probably something that needs to
be shown to the webapp user or whatever anyway.
BTW, I tried to make updates use the speculative insertion
infrastructure at one point at Heikki's request, and it was utterly
intractable (MVCC snapshots cannot see speculatively inserted tuples,
but that goes out the window when updates need to work with it). But
that's incidental - my objection above is that doing 100% of what you
ask for is conceptually very hard to pin down.
--
Peter Geoghegan
--
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] GiST KNN Crasher
I wrote: Heikki Linnakangas hlinn...@iki.fi writes: I think that trying to find the equivalence member in create_index_scan() is too fragile. I agree; will contemplate how to do this better. I think probably what we ought to do here is just use exprType() of the ORDER BY expression. There are opclasses for which that would not work, because the operators are declared to accept anyarray or some other pseudotype; but I'm not aware of any current or contemplated indexorderby support that would hit such cases. It doesn't seem worth going out of our way for full generality when there are a lot of other restrictions on the indexorderby mechanism anyway. 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] GiST KNN Crasher
Paul Ramsey pram...@cleverelephant.ca writes: I'm implementing the recheck functionality for PostGIS so we can support it when 9.5 comes out, and came across this fun little crasher. Should be fixed as of git tip. Thanks for the report! 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
Alvaro Herrera wrote: That said, I'm not sure about having it be the same, either: first, I don't think we need to update the fe-exec.c code at all -- I mean, all the things I see there are very old compatibility stuff; (But as I said earlier, it doesn't really affect me either way, so feel free to rip it out.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
Andres Freund wrote: On 2015-05-20 21:22:08 -0400, Tom Lane wrote: Not to mention that several places in libpq/fe-exec.c should be taught about this new tag. And who-knows-what in other client-side libraries. I am not really sure that it was a good idea to invent this command tag. In fact, I'm pretty sure it was a *bad* idea --- what will happen if we ever create a statement actually named UPSERT? I think we should fix this by ripping out the variant tag, not trying to propagate it everywhere it would need to go. Cute ideas are not the same as good ideas. I'm not particularly worried about conflicting with a potential future UPSERT command. But I do see no corresponding benefit in having a differerent command tag, so I'm inclined to agree that ripping it out is likely the best way forward. On the other hand, this was noticed because Alvaro just argued that it *should* have a new command tag. Alvaro, where do you see the advantage? Well, I was just skimming nearby code and noticed that CreateCommandTag hadn't been updated. As I said elsewhere, I'm not even running commands. I'm not really set on having the tag be different. That said, I'm not sure about having it be the same, either: first, I don't think we need to update the fe-exec.c code at all -- I mean, all the things I see there are very old compatibility stuff; reporting the OID of the just-inserted row? Seriously, who has OIDs in user tables anymore? Surely we wouldn't try to do that for INSERT ON CONFLICT DO UPDATE at all ... if anyone wants that functionality, they can use RETURNING, which is far saner. As for PQcmdTuples, what would a single number returned mean? Are we returning the sum of both inserted and updated tuples? Isn't this a bit odd? If the number is useful, then perhaps we should distinguish the cases; and if it's not useful, why not just return zero? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
On Thu, May 21, 2015 at 4:32 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: (But as I said earlier, it doesn't really affect me either way, so feel free to rip it out.) That appears to be the consensus. Should I post a patch? -- Peter Geoghegan -- 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 TLSv1.2
I wrote: I think this was probably a mistake. I suggest that in the back branches we should leave the server alone (rejecting SSL v3 might annoy somebody using old non-libpq clients) but adjust libpq to use SSLv23_method() plus SSL_OP_NO_SSLv2 | SSL_OP_NO_SSLv3. IOW, back-patch 820f08cabdcbb899, though perhaps also the comment adjustments in 326e1d73c476a0b5. This would have the effect of allowing libpq to use TLS-anything, not only TLSv1 which is what it's been requiring since 7.3.2. Done at http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c6b7b9a9cef1253ad12122959d0e78f62d8aee1f This is too late for tomorrow's releases, but it will be in the next minor releases --- or if you're in a hurry, you could apply that patch locally. 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
On 2015-05-21 20:28:41 -0300, Alvaro Herrera wrote: That said, I'm not sure about having it be the same, either: first, I don't think we need to update the fe-exec.c code at all -- I mean, all the things I see there are very old compatibility stuff; reporting the OID of the just-inserted row? Seriously, who has OIDs in user tables anymore? Surely we wouldn't try to do that for INSERT ON CONFLICT DO UPDATE at all ... if anyone wants that functionality, they can use RETURNING, which is far saner. The oid currently is reported for UPSERT... I agree it's not worth much, but it seems pointless to break it for a single command. As for PQcmdTuples, what would a single number returned mean? Are we returning the sum of both inserted and updated tuples? Yes. Isn't this a bit odd? Imo it's pretty much in line with what's done with INSTEAD OF, FDWs and such. If the number is useful, then perhaps we should distinguish the cases; and if it's not useful, why not just return zero? There's libraries/frameworks checking if an insert succeeded by looking at that number, and it seems like a bad idea to needlessly break those. So I think we're good with ripping it out. Peter? -- 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] Float/Double cast to int
Ah, thanks! I did not realize numeric comes into play. But, this is even more interesting -- I would expect numeric is more consistent than float/double when dealing with stuff like rounding. I missed the not too long ago discussion, :-) Regardless of the mechanisms underneath, it would be quite hard to explain this behavior to customer. Maybe it is time to be brave, and be compatible with reality instead of backward? Best, Feng On Thu, May 21, 2015 at 12:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Feng Tian ft...@vitessedata.com writes: Here is a query, server was built witch GCC on Linux, AMD64. ftian=# select 1.5::int, 1.5::double precision::int, 314.5::int, 314.5::double precision::int; int4 | int4 | int4 | int4 --+--+--+-- 2 |2 | 315 | 314 (1 row) I believe this is because rint is broken -- can some expert on IEEE754 please help confirm that this is a bug? rint() is doing what the IEEE spec says, ie round to nearest even. Your third expression is doing numeric-to-int, and that code doesn't obey the IEEE spec. We've had discussions (not too long ago) about making these behaviors more consistent, but people seem to be too afraid of backwards-compatibility problems if we change it. regards, tom lane
Re: [HACKERS] GROUPING
David Fetter da...@fetter.org writes: Also is there a really great reason that bitwise operations don't work on NUMERIC? Lack of tuits is a good reason, but not, it seems to me, a great one. Not sure that bitwise operations make too much sense on values that are (a) possibly fractional and (b) inherently decimal not binary. 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On Thu, May 21, 2015 at 11:55 AM, Simon Riggs si...@2ndquadrant.com wrote: It seems strange to force the user to think about constraint handling and then not offer them any choices once they have done the thinking. What if both constraints are violated? Won't the update end up in trouble? Great question. We don't handle that at the moment. So how do we handle that? By writing two separate INSERT ... ON CONFLICT DO UPDATE statements? There is very little or no disadvantage to doing it that way. If the update is the same no matter which constraint is violated, why would I need to specify the constraint? We're forcing the developer to make an arbitrary choice between two constraints. Why would the update be the same, though? *If* is the keyword there. I'm having a hard time imagining a scenario in which the update would be the same. That's why I asked how it could be. I'm asking for a practical example involving plausible business rules. How could that make sense? It wouldn't, that is the point. So why does the current syntax force that? You're still going to have to update both unique-indexed columns with something, and that could fail. ISTM clear that you might want to handle each kind of violation differently, but we cannot. I think you can -- with two statements. We will see many people ask why they have to specify constraints explicitly. I'm not sure that we will, actually, but as I said, go ahead and propose removing the restriction if you think it's important (maybe start a thread on it). I am. I have. Many times. What is wrong with this thread or all of the other times I said it? Please look at the $SUBJECT of this thread. We're here now. What do you want me to do about it? I've said that I think that what you say about not mandating the inference clause in the parser could be okay. If you want to change it, obviously you're going to need to get some buy in, and this thread could easily be missed. I'm not willing to defend mandating it, and I'm not willing to argue for removing it (to be clear, I think being able to infer a unique index is very important, but that doesn't mean that I'm attached to mandating it for UPDATE). That's all. As I've pointed out, if the underlying model changes then you now have to explicitly recode all the SQL as well AND time that exactly so you roll out the new code at the same time you add/change constraints. That makes it much harder to use this feature than I would like. If the underlying model changes, then it's good that your queries break, because they're predicated on the original model. I don't think that happens very often at all. If it seldom happens, then why do we need to specify the conflict-target? If I know there is only one unique constraint, why can I not rely upon that knowledge? You say that as if I'm giving you pushback on that point -- for the third time, I'm not. If there is more than one unique constraint (or if there might be in the future), why take the chance that the update will take the wrong path? I'm not saying that that's the overriding consideration, but it is certainly a big consideration. What is much more routine - adding redundant indexes to reindex using CREATE INDEX CONCURRENTLY, or changing the predicate on whatever partial unique indexes happen to be defined on the table - is handled gracefully. What has CREATE INDEX CONCURRENTLY got to do with this? If you don't specify the conflict-target at all, it wouldn't matter what the indexes are. If you have two indexes the same then it clearly wouldn't matter which one was checked first. I'm not talking about that here. What I meant is that changes to unique indexes that don't affect the underlying model (as you put it) don't break your queries. Changes that do *will* break your queries. And that's definitely a good thing. I am pretty neutral on whether it's right to mandate that DO UPDATE statements *must* buy in to this. -- Peter Geoghegan -- 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] Problems with question marks in operators (JDBC, ECPG, ...)
[Sorry for being late to the party, travelling does take away too much time sometimes.] On 19.05.2015 21:04, Greg Sabino Mullane wrote: Bruno Harbulot asked for a devil's advocate by saying: My main point was that this is not specific to JDBC. Considering that even PostgreSQL's own ECPG is affected, the issue goes probably deeper than it seems. I'm just not convinced that passing the problem onto connectors, libraries and ultimately application developers is the right thing to do here. Well, one could argue that it *is* their problem, as they should be using the standard Postgres way for placeholders, which is $1, $2, $3... As Bruno already pointed out one could also argue that they just try to accept what the standard asked them for. I fail to see how such a way of arguing brings us closer to a solution, though. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at gmail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
* Simon Riggs (si...@2ndquadrant.com) wrote: On 21 May 2015 at 14:25, Peter Geoghegan p...@heroku.com wrote: If the update is the same no matter which constraint is violated, why would I need to specify the constraint? We're forcing the developer to make an arbitrary choice between two constraints. Why would the update be the same, though? *If* is the keyword there. Agreed. We will see many people ask why they have to specify constraints explicitly. I'm not sure that we will, actually, but as I said, go ahead and propose removing the restriction if you think it's important (maybe start a thread on it). I am. I have. Many times. What is wrong with this thread or all of the other times I said it? Please look at the $SUBJECT of this thread. We're here now. I've also asked for this. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Fix misaligned access of ItemPointerData on ARM
On 2015-05-21 15:34:00 -0400, Tom Lane wrote:
Piotr Stefaniak postg...@piotr-stefaniak.me writes:
But due to how ExecRowMark struct is laid out in memory, the packed
struct ItemPointerData begins at an uneven offset, leading to misaligned
access whenever BlockIdData is set by ItemPointerSetInvalid() (and
likely in some other places, too).
But BlockIdData is laid out and accessed as two 16-bit fields, so there
should be no problem. On what platform exactly do you see a failure?
It's probably aligned on a byte boundary:
typedef struct ExecRowMark
{
Relationrelation; /* opened and suitably locked
relation */
Index rti;/* its range table index */
Index prti; /* parent range table index, if
child */
Index rowmarkId; /* unique identifier for
resjunk columns */
RowMarkType markType; /* see enum in nodes/plannodes.h */
boolnoWait; /* NOWAIT option */
ItemPointerData curCtid;/* ctid of currently locked tuple, if
any */
} ExecRowMark;
due to the packedness curCtid will quite possibly be stored without any
padding after after noWait.
--
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] Fix misaligned access of ItemPointerData on ARM
Piotr Stefaniak postg...@piotr-stefaniak.me writes: But due to how ExecRowMark struct is laid out in memory, the packed struct ItemPointerData begins at an uneven offset, leading to misaligned access whenever BlockIdData is set by ItemPointerSetInvalid() (and likely in some other places, too). But BlockIdData is laid out and accessed as two 16-bit fields, so there should be no problem. On what platform exactly do you see a failure? 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] Problems with question marks in operators (JDBC, ECPG, ...)
available as soon as 9.6 came out. But from the perspective of a driver author who has to support queries written by other people, the problem would not be gone for at least ten years more. Changing the driver's behavior sounds like a more practical solution. Even if it means breaking the standard? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at gmail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GiST KNN Crasher
I'm implementing the recheck functionality for PostGIS so we can support it when 9.5 comes out, and came across this fun little crasher. This works: select id, name from geonames order by geom - 'SRID=4326;POINT(-75.6163 39.746)'::geometry limit 10; This crashes (just reversing the argument order to the - operator): select id, name from geonames order by 'SRID=4326;POINT(-75.6163 39.746)'::geometry - geom limit 10; The stack trace on crash looks like this: * thread #1: tid = 0x8d2bb, 0x000100455247 postgres`create_indexscan_plan(root=0x7fbf8d005c80, best_path=0x7fbf8b823600, tlist=0x7fbf8d0088d0, scan_clauses=0x, indexonly='\0') + 1063 at createplan.c:1354, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x24) * frame #0: 0x000100455247 postgres`create_indexscan_plan(root=0x7fbf8d005c80, best_path=0x7fbf8b823600, tlist=0x7fbf8d0088d0, scan_clauses=0x, indexonly='\0') + 1063 at createplan.c:1354 frame #1: 0x0001004518c9 postgres`create_scan_plan(root=0x7fbf8d005c80, best_path=0x7fbf8b823600) + 377 at createplan.c:360 frame #2: 0x00010044e749 postgres`create_plan_recurse(root=0x7fbf8d005c80, best_path=0x7fbf8b823600) + 73 at createplan.c:248 frame #3: 0x00010044e691 postgres`create_plan(root=0x7fbf8d005c80, best_path=0x7fbf8b823600) + 113 at createplan.c:209 frame #4: 0x000100460770 postgres`grouping_planner(root=0x7fbf8d005c80, tuple_fraction=0.048008487900660838) + 4560 at planner.c:1736 frame #5: 0x00010045e1dd postgres`subquery_planner(glob=0x7fbf8b823950, parse=0x7fbf8b823060, parent_root=0x, hasRecursion='\0', tuple_fraction=0, subroot=0x7fff5faf7028) + 2461 at planner.c:619 frame #6: 0x00010045d4a2 postgres`standard_planner(parse=0x7fbf8b823060, cursorOptions=0, boundParams=0x) + 450 at planner.c:229 frame #7: 0x00010045d2d1 postgres`planner(parse=0x7fbf8b823060, cursorOptions=0, boundParams=0x) + 81 at planner.c:157 frame #8: 0x00010054ab6c postgres`pg_plan_query(querytree=0x7fbf8b823060, cursorOptions=0, boundParams=0x) + 140 at postgres.c:809 frame #9: 0x00010054ac43 postgres`pg_plan_queries(querytrees=0x7fbf8d006230, cursorOptions=0, boundParams=0x) + 115 at postgres.c:868 frame #10: 0x00010054d920 postgres`exec_simple_query(query_string=0x7fbf8b821a38) + 800 at postgres.c:1033 frame #11: 0x00010054cda2 postgres`PostgresMain(argc=1, argv=0x7fbf8b8066d0, dbname=0x7fbf8b806538, username=0x7fbf8b806518) + 2546 at postgres.c:4025 frame #12: 0x0001004b17fe postgres`BackendRun(port=0x7fbf8b4079d0) + 686 at postmaster.c:4162 frame #13: 0x0001004b0d90 postgres`BackendStartup(port=0x7fbf8b4079d0) + 384 at postmaster.c:3838 frame #14: 0x0001004ad497 postgres`ServerLoop + 663 at postmaster.c:1594 frame #15: 0x0001004aab9c postgres`PostmasterMain(argc=3, argv=0x7fbf8b407760) + 5644 at postmaster.c:1241 frame #16: 0x0001003e649d postgres`main(argc=3, argv=0x7fbf8b407760) + 749 at main.c:221 frame #17: 0x7fff8ca915fd libdyld.dylib`start + 1 And my SQL declarations look like this: #if POSTGIS_PGSQL_VERSION = 95 -- Availability: 2.2.0 CREATE OR REPLACE FUNCTION geography_knn_distance(geography, geography) RETURNS float8 AS 'MODULE_PATHNAME','geography_distance' LANGUAGE 'c' IMMUTABLE STRICT COST 100; -- Availability: 2.2.0 CREATE OPERATOR - ( LEFTARG = geography, RIGHTARG = geography, PROCEDURE = geography_knn_distance, COMMUTATOR = '-' ); -- Availability: 2.2.0 CREATE OR REPLACE FUNCTION geography_gist_distance(internal, geography, int4) RETURNS float8 AS 'MODULE_PATHNAME' ,'gserialized_gist_distance' LANGUAGE 'c'; #endif -- Availability: 1.5.0 CREATE OPERATOR CLASS gist_geography_ops DEFAULT FOR TYPE geography USING GIST AS STORAGE gidx, OPERATOR3 , -- OPERATOR6~= , -- OPERATOR7~ , -- OPERATOR8@ , #if POSTGIS_PGSQL_VERSION = 95 -- Availability: 2.2.0 OPERATOR13 - FOR ORDER BY pg_catalog.float_ops, FUNCTION8geography_gist_distance (internal, geography, int4), #endif FUNCTION1geography_gist_consistent (internal, geography, int4), FUNCTION2geography_gist_union (bytea, internal), FUNCTION3geography_gist_compress (internal), FUNCTION4geography_gist_decompress (internal), FUNCTION5geography_gist_penalty (internal, internal, internal), FUNCTION6geography_gist_picksplit (internal, internal), FUNCTION7geography_gist_same (box2d, box2d, internal); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On 19 May 2015 at 19:59, Peter Geoghegan p...@heroku.com wrote: On Tue, May 19, 2015 at 2:28 PM, Simon Riggs si...@2ndquadrant.com wrote: On 19 May 2015 at 17:10, Peter Geoghegan p...@heroku.com wrote: On Tue, May 19, 2015 at 1:57 PM, Simon Riggs si...@2ndquadrant.com wrote: We should allow DO UPDATE to exclude a constraint and apply a deterministic order to the constraints. 1. PK if it exists. 2. Replica Identity, when not PK, 3. UNIQUE constraints in name order, like triggers, so users can define a default evaluation order, just like they do with triggers. That seems like something way worse than just allowing it for all constraints. I'm talking about the evaluation order; it would still match all constraints, otherwise they wouldn't be constraints. But it doesn't match all constraints when a would-be conflict is detected. No not all, but we can evaluate the constraints one at a time in a consistent order. My point is this: We do not need to explicitly specify the constraint we wish to test to ensure that we get deterministic behaviour. So it is possible to avoid specifying a constraint/conflict target and still get deterministic behaviour (which is essential). With Postgres, we want to make sure that the user has put thought into the condition they take that update path on, and so it is mandatory (it can infer multiple unique indexes, but only when they're basically equivalent for this purpose). If I have two constraints and I think about it, I would want to be able to specify this... INSERT ON CONFLICT (col1) DO UPDATE... (handle it one way) ON CONFLICT (col2) DO UPDATE... (handle it 2nd way) but I cannot with the current syntax. It seems strange to force the user to think about constraint handling and then not offer them any choices once they have done the thinking. If the update is the same no matter which constraint is violated, why would I need to specify the constraint? We're forcing the developer to make an arbitrary choice between two constraints. I actually do not feel strongly that it would be terrible to allow the user to omit an inference clause for the DO UPDATE variant (on the grounds of that being closer to MySQL). After all, we don't mandate that the user specifies an explicit targetlist for INSERT, and that seems like a footgun to me. If you want to make the case for doing things that way, I probably will not oppose it. FWIW, I don't think it's unreasonable to have a little discussion on fine points of semantics like that post feature-freeze. We will see many people ask why they have to specify constraints explicitly. As I've pointed out, if the underlying model changes then you now have to explicitly recode all the SQL as well AND time that exactly so you roll out the new code at the same time you add/change constraints. That makes it much harder to use this feature than I would like. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] INSERT ... ON CONFLICT DO UPDATE with _any_ constraint
On 20 May 2015 at 05:49, Geoff Winkless pgsqlad...@geoff.dj wrote: On 19 May 2015 at 21:57, Simon Riggs si...@2ndquadrant.com wrote: It's not clear to me how a single INSERT could cause two or more UPDATEs. CREATE TABLE mytable ( c1 int NOT NULL, c2 int NOT NULL, PRIMARY KEY (c1), UNIQUE (c2) ); INSERT INTO mytable (c1, c2) (10, 20); INSERT INTO mytable (c1, c2) (11, 21); INSERT INTO mytable (c1, c2) (10, 21) ON CONFLICT DO UPDATE . Or did you mean from a coding point of view how it would be possible to implement? I mean how could that possibly have useful meaning?. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] GROUPING
On Thu, May 21, 2015 at 12:21 PM, Andrew Gierth
and...@tao11.riddles.org.uk wrote:
David == David Fetter da...@fetter.org writes:
David How about a more sensible data structure as a PG-specific addon.
David GROUPING_JSON() seems like just the thing.
What exactly do you think it should return?
I vote for { rube : goldberg }.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING
On 21 May 2015 at 17:15, David Fetter da...@fetter.org wrote: On Thu, May 21, 2015 at 04:19:27PM +0100, Andrew Gierth wrote: Dean == Dean Rasheed dean.a.rash...@gmail.com writes: Consider that in both MSSQL 2014 and Oracle 12 the limit on the number of arguments in a GROUPING() expression is ... 1. Dean Actually Oracle haven't quite followed the standard. They have 2 Dean separate functions: GROUPING() which only allows 1 parameter, and Dean GROUPING_ID() which allows multiple parameters, and returns a Dean bitmask like our GROUPING() function. However, their Dean GROUPING_ID() function seems to return an arbitrary precision Dean number and allows an arbitrary number of parameters (well, I Dean tested it up 70 to prove it wasn't a 64-bit number). True. It can handle more than 128 bits, even - I gave up trying after that. So. Options: 1) change GROUPING() to return bigint and otherwise leave it as is. Seems cheap and reasonable. Making sure people know that GROUPING can be called multiple times seems like another cheap and reasonable measure. Yeah, seems reasonable. The lack of any bitwise operations on numeric makes it pretty-much useless in this context. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING
Andres == Andres Freund and...@anarazel.de writes:
Andres I'd vote for either 0) do nothing or 1). I think the use case
Andres for specifying 64+ (or even 32+) columns in grouping is pretty
Andres darn slim. And as you said, it's not that hard to work around
Andres it if you need it, and that's only going to be in an automated
Andres fashion anyway.
If the vote goes with (1), this patch ought to suffice:
--
Andrew (irc:RhodiumToad)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 89a609f..e0eeae0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13350,10 +13350,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
functionGROUPING(replaceable class=parameterargs.../replaceable)/function
/entry
entry
- typeinteger/type
+ typebigint/type
/entry
entry
- Integer bitmask indicating which arguments are not being included in the current
+ Bitmask indicating which arguments are not being included in the current
grouping set
/entry
/row
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index d414e20..70e9c28 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -3034,10 +3034,10 @@ ExecEvalGroupingFuncExpr(GroupingFuncExprState *gstate,
bool *isNull,
ExprDoneCond *isDone)
{
- int result = 0;
- int attnum = 0;
- Bitmapset *grouped_cols = gstate-aggstate-grouped_cols;
- ListCell *lc;
+ int64 result = 0;
+ int attnum = 0;
+ Bitmapset *grouped_cols = gstate-aggstate-grouped_cols;
+ ListCell *lc;
if (isDone)
*isDone = ExprSingleResult;
@@ -3054,7 +3054,7 @@ ExecEvalGroupingFuncExpr(GroupingFuncExprState *gstate,
result = result | 1;
}
- return (Datum) result;
+ return Int64GetDatum(result);
}
/*
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 4176393..baa3303 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -55,7 +55,7 @@ exprType(const Node *expr)
type = ((const Aggref *) expr)-aggtype;
break;
case T_GroupingFunc:
- type = INT4OID;
+ type = INT8OID;
break;
case T_WindowFunc:
type = ((const WindowFunc *) expr)-wintype;
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 1e3f2e0..8119af5 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -238,10 +238,10 @@ transformGroupingFunc(ParseState *pstate, GroupingFunc *p)
List *result_list = NIL;
GroupingFunc *result = makeNode(GroupingFunc);
- if (list_length(args) 31)
+ if (list_length(args) 63)
ereport(ERROR,
(errcode(ERRCODE_TOO_MANY_ARGUMENTS),
- errmsg(GROUPING must have fewer than 32 arguments),
+ errmsg(GROUPING must have fewer than 64 arguments),
parser_errposition(pstate, p-location)));
foreach(lc, args)
--
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 TLSv1.2
On 22/05/15 02:06, Tom Lane wrote: Jan Bilek jan.bi...@eftlab.co.uk writes: We are trying to setup Postgres with TLSv1.2 (undergoing PA:DSS audit), but getting a bit stuck there with Postgres reporting “could not accept SSL connection: no shared cipher�. This is obviously an internal OpenSSL message, but worrying part is that we've had this setup running with the other encryptions and the same certificates without any problems. We've been trying to follow documentation from here: http://www.postgresql.org/docs/9.3/static/ssl-tcp.html. libpq versions before 9.4 will only accept TLSv1 exactly. In 9.4 it should negotiate the highest TLS version supported by both server and client. I don't recall why we didn't back-patch that change, probably excessive concern for backwards compatibility ... but anyway, AFAICS from the git logs, it's not in 9.3.x. I think you could get TLS 1.2 from a 9.3 server and 9.4 libpq, if that helps. regards, tom lane That explains it whole. Thank you for your fast and clear answer. Best, Jan -- 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 TLSv1.2
I wrote: libpq versions before 9.4 will only accept TLSv1 exactly. In 9.4 it should negotiate the highest TLS version supported by both server and client. I don't recall why we didn't back-patch that change, probably excessive concern for backwards compatibility ... but anyway, AFAICS from the git logs, it's not in 9.3.x. I think you could get TLS 1.2 from a 9.3 server and 9.4 libpq, if that helps. Looking back at the discussions, it seems the reason we only changed HEAD is that the change to let libpq negotiate TLS v1 got conflated with modifying the server to reject SSL v3. See threads here: http://www.postgresql.org/message-id/flat/20140110061253.46e0e153e...@machamp.omnigroup.com#20140110061253.46e0e153e...@machamp.omnigroup.com http://www.postgresql.org/message-id/flat/e1w6rb1-go...@gemulon.postgresql.org and particularly commits 820f08cabdcbb899 and 326e1d73c476a0b5. I think this was probably a mistake. I suggest that in the back branches we should leave the server alone (rejecting SSL v3 might annoy somebody using old non-libpq clients) but adjust libpq to use SSLv23_method() plus SSL_OP_NO_SSLv2 | SSL_OP_NO_SSLv3. IOW, back-patch 820f08cabdcbb899, though perhaps also the comment adjustments in 326e1d73c476a0b5. This would have the effect of allowing libpq to use TLS-anything, not only TLSv1 which is what it's been requiring since 7.3.2. Thoughts? 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] Missing importing option of postgres_fdw
On Mon, May 18, 2015 at 4:03 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: On 2015/05/16 3:32, Robert Haas wrote: On Thu, May 14, 2015 at 6:37 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: On second thought, I noticed that as for this option, we cannot live without allowing IMPORT FOREIGN SCHEMA to return ALTER FOREIGN TABLE statements because we cannot declare the convalidated information in the CREATE FOREIGN TABLE statement. So, I think we shoould also allow it to return ALTER FOREIGN TABLE statements. Am I right? Isn't convalidated utterly meaningless for constraints on foreign tables? Let me explain. I think that convalidated would be *essential* for accurately performing relation_excluded_by_constraints for foreign tables like plain tables; if we didn't have that information, I think we would fail to accurately detect whether foreign tables need not be scanned. My point is that any constraint on a foreign table is just something we HOPE the remote side is enforcing. Regardless of whether convalidated is true or false locally, it could have some other value on the remote side, or the constraint might not exist on the remote side at all. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUPING
On Thu, May 21, 2015 at 04:19:27PM +0100, Andrew Gierth wrote: Dean == Dean Rasheed dean.a.rash...@gmail.com writes: Consider that in both MSSQL 2014 and Oracle 12 the limit on the number of arguments in a GROUPING() expression is ... 1. Dean Actually Oracle haven't quite followed the standard. They have 2 Dean separate functions: GROUPING() which only allows 1 parameter, and Dean GROUPING_ID() which allows multiple parameters, and returns a Dean bitmask like our GROUPING() function. However, their Dean GROUPING_ID() function seems to return an arbitrary precision Dean number and allows an arbitrary number of parameters (well, I Dean tested it up 70 to prove it wasn't a 64-bit number). True. It can handle more than 128 bits, even - I gave up trying after that. So. Options: 1) change GROUPING() to return bigint and otherwise leave it as is. Seems cheap and reasonable. Making sure people know that GROUPING can be called multiple times seems like another cheap and reasonable measure. *) any other ideas? How about a more sensible data structure as a PG-specific addon. GROUPING_JSON() seems like just the thing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] GROUPING
David == David Fetter da...@fetter.org writes: David How about a more sensible data structure as a PG-specific addon. David GROUPING_JSON() seems like just the thing. What exactly do you think it should return? -- 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
