Re: [HACKERS] inherit support for foreign tables
(2014/03/28 13:28), Kyotaro HORIGUCHI wrote: Hmm. The assertion (not shown above but you put in parameterize_path:) seems to say that 'base relation for foreign paths must be a RTE_RELATION' isn't right? I think that's right. Please see allpaths.c, especially set_rel_pathlist(). For your information, the same assertion can be found in create_foreignscan_plan(). Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] separate output dirs for test decoding pieces.
Hi, On 2014-03-29 13:20:41 -0400, Andrew Dunstan wrote: make check in contrib/test_decoding actually does two regression runs, one with pg_regress and one with pg_isolation_regress. These both use the same (default) outputdir, so one overwrites the other, which is a bit unfortunate from the buildfarm's point of view. I propose to make them use separate outputdirs, via the attached small patch. Comments? Thanks for taking care of this, sounds like a good idea. 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] MultiXactId error after upgrade to 9.3.4
Hi, On 2014-03-30 00:00:30 -0400, Stephen Frost wrote: Greetings, Looks like we might not be entirely out of the woods yet regarding MultiXactId's. After doing an upgrade from 9.2.6 to 9.3.4, we saw the following: ERROR: MultiXactId 6849409 has not been created yet -- apparent wraparound The table contents can be select'd out and match the pre-upgrade backup, but any attempt to VACUUM / VACUUM FULL / CLUSTER fails, unsurprisingly. Without having looked at the code, IIRC this looks like some place misses passing allow_old=true where it's actually required. Any chance you can get a backtrace for the error message? I know you said somewhere below that you'd worked around the problem, but maybe you have a copy of the database somewhere? 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
[HACKERS] _mdfd_getseg can be expensive
Hi, I recently have seen some perf profiles in which _mdfd_getseg() was in the top #3 when VACUUMing large (~200GB) relations. Called by mdread(), mdwrite(). Looking at it's implementation, I am not surprised. It iterates over all segment entries a relations has; for every read or write. That's not painful for smaller relations, but at a couple of hundred GB it starts to be annoying. Especially if kernel readahead has already read in all data from disk. I don't have a good idea what to do about this yet, but it seems like something that should be fixed mid-term. The best I can come up is is caching the last mdvec used, but that's fairly ugly. Alternatively it might be a good idea to not store MdfdVec as a linked list, but as a densely allocated array. 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] B-Tree support function number 3 (strxfrm() optimization)
On 31 March 2014 06:51, Peter Geoghegan p...@heroku.com wrote: On Wed, Mar 26, 2014 at 8:08 PM, Peter Geoghegan p...@heroku.com wrote: The API I envisage is a new support function 3 that operator class authors may optionally provide. I've built a prototype patch, attached, that extends SortSupport and tuplesort to support poor man's normalized keys. All the regression tests pass, so while it's just a proof of concept, it is reasonably well put together for one. The primary shortcoming of the prototype (the main reason why I'm calling it a prototype rather than just a patch) is that it isn't sufficiently generalized (i.e. it only works for the cases currently covered by SortSupport - not B-Tree index builds, or B-Tree scanKeys). There is no B-Tree support function number 3 in the patch. I didn't spend too long on this. I'm pretty happy with the results for in-memory sorting of text (my development system uses 'en_US.UTF8', so please assume that any costs involved are for runs that use that collation). With the dellstore2 sample database [1] restored to my local development instance, the following example demonstrates just how much the technique can help performance. With master: pg@hamster:~/sort-tests$ cat sort.sql select * from (select * from customers order by firstname offset 10) d; pg@hamster:~/sort-tests$ pgbench -f sort.sql -n -T 100 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 819 latency average: 122.100 ms tps = 8.186197 (including connections establishing) tps = 8.186522 (excluding connections establishing) With patch applied (requires initdb for new text SortSupport pg_proc entry): pg@hamster:~/sort-tests$ cat sort.sql select * from (select * from customers order by firstname offset 10) d; pg@hamster:~/sort-tests$ pgbench -f sort.sql -n -T 100 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 2525 latency average: 39.604 ms tps = 25.241723 (including connections establishing) tps = 25.242447 (excluding connections establishing) As another data point, I ran the same benchmark, but I don't appear to yield the same positive result. An initdb was done for each rebuild, my system uses en_GB.UTF-8 (if that's relevant) and I used your same sort.sql... With master: thom@swift ~/Development $ pgbench -f sort.sql -n -T 100 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 421479 latency average: 0.237 ms tps = 4214.769601 (including connections establishing) tps = 4214.906079 (excluding connections establishing) With patch applied: thom@swift ~/Development $ pgbench -f sort.sql -n -T 100 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 412405 latency average: 0.242 ms tps = 4124.047237 (including connections establishing) tps = 4124.177437 (excluding connections establishing) And with 4 runs (TPS): Master: 4214.906079 / 4564.532623 / 4152.784608 / 4152.578297 (avg: 4271) Patched: 4124.177437 / 3777.561869 / 3777.561869 / 2484.220475 (avg: 3481) I'm not sure what's causing the huge variation. I ran 5 minute benchmarks too: Master: thom@swift ~/Development $ pgbench -f sort.sql -n -T 300 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 300 s number of transactions actually processed: 1092221 latency average: 0.275 ms tps = 3640.733002 (including connections establishing) tps = 3640.784628 (excluding connections establishing) Patched: thom@swift ~/Development $ pgbench -f sort.sql -n -T 300 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 300 s number of transactions actually processed: 1068239 latency average: 0.281 ms tps = 3560.794946 (including connections establishing) tps = 3560.835076 (excluding connections establishing) And per-second results for the first 30 seconds: Master: progress: 1.0 s, 2128.8 tps, lat 0.464 ms stddev 0.084 progress: 2.0 s, 2138.9 tps, lat 0.464 ms stddev 0.015 progress: 3.0 s, 2655.6 tps, lat 0.374 ms stddev 0.151 progress: 4.0 s, 2214.0 tps, lat 0.448 ms stddev 0.080 progress: 5.0 s, 2171.1 tps, lat 0.457 ms stddev 0.071 progress: 6.0 s, 2131.6 tps, lat 0.466 ms stddev 0.035 progress: 7.0 s, 3811.2 tps, lat 0.260 ms stddev 0.177 progress: 8.0 s, 2139.6 tps, lat 0.464 ms stddev 0.017 progress: 9.0 s, 7989.7 tps, lat 0.124 ms stddev 0.091 progress: 10.0 s, 8509.7 tps, lat 0.117 ms stddev 0.062 progress: 11.0 s, 3131.3 tps, lat 0.317 ms stddev 0.177 progress: 12.0 s,
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Stephen Frost wrote: * Stephen Frost (sfr...@snowman.net) wrote: I have the pre-upgrade database and can upgrade/rollback/etc that pretty easily. Note that the table contents weren't changed during the upgrade, of course, and so the 9.2.6 instance has HEAP_XMAX_IS_MULTI set while t_xmax is 6849409 for the tuple in question- even though pg_controldata reports NextMultiXactId as 1601462 (and it seems very unlikely that there's been a wraparound on that in this database..). Further review leads me to notice that both HEAP_XMAX_IS_MULTI and HEAP_XMAX_INVALID are set: t_infomask | 6528 6528 decimal - 0x1980 0001 1001 1000 Which gives us: 1000 - HEAP_XMAX_LOCK_ONLY 0001 - HEAP_XMIN_COMMITTED 1000 - HEAP_XMAX_INVALID 0001 - HEAP_XMAX_IS_MULTI Which shows that both HEAP_XMAX_INVALID and HEAP_XMAX_IS_MULTI are set. My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. -- Á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] MultiXactId error after upgrade to 9.3.4
On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. 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] MultiXactId error after upgrade to 9.3.4
Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. Ah, you're right. It even says so on the comment at the top (no caffeine yet.) But what it's doing is still buggy, per this report, so we need to do *something* ... -- Á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] MultiXactId error after upgrade to 9.3.4
On 2014-03-31 09:19:12 -0300, Alvaro Herrera wrote: Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. Ah, you're right. It even says so on the comment at the top (no caffeine yet.) But what it's doing is still buggy, per this report, so we need to do *something* ... Are you sure needs_freeze() is the problem here? IIRC it already does some checks for allow_old? Why is the check for that not sufficient? 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] New parameter RollbackError to control rollback behavior on error
On Wed, Mar 26, 2014 at 5:53 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
* I'm not too fond of the RollbackError name. It sounds like an error while
rolling back. I googled around and found out that DataDirect's proprietary
driver has the same option, and they call it TransactionErrorBehavior. See
http://blogs.datadirect.com/2013/07/solution-unexpected-postgres-current-transaction-aborted-error.html.
* Instead of using 0-2 as the values, let's give them descriptive names.
Something like none, RollbackTransaction, RollbackStatement.
(actually, we'll probably want to also allow the integers, to keep the
connection string short, as there is a size limit on that)
OK, I have been working more on that, giving the attached patch. The
parameter name is changed to TransactionErrorBehavior, able to use the
values Statement, Nop, Transaction and Default. Default
corresponds to the default behavior, that is used to set the rollback
behavior depending on the Postgres version driver is connected with.
As of now, TransactionErrorBehavior does not accept integer values as
it makes the patch a bit more simple, this could be added with some
atoi calls in dlg_specific.c.
I have updated dlg_wingui.c as well. Patch has always its set of docs
and regression tests.
Regards,
--
Michael
diff --git a/connection.c b/connection.c
index 8601cb7..8361691 100644
--- a/connection.c
+++ b/connection.c
@@ -288,7 +288,7 @@ CC_conninfo_init(ConnInfo *conninfo, UInt4 option)
conninfo-bytea_as_longvarbinary = -1;
conninfo-use_server_side_prepare = -1;
conninfo-lower_case_identifier = -1;
- conninfo-rollback_on_error = -1;
+ conninfo-transaction_error_value = TRANSACTION_ERROR_DEFAULT;
conninfo-force_abbrev_connstr = -1;
conninfo-bde_environment = -1;
conninfo-fake_mss = -1;
@@ -321,6 +321,7 @@ CC_copy_conninfo(ConnInfo *ci, const ConnInfo *sci)
CORR_STRCPY(username);
NAME_TO_NAME(ci-password, sci-password);
CORR_STRCPY(protocol);
+ CORR_STRCPY(transaction_error);
CORR_STRCPY(port);
CORR_STRCPY(sslmode);
CORR_STRCPY(onlyread);
@@ -341,7 +342,7 @@ CC_copy_conninfo(ConnInfo *ci, const ConnInfo *sci)
CORR_VALCPY(bytea_as_longvarbinary);
CORR_VALCPY(use_server_side_prepare);
CORR_VALCPY(lower_case_identifier);
- CORR_VALCPY(rollback_on_error);
+ CORR_VALCPY(transaction_error_value);
CORR_VALCPY(force_abbrev_connstr);
CORR_VALCPY(bde_environment);
CORR_VALCPY(fake_mss);
@@ -2370,7 +2371,7 @@ int CC_get_max_idlen(ConnectionClass *self)
{
QResultClass*res;
- res = CC_send_query(self, show max_identifier_length, NULL,
ROLLBACK_ON_ERROR | IGNORE_ABORT_ON_CONN, NULL);
+ res = CC_send_query(self, show max_identifier_length, NULL,
TRANSACTION_ERROR | IGNORE_ABORT_ON_CONN, NULL);
if (QR_command_maybe_successful(res))
len = self-max_identifier_length = atoi(res-command);
QR_Destructor(res);
@@ -2539,7 +2540,7 @@ static void CC_clear_cursors(ConnectionClass *self, BOOL
on_abort)
{
snprintf(cmd, sizeof(cmd), MOVE 0 in
\%s\, QR_get_cursor(res));
CONNLOCK_RELEASE(self);
- wres = CC_send_query(self, cmd, NULL,
ROLLBACK_ON_ERROR | IGNORE_ABORT_ON_CONN, NULL);
+ wres = CC_send_query(self, cmd, NULL,
TRANSACTION_ERROR | IGNORE_ABORT_ON_CONN, NULL);
QR_set_no_survival_check(res);
if (QR_command_maybe_successful(wres))
QR_set_permanent(res);
@@ -2733,7 +2734,7 @@ CC_send_query_append(ConnectionClass *self, const char
*query, QueryInfo *qi, UD
BOOLignore_abort_on_conn = ((flag IGNORE_ABORT_ON_CONN) != 0),
create_keyset = ((flag CREATE_KEYSET) != 0),
issue_begin = ((flag GO_INTO_TRANSACTION) != 0
!CC_is_in_trans(self)),
- rollback_on_error, query_rollback, end_with_commit;
+ rollback_error_value, query_rollback, end_with_commit;
const char *wq;
charswallow, *ptr;
@@ -2844,13 +2845,13 @@ CC_send_query_append(ConnectionClass *self, const char
*query, QueryInfo *qi, UD
return res;
}
- rollback_on_error = (flag ROLLBACK_ON_ERROR) != 0;
+ rollback_error_value = (flag TRANSACTION_ERROR) != 0;
end_with_commit = (flag END_WITH_COMMIT) != 0;
#definereturn DONT_CALL_RETURN_FROM_HERE???
consider_rollback = (issue_begin || (CC_is_in_trans(self)
!CC_is_in_error_trans(self)) || strnicmp(query, begin, 5) == 0);
- if (rollback_on_error)
- rollback_on_error =
Re: [HACKERS] New parameter RollbackError to control rollback behavior on error
On Mon, Mar 31, 2014 at 9:40 PM, Michael Paquier michael.paqu...@gmail.com wrote: OK, I have been working more on that, giving the attached patch... Sorry, wrong mailing list... My apologies. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Andres Freund wrote:
On 2014-03-31 09:19:12 -0300, Alvaro Herrera wrote:
Andres Freund wrote:
On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote:
My conclusion here is that some part of the code is failing to examine
XMAX_INVALID before looking at the value stored in xmax itself. There
ought to be a short-circuit. Fortunately, this bug should be pretty
harmless.
.. and after looking, I'm fairly sure the bug is in
heap_tuple_needs_freeze.
heap_tuple_needs_freeze() isn't *allowed* to look at
XMAX_INVALID. Otherwise it could miss freezing something still visible
on a standby or after an eventual crash.
Ah, you're right. It even says so on the comment at the top (no
caffeine yet.) But what it's doing is still buggy, per this report, so
we need to do *something* ...
Are you sure needs_freeze() is the problem here?
IIRC it already does some checks for allow_old? Why is the check for
that not sufficient?
GetMultiXactIdMembers has this:
if (MultiXactIdPrecedes(multi, oldestMXact))
{
ereport(allow_old ? DEBUG1 : ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg(MultiXactId %u does no longer exist -- apparent
wraparound,
multi)));
return -1;
}
if (!MultiXactIdPrecedes(multi, nextMXact))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg(MultiXactId %u has not been created
yet -- apparent wraparound,
multi)));
I guess I wasn't expecting that too-old values would last longer than a
full wraparound cycle. Maybe the right fix is just to have the second
check also conditional on allow_old.
Anyway, it's not clear to me why this database has a multixact value of
6 million when the next multixact value is barely above one million.
Stephen said a wraparound here is not likely.
--
Á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] MultiXactId error after upgrade to 9.3.4
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: I guess I wasn't expecting that too-old values would last longer than a full wraparound cycle. Maybe the right fix is just to have the second check also conditional on allow_old. I don't believe this was a wraparound case. Anyway, it's not clear to me why this database has a multixact value of 6 million when the next multixact value is barely above one million. Stephen said a wraparound here is not likely. I don't think the xmax value is a multixact at all- I think it's actually a regular xid, but everyone is expected to ignore it because XMAX_IS_INVALID, yet somehow the MULTIXACT bit was also set and the new code expects to be able to look at the xmax field even though it's marked as invalid.. I'm going through the upgrade process again from 9.2 and will get a stack trace. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
On 2014-03-31 09:09:08 -0400, Stephen Frost wrote: * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: I guess I wasn't expecting that too-old values would last longer than a full wraparound cycle. Maybe the right fix is just to have the second check also conditional on allow_old. I don't believe this was a wraparound case. Could you show pg_controldata from the old cluster? Anyway, it's not clear to me why this database has a multixact value of 6 million when the next multixact value is barely above one million. Stephen said a wraparound here is not likely. I don't think the xmax value is a multixact at all- I think it's actually a regular xid, but everyone is expected to ignore it because XMAX_IS_INVALID, yet somehow the MULTIXACT bit was also set and the new code expects to be able to look at the xmax field even though it's marked as invalid.. XMAX_IS_INVALID was never allowed to be ignored for vacuum AFAICS. So I don't think this is a valid explanation. 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] MultiXactId error after upgrade to 9.3.4
Andres, * Andres Freund (and...@2ndquadrant.com) wrote: Without having looked at the code, IIRC this looks like some place misses passing allow_old=true where it's actually required. Any chance you can get a backtrace for the error message? I know you said somewhere below that you'd worked around the problem, but maybe you have a copy of the database somewhere? #0 GetMultiXactIdMembers (allow_old=optimized out, members=0x7fff78200ca0, multi=6849409) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/access/transam/multixact.c:1130 #1 GetMultiXactIdMembers (multi=6849409, members=0x7fff78200ca0, allow_old=optimized out) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/access/transam/multixact.c:1056 #2 0x7f46e9e707f8 in FreezeMultiXactId (flags=synthetic pointer, cutoff_multi=optimized out, cutoff_xid=4285178915, t_infomask=optimized out, multi=6849409) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/access/heap/heapam.c:5355 #3 heap_prepare_freeze_tuple (tuple=0x7f46e2d9a328, cutoff_xid=4285178915, cutoff_multi=optimized out, frz=0x7f46ec4d31b0) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/access/heap/heapam.c:5593 #4 0x7f46e9f72aca in lazy_scan_heap (scan_all=0 '\000', nindexes=2, Irel=optimized out, vacrelstats=optimized out, onerel=0x7f46e9d65ca0) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/commands/vacuumlazy.c:899 #5 lazy_vacuum_rel (onerel=0x7f46e9d65ca0, vacstmt=optimized out, bstrategy=optimized out) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/commands/vacuumlazy.c:236 #6 0x7f46e9f70755 in vacuum_rel (relid=288284, vacstmt=0x7f46ec59f780, do_toast=1 '\001', for_wraparound=0 '\000') at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/commands/vacuum.c:1205 #7 0x7f46e9f71445 in vacuum (vacstmt=0x7f46ec59f780, relid=optimized out, do_toast=1 '\001', bstrategy=optimized out, for_wraparound=0 '\000', isTopLevel=optimized out) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/commands/vacuum.c:234 #8 0x7f46ea05e773 in standard_ProcessUtility (parsetree=0x7f46ec59f780, queryString=0x7f46ec59ec90 vacuum common.wave_supplemental;, context=optimized out, params=0x0, dest=optimized out, completionTag=0x7fff782016e0 ) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/utility.c:639 #9 0x7f46ea05b5a3 in PortalRunUtility (portal=0x7f46ec4f82e0, utilityStmt=0x7f46ec59f780, isTopLevel=1 '\001', dest=0x7f46ec59fae0, completionTag=0x7fff782016e0 ) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/pquery.c:1187 #10 0x7f46ea05c1d6 in PortalRunMulti (portal=0x7f46ec4f82e0, isTopLevel=1 '\001', dest=0x7f46ec59fae0, altdest=0x7f46ec59fae0, completionTag=0x7fff782016e0 ) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/pquery.c:1318 #11 0x7f46ea05ce6d in PortalRun (portal=0x7f46ec4f82e0, count=9223372036854775807, isTopLevel=1 '\001', dest=0x7f46ec59fae0, altdest=0x7f46ec59fae0, completionTag=0x7fff782016e0 ) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/pquery.c:816 #12 0x7f46ea05908c in exec_simple_query (query_string=0x7f46ec59ec90 vacuum common.wave_supplemental;) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/postgres.c:1048 #13 PostgresMain (argc=optimized out, argv=optimized out, dbname=0x7f46ec4b9010 mine, username=optimized out) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/tcop/postgres.c:3997 #14 0x7f46ea01487d in BackendRun (port=0x7f46ec4fc2c0) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/postmaster/postmaster.c:3996 #15 BackendStartup (port=0x7f46ec4fc2c0) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/postmaster/postmaster.c:3685 #16 ServerLoop () at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/postmaster/postmaster.c:1586 #17 PostmasterMain (argc=optimized out, argv=optimized out) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/postmaster/postmaster.c:1253 #18 0x7f46e9e4950c in main (argc=5, argv=0x7f46ec4b81f0) at /tmp/buildd/postgresql-9.3-9.3.4/build/../src/backend/main/main.c:206 Looks like your idea that is has to do w/ freezeing is accurate... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
* Andres Freund (and...@2ndquadrant.com) wrote: On 2014-03-31 09:09:08 -0400, Stephen Frost wrote: * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: I guess I wasn't expecting that too-old values would last longer than a full wraparound cycle. Maybe the right fix is just to have the second check also conditional on allow_old. I don't believe this was a wraparound case. Could you show pg_controldata from the old cluster? Per the original email- The *OLD* (9.2.6) control data had: pg_control version number:922 Catalog version number: 201204301 Latest checkpoint's NextXID: 0/40195831 Latest checkpoint's NextOID: 53757891 Latest checkpoint's NextMultiXactId: 1601462 Latest checkpoint's NextMultiOffset: 4503112 Latest checkpoint's oldestXID:654 Latest checkpoint's oldestXID's DB: 12870 Latest checkpoint's oldestActiveXID: 0 (It doesn't report the oldestMulti info under 9.2.6). Was there something else you were looking for? I don't think the xmax value is a multixact at all- I think it's actually a regular xid, but everyone is expected to ignore it because XMAX_IS_INVALID, yet somehow the MULTIXACT bit was also set and the new code expects to be able to look at the xmax field even though it's marked as invalid.. XMAX_IS_INVALID was never allowed to be ignored for vacuum AFAICS. So I don't think this is a valid explanation. The old 9.2 cluster certainly had no issue w/ vacuum'ing this table/tuple. Unfortunately, I can't have the 9.2 debug packages installed concurrently w/ the 9.3 debug packages, so it's a bit awkward to go back and forth between the two. Anything else of interest while I have the 9.3 instance running under gdb? Sent the requested backtrace in another email. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
On 31 March 2014 11:32, Thom Brown t...@linux.com wrote: On 31 March 2014 06:51, Peter Geoghegan p...@heroku.com wrote: On Wed, Mar 26, 2014 at 8:08 PM, Peter Geoghegan p...@heroku.com wrote: The API I envisage is a new support function 3 that operator class authors may optionally provide. I've built a prototype patch, attached, that extends SortSupport and tuplesort to support poor man's normalized keys. All the regression tests pass, so while it's just a proof of concept, it is reasonably well put together for one. The primary shortcoming of the prototype (the main reason why I'm calling it a prototype rather than just a patch) is that it isn't sufficiently generalized (i.e. it only works for the cases currently covered by SortSupport - not B-Tree index builds, or B-Tree scanKeys). There is no B-Tree support function number 3 in the patch. I didn't spend too long on this. I'm pretty happy with the results for in-memory sorting of text (my development system uses 'en_US.UTF8', so please assume that any costs involved are for runs that use that collation). With the dellstore2 sample database [1] restored to my local development instance, the following example demonstrates just how much the technique can help performance. With master: pg@hamster:~/sort-tests$ cat sort.sql select * from (select * from customers order by firstname offset 10) d; pg@hamster:~/sort-tests$ pgbench -f sort.sql -n -T 100 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 819 latency average: 122.100 ms tps = 8.186197 (including connections establishing) tps = 8.186522 (excluding connections establishing) With patch applied (requires initdb for new text SortSupport pg_proc entry): pg@hamster:~/sort-tests$ cat sort.sql select * from (select * from customers order by firstname offset 10) d; pg@hamster:~/sort-tests$ pgbench -f sort.sql -n -T 100 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 2525 latency average: 39.604 ms tps = 25.241723 (including connections establishing) tps = 25.242447 (excluding connections establishing) As another data point, I ran the same benchmark, but I don't appear to yield the same positive result. An initdb was done for each rebuild, my system uses en_GB.UTF-8 (if that's relevant) and I used your same sort.sql... With master: thom@swift ~/Development $ pgbench -f sort.sql -n -T 100 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 421479 latency average: 0.237 ms tps = 4214.769601 (including connections establishing) tps = 4214.906079 (excluding connections establishing) With patch applied: thom@swift ~/Development $ pgbench -f sort.sql -n -T 100 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 100 s number of transactions actually processed: 412405 latency average: 0.242 ms tps = 4124.047237 (including connections establishing) tps = 4124.177437 (excluding connections establishing) And with 4 runs (TPS): Master: 4214.906079 / 4564.532623 / 4152.784608 / 4152.578297 (avg: 4271) Patched: 4124.177437 / 3777.561869 / 3777.561869 / 2484.220475 (avg: 3481) I'm not sure what's causing the huge variation. I ran 5 minute benchmarks too: Master: thom@swift ~/Development $ pgbench -f sort.sql -n -T 300 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 300 s number of transactions actually processed: 1092221 latency average: 0.275 ms tps = 3640.733002 (including connections establishing) tps = 3640.784628 (excluding connections establishing) Patched: thom@swift ~/Development $ pgbench -f sort.sql -n -T 300 ds2 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 300 s number of transactions actually processed: 1068239 latency average: 0.281 ms tps = 3560.794946 (including connections establishing) tps = 3560.835076 (excluding connections establishing) And per-second results for the first 30 seconds: Master: progress: 1.0 s, 2128.8 tps, lat 0.464 ms stddev 0.084 progress: 2.0 s, 2138.9 tps, lat 0.464 ms stddev 0.015 progress: 3.0 s, 2655.6 tps, lat 0.374 ms stddev 0.151 progress: 4.0 s, 2214.0 tps, lat 0.448 ms stddev 0.080 progress: 5.0 s, 2171.1 tps, lat 0.457 ms stddev 0.071 progress: 6.0 s, 2131.6 tps, lat 0.466 ms stddev 0.035 progress: 7.0 s, 3811.2 tps, lat 0.260 ms stddev 0.177 progress: 8.0 s, 2139.6 tps, lat 0.464 ms stddev 0.017 progress: 9.0 s, 7989.7 tps, lat 0.124 ms stddev 0.091
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Stephen Frost wrote: Further review leads me to notice that both HEAP_XMAX_IS_MULTI and HEAP_XMAX_INVALID are set: t_infomask | 6528 6528 decimal - 0x1980 0001 1001 1000 Which gives us: 1000 - HEAP_XMAX_LOCK_ONLY 0001 - HEAP_XMIN_COMMITTED 1000 - HEAP_XMAX_INVALID 0001 - HEAP_XMAX_IS_MULTI Which shows that both HEAP_XMAX_INVALID and HEAP_XMAX_IS_MULTI are set. Of some interest is that HEAP_XMAX_LOCK_ONLY is also set.. This combination seems reasonable. This tuple had two FOR SHARE lockers, so it was marked HEAP_XMAX_SHARED_LOCK|HEAP_XMAX_IS_MULTI (0x1080). Then those lockers finished, and somebody else checked the tuple with a tqual.c routine (say HeapTupleSatisfiesUpdate), which saw the lockers were gone and marked it as HEAP_XMAX_INVALID (0x800), without removing the Xmax value and without removing the other bits. This is all per spec, so we must cope. -- Á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] MultiXactId error after upgrade to 9.3.4
Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. I think this rule is wrong. I think the rule ought to be something like if the XMAX_INVALID bit is set, then reset whatever is there if there is something; if the bit is not set, proceed as today. Otherwise we risk reading garbage, which is what is happening in this case. -- Á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] PQputCopyData dont signal error
http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_man_excerpt.png These were my results: http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_embedded_copy_log_excerpt.png I'd advise anyone contemplating using this feature to seriously seriously seriously test this and examine your logs after each test run before you move this feature into your baseline. Maybe you'll have better luck than I did. For what it's worth I got very good performance results from using INSERT with multiple values clauses that inserted 1000 records at a time. For example on one error test (of many) purposefully attempting to insert alphabetic data into a numeric field yielded explicit, correct information about the exact line of data causing the error within the 1000 records attempting to be inserted. With this information in hand it would be eminently feasible to go back to the baseline and examine any recent source code updates that might have altered the generation of the data that caused an error like this. Hopefully this helps anyone trying to handle large amounts of data quickly and wondering what a viable solution might be. Best regards to everyone and thank you all for your time, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798002.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] MultiXactId error after upgrade to 9.3.4
Alvaro Herrera wrote: Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. I think this rule is wrong. I think the rule ought to be something like if the XMAX_INVALID bit is set, then reset whatever is there if there is something; if the bit is not set, proceed as today. Otherwise we risk reading garbage, which is what is happening in this case. Andres asks on IM: How come there is garbage there in the first place? I have to admit I have no idea. -- Á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] MultiXactId error after upgrade to 9.3.4
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: I think this rule is wrong. I think the rule ought to be something like if the XMAX_INVALID bit is set, then reset whatever is there if there is something; if the bit is not set, proceed as today. Otherwise we risk reading garbage, which is what is happening in this case. Andres asks on IM: How come there is garbage there in the first place? I have to admit I have no idea. I haven't got any great explanation for that either. I continue to feel that it's much more likely that it's an xid than a multixid. Is it possible that it was stamped with a real xmax through some code path which ignored the IS_MULTI flag? This could have been from as far back as 9.0-era. On this over-7TB database, only this one tuple had the issue. I have another set of databases which add up to ~20TB that I'm currently testing an upgrade from 9.2 to 9.3 on and will certainly let everyone know if I run into a similar situation there. On our smallest DB (which we upgraded first) which is much more OLTP instead of OLAP, we didn't run into this. This is all on physical gear and we've seen no indications that there has been any corruption. Hard to rule it out completely, but it seems pretty unlikely. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Comment in src/backend/commands/vacuumlazy.c
On Mon, Mar 24, 2014 at 12:28 AM, Amit Langote amitlangot...@gmail.com wrote: Hi, Note the following comment in src/backend/commands/vacuumlazy.c:lazy_scan_heap() 1088 /* If no indexes, make log report that lazy_vacuum_heap would've made */ 1089 if (vacuumed_pages) 1090 ereport(elevel, Just wondering if it would read better as: 1088 /* Make the log report that lazy_vacuum_heap would've made had there been no indexes */ Is that correct? No. Your rewrite means the opposite of what the comment means now. vacuumed_pages will be non-zero only if the relation does NOT have indexes. -- 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] B-Tree support function number 3 (strxfrm() optimization)
On 03/31/2014 08:51 AM, Peter Geoghegan wrote:
+ #ifdef HAVE_LOCALE_T
+ if (tss-locale)
+ strxfrm_l(pres, tss-buf1, Min(sizeof(Datum), len),
tss-locale);
+ else
+ #endif
+ strxfrm(pres, tss-buf1, Min(sizeof(Datum), len));
+
+ pres[Min(sizeof(Datum) - 1, len)] = '\0';
I'm afraid that trick isn't 100% reliable. The man page for strxrfm says:
RETURN VALUE
The strxfrm() function returns the number of bytes required to store
the transformed string in dest excluding the terminating null byte
('\0'). If the value returned is n or more, the contents of dest are
indeterminate.
Note the last sentence. To avoid the undefined behavior, you have to
pass a buffer that's large enough to hold the whole result, and then
truncate the result.
- 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] PQputCopyData dont signal error
On 03/31/2014 10:18 AM, steve k wrote: http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_man_excerpt.png These were my results: http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_embedded_copy_log_excerpt.png I'd advise anyone contemplating using this feature to seriously seriously seriously test this and examine your logs after each test run before you move this feature into your baseline. Maybe you'll have better luck than I did. You appear not to have understood what you have been told. Specifically Tom Lane's remarks: PQputCopyData/PQputCopyEnd are only concerned with transferring data. After you're done with that, you should call PQgetResult to find out the COPY command's completion status. You should get PGRES_COMMAND_OK, otherwise it'll be an error result. Neither of these is supposed to return -1 if there's a COPY error, only if there's a data transfer error. Plenty of people have made this work just fine. 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] PQputCopyData dont signal error
Am I to understand then that I should expect no error feedback if copy fails because of something like attempting to insert alphabetic into a numeric? I apologize for my ignorance, but all my return codes were always successful (PGRES_COMMAND_OK) even if nothing was copied due to garbage data. Also, calling PQgetResult never returned any information either because everything was always PGRES_COMMAND_OK. If that's what is supposed to happen then I have completely missed the boat and apologize for wasting everyone's time. The exact same garbage data test returned specific error related information if the copy is done with the same sql query from the command line. This is what I was trying to get to happen with the embedded sql so that in the event of bad data, you could stop and determine the source of the bad data, and not go on assuming you were successfully copying only to find later that 1000 recs here and a 1000 records there were never inserted/copied and to have no idea or log messages as to why. In a sense, I have made this work just fine. It compiled, ran, and copied data - as long as all the data was perfect. I was trying set up error handling to notify in case there was invalid data contained within the records being copied. Again, I apologize if I was unclear and further apologize for irritating everyone that has replied thus far. Thank you all for your time and best regards. I am examining other ways to do mass inserts/writes that allow for notification if some of the data contained within for some reason fails to copy/insert so that the cause of the bad data can be examined and remedied as soon as it occurs as well as writing the offending data to a log so that not all of it is lost. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798032.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] PQputCopyData dont signal error
steve k wrote: I am examining other ways to do mass inserts/writes that allow for notification if some of the data contained within for some reason fails to copy/insert so that the cause of the bad data can be examined and remedied as soon as it occurs as well as writing the offending data to a log so that not all of it is lost. Have you looked at PGLoader? https://github.com/dimitri/pgloader -- Á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] jsonb and nested hstore
On Fri, Mar 14, 2014 at 9:17 PM, Josh Berkus j...@agliodbs.com wrote:
On 03/14/2014 06:44 PM, Tomas Vondra wrote:
Stupid question - so if I have a json like this:
Not a stupid question, actually. In fact, I expect to answer it 400 or
500 times over the lifespan of 9.4.
{ a : { b : c}}
the GIN code indexes {b : c} as a single value? And then takes c
and indexes it as a single value too?
I don't know that c is indexed separately.
Because otherwise I don't understand how the index could be used for
queries with @ '{a : {b : c}}' conditions (i.e. path [a,b] with
value c).
H, if that's how it works, removing the size limit would be
certainly more difficult than I thought.
Precisely. Hence, the Russian plans for VODKA.
Have these plans been shared publicly somewhere? Got a link?
--
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] HEAD seems to generate larger WAL regarding GIN index
On Thu, Mar 20, 2014 at 1:12 PM, Jesper Krogh jes...@krogh.cc wrote: On 15/03/14 20:27, Heikki Linnakangas wrote: That said, I didn't expect the difference to be quite that big when you're appending to the end of the table. When the new entries go to the end of the posting lists, you only need to recompress and WAL-log the last posting list, which is max 256 bytes long. But I guess that's still a lot more WAL than in the old format. That could be optimized, but I figured we can live with it, thanks to the fastupdate feature. Fastupdate allows amortizing that cost over several insertions. But of course, you explicitly disabled that... In a concurrent update environment, fastupdate as it is in 9.2 is not really useful. It may be that you can bulk up insertion, but you have no control over who ends up paying the debt. Doubling the amount of wal from gin-indexing would be pretty tough for us, in 9.2 we generate roughly 1TB wal / day, keeping it for some weeks to be able to do PITR. The wal are mainly due to gin-index updates as new data is added and needs to be searchable by users. We do run gzip that cuts it down to 25-30% before keeping the for too long, but doubling this is going to be a migration challenge. If fast-update could be made to work in an environment where we both have users searching the index and manually updating it and 4+ backend processes updating the index concurrently then it would be a good benefit to gain. the gin index currently contains 70+ million records with and average tsvector of 124 terms. Should we try to install some hack around fastupdate for 9.4? I fear the divergence between reasonable values of work_mem and reasonable sizes for that list is only going to continue to get bigger. I'm sure there's somebody out there who has work_mem = 16GB, and stuff like 263865a48973767ce8ed7b7788059a38a24a9f37 is only going to increase the appeal of large values. -- 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] PQputCopyData dont signal error
steve k wrote Am I to understand then that I should expect no error feedback if copy fails because of something like attempting to insert alphabetic into a numeric? I apologize for my ignorance, but all my return codes were always successful (PGRES_COMMAND_OK) even if nothing was copied due to garbage data. Also, calling PQgetResult never returned any information either because everything was always PGRES_COMMAND_OK. If that's what is supposed to happen then I have completely missed the boat and apologize for wasting everyone's time. In your example you successfully sent an error message to the server and so PQputCopyEnd does not report an error since it did what it was asked to do. Later, when you finish the copy and ask for the error message, you probably will get the same message that you sent here but you may get a different one depending on whether the server encountered any other errors before you sent the explicit error. Regardless of the message you are guaranteed to get back an error after calling PQgetResult. It seems to me that you need to supply a simple C program - along with a test file that you expect to fail - that never reports an error so that others may evaluate actual code. The likelihood of this NOT being human error is small so we really have to see your actual code since that is most probably the culprit. Note that, as Tom mentioned, psql is open source. If you are trying to duplicate its behavior in your own code you should probably look to see what it does. The fact that you get the proper errors in some cases means that the server is quite obviously capable of working in the manner you desire and thus the client - again your specific software - it where any issue likely resides. Again, from what I'm reading PQputCopy(Data|End) will not report on data parsing issues. You will only see those after issuing PQgetResult - which is noted in the last paragraph of the PQputCopyEnd documentation excerpt you provided. The put commands only report whether the sending of the data was successful. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798036.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] About adding an attribute to a system catalog
On Mon, Mar 24, 2014 at 6:38 PM, Tanmay Deshpande tp.deshpand...@gmail.com wrote: My doubt is what changes does one have to make in the source code if he/she is trying to add an attribute to the existing system catalogs table ? In general, you mostly need to modify the pg_whatever.h file that defines that system catalog, including the DATA lines which appear there, and update the documentation in catalogs.sgml. Then you'll want to look for places where Form_pg_whatever values are initialized and make the appropriate changes there. For a recent example of a relatively simple patch that did this kind of thing, see commit 6cb86143e8e1e855255edc706bce71c6ebfd9a6c. Generally, I find that it's helpful to pick an attribute in the same table that has properties similar to the new thing I'm adding, and then grep for that throughout the source base. Then, I go check whether any of those places need updating. Adding attributes to bootstrap tables is a whole lot more fiddly than for other system tables. I suggest looking at a patch that did this to see what's involved. For example, commit 76a47c0e7423891d4b4f0977312f46fec6c5c416 removed and replaced a column in pg_attribute, so by looking through there you could see all of the places that had to be updated. -- 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] PQputCopyData dont signal error
Hi Alvaro, Thanks for the prompt response. PGLoader looks like an awesome project and I especially liked this part: /Handling PostgreSQL errors Some data will get rejected by PostgreSQL, even after being carefully prepared by the transformation functions you can attach to pgloader. Then pgloader parses the PostgreSQL CONTEXT error message that contains the line number in the batch of where the error did happen. It's then easy enough to *resend the all the rows from the batch that are located before the error, skip and log as rejected the faulty row, and continue*, handling eventual next errors the same way. / I didn't see anything in the documentation about binary files and that is unfortunately the only thing I have for input currently. They used binary files because that was the fastest way to write the data for each of the frames in the sim without falling out of realtime. We're trying to bring some parts of this project more up to date with the ultimate goal of being able to write directly to the database itself without falling out of realtime and developing a dashboard for researchers to monitor during experiments and simulation runs. Thanks again for the suggestion and I'll definitely keep PGLoader in mind as things unfold here on this project. Best Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798038.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] Fix typo in decoding always passing true for the startup callback's is_init.
On Tue, Mar 25, 2014 at 10:46 AM, Andres Freund and...@2ndquadrant.com wrote: I found a typo causing is_init set to be to true when it should be false... Committed. -- 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] jsonb and nested hstore VODKA
On 03/31/2014 09:34 AM, Robert Haas wrote: On Fri, Mar 14, 2014 at 9:17 PM, Josh Berkus j...@agliodbs.com wrote: Precisely. Hence, the Russian plans for VODKA. Have these plans been shared publicly somewhere? Got a link? Nothing other than the pgCon proposal. Presumably we'll know at pgCon, unless one of them replies to this. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
I started with this: DBInsert_excerpts6_test_cpdlc.cpp http://postgresql.1045698.n5.nabble.com/file/n5798049/DBInsert_excerpts6_test_cpdlc.cpp Due to a cut and paste error I was originally querying the wrong sequence which led to me wondering why data wasn't in the table that the log reported as having successfully been written. After I fixed the sequence query error and got done laughing at myself for being so ignorant I realized I better see what happened if there was actually bad data and not just user stupidity. The data is in this log file excerpt.raptor_efb2_excerpts.out http://postgresql.1045698.n5.nabble.com/file/n5798049/raptor_efb2_excerpts.out This excerpt is from a run that copied as expected with perfect data. The test was to modify line 36 in the code excerpt from: | dataRec.getMinute() to: | dataRec.getMinute() a which put alphabetic data into the data which should have been rejected as nonnumeric. No error was raised and the log indicated that there should be 20 rows of data in the cpdlc table except there weren't. By the time I gave up on this I had so many friggin log statements in these 2 methods that there was more logging than actual useful code. Results never changed though. No errors and no messages when I appended the a onto numeric data destined for a numeric field. This was naturally unsettling so I turned to message boards to see if anyone had similar experiences or knew a work around or better way. I'm taking the time to give you all this information because I want to learn from it despite the potential for acute public embarassment. Maybe others will get something too. It's how we all get ahead. At any rate, thanks for your time and I look forward to what you find. Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798049.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] Why MarkBufferDirtyHint doesn't increment shared_blks_dirtied
On Wed, Mar 26, 2014 at 11:23 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Mar 27, 2014 at 1:39 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 24, 2014 at 9:02 PM, Amit Kapila amit.kapil...@gmail.com wrote: MarkBufferDirty() always increment BufferUsage counter (shared_blks_dirtied) for dirty blocks whenever it dirties any block, whereas same is not true for MarkBufferDirtyHint(). Is there any particular reason for not incrementing shared_blks_dirtied in MarkBufferDirtyHint()? Hmm, I think that's a bug, dating back to this commit: commit 2254367435fcc4a31cc3b6d8324e33c5c30f265a Right. Do you think the fix attached in my previous mail is appropriate? http://www.postgresql.org/message-id/CAA4eK1KQQSpNmfxg8Cg3-JZD23Q4Ee3iCsuLZGekH=dnagp...@mail.gmail.com Looks right to me. Committed and back-patched to 9.2. -- 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] Cube extension kNN support
On Thu, Mar 27, 2014 at 3:26 PM, Sergey Konoplev gray...@gmail.com wrote: On Sun, Sep 22, 2013 at 4:38 PM, Stas Kelvich stas.kelv...@gmail.com wrote: Here is the patch that introduces kNN search for cubes with euclidean, taxicab and chebyshev distances. What is the status of this patch? Referring to our private conversation with Alexander Korotkov, the patch is in WIP state currently, and, hopefully, will be ready by 9.5. I'm ready to actively participate in its testing on a real world production set of data. I'm not sure if it is doable at all, but are there any possibility to implement here, or, what would be just great, any ready/half ready solutions of a Hamming distance based kNN search? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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 comment ghost-timing
On Sat, Dec 28, 2013 at 08:20:59PM -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-12-24 12:27:59 -0500, Tom Lane wrote: What I was proposing was that we do include comments in what we send, as long as those comments are embedded in the statement text, not on lines before it. The common way I've seen what I've described above done as is something like: /* File:path/to/some/file.whatever Function:foo::something() * Comment: Expensive, but that's ok! */ SELECT here_comes FROM my WHERE some_sql; If I unerstood what you propose correctly, we'd now drop that comment, where we sent it before? Yeah. But I just noticed that this would cause the regression test results to change dramatically --- right now, most -- comment comments get echoed to the output, and that would stop. So maybe it's not such a great idea after all. Where are we on this? It seem odd that psql sends /* */ comments to the server, but not -- comments. Should this be documented or changed? I am confused why changing the behavior would affect the regression test output as -- and /* */ comments already appear, and it was stated that -- comments are already not sent to the server. Everyone agreed that suppressing \timing output for a PGRES_EMPTY_QUERY return result was not a good idea. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] doPickSplit stack buffer overflow in XLogInsert?
On Wed, Mar 26, 2014 at 9:45 PM, Peter Geoghegan p...@heroku.com wrote: On Wed, Nov 27, 2013 at 9:10 AM, Noah Misch n...@leadboat.com wrote: The threat is that rounding the read size up to the next MAXALIGN would cross into an unreadable memory page, resulting in a SIGSEGV. Every palloc chunk has MAXALIGN'd size under the hood, so the excess read of toDelete cannot cause a SIGSEGV. For a stack variable, it depends on the ABI. I'm not aware of an ABI where the four bytes past the end of this stack variable could be unreadable, which is not to claim I'm well-read on the topic. We should fix this in due course on code hygiene grounds, but I would not back-patch it. Attached patch silences the Invalid read of size n complaints of Valgrind. I agree with your general thoughts around backpatching. Note that the patch addresses a distinct complaint from Kevin's, as Valgrind doesn't take issue with the invalid reads past the end of spgxlogPickSplit variables on the stack. Is the needless zeroing this patch introduces apt to cause a performance problem? This function is actually pretty wacky. If we're stuffing bytes with undefined contents into the WAL record, maybe the answer isn't to force the contents of those bytes to be defined, but rather to elide them from the WAL record. -- 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] separate output dirs for test decoding pieces.
On Mon, Mar 31, 2014 at 5:27 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-03-29 13:20:41 -0400, Andrew Dunstan wrote: make check in contrib/test_decoding actually does two regression runs, one with pg_regress and one with pg_isolation_regress. These both use the same (default) outputdir, so one overwrites the other, which is a bit unfortunate from the buildfarm's point of view. I propose to make them use separate outputdirs, via the attached small patch. Comments? Thanks for taking care of this, sounds like a good idea. It's a little weird that the normal directory name is results and now we have regression_output and isolation_output. Why s/results/output/? Also, the patch failed to update .gitignore, which I have now done. If we do any further renaming here, .gitignore should not miss the bus. -- 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] Still something fishy in the fastpath lock stuff
On Wed, Mar 26, 2014 at 12:59 AM, Robert Haas robertmh...@gmail.com wrote: It seems to me that the simplest thing to do might be just this: -static FastPathStrongRelationLockData *FastPathStrongRelationLocks; +static volatile FastPathStrongRelationLockData *FastPathStrongRelationLocks; Do you see any problem with that approach? Hearing nothing, I went ahead and did this. I see that the failure on prairiedog only occurred once, so I don't know how we'll know whether this fixed the problem that caused that failure or merely fixed a problem that could cause a failure, but I'm not sure what else we can really do at this point. -- 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] Array of composite types returned from python
Attached is the patch for the code change described below: Project Name : Allow return array of composites from PL/Python Currently, a SQL array of non-composite variables can be returned from PL/Python code by returning a iterable object. A SQL composite value may be returned by returning a iterable or a subscriptable object. However, returning a list of objects that each represent a composite variable is not converted to an SQL array of composite variables. This code change allows that conversion to take place. This allows for smooth, elegant interface between SQL and PL/Python. This is a patch against MASTER The patch compiles successfully. All the standard regression tests pass. Some modifications are needed for the .out files in order for the PL/Python regression tests to pass. This is due to the fact that the current .out files expect errors when a python array of composite is converted, where my modifications expect success. All tests have been performed with both Python2 and Python3. -Ed From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Behn, Edward (EBEHN) Sent: Thursday, March 20, 2014 5:54 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Array of composite types returned from python I've endeavored to enable the return of arrays of composite types from code written in PL/Python. It seems that this can be accomplished though a very minor change to the code: On line 401 in the file src/pl/plpython/plpy_typeio.c, remove the error report PL/Python functions cannot return type. and replace it with the command arg-func = PLyObject_ToComposite; From all that I can see, this does exactly what I want. A python list of tuples is converted to an array of composite types in SQL. I ran the main and python regression suites for both python2 and python3 with assert enabled. The only discrepancies I got were ones that were due to the output expecting an error. When I altered the .out files to the expected behavior, it matched just fine. Am I missing anything, (ie memory leak, undesirable behavior elsewhere)? -Ed Ed Behn / Staff Engineer / Airline and Network Services Information Management Services 2551 Riva Road, Annapolis, MD 21401 USA Phone: 410.266.4426 / Cell: 240.696.7443 eb...@arinc.com http://www.rockwellcollins.com/ www.rockwellcollins.com image001.pngimage003.png PLPythonCompositeArrays_v1.patch Description: Binary data smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] About adding an attribute to a system catalog
Robert Haas robertmh...@gmail.com writes: Adding attributes to bootstrap tables is a whole lot more fiddly than for other system tables. I suggest looking at a patch that did this to see what's involved. For example, commit 76a47c0e7423891d4b4f0977312f46fec6c5c416 removed and replaced a column in pg_attribute, so by looking through there you could see all of the places that had to be updated. One way in which that particular commit isn't a great example is that, since the number of columns didn't change, it didn't expose the need to update pg_attribute's handmade row in pg_class.h. Failing to update the column count in that DATA line is a standard gotcha when adding columns to one of the BKI_BOOTSTRAP tables. In general, though, Robert's advice is good: find a previous commit that did something similar to what you need. And grep is your friend in any case. 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] issue log message to suggest VACUUM FULL if a table is nearly empty
On Mon, Mar 31, 2014 at 12:35 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Mar 26, 2014 at 11:32 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 9, 2014 at 5:28 PM, Wang, Jing ji...@fast.au.fujitsu.com wrote: Enclosed is the patch to implement the requirement that issue log message to suggest VACUUM FULL if a table is nearly empty. The requirement comes from the Postgresql TODO list. If the relpage of the table RELPAGES_VALUES_THRESHOLD(default 1000) then the table is considered to be large enough. If the free_space/total_space FREESPACE_PERCENTAGE_THRESHOLD(default 0.5) then the table is considered to have large numbers of unused rows. I'm not sure that we want people to automatically VF a table just because it's 2x bloated. Doesn't it depend on the table size? And in sort of a funny way, too, like, if the tables is small, 2x bloat is not wasting much disk space, but getting rid of it is probably easy, so maybe you should - but if the table is a terabyte, even 50% bloat might be pretty intolerable, but whether it makes sense to try to get rid of it depends on your access pattern. I'm not really too sure whether it makes sense to try to make an automated recommendation here, or maybe only in egregious cases. I think here main difficulty is to decide when it will be considered good to display such a message. As you said, that it depends on access pattern whether 50% bloat is tolerable or not, so one way could be to increase the bloat limit and table size threshold to higher value (bloat - 80%, table_size = 500M) where it would make sense to recommend VF for all cases or another way could be to consider using some auto vacuum threshold parameter like autovacuum_vacuum_scale_factor to calculate threshold value for issuing this message. I think parameter like scale factor can make sense as to an extent this parameter is an indicative of how much dead space percentage is tolerable for user. I don't think there's a very direct relationship between those things. One of the problems we repeatedly encounter is that the scale factor only governs when the table becomes eligible to be vacuumed; once that happens, it takes some amount of time - ideally 1 minute but more if all workers are busy or if autovacuum_naptime has unfortunately been increased - for the vacuum to start, and then more time after that for the vacuum to finish. I think the latter is really the kicker. Even if your system is relatively well-tuned, a big table takes a long time to vacuum, and you're going to continue accumulating bloat while the vacuum is running. Another aspect of my ambivalence about this is that VACUUM FULL tends to get overused as it is. If we start making automated recommendations in that direction, it might cause people to lean that way even further, which would not, on the whole, be a good thing. On the other hand, if the table is 80% dead space, it's a pretty good bet that a VACUUM FULL is needed. Even there, though, the VACUUM FULL may be a pretty temporary fix unless the user also fixes the underlying issue that caused the table bloat to accumulate in the first place. Sometimes bloat is caused by a one-off issue, like one long-running query. But sometimes it's caused by something systematic, like setting the cost limit too low or the nap time too high. Just telling the user to run VACUUM FULL is likely to make the user conclude that PostgreSQL sucks, I have to keep running VACUUM FULL all the time, taking a full-table lock. Of course, really giving the user a useful level of information here is probably impractical in a log message anyway, but that doesn't mean giving them too little information to do something useful is better. Yet another thing that bothers me about this is that the table might already be getting vacuumed very frequently. If you start getting this message from autovac once per minute, you're going to think that's pretty stupid - especially after you try VACUUM FULL and the problem comes right back because of constant update pressure. -- 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] Securing make check (CVE-2014-0067)
On Sun, Mar 30, 2014 at 3:52 PM, Christoph Berg c...@df7cb.de wrote: Re: Noah Misch 2014-03-30 20140330014531.ge170...@tornado.leadboat.com On Sat, Mar 29, 2014 at 10:04:55AM +0100, Christoph Berg wrote: Fwiw, to relocate the pg_regress socket dir, there is already the possibility to run make check EXTRA_REGRESS_OPTS=--host=/tmp. (With the pending fix I sent yesterday to extend this to contrib/test_decoding.) That doesn't work for make check, because the postmaster ends up with listen_addresses=/tmp. Oh, right. There's this other patch which apparently works so well that I already forgot it's there: Enable pg_regress --host=/path/to/socket: https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-9.4/trunk/view/head:/debian/patches/60-pg_regress_socketdir.patch Wasn't this patch submitted for inclusion in PostgreSQL at some point? Did we have some good reason for not accepting 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] PQputCopyData dont signal error
steve k wrote I started with this: DBInsert_excerpts6_test_cpdlc.cpp http://postgresql.1045698.n5.nabble.com/file/n5798049/DBInsert_excerpts6_test_cpdlc.cpp Can you point out to me where in that code you've followed this instruction from the documentation: After successfully calling PQputCopyEnd, call PQgetResult to obtain the final result status of the COPY command. One can wait for this result to be available in the usual way. Then return to normal operation. Since I cannot seem to find it. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798077.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] PATCH: decreasing memory needlessly consumed by array_agg
On Thu, Mar 27, 2014 at 10:00 PM, Tomas Vondra t...@fuzzy.cz wrote: The patch also does one more thing - it changes how the arrays (in the aggregate state) grow. Originally it worked like this /* initial size */ astate-alen = 64; /* when full, grow exponentially */ if (astate-nelems = astate-alen) astate-alen *= 2; so the array length would grow like this 64 - 128 - 256 - 512 ... (note we're talking about elements, not bytes, so with with 32-bit integers it's actually 256B - 512B - 1024B - ...). While I do understand the point of this (minimizing palloc overhead), I find this pretty dangerous, especially in case of array_agg(). I've modified the growth like this: /* initial size */ astate-alen = 4; /* when full, grow exponentially */ if (astate-nelems = astate-alen) astate-alen += 4; I admit that might be a bit too aggressive, and maybe there's a better way to do this - with better balance between safety and speed. I was thinking about something like this: /* initial size */ astate-alen = 4; /* when full, grow exponentially */ if (astate-nelems = astate-alen) if (astate-alen 128) astate-alen *= 2; else astate-alen += 128; i.e. initial size with exponential growth, but capped at 128B. So I think this kind of thing is very sensible, but the last time I suggested something similar, I got told no: http://www.postgresql.org/message-id/caeylb_wlght7yjlare9ppert5rkd5zjbb15te+kpgejgqko...@mail.gmail.com But I think you're right and the objections previously raised are wrong. I suspect that the point at which we should stop doubling is higher than 128 elements, because that's only 8kB, which really isn't that big - and the idea that the resizing overhead takes only amortized constant time is surely appealing. But I still think that doubling *forever* is a bad idea, here and there. The fact that we've written the code that way in lots of places doesn't make it the right algorithm. -- 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] Still something fishy in the fastpath lock stuff
Robert Haas robertmh...@gmail.com writes: On Wed, Mar 26, 2014 at 12:59 AM, Robert Haas robertmh...@gmail.com wrote: It seems to me that the simplest thing to do might be just this: -static FastPathStrongRelationLockData *FastPathStrongRelationLocks; +static volatile FastPathStrongRelationLockData *FastPathStrongRelationLocks; Do you see any problem with that approach? Hearing nothing, I went ahead and did this. Sorry, I've been preoccupied with personal matters for the past little while, and hadn't had time to think about this. After a review of the code it looks probably all right to me. I see that the failure on prairiedog only occurred once, so I don't know how we'll know whether this fixed the problem that caused that failure or merely fixed a problem that could cause a failure, but I'm not sure what else we can really do at this point. Well, it's only one failure, but it occurred after prairiedog had run a grand total of 20 buildfarm cycles on 9.2 and newer, so I'm thinking that the probability of failure on that machine is more than epsilon. If we don't see it again for a good long while then I'll believe that this fixed it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
On Mon, Mar 31, 2014 at 8:08 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Note the last sentence. To avoid the undefined behavior, you have to pass a buffer that's large enough to hold the whole result, and then truncate the result. I was working off the glibc documentation, which says: The return value is the length of the entire transformed string. This value is not affected by the value of size, but if it is greater or equal than size, it means that the transformed string did not entirely fit in the array to. In this case, only as much of the string as actually fits was stored. To get the whole transformed string, call strxfrm again with a bigger output array. It looks like this may be a glibc-ism. I'm not sure whether or not it's worth attempting to benefit from glibc's apparent additional guarantees here. Obviously that is something that would have to be judged by weighing any actual additional benefit. FWIW, I didn't actually imagine that this was a trick I was exploiting. I wouldn't be surprised if this wasn't very important in practice. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Securing make check (CVE-2014-0067)
Robert Haas robertmh...@gmail.com writes: On Sun, Mar 30, 2014 at 3:52 PM, Christoph Berg c...@df7cb.de wrote: Oh, right. There's this other patch which apparently works so well that I already forgot it's there: Enable pg_regress --host=/path/to/socket: https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-9.4/trunk/view/head:/debian/patches/60-pg_regress_socketdir.patch Wasn't this patch submitted for inclusion in PostgreSQL at some point? Did we have some good reason for not accepting it? Well, other than very bad coding style (casual disregard of the message localizability guidelines, and the dubious practice of two different format strings in one printf call) it doesn't seem like a bad idea on its face to allow pg_regress to set a socket path. But do we want pg_regress to *not* specify a listen_addresses string? I think we are currently setting that to empty intentionally on non-Windows. If it defaults to not-empty, which is what I think will happen with this patch, isn't that opening a different security hole? I think we need a somewhat larger understanding of what cases we're trying to support, in any case ... 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] Cube extension kNN support
On Mon, Mar 31, 2014 at 12:09 PM, Alexander Korotkov aekorot...@gmail.com wrote: I'm not sure if it is doable at all, but are there any possibility to implement here, or, what would be just great, any ready/half ready solutions of a Hamming distance based kNN search? Cube dealing with float8 numbers. There is another patch making it work with other number types. But Hamming distance is for bit vectors, isn't it? It can be generalized as for character vectors. Though, I agree, that was an off topic question in some extent. I was wondering if there were any postgres related indexable Hamming/Manhattan distance experiments/thoughts/discussions, if kNN can be used here or not, because from my understanding it can be represented as spatial (I might be very wrong here). -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.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] Cube extension kNN support
On Mon, Mar 31, 2014 at 10:01 PM, Sergey Konoplev gray...@gmail.com wrote: On Thu, Mar 27, 2014 at 3:26 PM, Sergey Konoplev gray...@gmail.com wrote: On Sun, Sep 22, 2013 at 4:38 PM, Stas Kelvich stas.kelv...@gmail.com wrote: Here is the patch that introduces kNN search for cubes with euclidean, taxicab and chebyshev distances. What is the status of this patch? Referring to our private conversation with Alexander Korotkov, the patch is in WIP state currently, and, hopefully, will be ready by 9.5. I'm ready to actively participate in its testing on a real world production set of data. I'm not sure if it is doable at all, but are there any possibility to implement here, or, what would be just great, any ready/half ready solutions of a Hamming distance based kNN search? Cube dealing with float8 numbers. There is another patch making it work with other number types. But Hamming distance is for bit vectors, isn't it? With best regards, Alexander Korotkov.
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: - CREATE SEQUENCE [ IF NOT EXISTS ] - CREATE DOMAIN [ IF NOT EXISTS ] - CREATE EVENT TRIGGER [ IF NOT EXISTS ] - CREATE ROLE [ IF NOT EXISTS ] Seems that no one reviewed this part or was rejected with others? Why don't those fall into the same concern, specifically that what we really want is 'CREATE-OR-REPLACE' semantics for them instead? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Mar 31, 2014 at 4:52 PM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: - CREATE SEQUENCE [ IF NOT EXISTS ] - CREATE DOMAIN [ IF NOT EXISTS ] - CREATE EVENT TRIGGER [ IF NOT EXISTS ] - CREATE ROLE [ IF NOT EXISTS ] Seems that no one reviewed this part or was rejected with others? Why don't those fall into the same concern, specifically that what we really want is 'CREATE-OR-REPLACE' semantics for them instead? Ok, but I think this semantics is desirable just to CREATE DOMAIN and CREATE EVENT TRIGGER. Isn't desirable add CINE to SEQUENCEs and ROLEs? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Mon, Mar 31, 2014 at 4:52 PM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: - CREATE SEQUENCE [ IF NOT EXISTS ] - CREATE DOMAIN [ IF NOT EXISTS ] - CREATE EVENT TRIGGER [ IF NOT EXISTS ] - CREATE ROLE [ IF NOT EXISTS ] Seems that no one reviewed this part or was rejected with others? Why don't those fall into the same concern, specifically that what we really want is 'CREATE-OR-REPLACE' semantics for them instead? Ok, but I think this semantics is desirable just to CREATE DOMAIN and CREATE EVENT TRIGGER. Isn't desirable add CINE to SEQUENCEs and ROLEs? Why would it be difficult to have COR for sequences..? Or roles? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PQputCopyData dont signal error
David, The code I posted is what I started/finished with. As I tried to figure things out I kept adding PQgetResult, PQresultStatus and PQresultErrorMessage calls and writing the results to the log file. By the time I was done it was so messy that I stripped all that messaging/logging back out and made sure it ran with good data and moved on to something else because I couldn't waste any more time on it and figured I'd look it over again if the insert with multiple values clauses was a bust too (which thankfully it wasn't). When I got the multiple values clause insert replacements going and compared processing times and error handling I dumped all the old debugging version stuff because that's not the kind of thing you commit to version control. In the end I didn't think it mattered because I wasn't going to use any of it since I had no way to know if bad data actually didn't get written by virtue of its being bad data. And no return code ever indicated anything was awry. I'd love to see an actual working example where an executing C++ program was able to in fact determine that copy data containing bad data that was sent by the C++ program was rejected by the server and subsequently the C++ program was able to raise/log/notify specifically which block of data failed and then log information about it. However, all I ever got was PGRES_COMMAND_OK whether or not the data that was sent was rejected as containing bad data or not. Effectively these were silent failures. Sorry I can't provide more information but I do appreciate your time. If you can't get any further with it I understand and don't expect another reply. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798104.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] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Mar 31, 2014 at 5:00 PM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: On Mon, Mar 31, 2014 at 4:52 PM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: - CREATE SEQUENCE [ IF NOT EXISTS ] - CREATE DOMAIN [ IF NOT EXISTS ] - CREATE EVENT TRIGGER [ IF NOT EXISTS ] - CREATE ROLE [ IF NOT EXISTS ] Seems that no one reviewed this part or was rejected with others? Why don't those fall into the same concern, specifically that what we really want is 'CREATE-OR-REPLACE' semantics for them instead? Ok, but I think this semantics is desirable just to CREATE DOMAIN and CREATE EVENT TRIGGER. Isn't desirable add CINE to SEQUENCEs and ROLEs? Why would it be difficult to have COR for sequences..? Or roles? Because they maintain user data? -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Securing make check (CVE-2014-0067)
Re: Tom Lane 2014-03-31 22183.1396293...@sss.pgh.pa.us Enable pg_regress --host=/path/to/socket: https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-9.4/trunk/view/head:/debian/patches/60-pg_regress_socketdir.patch Wasn't this patch submitted for inclusion in PostgreSQL at some point? Did we have some good reason for not accepting it? Well, other than very bad coding style (casual disregard of the message localizability guidelines, and the dubious practice of two different format strings in one printf call) it doesn't seem like a bad idea on I had posted it here before, but I've got around to formally put it into a CF, so sorry for not cleaning up. The double-formatstr thing was done to avoid the need for twice as much almost-identical formatstrs. There's probably smarter ways to do that. its face to allow pg_regress to set a socket path. But do we want pg_regress to *not* specify a listen_addresses string? I think we are currently setting that to empty intentionally on non-Windows. The patch tries to reuse the existing switches; --host=/tmp is just the equivalent of the host=/tmp connection parameter. Of course it could as well introduce a new parameter --socket-dir=/tmp. If it defaults to not-empty, which is what I think will happen with this patch, isn't that opening a different security hole? I think we need a somewhat larger understanding of what cases we're trying to support, in any case ... The patch solves a usability problem, security wasn't a concern at the time of writing. I'll rethink that bit and come up with a better solution. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
Peter Geoghegan p...@heroku.com writes: On Mon, Mar 31, 2014 at 8:08 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Note the last sentence. To avoid the undefined behavior, you have to pass a buffer that's large enough to hold the whole result, and then truncate the result. I was working off the glibc documentation, which says: The return value is the length of the entire transformed string. This value is not affected by the value of size, but if it is greater or equal than size, it means that the transformed string did not entirely fit in the array to. In this case, only as much of the string as actually fits was stored. To get the whole transformed string, call strxfrm again with a bigger output array. It looks like this may be a glibc-ism. Possibly worth noting is that on my RHEL6 and Fedora machines, man strxfrm says the same thing as the POSIX spec. Likewise on OS X. I don't think we can rely on what you suggest. 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] Securing make check (CVE-2014-0067)
On Mon, Mar 31, 2014 at 3:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Mar 30, 2014 at 3:52 PM, Christoph Berg c...@df7cb.de wrote: Oh, right. There's this other patch which apparently works so well that I already forgot it's there: Enable pg_regress --host=/path/to/socket: https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-9.4/trunk/view/head:/debian/patches/60-pg_regress_socketdir.patch Wasn't this patch submitted for inclusion in PostgreSQL at some point? Did we have some good reason for not accepting it? Well, other than very bad coding style (casual disregard of the message localizability guidelines, and the dubious practice of two different format strings in one printf call) it doesn't seem like a bad idea on its face to allow pg_regress to set a socket path. But do we want pg_regress to *not* specify a listen_addresses string? I think we are currently setting that to empty intentionally on non-Windows. If it defaults to not-empty, which is what I think will happen with this patch, isn't that opening a different security hole? Good points... I think we need a somewhat larger understanding of what cases we're trying to support, in any case ... My impression is that some people just want the postmaster that gets started for the pg_regress runs to listen on a socket rather than a port. Obviously that won't work on Windows, but it seems like a pretty reasonable thing to want to do everywhere else, especially in light of the security issues around make check we've been discussing. There's not going to be any convenient, cross-platform, unprivileged way of preventing other applications on the same system from connecting to a local TCP socket, whereas with a UNIX socket you can nail that door shut pretty tight using filesystem permissions. That isn't to say that you can't secure the TCP socket, but it's gotta be done through the user auth methods and is only as good as those methods are. I think an adequate solution can be achieved that way, but with the UNIX socket method the attack surface area is a whole lot more restricted, which seems appealing from where I sit. -- 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] Patch to add support of IF NOT EXISTS to others CREATE statements
* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: Because they maintain user data? Eh? You mean like the sequence #? Yes, I'd expect 'CREATE OR REPLACE SEQUENCE' to want a minvalue or something on a 'replace' case to ensure that it doesn't roll backwards unless explicitly asked for. Perhaps the same for any non-default parameters as well, though I'd look at the other COR cases to see what they do. CREATE OR REPLACE ROLE is actually easier, no? All you'd be updating are the various role attributes, I'd think, since only those are available at CREATE time today. Any role memberships or ownership would be left alone. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PQputCopyData dont signal error
steve k wrote Sorry I can't provide more information but I do appreciate your time. If you can't get any further with it I understand and don't expect another reply. For the benefit of others I'm reading this as basically you've found a better way to do this so you are no longer concerned with correcting the broken (incomplete) code you have provided. It should be as simple as adding one more if statement between the copy-end check and the overall failure check to see whether the copy command itself failed in addition to the existing checks to see if sending the data or ending the data send failed. I will not pretend to write c code but the general logic and requirements seems quite clear from the documentation I have read/shown. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798115.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] Archive recovery won't be completed on some situation.
On Fri, Mar 28, 2014 at 1:06 AM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Mmm. I don't think it is relevant to this problem. The problem specific here is 'The database was running until just now, but shutdown the master (by pacemaker), then restart, won't run anymore'. Deleting backup_label after immediate shutdown is the radical measure but existing system would be saved by the option. I don't find that very radical at all. The backup_label file is *supposed* to be removed on the master if it crashes during the backup; and it should never be removed from the backup itself. At least that's how I understand it. Unfortunately, people too often remove the file from the backup and, judging by your report, leave it there on the master. (We could try to fix this confusion - and thereby confuse all the people who understand it now - by changing things so that you have to leave the file there on the master, and remove it from the backup. Bwahaha!) -- 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] posting tree compression (WAS: Proposal: fix range queries in btree_gin)
On Fri, Mar 28, 2014 at 11:30 AM, Alexander Korotkov aekorot...@gmail.com wrote: after reading Heikki Linnakangas presentation about GIN from Nordic PGDay, I figure out that btree_gin became much more attractive. http://hlinnaka.iki.fi/presentations/NordicPGDay2014-GIN.pdf This is a mighty interesting presentation. Could the posting tree compression described on the posting tree page format slides (pp. 16-17, I think) be used for btree also? Would we get similar benefits? How much more expensive are updates with the new system? -- 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] posting tree compression (WAS: Proposal: fix range queries in btree_gin)
On Mon, Mar 31, 2014 at 6:21 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 28, 2014 at 11:30 AM, Alexander Korotkov aekorot...@gmail.com wrote: after reading Heikki Linnakangas presentation about GIN from Nordic PGDay, I figure out that btree_gin became much more attractive. http://hlinnaka.iki.fi/presentations/NordicPGDay2014-GIN.pdf This is a mighty interesting presentation. Could the posting tree compression described on the posting tree page format slides (pp. 16-17, I think) be used for btree also? Would we get similar benefits? How much more expensive are updates with the new system? I'd believe so, but it can make updates quite complicated. I was going to take a stab at prefix-compression in b-tree, I could explore delta compression of the tids as well. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] posting tree compression (WAS: Proposal: fix range queries in btree_gin)
Claudio Freire klaussfre...@gmail.com writes: On Mon, Mar 31, 2014 at 6:21 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 28, 2014 at 11:30 AM, Alexander Korotkov aekorot...@gmail.com wrote: after reading Heikki Linnakangas presentation about GIN from Nordic PGDay, I figure out that btree_gin became much more attractive. http://hlinnaka.iki.fi/presentations/NordicPGDay2014-GIN.pdf This is a mighty interesting presentation. Could the posting tree compression described on the posting tree page format slides (pp. 16-17, I think) be used for btree also? Would we get similar benefits? How much more expensive are updates with the new system? I'd believe so, but it can make updates quite complicated. I was going to take a stab at prefix-compression in b-tree, I could explore delta compression of the tids as well. Prefix compression definitely seems like it could be a win thanks to index ordering (although note that on little-endian machines, you need to be careful about which end of an integer is the prefix). I'm pretty dubious about tid deltas being useful for btree, though. GIN has the luxury of being able to sort a lot of tids into tid order, btree doesn'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] posting tree compression (WAS: Proposal: fix range queries in btree_gin)
On Mon, Mar 31, 2014 at 6:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Claudio Freire klaussfre...@gmail.com writes: On Mon, Mar 31, 2014 at 6:21 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 28, 2014 at 11:30 AM, Alexander Korotkov aekorot...@gmail.com wrote: after reading Heikki Linnakangas presentation about GIN from Nordic PGDay, I figure out that btree_gin became much more attractive. http://hlinnaka.iki.fi/presentations/NordicPGDay2014-GIN.pdf This is a mighty interesting presentation. Could the posting tree compression described on the posting tree page format slides (pp. 16-17, I think) be used for btree also? Would we get similar benefits? How much more expensive are updates with the new system? I'd believe so, but it can make updates quite complicated. I was going to take a stab at prefix-compression in b-tree, I could explore delta compression of the tids as well. Prefix compression definitely seems like it could be a win thanks to index ordering (although note that on little-endian machines, you need to be careful about which end of an integer is the prefix). I'm pretty dubious about tid deltas being useful for btree, though. GIN has the luxury of being able to sort a lot of tids into tid order, btree doesn't. You still have lots of natural correlation in many real-world cases. Still, I agree that the overhead of maintaining such delta-compressed tid list can (and probably will) outweight the advantage. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Mon, Mar 31, 2014 at 5:46 PM, Stephen Frost sfr...@snowman.net wrote: * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote: Because they maintain user data? Eh? You mean like the sequence #? Yes, I'd expect 'CREATE OR REPLACE SEQUENCE' to want a minvalue or something on a 'replace' case to ensure that it doesn't roll backwards unless explicitly asked for. Perhaps the same for any non-default parameters as well, though I'd look at the other COR cases to see what they do. You mean if we execute 'CREATE OR REPLACE' must we verify the default values of this statement and compare with the existing ones? CREATE OR REPLACE ROLE is actually easier, no? All you'd be updating are the various role attributes, I'd think, since only those are available at CREATE time today. Any role memberships or ownership would be left alone. Think about the statements below: CREATE ROLE test NOLOGIN; CREATE OR REPLACE ROLE test; If we execute the statements above the result should be the role 'test' can login. Correct? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)
On Mon, Mar 31, 2014 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Possibly worth noting is that on my RHEL6 and Fedora machines,
man strxfrm says the same thing as the POSIX spec. Likewise on OS X.
I don't think we can rely on what you suggest.
Okay. Attached revision only trusts strxfrm() blobs (as far as that
goes) when the buffer passed to strxfrm() was sufficiently large that
the blob could fully fit. We're now managing two buffers as part of
the text sort support state (for leading poor man's keys, rather than
just for non-leading keys as before) - one for the original string (to
put in a NULL byte, since like strcoll(), strxfrm() expects that), and
the other to temporarily store the blob before memcpy()ing over to the
pass-by-value poor man's normalized key Datum. These are the same two
persistent sortsupport-state buffers used when sorting a non-leading
text key, where there is one for each string that needs to have a NULL
byte appended.
This appears to perform at least as well as the prior revision, and
possibly even appreciably better. I guess that glibc was internally
managing a buffer to do much the same thing, so perhaps we gain
something more from mostly avoiding that with a longer lived buffer.
Perhaps you could investigate the performance of this new revision too, Thom?
I'd really like to see this basic approach expanded to store pseudo
leading keys in internal B-Tree pages too, per my earlier proposals.
At the very least, B-Tree index builds should benefit from this. I
think I'm really only scratching the surface here, both in terms of
the number of places in the code where a poor man's normalized key
could usefully be exploited, as well as in terms of the number of
B-Tree operator classes that could be made to provide one.
--
Peter Geoghegan
*** a/src/backend/utils/adt/varlena.c
--- b/src/backend/utils/adt/varlena.c
***
*** 17,22
--- 17,23
#include ctype.h
#include limits.h
+ #include access/nbtree.h
#include access/tuptoaster.h
#include catalog/pg_collation.h
#include catalog/pg_type.h
***
*** 29,34
--- 30,36
#include utils/bytea.h
#include utils/lsyscache.h
#include utils/pg_locale.h
+ #include utils/sortsupport.h
/* GUC variable */
*** typedef struct
*** 50,61
--- 52,85
int skiptable[256]; /* skip distance for given mismatched char */
} TextPositionState;
+ typedef struct
+ {
+ char *buf1; /* Also used as poorman original string buf */
+ char *buf2; /* Used to store leading key/poor man blob */
+ int buflen1;
+ int buflen2;
+ #ifdef HAVE_LOCALE_T
+ pg_locale_t locale;
+ #endif
+ } TextSortSupport;
+
+ /*
+ * This should be large enough that most strings will be fit, but small enough
+ * that we feel comfortable putting it on the stack
+ */
+ #define TEXTBUFLEN 1024
+
#define DatumGetUnknownP(X) ((unknown *) PG_DETOAST_DATUM(X))
#define DatumGetUnknownPCopy(X) ((unknown *) PG_DETOAST_DATUM_COPY(X))
#define PG_GETARG_UNKNOWN_P(n) DatumGetUnknownP(PG_GETARG_DATUM(n))
#define PG_GETARG_UNKNOWN_P_COPY(n) DatumGetUnknownPCopy(PG_GETARG_DATUM(n))
#define PG_RETURN_UNKNOWN_P(x) PG_RETURN_POINTER(x)
+ static void btpoorman_worker(SortSupport ssup, Oid collid);
+ static int bttextfastcmp_c(Datum x, Datum y, SortSupport ssup);
+ static int bttextfastcmp_locale(Datum x, Datum y, SortSupport ssup);
+ static int bttextfastcmp_locale_poorman(Datum x, Datum y, SortSupport ssup);
+ static Datum bttext_convert(Datum d, SortSupport ssup);
static int32 text_length(Datum str);
static text *text_catenate(text *t1, text *t2);
static text *text_substring(Datum str,
*** varstr_cmp(char *arg1, int len1, char *a
*** 1356,1365
}
else
{
! #define STACKBUFLEN 1024
!
! char a1buf[STACKBUFLEN];
! char a2buf[STACKBUFLEN];
char *a1p,
*a2p;
--- 1380,1387
}
else
{
! char a1buf[TEXTBUFLEN];
! char a2buf[TEXTBUFLEN];
char *a1p,
*a2p;
*** varstr_cmp(char *arg1, int len1, char *a
*** 1393,1416
int a2len;
int r;
! if (len1 = STACKBUFLEN / 2)
{
a1len = len1 * 2 + 2;
a1p = palloc(a1len);
}
else
{
! a1len = STACKBUFLEN;
a1p = a1buf;
}
! if (len2 = STACKBUFLEN / 2)
{
a2len = len2 * 2 + 2;
a2p = palloc(a2len);
}
else
{
! a2len = STACKBUFLEN;
a2p = a2buf;
}
--- 1415,1438
int a2len;
int r;
! if (len1 = TEXTBUFLEN / 2)
{
a1len = len1 * 2 + 2;
a1p = palloc(a1len);
}
else
{
! a1len = TEXTBUFLEN;
a1p = a1buf;
}
! if (len2 = TEXTBUFLEN / 2)
{
a2len = len2 * 2 + 2;
a2p = palloc(a2len);
}
else
{
! a2len = TEXTBUFLEN;
a2p = a2buf;
}
*** varstr_cmp(char *arg1, int len1, char *a
*** 1475,1485
}
#endif /* WIN32
Re: [HACKERS] [BUG FIX] Compare returned value by socket() against PGINVALID_SOCKET instead of 0
On Wed, Dec 25, 2013 at 01:35:15PM +0100, Joel Jacobson wrote:
Hi,
I've tried to fix some bugs reported by Andrey Karpov in an article at
http://www.viva64.com/en/b/0227/
The value returned by socket() is unsigned on Windows and can thus not
be checked if less than zero to detect an error, instead
PGINVALID_SOCKET should be used, which is hard-coded to -1 on
non-windows platforms.
I reviewed this patch and you are correct that we are not handling
socket() and accept() returns properly on Windows. We were doing it
properly in most place in the backend, but your patch fixes the
remaining places:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms740516%28v=vs.85%29.aspx
However, libpq doesn't seem to be doing much to handle Windows properly
in this area. I have adjusted libpq to map socket to -1, but the proper
fix is to distribute pgsocket and PGINVALID_SOCKET checks throughout the
libpq code. I am not sure how to handle PQsocket() --- should it still
return -1? Having the return value be conditional on the operating
system is ugly. How much of this should be backpatched? Why aren't we
getting warnings on Windows about assigning the socket() return value to
an integer?
Updated patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
new file mode 100644
index 31ade0b..f674372
*** a/src/backend/libpq/auth.c
--- b/src/backend/libpq/auth.c
*** ident_inet(hbaPort *port)
*** 1453,1459
sock_fd = socket(ident_serv-ai_family, ident_serv-ai_socktype,
ident_serv-ai_protocol);
! if (sock_fd 0)
{
ereport(LOG,
(errcode_for_socket_access(),
--- 1453,1459
sock_fd = socket(ident_serv-ai_family, ident_serv-ai_socktype,
ident_serv-ai_protocol);
! if (sock_fd == PGINVALID_SOCKET)
{
ereport(LOG,
(errcode_for_socket_access(),
*** ident_inet(hbaPort *port)
*** 1533,1539
ident_response)));
ident_inet_done:
! if (sock_fd = 0)
closesocket(sock_fd);
pg_freeaddrinfo_all(remote_addr.addr.ss_family, ident_serv);
pg_freeaddrinfo_all(local_addr.addr.ss_family, la);
--- 1533,1539
ident_response)));
ident_inet_done:
! if (sock_fd != PGINVALID_SOCKET)
closesocket(sock_fd);
pg_freeaddrinfo_all(remote_addr.addr.ss_family, ident_serv);
pg_freeaddrinfo_all(local_addr.addr.ss_family, la);
*** CheckRADIUSAuth(Port *port)
*** 2351,2357
packet-length = htons(packet-length);
sock = socket(serveraddrs[0].ai_family, SOCK_DGRAM, 0);
! if (sock 0)
{
ereport(LOG,
(errmsg(could not create RADIUS socket: %m)));
--- 2351,2357
packet-length = htons(packet-length);
sock = socket(serveraddrs[0].ai_family, SOCK_DGRAM, 0);
! if (sock == PGINVALID_SOCKET)
{
ereport(LOG,
(errmsg(could not create RADIUS socket: %m)));
diff --git a/src/backend/libpq/ip.c b/src/backend/libpq/ip.c
new file mode 100644
index 7e8fc78..4db64fb
*** a/src/backend/libpq/ip.c
--- b/src/backend/libpq/ip.c
*** pg_foreach_ifaddr(PgIfAddrCallback callb
*** 670,676
total;
sock = socket(AF_INET, SOCK_DGRAM, 0);
! if (sock == -1)
return -1;
while (n_buffer 1024 * 100)
--- 670,676
total;
sock = socket(AF_INET, SOCK_DGRAM, 0);
! if (sock == PGINVALID_SOCKET)
return -1;
while (n_buffer 1024 * 100)
*** pg_foreach_ifaddr(PgIfAddrCallback callb
*** 711,717
#ifdef HAVE_IPV6
/* We'll need an IPv6 socket too for the SIOCGLIFNETMASK ioctls */
sock6 = socket(AF_INET6, SOCK_DGRAM, 0);
! if (sock6 == -1)
{
free(buffer);
close(sock);
--- 711,717
#ifdef HAVE_IPV6
/* We'll need an IPv6 socket too for the SIOCGLIFNETMASK ioctls */
sock6 = socket(AF_INET6, SOCK_DGRAM, 0);
! if (sock6 == PGINVALID_SOCKET)
{
free(buffer);
close(sock);
*** pg_foreach_ifaddr(PgIfAddrCallback callb
*** 788,797
char *ptr,
*buffer = NULL;
size_t n_buffer = 1024;
! int sock;
sock = socket(AF_INET, SOCK_DGRAM, 0);
! if (sock == -1)
return -1;
while (n_buffer 1024 * 100)
--- 788,797
char *ptr,
*buffer = NULL;
size_t n_buffer = 1024;
! pgsocket sock;
sock = socket(AF_INET, SOCK_DGRAM, 0);
! if (sock == PGINVALID_SOCKET)
return -1;
while (n_buffer 1024 * 100)
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
new file mode 100644
index 1eae183..0179451
*** a/src/backend/libpq/pqcomm.c
--- b/src/backend/libpq/pqcomm.c
*** StreamServerPort(int family, char *hostN
*** 392,398
break;
}
! if ((fd = socket(addr-ai_family, SOCK_STREAM, 0)) 0)
{
ereport(LOG,
(errcode_for_socket_access(),
--- 392,398
break;
}
Re: [HACKERS] Fix memset usage in pgcrypto
On Thu, Dec 26, 2013 at 03:42:12PM +0200, Marko Kreen wrote: http://www.viva64.com/en/b/0227/ reported that on-stack memset()s might be optimized away by compilers. Fix it. * Replace memset() with px_memset() * Add px_memset to copy_crlf() * ADd px_memset to pgp-s2k.c Where are we on this patch? Seems it needs backpatching too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comment in src/backend/commands/vacuumlazy.c
On Mon, Mar 31, 2014 at 11:44 PM, Robert Haas robertmh...@gmail.com wrote:
On Mon, Mar 24, 2014 at 12:28 AM, Amit Langote amitlangot...@gmail.com
wrote:
Hi,
Note the following comment in
src/backend/commands/vacuumlazy.c:lazy_scan_heap()
1088 /* If no indexes, make log report that lazy_vacuum_heap
would've made */
1089 if (vacuumed_pages)
1090 ereport(elevel,
Just wondering if it would read better as:
1088 /* Make the log report that lazy_vacuum_heap would've made
had there been no indexes */
Is that correct?
No. Your rewrite means the opposite of what the comment means now.
vacuumed_pages will be non-zero only if the relation does NOT have
indexes.
You are correct. It does sound opposite of what is actually happening:
Somewhere in c:lazy_scan_heap(),
941 /*
942 * If there are no indexes then we can vacuum the page right now
943 * instead of doing a second scan.
944 */
945 if (nindexes == 0
946 vacrelstats-num_dead_tuples 0)
947 {
948 /* Remove tuples from heap */
949 lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats, vmbuffer);
950 has_dead_tuples = false;
951
952 /*
953 * Forget the now-vacuumed tuples, and press on, but be careful
954 * not to reset latestRemovedXid since we want that value to be
955 * valid.
956 */
957 vacrelstats-num_dead_tuples = 0;
958 vacuumed_pages++;
959 }
Sorry about the noise.
--
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Tue, Apr 1, 2014 at 7:28 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Think about the statements below: CREATE ROLE test NOLOGIN; CREATE OR REPLACE ROLE test; If we execute the statements above the result should be the role 'test' can login. Correct? Except if I am missing something, the second query means that it is going to replace the existing user test with a new one, with the settings specified in the 2nd query, all being default values. As the default for login is NOLOGIN, the user test should not be able to log in the server. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
* Michael Paquier (michael.paqu...@gmail.com) wrote: On Tue, Apr 1, 2014 at 7:28 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Think about the statements below: CREATE ROLE test NOLOGIN; CREATE OR REPLACE ROLE test; If we execute the statements above the result should be the role 'test' can login. Correct? Except if I am missing something, the second query means that it is going to replace the existing user test with a new one, with the settings specified in the 2nd query, all being default values. As the default for login is NOLOGIN, the user test should not be able to log in the server. That's more-or-less the behavior we're trying to work out. I've been meaning to go back and look at what we've been doing with the existing COR cases and just haven't gotten to it yet. The pertinent question being if we assume the user intended for the values not specified to be reset to their defaults, or not. Where this is a bit more interesting is in the case of sequences, where resetting the sequence to zero may cause further inserts into an existing table to fail. Of course, were a user to use 'drop if exists' followed by a 'create', they'd get the same behavior.. However, 'create if not exists' would leave the sequence alone, but in a potentially unknown state. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Tue, Apr 1, 2014 at 1:14 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Apr 1, 2014 at 7:28 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Think about the statements below: CREATE ROLE test NOLOGIN; CREATE OR REPLACE ROLE test; If we execute the statements above the result should be the role 'test' can login. Correct? Except if I am missing something, the second query means that it is going to replace the existing user test with a new one, with the settings specified in the 2nd query, all being default values. As the default for login is NOLOGIN, the user test should not be able to log in the server. Yeah... you are correct... I meant: CREATE ROLE test LOGIN; CREATE OR REPLACE ROLE test; Then the COR will replace the user 'test' setting a new default value to NOLOGIN. Correct? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] PQputCopyData dont signal error
On Tue, Apr 1, 2014 at 1:33 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: steve k wrote: I am examining other ways to do mass inserts/writes that allow for notification if some of the data contained within for some reason fails to copy/insert so that the cause of the bad data can be examined and remedied as soon as it occurs as well as writing the offending data to a log so that not all of it is lost. Have you looked at PGLoader? https://github.com/dimitri/pgloader Or pg_bulkload? It does exactly what you are looking for, aka filtering rows that failed with COPY, on top of other things. http://pgbulkload.projects.pgfoundry.org/pg_bulkload.html -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Archive recovery won't be completed on some situation.
On Monday, March 31, 2014, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 28, 2014 at 1:06 AM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp javascript:; wrote: Mmm. I don't think it is relevant to this problem. The problem specific here is 'The database was running until just now, but shutdown the master (by pacemaker), then restart, won't run anymore'. Deleting backup_label after immediate shutdown is the radical measure but existing system would be saved by the option. I don't find that very radical at all. The backup_label file is *supposed* to be removed on the master if it crashes during the backup; and it should never be removed from the backup itself. At least that's how I understand it. Unfortunately, people too often remove the file from the backup and, judging by your report, leave it there on the master. At first blush it seems pretty radical to me. Just because the server was e-stopped doesn't mean the backup rsync/cp -r/scp etc. isn't still running, and it is not clear to me that yanking the backup label file out from under it wouldn't cause problems. I mean, you already have problems if you are trying to restore from that backup, but the missing file might make those problems less obvious. Of course first blush is often wrong, but... Cheers, Jeff
Re: [HACKERS] issue log message to suggest VACUUM FULL if a table is nearly empty
On Tue, Apr 1, 2014 at 12:24 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 31, 2014 at 12:35 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Mar 26, 2014 at 11:32 AM, Robert Haas robertmh...@gmail.com wrote: I'm not really too sure whether it makes sense to try to make an automated recommendation here, or maybe only in egregious cases. I think here main difficulty is to decide when it will be considered good to display such a message. As you said, that it depends on access pattern whether 50% bloat is tolerable or not, so one way could be to increase the bloat limit and table size threshold to higher value (bloat - 80%, table_size = 500M) where it would make sense to recommend VF for all cases or another way could be to consider using some auto vacuum threshold parameter like autovacuum_vacuum_scale_factor to calculate threshold value for issuing this message. I think parameter like scale factor can make sense as to an extent this parameter is an indicative of how much dead space percentage is tolerable for user. Another aspect of my ambivalence about this is that VACUUM FULL tends to get overused as it is. If we start making automated recommendations in that direction, it might cause people to lean that way even further, which would not, on the whole, be a good thing. On the other hand, if the table is 80% dead space, it's a pretty good bet that a VACUUM FULL is needed. Even there, though, the VACUUM FULL may be a pretty temporary fix unless the user also fixes the underlying issue that caused the table bloat to accumulate in the first place. Sometimes bloat is caused by a one-off issue, like one long-running query. But sometimes it's caused by something systematic, like setting the cost limit too low or the nap time too high. Right, but it can happen even if the settings for auto vacuum are done considering the general usage but as a one of case there is sudden spike in update in which case it might make sense to give such a message. However if this message keep appearing in the log every now and then, it will mean that autovacumm settings are not appropriate for the load. I think it will be difficult to know the exact reason for dead space, do you think it can make sense if the message indicates (as Hint) such that, if user observes this message repeatedly the autovacuum settings are not as per load. Another way could be to update docs to indicate the same. Just telling the user to run VACUUM FULL is likely to make the user conclude that PostgreSQL sucks, I have to keep running VACUUM FULL all the time, taking a full-table lock. Agreed user can conclude such things, but even if he figures that out himself (which is quite possible), he will reach to same conclusion unless he is aware that the reason could be the autovacuum settings. Another thought that occurred to me is might be giving such an information for Index can be more useful as there are always more chances for index bloat especially in context of below information from docs. B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is recommended. There are certain usage pattern's like always inserting data in particular (increasing/decreasing) order which can lead to bloat in above context. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements
On Tue, Apr 1, 2014 at 1:34 PM, Stephen Frost sfr...@snowman.net wrote: * Michael Paquier (michael.paqu...@gmail.com) wrote: On Tue, Apr 1, 2014 at 7:28 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: Think about the statements below: CREATE ROLE test NOLOGIN; CREATE OR REPLACE ROLE test; If we execute the statements above the result should be the role 'test' can login. Correct? Except if I am missing something, the second query means that it is going to replace the existing user test with a new one, with the settings specified in the 2nd query, all being default values. As the default for login is NOLOGIN, the user test should not be able to log in the server. That's more-or-less the behavior we're trying to work out. I've been meaning to go back and look at what we've been doing with the existing COR cases and just haven't gotten to it yet. For example, on views, COR fails if it the new view does not contain the old list of columns, same order and same data type, and can be completed with new columns. The ownership of the view remains the same as well. For functions, the argument types and return type need to remain the same. As I understand, COR are useful because they guarantee that no objects depending on it would be broken and are made when a user wants to extend an object or redefine its internals. For example, we should not allow that IMO: CREATE ROLE foo LOGIN REPLICATION; -- ok CREATE OR REPLACE ROLE foo NOREPLICATION; --error Because with the 2nd query replication would break replication. For roles, I am not completely sure how you would to that, but I would imagine that you would need to keep track of all the parameters are using non-default settings and specified directly by the user in CREATE ROLE/USER. Then COR would fail if user tries to change some of those parameters to values that do not map the non-default ones in the first query (by tracking them in a new pg_authid column, berk, without thinking about complications induced by IN ROLE, IN GROUP and friends...). Perhaps I am thinking too much though. The pertinent question being if we assume the user intended for the values not specified to be reset to their defaults, or not. Isn't it what ALTER ROLE aims at? Where this is a bit more interesting is in the case of sequences, where resetting the sequence to zero may cause further inserts into an existing table to fail. Of course, were a user to use 'drop if exists' followed by a 'create', they'd get the same behavior.. However, 'create if not exists' would leave the sequence alone, but in a potentially unknown state. You could face failures on a serial column as well by changing the increment sign of its sequence with a COR, so you would need more guarantees than a min value. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typo fix in contrib/sepgsql/relation.c
Hi, Just noticed the following in contrib/sepgsql/relation.c: 1 /* - 2 * 3 * contrib/sepgsql/label.c 4 * Attached fixes this. -- Amit sepgsql-file-label-fix.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
