Re: [HACKERS] UTF-8 encoding problem w/ libpq
Can't really blame Windows on that. On Windows, we don't require that the encoding and LC_CTYPE's charset match. The OP used UTF-8 encoding in the server, but LC_CTYPE=English_United Kingdom.1252, ie. LC_CTYPE implies WIN1252 encoding. We allow that and it generally works on Windows because in varstr_cmp, we use MultiByteToWideChar() followed by wcscoll_l(), which doesn't care about the charset implied by LC_CTYPE. But for isupper(), it matters. Does this mean that the UTF-8 messing up would disappear if the database were using a different locale for LC_CTYPE? If so, which locale should I use? This would be useful for a temporary workaround. We talked about this before and went off into the weeds about whether it was sensible to try to use towlower() and whether that wouldn't create undesirably platform-sensitive results. I wonder though if we couldn't just fix this code to not do anything to high-bit-set bytes in multibyte encodings. Yeah, we should do that. It makes no sense to call isupper or tolower on bytes belonging to multi-byte characters. Actually, I would expect that 'create table HÄUSER (...)' would create a table named 'häuser', and not a table named 'hÄuser', so towlower seems the right choice IMHO. Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] local_preload_libraries logspam
On 6/3/13 8:19 PM, Peter Geoghegan wrote: On Mon, May 13, 2013 at 3:22 PM, Peter Geoghegan p...@heroku.com wrote: Attached patch renders all loaded library... messages DEBUG1, regardless of whether local_preload_libraries or shared_preload_libraries is involved, and regardless of EXEC_BACKEND. Can someone take a look at this, please? I'd rather like to see this issue fixed. I wasn't considering this for 9.3. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create a git symbolic-ref for REL9_3_STABLE
On 6/3/13 9:43 PM, Andrew Dunstan wrote: On 06/03/2013 09:30 PM, Peter Eisentraut wrote: I suppose we'll be branching off 9.3 in a few weeks. That event always creates a service gap in the build farm and similar services, and a race in the NLS service to get everything adjusted to the new branch. The buildfarm has had a mechanism for handling this for a little while now. See http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto#Using_run_branches.pl That helps if you run all the branches anyway. But I think my request is still useful for other places. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] local_preload_libraries logspam
On 2013-06-04 08:39:18 -0400, Peter Eisentraut wrote: On 6/3/13 8:19 PM, Peter Geoghegan wrote: On Mon, May 13, 2013 at 3:22 PM, Peter Geoghegan p...@heroku.com wrote: Attached patch renders all loaded library... messages DEBUG1, regardless of whether local_preload_libraries or shared_preload_libraries is involved, and regardless of EXEC_BACKEND. Can someone take a look at this, please? I'd rather like to see this issue fixed. I wasn't considering this for 9.3. I don't really see a point in delaying it towards 9.4. The change itself is trivial, doesn't change behaviour users can rely on directly. So what's to be gained by waiting? 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] PostgreSQL Process memory architecture
On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior lior.ben-z...@hp.com wrote: No matter how I try to redesign the schema the indexes consume large amount of memory, About 8KB per index. 8KB per index -- is that a typo? that doesn't seem like a lot to me. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
No it isn't a typo, All the tables are empty and all the indexes are empty -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Tuesday, June 04, 2013 16:10 To: Ben Zeev, Lior Cc: Atri Sharma; Stephen Frost; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior lior.ben-z...@hp.com wrote: No matter how I try to redesign the schema the indexes consume large amount of memory, About 8KB per index. 8KB per index -- is that a typo? that doesn't seem like a lot to me. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimising Foreign Key checks
On 4 June 2013 01:54, Noah Misch n...@leadboat.com wrote: On Sun, Jun 02, 2013 at 10:45:21AM +0100, Simon Riggs wrote: For clarity the 4 problems are 1. SQL execution overhead 2. Memory usage 3. Memory scrolling 4. Locking overhead, specifically FPWs and WAL records from FK checks probably in that order or thereabouts. The above is why I went for a technique that avoided SQL execution entirely, as well as conserving memory by de-duplicating the values in a hash table as we go, which avoids all three problems. The fourth was solved by the more extreme approach to locking. That nicely frames the benefits of your proposals. Makes sense. I think it might be worth considering joining the after trigger queue directly to the referenced table(s), something like this... CREATE OR REPLACE FUNCTION after_trigger_queue() RETURNS SETOF tid AS $$ ... $$ LANGUAGE SQL; EXPLAIN SELECT 1 FROM ONLY order WHERE orderid IN (SELECT orderid FROM ONLY order_line WHERE ctid IN (SELECT after_trigger_queue FROM after_trigger_queue() )) FOR KEY SHARE; Agreed. Hmm, although the above is cute, it still falls down by requiring lots of memory (problem 2) and churning memory again (problem 3). Lets rethink things to put a few options on the table and see what we get... 1. Store all the after trigger events in a big queue, then execute later. That requires us to scroll it to disk to solve problem2, but it still falls foul of problem3, which becomes severe on big data. Implementation: Implement scrolling to disk for the after trigger queue. Then implement event batching in the RI handler code, similar to what is suggested above or directly as suggested by Noah on previous post. 2. Don't store FK events in the after trigger queue at all, but apply them as we go. That solves problems2 and 3. That could be considered to be in violation of the SQL standard, which requires us to apply the checks at the end of the statement. We already violate the standard with regard to uniqueness checks, so doing it here doesn't seem unacceptable. Implementation: Given we know that COPY uses a ring buffer, and given your earlier thoughts on use of a batched SQL, I have a new suggestion. Every time the ring buffer fills, we go through the last buffers accessed, pulling out all the PKs and then issue them as a single SQL statement (as above). We can do that manually, or using the block scan mentioned previously. This uses batched SQL to solve problem1. It doesn't build up a large data structure in memory, problem2, and it also solves problem3 by accessing data blocks before they fall out of the ring buffer. If there are no duplicates in the referenced table, then this behavious will do as much as possible to make accesses to the referenced table also use a small working set. (We may even wish to consider making the batched SQL use a separate ring buffer for RI accesses). That approach doesn't make any assumptions about duplicates. 3. Strip the PK values from the rows at access time and store them in a hash table, de-duplicating as we go. If that gets too big, write seldom accessed values to a temporary table which will automatically scroll to disk when it exceeds temp_buffers. We don't create the temp table until we hit work_mem, so we only create that for larger statements. Then at the end of the statement, copy the hash table into the temp table and then join to the referenced table directly. We don't guarantee the list of PK values is completely unique, but we will have significantly reduced the number of duplicates to check. Comparison: Approach 1 suffers from memory scrolling, problem3. But it follows the SQL standard. Approach 2 solves all performance problems but doesn't follow the letter of the standard (AIUI - anyone see differently?) Approach 3 solves all performance problems and follows the SQL standard Perhaps another way would be to avoid very large COPY statements altogether, breaking down loads into smaller pieces. For example pg_dump could issue COPY in 1 row chunks rather than big copy. That would mostly avoid problem2 and problem3 and is more realistic with wild data. Based on that thought, implementation option 1 looks most reasonable. Which in short summary is a) scroll after trigger queue to disk and b) batch SQL values together as Noah originally suggested, or in the SQL above. Let me know what you think of that analysis. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Add support for TAS/S_UNLOCK for aarch64
Hi, I was asked [1] to add following patch downstream, could it be considered upstream also? Thanks, Pavel. [1] https://bugzilla.redhat.com/show_bug.cgi?id=970661 From ed791f40aa117d4fc273e4b96d9295ee9571fc96 Mon Sep 17 00:00:00 2001 From: Mark Salter msal...@redhat.com Date: Tue, 4 Jun 2013 17:23:01 +0200 Subject: [PATCH] Add support for TAS/S_UNLOCK for aarch64 --- src/include/storage/s_lock.h | 19 +++ 1 file changed, 19 insertions(+) diff --git a/src/include/storage/s_lock.h b/src/include/storage/s_lock.h index ce45ffe..a2d6f63 100644 --- a/src/include/storage/s_lock.h +++ b/src/include/storage/s_lock.h @@ -336,6 +336,25 @@ tas(volatile slock_t *lock) #endif /* __arm__ */ +/* + * Use gcc builtins for AArch64. + */ +#if defined(__aarch64__) defined(HAVE_GCC_INT_ATOMICS) +#define HAS_TEST_AND_SET + +#define TAS(lock) tas(lock) + +typedef int slock_t; + +static __inline__ int +tas(volatile slock_t *lock) +{ + return __sync_lock_test_and_set(lock, 1); +} + +#define S_UNLOCK(lock) __sync_lock_release(lock) +#endif /* __aarch64__ */ + /* S/390 and S/390x Linux (32- and 64-bit zSeries) */ #if defined(__s390__) || defined(__s390x__) #define HAS_TEST_AND_SET -- 1.8.2.1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Configurable location for extension .control files
Hello, I am working with the NixOS Linux Distribution [nixos], which has a fairly radical approach to package management. If you aren't familiar with it, essentially all packages are installed in isolation - such that packages cannot interfere with each other. This is causing a bit of a problem with PostgreSQL extensions that are usually installed via CREATE EXTENSION. For extensions to be used, a .control file must be placed in SHAREDIR/extension, but this is not possible under NixOS as once PostgreSQL is installed that directory is essentially immutable. What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. Would love to hear your thoughts. Once I get confirmation on the best approach to take, I can try my hand at providing a patch. Thanks, - Ollie (ocharles) --- [nixos]: http://nixos.org -- Sent 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] Add support for TAS/S_UNLOCK for aarch64
On Tuesday, June 04, 2013 05:28:09 PM Pavel Raiskup wrote: Hi, I was asked [1] to add following patch downstream, could it be considered upstream also? Thanks, Pavel. [1] https://bugzilla.redhat.com/show_bug.cgi?id=970661 Oh, I see now it was already consulted here: http://www.postgresql.org/message-id/1368448758.23422.12.ca...@t520.redhat.com Sorry for noise, it can be discussed there. Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql \dv+ shows view size as 0 bytes
\dv+ shows the size of views as 0 bytes. This doesn't make any sense; I think it should show null. Maybe this is even the fault of the backend functions for returning a number to display in the first place. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] how to find out whether a view is updatable
I was looking for a way in which the average psql user could learn whether a view is updatable. I was expecting something in \d, \d+, \dv, \dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be through the information schema or the underlying pg_view_is_updatable function. Not even pg_views shows anything. Is this intentional or an oversight? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] local_preload_libraries logspam
On Tue, Jun 4, 2013 at 5:46 AM, Andres Freund and...@2ndquadrant.com wrote: I don't really see a point in delaying it towards 9.4. Me neither, obviously. It's not as if someone was willing to speak in defense of the current behavior. -- 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] psql \dv+ shows view size as 0 bytes
On 4 June 2013 16:54, Peter Eisentraut pete...@gmx.net wrote: \dv+ shows the size of views as 0 bytes. This doesn't make any sense; I think it should show null. Maybe this is even the fault of the backend functions for returning a number to display in the first place. It doesn't sound useful whether it's 0 or NULL in that output. Why have the column in the first place when it can't have a value? Is it somehow required for inclusion in the output of \d+ ? -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add support for TAS/S_UNLOCK for aarch64
On Tue, Jun 4, 2013 at 11:48 AM, Pavel Raiskup prais...@redhat.com wrote: On Tuesday, June 04, 2013 05:28:09 PM Pavel Raiskup wrote: Hi, I was asked [1] to add following patch downstream, could it be considered upstream also? Thanks, Pavel. [1] https://bugzilla.redhat.com/show_bug.cgi?id=970661 Oh, I see now it was already consulted here: http://www.postgresql.org/message-id/1368448758.23422.12.ca...@t520.redhat.com Sorry for noise, it can be discussed there. I think we should go ahead and commit this patch, or some variant of it. Having a buildfarm machine would be good... but I don't think that should be a prerequisite for this sort of support. We certainly have spinlock support for other platforms for which we don't have buildfarm machines. -- 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] Configurable location for extension .control files
Hello I am working with the NixOS Linux Distribution [nixos], which has a fairly radical approach to package management. If you aren't familiar with it, essentially all packages are installed in isolation - such that packages cannot interfere with each other. good. This is causing a bit of a problem with PostgreSQL extensions that are usually installed via CREATE EXTENSION. For extensions to be used, a .control file must be placed in SHAREDIR/extension, but this is not possible under NixOS as once PostgreSQL is installed that directory is essentially immutable. What about shared objects, .sql files and documentation an extension may have to install ? What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. There is another point into allowing installation in different path : make check and make installcheck targets... Would love to hear your thoughts. Once I get confirmation on the best approach to take, I can try my hand at providing a patch. No idea on the best approach yet. But I am interested in this topic (for debian packaging). PS: I have a serie of bugfix and patches pending in the current commitfest (http://commitfest.postgresql.org) to help build with VPATH. You may be interested in them... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Configurable location for extension .control files
Oliver Charles ol...@ocharles.org.uk writes: I am working with the NixOS Linux Distribution [nixos], which has a fairly radical approach to package management. If you aren't familiar with it, essentially all packages are installed in isolation - such that packages cannot interfere with each other. Maybe you need to rethink that concept. Surely there are many other cases where package A extends package B and needs to be installed somewhere where B is expecting to look for extensions. This is causing a bit of a problem with PostgreSQL extensions that are usually installed via CREATE EXTENSION. For extensions to be used, a .control file must be placed in SHAREDIR/extension, but this is not possible under NixOS as once PostgreSQL is installed that directory is essentially immutable. What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. Basically, none of those are likely to get accepted because of security concerns. We *don't* want this path to be run-time adjustable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add support for TAS/S_UNLOCK for aarch64
Robert Haas robertmh...@gmail.com writes: On Tue, Jun 4, 2013 at 11:48 AM, Pavel Raiskup prais...@redhat.com wrote: Oh, I see now it was already consulted here: http://www.postgresql.org/message-id/1368448758.23422.12.ca...@t520.redhat.com I think we should go ahead and commit this patch, or some variant of it. Having a buildfarm machine would be good... but I don't think that should be a prerequisite for this sort of support. We certainly have spinlock support for other platforms for which we don't have buildfarm machines. We got no response to the question of whether it couldn't be merged with the existing ARM32 code block. I'd prefer not to have essentially duplicate sections in s_lock.h if it's not necessary. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add support for TAS/S_UNLOCK for aarch64
On Tue, 2013-06-04 at 13:40 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jun 4, 2013 at 11:48 AM, Pavel Raiskup prais...@redhat.com wrote: Oh, I see now it was already consulted here: http://www.postgresql.org/message-id/1368448758.23422.12.ca...@t520.redhat.com I think we should go ahead and commit this patch, or some variant of it. Having a buildfarm machine would be good... but I don't think that should be a prerequisite for this sort of support. We certainly have spinlock support for other platforms for which we don't have buildfarm machines. We got no response to the question of whether it couldn't be merged with the existing ARM32 code block. I'd prefer not to have essentially duplicate sections in s_lock.h if it's not necessary. Of course it could be. I don't think it should be. Aarch64 is a completely new architecture with faint resemblance to 32bit arm. I went back and read the previous thread concerning the problems with builtin gcc atomics with certain tool/hardware combinations. Would it be okay to add a default implementation based on gcc builtins to be used if no arch-specific definitions exist? If so, I could work up a patch for review. --Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Improved error message for CREATE EXTENSION patch...
I ran in to the following situation: SET search_path = ENOENT, also_does_not_exist; CREATE EXTENSION pg_repack; ERROR: XX000: there is no default creation target LOCATION: CreateExtension, extension.c:1395 Which left me checking out the source code to figure out exactly what the problem was. Attached is an improved error message: ERROR: XX000: no schemas in search_path are available for CREATE EXTENSION -sc src-backend-commands-extension.c.patch Description: Binary data -- Sean Chittenden s...@chittenden.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
On 06/04/2013 06:25 PM, Tom Lane wrote: What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. Basically, none of those are likely to get accepted because of security concerns. We *don't* want this path to be run-time adjustable. It turns out that the current way things are done is almost enough. Here's how it works at the moment: We have a 'postgresql' expression which builds PostgreSQL from a tarball - configure/make/make install to some directory. Next, extensions are built into their own (entirely separate) directories. Finally, there is a bit of glue at the end that builds a 'postgresql-and-plugins' directory, which is a tree consisting entirely of symlinks to the 'postgresql' directory and all the various extensions. Thus the final tree looks as would be expected if this were on a mutable file system. I'm currently having a bit of a problem in that Pg still seems to look in the 'postgresql' directory, not the '-and-plugins' one. I'm not sure what's going on here, because if I understand the source code correctly, it should be finding a common root based on #defines at install time, and then finding this relative to the currently executing path. Could it be that the symlink to the postgres binary is being expanded before this path construction takes place? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
On 06/04/2013 10:25 AM, Tom Lane wrote: What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. Basically, none of those are likely to get accepted because of security concerns. We *don't* want this path to be run-time adjustable. Really? I don't see a security concern in having a postgresql.conf option which requires a full restart. If the user can edit postgresql.conf and do a cold restart, presumably they can do anything they want anyway. If SET PERSISTENT gets into 9.4, then we might need to restrict it from setting certain settings, like this one. But until that feature is real, I don't see the potential expliot here. -- 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] [PATCH] Add support for TAS/S_UNLOCK for aarch64
Mark Salter msal...@redhat.com writes: On Tue, 2013-06-04 at 13:40 -0400, Tom Lane wrote: We got no response to the question of whether it couldn't be merged with the existing ARM32 code block. I'd prefer not to have essentially duplicate sections in s_lock.h if it's not necessary. Of course it could be. I don't think it should be. Aarch64 is a completely new architecture with faint resemblance to 32bit arm. OK, fair enough. I went back and read the previous thread concerning the problems with builtin gcc atomics with certain tool/hardware combinations. Would it be okay to add a default implementation based on gcc builtins to be used if no arch-specific definitions exist? If so, I could work up a patch for review. We had pretty much rejected that concept in the previous discussion, I thought. In the first place, there is no good reason to assume that somebody on a nonstandard platform is using gcc, and in the second, our review of the available info suggested that the implementation quality of gcc's builtins is ... um ... variable. Blindly falling back to that on a platform we haven't tested does not seem very safe. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFC: ExecNodeExtender
I'd like to propose a feature that allows extensions to replace a part of plan-tree underlying PlannedStmt by self-defined exec node being associated with several callback functions implemented at extension module. Right now, about 30 built-in exec nodes are implemented, and all the query execution plan is a combination of them, but no way to extend it on the fly. This API enhancement will make sense to implement cutting edge features, but not upstreamed yet, without undesirable source branch. The API I'd like to propose is as follows: typedef struct { const char *extender_name; ExecNodeExtender_BeginExec_function fn_begin_exec; ExecNodeExtender_GetNext_function fn_getnext; ExecNodeExtender_ReScan_function fn_rescan; ExecNodeExtender_EndExec_function fn_end_exec; ExecNodeExtender_Explain_function fn_explain; } ENExecRoutine; Index RegisterExecNodeExtender(const ENExecRoutine *routine); It registers a set of callbacks being identified with a unique name, then returns a unique index to the internal array to save multiple self-define exec node. Once self-define exec node is registered, extension module can replace a part of plan-tree underlying PlannedStmt by ENExec node with the index value being returned on its registration time. Probably, planner_hook is the best position for extensions to modify PlannedStmt being constructed by standard planner. typedef struct { Planplan; Index enexec_id; List *private_list; } ENExec; Please note that it does not intend to inject self-defined node on planner stage (on initial implementation at least) to avoid unnecessary patch complexity. Then, ENExec node shall be transformed to ENExecState at ExecInitNode() time. typedef strcut { PlanState ps; ENExecRoutine *routine; void *private; } ENExecState; The overall process is much similar to APIs of FDWs. fn_begin_exec() shall be invoked on ExecInitNode() to initialize relevant ENExecState, then fn_end_exec() shall be also invoked on ExecEndNode(). One thing different from FDW is, it does not assume self-defined exec node is always table scan, thus, fn_getnext() has equivalent job with ExecForeignScan(), not IterateForeignScan. Extension module can choose whether it uses ExecScan() to implement an alternative scan, or something others like sort or aggregate. I'm not certain whether PlannedStmt in text format by nodeToString() needs to be valid beyond server restart, or not. One thing we have to pay attention is, server restart with module unload may make orphan PlannedStmt that contains self-defined node being not valid, if it has to be valid beyond server restarting. Please correct me. Also, I don't think ExecNodeExtender is not a good naming, because it is a bit long and abbreviation (ENE?) is hard to imagine the feature. Please give this feature a cool and well understandable name. I plan to submit a first working patch with PoC contrib module towards commit-fest:2nd, because we have less than 10 days now, and we already have enough queued patches including row-level security... Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
Josh Berkus j...@agliodbs.com writes: On 06/04/2013 10:25 AM, Tom Lane wrote: Basically, none of those are likely to get accepted because of security concerns. We *don't* want this path to be run-time adjustable. Really? I don't see a security concern in having a postgresql.conf option which requires a full restart. If the user can edit postgresql.conf and do a cold restart, presumably they can do anything they want anyway. Yeah, if the config option were to be superuser-only, the security issue would be ameliorated --- not removed entirely, IMO, but at least weakened. However, this seems to me to be missing the point, which is that the extensions feature is designed to let the DBA have control over which extensions are potentially installable. If we allow extension control files to be loaded from any random directory then we lose that. Part of the argument for not requiring superuser permissions to execute CREATE EXTENSION was based on that restriction, so we'd need to go back and rethink the permissions needed for CREATE EXTENSION. 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] Configurable location for extension .control files
On 2013-06-04 13:25:10 -0400, Tom Lane wrote: Oliver Charles ol...@ocharles.org.uk writes: I am working with the NixOS Linux Distribution [nixos], which has a fairly radical approach to package management. If you aren't familiar with it, essentially all packages are installed in isolation - such that packages cannot interfere with each other. Maybe you need to rethink that concept. Surely there are many other cases where package A extends package B and needs to be installed somewhere where B is expecting to look for extensions. Yea, I have my doubt about that concept as well. This is causing a bit of a problem with PostgreSQL extensions that are usually installed via CREATE EXTENSION. For extensions to be used, a .control file must be placed in SHAREDIR/extension, but this is not possible under NixOS as once PostgreSQL is installed that directory is essentially immutable. What wolud work best for us is to allow this path to be configurable, ideally through either an environment variable, command line switch, or (and this is the least desirable) a postgresql.conf option. Basically, none of those are likely to get accepted because of security concerns. We *don't* want this path to be run-time adjustable. But I have to say, I think this argument isn't all that convincing either. Without superuser rights loading a control file shouldn't give you any more permissions than plainly executing the sql script yourself. Everything else would be a bug we need to fix. With superuser rights there is nothing stopping the user to LOAD the library directly or create a C function that has an arbitrary library path hardcoded directly. With the latter you can trivially enough replace pg internal functions that are normally executed so you effectively have something which will get executed on every connection. Placing restrictions on what can be done in postgresql.conf, which already has access to shared_preload_libraries, doesn't provide additional protection as well since we don't require specific locations for files in s_p_l anyway. The only argument with a good bit of merit I can see is that it could lead to unexpected extensions being loaded if e.g. hstore isn't installed in the normal extension directory but another extension with the same name somewhere else. But that seems like a problem of the system administrator that configured the additional directories. 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] [PATCH] Add support for TAS/S_UNLOCK for aarch64
I wrote: Mark Salter msal...@redhat.com writes: On Tue, 2013-06-04 at 13:40 -0400, Tom Lane wrote: We got no response to the question of whether it couldn't be merged with the existing ARM32 code block. I'd prefer not to have essentially duplicate sections in s_lock.h if it's not necessary. Of course it could be. I don't think it should be. Aarch64 is a completely new architecture with faint resemblance to 32bit arm. OK, fair enough. Applied in HEAD and 9.2. If there's demand, we could patch further back, but we don't have aarch64 in config.guess/config.sub before 9.2, so we'd have to change that too. Given that aarch64 is barely past the vaporware stage, I'm not sure there's demand to install back-rev Postgres on 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] Configurable location for extension .control files
Andres Freund and...@2ndquadrant.com writes: The only argument with a good bit of merit I can see is that it could lead to unexpected extensions being loaded if e.g. hstore isn't installed in the normal extension directory but another extension with the same name somewhere else. And just think about the fun you could have with inconsistent files named hstore--1.0--1.1.sql in different directories. The extension feature is really really not designed to be able to search a path of directories. It presumably wouldn't be terribly hard for Oliver to patch the sources to look in something other than SHAREDIR/extension/, but I'm not sure I see the point of inventing a platform-specific name for that directory; seems like it would mostly just confuse users coming from other platforms. Instead, what about not treating that directory as part of the base package in the first place? If you've got the concept of directories that multiple packages can install into, just make this be one of those. 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] Improved error message for CREATE EXTENSION patch...
Sean Chittenden s...@chittenden.org writes: I ran in to the following situation: SET search_path = ENOENT, also_does_not_exist; CREATE EXTENSION pg_repack; ERROR: XX000: there is no default creation target LOCATION: CreateExtension, extension.c:1395 Which left me checking out the source code to figure out exactly what the problem was. Attached is an improved error message: ERROR: XX000: no schemas in search_path are available for CREATE EXTENSION Hm, I'm not sure that's much better than the existing wording. The bigger point here though is that if we consider this to be a user-facing error case, it ought to be ereport not elog. I checked what you get for the normal non-extension case: regression=# set search_path = bogus; SET regression=# create table t1 (f1 int); ERROR: no schema has been selected to create in regression=# \set VERBOSITY verbose regression=# create table t1 (f1 int); ERROR: 3F000: no schema has been selected to create in LOCATION: RangeVarGetCreationNamespace, namespace.c:485 Seems like we ought to use the same message (and SQLSTATE) as in namespace.c, since nobody's complained about that one. 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] Which table stored in which file in PGDATA/base/[db-oid]
- On Sat, Jun 1, 2013 at 3:57 PM, Andres Freund and...@2ndquadrant.comwrote: - - On 2013-06-01 13:04:55 +0200, Martijn van Oosterhout wrote: - To get the actual relfilenode you actually need to do something like: - SELECT relname, pg_relation_filenode(pg_class.oid) FROM pg_class; - - Dear Andres - - You are right, Some tables are mapped, and some other are global. - The SQL query is really helpful. Another option, is to use oid2name http://www.postgresql.org/docs/devel/static/oid2name.html oid2name -S -d database -f filename -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
On 2013-06-04 16:07:23 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: The only argument with a good bit of merit I can see is that it could lead to unexpected extensions being loaded if e.g. hstore isn't installed in the normal extension directory but another extension with the same name somewhere else. And just think about the fun you could have with inconsistent files named hstore--1.0--1.1.sql in different directories. The extension feature is really really not designed to be able to search a path of directories. Well, some definitional work would be needed. That specific problem seems sensibl solveable by defineing those to be relative to the control file. I don't really care much about Oliver's usecase TBH, but I would very much welcome making it easier for application developers to package part of ther in-database application code as extensions without either requiring a selfcompiled postgres with a custom extension dir or them having have root access to the machine running postgres. Providing them with access to a directory that's only configured as additional extension directory for a development cluster would be a huge improvement. 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] Improved error message for CREATE EXTENSION patch...
ERROR: XX000: no schemas in search_path are available for CREATE EXTENSION Hm, I'm not sure that's much better than the existing wording. The bigger point here though is that if we consider this to be a user-facing error case, it ought to be ereport not elog. I checked what you get for the normal non-extension case: regression=# set search_path = bogus; SET regression=# create table t1 (f1 int); ERROR: no schema has been selected to create in regression=# \set VERBOSITY verbose regression=# create table t1 (f1 int); ERROR: 3F000: no schema has been selected to create in LOCATION: RangeVarGetCreationNamespace, namespace.c:485 Seems like we ought to use the same message (and SQLSTATE) as in namespace.c, since nobody's complained about that one. Sounds good to me and is clear enough that it would unblock me w/o having to resort to the source tree. -sc -- Sean Chittenden s...@chittenden.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
Andres Freund and...@2ndquadrant.com writes: I don't really care much about Oliver's usecase TBH, but I would very much welcome making it easier for application developers to package part of ther in-database application code as extensions without either requiring a selfcompiled postgres with a custom extension dir or them having have root access to the machine running postgres. Well, if you're installing an extension that includes C code, you're going to need root access anyway to install the shlib (at least on conservatively-configured machines). For pure-SQL extensions, Dimitri's been pushing a different approach that needn't involve the filesystem at all. We didn't get that finished in 9.3 but I think it's still on the agenda for 9.4. 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] Configurable location for extension .control files
On 06/04/2013 09:07 PM, Tom Lane wrote: It presumably wouldn't be terribly hard for Oliver to patch the sources to look in something other than SHAREDIR/extension/, but I'm not sure I see the point of inventing a platform-specific name for that directory; seems like it would mostly just confuse users coming from other platforms. Instead, what about not treating that directory as part of the base package in the first place? If you've got the concept of directories that multiple packages can install into, just make this be one of those. I currently have postgresql patched to search getenv(PGSHAREDIR) before looking at SHAREDIR, which seems to work. As I said in a previous reply - by my understanding it should all just work without any patches, but for some reason the path coming out from get_share_dir is not relative to the executable symlink, but is relative to executable under that symlink. If that problem can be solved, then everything can just carry on working. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
On 2013-06-04 16:24:07 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: I don't really care much about Oliver's usecase TBH, but I would very much welcome making it easier for application developers to package part of ther in-database application code as extensions without either requiring a selfcompiled postgres with a custom extension dir or them having have root access to the machine running postgres. Well, if you're installing an extension that includes C code, you're going to need root access anyway to install the shlib (at least on conservatively-configured machines). For pure-SQL extensions, Dimitri's been pushing a different approach that needn't involve the filesystem at all. We didn't get that finished in 9.3 but I think it's still on the agenda for 9.4. Yea, I know of Dimitri's work ;). But I really would like this to work for C extensions as well. For me personally its no problem at all that this wouldn't work on conservatively configured machines. Heck, I *don't* want it to work on production machines. But being able to configure a dev machine to allow it would be very helpful. 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] pg_rewind, a tool for resynchronizing an old master after failover
On 05/28/2013 11:32 AM, Bruce Momjian wrote: I think Simon has a good point, as VMWare has asserted patents on some changes to their version of Postgres in the past, so if the copyright ... which I'll point out that they *didn't* contribute, and which may yet get resolved in a way that benefits the PostgreSQL community. mentions VMWare, we can't assume it is patent-free. Just the fact you had to check with the VMware legal department verifies there is cause for concern about things coming from VMWare. That seems rather like a catch-22 Bruce. If they don't check with the legal department, it's dangerous, but if they do check, it's dangerous? Presumably if they checked with the legal department, it's cleared. We should be wary of stuff contributed by company employees who *didn't* check. This particular tool seems highly unlikely to be legitimately patentable, anyway. There's too much prior art. -- 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] psql \dv+ shows view size as 0 bytes
On 6/4/13 12:08 PM, Thom Brown wrote: It doesn't sound useful whether it's 0 or NULL in that output. Why have the column in the first place when it can't have a value? Is it somehow required for inclusion in the output of \d+ ? \dv is just a special case of \dvti... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewind, a tool for resynchronizing an old master after failover
On 06/04/2013 01:55 PM, Josh Berkus wrote: That seems rather like a catch-22 Bruce. If they don't check with the legal department, it's dangerous, but if they do check, it's dangerous? Presumably if they checked with the legal department, it's cleared. We should be wary of stuff contributed by company employees who *didn't* check. This particular tool seems highly unlikely to be legitimately patentable, anyway. There's too much prior art. legitimately patentable is a rather ethereal phrase in our industry right now don't you think? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improved error message for CREATE EXTENSION patch...
Sean Chittenden s...@chittenden.org writes: Seems like we ought to use the same message (and SQLSTATE) as in namespace.c, since nobody's complained about that one. Sounds good to me and is clear enough that it would unblock me w/o having to resort to the source tree. -sc OK, done that way. 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] how to find out whether a view is updatable
On Wed, Jun 5, 2013 at 12:59 AM, Peter Eisentraut pete...@gmx.net wrote: I was looking for a way in which the average psql user could learn whether a view is updatable. I was expecting something in \d, \d+, \dv, \dv+, or a NOTICE from CREATE VIEW. So far, the only way appears to be through the information schema or the underlying pg_view_is_updatable function. Not even pg_views shows anything. Is this intentional or an oversight? Just by recalling the thread, an oversight. Having this information in ¥dv+ would be indeed a nice addition. -- Michael
Re: [HACKERS] create a git symbolic-ref for REL9_3_STABLE
- Original Message - From: Peter Eisentraut pete...@gmx.net I have never actually used symbolic-ref, but after playing with it a little bit, it seems it should work fine for this purpose. Comments? +1 its should work fine, because any how we just going to add symbolic-ref which contain ref: refs/heads/master. Just want to ask, what exactly you want, is like this 1. you want to create symbolic -ref as _git symbolic-ref refs/heads/REL9_3_STABLE refs/heads/master_ 2. which will show in _git branch_ as REL9_3_STABLE - master * master 3. Then you want to checkout to REL9_3_STABLE , and pull the all changes pointing to master? 4. while actual happened then want to create branch REL9_3_STABLE? Is this way? Regards Amul Sul -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Configurable location for extension .control files
On 5 June 2013 05:58, Andres Freund and...@2ndquadrant.com wrote: Yea, I know of Dimitri's work ;). But I really would like this to work for C extensions as well. For me personally its no problem at all that this wouldn't work on conservatively configured machines. Heck, I *don't* want it to work on production machines. But being able to configure a dev machine to allow it would be very helpful. Just the other day I was looking at what it would take to drop some extra compiled extensions somewhere that the Mac Postgres.app could find them, and was mildly disappointed to see that it would have to be inside the app bundle itself, so they would then disappear on upgrade etc. The more maccy way to install extensions for a user app generally is to stick them under ~/Library/appname. Note that Postgres.app is very much an application developer workstation targeted distribution, so the security issues don't really come into the picture. It would also be enough in this case to allow multiple paths to be compiled in rather than pulled from postgresql.conf, but either would work. Cheers Tom
Re: [HACKERS] pg_rewind, a tool for resynchronizing an old master after failover
On Tue, Jun 4, 2013 at 01:55:27PM -0700, Josh Berkus wrote: On 05/28/2013 11:32 AM, Bruce Momjian wrote: I think Simon has a good point, as VMWare has asserted patents on some changes to their version of Postgres in the past, so if the copyright ... which I'll point out that they *didn't* contribute, and which may yet get resolved in a way that benefits the PostgreSQL community. Right. Simon was just verifying that we were good with this new feature, as it had a VMWare copyright and was on github --- totally legitimate question. mentions VMWare, we can't assume it is patent-free. Just the fact you had to check with the VMware legal department verifies there is cause for concern about things coming from VMWare. That seems rather like a catch-22 Bruce. If they don't check with the legal department, it's dangerous, but if they do check, it's dangerous? Presumably if they checked with the legal department, it's cleared. We should be wary of stuff contributed by company employees who *didn't* check. My point is that there was no mention of a legal check in the original posting, which prompted Simon to ask: http://www.postgresql.org/message-id/519df910.4020...@vmware.com -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make targets of doc links used by phpPgAdmin static
On Tue, 2013-05-07 at 23:18 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: On Tue, 2013-05-07 at 00:32 -0500, Karl O. Pinc wrote: Attached is a documentation patch against head which makes static the targets of the on-line PG html documentation that are referenced by the phpPgAdmin help system.e done I wonder about backpatching this to 9.2 ? done -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make targets of doc links used by phpPgAdmin static
On 06/04/2013 10:16:20 PM, Peter Eisentraut wrote: On Tue, 2013-05-07 at 23:18 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: On Tue, 2013-05-07 at 00:32 -0500, Karl O. Pinc wrote: Attached is a documentation patch against head which makes static the targets of the on-line PG html documentation that are referenced by the phpPgAdmin help system.e done I wonder about backpatching this to 9.2 ? done Will this be in the next point release? Or just when will it go live? This is not a huge problem but it does break some existing links into the 9.2 PG docs. Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers