Re: [HACKERS] Allow peer/ident to fall back to md5?
On Wed, Oct 29, 2014 at 10:52:38AM +0800, Craig Ringer wrote:
> On 10/29/2014 10:45 AM, Tom Lane wrote:
> > Craig Ringer writes:
> >> At pgconf-eu Álvaro and I were discussing the idea of allowing 'peer'
> >> and 'ident' authentication to fall back to md5 if the peer/ident check
> >> failed.
> >
> > I think it would be acceptable to define *new* auth modes that work
> > that way. I'm violently against redefining the meaning of existing
> > pg_hba.conf entries like this: it's not terribly hard to imagine
> > cases where it'd be a security problem, and even if you claim it isn't,
> > people will get bent out of shape if they think you're poking holes
> > in their oh-so-carefully-chosen authentication arrangements.
Switching from today's "peer" to the proposed method in a given installation
can indeed open a security hole. If you accept peer authentication only,
quality of account passwords is irrelevant. Using this mode requires setting
a strong password or no password at all.
> There's no point adding a usability improvement that's off by default.
>
> Distros can still enable it, though, and they're what I'm interested in.
> Nobody uses PostgreSQL's initdb default for pg_hba.conf ('trust') anyway.
Switching away from "trust" has been a safe call for distributions, because
every other method is strictly less permissive. "md5_or_peer" would be
strictly more permissive than either "md5" or "peer", so a distribution
switching to the new mode would be betting that the extra usability makes up
for folks overlooking the change and getting a security hole. (I think the
care needed to vet a switch from md5 to md5+peer is less than that needed to
vet a switch from peer to md5+peer.)
> I don't care in the slightest how it's spelled; these:
>
>peer
>peer with_md5_fallback
>peer md5_fallback=on
>peer_or_md5
Think about making this an option of the "peer" method that allows trying
subsequent pg_hba.conf lines when "peer" fails. Call it something like
"continue" or "sufficient". pg_hba.conf would have:
local all all peer continue
local all all md5
This lets you pair peer authentication with methods other than md5.
Thanks,
nm
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO : Allow parallel cores to be used by vacuumdb [ WIP ]
On Tue, Oct 28, 2014 at 9:27 AM, Dilip kumar wrote:
> On 28 October 2014 09:18, Amit Kapila Wrote,
>
> >I am worried about the case if after setting the inAbort flag,
>
> >PQCancel() fails (returns error).
>
> >
>
> >> If select(maxFd + 1, workerset, NULL, NULL, &tv); come out, we can
know whether it came out because of cancel query and handle it accordingly.
>
> >>
>
> >
>
> >Yeah, it is fine for the case when PQCancel() is successful, what
>
> >if it fails?
>
> >I think even if select comes out due to any other reason, it will behave
>
> >as if it came out due to Cancel, even though actually Cancel is failed,
>
> >how are planning to handle that case?
>
>
>
> I think If PQcancel fails then also there is no problem, because we are
setting inAbort flag in handle_sigint handler, it means user have tried to
terminate.
>
Yeah, user has tried to terminate, however utility will emit the
message: "Could not send cancel request" in such a case and still
silently tries to cancel and disconnect all connections.
One other related point is that I think still cancel handling mechanism
is not completely right, code is doing that when there are not enough
number of freeslots, but otherwise it won't handle the cancel request,
basically I am referring to below part of code:
run_parallel_vacuum()
{
..
for (cell = tables->head; cell; cell = cell->next)
{
/*
* This will give the free connection slot, if no slot is free it will
* wait for atleast one slot to get free.
*/
free_slot = GetIdleSlot(connSlot, max_slot, dbname, progname,
completedb);
if (free_slot == NO_SLOT)
{
error = true;
goto fail;
}
prepare_command(connSlot[free_slot].connection, full, verbose,
and_analyze, analyze_only, freeze, &sql);
appendPQExpBuffer(&sql, " %s", cell->val);
connSlot[free_slot].isFree = false;
slotconn = connSlot[free_slot].connection;
PQsendQuery(slotconn, sql.data);
resetPQExpBuffer(&sql);
}
..
}
I am wondering if it would be better to setcancelconn in above loop.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] pg_dump/pg_restore seem broken on hamerkop
I wrote: > Alvaro Herrera writes: >> [Some more code and git-log reading later] I see that the %z is a very >> recent addition: it only got there as of commit ad5d46a449, of September >> 5th ... and now I also see that hamerkop's last green run before the >> failure, on Oct 13rd, did *not* include the pg_upgrade check. So I'm >> thinking this was broken much earlier than 0eea804. > Ooohh ... you are right, the first failing build involved not only > the pg_dump refactoring commit, but an upgrade in the buildfarm script > that hamerkop was using (from 4.4 to 4.14). So it's entirely possible > this issue was already there and we just weren't seeing it tested. hamerkop is still failing in its runs done today. However, I'm not sure that that proves anything about our hoped-for fix, because the commit SHAs it's showing on the buildfarm status page are still from Oct 13. It looks like hamerkop has failed to pull any git updates since it was migrated to the new buildfarm script version. 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] Allow peer/ident to fall back to md5?
On 10/29/2014 10:45 AM, Tom Lane wrote:
> Craig Ringer writes:
>> At pgconf-eu Álvaro and I were discussing the idea of allowing 'peer'
>> and 'ident' authentication to fall back to md5 if the peer/ident check
>> failed.
>
> I think it would be acceptable to define *new* auth modes that work
> that way. I'm violently against redefining the meaning of existing
> pg_hba.conf entries like this: it's not terribly hard to imagine
> cases where it'd be a security problem, and even if you claim it isn't,
> people will get bent out of shape if they think you're poking holes
> in their oh-so-carefully-chosen authentication arrangements.
Well, that's why I mentioned control over fallback via an option to
peer/ident below.
>> If anyone's concerned about that I think it'd be reasonable to
>> add an option in pg_hba.conf to allow 'ident' and 'peer' to be qualified
>> with a no_md5_fallback mode.
>
> You've got that exactly backwards.
There's no point adding a usability improvement that's off by default.
Distros can still enable it, though, and they're what I'm interested in.
Nobody uses PostgreSQL's initdb default for pg_hba.conf ('trust') anyway.
I don't care in the slightest how it's spelled; these:
peer
peer with_md5_fallback
peer md5_fallback=on
peer_or_md5
... or whatever else. Personally I'm not concerned about allowing a user
who has login rights on the database to log in with a correct password
in a new major release where we can release-note the change, but if you
are, I don't much care if it's off by default in core. Distros can fix that.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allow peer/ident to fall back to md5?
Craig Ringer writes: > At pgconf-eu Álvaro and I were discussing the idea of allowing 'peer' > and 'ident' authentication to fall back to md5 if the peer/ident check > failed. I think it would be acceptable to define *new* auth modes that work that way. I'm violently against redefining the meaning of existing pg_hba.conf entries like this: it's not terribly hard to imagine cases where it'd be a security problem, and even if you claim it isn't, people will get bent out of shape if they think you're poking holes in their oh-so-carefully-chosen authentication arrangements. > If anyone's concerned about that I think it'd be reasonable to > add an option in pg_hba.conf to allow 'ident' and 'peer' to be qualified > with a no_md5_fallback mode. You've got that exactly backwards. 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] logical decoding - reading a user catalog table
On 10/28/2014 01:31 PM, Andres Freund wrote:
On 2014-10-25 18:18:07 -0400, Steve Singer wrote:
My logical decoding plugin is occasionally getting this error
"could not resolve cmin/cmax of catalog tuple"
I get this when my output plugin is trying to read one of the user defined
catalog tables (user_catalog_table=true)
Hm. That should obviously not happen.
Could you describe how that table is modified? Does that bug happen
initially, or only after a while?
It doesn't happen right away, in this case it was maybe 4 minutes after
creating the slot.
The error also doesn't always happen when I run the this test workload
but it is reproducible with some trying.
I' don't do anything special to that table, it gets created then I do
inserts on it. I don't do an alter table or anything fancy like that.
I was running the slony failover test (all nodes under the same
postmaster) which involves the occasional dropping and recreating of
databases along with normal query load + replication.
I'll send you tar of the data directory off list with things in this state.
Do you have a testcase that would allow me to easily reproduce the
problem?
I don't have a isolated test case that does this. The test that I'm
hitting this with does lots of stuff and doesn't even always hit this.
I am not sure if this is a bug in the time-travel support in the logical
decoding support of if I'm just using it wrong (ie not getting a sufficient
lock on the relation or something).
I don't know yet...
This is the interesting part of the stack trace
#4 0x0091bbc8 in HeapTupleSatisfiesHistoricMVCC
(htup=0x7fffcf42a900,
snapshot=0x7f786ffe92d8, buffer=10568) at tqual.c:1631
#5 0x004aedf3 in heapgetpage (scan=0x28d7080, page=0) at
heapam.c:399
#6 0x004b0182 in heapgettup_pagemode (scan=0x28d7080,
dir=ForwardScanDirection, nkeys=0, key=0x0) at heapam.c:747
#7 0x004b1ba6 in heap_getnext (scan=0x28d7080,
direction=ForwardScanDirection) at heapam.c:1475
#8 0x7f787002dbfb in lookupSlonyInfo (tableOid=91754, ctx=0x2826118,
origin_id=0x7fffcf42ab8c, table_id=0x7fffcf42ab88,
set_id=0x7fffcf42ab84)
at slony_logical.c:663
#9 0x7f787002b7a3 in pg_decode_change (ctx=0x2826118, txn=0x28cbec0,
relation=0x7f787a3446a8, change=0x7f786ffe3268) at slony_logical.c:237
#10 0x007497d4 in change_cb_wrapper (cache=0x28cbda8, txn=0x28cbec0,
relation=0x7f787a3446a8, change=0x7f786ffe3268) at logical.c:704
Here is what the code in lookupSlonyInfo is doing
--
sltable_oid = get_relname_relid("sl_table",slony_namespace);
sltable_rel = relation_open(sltable_oid,AccessShareLock);
tupdesc=RelationGetDescr(sltable_rel);
scandesc=heap_beginscan(sltable_rel,
GetCatalogSnapshot(sltable_oid),0,NULL);
reloid_attnum = get_attnum(sltable_oid,"tab_reloid");
if(reloid_attnum == InvalidAttrNumber)
elog(ERROR,"sl_table does not have a tab_reloid column");
set_attnum = get_attnum(sltable_oid,"tab_set");
if(set_attnum == InvalidAttrNumber)
elog(ERROR,"sl_table does not have a tab_set column");
tableid_attnum = get_attnum(sltable_oid, "tab_id");
if(tableid_attnum == InvalidAttrNumber)
elog(ERROR,"sl_table does not have a tab_id column");
while( (tuple = heap_getnext(scandesc,ForwardScanDirection) ))
(Except missing spaces ;)) I don't see anything obviously wrong with
this.
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
[HACKERS] Allow peer/ident to fall back to md5?
Hi all At pgconf-eu Álvaro and I were discussing the idea of allowing 'peer' and 'ident' authentication to fall back to md5 if the peer/ident check failed. This can be done backwards compatibly and without protocol changes. A client won't be able to tell that the pg_hba.conf line wasn't 'md5'. I think this would be a nice usability improvement, especially for new users who have no idea what a unix socket is and who're quite confused by this: $ sudo -u postgres psql -q postgres=# CREATE USER alpha WITH PASSWORD 'beta'; postgres=# $ psql -W -U alpha Password for user alpha: psql: FATAL: Peer authentication failed for user "alpha" ... or client-library equivalents using connstrings where the fact that any supplied password is ignored is less obvious. In terms of BC impact, it'd possibly cause clients to prompt for a password where they'd otherwise exit with an error - but that's kind of the idea. If anyone's concerned about that I think it'd be reasonable to add an option in pg_hba.conf to allow 'ident' and 'peer' to be qualified with a no_md5_fallback mode. On the server side, what we'd do is check ident/peer if set in pg_hba.conf . If it's rejected we'd store the details of why we rejected it, then send a password request packet for md5 authentication. If password auth succeeds we'd log in normally. If password auth fails, we'd emit an error along the lines of: FATAL: Peer authentication and md5 fallback both failed for user "alpha" DETAIL: Local user "me" doesn't match remote user "alpha" and md5 password was not sent or did not match. ... and we'd log the usual info about remote and local user not matching to the server error log too. Reasonable? I'm also inclined to add a DEBUG1 message along the lines of "peer authentication failed because of username mismatch but fallback md5 authentication succeeded" to the client on successful auth. They wouldn't normally see it, but it'd make it easier to trace what's going on if you ask for it. IMO doing this would give us the most important benefits of a proper authentication negotiation handshake with fallback - without requiring protocol changes. It doesn't let you do fancier stuff like "try GSSAPI, fall back to md5" ... but personally, if it deals with the issues around peer/ident, I'll be happy. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] how to handle missing "prove"
On 10/28/2014 09:16 PM, Tom Lane wrote: Peter Eisentraut writes: Here is a patch to use "missing" to handle the case when "prove" is not present. Wouldn't it be easier to do what we do for Perl, viz in Makefile.global.in ifneq (@PERL@,) # quoted to protect pathname with spaces PERL = '@PERL@' else PERL = $(missing) perl endif However, with either of these approaches, "make check-world" gets a hard failure if you lack "prove". Is that what we want? It's certainly not very consistent with what you've been doing to make the tests just slide by (rather than fail on) missing/too old Perl modules. ISTM that the project policy for external components like this has been "don't rely on them unless user says to use them, in which case fail if they aren't present". So perhaps what we ought to have is a configure switch along the lines of "--enable-tap-tests". If you don't specify it, prove_check expands to nothing. If you do specify it, we fail if we lack any of the expected support, both "prove" and whatever the agreed-on set of Perl modules is. +1 If we go this way I'll add a tap icon to the buildfarm so you can see which animals are running the tests. 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] how to handle missing "prove"
On 10/28/14 9:16 PM, Tom Lane wrote: > Peter Eisentraut writes: >> Here is a patch to use "missing" to handle the case when "prove" is not >> present. > > Wouldn't it be easier to do what we do for Perl, viz in Makefile.global.in > > ifneq (@PERL@,) > # quoted to protect pathname with spaces > PERL = '@PERL@' > else > PERL = $(missing) perl > endif Yeah, maybe. > However, with either of these approaches, "make check-world" gets a hard > failure if you lack "prove". Is that what we want? It's certainly not > very consistent with what you've been doing to make the tests just slide > by (rather than fail on) missing/too old Perl modules. The patch has -$(missing) prove and the - will make make ignore failures. Admittedly, that is very well hidden. > ISTM that the project policy for external components like this has been > "don't rely on them unless user says to use them, in which case fail if > they aren't present". So perhaps what we ought to have is a configure > switch along the lines of "--enable-tap-tests". If you don't specify it, > prove_check expands to nothing. If you do specify it, we fail if we > lack any of the expected support, both "prove" and whatever the agreed-on > set of Perl modules is. That's also a good idea. (I might think of a different option name, because "TAP" is an output format, not a piece of software. pg_regress could output TAP as well, for example.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
On 10/27/14 7:36 PM, Stephen Frost wrote: > MySQL: > http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file > > (note they provide a way to limit access also, via secure_file_priv) They have a single privilege to allow the user to read or write any file. I think that feature could be useful. > Oracle: > http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm > http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#i2125999 >From the description, that CREATE DIRECTORY command looks to me more like a tablespace, or a general BLOB space, that you reference by object name, not by file name. > SQL Server: > http://msdn.microsoft.com/en-us/library/ms175915.aspx > (Note: they can actually run as the user connected instead of the SQL DB > server, if Windows authentication is used, which is basically doing > Kerberos proxying unless I'm mistaken; it's unclear how the security is > maintained if it's a SQL server logon user..). That could be useful. ;-) But it's not actually the same as the feature proposed here. > DB2: > http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dm.doc/doc/c0004589.html?cp=SSEPGG_9.7.0 That's also more like the single capability system that MySQL has. So while this is interesting food for thought, I don't think this really supports that claim that other systems have a facility very much like the proposed one. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] how to handle missing "prove"
Peter Eisentraut writes: > Here is a patch to use "missing" to handle the case when "prove" is not > present. Wouldn't it be easier to do what we do for Perl, viz in Makefile.global.in ifneq (@PERL@,) # quoted to protect pathname with spaces PERL= '@PERL@' else PERL= $(missing) perl endif However, with either of these approaches, "make check-world" gets a hard failure if you lack "prove". Is that what we want? It's certainly not very consistent with what you've been doing to make the tests just slide by (rather than fail on) missing/too old Perl modules. ISTM that the project policy for external components like this has been "don't rely on them unless user says to use them, in which case fail if they aren't present". So perhaps what we ought to have is a configure switch along the lines of "--enable-tap-tests". If you don't specify it, prove_check expands to nothing. If you do specify it, we fail if we lack any of the expected support, both "prove" and whatever the agreed-on set of Perl modules is. 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] TAP test breakage on MacOS X
On 10/28/14 12:46 AM, Noah Misch wrote: > Agreed. Having this framework when the pg_upgrade test suite originated would > have prevented acquiring parallel implementations in Perl and shell. Yes, that was certainly a motivation, and I would like to continue work on pg_upgrade testing. > Concretely, that means > discontinuing use of subplans and replacing IPC::Run with IPC::Cmd. (Windows > systems probably need to install IPC::Run so IPC::Cmd can use it internally; > that is okay.) If those restrictions make the test case developer experience > much worse, though, I'll backpedal toward less portability. I agree we should get rid of subplans. When I started out, they seemed useful, to avoid this sort of thing found in the postgresql-common test suite: use Test::More tests => ($#MAJORS == 0) ? 1 : 103 * 3; But as we are learning that they are a serious hindrance to portability, I think we can do without them. I have looked into IPC::Cmd, but the documentation keeps telling me that to do anything interesting I have to have IPC::Run anyway. I'll give it a try, though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/27/14 11:41 AM, Robert Haas wrote: > Beyond all that, I have serious doubts about whether, even if we > eventually get these tests mostly working in most places, whether they > will actually catch any bugs. Well, they caught the fact that pg_basebackup can't back up tablespaces with names longer than 99 characters, for example. But it's wrong to expect the primary value of tests to be to detect previously unknown bugs. Yes, that has been the experience in this project. We have software that we think works, and then we send it out to test on N obscure platforms, and we find interesting things. But the other dimension is that tests allow you to make changes with confidence. For example, the tablespace mapping logic in pg_basebackup has been whacked around about three times. I don't have any doubt that it still works correctly, because it has extensive test coverage. If someone sends in a patch to support cpio in pg_basebackup (hey, no symlink name length limits) and wants to refactor the entire source code to achieve that, I'll have no problem with that. Yes, some of the tests are low in value, perhaps a bit silly. But it's a foundation, and I expect to add more in the future. The good news is that because some of these "silly" tests actually exercise important internal functionality of the test mechanisms (e.g., create temporary directory, run program, record exit status, capture stdout and stderr separately, check output files), I am pretty confident that we can go far with the current infrastructure without needing any more external modules or something like that. So that also means that not all tests need to be run everywhere all the time. We have the option to run specific tests. It could be useful to have better groupings, but that has been discussed before and it hasn't come to a solution. > These are certainly good things to test, but I'd argue that once > you've verified that they are working, they're unlikely to get broken > again in the future. Famous last words ... ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_basebackup fails with long tablespace paths
On 10/20/14 2:59 PM, Tom Lane wrote:
> My Salesforce colleague Thomas Fanghaenel observed that the TAP tests
> for pg_basebackup fail when run in a sufficiently deeply-nested directory
> tree.
As for the test, we can do something like the attached to mark the test
as "TODO".
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index 597fb60..695fd98 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -68,6 +68,8 @@
"-T$tempdir/tblspc1=$tempdir/tbackup/tblspc1" ],
'plain format with tablespaces succeeds with tablespace mapping');
ok(-d "$tempdir/tbackup/tblspc1", 'tablespace was relocated');
+TODO: {
+ local $TODO = 'symlinks >99 chars not supported';
opendir(my $dh, "$tempdir/pgdata/pg_tblspc") or die;
ok( ( grep
{
@@ -77,6 +79,7 @@
} readdir($dh)),
"tablespace symlink was updated");
closedir $dh;
+}
mkdir "$tempdir/tbl=spc2";
psql 'postgres', "DROP TABLE test1;";
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/7/14 1:57 PM, Tom Lane wrote: > Peter had a patch to eliminate the overhead of multiple subinstalls; > not sure where that stands, but presumably it would address your issue. It will need some cleverness to sort out the parallel make issues that were brought up in the review thread. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Portability issues in TAP tests
On 7/21/14 10:06 AM, Christoph Berg wrote: > 6. The tests fail if your $LANG isn't en_something: This was fixed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Portability issues in TAP tests
On 7/17/14 3:31 PM, Tom Lane wrote: > My Salesforce colleagues have been complaining that the TAP tests added > in 9.4 don't work terribly well for them. I've been poking at this, > and I believe this is a reasonably complete list of the problems: Quick followup: > 1. "make [install]check-world" tries to run the TAP tests even when > "prove" was not found by configure. I regard this as a stop-ship issue > for 9.4; "prove" is not part of a basic Perl installation, and even if > it were, we don't require Perl to build from a tarball. I just sent in a proposed patch for that. > 2. Most of the tests fail in "make check" mode, unless you already did > "make install", because of failures to load libpq.so. The right fix for > this seems to be to modify LD_LIBRARY_PATH and friends This was fixed. > 3. Many of the tests depend on Test::More's "subtest" feature, This was changed so it doesn't fail anymore. Discussion is ongoing about whether to keep using that feature, but it's not a fatal error anymore. > 4. IPC::Run isn't installed by default on RHEL, and probably not on other > distros either. If there's a reasonably painless way to remove this > dependency, it'd improve the portability of the tests. This is lower > priority than the previous items, for sure. Same as above. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] how to handle missing "prove"
Here is a patch to use "missing" to handle the case when "prove" is not present. Other ideas? diff --git a/src/Makefile.global.in b/src/Makefile.global.in index b04d005..aff9af7 100644 --- a/src/Makefile.global.in +++ b/src/Makefile.global.in @@ -311,13 +311,13 @@ $(if $(filter $(PORTNAME),darwin),DYLD_LIBRARY_PATH,$(if $(filter $(PORTNAME),ai endef define prove_installcheck -cd $(srcdir) && TESTDIR='$(CURDIR)' PATH="$(bindir):$$PATH" PGPORT='6$(DEF_PGPORT)' $(PROVE) $(PG_PROVE_FLAGS) $(PROVE_FLAGS) t/*.pl +$(if $(PROVE),cd $(srcdir) && TESTDIR='$(CURDIR)' PATH="$(bindir):$$PATH" PGPORT='6$(DEF_PGPORT)' $(PROVE) $(PG_PROVE_FLAGS) $(PROVE_FLAGS) t/*.pl,-$(missing) prove) endef define prove_check $(MKDIR_P) tmp_check/log $(MAKE) -C $(top_builddir) DESTDIR='$(CURDIR)'/tmp_check/install install >'$(CURDIR)'/tmp_check/log/install.log 2>&1 -cd $(srcdir) && TESTDIR='$(CURDIR)' PATH="$(CURDIR)/tmp_check/install$(bindir):$$PATH" $(call add_to_path,$(ld_library_path_var),$(CURDIR)/tmp_check/install$(libdir)) PGPORT='6$(DEF_PGPORT)' $(PROVE) $(PG_PROVE_FLAGS) $(PROVE_FLAGS) t/*.pl +$(if $(PROVE),cd $(srcdir) && TESTDIR='$(CURDIR)' PATH="$(CURDIR)/tmp_check/install$(bindir):$$PATH" $(call add_to_path,$(ld_library_path_var),$(CURDIR)/tmp_check/install$(libdir)) PGPORT='6$(DEF_PGPORT)' $(PROVE) $(PG_PROVE_FLAGS) $(PROVE_FLAGS) t/*.pl,-$(missing) prove) endef # Installation. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trailing comma support in SELECT statements
On Fri, Oct 24, 2014 at 6:36 AM, Alex Goncharov < alex.goncharov@gmail.com> wrote: > On Tue, Oct 21, 2014 at 10:16 AM, Tom Lane wrote: > >> (Of course, I'm not for the feature w.r.t. SQL either. But breaking data >> compatibility is just adding an entire new dimension of trouble. >> > > Another dimension of the trouble is breaking the operation of the > tools that parse SQL statements for various purposes, e.g. for > dependency analysis. > If you hit the tool before you hit PostgreSQL then obviously you need to conform to whatever it accepts. For SQL directly generated from system catalogs we should not add extra commas. Function text is obviously one area where we keep queries as-is so how does this play with existing pl/pgsql static analysis routines? I'd be much more inclined to favor this if the user is provided a capability to have warnings emitted whenever extraneous commas are present - either via some form of strict mode or linting configuration. I do like the idea of being able to write "column," instead of ", column" with fewer "ooops" moments and marginal diff differences. David J.
Re: [HACKERS] Trailing comma support in SELECT statements
Jim Nasby writes: > On 10/28/14, 4:25 PM, David E. Wheeler wrote: >> This one, however, is more a judgment of people and their practices rather >> than the feature itself. Color me unimpressed. > +1. > Having users sweat of comma placement in this day and age is pretty stupid. I > can understand why we wouldn't want to break backwards compatibility, but I > think it does us and our users a disservice to dismiss the issue. I don't think anyone is just dismissing the issue. But it is certainly a judgment call as to whether the pros outweigh the cons, and I'm not seeing a clear majority of us thinking they do. 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] group locking: incomplete patch, just for discussion
On Tue, Oct 28, 2014 at 7:26 PM, Robert Haas wrote: > On Tue, Oct 28, 2014 at 7:22 PM, Jim Nasby wrote: >> On 10/28/14, 3:48 PM, Simon Riggs wrote: >>> Given your description of pg_background it looks an awful lot like >>> infrastructure to make Autonomous Transactions work, but it doesn't >>> even do that. I guess it could do in a very small additional patch, so >>> maybe it is useful for something. >> >> What do you see as being missing for autonomous transactios? > > Personally, I don't see this patch set as having much to do with real > autonomous transactions. > >> BTW, what I think would make this feature VERY useful is if it provided the >> ability to fire something up in another backend and leave it running in the >> background. > > You *can* do that. I mean, long-running transactions will have their > usual problems, but if you want to kick off a long-running (or a > short-running query) in the background and forget about it, this patch > lets you do that. Err, sorry. pg_background lets you do that, not this patch. -- 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] group locking: incomplete patch, just for discussion
On Tue, Oct 28, 2014 at 7:22 PM, Jim Nasby wrote: > On 10/28/14, 3:48 PM, Simon Riggs wrote: >> Given your description of pg_background it looks an awful lot like >> infrastructure to make Autonomous Transactions work, but it doesn't >> even do that. I guess it could do in a very small additional patch, so >> maybe it is useful for something. > > What do you see as being missing for autonomous transactios? Personally, I don't see this patch set as having much to do with real autonomous transactions. > BTW, what I think would make this feature VERY useful is if it provided the > ability to fire something up in another backend and leave it running in the > background. You *can* do that. I mean, long-running transactions will have their usual problems, but if you want to kick off a long-running (or a short-running query) in the background and forget about it, this patch lets you do that. -- 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] WIP: Access method extendability
On 2014-10-28 20:17:57 +, Simon Riggs wrote: > On 28 October 2014 17:47, Andres Freund wrote: > > On 2014-10-28 17:45:36 +, Simon Riggs wrote: > >> I'd like to avoid all of the pain by making persistent AMs that are > >> recoverable after a crash, rather than during crash recovery. > > > > Besides the actual difficulities of supporting this, imo not being > > available on HS and directly after a failover essentially makes them > > next to useless. > > Broken WAL implementations are worse than useless. > > I'm saying we should work on how to fix broken indexes first, before > we allow a crop of new code that might cause them. Why do we presume all of them will be that buggy? And why is that different for nbtree, gin, gist? And how is any form of automated invalidation changing anything fundamentally? To me this is a pretty independent issue. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] group locking: incomplete patch, just for discussion
On Tue, Oct 28, 2014 at 4:48 PM, Simon Riggs wrote: > On 16 October 2014 16:22, Robert Haas wrote: >>> Might I gently enquire what the "something usable" we are going to see >>> in this release? I'm not up on current plans. >> >> I don't know how far I'm going to get for this release yet. I think >> pg_background is a pretty good milestone, and useful in its own right. >> I would like to get something that's truly parallel working sooner >> rather than later, but this group locking issue is one of 2 or 3 >> significant hurdles that I need to climb over first. > > pg_background is very cute, but really its not really a step forward, > or at least very far. It's sounding like you've already decided that > is as far as we're going to get this release, which I'm disappointed > about. > > Given your description of pg_background it looks an awful lot like > infrastructure to make Autonomous Transactions work, but it doesn't > even do that. I guess it could do in a very small additional patch, so > maybe it is useful for something. > > You asked for my help, but I'd like to see some concrete steps towards > an interim feature so I can see some benefit in a clear direction. > > Can we please have the first step we discussed? Parallel CREATE INDEX? > (Note the please) What I've been thinking about trying to work towards is parallel sequential scan. I think that it would actually be pretty easy to code up a mostly-working version using the existing infrastructure, but the patch would be rejected with a bazooka, because the non-working parts would include things like: 1. The cooperating backends might not all be using the same snapshot, because that requires sharing the snapshot, combo CID hash, and transaction state. 2. The quals that got pushed down to the workers might not return the same answers that they would have produced with a single backend, because we have no mechanism for assessing pushdown-safety. 3. Deadlock detection would be to some greater or lesser degree broken, the details depending on the implementation choices you made. There is a bit of a chicken-and-egg problem here. If I submit a patch for parallel sequential scan, it'll (justifiably) get rejected because it doesn't solve those problems. So I'm trying to solve those above-enumerated problems first, with working and at least somewhat-useful examples that show how the incremental bits of infrastructure can be used to do stuff. But that leads to your (understandable) complaint that this isn't very real yet. Why am I now thinking about parallel sequential scan instead of parallel CREATE INDEX? You may remember that I posted a patch for a new memory allocator some time ago, and it came in for a fair amount of criticism and not much approbation. Some of that criticism was certainly justified, and perhaps I was as hard on myself as anyone else was. However you want to look at it, I see the trade-off between parallel sort and parallel seq-scan this way: parallel seq-scan requires dealing with the planner (ouch!) but parallel sort requires dealing with memory allocation in dynamic shared memory segments (ouch!). Both of them require solving the three problems listed above. And maybe a few others, but I think those are the big ones - and I think proper deadlock detection is the hardest of them. A colleague of mine has drafted patches for sharing snapshots and combo CIDs between processes, and as you might expect that's pretty easy. Sharing the transaction state (so we can test whether a transaction ID is "our" transaction ID inside the worker) is a bit trickier, but I think not too hard. Assessing pushdown-safety will probably boil down to adding some equivalent of proisparallel. Maybe not the most elegant, but defensible, and if you're looking for the shortest path to something usable, that's probably it. But deadlock detection ... well, I don't see any simpler solution than what I'm trying to build here. -- 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] group locking: incomplete patch, just for discussion
On 10/28/14, 3:48 PM, Simon Riggs wrote: Given your description of pg_background it looks an awful lot like infrastructure to make Autonomous Transactions work, but it doesn't even do that. I guess it could do in a very small additional patch, so maybe it is useful for something. What do you see as being missing for autonomous transactios? BTW, what I think would make this feature VERY useful is if it provided the ability to fire something up in another backend and leave it running in the background. I think you can do that with FDW, but then you have the authentication PITA to deal with (and perhaps pg_background is a more efficient way to move data between backends than FDW, but that's just a guess...) -- 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] Trailing comma support in SELECT statements
On 10/28/14, 4:25 PM, David E. Wheeler wrote: This is a misfeature for the benefit of edit-lazy users only. This one, however, is more a judgment of people and their practices rather than the feature itself. Color me unimpressed. +1. Having users sweat of comma placement in this day and age is pretty stupid. I can understand why we wouldn't want to break backwards compatibility, but I think it does us and our users a disservice to dismiss the issue. (BTW, I use a "comma first" formatting standard, so this doesn't actually effect me much, but I still find the original complaint very valid.) -- 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] Directory/File Access Permissions for COPY and Generic File Access Functions
On Tue, Oct 28, 2014 at 3:19 PM, Stephen Frost wrote: >> To articular my own concerns perhaps a bit better, there are two major >> things I don't like about the whole DIRALIAS proposal. Number one, >> you're creating this SQL object whose name is not actually used for >> anything other than manipulating the alias you created. > > I agree that this makes it feel awkward. Peter had an interesting > suggestion to make the dir aliases available as actual aliases for the > commands which they would be relevant to. I hadn't considered that- I > proposed 'diralias' because I didn't like 'directory' since we weren't > actually creating *directories* but rather defining aliases to existing > OS directories in PG. Right. Another way to go at this would be to just ditch the names. This exact syntax probably wouldn't work (or might not be a good idea) because GRANT is so badly overloaded already, but conceptually: GRANT READ ON DIRECTORY '/home/snowman' TO sfrost; Or maybe some variant of: ALTER USER sfrost GRANT READ ON DIRECTORY '/home/snowman'; > I'm not quite sure what to do with this comment. Perhaps it isn't at > the top of anyone's list (not even mine), but I didn't think we rejected > features because the community feels that some other feature is more > important. If we're going to start doing that then we should probably > come up with a list of what features the community wants, prioritize > them, and require that all committers work towards those features to the > exclusion of their own interests, or those of their employers or the > companies they own/run. I hope I've simply misunderstood the > implication here instead. No, that's not what I'm saying. Come on. From my point of view what happened is that a patch implementing a rather specific design for a problem I personally viewed as somewhat obscure just sort of dropped out of nowhere; and it came from people working at a company that is also working on a bunch of other security-related features. I wondered whether there was more to the story, but I guess not. -- 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] foreign data wrapper option manipulation during Create foreign table time?
hi, Andrew, thanks for the quick response. M understanding is that Alter Foreign Table can change the option values by user. What I need is to change the value programmatic inside foreign data wrapper code, and hope someone already done so I can learn from the existing design. I thought this email list is for developer(ie, who change the code), and pgsql-general for users. Hence, send to here. If it is wrong place, I will switch the question over. Anyway, appreciate the response. And good point on Alter-foreign-table, probably some logic there I can copy over to create-table Demai On Tue, Oct 28, 2014 at 3:00 PM, Andrew Dunstan wrote: > > On 10/28/2014 05:26 PM, Demai Ni wrote: > >> hi, guys, >> >> I am looking for a couple pointers here about fdw, and how to change the >> option values during CREATE table time. >> >> I am using postgres-xc-1.2.1 right now. For example, it contains >> file_fdw, whose create-table-stmt looks like: >> CREATE FOREIGN TABLE t1() >> SERVER file_server >> OPTIONS(format 'text',filename *'testing.txt'*); >> >> I would like to replace the 'testing.txt' with absolute path like >> '/user/testor1/testing.txt', and make sure the new value is saved in >> pg_foreign_table; the file_fdw_validator is used to validate the options, >> but is there a way to replace the optionValue here? And get the new value >> stored in pg_foreign_table? >> >> Thanks >> >> BTW, in my real use case, I am trying to interpret a hdfs file and would >> need to save some hostname/port information in the option value, which not >> necessary specified by user. >> >> > > This is the wrong list to ask this - it's a usage question that belongs on > pgsql-general > > See the documentation for ALTER FOREIGN TABLE. > > cheers > > andrew > >
Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?
On 10/28/2014 05:26 PM, Demai Ni wrote: hi, guys, I am looking for a couple pointers here about fdw, and how to change the option values during CREATE table time. I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw, whose create-table-stmt looks like: CREATE FOREIGN TABLE t1() SERVER file_server OPTIONS(format 'text',filename *'testing.txt'*); I would like to replace the 'testing.txt' with absolute path like '/user/testor1/testing.txt', and make sure the new value is saved in pg_foreign_table; the file_fdw_validator is used to validate the options, but is there a way to replace the optionValue here? And get the new value stored in pg_foreign_table? Thanks BTW, in my real use case, I am trying to interpret a hdfs file and would need to save some hostname/port information in the option value, which not necessary specified by user. This is the wrong list to ask this - it's a usage question that belongs on pgsql-general See the documentation for ALTER FOREIGN TABLE. 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] Trailing comma support in SELECT statements
On Oct 24, 2014, at 6:36 AM, Alex Goncharov wrote: > Another dimension of the trouble is breaking the operation of the > tools that parse SQL statements for various purposes, e.g. for > dependency analysis. That’s a valid point. > This is a misfeature for the benefit of edit-lazy users only. This one, however, is more a judgment of people and their practices rather than the feature itself. Color me unimpressed. Best, David smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] lag_until_you_get_something() OVER () window function
On Tue, Oct 28, 2014 at 12:40 PM, Kirk Roybal wrote: > Hi Guys, > > I propose a lag (and/or lead) window function that propagates the last > non-null value to the current row. > Here's an example of what I mean by that: > > CREATE TABLE lag_test (id serial primary key, natural_key integer, somebody > text); > > INSERT INTO lag_test(natural_key, somebody) > VALUES (1, NULL), (1, 'Kirk'), (1, NULL), (2, 'Roybal'), (2, NULL), (2, > NULL); > > /* > > Creates this data in the table. > id natural_key somebody > -- --- > 1 1NULL > 2 1Kirk > 3 1NULL > 4 2Roybal > 5 2NULL > 6 2NULL > > lag_until_you_get_something(text) function should return this in the > "somebody" column: > > id natural_key somebody > -- --- > 1 1NULL > 2 1Kirk > 3 1Kirk > 4 2Roybal > 5 2Roybal > 6 2Roybal > > Notice that row 6 has a value "Roybal", when the last known value was in row > 4. Also, Row 1 did not get a value. > */ > > -- Query that gets the right result for limited example data: > > CREATE FUNCTION last_elem (text[]) RETURNS text AS $$ > SELECT $1[array_upper($1,1)]; > $$ LANGUAGE SQL; > > > SELECT id, natural_key, > last_elem(string_to_array(string_agg(somebody, '|') OVER (ORDER BY > natural_key, id)::text, '|')) lag_hard > FROM lag_test > ORDER BY natural_key, id; Here's a more efficient and cleaner version of same: CREATE OR REPLACE FUNCTION GapFillInternal( s anyelement, v anyelement) RETURNS anyelement AS $$ BEGIN RETURN COALESCE(v,s); END; $$ LANGUAGE PLPGSQL IMMUTABLE; CREATE AGGREGATE GapFill(anyelement) ( SFUNC=GapFillInternal, STYPE=anyelement ); postgres=# select id, natural_key, gapfill(somebody) OVER (ORDER BY natural_key, id) from lag_test; id │ natural_key │ gapfill ┼─┼─ 1 │ 1 │ 2 │ 1 │ Kirk 3 │ 1 │ Kirk 4 │ 2 │ Roybal 5 │ 2 │ Roybal 6 │ 2 │ Roybal (6 rows) merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] foreign data wrapper option manipulation during Create foreign table time?
hi, guys, I am looking for a couple pointers here about fdw, and how to change the option values during CREATE table time. I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw, whose create-table-stmt looks like: CREATE FOREIGN TABLE t1() SERVER file_server OPTIONS(format 'text',filename *'testing.txt'*); I would like to replace the 'testing.txt' with absolute path like '/user/testor1/testing.txt', and make sure the new value is saved in pg_foreign_table; the file_fdw_validator is used to validate the options, but is there a way to replace the optionValue here? And get the new value stored in pg_foreign_table? Thanks BTW, in my real use case, I am trying to interpret a hdfs file and would need to save some hostname/port information in the option value, which not necessary specified by user. Demai
Re: [HACKERS] group locking: incomplete patch, just for discussion
On 16 October 2014 16:22, Robert Haas wrote: >> Might I gently enquire what the "something usable" we are going to see >> in this release? I'm not up on current plans. > > I don't know how far I'm going to get for this release yet. I think > pg_background is a pretty good milestone, and useful in its own right. > I would like to get something that's truly parallel working sooner > rather than later, but this group locking issue is one of 2 or 3 > significant hurdles that I need to climb over first. pg_background is very cute, but really its not really a step forward, or at least very far. It's sounding like you've already decided that is as far as we're going to get this release, which I'm disappointed about. Given your description of pg_background it looks an awful lot like infrastructure to make Autonomous Transactions work, but it doesn't even do that. I guess it could do in a very small additional patch, so maybe it is useful for something. You asked for my help, but I'd like to see some concrete steps towards an interim feature so I can see some benefit in a clear direction. Can we please have the first step we discussed? Parallel CREATE INDEX? (Note the please) -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lag_until_you_get_something() OVER () window function
There is already a patch for that (ignore/respect nulls in lead/lag): https://commitfest.postgresql.org/action/patch_view?id=1096 -- Vladimir -- Sent 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: Access method extendability
On 28 October 2014 17:50, Jim Nasby wrote: > On 10/28/14, 9:22 AM, Simon Riggs wrote: >> >> 2. Some additional code in Autovacuum to rebuild corrupt indexes at >> startup, using AV worker processes to perform a REINDEX CONCURRENTLY. > > > I don't think loading more functionality into autovac is the right way to do > that. You'd need to explain why and/or suggest your right way. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 17:58, Alexander Korotkov wrote: > Also, I'm not sure that many users have enough of courage to use unlogged > AMs. Absence of durability is only half of trouble, another half is lack of > streaming replication. I think if we have unlogged GIN then external > indexing engines would be used by majority of users instead of GIN. Please answer the problem I have raised. How will you act when the new AM that you write breaks? How will you advise your users that the durability they sensibly desire is actually causing them data loss? I haven't opposed your ideas in this patch; I have only observed the necessary surrounding infrastructure is incomplete. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 17:47, Andres Freund wrote: > On 2014-10-28 17:45:36 +, Simon Riggs wrote: >> I'd like to avoid all of the pain by making persistent AMs that are >> recoverable after a crash, rather than during crash recovery. > > Besides the actual difficulities of supporting this, imo not being > available on HS and directly after a failover essentially makes them > next to useless. Broken WAL implementations are worse than useless. I'm saying we should work on how to fix broken indexes first, before we allow a crop of new code that might cause them. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
Stephen Frost wrote: > the original ask was to be able to view logs as a DBA who isn't a > superuser, and without having to have those views delayed or > complex cron jobs running to set up access to them. I had kinda forgotten it, but I had to set up a cron log rsync at Wisconsin Courts. I understand the need. -- 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] Directory/File Access Permissions for COPY and Generic File Access Functions
Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Oct 28, 2014 at 11:16 AM, Stephen Frost wrote: > > There are more capabilities that I've been considering longer-term but > > wasn't sure if they should be independent or just lumped into the > > simpler read/write category: > > > > read (eg: importing log files, or importing from an NFS mount) > > write (eg: exporting to NFS mount) > > tablespace (eg: create a tablespace in a subdir of a directory) > > create directory (eg: create subdirs) > > modify permissions (eg: allow users other than pg to read/write/etc) > > directory listing > > large-object import/export (might be same as read/write) > > COPY PIPE > > I think it would be a good idea to figure out how this fits together > and propose a design that covers all the cases you think are > important, and then see how many of them the community agrees are > important. I have no problem with incremental commits moving toward > an agreed-upon design, but it's important that we don't go off in one > directly and then have to reverse course, because it creates upgrade > problems for our users. Certainly. > To articular my own concerns perhaps a bit better, there are two major > things I don't like about the whole DIRALIAS proposal. Number one, > you're creating this SQL object whose name is not actually used for > anything other than manipulating the alias you created. I agree that this makes it feel awkward. Peter had an interesting suggestion to make the dir aliases available as actual aliases for the commands which they would be relevant to. I hadn't considered that- I proposed 'diralias' because I didn't like 'directory' since we weren't actually creating *directories* but rather defining aliases to existing OS directories in PG. Perhaps it wasn't clear at the outset, but this is all work-in-progress and not intended to be the one-true-solution from on-high. Apologies if it came across that way. > The users are > still operating on pathnames. That's awfully strange. Number two, > every other SQL object we have has a name that is one or several > English words. DIRALIAS does not appear in any dictionary. The > second objection can be answered by renaming the facility, but the > first one is not so straightforward. I do think it's important to support subdirectories (the Amazon use-case is one where this would be required) and allowing users to specify the specific file names, so we'd have to come up with a way to combine the alias and the rest of the fully-qualified path. That might not be too bad but, to me at least, it seemed more natural to just use the full path. That was from a sysadmin perspective though, from a DBA perspective, knowing the rest of the path is probably not all that interesting and using the alias would be simpler for them. > > I'll discuss with Adam putting a wiki together which outlines the use > > cases and rationale for them and hopefully that'll lead into a better > > discussion about the possible permissions which would make sense to > > exist for these and that may inform us as to if a GUC-based approach > > would work. I'm still unsure about using GUCs to define permissions in > > this way. That feels novel to me for PG to do, but I'll admit that I > > may just be ignorant or forgetting existing cases where we do that. > > Well, there's temp_file_limit, for example. That's not exactly the > same, but it bears a passing resemblance. Hrm, yes, that's PG_SUSET and could be set per-user. > I'm definitely not saying that the GUC-based proposal is perfect. It > isn't, and if we're going to need a whole bunch of different > permissions that are all per-directory, that could get ugly in a > hurry. My points are (1) the community does not have to accept this > feature just because you propose it, and in fact there's a good > argument for rejecting it outright, which is that very few users are > going to get any benefit out of this, and it might end up being a > whole lot of code; and (2) the pros and cons of accepting this at all, > and of different designs, need to be debated here, on this list, in an > open way. I'd like to think that we're doing (2) now. As for (1), I certainly feel it's a useful capability and will argue for it, but the community certainly has the 'final say' on it, of course. I'm optomistic that the amount of code will be reasonable and that users will benefit from it or I wouldn't be advocating it, but that's obviously a judgement call and others will and are certainly entitled to have different opinions. > I think it would help, on all accounts, to explain why in the world > we're spending time on this in the first place. Because I feel it's a valuable feature...? So does Oracle, MySQL, and the other databases which support it. This isn't the first time it's come up either, as I pointed out up-thread. > I have a sneaking > suspicion this is 1 of N things we need to do to meet some US > government security sta
Re: [HACKERS] Reducing lock strength of adding foreign keys
There are actually TWO tables involved: the table upon which the trigger will actually fire, and some other table which is mentioned in passing in the trigger definition. It's possible that the locking requirements for the secondary table are weaker since I don't think the presence of the trigger actually affects runtime behavior there. However, there's probably little point in try to weaken the lock to less than the level required for the main table because a foreign key involves adding referential integrity triggers to both tables. - GUL -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reducing-lock-strength-of-adding-foreign-keys-tp5823894p5824376.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
Kevin, Thanks. * Kevin Grittner (kgri...@ymail.com) wrote: > Stephen my correct me on this, but I seem to remember him saying > that this was part of a general effort to avoid needing to use a > superuser login for routine tasks that don't fit into the area of > what a sysadmin would do. That seems like a laudable goal to me. Right, and this is one of those things that only a superuser can do now. I had expected to find other more complicated cases which would require a generalized "pg_permissions" type of approach but having gone through the superuser() checks, this is the one case where we really needed a complex ACL scheme that, in my view at least, warranted a new catalog table. Rather than come up with "pg_permissions" and some ugly hacks to make that work for a variety of object types, I looked to address the specific case of server-side directory access in a way similar to what other databases already provide. > Of course, most or all of what this particular feature would allow > can be done using superuser-owned SECURITY DEFINER functions, but > that is sure a lot clumsier and error-prone than being able to say > that role x can read from directory data/input and role y can write > to directory data/output. Exactly. > That said, Stephen does seem to have some additional specific use > cases in mind which he hasn't shared with the list; knowing what > problems we're talking about solving would sure help make > discussions about the possible solutions more productive. :-) It's actually more-or-less the opposite.. As I think I mentioned at some point earlier, the original ask was to be able to view logs as a DBA who isn't a superuser, and without having to have those views delayed or complex cron jobs running to set up access to them. That's a *frequently* asked for capability and I don't think this directory type approach will be the final solution to that specific problem, but it'll at least get us a lot closer while also providing capabilities that other databases have and that I've personally wanted for a long time. In other words, I took the ask and attempted to generalize it out to cover more use-cases that I've run into which are similar. While I have ideas and memories about times when I've wanted this capability for various use-cases, there's not some pre-defined list that I'm hiding offline in hopes that no one asks for it, nor is it for some government check-list. Since there is evidently interest in this, I'll try to provide some insight into the times I've run into this previously: The first time I came across COPY and was frustrated that I had to be a superuser to use it, period. Initially, I didn't realize it could do STDIN/STDOUT, but even once I discovered that, I felt it was unfortunate that only a superuser could do it server-side, unlike other databases. This, in my view, is probably the experience of nearly every new user to PG and COPY and, basically, it sucks. Later on, I started writing scripts to do server-side copy to avoid having to marshall data through whatever-client-API-I'm-using (perl, php, python, etc) and where I couldn't do that due to not being able to run as a superuser, I ended up doing ugly things in some cases (like exec'ing out to psql..) because I couldn't just tell the server "pull this file in". In some cases, COPY wasn't even supported by the client library, as I recall. That's better now, but new languages continue to come out and often initially support the bare minimum (wasn't ruby initially in this boat of lacking COPY protocol support initially..?). Then, when working with Pentaho I came across it again- having to marshall data through Java and over into PG, and it had to go over a local TCP connection instead of a unix socket (still the case with our JDBC driver, no?), primairly to get data into the DB which was out on an NFS mount in a format that PG could have digested just fine directly or could have made available via the file_fdw. Next was the Amazon use-case, which wasn't obvious to me initially but makes perfect sense now. They want to allow users to add new i/o channels and use them but can't let users run as the normal PG superuser, hence the idea about supporting CREATE TABLESPACE with this same 'diralias' approach. The thoughts around permissions related to 'diralias' (chmod, mkdir, ls, etc) are all just based on what unix provides already. Similairly, extending to support large-object import/export along with COPY just makes sense, as does supporting the file_fdw with this approach, imv. The file_fdw case is interesting as it's an extension and we'll need to be able to provide a clear and simple interface to check if the access is allowed or not which the file_fdw would then leverage. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Trailing comma support in SELECT statements
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/28/2014 05:20 AM, Alvaro Herrera wrote: > Alex Goncharov wrote: > >> This is a misfeature for the benefit of edit-lazy users only. > > +1 +1 Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1 iQIcBAEBAgAGBQJUT+MWAAoJEDfy90M199hllO8QAICsG86NK9hQE4QPLppIMkbv E5qGAv0Hml/g+dKtsDpxo1f2L5dguQnAQD7ERZeEMWv0X28PixN+PDxfUVUjFKm2 5uI+OiJ60xM4oN+/eNgt4gaJWgk8osVmS9lx4jcGRTY+z9loxLQVUvWJ04nEdbV4 CJjcC6QZB0bpIDSkWYPZIB/YYrBLZ+/gI2GMWSIye8QutshHXd0Bca6gqWcgrBcK sRVTVYd9hTNjnDLYPxSipuMJWHsyObMsSOnA0G9NuT+itj817uhOJhLOSq/Ctu02 C43CT4tM8Y0PX+EHhnkg2m0FMLeVYrSU8dCMI6MgjbSaghqjPRYHYjHoXyE9zqEk M2Vw3qsqax0hx1AAJr//PIrCTf2Kc7T5K2soZkUXaIwrilrk7GTW1wtl+OCizn5D Che5XCdwivh3m0Q6Wx/jtvdMqm1aJVA137kvHFLgR4SxkH2af/jhsP26ol5ieiAx lno00w3JSjjeVl+EFzDTVBDsFD0FRffdlUxB1V+gUUQ+XvOdpfZEbyppQfP01+Bd 2WHnc2tYr0QJKWwnInFMeN+OHP7cOvE5C1I48DIZUSvI76astP3QWPtFXa30xFiA CZUjzAKOqDQFvTpnHNMHoKRGCv28WZ525b5TPICWSO7EBaG2Uz/kMBvGXq4h6fOW g9Rrm4UuMcMxoHhWoz2T =JZC8 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trailing comma support in SELECT statements
2014-10-28 13:20 GMT+01:00 Alvaro Herrera : > Alex Goncharov wrote: > > > This is a misfeature for the benefit of edit-lazy users only. > > +1 > > +1 Pavel > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [HACKERS] Trailing comma support in SELECT statements
Alex Goncharov wrote: > This is a misfeature for the benefit of edit-lazy users only. +1 -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alter user/role CURRENT_USER
Marti Raudsepp wrote:
> On Fri, Oct 24, 2014 at 11:29 AM, Kyotaro HORIGUCHI
> wrote:
> > - 0001-ALTER-ROLE-CURRENT_USER_v2.patch - the patch.
>
> +RoleId:CURRENT_USER{ $$ =
> "current_user";}
> + | USER { $$ = "current_user";}
> + | CURRENT_ROLE { $$ = "current_user";}
> + | SESSION_USER { $$ = "session_user";}
>
> This is kind of ugly, and it means you can't distinguish between a
> CURRENT_USER keyword and a quoted user name "current_user". It's a
> legitimate user name, so the behavior of the following now changes:
>
> CREATE ROLE "current_user";
> ALTER ROLE "current_user" SET work_mem='10MB';
>
> There ought to be a better way to represent this than using magic string
> values.
Agreed. Since the current_user disease has already infected the USER
MAPPING stuff, I think we need to solve that problem -- how about having
this production return a new node which has either a string name or
flags for the various acceptable keywords?
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.4 bug] The database server hangs with write-heavy workload on Windows
On 10/14/2014 03:59 PM, MauMau wrote: BTW, in LWLockWaitForVar(), the first line of the following code fragment is not necessary, because lwWaitLink is set to head immediately. I think it would be good to eliminate as much unnecessary code as possible from the spinlock section. proc->lwWaitLink = NULL; /* waiters are added to the front of the queue */ proc->lwWaitLink = lock->head; Thanks, fixed! - 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] Directory/File Access Permissions for COPY and Generic File Access Functions
Robert Haas wrote: > I think it would help, on all accounts, to explain why in the world > we're spending time on this in the first place. I have a sneaking > suspicion this is 1 of N things we need to do to meet some US > government security standard, and if something like that is the case, > that could tip the balance toward doing it, or toward a particular > implementation of the concept. Stephen my correct me on this, but I seem to remember him saying that this was part of a general effort to avoid needing to use a superuser login for routine tasks that don't fit into the area of what a sysadmin would do. That seems like a laudable goal to me. Of course, most or all of what this particular feature would allow can be done using superuser-owned SECURITY DEFINER functions, but that is sure a lot clumsier and error-prone than being able to say that role x can read from directory data/input and role y can write to directory data/output. That said, Stephen does seem to have some additional specific use cases in mind which he hasn't shared with the list; knowing what problems we're talking about solving would sure help make discussions about the possible solutions more productive. :-) -- 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] WIP: Access method extendability
On Tue, Oct 28, 2014 at 01:51:21PM -0400, Tom Lane wrote: > Simon Riggs writes: > > On 28 October 2014 17:06, Tom Lane wrote: > >> My own thought is that allowing external AMs is simply a natural > >> consequence of PG's general approach to extensibility, and it would > >> be surprising if we were to decide we didn't want to allow that. > > > If it wasn't clear from my two earlier attempts, yes, +1 to that. > > > I'd like to avoid all of the pain by making persistent AMs that are > > recoverable after a crash, rather than during crash recovery. > > I think the notion of having AMs that explicitly don't have WAL support > is quite orthogonal to what's being discussed in this thread. It might > be worth doing that just to get the hash AM into a less-weird state > (given that nobody is stepping up to the plate to fix it properly). > > regards, tom lane > Hi, I think that someone is working on the hash index WAL problem, but are coming up to speed on the whole system, which takes time. I know that I have not had a large enough block of time to spend on it either. :( Regards, Ken -- Sent 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: Access method extendability
* Alexander Korotkov (aekorot...@gmail.com) wrote: > Having access methods as extensions can significantly improves situations > here. Imagine, GIN was an extension. One day we decide to change its binary > format. Then we can issue new extension, GIN2 for instance. User can > upgrade from GIN to GIN2 in following steps: We could support this without having GIN be an extension by simply having a GIN2 in core also, so I don't buy off on this being a good reason for extensions to provide AMs. For my 2c, I'm pretty happy with the general idea of "read-old, write-new" to deal with transistions. It's more complicated, certainly, but I don't think trying to force users off of an old version is actually going to work all that well and we'd just end up having to support both the old and new extensions indefinitely anyway. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WIP: Access method extendability
On Tue, Oct 28, 2014 at 8:04 PM, Simon Riggs wrote: > On 28 October 2014 14:22, Simon Riggs wrote: > > > Or put it another way, it will be easier to write new index AMs > > because we'll be able to skip the WAL part until we know we want it. > > To be clear: I am suggesting you do *less* work, not more. > > By allowing AMs to avoid writing WAL we get > * higher performance unlogged indexes > * we get fewer bugs in early days of new AMs > * writers of new AMs are OK to avoid majority of hard work and hard testing > > So overall, we get new AMs working faster because we can skip writing > the WAL code until we are certain the new AM code is useful and bug > free. > > For example, if GIN had avoided implementing WAL it would have been > easier to change on-disk representation. Major problem of changing on-disk representation is that we have to support both binary formats because of pg_upgrade. This problem is even burdened with WAL, because WAL record redo function also have to support both formats. However, it's also quite independent of WAL. Having access methods as extensions can significantly improves situations here. Imagine, GIN was an extension. One day we decide to change its binary format. Then we can issue new extension, GIN2 for instance. User can upgrade from GIN to GIN2 in following steps: 1. CREATE EXTENSION gin2; 2. CREATE INDEX CONCURRENTLY [new_index] USING gin2 ([index_def]); 3. DROP INDEX CONCURRENTLY [old_index]; 4. DROP EXTENSION gin; No need to write and debug the code which reads both old and new binary format. For sure, we need to support old GIN extension for some time. But, we have to support old in-core versions too. Unfortunately, I didn't mention this in the first post because I consider this as a serious argument for extensible AMs. Also, I'm not sure that many users have enough of courage to use unlogged AMs. Absence of durability is only half of trouble, another half is lack of streaming replication. I think if we have unlogged GIN then external indexing engines would be used by majority of users instead of GIN. -- With best regards, Alexander Korotkov.
Re: [HACKERS] WIP: Access method extendability
On 10/28/14, 9:22 AM, Simon Riggs wrote: 2. Some additional code in Autovacuum to rebuild corrupt indexes at startup, using AV worker processes to perform a REINDEX CONCURRENTLY. I don't think loading more functionality into autovac is the right way to do that. -- 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] WIP: Access method extendability
On 2014-10-28 13:37:33 -0400, Tom Lane wrote: > I'm not at all sold on the idea that we need to support dropping AMs. > I think it'd be fine to consider that installing an AM into a given > database is a one-way operation. Then you just need to insert some > pg_depend entries that "pin" the AM's individual functions, and you're > done. I think that'd be somewhat ugly. An extension adding such a AM would then either actively need to block dropping (e.g. by pinned entries, as you mention) or do rather odd things on recreation. I think that'd be dropping our own standards. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
Simon Riggs writes: > On 28 October 2014 17:06, Tom Lane wrote: >> My own thought is that allowing external AMs is simply a natural >> consequence of PG's general approach to extensibility, and it would >> be surprising if we were to decide we didn't want to allow that. > If it wasn't clear from my two earlier attempts, yes, +1 to that. > I'd like to avoid all of the pain by making persistent AMs that are > recoverable after a crash, rather than during crash recovery. I think the notion of having AMs that explicitly don't have WAL support is quite orthogonal to what's being discussed in this thread. It might be worth doing that just to get the hash AM into a less-weird state (given that nobody is stepping up to the plate to fix it properly). 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] Directory/File Access Permissions for COPY and Generic File Access Functions
On Tue, Oct 28, 2014 at 11:16 AM, Stephen Frost wrote: > There are more capabilities that I've been considering longer-term but > wasn't sure if they should be independent or just lumped into the > simpler read/write category: > > read (eg: importing log files, or importing from an NFS mount) > write (eg: exporting to NFS mount) > tablespace (eg: create a tablespace in a subdir of a directory) > create directory (eg: create subdirs) > modify permissions (eg: allow users other than pg to read/write/etc) > directory listing > large-object import/export (might be same as read/write) > COPY PIPE I think it would be a good idea to figure out how this fits together and propose a design that covers all the cases you think are important, and then see how many of them the community agrees are important. I have no problem with incremental commits moving toward an agreed-upon design, but it's important that we don't go off in one directly and then have to reverse course, because it creates upgrade problems for our users. To articular my own concerns perhaps a bit better, there are two major things I don't like about the whole DIRALIAS proposal. Number one, you're creating this SQL object whose name is not actually used for anything other than manipulating the alias you created. The users are still operating on pathnames. That's awfully strange. Number two, every other SQL object we have has a name that is one or several English words. DIRALIAS does not appear in any dictionary. The second objection can be answered by renaming the facility, but the first one is not so straightforward. > I'll discuss with Adam putting a wiki together which outlines the use > cases and rationale for them and hopefully that'll lead into a better > discussion about the possible permissions which would make sense to > exist for these and that may inform us as to if a GUC-based approach > would work. I'm still unsure about using GUCs to define permissions in > this way. That feels novel to me for PG to do, but I'll admit that I > may just be ignorant or forgetting existing cases where we do that. Well, there's temp_file_limit, for example. That's not exactly the same, but it bears a passing resemblance. I'm definitely not saying that the GUC-based proposal is perfect. It isn't, and if we're going to need a whole bunch of different permissions that are all per-directory, that could get ugly in a hurry. My points are (1) the community does not have to accept this feature just because you propose it, and in fact there's a good argument for rejecting it outright, which is that very few users are going to get any benefit out of this, and it might end up being a whole lot of code; and (2) the pros and cons of accepting this at all, and of different designs, need to be debated here, on this list, in an open way. I think it would help, on all accounts, to explain why in the world we're spending time on this in the first place. I have a sneaking suspicion this is 1 of N things we need to do to meet some US government security standard, and if something like that is the case, that could tip the balance toward doing it, or toward a particular implementation of the concept. From my point of view, if you made a list of all of the annoyances of using PostgreSQL and listed them in order of importance, you'd burn through a fair amount of paper before reaching this one. -- 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] Directory/File Access Permissions for COPY and Generic File Access Functions
On Tue, Oct 28, 2014 at 11:33 AM, Adam Brightwell wrote: >> Given that no fewer than four people - all committers - have expressed >> doubts about the design of this patch, I wonder why you're bothering >> to post a new version. > > I understand and my intent was in no way to disregard those concerns. The > only reason that I have posted a new version was simply to address some > minor issues that I noticed when responding to Peter's earlier comment about > missing files. > >> It seems to me that you should be discussing >> the fundamental design, not making minor updates to the code. > > Ok. I'm certainly looking at the other options proposed and will work with > Stephen to put together an appropriate design for discussion here. > >> I really hope this is not moving in the direction of another "surprise >> commit" like we had with RLS. There is absolutely NOT consensus on >> this design or anything close to it. > > Certainly not and I am in no way confused that consensus has not been > reached. OK, thanks. -- 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] WIP: Access method extendability
On 2014-10-28 17:45:36 +, Simon Riggs wrote: > I'd like to avoid all of the pain by making persistent AMs that are > recoverable after a crash, rather than during crash recovery. Besides the actual difficulities of supporting this, imo not being available on HS and directly after a failover essentially makes them next to useless. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 17:06, Tom Lane wrote: > My own thought is that allowing external AMs is simply a natural > consequence of PG's general approach to extensibility, and it would > be surprising if we were to decide we didn't want to allow that. If it wasn't clear from my two earlier attempts, yes, +1 to that. I'd like to avoid all of the pain by making persistent AMs that are recoverable after a crash, rather than during crash recovery. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] lag_until_you_get_something() OVER () window function
Hi Guys, I propose a lag (and/or lead) window function that propagates the last non-null value to the current row. Here's an example of what I mean by that: CREATE TABLE lag_test (id serial primary key, natural_key integer, somebody text); INSERT INTO lag_test(natural_key, somebody) VALUES (1, NULL), (1, 'Kirk'), (1, NULL), (2, 'Roybal'), (2, NULL), (2, NULL); /* Creates this data in the table. id natural_key somebody -- --- 1 1 NULL 2 1 Kirk 3 1 NULL 4 2 Roybal 5 2 NULL 6 2 NULL lag_until_you_get_something(text) function should return this in the "somebody" column: id natural_key somebody -- --- 1 1 NULL 2 1 Kirk 3 1 Kirk 4 2 Roybal 5 2 Roybal 6 2 Roybal Notice that row 6 has a value "Roybal", when the last known value was in row 4. Also, Row 1 did not get a value. */ -- Query that gets the right result for limited example data: CREATE FUNCTION last_elem (text[]) RETURNS text AS $$ SELECT $1[array_upper($1,1)]; $$ LANGUAGE SQL; SELECT id, natural_key, last_elem(string_to_array(string_agg(somebody, '|') OVER (ORDER BY natural_key, id)::text, '|')) lag_hard FROM lag_test ORDER BY natural_key, id; Sorry, I'm not a C-coder, or I'd whip this up myself and submit it. Thank you for your consideration, /Kirk
Re: [HACKERS] WIP: Access method extendability
Andres Freund writes: > On 2014-10-28 13:06:52 -0400, Tom Lane wrote: >> But having said that, it's quite unclear to me that we need the >> CREATE/DROP ACCESS METHOD infrastructure proposed here. The traditional >> theory about that is that if you're competent to develop an AM at all, >> you can certainly manage to insert a row into pg_am manually. > The problem with doing that is that you not only need to add a row in > pg_am, but also pg_depend. (1) That's not that hard; initdb makes pg_depend entries from SQL. (2) You only need a row in pg_depend if you provide a DROP command that would need to pay attention to it. > And a way to remove that row when the > respective extension is dropped. I'm not at all sold on the idea that we need to support dropping AMs. I think it'd be fine to consider that installing an AM into a given database is a one-way operation. Then you just need to insert some pg_depend entries that "pin" the AM's individual functions, and you're done. Yeah, sure, CREATE/DROP ACCESS METHOD would be cleaner. But in this case I'm not buying the "if you build it they will come" argument. External AMs *can* be built without any such SQL-level support, and if there were really much demand for them, there would be some out there. Let's build the essential WAL support first, and leave the syntactic sugar till we see some demand. 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] logical decoding - reading a user catalog table
On 2014-10-25 18:18:07 -0400, Steve Singer wrote:
> My logical decoding plugin is occasionally getting this error
>
> "could not resolve cmin/cmax of catalog tuple"
>
> I get this when my output plugin is trying to read one of the user defined
> catalog tables (user_catalog_table=true)
Hm. That should obviously not happen.
Could you describe how that table is modified? Does that bug happen
initially, or only after a while?
Do you have a testcase that would allow me to easily reproduce the
problem?
> I am not sure if this is a bug in the time-travel support in the logical
> decoding support of if I'm just using it wrong (ie not getting a sufficient
> lock on the relation or something).
I don't know yet...
> This is the interesting part of the stack trace
>
> #4 0x0091bbc8 in HeapTupleSatisfiesHistoricMVCC
> (htup=0x7fffcf42a900,
> snapshot=0x7f786ffe92d8, buffer=10568) at tqual.c:1631
> #5 0x004aedf3 in heapgetpage (scan=0x28d7080, page=0) at
> heapam.c:399
> #6 0x004b0182 in heapgettup_pagemode (scan=0x28d7080,
> dir=ForwardScanDirection, nkeys=0, key=0x0) at heapam.c:747
> #7 0x004b1ba6 in heap_getnext (scan=0x28d7080,
> direction=ForwardScanDirection) at heapam.c:1475
> #8 0x7f787002dbfb in lookupSlonyInfo (tableOid=91754, ctx=0x2826118,
> origin_id=0x7fffcf42ab8c, table_id=0x7fffcf42ab88,
> set_id=0x7fffcf42ab84)
> at slony_logical.c:663
> #9 0x7f787002b7a3 in pg_decode_change (ctx=0x2826118, txn=0x28cbec0,
> relation=0x7f787a3446a8, change=0x7f786ffe3268) at slony_logical.c:237
> #10 0x007497d4 in change_cb_wrapper (cache=0x28cbda8, txn=0x28cbec0,
> relation=0x7f787a3446a8, change=0x7f786ffe3268) at logical.c:704
>
>
>
> Here is what the code in lookupSlonyInfo is doing
> --
>
> sltable_oid = get_relname_relid("sl_table",slony_namespace);
>
> sltable_rel = relation_open(sltable_oid,AccessShareLock);
> tupdesc=RelationGetDescr(sltable_rel);
> scandesc=heap_beginscan(sltable_rel,
> GetCatalogSnapshot(sltable_oid),0,NULL);
> reloid_attnum = get_attnum(sltable_oid,"tab_reloid");
>
> if(reloid_attnum == InvalidAttrNumber)
> elog(ERROR,"sl_table does not have a tab_reloid column");
> set_attnum = get_attnum(sltable_oid,"tab_set");
>
> if(set_attnum == InvalidAttrNumber)
> elog(ERROR,"sl_table does not have a tab_set column");
> tableid_attnum = get_attnum(sltable_oid, "tab_id");
>
> if(tableid_attnum == InvalidAttrNumber)
> elog(ERROR,"sl_table does not have a tab_id column");
>
> while( (tuple = heap_getnext(scandesc,ForwardScanDirection) ))
(Except missing spaces ;)) I don't see anything obviously wrong with
this.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "snapshot too large" error when initializing logical replication (9.4)
Hi,
On 2014-10-25 18:09:36 -0400, Steve Singer wrote:
> I sometimes get the error "snapshot too large" from my logical replication
> walsender process when in response to a CREATE_REPLICATION_SLOT.
Yes. That's possible if 'too much' was going on until a consistent point
was reached. I think we can just use a much larger size for the array
if necessary.
I've attached patch for this. Could you try whether that helps? I don't
have a testcase handy that reproduces the problem.
> This is in SnapBuildExportSnapshot in snapbuild.c
>
> newxcnt is 212 at that point
>
> I have max_connections = 200
>
> procArray->maxProcs=212
>
> Should we be testing
> newxcnt > GetMaxSnapshotXidCount()
>
> instead of
> newxcnt >= GetMaxSnapshotXidCount()
It actually looks correct to me new - newxcnt is used as an offset into
an array of size GetMaxSnapshotXidCount().
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/src/backend/replication/logical/snapbuild.c b/src/backend/replication/logical/snapbuild.c
index 5e59c6b..2df1905 100644
--- a/src/backend/replication/logical/snapbuild.c
+++ b/src/backend/replication/logical/snapbuild.c
@@ -519,6 +519,7 @@ SnapBuildExportSnapshot(SnapBuild *builder)
TransactionId xid;
TransactionId *newxip;
int newxcnt = 0;
+ int newxcnt_max;
if (builder->state != SNAPBUILD_CONSISTENT)
elog(ERROR, "cannot export a snapshot before reaching a consistent state");
@@ -557,8 +558,9 @@ SnapBuildExportSnapshot(SnapBuild *builder)
MyPgXact->xmin = snap->xmin;
/* allocate in transaction context */
+ newxcnt_max = GetMaxSnapshotXidCount();
newxip = (TransactionId *)
- palloc(sizeof(TransactionId) * GetMaxSnapshotXidCount());
+ palloc(sizeof(TransactionId) * newxcnt_max);
/*
* snapbuild.c builds transactions in an "inverted" manner, which means it
@@ -579,8 +581,11 @@ SnapBuildExportSnapshot(SnapBuild *builder)
if (test == NULL)
{
- if (newxcnt >= GetMaxSnapshotXidCount())
-elog(ERROR, "snapshot too large");
+ if (newxcnt >= newxcnt_max)
+ {
+newxcnt_max *= 2;
+newxip = repalloc(newxip, sizeof(TransactionId) * newxcnt_max);
+ }
newxip[newxcnt++] = xid;
}
--
Sent 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: Access method extendability
On 2014-10-28 13:06:52 -0400, Tom Lane wrote: > Stephen Frost writes: > > * Andres Freund (and...@2ndquadrant.com) wrote: > >> The other thing I'm not sure about is that I'm unconvinced that we > >> really want external AMs... > > > I was wondering about this also and curious as to if there's been any > > prior on-list discussion about this proposal that I've simply missed..? > > We've touched on the issue a few times, but I don't think there's been > any attempt to define a project policy about it. > > My own thought is that allowing external AMs is simply a natural > consequence of PG's general approach to extensibility, and it would > be surprising if we were to decide we didn't want to allow that. It'd be entirely politicial. I agree. I'm pretty unhappy with the thought that we end up with several 'for pay' index ams out there. But then, PG is BSD style licensed. What I think we need to make absolutely sure is that we preserve the freedom to tinker with the AM functions. I think we'll very heavily curse ourselves if we can't as easily add new features there anymore. > But having said that, it's quite unclear to me that we need the > CREATE/DROP ACCESS METHOD infrastructure proposed here. The traditional > theory about that is that if you're competent to develop an AM at all, > you can certainly manage to insert a row into pg_am manually. The problem with doing that is that you not only need to add a row in pg_am, but also pg_depend. And a way to remove that row when the respective extension is dropped. Especially the latter imo changed the landscape a fair bit. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
Stephen Frost writes: > * Andres Freund (and...@2ndquadrant.com) wrote: >> The other thing I'm not sure about is that I'm unconvinced that we >> really want external AMs... > I was wondering about this also and curious as to if there's been any > prior on-list discussion about this proposal that I've simply missed..? We've touched on the issue a few times, but I don't think there's been any attempt to define a project policy about it. My own thought is that allowing external AMs is simply a natural consequence of PG's general approach to extensibility, and it would be surprising if we were to decide we didn't want to allow that. But having said that, it's quite unclear to me that we need the CREATE/DROP ACCESS METHOD infrastructure proposed here. The traditional theory about that is that if you're competent to develop an AM at all, you can certainly manage to insert a row into pg_am manually. I'm afraid that we'd be adopting and maintaining thousands of lines of code that won't ever come close to pulling their weight in usefulness, or probably ever be fully debugged. (The submitted patch is about 1K lines in itself, and it doesn't appear to address any of the consequences of establishing an expectation that AMs are something that can be dropped or modified. Can you say "cache flush"?) So I'd be inclined to put that part of the patch on the back burner until there are actually multiple externally maintained AMs that could use it. Even then, I'm not sure we want to buy into DROP ACCESS METHOD. I think we *do* need some credible method for extensions to emit WAL records, though. I've not taken any close look at the code proposed for that, but the two-sentence design proposal in the original post sounded plausible as far as it went. So my vote is to pursue the WAL extensibility part of this, but not the additional SQL commands. As for the proposed contrib module, we don't need it to test the WAL extensibility stuff: we could just rewrite some existing core code to emit the "extensible" WAL records instead of whatever it's emitting now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 14:22, Simon Riggs wrote: > Or put it another way, it will be easier to write new index AMs > because we'll be able to skip the WAL part until we know we want it. To be clear: I am suggesting you do *less* work, not more. By allowing AMs to avoid writing WAL we get * higher performance unlogged indexes * we get fewer bugs in early days of new AMs * writers of new AMs are OK to avoid majority of hard work and hard testing So overall, we get new AMs working faster because we can skip writing the WAL code until we are certain the new AM code is useful and bug free. For example, if GIN had avoided implementing WAL it would have been easier to change on-disk representation. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 16:19, Stephen Frost wrote: > Would be happy to go back and review earlier discussions, of course, but > I don't recall there being any. It depends how far back you go. I think I've had at least 2 tries at writing something, but not in last 5 years. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
* Andres Freund (and...@2ndquadrant.com) wrote: > The other thing I'm not sure about is that I'm unconvinced that we > really want external AMs... I was wondering about this also and curious as to if there's been any prior on-list discussion about this proposal that I've simply missed..? Would be happy to go back and review earlier discussions, of course, but I don't recall there being any. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WIP: Access method extendability
On Tue, Oct 28, 2014 at 7:57 PM, Simon Riggs wrote: > On 28 October 2014 14:53, Robert Haas wrote: > > On Tue, Oct 28, 2014 at 10:22 AM, Simon Riggs > wrote: > >> Or put it another way, it will be easier to write new index AMs > >> because we'll be able to skip the WAL part until we know we want it. > > > > I like the feature you are proposing, but I don't think that we should > > block Alexander from moving forward with a more-extensible WAL format. > > I believe that's a general need even if we get the features you're > > proposing, which would reduce the need for it. After all, if somebody > > builds an out-of-core index AM, ignoring WAL-logging, and then decides > > that it works well enough that they want to add WAL-logging, I think > > we should make that possible without requiring them to move the whole > > thing in-core. > > I'm not proposing an alternate or additional feature. > > I'm saying that the first essential step in adding WAL support to new > AMs is to realise that they *will* have bugs (since with the greatest > respect, the last two AMs from our friends did have multiple bugs) and > so we must have a mechanism that prevents such bugs from screwing > everything else up. Which is the mark-corrupt-index and rebuild > requirement. > > We skip straight to the add-buggy-AMs part at our extreme peril. We've > got about 10x as many users now since the 8.x bugs and all the new > users like the reputation Postgres has for resilience. I think we > should put the safety net in place first before we start to climb. > > agree and we thought about this > The patch as submitted doesn't have any safety checks for whether the > WAL records refer to persistent objects defined by the AM. At the very > least we need to be able to isolate an AM to only screw up their own > objects. Such checks look like they'd require some careful thought and > refactoring first. > the patch Alexander submitted is the PoC, we wanted to hear developers opinion and I see no principal objection to work in this direction and we'll continue to work on all possible issues. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] alter user/role CURRENT_USER
* Kevin Grittner (kgri...@ymail.com) wrote: > Stephen Frost wrote: > > > You can still double-quote reserved words and use them in general. What > > we're talking about here are cases where a word can't be used even if > > it's double-quoted, and we try really hard to keep those cases at an > > absolute minimum. We should also really be keeping a list of those > > cases somewhere, now that I think about it.. > > It is very important that a quoted identifier not be treated as a > keyword. I would be very interested in seeing that list, and in > ensuring that it doesn't get any longer. It's object specific and not handled through the grammar, so that gets pretty annoying. :/ The ones I could find by a quick look through backend/commands are: roles public none schemas pg_* operator => (throws a warning at least) There may be other cases that my quick review didn't find, of course. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WIP: Access method extendability
Hi, On 2014-10-15 16:08:38 +0400, Alexander Korotkov wrote: > Postgres was initially designed to support access methods extendability. > This extendability lives to present day. However, this is mostly internal > in-core extendability. One can quite easily add new access method into > PostgreSQL core. But if one try to implement access method as external > module, he will be faced with following difficulties: > >1. Need to directly insert into pg_am, because of no "CREATE ACCESS >METHOD" command. And no support of dependencies between am and opclasses >etc. >2. Module can't define xlog records. So, new am would be not WAL-logged. > > The first problem is purely mechanical. Nothing prevents us to implement > "CREATE ACCESS METHOD" and "DROP ACCESS METHOD" commands and support all > required dependencies. > > Problem of WAL is a bit more complex. According to previous discussions, we > don't want to let extensions declare their own xlog records. If we let them > then recovery process will depend on extensions. That is much violates > reliability. Solution is to implement some generic xlog record which is > able to represent difference between blocks in some general manner. I think this is a somewhat elegant way to attack this problem. But I'm not so sure it's actually sufficient. Consider e.g. how to deal with hot standby conflicts? How would you transport the knowledge that there's a xid conflict to the client? I guess my question essentially is whether it's actually sufficient for real world AMs. The other thing I'm not sure about is that I'm unconvinced that we really want external AMs... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Access method extendability
On 28 October 2014 14:53, Robert Haas wrote: > On Tue, Oct 28, 2014 at 10:22 AM, Simon Riggs wrote: >> Or put it another way, it will be easier to write new index AMs >> because we'll be able to skip the WAL part until we know we want it. > > I like the feature you are proposing, but I don't think that we should > block Alexander from moving forward with a more-extensible WAL format. > I believe that's a general need even if we get the features you're > proposing, which would reduce the need for it. After all, if somebody > builds an out-of-core index AM, ignoring WAL-logging, and then decides > that it works well enough that they want to add WAL-logging, I think > we should make that possible without requiring them to move the whole > thing in-core. I'm not proposing an alternate or additional feature. I'm saying that the first essential step in adding WAL support to new AMs is to realise that they *will* have bugs (since with the greatest respect, the last two AMs from our friends did have multiple bugs) and so we must have a mechanism that prevents such bugs from screwing everything else up. Which is the mark-corrupt-index and rebuild requirement. We skip straight to the add-buggy-AMs part at our extreme peril. We've got about 10x as many users now since the 8.x bugs and all the new users like the reputation Postgres has for resilience. I think we should put the safety net in place first before we start to climb. The patch as submitted doesn't have any safety checks for whether the WAL records refer to persistent objects defined by the AM. At the very least we need to be able to isolate an AM to only screw up their own objects. Such checks look like they'd require some careful thought and refactoring first. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
Robert, > Given that no fewer than four people - all committers - have expressed > doubts about the design of this patch, I wonder why you're bothering > to post a new version. I understand and my intent was in no way to disregard those concerns. The only reason that I have posted a new version was simply to address some minor issues that I noticed when responding to Peter's earlier comment about missing files. It seems to me that you should be discussing > the fundamental design, not making minor updates to the code. Ok. I'm certainly looking at the other options proposed and will work with Stephen to put together an appropriate design for discussion here. I really hope this is not moving in the direction of another "surprise > commit" like we had with RLS. There is absolutely NOT consensus on > this design or anything close to it. Certainly not and I am in no way confused that consensus has not been reached. -Adam -- Adam Brightwell - adam.brightw...@crunchydatasolutions.com Database Engineer - www.crunchydatasolutions.com
Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes
>>>Do we release the buffers for compressed data when fpw is changed from "compress" to "on"? >> The current code does not do this. >Don't we need to do that? Yes this needs to be done in order to avoid memory leak when compression is turned off at runtime while the backend session is running. >You don't need to make the processes except the startup process allocate >the memory for uncompressedPages when fpw=on. Only the startup process >uses it for the WAL decompression I see. fpw != on check can be put at the time of memory allocation of uncompressedPages in the backend code . And at the time of recovery uncompressedPages can be allocated separately if not already allocated. >BTW, what happens if the memory allocation for uncompressedPages for >the recovery fails? The current code does not handle this. This will be rectified. >Which would prevent the recovery at all, so PANIC should >happen in that case? IIUC, instead of reporting PANIC , palloc can be used to allocate memory for uncompressedPages at the time of recovery which will throw ERROR and abort startup process in case of failure. Thank you, Rahila Syed -- View this message in context: http://postgresql.1045698.n5.nabble.com/Compression-of-full-page-writes-tp5769039p5824613.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
* Robert Haas (robertmh...@gmail.com) wrote: > On Tue, Oct 28, 2014 at 9:24 AM, Stephen Frost wrote: > > That said, it sounds like the primary concern has been if we want this > > feature at all and there hasn't been much discussion of the design > > itself. Comments about the technical design would be great. I > > appreciate your thoughts about using a PGC_SUSER GUC, but I don't feel > > like it really works as it's all-or-nothing and doesn't provide > > read-vs-write, unless we extend it out to be multiple GUCs and then > > there is still the question about per-role access.. > > It sounds to me like you've basically settled on the way that you want > to implement it - without prior discussion on the mailing list - and > you're not trying very hard to make any of the alternatives work. I'm happy to put more effort into alternatives, was just trying to outline what capabilities I felt it should have and make sure others proposing designs understood the granularity requested. > It's not the community's job to come up with a design that satisfies > you; it's your job to come up with as design that satisfies the > community. That doesn't *necessarily* mean that you have to change > the design that you've come up with; convincing other people that your > design is the best one is also an option. But I don't see that you're > making any real attempt to do that. There was only one other design to contrast against- the rest has been concern about the desirability, which is what I've been trying to address by responding to Peter's request about documentation and how this capability exists in other systems. > Your previous comment on the idea of a PGC_SUSET GUC was "Hrm, perhaps > this would work though.." and then, with zero further on-list > discussion, you've arrived at "I don't feel like it really works as > it's all-or-nothing and doesn't provide read-vs-write". Those are > precisely the kinds of issues that you should be discussing here in > detail, not cogitating on in isolation and then expecting this group > of people to accept that your original design is really for the best > after all. Alright, I'll try and outline a more detailed proposal which uses GUCs to achieve the level of granularity that is being sought and we can discuss it. > I also find your technical arguments - to the extent that you've > bothered to articulate them at all - to be without merit. The > "question about per-role access" is easily dealt with, so let's start > there: if you make it a GUC, ALTER USER .. SET can be used to set > different values for different users. No problem. No, I simply hadn't thought about that approach and I'm glad that you're clarifying it.. I'll think about it more but my initial concern is being able to identify everything a user has access to would then become more complex as you'd have to consider what special GUCs they have set in pg_config. I see how what you're proposing would work there though. > Your other > criticism that it is "all-vs-nothing" seems to me to be totally > incomprehensible, since as far as I can see a GUC with a list of > pathnames is exactly the same functionality that you're proposing to > implement via a much more baroque syntax. It is no more or less > all-or-nothing than that. Apologies about not being clear- that 'all-or-nothing' was without considering using a per-user GUC to control it; I had thought the proposal was a single GUC and then a role attribute which said if a given role could access everything in the global list or not. Using a per-role GUC solves that. > Finally, you mention "read-vs-write" > access. You haven't even attempted to argue that we need to make that > distinction The use-case that I had described up-thread, I had thought, made it clear that there will be cases where a user should have only read-only access to a directory (able to import log files) and cases where a user should be able to write to a directory (exporting to an NFS mount or similar). > - in fact, you don't seem to have convinced a > significantly majority of the people that we need this feature at all That's certainly what I've been primairly focused on addressing as it's the first hurdle to jump. As I mentioned to Bruce, I didn't realize there was really a question about that, but evidently that was incorrect and I'm working to rectify the situation. > - but if we do, the fact that it might require two GUCs instead of one > is not a fatal objection to that design. (I'd be prepared to concede > that if there are half a dozen different privileges on directories > that we might want to grant, then wedging it into a GUC might be a > stretch.) There are more capabilities that I've been considering longer-term but wasn't sure if they should be independent or just lumped into the simpler read/write category: read (eg: importing log files, or importing from an NFS mount) write (eg: exporting to NFS mount) tablespace (eg: create a tablespace in a subdir of a dire
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 04:01 PM, Heikki Linnakangas wrote: Moving on to other issues, isn't 128 bits too small to store the squares of the processed numbers? That could overflow.. Yeah, which is why stddev_*(int8) and var_*(int8) still have to use Numeric in the aggregate state. For the int2 and int4 versions it is fine to use __int128_t. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 04:47 PM, Andreas Karlsson wrote: On 10/28/2014 03:40 PM, Heikki Linnakangas wrote: The patch doesn't do division with the 128-bit integers. It only does addition and multiplication. Those are pretty straightforward to implement. The patch uses division when converting from __int128_t to Numeric. Oh, I see. Hmph, looks like I'm losing an argument.. Moving on to other issues, isn't 128 bits too small to store the squares of the processed numbers? That could overflow.. - 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] alter user/role CURRENT_USER
On Tue, Oct 28, 2014 at 2:40 AM, Adam Brightwell wrote: > Taking a step back, I'm still not sure I understand the need for this > feature or the use case. It seems to have started as a potential fix to an > inconsistency between ALTER USER and ALTER ROLE syntax (which I think I > could see some value in). However, I think it has been taken beyond just > resolving the inconsistency and started to cross over into feature creep. > Is the intent simply to resolve inconsistencies between what is now an alias > of another command? Or is it to add new functionality? I think the > original proposal needs to be revisited and more time needs to be spent > defining the scope and purpose of this patch. +1. I've been reading this thread with some bemusement, but couldn't find a way articulate what you just said nearly as well as you just said it. -- 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] WIP: Access method extendability
On Tue, Oct 28, 2014 at 10:22 AM, Simon Riggs wrote: > Or put it another way, it will be easier to write new index AMs > because we'll be able to skip the WAL part until we know we want it. I like the feature you are proposing, but I don't think that we should block Alexander from moving forward with a more-extensible WAL format. I believe that's a general need even if we get the features you're proposing, which would reduce the need for it. After all, if somebody builds an out-of-core index AM, ignoring WAL-logging, and then decides that it works well enough that they want to add WAL-logging, I think we should make that possible without requiring them to move the whole thing in-core. -- 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] alter user/role CURRENT_USER
Stephen Frost wrote: > You can still double-quote reserved words and use them in general. What > we're talking about here are cases where a word can't be used even if > it's double-quoted, and we try really hard to keep those cases at an > absolute minimum. We should also really be keeping a list of those > cases somewhere, now that I think about it.. It is very important that a quoted identifier not be treated as a keyword. I would be very interested in seeing that list, and in ensuring that it doesn't get any longer. > I agree that we should probably seperate the concerns here. Personally, > I like the idea of being able to say "CURRENT_USER" in utility commands > to refer to the current user where a role would normally be expected, as > I could see it simplifying things for some applications, but that's a > new feature and independent of making role-vs-user cases more > consistent. Yeah, let's not mix those in the same patch. -- 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] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 03:40 PM, Heikki Linnakangas wrote: The patch doesn't do division with the 128-bit integers. It only does addition and multiplication. Those are pretty straightforward to implement. The patch uses division when converting from __int128_t to Numeric. - Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
On Tue, Oct 28, 2014 at 9:24 AM, Stephen Frost wrote: > That said, it sounds like the primary concern has been if we want this > feature at all and there hasn't been much discussion of the design > itself. Comments about the technical design would be great. I > appreciate your thoughts about using a PGC_SUSER GUC, but I don't feel > like it really works as it's all-or-nothing and doesn't provide > read-vs-write, unless we extend it out to be multiple GUCs and then > there is still the question about per-role access.. It sounds to me like you've basically settled on the way that you want to implement it - without prior discussion on the mailing list - and you're not trying very hard to make any of the alternatives work. It's not the community's job to come up with a design that satisfies you; it's your job to come up with as design that satisfies the community. That doesn't *necessarily* mean that you have to change the design that you've come up with; convincing other people that your design is the best one is also an option. But I don't see that you're making any real attempt to do that. Your previous comment on the idea of a PGC_SUSET GUC was "Hrm, perhaps this would work though.." and then, with zero further on-list discussion, you've arrived at "I don't feel like it really works as it's all-or-nothing and doesn't provide read-vs-write". Those are precisely the kinds of issues that you should be discussing here in detail, not cogitating on in isolation and then expecting this group of people to accept that your original design is really for the best after all. I also find your technical arguments - to the extent that you've bothered to articulate them at all - to be without merit. The "question about per-role access" is easily dealt with, so let's start there: if you make it a GUC, ALTER USER .. SET can be used to set different values for different users. No problem. Your other criticism that it is "all-vs-nothing" seems to me to be totally incomprehensible, since as far as I can see a GUC with a list of pathnames is exactly the same functionality that you're proposing to implement via a much more baroque syntax. It is no more or less all-or-nothing than that. Finally, you mention "read-vs-write" access. You haven't even attempted to argue that we need to make that distinction - in fact, you don't seem to have convinced a significantly majority of the people that we need this feature at all - but if we do, the fact that it might require two GUCs instead of one is not a fatal objection to that design. (I'd be prepared to concede that if there are half a dozen different privileges on directories that we might want to grant, then wedging it into a GUC might be a stretch.) -- 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] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 04:06 PM, Tom Lane wrote:
Heikki Linnakangas writes:
It wouldn't be too hard to just do:
struct {
int64 high_bits;
uint64 low_bits;
} pg_int128;
and some macros for the + - etc. operators. It might be less work than
trying to deal with the portability issues of a native C datatype for this.
-1. That's not that easy, especially for division, or if you want to
worry about overflow.
The patch doesn't do division with the 128-bit integers. It only does
addition and multiplication. Those are pretty straightforward to implement.
The point of this patch IMO is to get some low
hanging fruit; coding our own int128 arithmetic doesn't sound like
"low hanging" to me.
I wasn't thinking of writing a full-fledged 128-bit type, just the the
few operations needed for this patch.
Also, we've already got the configure infrastructure for detecting
whether a platform has working int64. It really shouldn't be much
work to transpose that to int128 (especially if we don't care about
printf support, which I think we don't).
It would be nicer to be able to use the same code on all platforms. With
a configure test, we'd still need a fallback implementation for
platforms that don't have it.
- 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] WIP: Access method extendability
On 15 October 2014 13:08, Alexander Korotkov wrote: > Postgres was initially designed to support access methods extendability. > This extendability lives to present day. However, this is mostly internal > in-core extendability. One can quite easily add new access method into > PostgreSQL core. But if one try to implement access method as external > module, he will be faced with following difficulties: ... > Problem of WAL is a bit more complex. According to previous discussions, we > don't want to let extensions declare their own xlog records. If we let them > then recovery process will depend on extensions. That is much violates > reliability. Solution is to implement some generic xlog record which is able > to represent difference between blocks in some general manner. Thank you for progressing with these thoughts. I'm still a little uncertain about the approach, now my eyes are open to the problems of extendability. The main problem we had in the past was that GiST and GIN indexes both had faulty implementations for redo, which in some cases caused severe issues. Adding new indexes will also suffer the same problems, so I see a different starting place. The faults there raised the need for us to be able to mark specific indexes as corrupt, so that they could be avoided during Hot Standby and in normal running after promotion. Here's the order of features I think we need 1. A mechanism to mark an index as corrupt so that it won't be usable by queries. That needs to work during recovery, so we can persist a data structure which tells us which indexes are corrupt. Then something that checks whether an index is known corrupt during relcache access. So if we decide an index is bad, we record the index as corrupt and then fire a relcache invalidation. 2. Some additional code in Autovacuum to rebuild corrupt indexes at startup, using AV worker processes to perform a REINDEX CONCURRENTLY. This will give us what we need to allow an AM to behave sensibly, even in the face of its own bugs. It also gives us UNLOGGED indexes for free. Unlogged indexes means we can change the way unlogged tables behave to allow them to truncate down to the highest unchanged data at recovery, so we don't lose all the data when we crash. 3. That then allows us to move towards having indexes that are marked "changed" when we perform first DML on the table in any checkpoint cycle. Which allows us to rebuild indexes which were in the middle of being changed when we crashed. (The way we'd do that is to have an LSN on the metapage and then only write WAL for the metapage). The difference here is that they are UNLOGGED but do not get trashed on recovery unless they were in the process of changing. If we do those things, then we won't even need to worry about needing AMs to write their own WAL records. Recovery will be safe AND we won't need to go through problems of buggy persistence implementations in new types of index. Or put it another way, it will be easier to write new index AMs because we'll be able to skip the WAL part until we know we want it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deferring some AtStart* allocations?
On 2014-10-24 11:25:23 -0400, Robert Haas wrote: > On Fri, Oct 24, 2014 at 10:10 AM, Andres Freund > wrote: > > What I was thinking was that you'd append the messages to the layer one > > level deeper than the parent. Then we'd missed the invalidations when > > rolling back the intermediate xact. But since I was quite mistaken > > above, this isn't a problem :) > > So, you happy with the patch now? Yes. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
Heikki Linnakangas writes:
> It wouldn't be too hard to just do:
> struct {
> int64 high_bits;
> uint64 low_bits;
> } pg_int128;
> and some macros for the + - etc. operators. It might be less work than
> trying to deal with the portability issues of a native C datatype for this.
-1. That's not that easy, especially for division, or if you want to
worry about overflow. The point of this patch IMO is to get some low
hanging fruit; coding our own int128 arithmetic doesn't sound like
"low hanging" to me.
Also, we've already got the configure infrastructure for detecting
whether a platform has working int64. It really shouldn't be much
work to transpose that to int128 (especially if we don't care about
printf support, which I think we don'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] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 2014-10-28 15:54:30 +0200, Heikki Linnakangas wrote:
> On 10/28/2014 03:24 PM, Andres Freund wrote:
> >On 2014-10-28 11:05:11 -0200, Arthur Silva wrote:
> >>On Sat, Oct 25, 2014 at 12:38 PM, Andreas Karlsson
> >>As far as I'm aware int128 types are supported on every major compiler when
> >>compiling for 64bit platforms. Right?
> >
> >Depends on what you call major. IIRC some not that old msvc versions
> >don't for example. Also, there's a couple 32 platforms with int128 bit
> >support. So I think we should just add a configure test defining the
> >type + a feature macro.
>
> It wouldn't be too hard to just do:
>
> struct {
> int64 high_bits;
> uint64 low_bits;
> } pg_int128;
>
> and some macros for the + - etc. operators. It might be less work than
> trying to deal with the portability issues of a native C datatype for this.
And noticeably slower. At least x86-64 does all of this in hardware...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superuser() shortcuts
* Andres Freund (and...@2ndquadrant.com) wrote: > For one I'm less than convinced that the new messages are an > improvement. They seem to be more verbose without a corresponding > improvement in clarity. The goal with the changes is to improve consistency of messaging. These messages are not at all consistent today. Personally, I like the idea of being clear in the main errmsg() that these are permission denied errors, but we could go the other way and change all the existing messages which say 'permission denied' to only say 'you have to be superuser or have X' instead and expect folks to realize it's a permission denied error from the SQL error code.. > For another I don't see any need to rush this into 9.4. Ok. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 03:24 PM, Andres Freund wrote:
On 2014-10-28 11:05:11 -0200, Arthur Silva wrote:
On Sat, Oct 25, 2014 at 12:38 PM, Andreas Karlsson
As far as I'm aware int128 types are supported on every major compiler when
compiling for 64bit platforms. Right?
Depends on what you call major. IIRC some not that old msvc versions
don't for example. Also, there's a couple 32 platforms with int128 bit
support. So I think we should just add a configure test defining the
type + a feature macro.
It wouldn't be too hard to just do:
struct {
int64 high_bits;
uint64 low_bits;
} pg_int128;
and some macros for the + - etc. operators. It might be less work than
trying to deal with the portability issues of a native C datatype for this.
- 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] jsonb generator functions
On 10/27/2014 05:57 PM, Alvaro Herrera wrote:
Andrew Dunstan wrote:
This bit:
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type->JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid)
+{
seems like it can return without having set the category and func OID,
if there's no available cast. Callers don't seem to check for this
condition; is this a bug? If not, why not? Maybe some extra comments
are warranted.
Umm, no. The outfuncoid is set by the call to getTypeOutputInfo() and
the category is set by every branch of the switch. We override the
funcoid in the case where there's a cast to json or jsonb.
I'll add a comment to that effect.
Right now, for the "general case" there, there are two syscache lookups
rather than one. The fix is simple: just do the getTypeOutputInfo call
inside each case inside the switch instead of once at the beginning, so
that the general case can omit it; then there is just one syscache
access in all the cases. json.c suffers from the same problem.
We only do more than one if it's not a builtin type, or an array or
composite. So 99% of the time this won't even be called.
Anyway this whole business of searching through the CASTSOURCETARGET
syscache seems like it could be refactored. If I'm counting correctly,
that block now appears four times (three in this patch, once in json.c).
Can't we add a new function to (say) lsyscache and remove that?
Twice, not three times in this patch, unless I'm going crazier than I
thought.
I can add a function to lsyscache along the lines of
Oid get_cast_func(Oid from_type, Oid to_type)
if you think it's worth it.
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] superuser() shortcuts
On 2014-10-28 09:43:35 -0400, Stephen Frost wrote: > All, > > * Stephen Frost (sfr...@snowman.net) wrote: > > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > > > > As I started looking at this, there are multiple other places where > > > > these types of error messages occur (opclasscmds.c, user.c, > > > > postinit.c, miscinit.c are just a few), not just around the changes in > > > > this patch. If we change them in one place, wouldn't it be best to > > > > change them in the rest? If that is the case, I'm afraid that might > > > > distract from the purpose of this patch. Perhaps, if we want to > > > > change them, then that should be submitted as a separate patch? > > > > > > Yeah. I'm just saying that maybe this patch should adopt whatever > > > wording we agree to, not that we need to change other places. On the > > > other hand, since so many other places have adopted the different > > > wording, maybe there's a reason for it and if so, does anybody know what > > > it is. But I have to say that it does look inconsistent to me. > > > > Updated patch attached. Comments welcome. > > Looking over this again, I had another thought about it- given that this > changes the error messages returned for replication slots, which are new > in 9.4, should it be back-patched to 9.4? Otherwise we'll put 9.4 > out and then immediately change these error messages in 9.5. -1. For one I'm less than convinced that the new messages are an improvement. They seem to be more verbose without a corresponding improvement in clarity. For another I don't see any need to rush this into 9.4. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BUG: *FF WALs under 9.2 (WAS: .ready files appearing on slaves)
On 10/27/2014 06:12 PM, Heikki Linnakangas wrote: On 10/27/2014 02:12 PM, Fujii Masao wrote: >On Fri, Oct 24, 2014 at 10:05 PM, Heikki Linnakangas > wrote: >>On 10/23/2014 11:09 AM, Heikki Linnakangas wrote: >>> >>>At least for master, we should consider changing the way the archiving >>>works so that we only archive WAL that was generated in the same server. >>>I.e. we should never try to archive WAL files belonging to another >>>timeline. >>> >>>I just remembered that we discussed a different problem related to this >>>some time ago, at >>> >>>http://www.postgresql.org/message-id/20131212.110002.204892575.horiguchi.kyot...@lab.ntt.co.jp. >>>The conclusion of that was that at promotion, we should not archive the >>>last, partial, segment from the old timeline. >> >> >>So, this is what I came up with for master. Does anyone see a problem with >>it? > >What about the problem that I raised upthread? This is, the patch >prevents the last, partial, WAL file of the old timeline from being archived. >So we can never PITR the database to the point that the last, partial WAL >file has. A partial WAL file is never archived in the master server to begin with, so if it's ever used in archive recovery, the administrator must have performed some manual action to copy the partial WAL file from the original server. When he does that, he can also copy it manually to the archive, or whatever he wants to do with it. Note that the same applies to any complete, but not-yet archived WAL files. But we've never had any mechanism in place to archive those in the new instance, after PITR. Actually, I'll take back what I said above. I had misunderstood the current behavior. Currently, a server *does* archive any files that you copy manually to pg_xlog, after PITR has finished. Eventually. We don't create a .ready file for them until they're old enough to be recycled. We do create a .ready file for the last, partial, segment, but it's pretty weird to do it just for that, and not any other, complete, segments that might've been copied to pg_xlog. So what happens is that the last partial segment gets archived immediately after promotion, but any older segments will linger unarchived until much later. The special treatment of the last partial segment still makes no sense. If we want the segments from the old timeline to be archived after PITR, we should archive them all immediately after end of recovery, not just the partial one. The exception for just the last partial segment is silly. Now, the bigger question is whether we want the server after PITR to be responsible for archiving the segments from the old timeline at all. If we do, then we should remove the special treatment of the last, partial segment, and create the .ready files for all the complete segments too. And actually, I think we should *not* archive the partial segment. We don't normally archive partial segments, and all the WAL required to restore the server to new timeline is copied to the file with the new TLI. If the old timeline is still live, i.e. there's a server somewhere still writing new WAL on the old timeline, the partial segment will clash with a complete segment that the other server will archive later. Yet another consideration is that we currently don't archive files streamed from the master. If we think that the standby server is responsible for archiving old segments after recovery, why is it not responsible for archiving the streamed segments? It's because in most cases, the master will archive the file, and we don't want two servers to archive the same file, but there is actually no guarantee on that. It might well be that the archiver runs a little bit behind in the master, and after crash the archive will miss some of the segments required. That's not good either. I'm not sure what to do here. The current behavior is inconsistent, and there are a some nasty gotchas that would be nice to fix. I think someone needs to sit down and write a high-level design of how this all should work. - 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] superuser() shortcuts
All, * Stephen Frost (sfr...@snowman.net) wrote: > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > > > As I started looking at this, there are multiple other places where > > > these types of error messages occur (opclasscmds.c, user.c, > > > postinit.c, miscinit.c are just a few), not just around the changes in > > > this patch. If we change them in one place, wouldn't it be best to > > > change them in the rest? If that is the case, I'm afraid that might > > > distract from the purpose of this patch. Perhaps, if we want to > > > change them, then that should be submitted as a separate patch? > > > > Yeah. I'm just saying that maybe this patch should adopt whatever > > wording we agree to, not that we need to change other places. On the > > other hand, since so many other places have adopted the different > > wording, maybe there's a reason for it and if so, does anybody know what > > it is. But I have to say that it does look inconsistent to me. > > Updated patch attached. Comments welcome. Looking over this again, I had another thought about it- given that this changes the error messages returned for replication slots, which are new in 9.4, should it be back-patched to 9.4? Otherwise we'll put 9.4 out and then immediately change these error messages in 9.5. That said, it seems likely we'll be doing a more thorough review and update of error messages for 9.5 (if others agree with my up-thread proposal), such that these changes would be minor additional ones. Thoughts? I don't have a preference either way, which makes me lean towards not messing with 9.4, but wanted to bring it up. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
* Andres Freund (and...@2ndquadrant.com) wrote: > On 2014-10-28 09:24:18 -0400, Stephen Frost wrote: > > There is no doubt that consensus on the desirability and design needs > > to be reached before we can even consider committing it. I suspect > > Adam posted it simply because he had identified issues himself and > > wanted to make others aware that things had been fixed. > > > > That said, it sounds like the primary concern has been if we want this > > feature at all and there hasn't been much discussion of the design > > itself. > > Well, why waste time on the technical details when we haven't agreed > that the feature is worthwile? Review bandwidth is a serious problem in > this community. Fair enough, and I'm happy to discuss that (and have been..); I was simply objecting to the implication that the desirability concerns raised were design concerns- the only design concern raised was wrt it being possibly too heavyweight and the PGC_SUSET GUC suggestion (at least, based on my re-reading of the thread..). Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 10/28/2014 02:05 PM, Arthur Silva wrote: As far as I'm aware int128 types are supported on every major compiler when compiling for 64bit platforms. Right? Both gcc and clang support __int128_t, but I do not know about other compilers like icc and MSVC. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
On 2014-10-28 09:24:18 -0400, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: > > There is absolutely NOT consensus on > > this design or anything close to it. > > There is no doubt that consensus on the desirability and design needs > to be reached before we can even consider committing it. I suspect > Adam posted it simply because he had identified issues himself and > wanted to make others aware that things had been fixed. > > That said, it sounds like the primary concern has been if we want this > feature at all and there hasn't been much discussion of the design > itself. Well, why waste time on the technical details when we haven't agreed that the feature is worthwile? Review bandwidth is a serious problem in this community. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On 2014-10-28 11:05:11 -0200, Arthur Silva wrote: > On Sat, Oct 25, 2014 at 12:38 PM, Andreas Karlsson > As far as I'm aware int128 types are supported on every major compiler when > compiling for 64bit platforms. Right? Depends on what you call major. IIRC some not that old msvc versions don't for example. Also, there's a couple 32 platforms with int128 bit support. So I think we should just add a configure test defining the type + a feature macro. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
* Robert Haas (robertmh...@gmail.com) wrote: > There is absolutely NOT consensus on > this design or anything close to it. There is no doubt that consensus on the desirability and design needs to be reached before we can even consider committing it. I suspect Adam posted it simply because he had identified issues himself and wanted to make others aware that things had been fixed. That said, it sounds like the primary concern has been if we want this feature at all and there hasn't been much discussion of the design itself. Comments about the technical design would be great. I appreciate your thoughts about using a PGC_SUSER GUC, but I don't feel like it really works as it's all-or-nothing and doesn't provide read-vs-write, unless we extend it out to be multiple GUCs and then there is still the question about per-role access.. I'm not sure that I see a way to allow the per-role granularity without having a top-level catalog object on which the GRANT can be executed and ACL information stored. Perhaps it's unfortunate that we don't have a more generic way to address that but I'm not sure I really see another catalog table as a big problem.. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On Sat, Oct 25, 2014 at 9:38 AM, Andreas Karlsson wrote: > Hi, > > There was recently talk about if we should start using 128-bit integers > (where available) to speed up the aggregate functions over integers which > uses numeric for their internal state. So I hacked together a patch for this > to see what the performance gain would be. > > Previous thread: > http://www.postgresql.org/message-id/20141017182500.gf2...@alap3.anarazel.de > > What the patch does is switching from using numerics in the aggregate state > to int128 and then convert the type from the 128-bit integer in the final > function. > > The functions where we can make use of int128 states are: > > - sum(int8) > - avg(int8) > - var_*(int2) > - var_*(int4) > - stdev_*(int2) > - stdev_*(int4) > > The initial benchmark results look very promising. When summing 10 million > int8 I get a speedup of ~2.5x and similarly for var_samp() on 10 million > int4 I see a speed up of ~3.7x. To me this indicates that it is worth the > extra code. What do you say? Is this worth implementing? yes. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Directory/File Access Permissions for COPY and Generic File Access Functions
On Mon, Oct 27, 2014 at 5:59 PM, Adam Brightwell wrote: > Attached is a patch with minor updates/corrections. Given that no fewer than four people - all committers - have expressed doubts about the design of this patch, I wonder why you're bothering to post a new version. It seems to me that you should be discussing the fundamental design, not making minor updates to the code. I really hope this is not moving in the direction of another "surprise commit" like we had with RLS. There is absolutely NOT consensus on this design or anything close to it. -- 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] [WIP Patch] Using 128-bit integers for sum, avg and statistics aggregates
On Sat, Oct 25, 2014 at 12:38 PM, Andreas Karlsson wrote: > Hi, > > There was recently talk about if we should start using 128-bit integers > (where available) to speed up the aggregate functions over integers which > uses numeric for their internal state. So I hacked together a patch for > this to see what the performance gain would be. > > Previous thread: http://www.postgresql.org/message-id/20141017182500. > gf2...@alap3.anarazel.de > > What the patch does is switching from using numerics in the aggregate > state to int128 and then convert the type from the 128-bit integer in the > final function. > > The functions where we can make use of int128 states are: > > - sum(int8) > - avg(int8) > - var_*(int2) > - var_*(int4) > - stdev_*(int2) > - stdev_*(int4) > > The initial benchmark results look very promising. When summing 10 million > int8 I get a speedup of ~2.5x and similarly for var_samp() on 10 million > int4 I see a speed up of ~3.7x. To me this indicates that it is worth the > extra code. What do you say? Is this worth implementing? > > The current patch still requires work. I have not written the detection of > int128 support yet, and the patch needs code cleanup (for example: I used > an int16_ prefix on the added functions, suggestions for better names are > welcome). I also need to decide on what estimate to use for the size of > that state. > > The patch should work and pass make check on platforms where __int128_t is > supported. > > The simple benchmarks: > > CREATE TABLE test_int8 AS SELECT x::int8 FROM generate_series(1, 1000) > x; > > Before: > > # SELECT sum(x) FROM test_int8; > sum > > 500500 > (1 row) > > Time: 2521.217 ms > > After: > > # SELECT sum(x) FROM test_int8; > sum > > 500500 > (1 row) > > Time: 1022.811 ms > > CREATE TABLE test_int4 AS SELECT x::int4 FROM generate_series(1, 1000) > x; > > Before: > > # SELECT var_samp(x) FROM test_int4; > var_samp > > 83416.6667 > (1 row) > > Time: 3808.546 ms > > After: > > # SELECT var_samp(x) FROM test_int4; > var_samp > > 83416.6667 > (1 row) > > Time: 1033.243 ms > > Andreas > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > These are some nice improvements. As far as I'm aware int128 types are supported on every major compiler when compiling for 64bit platforms. Right?
Re: [HACKERS] alter user/role CURRENT_USER
* Adam Brightwell (adam.brightw...@crunchydatasolutions.com) wrote:
> > +RoleId:CURRENT_USER{ $$ =
> > "current_user";}
> > + | USER { $$ = "current_user";}
> > + | CURRENT_ROLE { $$ = "current_user";}
> > + | SESSION_USER { $$ = "session_user";}
> >
> > This is kind of ugly, and it means you can't distinguish between a
> > CURRENT_USER keyword and a quoted user name "current_user".
>
> You are right. I'm not sure I have an opinion on how clean it is, but
> FWIW, it is similar to the way that the 'auth_ident' type in the grammar is
> defined (though, not to imply that it makes it right).
No, it's not right and it's an existing problem. :(
=*# create extension postgres_fdw;
CREATE EXTENSION
=# create server s1 foreign data wrapper postgres_fdw ;
CREATE SERVER
=*# create user mapping for "current_user" server s1;
CREATE USER MAPPING
=*# table pg_user_mappings;
-[ RECORD 1 ]-
umid | 24623
srvid | 24622
srvname | s1
umuser| 16384
usename | sfrost
umoptions |
> As well, the
> originally proposed "RoleId_or_curruser" suffers from the same issue. I'm
> going to go out on a limb here, but is it not possible to consider
> "current_user", etc. reserved in the same sense that we do with PUBLIC and
> NONE and disallow users/roles from being created as them?
Maybe we could in some future release, but we can't for back-branches so
I'm afraid we're going to have to figure out how to fix this to work
regardless.
> I mean, after
> all, they *are* already reserved keywords. Perhaps there is a very good
> reason why we wouldn't want to do that and I am sure there is since they
> have not been treated this way thus far. If anyone would like to share
> why, then I'd very much appreciate the "lesson".
You can still double-quote reserved words and use them in general. What
we're talking about here are cases where a word can't be used even if
it's double-quoted, and we try really hard to keep those cases at an
absolute minimum. We should also really be keeping a list of those
cases somewhere, now that I think about it..
> Taking a step back, I'm still not sure I understand the need for this
> feature or the use case. It seems to have started as a potential fix to an
> inconsistency between ALTER USER and ALTER ROLE syntax (which I think I
> could see some value in). However, I think it has been taken beyond just
> resolving the inconsistency and started to cross over into feature creep.
> Is the intent simply to resolve inconsistencies between what is now an
> alias of another command? Or is it to add new functionality? I think the
> original proposal needs to be revisited and more time needs to be spent
> defining the scope and purpose of this patch.
I agree that we should probably seperate the concerns here. Personally,
I like the idea of being able to say "CURRENT_USER" in utility commands
to refer to the current user where a role would normally be expected, as
I could see it simplifying things for some applications, but that's a
new feature and independent of making role-vs-user cases more
consistent.
Thanks!
Stephen
signature.asc
Description: Digital signature
