Re: [HACKERS] Redesigning checkpoint_segments
On Mon, Mar 16, 2015 at 11:05 PM, Jeff Janes 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 checkpoint_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
max_wal_size.
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] a few thoughts on the schedule
On Wed, May 20, 2015 at 09:15:14AM -0400, Simon Riggs wrote: > On 20 May 2015 at 03:13, Noah Misch wrote: > > Brief committer appraisals are unhelpful individually, but patterns > > matter. I > > would make the questionnaire as simple as necessary to get 4-7 committer > > evaluations per patch. Prefer 30-second analyses from each of five > > committers, not 30-minute analyses from two. > I'm happy to participate as a "triager" and will follow whatever process we > decide. > > I would very much like to make this something we do via the CF app. Good place for it. > I believe we should include in our thinking how we nurture and grow > reviewers, contributors and committers. I am more likely to treat a > low-value patch seriously if it is an early contribution from someone, for > example. Absolutely, though I am unsure how to specifically account for that in community processes. -- 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] [COMMITTERS] pgsql: Add pg_audit, an auditing extension
On Thu, May 14, 2015 at 01:38:49PM -0400, Tom Lane wrote:
> * The comments in the code betray utter ignorance of how logging actually
> works, in particular this:
>
> * Administrators can choose which log level the audit log is to be logged
> * at. The default level is LOG, which goes into the server log but does
> * not go to the client. Set to NOTICE in the regression tests.
>
> All the user has to do is change client_min_messages and he'll see all the
> reports, which means if you think that letting the user see the audit
> reports is a security problem then you have a hole a mile wide.
That indicates the patch's general readiness:
> + /* These are DDL, unless they are ROLE */
> + case LOGSTMT_DDL:
> + className = CLASS_DDL;
> + class = LOG_DDL;
> +
> + /* Identify role statements */
> + switch (stackItem->auditEvent.commandTag)
> + {
> + /* We know these are all role statements */
> + case T_GrantStmt:
> + case T_GrantRoleStmt:
> + case T_CreateRoleStmt:
> + case T_DropRoleStmt:
> + case T_AlterRoleStmt:
> + case T_AlterRoleSetStmt:
> + className = CLASS_ROLE;
> + class = LOG_ROLE;
> + break;
Not T_AlterDefaultPrivilegesStmt?
> +static void
> +pg_audit_ProcessUtility_hook(Node *parsetree,
> + const char
> *queryString,
> + ProcessUtilityContext
> context,
> + ParamListInfo params,
> + DestReceiver *dest,
> + char *completionTag)
> +{
> + AuditEventStackItem *stackItem = NULL;
> + int64 stackId = 0;
> +
> + /*
> + * Don't audit substatements. All the substatements we care about
> should
> + * be covered by the event triggers.
> + */
> + if (context <= PROCESS_UTILITY_QUERY &&
> !IsAbortedTransactionBlockState())
They aren't covered. A GRANT inside CREATE SCHEMA escapes auditing:
create extension pg_audit;
SET pg_audit.log = 'role';
SET pg_audit.log_catalog = OFF;
SET pg_audit.log_level = 'warning';
SET pg_audit.log_parameter = on;
SET pg_audit.log_relation = on;
SET pg_audit.role = auditor;
SET pg_audit.log_statement_once = ON;
create table t ();
create role alice;
grant select on t to alice;
revoke select on t from alice;
\z t
create schema foo grant select on public.t to alice;
\z t
I'm wary of the ease of forgetting to run CREATE EXTENSION. One gets much
auditing from GUCs alone; for example, we audit "CREATE TABLE t ()" with or
without the extension, but only with the extension do we audit the inner
CREATE TABLE of "CREATE SCHEMA s CREATE TABLE t ()". A user that creates a
database without creating the extension might look at the audit messages and
mistakenly think the database is all set.
> + /* Return objects affected by the (non drop) DDL statement */
> + query = "SELECT UPPER(object_type), object_identity\n"
> + " FROM pg_event_trigger_ddl_commands()";
This SPI query neglects to schema-qualify its function calls.
> + DefineCustomStringVariable(
> + "pg_audit.log",
> +
> + "Specifies which classes of statements will be logged by
> session audit "
> + "logging. Multiple classes can be provided using a
> comma-separated "
> + "list and classes can be subtracted by prefacing the class with
> a "
> + "- sign.",
> +
> + NULL,
The short_desc is several lines long, while long_desc is NULL.
> --- /dev/null
> +++ b/contrib/pg_audit/sql/pg_audit.sql
I do applaud the breadth of test coverage.
> +-- Set pg_audit parameters for the current (super)user.
> +ALTER ROLE :current_user SET pg_audit.log = 'Role';
> +ALTER ROLE :current_user SET pg_audit.log_level = 'notice';
Do not ALTER the initial login role in a regression test. In the installcheck
case, the role belongs to the test operator; it is not ours to modify.
> +CREATE USER user1;
> +ALTER ROLE user1 SET pg_audit.log = 'ddl, ROLE';
> +ALTER ROLE user1 SET pg_audit.log_level = 'notice';
> +
> +--
> +-- Create, select, drop (select will not be audited)
> +\connect - user1
Adding new CREATE USER statements, as opposed to CREATE ROLE, to regression
tests is almost always wrong. During "make check" on Windows, such users
cannot connect. "REGRESS_OPTS = --create-role=user1" fixes that problem but
does not help installcheck to pass under MD5 authentication (which it did as
of commit c82725e). Skip past these c
Re: [HACKERS] recovery_target_action = pause & hot_standby = off
On Mon, Mar 16, 2015 at 7:39 PM, Andres Freund wrote: > On 2015-03-16 07:52:20 +, Simon Riggs wrote: >> On 15 March 2015 at 22:38, Andres Freund wrote: >> >> > Sorry, I don't buy this. If I have "recovery_target_action = 'pause'" in >> > the config file, I want it to pause. >> >> You want it to enter a state where you cannot perform any action other >> than shutdown? >> >> Why would anyone want that? > > You actually still could promote. But I'd be perfectly happy if postgres > said > ERROR: recovery_target_action = 'pause' in "%s" cannot be used without > hot_standby > DETAIL: Recovery pauses cannot be resumed without SQL level access. > HINT: Configure hot_standby and try again. This works for me. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] recovery_target_action doesn't work for anything but shutdown
On Thu, Mar 12, 2015 at 11:53 PM, Andres Freund wrote: > On 2015-03-12 15:52:02 +0100, Andres Freund wrote: >> I guess what you actually intended to test was StandbyModeRequested? > > Err, EnableHotStandby. Pushed the fix. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issues in Replication Progress Tracking
On Thu, May 21, 2015 at 12:42 AM, Andres Freund wrote: > > On 2015-05-20 19:27:05 +0530, Amit Kapila wrote: > > > 13. > > In function replorigin_session_setup() and or > > replorigin_session_advance(), don't we need to WAL log the > > use of Replication state? > > No, the point is that the replication progress is persisted via an extra > data block in the commit record. That's important for both performance > and correctness, because otherwise it gets hard to tie a transaction > made during replay with the update to the progress. Unless you use 2PC > which isn't really an alternative. > Okay, but what triggered this question was the difference of those functions as compare to when user call function pg_replication_origin_advance(). pg_replication_origin_advance() will WAL log the information during that function call itself (via replorigin_advance()). So even if the transaction issuing pg_replication_origin_advance() function will abort, it will still update the Replication State, why so? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Typo in tablecmds.c
On 2015/05/20 22:59, Heikki Linnakangas wrote: On 05/20/2015 12:40 PM, Etsuro Fujita wrote: The attached patch fixes a typo in a comment in tablecmds.c. Fixed, along with dozens more similar typos I found with some grepping. Thanks for doint that completely! Best regards, Etsuro Fujita -- 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 Wed, May 20, 2015 at 6:12 PM, Andres Freund wrote: > You realize there's other instances of this in the same damn function? I was misled by the argument name, "parsetree" -- in the past, CreateCommandTag() actually only processed raw parse trees. Beyond that, I wasn't aware that it is possible to produce a command tag for every possible representation of an optimizable statement at every stage of query processing. I guess that I'll know next time I add a command tag. -- 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 8:22 PM, Jim Nasby wrote: >> It might be a good idea to do something like this, but it's >> significantly more complicated than a protocol-level SET SESSION >> AUTHORIZATION. Right now, you can never go backwards from an >> authenticated state to an unauthenticated state, and there may be code >> in the backend that relies on that in subtle ways. The initial >> bootstrap sequence is pretty complicated, and I'm pretty sure that any >> naive attempt to redo that stuff is going to have unpleasant, probably >> security-relevant bugs. > > What about the middle-ground of not doing de-auth right now? That eliminates > your concerns but still allows getting rid of ugly things like copies of the > password file (FWIW, my understanding is pgBouncer was meant more to run on > the database server where you'd just point it at the native password file). Uh, I don't have a clue what you mean when you say "the middle ground of not doing de-auth right now". -- 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] Disabling trust/ident authentication configure option
On Wed, May 20, 2015 at 7:09 PM, Michael Banck wrote: >> I think Andres' point about "trust" being an essential disaster recovery >> mode is something to consider, as well. That puts pretty strict limits >> on what would be a credible replacement. > > Then let's rename it from `trust' to `disaster'... ;) I still don't buy it. Say you have a server that connects on its own VLAN every night to run a backup. What's wrong with trust? Would you really be better putting it on a less-secure network and using a password that will just have to be stored in a config file someplace? Answer: No, you wouldn't. -- 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] Change pg_cancel_*() to ignore current backend
Jim Nasby wrote: > BTW, is there a reason we're putting function SQL in that file other than it > was a convenient place? Probably not. I've looked at that file wondering the same thing a number of times ... -- Á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 Wed, May 20, 2015 at 6:22 PM, Tom Lane 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? > 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 don't feel particularly strongly about it one way or the other. The way the command tag reports number of rows affected beside the INSERT tag in psql is relevant. If some of those rows were actually updated, that could mislead. I'm not saying that it outweighs your concern, but it was the reason for inventing a variant tag, and it is a consideration. -- 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] Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
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? Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
Andres Freund 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. Cute ideas are not the same as good ideas. 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-20 15:21:49 -0700, Peter Geoghegan wrote:
> On Wed, May 20, 2015 at 3:14 PM, Peter Geoghegan wrote:
> > I think you're right. The initial commit neglected to update that, and
> > only handled it from ProcessQuery(). So it works for PlannedStmts, not
> > raw parse trees.
>
> Attached patch fixes this. Thanks for the report.
> diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
> index a95eff1..8fd5ee8 100644
> --- a/src/backend/tcop/utility.c
> +++ b/src/backend/tcop/utility.c
> @@ -1898,7 +1898,14 @@ CreateCommandTag(Node *parsetree)
> {
> /* raw plannable queries */
> case T_InsertStmt:
> - tag = "INSERT";
> + {
> + InsertStmt *stmt = (InsertStmt *) parsetree;
> +
> + tag = "INSERT";
> + if (stmt->onConflictClause &&
> + stmt->onConflictClause->action ==
> ONCONFLICT_UPDATE)
> + tag = "UPSERT";
> + }
> break;
>
> case T_DeleteStmt:
You realize there's other instances of this in the same damn function?
--
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] Change pg_cancel_*() to ignore current backend
On 2015-05-20 20:38:51 -0400, Tom Lane wrote: > Jim Nasby writes: > > On 5/20/15 6:56 PM, Andres Freund wrote: > >> On 2015-05-20 18:48:59 -0500, Jim Nasby wrote: > >>> and generally if you want to terminate the connection there's easier > >>> ways to do that then "SELECT pg_terminate_backend(pg_backend_pid())". > > >> Which would be what exactly? Say, you're inside a security definer > >> function. > > > Error isn't good enough so you want to kill the backend? Yep. > > I hadn't considered that; what's the common use case for it? I've seen it basically in two cases: 1) The "role" of the server has changed in some way, and some function wants to force a reconnect. Say a former master that's now a logical replication (in that case IIRC londiste) standby, and a trigger was installed to rediredt existing writers. 2) A function detects that something has has gone rather wrong with a session state and wants to force a reconnect. I've seen this in a "handwritten" RLS implementation. > > ISTM it'd be better > > to allow elog to log and then terminate the backend, but of course that > > doesn't help with backwards compatibility. :/ > > That's spelled elog(FATAL), no? Which is, to my knowledge, inaccessible from at least plpgsql. 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. -- 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] Change pg_cancel_*() to ignore current backend
Jim Nasby writes: > On 5/20/15 6:56 PM, Andres Freund wrote: >> On 2015-05-20 18:48:59 -0500, Jim Nasby wrote: >>> and generally if you want to terminate the connection there's easier >>> ways to do that then "SELECT pg_terminate_backend(pg_backend_pid())". >> Which would be what exactly? Say, you're inside a security definer >> function. > Error isn't good enough so you want to kill the backend? I hadn't > considered that; what's the common use case for it? ISTM it'd be better > to allow elog to log and then terminate the backend, but of course that > doesn't help with backwards compatibility. :/ That's spelled elog(FATAL), 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] Disabling trust/ident authentication configure option
On 5/20/15 7:19 PM, Stephen Frost wrote: * Andres Freund (and...@anarazel.de) wrote: >On 2015-05-20 19:46:12 -0400, Stephen Frost wrote: > >In other words, I agree with you that we can't simply get rid of 'trust' > >without having another solution. I*do* believe that a real single-user > >mode that is only available to the owner of the cluster would go a long > >way towards this goal. > >I think that's a restriction that doesn't make much sense. What if you >want to dump the data as fast as possible to get things up in another >machine/datacenter/whatever after a fault? Uh wait, parallel dump won't >work with single user mode. We're talking about vaporware here at the moment, so I'll just throw out that, perhaps, you could have multiple PG instances in single-user which are all running at the same time in a read-only fashion.:) Actually, having a tool like that would be*really* handy for a lot of uses. In some ways, I believe our lack of such tooling is specifically because we simply don't have as many issues in this area as other databases do. Where is a tool to extract out all the records (with their system columns) from a file based on a provided table definition? With that, you could certainly parallelize pulling all of the data out into flat files. Now that we have shared memory queues, perhaps it wouldn't be that hard to use them as an alternative communication method. OS handles auth for you (and I'd hope this would work in windows too...) -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Change pg_cancel_*() to ignore current backend
On 5/20/15 6:56 PM, Andres Freund wrote: On 2015-05-20 18:48:59 -0500, Jim Nasby wrote: and generally if you want to terminate the connection there's easier ways to do that then "SELECT pg_terminate_backend(pg_backend_pid())". Which would be what exactly? Say, you're inside a security definer function. Error isn't good enough so you want to kill the backend? I hadn't considered that; what's the common use case for it? ISTM it'd be better to allow elog to log and then terminate the backend, but of course that doesn't help with backwards compatibility. :/ What do people think about pg_cancel_query() and pg_terminate_session()? -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On 5/20/15 3:31 PM, Robert Haas wrote: On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera wrote: >Robert Haas wrote: >After mulling over this a bit, I think that if we're to do something to >improve things here we should redesign the protocol so that it considers >poolers explicitely. Right now I think a pooler is pretty limited in >what it can do. If we were to have messages specifically for poolers, >life would be simpler: pooler authenticates to main server, client >authenticates to pooler. The pooler can change auth on the server >connection to whatever the client has, and begin passthrough of protocol >data; when client closes connection, pooler recycles connection and >de-authenticates it with main server so that it can be reused for >another client (re-auth). Client by itself cannot "de-auth" to steal >the connection under somebody else's name. It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. What about the middle-ground of not doing de-auth right now? That eliminates your concerns but still allows getting rid of ugly things like copies of the password file (FWIW, my understanding is pgBouncer was meant more to run on the database server where you'd just point it at the native password file). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Disabling trust/ident authentication configure option
* Andres Freund (and...@anarazel.de) wrote: > On 2015-05-20 19:46:12 -0400, Stephen Frost wrote: > > In other words, I agree with you that we can't simply get rid of 'trust' > > without having another solution. I *do* believe that a real single-user > > mode that is only available to the owner of the cluster would go a long > > way towards this goal. > > I think that's a restriction that doesn't make much sense. What if you > want to dump the data as fast as possible to get things up in another > machine/datacenter/whatever after a fault? Uh wait, parallel dump won't > work with single user mode. We're talking about vaporware here at the moment, so I'll just throw out that, perhaps, you could have multiple PG instances in single-user which are all running at the same time in a read-only fashion. :) Actually, having a tool like that would be *really* handy for a lot of uses. In some ways, I believe our lack of such tooling is specifically because we simply don't have as many issues in this area as other databases do. Where is a tool to extract out all the records (with their system columns) from a file based on a provided table definition? With that, you could certainly parallelize pulling all of the data out into flat files. > This isn't strengthening security. This is making something far too > complicated (pg_hba.conf) into something even more complicated, because > suddenly even the most basic things only work in some environments. If > you want to improve security significantly, make it easier to configure > authentication/authorization. That's one of the hardest parts of > postgres. pg_hba.conf isn't being made any more complicated by removal of an option. I agree that we could certainly improve on it. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Disabling trust/ident authentication configure option
On 2015-05-20 19:46:12 -0400, Stephen Frost wrote: > In other words, I agree with you that we can't simply get rid of 'trust' > without having another solution. I *do* believe that a real single-user > mode that is only available to the owner of the cluster would go a long > way towards this goal. I think that's a restriction that doesn't make much sense. What if you want to dump the data as fast as possible to get things up in another machine/datacenter/whatever after a fault? Uh wait, parallel dump won't work with single user mode. This isn't strengthening security. This is making something far too complicated (pg_hba.conf) into something even more complicated, because suddenly even the most basic things only work in some environments. If you want to improve security significantly, make it easier to configure authentication/authorization. That's one of the hardest parts of postgres. -- 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] anole: assorted stability problems
On 5/20/15 3:09 PM, Tom Lane wrote: Andres Freund writes: On 2015-05-20 16:44:12 -0300, Alvaro Herrera wrote: Andres Freund wrote: Lots? As far as I can tell, this is the only Itanium machine in the buildfarm. ... (It's times like this that I regret not working for Red Hat any more, and having access to all their test hardware ...) I believe pg.us could fund acquisition of older hardware for such purposes, I think it's more a question of hosting and someone to maintain them... -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Change pg_cancel_*() to ignore current backend
On 2015-05-20 18:48:59 -0500, Jim Nasby wrote: > and generally if you want to terminate the connection there's easier > ways to do that then "SELECT pg_terminate_backend(pg_backend_pid())". Which would be what exactly? Say, you're inside a security definer function. -- 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
* Michael Banck (mba...@gmx.net) wrote: > On Wed, May 20, 2015 at 07:03:26PM -0400, Tom Lane wrote: > > I think Andres' point about "trust" being an essential disaster recovery > > mode is something to consider, as well. That puts pretty strict limits > > on what would be a credible replacement. > > Then let's rename it from `trust' to `disaster'... ;) While certainly an amusing idea, I'm afraid that it's simply not worth the complaints we'd get, as we wouldn't have materially improved things. :/ Not only that, but they might mistakenly believe that it's then only available when doing recovery or something along those lines. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Change pg_cancel_*() to ignore current backend
On 5/20/15 11:15 AM, Jon Nelson wrote: On Wed, May 20, 2015 at 9:09 AM, Tom Lane wrote: I think backwards compatibility probably trumps that argument. I have no objection to providing a different call that behaves this way, but changing the behavior of existing applications will face a *much* higher barrier to acceptance. Especially since a real use-case for the current behavior was shown upthread, which means you can't argue that it's simply a bug. regards, tom lane Agree. It breaks backwards compatibility. I use this function a fair bit to terminate the current backend all the time. Could you elaborate on your use case for doing that? Echoing David's comment elsewhere, I suspect non-developers won't have a use for self-termination. I don't see how self-cancel even makes sense, and generally if you want to terminate the connection there's easier ways to do that then "SELECT pg_terminate_backend(pg_backend_pid())". I certainly don't want to cause pain for developers, but is this really that common? BTW, if someone had an awesome idea for a new function name then we could just go that route. I can't think of anything better than pg_*_session. Though, I guess we could do pg_terminate_session and pg_cancel_query, which wouldn't be horrid. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Disabling trust/ident authentication configure option
Andres, * Andres Freund (and...@anarazel.de) wrote: > On 2015-05-20 15:42:23 -0400, Stephen Frost wrote: > > > So the first thing to establish is "other than Volker himself, who are > > > we helping here?" > > > > I don't agree with this either. Providing a "bypass all authentication" > > configuration option really isn't a good thing. Why don't packagers use > > our default pg_hba.conf? Because it only makes sense in a development > > type of environment. I'd argue the same is true for 'trust'. > > Uh. So if the shit hit the fan because you mismanaged a password > rollover, kereberos is down, or something like that, and you can't > access postgres anymore you want to recompile? And no peer isn't an > answer isn't an answer, it's not available on windows. Your only way out > is going to be single user mode. But wait, that's a security hole too. Apologies for not being clearer. I agree that we need an alternative for addressing this use-case before we can consider getting rid of 'trust' or not having it built into the binaries which are distributed. In other words, I agree with you that we can't simply get rid of 'trust' without having another solution. I *do* believe that a real single-user mode that is only available to the owner of the cluster would go a long way towards this goal. If 'trust' was only able to be used by the owner of the database, I'd have much less of an issue with it. > I find the arguments presented in this thread for a configure option > entirely unconvincing. If you'd argued for a saner default > authentication setup: I'd be on board with that. But this seems just a > pointless exercise in making things more complicated. Thankfully, the packagers have already addressed the insecure default that the source build provides for pg_hba.conf and so we don't need to worry about it (except perhaps for new distributions or new packagers, but I hope they'll usually look at the existing packages and not just distribute what we provide as the default pg_hba.conf). Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Change pg_cancel_*() to ignore current backend
On 5/20/15 8:47 AM, Tom Lane wrote: Jim Nasby writes: On 5/19/15 9:19 PM, FabrÃzio de Royes Mello wrote: +1 to add a second parameter to current functions. Instead of allow_own_pid, I went with skip_own_pid. I have the function still returning true even when it skips it's own PID... that seems a bit weird, but I think it's better than returning false. Unless someone thinks it should return NULL, but I don't see that as any better either. The implementation would probably be considerably simpler if you treated these as separate functions at the SQL/C level, ie overload rather than try to treat the added parameter as having a default. AFAICS that's just a minor change to what I'm doing in catalog/system_view.sql and nothing else, so I'm not seeing the win. What am I missing? Now that we have default parameters my preference is to use them if for no other reason than reduce bloat in \df... BTW, is there a reason we're putting function SQL in that file other than it was a convenient place? -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Disabling trust/ident authentication configure option
On Wed, May 20, 2015 at 07:03:26PM -0400, Tom Lane wrote: > Alvaro Herrera writes: > > Michael Banck wrote: > >> The other set of users I could think of are those who, for whatever > >> reason, tend to always compile PostgreSQL from source for their > >> company/organization. Maybe they have internal rules that requires a > >> custom installation prefix for all their servers or whatever. Due to > >> procedural requirements, or just the unwillingness to carry deltas, they > >> absolutely want to use the pristine tarballs as well but would be very > >> happy to get rid of some of the authentication methods. > > > Right. That's the set of users that Josh B says is only comprised of > > Volker (the OP). > > That might be a bit harsh, but here's the thing: assuming you're willing > to build from source, what is the reason for wanting $small_market_feature > to be built into Postgres rather than being something you carry a patch > for? ISTM the core reason is that you're expecting the community to carry > the load of testing and maintaining the feature. And the fact of the > matter is that we're not terribly good at testing non-mainstream build > options. (There is depressingly little variety in the configure options > used in the buildfarm, for example.) So I wouldn't be a bit surprised > if something like this broke every time somebody touched the auth code, > and we would not notice. It would only be reliable if it were something > the community tended to use regularly ... which gets us back to the point > that what needs to happen first is a credible replacement for "trust" > mode. Fair enough. > I think Andres' point about "trust" being an essential disaster recovery > mode is something to consider, as well. That puts pretty strict limits > on what would be a credible replacement. Then let's rename it from `trust' to `disaster'... ;) Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disabling trust/ident authentication configure option
Alvaro Herrera writes: > Michael Banck wrote: >> The other set of users I could think of are those who, for whatever >> reason, tend to always compile PostgreSQL from source for their >> company/organization. Maybe they have internal rules that requires a >> custom installation prefix for all their servers or whatever. Due to >> procedural requirements, or just the unwillingness to carry deltas, they >> absolutely want to use the pristine tarballs as well but would be very >> happy to get rid of some of the authentication methods. > Right. That's the set of users that Josh B says is only comprised of > Volker (the OP). That might be a bit harsh, but here's the thing: assuming you're willing to build from source, what is the reason for wanting $small_market_feature to be built into Postgres rather than being something you carry a patch for? ISTM the core reason is that you're expecting the community to carry the load of testing and maintaining the feature. And the fact of the matter is that we're not terribly good at testing non-mainstream build options. (There is depressingly little variety in the configure options used in the buildfarm, for example.) So I wouldn't be a bit surprised if something like this broke every time somebody touched the auth code, and we would not notice. It would only be reliable if it were something the community tended to use regularly ... which gets us back to the point that what needs to happen first is a credible replacement for "trust" mode. I think Andres' point about "trust" being an essential disaster recovery mode is something to consider, as well. That puts pretty strict limits on what would be a credible replacement. 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] Disabling trust/ident authentication configure option
Michael Banck wrote: > On Wed, May 20, 2015 at 02:10:30PM -0400, Tom Lane wrote: > > One reason why it would not be, if it's a build-time decision, > > is that it's quite unlikely that any popular packagers would build > > that way. So this would only be applicable to custom-built binaries, > > which is a pretty small class of users to begin with. > > There might be appliance vendors who ship PostgreSQL along with their > product. Then, they decide they want to use the pristine tarballs for > reproducibility and accountability. If done right, they could publish > their set of configure options and a build-id or whatever, and 3rd > parties could verify the binaries they ship have not been tampered > with[1]. Granted, they could also just publish the patch for those 3rd > parties to apply as well, but that sounds slightly inelegant. I don't think you can mix "elegance" and "appliance vendor" in the same sentence with a straight face, so while I agree that in theory this might be true, in reality this functionality would seldom be used for this. > The other set of users I could think of are those who, for whatever > reason, tend to always compile PostgreSQL from source for their > company/organization. Maybe they have internal rules that requires a > custom installation prefix for all their servers or whatever. Due to > procedural requirements, or just the unwillingness to carry deltas, they > absolutely want to use the pristine tarballs as well but would be very > happy to get rid of some of the authentication methods. Right. That's the set of users that Josh B says is only comprised of Volker (the OP). -- Á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 Wed, May 20, 2015 at 3:14 PM, Peter Geoghegan wrote:
> I think you're right. The initial commit neglected to update that, and
> only handled it from ProcessQuery(). So it works for PlannedStmts, not
> raw parse trees.
Attached patch fixes this. Thanks for the report.
--
Peter Geoghegan
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index a95eff1..8fd5ee8 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1898,7 +1898,14 @@ CreateCommandTag(Node *parsetree)
{
/* raw plannable queries */
case T_InsertStmt:
- tag = "INSERT";
+ {
+InsertStmt *stmt = (InsertStmt *) parsetree;
+
+tag = "INSERT";
+if (stmt->onConflictClause &&
+ stmt->onConflictClause->action == ONCONFLICT_UPDATE)
+ tag = "UPSERT";
+ }
break;
case T_DeleteStmt:
--
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 02:10:30PM -0400, Tom Lane wrote: > One reason why it would not be, if it's a build-time decision, > is that it's quite unlikely that any popular packagers would build > that way. So this would only be applicable to custom-built binaries, > which is a pretty small class of users to begin with. There might be appliance vendors who ship PostgreSQL along with their product. Then, they decide they want to use the pristine tarballs for reproducibility and accountability. If done right, they could publish their set of configure options and a build-id or whatever, and 3rd parties could verify the binaries they ship have not been tampered with[1]. Granted, they could also just publish the patch for those 3rd parties to apply as well, but that sounds slightly inelegant. The other set of users I could think of are those who, for whatever reason, tend to always compile PostgreSQL from source for their company/organization. Maybe they have internal rules that requires a custom installation prefix for all their servers or whatever. Due to procedural requirements, or just the unwillingness to carry deltas, they absolutely want to use the pristine tarballs as well but would be very happy to get rid of some of the authentication methods. That said, I agree that both examples are rather contrived and this is more of an advocatus diaboli kind of reply. Michael [1] seems like PostgreSQL is in the set of packages which successfully build reproducibly according to the Debian reproducible builds effort, yay -- 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 Wed, May 20, 2015 at 3:09 PM, Alvaro Herrera wrote: >> Are you using an old psql? I thought that that would just result in no >> command tag being displayed. > > Well, I'm using an editor to read the code of CreateCommandTag(), not > executing anything. I guess that function needs an update, then, no? I think you're right. The initial commit neglected to update that, and only handled it from ProcessQuery(). So it works for PlannedStmts, not raw parse trees. -- 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] Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
Peter Geoghegan wrote: > On Wed, May 20, 2015 at 2:58 PM, Alvaro Herrera > wrote: > > Hm, I just realized that the command tag for INSERT ON CONFLICT is still > > just INSERT. Is that okay? To me, the behavior is different enough > > that it should have its own tag. I'm not too set on this, but maybe > > others share this opinion? > > No it isn't. ON CONFLICT DO UPDATE has an "UPSERT" command tag. DO > NOTHING has INSERT as its command tag. ... ah ... > Are you using an old psql? I thought that that would just result in no > command tag being displayed. Well, I'm using an editor to read the code of CreateCommandTag(), not executing anything. I guess that function needs an update, then, no? -- Á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 Wed, May 20, 2015 at 2:58 PM, Alvaro Herrera wrote: > Hm, I just realized that the command tag for INSERT ON CONFLICT is still > just INSERT. Is that okay? To me, the behavior is different enough > that it should have its own tag. I'm not too set on this, but maybe > others share this opinion? No it isn't. ON CONFLICT DO UPDATE has an "UPSERT" command tag. DO NOTHING has INSERT as its command tag. Are you using an old psql? I thought that that would just result in no command tag being displayed. -- 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] Disabling trust/ident authentication configure option
Andres Freund wrote: > On 2015-05-20 15:42:23 -0400, Stephen Frost wrote: > > > So the first thing to establish is "other than Volker himself, who are > > > we helping here?" > > > > I don't agree with this either. Providing a "bypass all authentication" > > configuration option really isn't a good thing. Why don't packagers use > > our default pg_hba.conf? Because it only makes sense in a development > > type of environment. I'd argue the same is true for 'trust'. > > Uh. So if the shit hit the fan because you mismanaged a password > rollover, kereberos is down, or something like that, and you can't > access postgres anymore you want to recompile? Yeah, it's pretty messy. I thought about providing the feature without involving configure: say have a file listing allowed auth methods; in Volker's case it's easy to ship packages were trust/peer are removed in the file. If you're in deep trouble just enable trust there and fix it. But this has the exact problem we started with: the lazy admin will just enable it "momentarily" to get the thing running and forget to turn it off later. I was thinking that another use for this would be cases where an auth method is found to be insecure and you want to disable it completely (say Kerberos is cracked irreparably or whatever). But the real solution to that problem is to remove it from pg_hba.conf. In the end, it seems there is no actual hole being solved here that cannot be better attacked by other means. -- Á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
[HACKERS] Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
On 2015-05-20 18:58:16 -0300, Alvaro Herrera wrote: > Andres Freund wrote: > > Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. > > Hm, I just realized that the command tag for INSERT ON CONFLICT is still > just INSERT. Is that okay? To me, the behavior is different enough > that it should have its own tag. I'm not too set on this, but maybe > others share this opinion? It's actually INSERT for DO NOTHING, and UPSERT for DO UPDATE. It's even documented ;) Andres -- 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: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
Andres Freund wrote: > Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. Hm, I just realized that the command tag for INSERT ON CONFLICT is still just INSERT. Is that okay? To me, the behavior is different enough that it should have its own tag. I'm not too set on this, but maybe others share this opinion? -- Á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] Disabling trust/ident authentication configure option
On 2015-05-20 15:42:23 -0400, Stephen Frost wrote: > > So the first thing to establish is "other than Volker himself, who are > > we helping here?" > > I don't agree with this either. Providing a "bypass all authentication" > configuration option really isn't a good thing. Why don't packagers use > our default pg_hba.conf? Because it only makes sense in a development > type of environment. I'd argue the same is true for 'trust'. Uh. So if the shit hit the fan because you mismanaged a password rollover, kereberos is down, or something like that, and you can't access postgres anymore you want to recompile? And no peer isn't an answer isn't an answer, it's not available on windows. Your only way out is going to be single user mode. But wait, that's a security hole too. I find the arguments presented in this thread for a configure option entirely unconvincing. If you'd argued for a saner default authentication setup: I'd be on board with that. But this seems just a pointless exercise in making things more complicated. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disabling trust/ident authentication configure option
Stephen Frost writes: > I don't agree with this either. Providing a "bypass all authentication" > configuration option really isn't a good thing. Why don't packagers use > our default pg_hba.conf? Because it only makes sense in a development > type of environment. I'd argue the same is true for 'trust'. Sure. And the problem is that development environments are a perfectly common and respectable use-case. I cannot see Red Hat, for example, shipping a Postgres that's built (not merely configured by user-changeable config files, but hard-wired) to be unfriendly to developers. If we could get to a point where there is another way that is superior to "trust" even for single-user development environments, then maybe it would be useful to try to persuade packagers to disable "trust". But I don't even see a proposal for such a thing, let alone a track record showing that nobody needs "trust". And you really have got to get to the point of being able to argue that *nobody* needs trust, not that some use-cases don't need it, before you will impress most packagers. 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] ERROR: cannot GetMultiXactIdMembers() during recovery
Alvaro Herrera writes: > Marko Tiikkaja wrote: >> Any chance to get this fixed in time for 9.1.16? > I hope you had pinged some days earlier. Here's a patch, but I will > wait until this week's releases have been tagged before pushing. BTW, I meant to update this thread but forgot until now: these changes did wind up included in the final tarballs for 9.2 and before, on account of the re-wrap the next day. In the rush to re-do the wrap, I forgot that I should've added entries to the release notes for these commits :-( So the documentation doesn't mention the fix, but it's there. 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] Disabling trust/ident authentication configure option
Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I don't agree with this either. Providing a "bypass all authentication" > > configuration option really isn't a good thing. Why don't packagers use > > our default pg_hba.conf? Because it only makes sense in a development > > type of environment. I'd argue the same is true for 'trust'. > > Sure. And the problem is that development environments are a perfectly > common and respectable use-case. Apologies for the confusion- the 'development type of environment' I was referring to above is the environment where our default from-source pg_hba.conf is installed: when doing *PostgreSQL* development. I don't see the use-case for using 'trust' when doing application development with PG as a database. I've certainly not used it before and when I've found it in places that I've gone, after explaining how it actually works, everyone I've worked with has either changed it or made plans to do so. > If we could get to a point where there is another way that is superior > to "trust" even for single-user development environments, then maybe > it would be useful to try to persuade packagers to disable "trust". I agree with this, as mentioned up-thread. Having a way to support single-user development / running of PG would really need to exist before we could encourage getting rid of 'trust'. > But I don't even see a proposal for such a thing, let alone a track record > showing that nobody needs "trust". And you really have got to get to the > point of being able to argue that *nobody* needs trust, not that some > use-cases don't need it, before you will impress most packagers. I don't see a real proposal for it either; I was simply trying to outline a path forward, one which I would agree with, rather than simply saying "no, never." Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera wrote: > Robert Haas wrote: >> On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja wrote: >> > Now that we're on the topic of interesting things, would it make sense to >> > add protocol support for a sort of a "re-authenticate"? So a pooler could >> > first say "this user wants to log in from this host", then get back a >> > message saying how to authenticate that user, which the pooler could then >> > pass that on to the client. >> >> I don't think this will work, because the authentication dialogue is >> structured a series of challenges and responses. > > After mulling over this a bit, I think that if we're to do something to > improve things here we should redesign the protocol so that it considers > poolers explicitely. Right now I think a pooler is pretty limited in > what it can do. If we were to have messages specifically for poolers, > life would be simpler: pooler authenticates to main server, client > authenticates to pooler. The pooler can change auth on the server > connection to whatever the client has, and begin passthrough of protocol > data; when client closes connection, pooler recycles connection and > de-authenticates it with main server so that it can be reused for > another client (re-auth). Client by itself cannot "de-auth" to steal > the connection under somebody else's name. It might be a good idea to do something like this, but it's significantly more complicated than a protocol-level SET SESSION AUTHORIZATION. Right now, you can never go backwards from an authenticated state to an unauthenticated state, and there may be code in the backend that relies on that in subtle ways. The initial bootstrap sequence is pretty complicated, and I'm pretty sure that any naive attempt to redo that stuff is going to have unpleasant, probably security-relevant bugs. (In the current architecture, you also can't rebind to a new database; I'm not sure if your proposal would change things from that side, but if so, that adds a further level of complexity.) I would urge, rather strongly, that we keep the first version of this simple: let the pooler, via a protocol message, set the session authorization in a fashion that prevents it from being changed back except by another protocol message. If we want to do something like this after that, fine, but letting the pooler switch the authorization in a non-subvertable way is a whole lot simpler than what you are talking about. > There's an issue that in order to authenticate a client, the pooler > needs to have info from the server about auth data. Last I checked > pgbouncer, you had to copy a list of username/passwords from the server > to a pgbouncer config file, which is ugly and dangerous (not to mention > tedious and error-prone). We could fix that sort of thing too, if we > were to design something here with poolers in mind. I think this is fundamentally backwards. If the client is going to authenticate directly to the pooler, then the pooler should be the master source of the authentication information, and pooler should just log into the server as superuser. If you instead do what you're proposing and teach the server to send its authentication secrets to the pooler, you risk somebody evil using the same feature to extract those secrets for malicious purposes (think: DBA who is about to be fired). You're basically adding a server "feature" whereby it facilitates MITM attacks against itself. -- 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] Problems with question marks in operators (JDBC, ECPG, ...)
Andrew Dunstan wrote: > On 05/20/2015 03:34 PM, Tom Lane wrote: >> The operator for tintervals can be traced back at least to >> Postgres v4r2 (1994), which is the oldest tarball I have at >> hand. Most of the current list are geometric operators that >> were added by Tom Lockhart in 1997. > When did the SQL standard add any mention of ? FWIW, the first public, production release of Java in 1995 used it for parameters. ODBC 1.0 was released in 1992. I would guess that the question mark for parameters was there from the beginning, but can't swear to it before 1995. -- Kevin Grittner EDB: 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] anole: assorted stability problems
Andres Freund writes: > On 2015-05-20 16:44:12 -0300, Alvaro Herrera wrote: >> Andres Freund wrote: >>> Hm. Anole hasn't reported reliably for a while before these. It's quite >>> possible that this is a ac++ portability problem around the >>> atomics. There's lots of other IA64 animals not having problems, but >>> they're not using ac++. >> Lots? As far as I can tell, this is the only Itanium machine in the >> buildfarm. > Uh. I'm pretty sure there were some back when that patch went in. And > there definitely used to be a couple earlier. I guess itanium really is > dying (mixed bad: It's a horrible architecture, but more coverage would > still be good). Since that machine is run by EDB, maybe we could persuade them to set up a second critter on it that uses gcc. That would at least help narrow down whether it's a compiler-specific issue. (It's times like this that I regret not working for Red Hat any more, and having access to all their test hardware ...) 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, ...)
Andrew Dunstan writes: > When did the SQL standard add any mention of ? It's in SQL92. I don't have a copy of SQL89, or whatever the previous spec was, to look at. (So you could argue that Yu and Chen should've removed ? from the set of allowed operator characters when they grafted SQL syntax onto Postgres. But they didn't ...) 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, ...)
On 05/20/2015 03:34 PM, Tom Lane wrote: Dave Cramer writes: Notably absent from the discussion is ODBC upon which JDBC was modelled and probably predates any use of ? as an operator It would be a mistake to imagine that operators containing '?' are some johnny-come-lately. The operator for tintervals can be traced back at least to Postgres v4r2 (1994), which is the oldest tarball I have at hand. Most of the current list are geometric operators that were added by Tom Lockhart in 1997. The only ones that aren't old enough to vote are the JSONB ones we added last year. Not that the problem's not real, but these operators predate any attempt to make Postgres work with ODBC or JDBC or any other connector. Otherwise we might've thought better of using '?'. Yeah, I knew they were pretty old. When did the SQL standard add any mention of ? 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] anole: assorted stability problems
On 2015-05-20 16:44:12 -0300, Alvaro Herrera wrote: > Andres Freund wrote: > > Hm. Anole hasn't reported reliably for a while before these. It's quite > > possible that this is a ac++ portability problem around the > > atomics. There's lots of other IA64 animals not having problems, but > > they're not using ac++. > > Lots? As far as I can tell, this is the only Itanium machine in the > buildfarm. Uh. I'm pretty sure there were some back when that patch went in. And there definitely used to be a couple earlier. I guess itanium really is dying (mixed bad: It's a horrible architecture, but more coverage would still be good). -- 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] anole: assorted stability problems
Andres Freund wrote: > On 2015-05-20 16:21:57 -0300, Alvaro Herrera wrote: > > In HEAD only. Previous branches seem mostly clean, so there's something > > going wrong. Spinlocks going wrong perhaps? > > > > http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=anole&dt=2015-05-20%2016%3A30%3A26&stg=check > > ! PANIC: stuck spinlock (cd6f4140) detected at lwlock.c:816 > > ! server closed the connection unexpectedly > > ! This probably means the server terminated abnormally > > ! before or while processing the request. > > ! connection to server was lost > > Hm. Anole hasn't reported reliably for a while before these. It's quite > possible that this is a ac++ portability problem around the > atomics. There's lots of other IA64 animals not having problems, but > they're not using ac++. Lots? As far as I can tell, this is the only Itanium machine in the buildfarm. -- Á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] jsonb concatenate operator's semantics seem questionable
On 05/20/2015 03:37 PM, Tom Lane wrote: Josh Berkus writes: That does cover all bases, and users would be able to create the operator which suits their particular use case easily. It's also fairly similar to how jsquery works, although the syntax is completely different. But ... it's after feature freeze. So, thoughts? I think this could be seen as a correction/bug fix for a pre-freeze feature. We should not be too resistant to filing down rough edges on new features, even if that involves a spec change. OK, I'll run with that. The rest is largely bikeshedding, really. 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] jsonb concatenate operator's semantics seem questionable
On 2015-05-20 15:37:15 -0400, Tom Lane wrote: > Josh Berkus writes: > > That does cover all bases, and users would be able to create the > > operator which suits their particular use case easily. It's also fairly > > similar to how jsquery works, although the syntax is completely different. > > > But ... it's after feature freeze. So, thoughts? > > I think this could be seen as a correction/bug fix for a pre-freeze > feature. We should not be too resistant to filing down rough edges > on new features, even if that involves a spec change. +1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Non-user-resettable SET SESSION AUTHORISATION
Robert Haas wrote: > On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja wrote: > > Now that we're on the topic of interesting things, would it make sense to > > add protocol support for a sort of a "re-authenticate"? So a pooler could > > first say "this user wants to log in from this host", then get back a > > message saying how to authenticate that user, which the pooler could then > > pass that on to the client. > > I don't think this will work, because the authentication dialogue is > structured a series of challenges and responses. After mulling over this a bit, I think that if we're to do something to improve things here we should redesign the protocol so that it considers poolers explicitely. Right now I think a pooler is pretty limited in what it can do. If we were to have messages specifically for poolers, life would be simpler: pooler authenticates to main server, client authenticates to pooler. The pooler can change auth on the server connection to whatever the client has, and begin passthrough of protocol data; when client closes connection, pooler recycles connection and de-authenticates it with main server so that it can be reused for another client (re-auth). Client by itself cannot "de-auth" to steal the connection under somebody else's name. There's an issue that in order to authenticate a client, the pooler needs to have info from the server about auth data. Last I checked pgbouncer, you had to copy a list of username/passwords from the server to a pgbouncer config file, which is ugly and dangerous (not to mention tedious and error-prone). We could fix that sort of thing too, if we were to design something here with poolers in mind. -- Á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] Disabling trust/ident authentication configure option
* Josh Berkus (j...@agliodbs.com) wrote: > On 05/20/2015 11:10 AM, Tom Lane wrote: > > Alvaro Herrera writes: > >> The proposal here is to have a configure argument that disables > >> arbitrary auth mechanisms. How is that specific to a particular > >> environment? > > > > I think Josh's question is whether the feature is actually useful to > > a large class of users. > > > > One reason why it would not be, if it's a build-time decision, > > is that it's quite unlikely that any popular packagers would build > > that way. So this would only be applicable to custom-built binaries, > > which is a pretty small class of users to begin with. > > Precisely. I do not agree with the 'quite unlikely' characterization. We would need to make a case to them as to why there are better options than having 'trust' be supported, but that only makes sense once there *are*. We know there are reasons we need it today and there's not much point having this discussion until those have been addressed. > So the first thing to establish is "other than Volker himself, who are > we helping here?" I don't agree with this either. Providing a "bypass all authentication" configuration option really isn't a good thing. Why don't packagers use our default pg_hba.conf? Because it only makes sense in a development type of environment. I'd argue the same is true for 'trust'. > The second major issue I have is that it's an anti-security feature. > That is, it ignores the several ways in which someone with superuser > access can bypass the lack of an auth mechanism, while giving anyone who > installs the option a false sense of safety. Ineffective security > measures lead to worse security holes than being aware that you're at risk. I don't believe this is correct. Your definition of an anti-security feature is fine, but I don't agree with the assumption that people will feel secure because 'trust' isn't compiled in. Most users are unlikely to notice it's gone and the argument you're making here is that the users who will be upset that it's no longer available are all knowledgable enough to realize when it's valid to use. I seriously doubt that's the case and throwing a big warning in front of them saying "trust is no longer built-in because it's a big glaring security hole, please use another mechanism" would hopefully get them to understand why it was an issue, why it shouldn't be used, and what options are available and what their tradeoffs are. I still don't believe there's much point in the discussion until there are viable alternatives for the use-cases where we really need to be able to just get into PG and get data out quickly in the face of corruption, but I wanted to share my disagreement regarding the assumption that packagers would never consider disabling 'trust'. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] anole: assorted stability problems
On 2015-05-20 16:21:57 -0300, Alvaro Herrera wrote: > In HEAD only. Previous branches seem mostly clean, so there's something > going wrong. Spinlocks going wrong perhaps? > > http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=anole&dt=2015-05-20%2016%3A30%3A26&stg=check > ! PANIC: stuck spinlock (cd6f4140) detected at lwlock.c:816 > ! server closed the connection unexpectedly > ! This probably means the server terminated abnormally > ! before or while processing the request. > ! connection to server was lost Hm. Anole hasn't reported reliably for a while before these. It's quite possible that this is a ac++ portability problem around the atomics. There's lots of other IA64 animals not having problems, but they're not using ac++. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb concatenate operator's semantics seem questionable
Josh Berkus writes: > That does cover all bases, and users would be able to create the > operator which suits their particular use case easily. It's also fairly > similar to how jsquery works, although the syntax is completely different. > But ... it's after feature freeze. So, thoughts? I think this could be seen as a correction/bug fix for a pre-freeze feature. We should not be too resistant to filing down rough edges on new features, even if that involves a spec change. 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, ...)
Dave Cramer writes: > Notably absent from the discussion is ODBC upon which JDBC was modelled and > probably predates any use of ? as an operator It would be a mistake to imagine that operators containing '?' are some johnny-come-lately. The operator for tintervals can be traced back at least to Postgres v4r2 (1994), which is the oldest tarball I have at hand. Most of the current list are geometric operators that were added by Tom Lockhart in 1997. The only ones that aren't old enough to vote are the JSONB ones we added last year. Not that the problem's not real, but these operators predate any attempt to make Postgres work with ODBC or JDBC or any other connector. Otherwise we might've thought better of using '?'. 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] RFC: Non-user-resettable SET SESSION AUTHORISATION
On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja wrote: > On 5/20/15 5:21 PM, Robert Haas wrote: >> On Tue, May 19, 2015 at 5:02 PM, Simon Riggs >> wrote: >>> That's a reasonable argument. So +1 to protocol from me. >>> >>> To satisfy Tom, I think this would need to have two modes: one where the >>> session can never be reset, for ultra security, and one where the session >>> can be reset, which allows security and speed of pooling. >> >> I think the the second one is a lot more interesting, but I don't have >> a problem with having the first one, too, if somebody wants it. We >> can use one protocol message for both, with a 1-byte character field >> used to indicate which mode the client is requesting. > > Now that we're on the topic of interesting things, would it make sense to > add protocol support for a sort of a "re-authenticate"? So a pooler could > first say "this user wants to log in from this host", then get back a > message saying how to authenticate that user, which the pooler could then > pass that on to the client. Once the client has passed its credentials, the > pooler could (possibly in another backend) try to authenticate using those > credentials, and only then set the session's authentication. This would > allow for more transparent poolers while still, well, pooling connections. > > I haven't thought about this at all, so maybe it's a stupid idea (or the > backends don't have all the information to do this), or whatever. I don't think this will work, because the authentication dialogue is structured a series of challenges and responses. For many authentication methods, these are replay-resistant by design; if you could watch a Kerberos authentication sequence and then, based on having seen it, conduct an authentication dialog with somebody else successfully, that would be a very serious security flaw; it would amount to being able to steal the secret key by observing one authentication dialog. Even md5 authentication is intended to be replay-resistant, by using a different salt each time. Sure, the pooler COULD reuse the same salt over and over and just look for a matching response, but then md5 authentication via the pooler becomes much less secure than md5 authentication that goes directly to the server. That's bad. I suspect you're asking about this because you are concerned about the problem of authentication to the pooler being awkward and maybe insecure. I suspect that the only real solution to that problem is going to be to put the pooler into the database server itself, so that you just have one piece of software. That doesn't mean we shouldn't look for other methods of improving things between now and then, but I think it's going to be a hard problem to solve. -- 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] anole: assorted stability problems
In HEAD only. Previous branches seem mostly clean, so there's something going wrong. Spinlocks going wrong perhaps? http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=anole&dt=2015-05-20%2016%3A30%3A26&stg=check ! PANIC: stuck spinlock (cd6f4140) detected at lwlock.c:816 ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=anole&dt=2015-05-09%2020%3A30%3A29 ! PANIC: semop(id=0) failed: Result too large ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=anole&dt=2015-05-05%2018%3A39%3A38&stg=check ! FATAL: semop(id=0) failed: File too large ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=anole&dt=2015-05-03%2012%3A30%3A18 ! PANIC: semop(id=-1073741824) failed: Invalid argument ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=anole&dt=2015-04-29%2004%3A30%3A25 ! PANIC: stuck spinlock (cd335360) detected at lwlock.c:767 ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost -- Á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] jsonb concatenate operator's semantics seem questionable
It is like bugfix than new feature Dne 20.5.2015 21:08 napsal uživatel "Josh Berkus" : > On 05/20/2015 11:34 AM, Andrew Dunstan wrote: > > So Dmitry, at my suggestion, has come up with a way of doing that, by > > adding a parameter to jsonb_replace(). If this parameter is set to true > > (it defaults to false) and the key or array element pointed to by the > > last element of the path doesn't exist, it gets created. > > That does cover all bases, and users would be able to create the > operator which suits their particular use case easily. It's also fairly > similar to how jsquery works, although the syntax is completely different. > > But ... it's after feature freeze. So, thoughts? > > -- > Josh Berkus > PostgreSQL Experts Inc. > http://pgexperts.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] GROUPING
> "Tom" == Tom Lane writes: >> 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). Tom> Maybe INT8 would be a better choice than INT4? But I'm not sure Tom> there's any practical use-case for more than 30 grouping sets Tom> anyway. Keep in mind the actual output volume probably grows like Tom> 2^N. Spec says "The declared type of the result is exact numeric with an implementation-defined precision and a scale of 0 (zero)." for what that's worth. It doesn't give any hint that I can see for the max number of columns; it just defines grouping(a...,z) as being equal to 2*grouping(a...) + grouping(z). But the number of grouping sets isn't really relevant here, rather the number of columns used for grouping. In any case, if 31 isn't enough for you, you can call it multiple times: select ..., grouping(a,b,...,z), grouping(a1,b1,...z1), ... I didn't think >31 columns would be an issue, but changing it to bigint is of course trivial if anyone thinks it necessary. A possibly more interesting question is whether any other db products have operations like GROUPING() that we could usefully support? -- 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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On Wed, May 20, 2015 at 11:26 AM, Andres Freund wrote: > Even if maybe not directly under the guise of exclusion constraints > themselves, but I do think it's an interesting way to more easily allow > to implement unique constraints on !amcanunique type indexes. Or, more > interestingly, for unique keys spanning partitions Alright, then. It's just that at one point people seemed to think that upsert should support exclusion constraints, and that position was, at the time, lacking a good justification IMV. What you talk about here seems much more practical than generalizing upsert to work with exclusion constraints. You're talking about exclusion constraints as an implementation detail of something interesting, which I had not considered. -- 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] Issues in Replication Progress Tracking
Hi,
Thanks for looking through this!
On 2015-05-20 19:27:05 +0530, Amit Kapila wrote:
> 5.
> origin.c
>
> * * To create and drop replication origins an exclusive lock on
> * pg_replication_slot is required for the
> duration. That allows us to
> * safely and conflict free assign new origins using a dirty snapshot.
> b. "..safely and conflict free assign..", I understand this part
> of comment, but not sure if this is the best way to write it.
Hm, don't see a problem with that part.
> 8.
> origin.c
> replorigin_drop()
> {
> ..
> tuple = SearchSysCache1(REPLORIGIDENT, ObjectIdGetDatum(roident));
> simple_heap_delete(rel, &tuple-
> >t_self);
> ..
> }
>
> Isn't it better to have check for a valid tuple after SearchSysCache1()?
> if (!HeapTupleIsValid(tuple))
> elog(ERROR, "cache lookup failed
Sounds good to me.
> 9.
> origin.c
> ReplicationOriginShmemSize(void)
> {
> ..
> * XXX: max_replication_slots is arguablethe wrong
> ..
> }
> b. One thing that in favour of using a separate/new guc for
>ReplicationState is that even if the user has configured
>max_replication_slots for some other usage (other than
>tracking Replication Origin) of ReplicationSlots, even then we
>will end up allocating shared memory which will never be used,
>OTOH as the memory will not be huge, so we can even ignore it.
I don't think it matters much for now, as you say it's only a small
amount of memory.
> 12.
> In funcions replorigin_advance() and replorigin_session_setup(),
> different ways (free_state and free_slot) are used. Isn't it better
> to use same way?
Phew, I don't really care.
> 13.
> In function replorigin_session_setup() and or
> replorigin_session_advance(), don't we need to WAL log the
> use of Replication state?
No, the point is that the replication progress is persisted via an extra
data block in the commit record. That's important for both performance
and correctness, because otherwise it gets hard to tie a transaction
made during replay with the update to the progress. Unless you use 2PC
which isn't really an alternative.
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Disabling trust/ident authentication configure option
On 05/20/2015 11:10 AM, Tom Lane wrote: > Alvaro Herrera writes: >> Josh Berkus wrote: >>> As such, proposals are more likely to be successful if the proposer can >>> show how they apply to a general use case, or adapt them so that they >>> are useful to a large number of our users. This means that "this works >>> in our environment which has conditions X, Y, and Z" is not an effective >>> argument, unless you can follow it up with "... and here's the reason >>> why [large class of users] also has conditions X, Y and Z." > >> The proposal here is to have a configure argument that disables >> arbitrary auth mechanisms. How is that specific to a particular >> environment? > > I think Josh's question is whether the feature is actually useful to > a large class of users. > > One reason why it would not be, if it's a build-time decision, > is that it's quite unlikely that any popular packagers would build > that way. So this would only be applicable to custom-built binaries, > which is a pretty small class of users to begin with. Precisely. My second point is that it's not useful for the reason Volker says it is; that is, it simply doesn't protect against "lazy admin mistakes", *and* there are already other mechanisms in the world of IT which do a better job of this (primarily, centralized configuration management). I am aware of a large group of users who are concerned with lock-down security and do custom builds: the makers of security appliances, many or most of whom use PostgreSQL as a database. However, those vendors also lock down access to pg_hba.conf and postgresql.conf, so a compile-time option is of, at best, marginal use to them. Stretching my imagination over the different types of PostgreSQL users I know, I can't actually think of any who, as a group, would make use of this feature. So the first thing to establish is "other than Volker himself, who are we helping here?" The second major issue I have is that it's an anti-security feature. That is, it ignores the several ways in which someone with superuser access can bypass the lack of an auth mechanism, while giving anyone who installs the option a false sense of safety. Ineffective security measures lead to worse security holes than being aware that you're at risk. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 2015-05-20 12:07:56 -0700, Peter Geoghegan wrote: > You're talking about exclusion constraints as an implementation detail > of something interesting, which I had not considered. I did mention those two usecases a bunch of times... ;) -- 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] jsonb concatenate operator's semantics seem questionable
On 05/20/2015 11:34 AM, Andrew Dunstan wrote: > So Dmitry, at my suggestion, has come up with a way of doing that, by > adding a parameter to jsonb_replace(). If this parameter is set to true > (it defaults to false) and the key or array element pointed to by the > last element of the path doesn't exist, it gets created. That does cover all bases, and users would be able to create the operator which suits their particular use case easily. It's also fairly similar to how jsquery works, although the syntax is completely different. But ... it's after feature freeze. So, thoughts? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Problems with question marks in operators (JDBC, ECPG, ...)
Dave Cramer writes: > Back to the issue at hand. Does anyone have a recommendation for a > replacement operator besides ? The bikeshedding potential here might be the worst part of the whole thing. Still, if we can agree on reasonable substitute names, I wouldn't be against it, even with the huge lead time in mind. > When I first noticed this one thought was to create duplicate operators > specifically for the use of the JDBC driver. > I had dismissed this at the time, now I'm not so sure If you mean fixing the problem with an extension that adds replacement operators without any core code changes, I'm afraid probably not. It would work okay for operators that are not indexable, but not for those that can be indexed. (I think only a couple of the existing problem operators are indexable, but that's enough to make the idea not fly.) The difficulty with indexable operators is that there is no provision for multiple operators sharing the same strategy "slot" in an opclass. So the only way to add additional operators to an opclass is to give them new strategy numbers, which requires teaching the opclass' support functions to know about those numbers. This would be just a minor change (add some case labels) but it *is* a change in the core code. 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, ...)
On Wed, May 20, 2015 at 5:46 PM, Robert Haas wrote: > I think we should be more focused on this part of the issue. It seems > to me that it's a good idea for connectors to have an escaping > mechanism. Pretty much any syntax that supports funny characters that > do magical things should also have a way to turn the magic off when > it's not wanted. > I certainly don't disagree it's a good idea for connectors to have an escaping mechanism, but the problem here is that there's a blurred line regarding whose magic it is. It would make sense for connectors to allow for their magic to be escaped, but it turns out that the magic they do is a very close match to what seems to be in the SQL spec under the Dynamic SQL section. It could be argued that ? should be always escaped anyway, even in a direct SQL query, simply not to make it conflict with Dynamic SQL, but there doesn't seem to be such a mechanism in the SQL spec as far as I can see (and always having to escape the end result doesn't really make sense). More practically, getting connectors to add an escape mechanism can work for some connectors where the authors are more reactive and where the user base can also upgrade quickly (e.g. Perl's DBD::Pg), but the hopes of getting ODBC and JDBC and whatever depends on them to adapt are extremely low. (I'm also not sure if ECPG is used much compare to libpq, but in principle, not being able to use these operators there isn't great.) In contrast, providing a new set of operators (that wouldn't have this problem) should be doable with a rather smooth transition (since CREATE OPERATOR can be run on existing installations, if backporting the new operators is needed). (The existing operators wouldn't have to be removed in the short term, if ever.) Best wishes, Bruno.
Re: [HACKERS] WIP: Enhanced ALTER OPERATOR
Hi, On 2015-05-20 12:22:34 +0300, Uriy Zhuravlev wrote: > On Monday 18 May 2015 10:21:10 you wrote: > > difficulty of updating existing cached plans > Could you specify more precisely about some caches we talking about? PREPARE > working correctly: > > CREATE TABLE test_ints(i int4); > CREATE TABLE > CREATE INDEX idx ON test_ints(i); > CREATE INDEX > set enable_bitmapscan=off; > SET > set enable_seqscan=off; > SET > PREPARE test_plan (int) AS > SELECT * FROM test_ints WHERE $1::int4 > i; > PREPARE > EXPLAIN (COSTS OFF) > EXECUTE test_plan(5); >QUERY PLAN > > Index Only Scan using idx on test_ints >Index Cond: (i < 5) > > ALTER OPERATOR > (int4, int4) SET COMMUTATOR NONE; > ALTER OPERATOR > EXPLAIN (COSTS OFF) > EXECUTE test_plan(5); >QUERY PLAN > > Index Only Scan using idx on test_ints >Filter: (5 > i) Note that this very likely wasn't actually using a prepared plan. Due to the custom plan infrastructure the first few invocations are going to be replanned. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Enhanced ALTER OPERATOR
Alvaro Herrera writes: > Uriy Zhuravlev wrote: >> And can you explain more about the syntax? > I think he means to treat COMMUTATOR etc like a generic element list, > i.e. don't define new keywords in kwlist.h/gram.y at all but rather pass > the names as strings (probably using a list of DefElem) and strcmp() > them in OperatorUpd() or something. Yeah. If they aren't keywords in CREATE OPERATOR, I don't think they should be in ALTER OPERATOR either. Indeed, the syntax of the two commands probably ought to be similar. 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] jsonb concatenate operator's semantics seem questionable
On Wed, May 20, 2015 at 12:34 PM, Andrew Dunstan wrote: > > So Dmitry, at my suggestion, has come up with a way of doing that, by > adding a parameter to jsonb_replace(). If this parameter is set to true (it > defaults to false) and the key or array element pointed to by the last > element of the path doesn't exist, it gets created. > +1
Re: [HACKERS] GROUPING
David Fetter 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. 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] jsonb concatenate operator's semantics seem questionable
Petr Jelinek wrote:
> On 20/05/15 01:38, Jim Nasby wrote:
> >If we get this wrong now, we'll be stuck with it forever. At a minimum I
> >think we should use anything other than || until we can figure this out.
> >That leaves || available for whichever case we decide on.
>
> I am of strong opinion that concat should be shallow by default. Again it's
> how jquery works by default, it's how python's dict.update works and you can
> find this behavior in other languages as well when dealing with nested
> hashes. It's also how json would behave if you'd just did string
> concatenation (removing the outermost curly brackets) and parse it to json
> afterwards.
>
> I think this whole discussion shows primarily that it's by far not
> universally agreed if concatenation of json should be shallow or deep by
> default and AFAICS this is true even in javascript world so we don't really
> have where to look for precedents.
>
> Given the above I would vote to just provide the function and leave out the
> || operator for now.
+1 for providing just functions, not operators, for this JSON so-called
"concatenation".
But it is not really concatenation, is it? This is more like a "merge"
operation. I mean, if you told somebody that this operation is
concatenation
'xyz foo yxz' || 'bar' --> 'xyz bar yxz'
they would laugh at you pretty hard. But that's precisely what is
happening here:
alvherre=# select jsonb '{"a": 1, "b": 2, "c": 3}' || '{"b": 4}';
?column?
--
{"a": 1, "b": 4, "c": 3}
(1 fila)
--
Á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] WIP: Enhanced ALTER OPERATOR
Uriy Zhuravlev wrote: > And can you explain more about the syntax? I think he means to treat COMMUTATOR etc like a generic element list, i.e. don't define new keywords in kwlist.h/gram.y at all but rather pass the names as strings (probably using a list of DefElem) and strcmp() them in OperatorUpd() or something. -- Á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] jsonb concatenate operator's semantics seem questionable
On 05/20/2015 02:11 AM, Peter Geoghegan wrote:
On Tue, May 19, 2015 at 10:43 PM, Petr Jelinek wrote:
I am of strong opinion that concat should be shallow by default. Again it's
how jquery works by default, it's how python's dict.update works and you can
find this behavior in other languages as well when dealing with nested
hashes. It's also how json would behave if you'd just did string
concatenation (removing the outermost curly brackets) and parse it to json
afterwards.
As I said, that argument might be a good one if you were able to
subscript jsonb and have the update affect one particular subdocument.
You're not, though -- updating jsonb usually requires you to write an
SQL expression that evaluates to the final jsonb document that you'd
like to update a record to contain.
I think this whole discussion shows primarily that it's by far not
universally agreed if concatenation of json should be shallow or deep by
default and AFAICS this is true even in javascript world so we don't really
have where to look for precedents.
Given the above I would vote to just provide the function and leave out the
|| operator for now.
I've said my piece; I think it's a mistake to use an operator that has
a certain association, the association that the concatenate operate
got from hstore. || is the operator broadly useful for updates in
people's minds. I think this *positioning* of the operator is a
mistake. I'll leave it at that.
OK, I'm going to suggest a way out of this. ISTM the real trouble is
that you're wanting to shoehorn a meaning onto || which many people
don't think it should have. || doesn't mean "update" to me, it means
"concatenate", which in the json context means
'{ items1}' || '{items2}' = '{items1, items2}'
That's 100% consistent not only with hstore but with the use of this
operator for strings and arrays. The fact that it's used as the way to
update hstore is a byproduct of the way hstore works rather than a
fundamental part of the meaning of ||. If hstore's rule were "first one
wins" instead of "last one wins" we'd have to use something else.
But leaving that aside, your real gripe is that we don't currently have
any way of adding a value somewhere nested inside json.
So Dmitry, at my suggestion, has come up with a way of doing that, by
adding a parameter to jsonb_replace(). If this parameter is set to true
(it defaults to false) and the key or array element pointed to by the
last element of the path doesn't exist, it gets created.
Examples:
andrew=# select
jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}',
true);
jsonb_replace
--
{"a": 1, "b": [0, 1, 2], "c": {"d": 4, "e": {"foo": "bar"}}}
(1 row)
andrew=# select
jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}',
false);
jsonb_replace
-
{"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
(1 row)
andrew=# select
jsonb_replace('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":"bar"}');
jsonb_replace
-
{"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
(1 row)
This seems to me a much more straightforward way of adding a value
inside a jsonb than any operator can offer.
This is actually a tiny change - less than 200 lines - and given the
evident angst over this issue, I'm prepared to incorporate it. I'm still
working on the array piece, will have it done later today, but the
object field piece just works.
If we do we might want to reconsider the name of jsonb_replace - maybe
call it jsonb_set.
So, can we bend the rules just a tad to do this and (I hope) make a lot
of people a lot happier?
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] Problems with question marks in operators (JDBC, ECPG, ...)
On Wed, May 20, 2015 at 11:13 AM, Tom Lane wrote:
> Jeff Janes writes:
> > What if something like this was made to work?
> > select '{"3":5}'::jsonb operator("pg_catalog"."?") '3';
> > (Where the double quotes around the ? would be tolerated, which they
> > currently are not)
>
> > Is there a reason it can't be made to work?
>
> It could be made to work, I'm sure, but I fail to see why any user
> would prefer to write that over ?? or \? or {?} or pretty much any
> of the other notations that've been suggested. It's ten times as
> many keystrokes ...
>
Because it is a completely general solution using the existing escaping
infrastructure, on both ends, except for this one small exception.
It is ugly, but so is leaning toothpick syndrome where you have to escape
your escapes from someone else's escapes.
Anyway, I've never looked at code written to use JDBC and thought "Boy,
that sure is pretty".
Cheers,
Jeff
Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 2015-05-20 11:24:06 -0700, Peter Geoghegan wrote: > On Wed, May 20, 2015 at 10:37 AM, Andres Freund wrote: > > But you *can* use a exclusion constraint for DO NOTHING. Just not (yet) > > for DO UPDATE. > > FWIW, I don't think exclusion constraint DO UPDATE support is ever > going to be useful. Why? Even if maybe not directly under the guise of exclusion constraints themselves, but I do think it's an interesting way to more easily allow to implement unique constraints on !amcanunique type indexes. Or, more interestingly, for unique keys spanning partitions. -- 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 UPDATE/IGNORE 4.0
On Wed, May 20, 2015 at 10:37 AM, Andres Freund wrote: > But you *can* use a exclusion constraint for DO NOTHING. Just not (yet) > for DO UPDATE. FWIW, I don't think exclusion constraint DO UPDATE support is ever going to be useful. -- 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, ...)
Jeff Janes writes:
> What if something like this was made to work?
> select '{"3":5}'::jsonb operator("pg_catalog"."?") '3';
> (Where the double quotes around the ? would be tolerated, which they
> currently are not)
> Is there a reason it can't be made to work?
It could be made to work, I'm sure, but I fail to see why any user
would prefer to write that over ?? or \? or {?} or pretty much any
of the other notations that've been suggested. It's ten times as
many keystrokes ...
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, ...)
On Wed, May 20, 2015 at 7:04 PM, Jeff Janes wrote:
>
> What if something like this was made to work?
>
> select '{"3":5}'::jsonb operator("pg_catalog"."?") '3';
>
> (Where the double quotes around the ? would be tolerated, which they
> currently are not)
>
> Is there a reason it can't be made to work?
>
>
I'm not sure whether that could be made to work, but wouldn't that defeat
the point of using operators, i.e. something rather short, as opposed to
functions? (That's also partly one of the arguments against too much
escaping: over-complicating what's intended to be a relatively simple
notation, as the Hibernate example I mentioned earlier showed: "SELECT
. \\?\\? ".)
Best wishes,
Bruno.
Re: [HACKERS] Disabling trust/ident authentication configure option
Alvaro Herrera writes: > Josh Berkus wrote: >> As such, proposals are more likely to be successful if the proposer can >> show how they apply to a general use case, or adapt them so that they >> are useful to a large number of our users. This means that "this works >> in our environment which has conditions X, Y, and Z" is not an effective >> argument, unless you can follow it up with "... and here's the reason >> why [large class of users] also has conditions X, Y and Z." > The proposal here is to have a configure argument that disables > arbitrary auth mechanisms. How is that specific to a particular > environment? I think Josh's question is whether the feature is actually useful to a large class of users. One reason why it would not be, if it's a build-time decision, is that it's quite unlikely that any popular packagers would build that way. So this would only be applicable to custom-built binaries, which is a pretty small class of users to begin with. 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] Improving GEQO
On Wed, May 20, 2015 at 1:06 PM, alejandro wrote: > hello, my partner and me are working with the goal of improve the GEQO's > performance, we tried with Ant Colony Optimization, but it does not improve, > actually we are trying with a new variant of Genetic Algorithm, specifically > Micro-GA. This algorithm finds a better solution than GEQO in less time, > however the total query execution time is higher. The fitness is calculated > by geqo_eval function. Does anybody know why this happens? > It will be difficult for anyone here to figure out anything without the code to look at -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Josh Berkus wrote: > > > As such, proposals are more likely to be successful if the proposer can > > show how they apply to a general use case, or adapt them so that they > > are useful to a large number of our users. This means that "this works > > in our environment which has conditions X, Y, and Z" is not an effective > > argument, unless you can follow it up with "... and here's the reason > > why [large class of users] also has conditions X, Y and Z." > > The proposal here is to have a configure argument that disables > arbitrary auth mechanisms. How is that specific to a particular > environment? For my 2c, I'm still a fan of the general idea.. I still view 'trust' as appropriate only for development environments and I don't believe it really solves any use-cases that having password-less accounts doesn't. Removing it as an option simply removes a potential configuration mistake. It's not going to protect you from a malicious administrator who has root-level access any more than a CHECK constraint would stop a malicious DBA. Thanks! Stephen signature.asc Description: Digital signature
[HACKERS] Improving GEQO
hello, my partner and me are working with the goal of improve the GEQO's performance, we tried with Ant Colony Optimization, but it does not improve, actually we are trying with a new variant of Genetic Algorithm, specifically Micro-GA. This algorithm finds a better solution than GEQO in less time, however the total query execution time is higher. The fitness is calculated by geqo_eval function. Does anybody know why this happens? Regards.
[HACKERS] GROUPING
Folks, 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). Contravening the spec, but much more usefully, GROUPING should probably produce a (possibly ordered) set of key-value pairs. Alternatively, we could create something like GROUPING_JSON(). Cheers, David. -- David Fetter 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] Problems with question marks in operators (JDBC, ECPG, ...)
On Fri, May 15, 2015 at 1:23 PM, Dave Cramer wrote:
>
>
>
> On 15 May 2015 at 16:21, Robert Haas wrote:
>
>> On Fri, May 15, 2015 at 4:13 PM, Dave Cramer wrote:
>> > Not sure what the point of this is: as you indicated the ship has
>> sailed so
>> > to speak
>>
>> Well, if we were to agree this was a problem, we could introduce new,
>> less-problematic operator names and then eventually deprecate the old
>> ones. Personally, it wouldn't take a lot to convince me that if a
>> certain set of operator names is problematic for important connectors,
>> we should avoid using those and switch to other ones. I expect others
>> on this mailing list to insist that if the connectors don't work,
>> that's the connector drivers fault for coding their connectors wrong.
>> And maybe that's the right answer, but on the other hand, maybe it's a
>> little myopic. I think the discussion is worth having.
>>
>
> In that case my vote is new operators. This has been a sore point for the
> JDBC driver
>
What if something like this was made to work?
select '{"3":5}'::jsonb operator("pg_catalog"."?") '3';
(Where the double quotes around the ? would be tolerated, which they
currently are not)
Is there a reason it can't be made to work?
Cheers,
Jeff
Re: [HACKERS] Disabling trust/ident authentication configure option
Josh Berkus wrote: > As such, proposals are more likely to be successful if the proposer can > show how they apply to a general use case, or adapt them so that they > are useful to a large number of our users. This means that "this works > in our environment which has conditions X, Y, and Z" is not an effective > argument, unless you can follow it up with "... and here's the reason > why [large class of users] also has conditions X, Y and Z." The proposal here is to have a configure argument that disables arbitrary auth mechanisms. How is that specific to a particular environment? -- Á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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Andres Freund writes: > On 2015-05-20 13:31:57 -0400, Tom Lane wrote: >> If you can't use an exclusion constraint to support the command, >> then the error message shouldn't be worded like that. > But you *can* use a exclusion constraint for DO NOTHING. Just not (yet) > for DO UPDATE. Hm. Maybe it would be worth having two different message texts for the two cases, then? 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] Typo patch
Tom Lane wrote: > Heikki Linnakangas writes: > > Thanks, committed. Except for this one: > > > - * *Only* a frozen-for-read tape can be seeked. > > + * *Only* a frozen-for-read tape can be sought. > > > It's true that the past tense of "seek" is "sought", but it feels a bit > > weird to me in this context. This is a comment on a function called > > "seek", and it's not clear to me that it should conjugate > > like the normal "seek" verb. > > I agree that "sought" is not the word to use here, but the existing > wording isn't very good English either. Perhaps "Seeking is only allowed > on frozen-for-read tapes"? It would be great if you could find a solution for words such as "shutdowned" or "backuped". (I don't know that we use the latter in our code, but I've seen it around. The former we even have in an enum somewhere.) -- Á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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 2015-05-20 13:31:57 -0400, Tom Lane wrote: > Sure, but on what basis does it decide that there's a conflict? > > If you can't use an exclusion constraint to support the command, > then the error message shouldn't be worded like that. But you *can* use a exclusion constraint for DO NOTHING. Just not (yet) for DO UPDATE. -- 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 05/20/2015 01:20 AM, Volker Aßmann wrote: So, in the interests of trying to get you to understand why your proposal met with a negative response, and to improve future proposals: > You don't seem to have much trust in your other authentication > mechanisms and seem to know our environment quite well then... PostgreSQL is a general-purpose database with millions of users. As such, features which are only useful in a specialized environment receive far more scrutiny and pushback than feature proposals which are obviously generally applicable. As such, proposals are more likely to be successful if the proposer can show how they apply to a general use case, or adapt them so that they are useful to a large number of our users. This means that "this works in our environment which has conditions X, Y, and Z" is not an effective argument, unless you can follow it up with "... and here's the reason why [large class of users] also has conditions X, Y and Z." -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Andres Freund writes: > On 2015-05-20 18:09:05 +0100, Thom Brown wrote: This implies that an exclusion constraint is valid in the statement, which contradicts the docs. Which one is correct? >>> ON CONFLICT can be used for ... DO NOTHING as well. >> Yes, but still confusing when not using DO NOTHING. > I'm not sure I can follow. INSERT INTO account VALUES(...) ON CONFLICT > (email) DO NOTHING; seems to make sense to me? Sure, but on what basis does it decide that there's a conflict? If you can't use an exclusion constraint to support the command, then the error message shouldn't be worded like that. 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] Typo patch
Heikki Linnakangas writes: > Thanks, committed. Except for this one: > - * *Only* a frozen-for-read tape can be seeked. > + * *Only* a frozen-for-read tape can be sought. > It's true that the past tense of "seek" is "sought", but it feels a bit > weird to me in this context. This is a comment on a function called > "seek", and it's not clear to me that it should conjugate > like the normal "seek" verb. I agree that "sought" is not the word to use here, but the existing wording isn't very good English either. Perhaps "Seeking is only allowed on frozen-for-read tapes"? 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 UPDATE/IGNORE 4.0
On 2015-05-20 18:09:05 +0100, Thom Brown wrote: > On 20 May 2015 at 17:54, Andres Freund wrote: > > On 2015-05-20 17:44:05 +0100, Thom Brown wrote: > >> The docs say "Note that exclusion constraints are not supported with > >> ON CONFLICT DO UPDATE." > >> > >> But I get the following error message text: > >> > >> "ERROR: there is no unique or exclusion constraint matching the ON > >> CONFLICT specification" > >> > >> This implies that an exclusion constraint is valid in the statement, > >> which contradicts the docs. Which one is correct? > > > > ON CONFLICT can be used for ... DO NOTHING as well. > > Yes, but still confusing when not using DO NOTHING. I'm not sure I can follow. INSERT INTO account VALUES(...) ON CONFLICT (email) DO NOTHING; seems to make sense to me? Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 20 May 2015 at 17:54, Andres Freund wrote: > On 2015-05-20 17:44:05 +0100, Thom Brown wrote: >> On 8 May 2015 at 16:03, Andres Freund wrote: >> > So I've committed the patch yesterday evening. I'm pretty sure there'll >> > be some more minor things to change. But overall I feel good about the >> > current state. >> > >> > It'd be quite helpful if others could read the docs, specifically for >> > insert, and comment whether they're understandable. I've spent a fair >> > amount of time trying to make them somewhat simpler, but I do think I >> > only succeeded partially. And there's also my own brand of english to >> > consider ;) >> >> The docs say "Note that exclusion constraints are not supported with >> ON CONFLICT DO UPDATE." >> >> But I get the following error message text: >> >> "ERROR: there is no unique or exclusion constraint matching the ON >> CONFLICT specification" >> >> This implies that an exclusion constraint is valid in the statement, >> which contradicts the docs. Which one is correct? > > ON CONFLICT can be used for ... DO NOTHING as well. Yes, but still confusing when not using DO NOTHING. -- Thom -- 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] Typo patch
On 05/20/2015 07:29 PM, CharSyam wrote: Hi, I changed typos error. and attached patch for this. Thanks you. I only changed comments only Thanks, committed. Except for this one: --- src/backend/utils/sort/logtape.c +++ src/backend/utils/sort/logtape.c @@ -926,7 +926,7 @@ LogicalTapeBackspace(LogicalTapeSet *lts, int tapenum, size_t size) /* * Seek to an arbitrary position in a logical tape. * - * *Only* a frozen-for-read tape can be seeked. + * *Only* a frozen-for-read tape can be sought. * * Return value is TRUE if seek successful, FALSE if there isn't that much * data in the tape (in which case there's no state change). It's true that the past tense of "seek" is "sought", but it feels a bit weird to me in this context. This is a comment on a function called "seek", and it's not clear to me that it should conjugate like the normal "seek" verb. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 2015-05-20 17:44:05 +0100, Thom Brown wrote: > On 8 May 2015 at 16:03, Andres Freund wrote: > > So I've committed the patch yesterday evening. I'm pretty sure there'll > > be some more minor things to change. But overall I feel good about the > > current state. > > > > It'd be quite helpful if others could read the docs, specifically for > > insert, and comment whether they're understandable. I've spent a fair > > amount of time trying to make them somewhat simpler, but I do think I > > only succeeded partially. And there's also my own brand of english to > > consider ;) > > The docs say "Note that exclusion constraints are not supported with > ON CONFLICT DO UPDATE." > > But I get the following error message text: > > "ERROR: there is no unique or exclusion constraint matching the ON > CONFLICT specification" > > This implies that an exclusion constraint is valid in the statement, > which contradicts the docs. Which one is correct? ON CONFLICT can be used for ... DO NOTHING as well. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typo patch
Thanks :) You make sense. 2015-05-21 1:49 GMT+09:00 Heikki Linnakangas : > On 05/20/2015 07:29 PM, CharSyam wrote: > >> Hi, >> >> I changed typos error. and attached patch for this. >> Thanks you. >> >> I only changed comments only >> > > Thanks, committed. Except for this one: > > --- src/backend/utils/sort/logtape.c > +++ src/backend/utils/sort/logtape.c > @@ -926,7 +926,7 @@ LogicalTapeBackspace(LogicalTapeSet *lts, int tapenum, > size_t size) > /* > * Seek to an arbitrary position in a logical tape. > * > - * *Only* a frozen-for-read tape can be seeked. > + * *Only* a frozen-for-read tape can be sought. > * > * Return value is TRUE if seek successful, FALSE if there isn't that much > * data in the tape (in which case there's no state change). > > It's true that the past tense of "seek" is "sought", but it feels a bit > weird to me in this context. This is a comment on a function called " blah>seek", and it's not clear to me that it should conjugate like the > normal "seek" verb. > > - Heikki > >
Re: [HACKERS] Problems with question marks in operators (JDBC, ECPG, ...)
On Tue, May 19, 2015 at 5:34 PM, Bruno Harbulot
wrote:
> Users of question mark operators are already admitting their application and
> code isn't portable (since they are specific to PostgreSQL and its
> extensions). The problem has more to do with how the other tools around
> handle these customisations. For example, it can be useful to have a model
> based on Hibernate in Java and be able to use ? operators for specific
> features. (Other tools like SQLAlchemy in Python also allow you to have
> customisations specific to the RDMBS platform, while being able to use the
> core features in a more platform-neutral way.)
>
> It turns out that you can indeed use ? in JSONB with a custom Hibernate
> query, you just need to double-escape it as follows: ? becomes ?? and has to
> be escaped as \?\?, but \ has to be escaped itself...
>
> SQLQuery query = session
> .createSQLQuery("SELECT
> CAST((CAST('{\"key1\":123,\"key2\":\"Hello\"}' AS jsonb) \\?\\? CAST(? AS
> text)) AS BOOLEAN)");
> query.setString(0, "key1");
I think we should be more focused on this part of the issue. It seems
to me that it's a good idea for connectors to have an escaping
mechanism. Pretty much any syntax that supports funny characters that
do magical things should also have a way to turn the magic off when
it's not wanted. But it's not a bad thing either for the core project
to try to steer around operator names that are likely to require
frequent use of that escaping mechanism.
--
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] INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
On 8 May 2015 at 16:03, Andres Freund wrote: > So I've committed the patch yesterday evening. I'm pretty sure there'll > be some more minor things to change. But overall I feel good about the > current state. > > It'd be quite helpful if others could read the docs, specifically for > insert, and comment whether they're understandable. I've spent a fair > amount of time trying to make them somewhat simpler, but I do think I > only succeeded partially. And there's also my own brand of english to > consider ;) The docs say "Note that exclusion constraints are not supported with ON CONFLICT DO UPDATE." But I get the following error message text: "ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification" This implies that an exclusion constraint is valid in the statement, which contradicts the docs. Which one is correct? -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
