Re: [HACKERS] Horizontal scalability/sharding
On Mon, 2015-08-31 at 22:21 +, Robert Haas wrote: > It seems to me that sharding consists of (1) breaking your data set up > into shards, (2) possibly replicating some of those shards onto > multiple machines, and then (3) being able to access the remote data > from local queries. [...] I believe there is another aspect to sharding that I have not yet seen mentioned, which is one of connection routing. One use case that I have been involved in, is to simply partition the application into entirely, or almost entirely, separate datasets running on separate databases with little or no need for queries to access remote data. This allows each database to deal only with connections from clients that actually want its local data, greatly reducing the number of connections on any individual database. If this works for your application, your ability to scale is great. The pain point comes from trying to route queries to the correct database. Inevitably, everyone taking this route builds custom connection-selection layers into their apps. It seems to me that even with the more sophisticated types of sharding being discussed here, the ability to conditionally route a query/connection to a suitable starting database could be quite beneficial. Although this is probably a job for the pgbouncer/pgpool developers rather than the hackers on this list, this thread seems to be a good place to mention it. __ Marc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #11867: Strange behaviour with composite types after resetting database tablespace
On Mon, 2014-11-03 at 22:08 -0500, Tom Lane wrote: spockFascinating./spock :-) I believe what is happening is: [ . . . ] This is not mission-critical for me but I'd be grateful for suggestions for work-arounds. I don't see any workaround that's much easier than fixing the bug. But what's your use case that involves flipping databases from one tablespace to another and then back again within the life expectancy of unused shared-buffers pages? It doesn't seem terribly surprising that nobody noticed this before ... It's a completely unreal use case. I am working on a diff tool, and this was found as part of my test suite: build db x, drop db x, build db y, apply diff y-x, compare with original x, apply diff x-y, compare with original y. So this is a fairly minor inconvenience for me. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [v9.4] row level security
On Wed, 2013-09-04 at 14:35 +, Robert Haas wrote: On Fri, Aug 30, 2013 at 3:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think it's entirely sensible to question whether we should reject (not hold up) RLS if it has major covert-channel problems. We've already had this argument before, about the security_barrier [ . . . ] Sorry for following up on this so late, I have just been trying to catch up with the mailing lists. I am the developer of Veil, which this thread mentioned a number of times. I wanted to state/confirm a number of things: Veil is not up to date wrt Postgres versions. I didn't release a new version for 9.2, and when no-one complained I figured no-one other than me was using it. I'll happily update it if anyone wants it. Veil makes no attempt to avoid covert channels. It can't. Veil is a low-level toolset designed for optimising queries about privileges. It allows you to build RLS with reasonable performance, but it is not in itself a solution for RLS. I wish the Postgres RLS project well and look forward to its release in Postgres 9.4. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] leakproof
On Wed, 2012-02-22 at 12:44 -0400, Andrew Dunstan wrote: Returning to the original point, I've come to the conclusion that pure isn't the right way to go. The trouble with leakproof is that it doesn't point to what it is that's not leaking, which is information rather than memory, as many might imagine (and I did) without further hints. I'm not sure any single English word would be as descriptive as I'd like. As the developer of veil I feel marginally qualified to bikeshed here: how about silent? A silent function being one that will not blab. There are also quite a few synonyms in the thesaurus for trustworthy. I kind of like honorable or righteous myself. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Schema grants for creating and dropping objects
On Sun, 2011-10-09 at 11:58 -0400, Tom Lane wrote: Marc Munro m...@bloodnok.com writes: It seems that in order to create an object in a given schema, I must have been granted create privilege on the schema. But in order to drop that object I require usage privilege. This means that with the right privilege settings I can create objects that I cannot subsequently drop, or can drop an object that I cannot recreate. Yeah. So? You can get similar effects with read-only or write-only directories in Unix filesystems. Don't see why you find this surprising. It's just that ordinarily as the owner of an object, I can do what I like with it. In this case, I can't. In fact, once I've created the table I can't access it. I guess the surprising thing to me is that I'm allowed to create it without usage privilege. However, it is what it is, and as it is intended behaviour I will happily work with it. Perhaps some extra notes in the documentation of the sql-grants section might be useful. As a side note: creating a file in a write-only directory on Unix doesn't work for me. regards, tom lane Thanks for the response. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Schema grants for creating and dropping objects
It seems that in order to create an object in a given schema, I must have been granted create privilege on the schema. But in order to drop that object I require usage privilege. This means that with the right privilege settings I can create objects that I cannot subsequently drop, or can drop an object that I cannot recreate. I assume this is a bug but if it's intended behaviour I'd love to hear the rationale. I checked this on 8.3, 8.4, 9.0 and 9.1 all with the same results. Best regards. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] Dropping extensions
On Sat, 2011-07-23 at 11:08 -0400, Tom Lane wrote: If I drop the extension veil_demo, I am left with the veil_demo version of veil_init(). Is this a feature or a bug? Is there a work-around? Hmm. I don't think we have any code in there to prohibit the same object from being made a member of two different extensions ... but this example suggests that maybe we had better check that. In general, though, it is not intended that extension creation scripts use CREATE OR REPLACE, which I gather you must be doing. That's right. Ultimately I'd like to be able to create a number of extensions, all further extending the base functionality of veil, with each one further extending veil_init(). I could consider a more generalised callback mechanism but that adds more complexity and overhead without really buying me anything functionally. I will look into it though. While it would be great to be able to return functions to their previous definition automatically, other simpler mechanisms might suffice. For me, being able to run a post-drop script would probably be adequate. For now, I will just add some notes to the documentation. Thanks for the response. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] Date conversion using day of week
On Thu, 2011-03-31 at 08:00 -0700, Adrian Klaver wrote:
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
On 31 March 2011 03:15, Steve Crawford scrawf...@pinpointresearch.com
wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
[. . .]
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
[. . .]
Just to play Devils advocate here, but why not? The day name is the same
either
way, it is the index that changes. I am not sure why that could not be
context
specific?
Just to be clear, the reason I was mixing things in this way was that I
wanted to validate that the dayname being passed was valid for the
current locale, and I could find no easier way of doing it. FTR, I have
now resorted to finding the given dayname in the results of this query:
select day, to_char(day, 'dy') as dayname,
extract('dow' from day) as dayno
from (
select current_date + n as day
from generate_series(0, 6) as n) d;
If there is an easier way of doing this, please let me know. As far as
the postgres API goes, exposing a function that would validate a dayname
returning a day number would resolve all of this for considerably less
complexity. Also throwing an error in the to_date function for
unexpectedly mixed input formats seems quite reasonable.
Thanks for your time and attention. The commercial RDBMS vendors could
learn a lot about customer support from this forum.
__
Marc Munro
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS
On Fri, 2010-06-04 at 10:33 -0400, Tom Lane wrote: Hmm ... that's a mighty interesting example, because it shows that any well-meaning change in error handling might render seemingly-unrelated functions unsafe. And we're certainly not going to make error messages stop showing relevant information just because of this. Although that looks like a show-stopper, I think it can be worked around. Errors in operations on security views could simply be caught and conditionally rewritten. The original error could still appear in the logs but the full details need not be reported to unprivileged users. If that can be done, then we would still need to be able to identify trusted functions and views used for security purposes, and ensure that (please excuse my terminology if it is incorrect) untrusted quals do not get pushed down inside secured views. If all of that can be done along with the error trapping, then we may have a solution. My big concern is still about performance, particularly when joining between multiple security views and other objects. I don't expect to get security for free but I don't want to see unnecessary barriers to optimisation. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [pgsql-hackers] Daily digest v1.10705 (13 messages)
On Thu, 2010-06-03 at 05:53 -0300, pgsql-hackers-ow...@postgresql.org wrote: [ . . . ] In my current idea, when a qual-node that contains FuncExpr tries to reference a part of relations within a security view, its referencing relations will be expanded to whole of the security view at distribute_qual_to_rels(). [ . . . ] I may be missing something here but this seems a bit too simplistic and, I think, fails to deal with an important use case. Security views, that do anything useful at all, tend to introduce performance issues. I am concerned that placing a conceptual barrier between the secured and unsecured parts of queries is going to unnecessarily restrict what the optimiser can do. For example consider that we have three secured views, each of the form: create view s_x as select * from x where i_can_see(x.key); and consider the query: select stuff from s_x inner join s_y on s_y.key = s_x.key inner join s_z on s_z.key = s_x.key where fn(s_x.a) = 3; The optimiser ought to be able to spot the fact that i_can_see() need only be called once for each joined result. By placing a barrier (if I understand your proposal correctly) between the outermost joins and the inner views, doesn't this optimisation become impossible? I think a simpler solution may be possible here. If you can tag the function i_can_see() as a security function, at least in the context of its use in the security views, and then create the rule that security functions are always considered to be lower cost than user-defined non-security functions, don't we achieve the result of preventing the insecure function from seeing rows that it shouldn't? I guess my concern is that a query may be constructed a=out of secured and unsecured parts and the optimiser should be free to group all of the secured parts together before considering the unsecured parts. Sorry for the imprecise language and terminolgy, and also if I have completely misunderstood the implications. Best Wishes __ Marc (the veil guy) signature.asc Description: This is a digitally signed message part
[HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Mon, 2010-03-01 at 16:12 -0400, pgsql-hackers-ow...@postgresql.org wrote: . . . However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. To resolve this I think you would have to introduce some chance for the slave to catch up. Something like refusing to use a snapshot older than max_standby_age/2 and instead wait until the existing queries finish and the slave gets a chance to catch up and see a more recent snapshot. The problem is that this would result in very unpredictable and variable response times from the slave. A single long-lived query could cause replay to pause for a big chunk of max_standby_age and prevent any new query from starting. Does anyone see any way to guarantee that the slave gets a chance to replay and new snapshots will become visible without freezing out new queries for extended periods of time? At the risk of looking foolish, I have a hand-wavy, unlikely-to-be-possible, not-going-to-make-it-for-9.0, and maybe unoriginal idea that I'll share. As Greg has identified, no matter what max_standby_age you select, a sequence of overlapping queries will eventually exceed the max_standby_delay threshold, and tuples that your query depends on would then be modified underneath you. IIUC this is only a problem for WAL from HOT updates and vacuums. If no vacuums or HOT updates have been performed, there is no risk of returning bad data. So WAL that does not contain HOT updates or vacuums could be applied on the standby without risk, even if there are long-running queries in play. This is not a complete solution but may reduce the likelihood of queries having to be cancelled. I guess the approach here would be to check WAL before applying it, and only cancel queries if the WAL contains HOT updates or vacuums. Taking the idea further, if WAL records contained the tid of the latest tuples that were overwritten, even more WAL could be applied without having to cancel queries. To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Apologies if this has already been covered, some of this discussion went over my head. /action Puts on asbestos underwear __ Marc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Just to intoduce myself, I'm Marc Munro the developer of Veil, a postgres add-in that allows you to implement virtual private databases using views. The problem we are discussing here is the existence of covert or side-channels being available from functions that can leak information about the rows they see, even though the end-user may not see those rows. These can be built-ins such as set_config() (thanks, Heikki) or user-defined. I assert that any attempt to use a secured-view's where-clause conditions before any other conditions are applied will lead to poor performance. Here is a typical veil secured view definition: create view parties as SELECT party_id, client_id, party_type_id, username, party_name FROM parties.parties WHERE api.user_has_client_or_personal_privilege(client_id, party_id, 'select parties') OR api.user_has_client_privilege(party_id, 'select clients'); A typical query against this would be: select * from parties where party_id = 42; The conditions in the view's where clause cannot generally be indexed. Applying those conditions before the user-supplied conditions would mean that a full-table scan would be required and performance would suck. In fact, this very suckiness also exposes a covert channel in that now we can use the performance of the query to estimate the number of party records. The most acceptable solution I have heard so far for this issue, is to identify those functions which can leak information as 'insecure', and those views which are for security purpose as 'secured'. Then it is simply (hah!) a matter of planning the query of secured views so that all insecure functions are called after all secure functions. In this way, they will only be able to leak what the user is entitled to see, and performance will only be as badly affected as is strictly necessary. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] RFE: Transparent encryption on all fields
On Thu, 2009-04-23 at 16:08 -0300, pgsql-hackers-ow...@postgresql.org wrote: On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote: [...] It's possible that this could be accomplished by something like Veil, Veil? Care to share an URL? http://veil.projects.postgresql.org/curdocs/index.html Veil is intended to enable implementation of virtual private databases. It provides a bunch of primitives for managing bitmaps of privileges. These privileges can be used to control access to individual rows within a table. In principle it could be used in the way that Bill Moran suggests though I have never used it that way. I am somewhat suspicious of passing encryption keys to the database server as there is always the potential for them to be leaked. It is generally much safer to keep keys and the decryption process on a separate server. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [BUGS] Status of issue 4593
On Mon, 2009-01-12 at 14:35 -0400, Jeff Davis wrote: ate: Mon, 12 Jan 2009 09:52:00 -0800 On Mon, 2009-01-12 at 08:32 -0500, Tom Lane wrote: That code has been working like this for eight or ten years now and this is the first complaint, so taking away functionality on the grounds that someone might happen to update the ordering column doesn't seem like the answer to me. If they are using FOR UPDATE, they clearly expect concurrent updates. If they're using ORDER BY, they clearly expect the results to be in order. So who is the target user of this functionality we're trying to protect? If by the question above you are asking for a rational use-case, I think I can give you one. In my Oracle days I used to use select for update order by, in order to reduce the likelihood of deadlocks. If locks are always taken in the same order, deadlocks become considerably less likely. Unfortunately, I took this construct, which as far as I know works fine in Oracle, and continued to use it in Postgres just assuming that it would work. At least now the source of some of my more mysterious deadlocks is apparent :-) I'd second the request for at least a warning to be issued. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Patch to eliminate duplicate b64 code from pgcrypto
Oops, forgot to cc my reply to hackers: On Thu, 2008-12-18 at 01:49 +0200, Marko Kreen wrote: On 12/16/08, Bruce Momjian br...@momjian.us wrote: Would someone who understand pgcrypto please review this? Marc Munro wrote: I am attaching a patch to eliminate duplicate b64_encode and decode functions from pgcrypto, and to expose those functions for use by add-ins (I want to use them in Veil). Although your patch achieves the goal, it may make more sense to export pg_find_encoding() and struct pg_encoding, thus making all encoding algorithms available externally. I had a very specific use-case in mind which was to eliminate the need for Veil to re-implement b64_encode and b64_decode, so I took the path of least effort. The patch was made against CVS head today. It compiles and tests successfully. Though I was unable to run pgrypto regression tests, I did give the b64 encoding a sanity check. I also added a b64_char() function that is now used from pgcrypto. This allowed me to remove the duplicate _base64 character array. I think this can be avoided by using plain b64_encode() for those 3 bytes. That step is really not speed critical. You are probably right: I was being somewhat timid and tried to make the smallest set of changes that were possible. Unfortunately I don't have the time right now to revisit the patch. If you or anyone else would like to re-implement it as you suggest I will still be a happy camper. If not, Veil has already re-implemented the functions, for compatibility with current and older versions of postgres, so I can live with that too. Thanks very much for looking at it. To be honest, I thought that it had got lost in the transition to the new improved patch process so I was expecting to have to resubmit it later when I have more time. Kudos to Bruce for tracking it down. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Re: Do we really want to migrate plproxy and citext into PG core distribution?
On Mon, 2008-07-21 at 17:03 -0300, Tom Lane wrote: [. . .] I think it would be a good idea to be open to reviewing pgfoundry code with the same standards we'd use if we were going to integrate it. Perhaps commitfest is not the right venue for that, though, if only because of the possibility of confusion over what's supposed to happen. I think this would be a great idea. I would be overjoyed to have veil http://pgfoundry.org/projects/veil/ reviewed by postgres developers. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Patch to eliminate duplicate b64 code from pgcrypto
I am attaching a patch to eliminate duplicate b64_encode and decode
functions from pgcrypto, and to expose those functions for use by
add-ins (I want to use them in Veil).
The patch was made against CVS head today. It compiles and tests
successfully. Though I was unable to run pgrypto regression tests, I
did give the b64 encoding a sanity check.
I also added a b64_char() function that is now used from pgcrypto. This
allowed me to remove the duplicate _base64 character array.
I hope this is the correct place to submit the patch. The wiki
(http://wiki.postgresql.org/wiki/Submitting_a_Patch) refers to
pgsql-patches but I believe that is now deprecated.
__
Marc
Index: contrib/pgcrypto/pgp-armor.c
===
RCS file: /projects/cvsroot/pgsql/contrib/pgcrypto/pgp-armor.c,v
retrieving revision 1.3
diff -c -r1.3 pgp-armor.c
*** contrib/pgcrypto/pgp-armor.c 15 Oct 2005 02:49:06 - 1.3
--- contrib/pgcrypto/pgp-armor.c 17 Jul 2008 22:37:27 -
***
*** 30,181
*/
#include postgres.h
#include px.h
#include mbuf.h
#include pgp.h
/*
- * BASE64 - duplicated :(
- */
-
- static const unsigned char _base64[] =
- ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/;
-
- static int
- b64_encode(const uint8 *src, unsigned len, uint8 *dst)
- {
- uint8 *p,
- *lend = dst + 76;
- const uint8 *s,
- *end = src + len;
- int pos = 2;
- unsigned long buf = 0;
-
- s = src;
- p = dst;
-
- while (s end)
- {
- buf |= *s (pos 3);
- pos--;
- s++;
-
- /*
- * write it out
- */
- if (pos 0)
- {
- *p++ = _base64[(buf 18) 0x3f];
- *p++ = _base64[(buf 12) 0x3f];
- *p++ = _base64[(buf 6) 0x3f];
- *p++ = _base64[buf 0x3f];
-
- pos = 2;
- buf = 0;
- }
- if (p = lend)
- {
- *p++ = '\n';
- lend = p + 76;
- }
- }
- if (pos != 2)
- {
- *p++ = _base64[(buf 18) 0x3f];
- *p++ = _base64[(buf 12) 0x3f];
- *p++ = (pos == 0) ? _base64[(buf 6) 0x3f] : '=';
- *p++ = '=';
- }
-
- return p - dst;
- }
-
- /* probably should use lookup table */
- static int
- b64_decode(const uint8 *src, unsigned len, uint8 *dst)
- {
- const uint8 *srcend = src + len,
- *s = src;
- uint8 *p = dst;
- char c;
- unsigned b = 0;
- unsigned long buf = 0;
- int pos = 0,
- end = 0;
-
- while (s srcend)
- {
- c = *s++;
- if (c = 'A' c = 'Z')
- b = c - 'A';
- else if (c = 'a' c = 'z')
- b = c - 'a' + 26;
- else if (c = '0' c = '9')
- b = c - '0' + 52;
- else if (c == '+')
- b = 62;
- else if (c == '/')
- b = 63;
- else if (c == '=')
- {
- /*
- * end sequence
- */
- if (!end)
- {
- if (pos == 2)
- end = 1;
- else if (pos == 3)
- end = 2;
- else
- return PXE_PGP_CORRUPT_ARMOR;
- }
- b = 0;
- }
- else if (c == ' ' || c == '\t' || c == '\n' || c == '\r')
- continue;
- else
- return PXE_PGP_CORRUPT_ARMOR;
-
- /*
- * add it to buffer
- */
- buf = (buf 6) + b;
- pos++;
- if (pos == 4)
- {
- *p++ = (buf 16) 255;
- if (end == 0 || end 1)
- *p++ = (buf 8) 255;
- if (end == 0 || end 2)
- *p++ = buf 255;
- buf = 0;
- pos = 0;
- }
- }
-
- if (pos != 0)
- return PXE_PGP_CORRUPT_ARMOR;
- return p - dst;
- }
-
- static unsigned
- b64_enc_len(unsigned srclen)
- {
- /*
- * 3 bytes will be converted to 4, linefeed after 76 chars
- */
- return (srclen + 2) * 4 / 3 + srclen / (76 * 3 / 4);
- }
-
- static unsigned
- b64_dec_len(unsigned srclen)
- {
- return (srclen * 3) 2;
- }
-
- /*
* PGP armor
*/
--- 30,42
*/
#include postgres.h
+ #include utils/builtins.h
#include px.h
#include mbuf.h
#include pgp.h
/*
* PGP armor
*/
***
*** 215,234
memcpy(pos, armor_header, n);
pos += n;
! n = b64_encode(src, len, pos);
pos += n;
if (*(pos - 1) != '\n')
*pos++ = '\n';
*pos++ = '=';
! pos[3] = _base64[crc 0x3f];
crc = 6;
! pos[2] = _base64[crc 0x3f];
crc = 6;
! pos[1] = _base64[crc 0x3f];
crc = 6;
! pos[0] = _base64[crc 0x3f];
pos += 4;
n = strlen(armor_footer);
--- 76,95
memcpy(pos, armor_header, n);
pos += n;
! n = b64_encode((char *) src, len, (char *) pos);
pos += n;
if (*(pos - 1) != '\n')
*pos++ = '\n';
*pos++ = '=';
! pos[3] = b64_char(crc);
crc = 6;
! pos[2] = b64_char(crc);
crc = 6;
! pos[1] = b64_char(crc);
crc = 6;
! pos[0] = b64_char(crc);
pos += 4;
n = strlen(armor_footer);
***
*** 356,367
goto out;
/* decode crc */
! if (b64_decode(p + 1, 4, buf) != 3)
goto out;
crc = (((long) buf[0]) 16) + (((long) buf[1]) 8) + (long) buf[2];
/* decode data */
! res = b64_decode(base64_start, base64_end - base64_start, dst);
/* check crc */
if (res = 0 crc24(dst, res) != crc)
--- 217,229
[HACKERS] b64_encode and decode
I require base64 or some similar encoding scheme from a C language extension and need it to be as fast as reasonably possible. In src/backend/utils/adt/encode.c there are functions b64_encode and b64_decode which would be ideal but these are defined static and so are not available to my code. I know I could call these functions indirectly by calling binary_ecncode through DirectFunctionCalln() but this is a whole lot more complexity and overhead than I'd like. I note that /contrib/pgcrypto/pgp-armor.c appears to have its own copies of these 2 functions and now I have elected to do the same. So, would there be any chance of redefining the base64 functions in encode.c as extern to eliminate this redundancy? __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] b64_encode and decode
On Thu, 2008-06-12 at 19:07 -0400, Andrew Dunstan wrote: Marc Munro wrote: I require base64 or some similar encoding scheme from a C language. . . I know I could call these functions indirectly by calling binary_ecncode through DirectFunctionCalln() but this is a whole lot more complexity and overhead than I'd like. . . Just how much complexity do you think calling binary_encode involves? You can probably do the whole thing in one or two lines of code. I'm sure that's true once I've got my head around the mechanism, but it adds two levels of indirection that seem quite unnecessary, and given that the author of pgcrypto has also wound up copying the functions I guess I'm not the only one who'd rather avoid it. If there are good reasons not to expose the functions, or if the hackers just don't want to do it I'm fine with that. For dealing with 8.3 and earlier I will have to live with the redundancy. For 8.4 I'd like not to, but it's really not a big deal. Thanks for the response though. Being able to get a response from developers means a lot. I used to be an Oracle DBA and I have to say the response I get from this group is light years ahead of what I used to have to pay for. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] b64_encode and decode
On Thu, 2008-06-12 at 19:10 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: So, would there be any chance of redefining the base64 functions in encode.c as extern to eliminate this redundancy? It'd only last until the next time Bruce runs his script that static-izes things that aren't used outside their own module ... Hmmm. Does that script look in contrib? If so I'd be happy to provide a patch to eliminate the redundancy there. If not, maybe it could be persuaded to be more inclusive? __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
In a digest for Tue, 2008-05-06 at 22:57 -0300, Tom Lane wrote: ...[discussion of SE-PostgreSQL patch deleted]... (And of course the next question after that is why we should want to depend on SELinux at all, rather than implementing row filtering in the framework of SQL permissions...) I would love to see something like this to replace Veil which I develop and support. What sort of row filtering did you have in mind? As a database application developer I have found being able to define access controls in terms of data relationships to be tremendously useful and I would love to see something like this built into postgres. As far as I have been able to understand SE-PostgreSQL, it is aimed at a very security-conscious, and expert, customer base but it cannot offer the sort of relationally-defined security access that Veil is intended for (I'd be happy to be wrong about this). On the other hand Veil is not going to be able to provide the degree of certainty (provability?) of SE-PostgreSQL. As an example of relationally-defined security, suppose I want only the members of a project team to be able to see project information: In the veil demo application ( http://veil.projects.postgresql.org/curdocs/demo-model.html ) we can assign a developer to a project by inserting into the assignments table a record for that developer, the given project and a specific role. Once assigned, the developer can see project_details for that project that previously were unavailable. If row filtering is to be implemented directly within Postgres, I would like this sort of capability to be considered. For the record, Veil was written to provide similar functionality to Oracle's Virtual Private Databases. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] function body actors (was: [PERFORM] viewing source code)
On Fri, 2007-21-12 at 18:05 -0400, Andrew Sullivan [EMAIL PROTECTED] wrote: 2. Protect the content of a field from _some_ users on a given system, I would argue that (2) is reasonably well served today by setting up separate databases for separate users. I thought actually this was one of the use-cases we were hearing. Different people using the same database (because the same data), with rules about the different staff being able to see this or that function body. I can easily imagine such a case, for instance, in a large organization with different departments and different responsibilities. It seems a shame that the only answer we have there is, Give them different databases. There is also Veil: http://veil.projects.postgresql.org/curdocs/index.html __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Feature freeze progress report
On Wed, 2007-02-05 at 08:27 -0400, Andrew Dunstan wrote: Naz Gassiep wrote: Andrew Dunstan wrote: Naz Gassiep wrote: I believe the suggestion was to have an automated process that only ran on known, sane patches. How do we know in advance of reviewing them that they are sane? Same way as happens now. The question was rhetorical ... there is no list of certified sane but unapplied patches. You are proceeding on the basis of a faulty understanding of how our processes work. Why do we need to know the patch is sane? If it does not apply cleanly or causes regression tests to fail, the process would figure that out quickly and cheaply. There is little cost in attempting to apply a non-sane patch. I am not sure that I have explained exactly what I was suggesting. Some people seem to grok it, others seem to be talking something slightly different. To clarify, here it is in pseudo-code: for each patch in the queue regression_success := false patch_success := attempt to apply patch to head if patch_success regression_success := attempt to run regression tests -- (On one machine only, not on the buildfarm) end if if this is a new patch maybe mail the author and tell them patch_success and regression_success else if status is different from last time mail the author and tell them their patch has changed status end end record the status for this patch end loop __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Feature freeze progress report
On Tue, 2007-01-05 at 02:54 +0100, Gregory Stark wrote: Naz Gassiep [EMAIL PROTECTED] writes: Even if the patch inventory wasn't kept right up to date, this system could potentially help many regression issues or bugs to surface sooner, I just don't understand what this would accomplish. People run regressions before submitting anyways. They can't run them on all architectures but bugs that only affect some architectures are uncommon. The intention is to help keep patches, which may remain in the queue for extended lengths of time, reasonably current. The mechanism aims to help with these specific problems: - patches accumulates bitrot and are consequently harder to apply and understand - the author, by the time review occurs, no longer has the details of the patch uppermost in their mind If the author can be automatically prodded when the patch no longer cleanly applies, or when it suddenly breaks regression tests, they will be able to keep the patch current, may discover bugs in it themselves prior to review, and it will remain more fresh in their minds. For sure, there will be classes of patch for which this mechanism provides no benefit. For instance, where a patch contains code that is for discussion only, or a patch that is dependant on another patch. In these cases, the mechanism would simply note that they don't apply cleanly, or don't pass tests, and would do nothing further. I can see no harm here. This seems to be merely institutionalizing having a large backlog of patches which survive for long periods of time. But even in that situation I don't see what it buys us. Detecting bitrot isn't terribly helpful and it doesn't help us actually deal with the bitrot once it's happened. I hope that it would not encourage reviewers to leave things in the patch queue. I don't see why it would, so don't think this would institutionalize a backlog. I also disagree that detecting bitrot is not helpful. If I had eagerly submitted a patch, I would definitely want to fix any bitrot that occurred and would be thankful to be automatically informed. And to clarify, I do not think the buildfarm should be involved in this process. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Feature freeze progress report
On Mon, 2007-30-04 at 08:56 -0300, Heikki Linnakangaspgsql wrote: Date: Mon, 30 Apr 2007 09:18:36 +0100 From: Heikki Linnakangas [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Dave Page [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED], PostgreSQL-development pgsql-hackers@postgresql.org Subject: Re: Feature freeze progress report Message-ID: [EMAIL PROTECTED] If we had a 1-2 lines status blurp attached to each patch in the queue, like waiting for review, author is fixing issue XX, etc., that might help. Bruce would need to do that if we keep the current patch queue system unmodified otherwise, or we'd need to switch to something else. Would it be possible to also automatically determine some sort of bit-rot status? What I had in mind was an automated process that would apply each patch to HEAD on a daily basis and report whether the patch still applies cleanly and still allows all regression tests to pass on at least one platform. If and when the result of these tests changes from pass to fail, the patch submitter would be automatically notified. The patch status could then also show the last time at which the patch applied cleanly, and the last time that regression tests ran successfully. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Mon, 2007-12-02 at 00:10 -0500, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: Consider a table C containing 2 child records C1 and C2, of parent P. If transaction T1 updates C1 and C2, the locking order of the the records will be C1, P, C2. Another transaction, T2, that attempts to update only C2, will lock the records in order C2, P. The locks on C2 and P are taken in different orders by the two transactions, leading to the possibility of deadlock. But the lock on P is shared, hence no deadlock. Doh! Yes, you are right. It is not that simple. For deadlock to occur, we need a transaction that takes an exclusive lock on P as well as on one of the children. Let us replace T2 with a new transaction, T3, which is going to update P and only one of its children. If T3 is going to update P and C1 without the possibility of deadlock against T1, then it must take out the locks in the order C1, P. If, on the other hand, it is going to update P and C2, then the locks must be taken in the order P, C2. This means that there is no single strategy we can apply to T3 that will guarantee to avoid deadlocks with transactions that update only C (ie transactions, which to a developers point of view do nothing to P, and so should be unable to deadlock with T3). From an application developer's standpoint there are few options, none of them ideal: 1) Insist on a locking policy that requires updates to first lock their parent records. This is horrible for so many reasons. It should be unnecessary; it causes exclusive locking on parent records, thereby eliminating the gains made by introducing row share locks in 8.1; it is onerous on the developers; it is error-prone; etc 2) Remove FK constraints to eliminate the possibility of RI-triggered deadlocks. Ugh. 3) Encapsulate all transactions in some form of retry mechanism that traps deadlocks and retries those transactions. This may not be practicable, and incurs all of the overhead of encountering and trapping deadlocks in the first place. Also, as each deadlock occurs, a number of locks will be left active before deadlock detection kicks in, increasing the window for further deadlocks. On a busy system, the first deadlock may well trigger a cascade of further deadlocks. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Tue, 2007-13-02 at 11:38 -0500, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: From an application developer's standpoint there are few options, none of them ideal: How about 4) Make all the FK constraints deferred, so that they are only checked at end of transaction. Then the locking order of transactions that only modify C is always C1, C2, ..., P. Excellent suggestion. Thank you. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Sun, 2007-11-02 at 12:21 -0600, Jim C. Nasby wrote: On Thu, Feb 08, 2007 at 08:47:42AM -0800, Marc Munro wrote: One of the causes of deadlocks in Postgres is that its referential integrity triggers can take locks in inconsistent orders. Generally a child record will be locked before its parent, but not in all cases. Where would PostgreSQL lock the parent before the child? AFAIK the behavior should be consistent... Consider a table C containing 2 child records C1 and C2, of parent P. If transaction T1 updates C1 and C2, the locking order of the the records will be C1, P, C2. Another transaction, T2, that attempts to update only C2, will lock the records in order C2, P. The locks on C2 and P are taken in different orders by the two transactions, leading to the possibility of deadlock. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
Oops, forgot to include pgsql-hackers when I responded to this the first time. On Tue, 2007-06-02 at 20:53 -0500, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: The RI triggers currently fire when a record is updated. Under my proposal they would fire in the same way but before the record is locked rather than after. Or am I missing your point? IOW, some other transaction could update or delete the tuple meanwhile? Doesn't seem very promising. That other transaction, T1, would have run the same RI triggers and so would have the same parent records locked. The blocked transaction, T2, once T1 has committed, would fail. I don't see this as being much different from the current case, where T1 locks and deletes or updates a row, and T2 then tries to manipulate the same row. In both cases, locks manage the race for the row, and MVCC ensures that T2 fails. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
I am going to restate my earlier proposal, to clarify it and in the hope of stimulating more discussion. One of the causes of deadlocks in Postgres is that its referential integrity triggers can take locks in inconsistent orders. Generally a child record will be locked before its parent, but not in all cases. My proposal modifies the order in which locks are taken by referential integrity triggers, so that parents are always locked before their children. The proposal is, that referential integrity triggers should fire before locking the tuple from which they are triggered. I guess a new sort of trigger is required for this, a before-lock trigger. If this is a dumb idea, please tell me why. If it would cause more problems than it solves, ditto. If it would be difficult to implement, let's discuss and try to find solutions. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Thu, 2007-08-02 at 18:06 +0100, Csaba Nagy wrote: The problem is that eliminating the deadlock is still not the complete cake... the interlocking still remains, possibly leading to degraded performance on high contention on very common parent rows. The real solution would be when an update on the parent table's non-referenced fields is not interlocking at all with updates of the child rows... and I think there were some proposals to do that. Agreed. There are two issues here, unnecessary blocking and deadlock. These can be tackled separately. My proposal deals only with the deadlock issue. Even if if contention is reduced, for instance by implementing column-level locking, there will still be the potential for deadlock arising from inconsistent ordering of locks. I continue to stand by my proposal. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
On Thu, 2007-08-02 at 10:06 -0800, Stephan Szabo wrote: On Thu, 8 Feb 2007, Marc Munro wrote: . . . That other transaction, T1, would have run the same RI triggers and so would have the same parent records locked. That's not true in the case of delete, since the referencing table triggers are on insert and update. . . . Let me see if I have this scenario right: Transaction T1 updates child record C1, with RI causing the parent P1 to be locked before the child. In the meantime transaction T2, successfully deletes C1 as it has not yet been locked. (Please tell me if I have misunderstood what you are saying) Yes in this case, T1 must abort because the record it was going to update has disappeared from underneath it. I don't see how this is significantly different from the same race for the record if the table had no RI constraints. The only difference that I can see, is that T1 now has some locks that it must relinquish as the transaction aborts. . . . Second, the parent record locks are not exclusive which means that both can be granted, so I don't see how this stops the second from continuing before the first. I don't think this does stop the second from continuing before the first. What will stop it, is the eventual lock that is taken on the child (triggering) record. I am not proposing reducing the number of locks taken, but rather changing the order in which the locks are taken. concerned frown What am I missing? /concerned frown __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: Yes in this case, T1 must abort because the record it was going to update has disappeared from underneath it. I don't see how this is significantly different from the same race for the record if the table had no RI constraints. The only difference that I can see, is that T1 now has some locks that it must relinquish as the transaction aborts. No, the difference is there would have been no error at all before; if the record were deleted before T1 got to it then it wouldn't have attempted to update it. I really don't think you can make it work to perform updates or deletes on a record you have not yet locked. The record would be locked before the update or delete is attempted, however it would not be locked until the referential integrity constraints have succeeded in acquiring their locks. It is becoming clear to me that I am missing something but I still don't know what it is. If anyone can see it and explain it I'd really appreciate it. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
On Thu, 2007-08-02 at 12:24 -0800, Stephan Szabo wrote: On Thu, 8 Feb 2007, Marc Munro wrote: I don't think this does stop the second from continuing before the first. What will stop it, is the eventual lock that is taken on the child (triggering) record. But at that point, you've already had to compose the new row in order to call the trigger for the ri check, right? So, one of those new rows will be out of date by the time it actually gets the lock. Presumably that means that you need to recalculate the new row, but you've already done a check and gotten a lock based on the old new row. Yes. That is tricky. For my proposed scheme to work, I guess we'd have to be able to drop those locks which were just acquired by the RI triggers. Not too simple, I guess. Also, another big problem is the fact that SQL requires that the action already have happened before the check in cases where the constraint references the same table. The row being updated or inserted might reference a row that will be updated or inserted by a later action of the same statement. Hmmm. That does seem to be the final nail in the coffin. Consider the proposal withdrawn, and thanks for explaining it all to me. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
Simon Riggs started this thread with the question: . . . Why do we need a SHARE lock at all, on the **referenc(ed)** table? . . . The root problem addressed by this thread seems to be that using share locks in this way increases the likelihood of deadlock, and causes blocking when no blocking is actually needed. I would like to make a few observations leading to two alternative proposals for dealing with this issue. Deadlocks arise because of differences in the order in which locks are taken. If we have a parent table P, and a child C, and we modify two children of the same P, locks will be taken in the order C1, P, C2. Another process modifying only C2, will cause locks to be taken in the order C2, P, leading to the possibility of deadlock. With the current system of RI, this sort of deadlock arises far too easily with the result that RI is often disabled. It is solely the order in which the locks are taken that causes the problem. If the RI constraints could lock the parent records before locking the child, the possibility of deadlock would be much reduced. Proposal 1: Alter the way RI triggers fire, so that they complete before locking the row against which they fire. Having a background in Oracle, I found myself considering how this is not usually a problem with Oracle databases. If I understand it correctly, in Oracle the referential integrity constraints are implemented by locking the index associated with the constraint, rather than the records themselves. Proposal 2: Lock the index associated with the parent record, rather than the parent record itself. Updates to indexed fields, and deletions of records would need to also take such locks, but this should be enough to allow non-referenced fields to be updated in a parent, even while transactions are modifying its children. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] referential Integrity and SHARE locks
On Tue, 2007-06-02 at 23:47 +, Gregory Stark wrote: Marc Munro [EMAIL PROTECTED] writes: Proposal 1: Alter the way RI triggers fire, so that they complete before locking the row against which they fire. It's kind of hard to know what records the user will choose to update before he actually does the update... The RI triggers currently fire when a record is updated. Under my proposal they would fire in the same way but before the record is locked rather than after. Or am I missing your point? Proposal 2: Lock the index associated with the parent record, rather than the parent record itself. That doesn't help in our case because each version of a record has an index entry. So even updates to unrelated fields imply index modifications. Worse, deleting and updating don't remove the old index entries so even if you've locked them you won't prevent people from doing exactly those operations you're trying to avoid. I guess my proposal was incomplete. Obviously, before deleting, or updating an indexed column, a lock would have to be taken on the index. I believe this would suffice to guarantee referential integrity without blocking updates that leave the referred indexes unchanged. What you say about each version of a record having an index entry confuses me. I thought there was one index entry that lead to a chain of tuples. If this is not the case, I don't see how the current exclusive locks on indexes work to enforce uniqueness. Could you point me to somewhere in the code or the documentation that explains this? It still seems to me that if we can lock an index entry to guarantee uniqueness, we can also lock it to implement RI constraints. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] New shared memory hooks proposal (was Re:
On Sat, 2006-10-14 at 14:55 -0400, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
The attached patch provides add-ins with the means to register for
shared memory and LWLocks.
I finally got around to reviewing this patch, and realized that it's got
a pretty fundamental design flaw: it isn't useful under Windows (or any
other EXEC_BACKEND platform), because there isn't any provision for
backends to locate structs allocated by other backends by means of
searching in shared memory. AFAICS the code can only do something
useful in a platform where allocations made in the postmaster process
can be found by backends via fork inheritance of pointers.
Rats! You are right. I had quite overlooked the windows case.
The right way to handle shmem allocations is to use ShmemInitStruct
to either allocate a shared struct for the first time or attach to a
previously made instance of the struct. (This struct could be a
memory allocation arena itself, but that's not the core code's problem.)
Now we could extend the patch so that each addin has its own
ShmemIndex within its private workspace, but I think that's probably
overkill. My inclination is to rip out ShmemAllocFromContext and expect
addins to use ShmemInitStruct the same as everyone else. The hook
callable at shared_preload_libraries time should just serve to add
the necessary amount to the computed size of the shared memory segment.
I think that works for me.
RegisterAddinLWLock is broken in the same way: it could only be used
safely if the registered lock ID were remembered in shared memory,
but since shared memory doesn't exist at the time it's supposed to be
called, there's no way to do that. Again, it'd seem to work as long as
the lock ID value were inherited via fork, but that's gonna fail on
EXEC_BACKEND builds. I think we should probably take this out in favor
of something that just increments a counter that replaces
NUM_USER_DEFINED_LWLOCKS, and expect people to use LWLockAssign() at an
appropriate time while initializing their shared memory areas.
Agreed.
It strikes me that there's a race condition here, which we've not seen
in previous use because backends expect all standard shared memory
structs to have already been initialized by the postmaster. An add-on
will instead have to operate under the regime of first backend wanting
to use the struct must init it. Although ShmemInitStruct returns a
found bool telling you you've got to init it, there's no interlock
ensuring that you can do so before someone else comes along and tries to
use the struct (which he'll assume is done because he sees found = true).
And, per above discussion, an add-on can't solve this for itself using
an add-on LWLock, because it really has to acquire its add-on locks
while initializing that same shmem struct, which is where it's going to
keep the locks' identity :-(
So I think we need to provide a standard LWLock reserved for the purpose
of synchronizing first-time use of a shmem struct. The coding rules for
an add-on would then look like:
* in the shared_preload_libraries hook:
RequestAddinShmemSpace(size);
RequestAddinLWLocks(n);
* in a backend, to access a shared memory struct:
static mystruct *ptr = NULL;
if (!ptr)
{
boolfound;
LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
ptr = ShmemInitStruct(my struct name, size, found);
if (!ptr)
elog(ERROR, out of shared memory);
if (!found)
{
initialize contents of shmem area;
acquire any requested LWLocks using:
ptr-mylockid = LWLockAssign();
}
LWLockRelease(AddinShmemInitLock);
}
Thoughts?
I am content that what you suggest is the right way to go. I will work
on a new patch immediately, unless you would prefer to do this yourself.
It seems to me that these coding rules should be documented in the main
documentation, I guess in the section that describes Dynamic Loading.
Would you like me to take a stab at that?
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] [pgsql-hackers] Daily digest v1.6352 (22 messages)
On Mon, 2006-10-02 at 12:02 -0300, Shaunak Godbole wrote: Hi, We are trying to introduce access control. For this we have to rewrite the input query by replacing each relation by its corresponding authorized view. I assume from this that you are trying to implement something like Oracle's Virtual Private Database. If you need access controls at the row or column level, and do not want to implement this whole thing yourself, please take a look at Veil (http://veil.projects.postgresql.org/) which can already accomplish the same sort of thing, though in a different way. If Veil is insufficient for your needs in some way, I'd be interested to hear about it. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Making config file parser available to add-ins
I want Veil (http://pgfoundry.org/projects/veil/) to be able to read configuration details from a configuration file. Rather than implement my own config file parser, I'd like to be able to re-use the parser defined in guc-file.l If this is not contentious, I will submit a patch to make the parser available to add-ins. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Making config file parser available to add-ins
On Mon, 2006-07-24 at 20:31 +0200, Peter Eisentraut wrote: Marc Munro wrote: I want Veil (http://pgfoundry.org/projects/veil/) to be able to read configuration details from a configuration file. What kind of details? By the time any server-side module is loaded, the configuration file has already been read, so why would you need to read it again? I want to read a veil config file rather than the postgres config file. Basically, I just want access to the rather nice config file parser that already exists. As for the kind of details: - how much shared memory to allocate for veil - the size of the hash tables for veil's shared variables __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Making config file parser available to add-ins
On Mon, 2006-07-24 at 14:44 -0400, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Marc Munro wrote: I want Veil (http://pgfoundry.org/projects/veil/) to be able to read configuration details from a configuration file. What kind of details? By the time any server-side module is loaded, the configuration file has already been read, so why would you need to read it again? Probably the correct question is whether Marc's problem isn't already solved by the custom GUC variable mechanism --- that is, whatever he wants to configure should be defined as custom GUCs within the existing configuration file. I don't think it's already solved but I may be missing the point. The Veil shared library will be loaded by process_preload_libraries only after the postgresql config file has been read. I was assuming that at this point it would be too late to specify custom GUCs. Instead I want to load my own veil.conf file which would support the same sort of syntax as postgresql.conf. My proposal is to simply expose a new function processAddinConfigFile() which would mimic ProcessConfigFile but would call a user-supplied function to deal with each entry. Obviously, this would be usable by future add-ins and not just Veil. If there is a better way to do this please tell me. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Making config file parser available to add-ins
On Mon, 2006-07-24 at 15:17 -0400, Tom Lane wrote: No, being able to do that is exactly the point of the custom-GUC mechanism. Excellent. I shall study this and hope to bother you no further :-) Thanks. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
For the record, here are the results of our (ongoing) inevstigation into the index/heap corruption problems I reported a couple of weeks ago. We were able to trigger the problem with kernels 2.6.16, 2.6.17 and 2.6.18.rc1, with 2.6.16 seeming to be the most flaky. By replacing the NFS-mounted netapp with a fibre-channel SAN, we have eliminated the problem on all kernels. From this, it would seem to be an NFS bug introduced post 2.6.14, though we cannot rule out a postgres bug exposed by unusual timing issues. Our starting systems are: Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875 Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux (and others) kernel boot option: elevator=deadline 16 Gigs of RAM postgresql-8.0.8-1PGDG Bonded e1000/tg3 NICs with 8192 MTU. Slony 1.1.5 NetApp FAS270 OnTap 7.0.3 Mounted with the NFS options rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac Jumbo frames 8192 MTU. All postgres data and logs are stored on the netapp. All tests results were reproduced with postgres 8.0.8 __ Marc On Fri, 2006-06-30 at 23:20 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: We tried all of these suggestions and still get the problem. Nothing interesting in the log file so I guess the Asserts did not fire. Not surprising, it was a long shot that any of those things were really broken. But worth testing. We are going to try experimenting with different kernels now. Unless anyone has any other suggestions. Right at the moment I have no better ideas :-( regards, tom lane signature.asc Description: This is a digitally signed message part
[HACKERS] New shared memory hooks proposal (was Re: pre_load_libraries)
On Thu, 2006-07-13 at 01:13 -0300, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: ... A better solution from my point of view would be to simply move the call to process_preload_libraries to a point after shared memory has been set up. Is there some reason this could not be done? That would make it impossible for a preloaded library to request any shared memory of its own --- something that admittedly we don't have a hook to support, but do we want to foreclose it permanently? That does sound like the right way to go. Here is my new proposal: Add-ins register their requirement for shared memory using a new function: RegisterShmemRequirement(char *context_name, int size). This would be called by the init function called from process_preload_libraries. When shared memory is initialised, extra space is allocated for each registered add-in. Each add-in's registered allocation is a separate memory context identified by the context_name parameter provided during registration. Add-ins allocate shared memory from their own context using a new function ShemAddinAlloc(), which adds the context_name parameter to the normal ShemAlloc parameter list. This would save add-ins from having to manage their own shared memory segements while providing a degree of separation and isolation so that one add-in could not exhaust the shared memory of another or of the backend. If this is acceptable, I think it is within my skill level to implement. Comments? __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] pre_load_libraries
On Wed, 2006-07-12 at 02:18 -0300, I wrote: I am trying to create an initialisation function that is called using the preload_libraries option. The purpose of this is to set up shared memory for Veil, independant of postgres' own shared memory. Simple init functions work fine, but as soon as I place calls to ShemAlloc, or LWLockAssign, the server startup simply halts. To answer my own question, yes I am being unreasonable. Shared memory has not been set up at the time that process_preload_libraries is called. Since lwlocks are allocated from postgres shared memory, I cannot assign an lwlock from Veil's initialisation code. Instead, I can make the allocation of the lwlock the responsibility of the first session that uses a Veil function but I need a lock to prevent a race. My current thinking is to simply subvert one of the existing lwlocks while I assign my own. A better solution from my point of view would be to simply move the call to process_preload_libraries to a point after shared memory has been set up. Is there some reason this could not be done? On a related note, I can see no way to release Veil's shared memory segment when postgres is shut down. Perhaps I should be thinking about making the management of such shared memory segments something that postgres does on behalf of its add-ins, though that seems presumptious. I'd appreciate any suggestions, pointers, random thoughts, etc. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] pre_load_libraries
I am trying to create an initialisation function that is called using the preload_libraries option. The purpose of this is to set up shared memory for Veil, independant of postgres' own shared memory. Simple init functions work fine, but as soon as I place calls to ShemAlloc, or LWLockAssign, the server startup simply halts. Am I being unreasonable in trying to call these functions at this point of the server startup, or is this just some stupid bug in my code? I wish to call ShmemAlloc in order to simply create a shared reference to the Veil shared memory segments that I will set up separately. Thanks __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Thu, 2006-06-29 at 21:47 -0400, Tom Lane wrote: One easy thing that would be worth trying is to build with --enable-cassert and see if any Asserts get provoked during the A couple other things to try, given that you can provoke the failure fairly easily: . . . 1. In studying the code, it bothers me a bit that P_NEW is the same as InvalidBlockNumber. The intended uses of P_NEW appear to be adequately . . . 2. I'm also eyeing this bit of code in hio.c: If someone else has just extended the relation, it's possible that this will allow a process to get to the page before the intended extender has . . . We tried all of these suggestions and still get the problem. Nothing interesting in the log file so I guess the Asserts did not fire. We are going to try experimenting with different kernels now. Unless anyone has any other suggestions. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Tom Lane's advice, we upgraded to Postgres 8.0.8. We also upgraded slony to 1.1.5, due to some rpm issues. Apart from that everything is as described below. We were able to corrupt the index within 90 minutes of starting up our cluster. A slony-induced vacuum was under way on the provider at the time the subscriber failed. What can we do to help identify the cause of this? We have a test system that seems able to reproduce this fairly easily. __ Marc On Wed, 2006-06-28 at 09:28 -0700, Marc Munro wrote: We have now experienced index corruption on two separate but identical slony clusters. In each case the slony subscriber failed after attempting to insert a duplicate record. In each case reindexing the sl_log_1 table on the provider fixed the problem. The latest occurrence was on our production cluster yesterday. This has only happened since we performed kernel upgrades and we are uncertain whether this represents a kernel bug, or a postgres bug exposed by different timings in the new kernel. Our systems are: Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875 Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux kernel boot option: elevator=deadline 16 Gigs of RAM postgresql-8.0.3-1PGDG Bonded e1000/tg3 NICs with 8192 MTU. Slony 1.1.0 NetApp FAS270 OnTap 7.0.3 Mounted with the NFS options rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac Jumbo frames 8192 MTU. All postgres data and logs are stored on the netapp. In the latest episode, the index corruption was coincident with a slony-induced vacuum. I don't know if this was the case with our test system failures. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Thu, 2006-06-29 at 12:11 -0400, Tom Lane wrote:
OK, so it's not an already-known problem.
We were able to corrupt the index within 90 minutes of starting up our
cluster. A slony-induced vacuum was under way on the provider at the
time the subscriber failed.
You still haven't given any details. What do you mean by corrupt the
index --- what actual symptoms are you seeing?
We have a two node slony cluster with load tests running againt the
provider node.
After resyncing the subscriber, and running load tests for about an
hour, the slony subscriber begins to fail. From the log file:
2006-06-28 17:58:43 PDT ERROR remoteWorkerThread_1: insert into
public.table_trans_attribute (table_transaction_id,attribute_type,value)
values ('374520943','22001','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22002','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22003','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22004','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22005','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22006','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22007','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520943','22007','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520942','22009','0');
insert into public.table_trans_attribute
(table_transaction_id,attribute_type,value) values ('374520942','22010','0');
ERROR: duplicate key violates unique constraint table_trans_attr_pk
As you see, slony is attempting to enter one tuple
('374520943','22007','0') two times.
Each previous time we have had this problem, rebuilding the indexes on
slony log table (sl_log_1) has fixed the problem. I have not reindexed
the table this time as I do not want to destroy any usable evidence.
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
We have reproduced the problem again. This time it looks like vacuum is
not part of the picture. From the provider's log:
2006-06-29 14:02:41 CST DEBUG2 cleanupThread: 101.057 seconds for vacuuming
And from the subscriber's:
2006-06-29 13:00:43 PDT ERROR remoteWorkerThread_1: insert into
public.table_trans_attribute (table_transaction_id,attribute_type,value)
values ('374740387','22008','4000');
If my maths is correct and the logs are honest, the vacuum would have
started at 14:01:00 CST (13:01:PDT), about 20 seconds after we first
encounter the problem. The clocks on the two machines, though in
different timezones, are currently synced.
Tom, I will create another tarball of the sl_log_1 table and indexes.
Should be quite a bit smaller than the previous one.
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Fri, 2006-06-30 at 00:37 +0300, Hannu Krosing wrote: Marc: do you have triggers on some replicated tables ? We have a non-slony trigger on only 2 tables, neither of them involved in this transaction. We certainly have no circular trigger structures. I remember having some corruption in a database with weird circular trigger structures, some of them being slony log triggers. The thing that seemed to mess up something inside there, was when change on parent rownt fired a trigger that changes child table rows and there rows fired another trigger that changed the same parent row again. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] [Re: Index corruption]
Ooops: forgot to cc this to the list. On Thu, 2006-06-29 at 19:27 -0400, Tom Lane wrote: Are you *certain* this slave isn't running 8.0.2 or older? If you can verify that, then I guess we need to look for another mechanism that could cause the same kind of thing. Certain. We built new rpms for 8.0.8 and installed them on both database servers (and I just checked them again to make sure). If there's anything we can do to help debug this we will. We can apply patches, different build options, etc. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] index corruption
On Thu, 2006-06-29 at 19:59 -0400, Tom Lane wrote: Ummm ... you did restart the server? select version(); would be the definitive test. Can't say I blame you for the skepticism but I have confirmed it again. test=# select version(); version - PostgreSQL 8.0.8 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3) (1 row) __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Thu, 2006-06-29 at 21:47 -0400, Tom Lane wrote:
One easy thing that would be worth trying is to build with
--enable-cassert and see if any Asserts get provoked during the
failure case. I don't have a lot of hope for that, but it's
something that would require only machine time not people time.
I'll try this tomorrow.
A couple other things to try, given that you can provoke the failure
fairly easily:
1. In studying the code, it bothers me a bit that P_NEW is the same as
InvalidBlockNumber. The intended uses of P_NEW appear to be adequately
interlocked, but it's fairly easy to see how something like this could
happen if there are any places where InvalidBlockNumber is
unintentionally passed to ReadBuffer --- that would look like a P_NEW
call and it *wouldn't* be interlocked. So it would be worth changing
P_NEW to (-2) (this should just take a change in bufmgr.h and
recompile) and adding an Assert(blockNum != InvalidBlockNumber)
at the head of ReadBufferInternal(). Then rebuild with asserts enabled
and see if the failure case provokes that assert.
I'll try this too.
2. I'm also eyeing this bit of code in hio.c:
/*
* If the FSM knows nothing of the rel, try the last page before
* we give up and extend. This avoids one-tuple-per-page syndrome
* during bootstrapping or in a recently-started system.
*/
if (targetBlock == InvalidBlockNumber)
{
BlockNumber nblocks = RelationGetNumberOfBlocks(relation);
if (nblocks 0)
targetBlock = nblocks - 1;
}
If someone else has just extended the relation, it's possible that this
will allow a process to get to the page before the intended extender has
finished initializing it. AFAICT that's not harmful because the page
will look like it has no free space ... but it seems a bit fragile.
If you dike out the above-mentioned code, can you still provoke the
failure?
By dike out, you mean remove? Please confirm and I'll try it.
A different line of attack is to see if you can make a self-contained
test case so other people can try to reproduce it. More eyeballs on the
problem are always better.
Can't really see this being possible. This is clearly a very unusual
problem and without similar hardware I doubt that anyone else will
trigger it. We ran this system happily for nearly a year on the
previous kernel without experiencing this problem (tcp lockups are a
different matter). Also the load is provided by a bunch of servers and
robots simulating rising and falling load.
Lastly, it might be interesting to look at the WAL logs for the period
leading up to a failure. This would give us an idea of what was
happening concurrently with the processes that seem directly involved.
Next time we reproduce it, I'll take a copy of the WAL files too.
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Index corruption
On Thu, 2006-06-29 at 21:59 -0400, Tom Lane wrote: [ back to the start of the thread... ] BTW, a couple of thoughts here: * If my theory about the low-level cause is correct, then reindexing sl_log_1 would make the duplicate key errors go away, but nonetheless you'd have lost data --- the overwritten rows would be gone. I suppose that this would result in the slave missing some rows that are present on the master. Have you tried comparing slave and master databases to see if you can find any discrepancies? Haven't done that yet - in test we tend to restart the old subscriber as the new provider and rebuild the cluster. I'll check the logs from our production failure to figure out what to compare and see what I can discover. * One way that the problem could happen would be if a race condition in the kernel allowed an lseek(fd, 0, SEEK_END) to return a value less than the true end-of-file (as determined by another process' write() extending the EOF just slightly earlier --- ie, lseek fails to note the effects of the just-completed write, and returns the prior EOF value). PG does have internal locking that should guarantee that the lseek is not done until after the write completes ... but could there be a bug in the kernel allowing stale data to be returned? The SMP hardware is relevant (maybe one processor sees different data than the other) and frankly I don't trust NFS very far at all for questions such as this. It'd be interesting to see if you can reproduce the problem in a database on local storage. Unfortunately we haven't got any local storage that can stand the sort of loads we are putting through. With slower storage the CPUs mostly sit idle and we are very unlikely to trigger a timing-based bug if that's what it is. I'll get back to you with kernel build information tomorrow. We'll also try to talk to some kernel hackers about this. Many thanks for your efforts so far. __ Marc signature.asc Description: This is a digitally signed message part
[HACKERS] Index corruption
We have now experienced index corruption on two separate but identical slony clusters. In each case the slony subscriber failed after attempting to insert a duplicate record. In each case reindexing the sl_log_1 table on the provider fixed the problem. The latest occurrence was on our production cluster yesterday. This has only happened since we performed kernel upgrades and we are uncertain whether this represents a kernel bug, or a postgres bug exposed by different timings in the new kernel. Our systems are: Sun v40z 4 x Dual Core AMD Opteron(tm) Processor 875 Kernel 2.6.16.14 #8 SMP x86_64 x86_64 x86_64 GNU/Linux kernel boot option: elevator=deadline 16 Gigs of RAM postgresql-8.0.3-1PGDG Bonded e1000/tg3 NICs with 8192 MTU. Slony 1.1.0 NetApp FAS270 OnTap 7.0.3 Mounted with the NFS options rw,nfsvers=3,hard,rsize=32768,wsize=32768,timeo=600,tcp,noac Jumbo frames 8192 MTU. All postgres data and logs are stored on the netapp. In the latest episode, the index corruption was coincident with a slony-induced vacuum. I don't know if this was the case with our test system failures. What can we do to help identify the cause of this? I believe we will be able to reproduce this on a test system if there is some useful investigation we can perform. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] New feature proposal
On Fri, 2006-05-19 at 12:35 -0700, Marc Munro wrote: On Fri, 2006-05-19 at 14:44 -0400, Tom Lane wrote: This could all be solved in a cleaner, more bulletproof way if you simply require such add-ins to be preloaded into the postmaster process using the existing preload_libraries hook. Then, such an add-in would allocate its own shmem segment independent of the main Postgres one. This totally eliminates worries about one chunk of code eating the other one's memory, which otherwise we'd have to have additional mechanism to deal with. This is an interesting idea that I had not previously considered. I will give it some thought. I have give this idea some further thought and I agree; Tom's solution is more bulletproof and is the right way to go. My original proposal is withdrawn. I am going to look into the best way to implement this but my gut feeling is that I would like the support infrastructure for this to be in Postgres rather than in Veil. By support infrastructure, I mean APIs to create and access new shared memory segments, and allocate chunks of memory from those shared segments. I think this code is better placed in Postgres rather than in specific add-ins because: it is functionality that could benefit many add-ins; it can make use of existing postgres code; and it can be easily tested in the regression suite using the buildfarm. I don't want to start working on this without knowing there is a chance of the patch being acceptable, so feedback is invited. Thanks. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] New feature proposal
On Fri, 2006-05-19 at 13:41 -0300, [EMAIL PROTECTED] wrote: On Thu, 2006-05-18 at 17:39 -0700, Marc Munro wrote: For Postgres 8.2 I would like Veil to be a better citizen and use only what shared memory has been reserved for postgres add-ins. How would Postgres ask the add-in how much memory it needs? How would the add-in know how much has been reserved for it? How would an add-in know whether it was the only add-in and whether it could take all of the allocation? Postgres would not ask any add-ins how much they need, it would simply allocate the extra amount defined in a GUC and not make that available through the normal shared memory allocation mechanism. The add-in would not know how much had been allocated to it, but could be told through it's own config file. I envisage something like: in postgresql.conf # add_in_shmem = 0# Amount of shared mem to set aside for add-ins # in KBytes add_in_shem = 64 in veil.conf veil_shmem = 32 # Amount of shared memory we can use from # the postgres add-ins shared memory pool I think this is better than add-ins simply stealing from, and contending for, postgres shared memory which is the only real alternative right now. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [pgsql-hackers] Daily digest v1.5943 (21 messages)
On Fri, 2006-05-19 at 13:41 -0300, [EMAIL PROTECTED] wrote: Marc Munro wrote: Veil http://pgfoundry.org/projects/veil is currently not a very good Postgres citizen. It steals what little shared memory it needs from postgres' shared memory using ShmemAlloc(). For Postgres 8.2 I would like Veil to be a better citizen and use only what shared memory has been reserved for postgres add-ins. Why should this be individually restricted? AFAICS Veil's functionality would be essential to access row level ACL controlled tables, so if it fails for low mem conditions it's much like a backend failure. The problem is that postgres currently has no way of knowing how much shared memory Veil is likely to require, and so will not allocate space for it. Consequently if Veil needs a lot of shared memory, it is unlikely to be able to get it. My intention is to allow add-ins to use only shared memory that has been allocated for their own use, to ensure that enough has been allocated, to isolate postgres shared memory from abuse by add-ins, and to avoid the need for add-ins to manage their own shared memory. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] New feature proposal
On Fri, 2006-05-19 at 10:05 -0700, Josh Berkus wrote: Marc, The add-in would not know how much had been allocated to it, but could be told through it's own config file. I envisage something like: in postgresql.conf # add_in_shmem = 0# Amount of shared mem to set aside for add-ins # in KBytes add_in_shem = 64 in veil.conf veil_shmem = 32 # Amount of shared memory we can use from # the postgres add-ins shared memory pool I think this is better than add-ins simply stealing from, and contending for, postgres shared memory which is the only real alternative right now. H ... what would happen if I did: add_in_shmem = 64 veil_shmem = 128 or even: add_in_shmem = 128 veil_shmem = 64 plperl_shmem = 64 pljava_shmem = 64 If that happens, one of the add-ins will be sadly disappointed when it tries to use its allocation. The same as would happen now, if Veil attempted to allocate too large a chunk of shared memory. My proposal makes it possible for properly configured add-ins to have a guaranteed amount of shared memory available. It allows add-ins to be well-behaved in their use of shared memory, and it prevents them from being able to exhaust postgres' own shared memory. It doesn't prevent add-ins from over-allocating from the add-in memory context, nor do I think it can or should do this. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] New feature proposal
On Fri, 2006-05-19 at 14:44 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: My proposal makes it possible for properly configured add-ins to have a guaranteed amount of shared memory available. This could all be solved in a cleaner, more bulletproof way if you simply require such add-ins to be preloaded into the postmaster process using the existing preload_libraries hook. Then, such an add-in would allocate its own shmem segment independent of the main Postgres one. This totally eliminates worries about one chunk of code eating the other one's memory, which otherwise we'd have to have additional mechanism to deal with. This is an interesting idea that I had not previously considered. I will give it some thought. I'm not convinced that we actually do need to prevent add-ins from eating each other's memory. Just as existing add-ins that use palloc are expected to use the appropriate memory context and behave themselves, I would expect the same to be true for add-ins that require shared memory. In a Unix environment, such a thing would Just Work because pointers to the new segment would be inherited through fork(). In the Windows port you'd need to do more pushups --- perhaps allocate a small amount of memory in the main Postgres shmem segment containing the ID of the other shmem segment, which a backend would use to reattach. For me, adding windows-specific code to Veil is highly unappealling - I have no easy way to build or test for windows, and no experience of doing so, so the more I can leverage the existing functionality, the better. I had hoped to simply piggyback on Postgres' existing memory management with a very small change to effectively add an add-in shared memory context. On the other hand, if this is the way we have to go, then perhaps it could be added to Postgres as part of its api, rather than having Veil, and perhaps other add-ins, implement it for themselves. Thoughts? __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] audit table containing Select statements submitted
You could do this using Veil, http://pgfoundry.org/projects/veil/, or something like it. A Veil access function, http://veil.projects.postgresql.org/curdocs/overview-page.html, could be used to record every row returned within a query to the user that requested it. Note that this operates at the level of fetches and not the resultset, meaning that queries like: select stuff from a where exists (select 1 from b where); would record a fetch against b. The basic trick is to replace table_that_you_want_audited with a view that does something like: select * from table_that_you_want_audited where audit_this_fetch(row_identifier); You will also need instead-of triggers for insert, update and delete of the view. __ Marc On Fri, 2006-05-12 at 14:19 -0300, [EMAIL PROTECTED] wrote: From: Hogan, James F. Jr. Sent: Thursday, May 04, 2006 12:46 PM To: 'pgsql-sql@postgresql.org'; 'pgsql-general@postgresql.org' Subject: audit table containing Select statements submitted No response from the pgsql-admin list so I though I would try cross posting here: pgsql-sql@postgresql.org pgsql-general@postgresql.org I just know I am not the first to try and do this Jim * Can anyone point me in a direction that may help me populate in real time a table that holds? Current_user Timestamp The Select Statement Submitted by the User I need to be able to determine who viewed what and when they viewed it. I have considered the fact that the result from SELECT yesterday may be different than the result set returned by the SAME SELECT statement today, but when used in conjunction with the INSERT, UPDATE, DELETE audit logging I have already created, the answers to who viewed, what and when would be readily available. I have been searching all morning and... The only thing I find on logging of Select statements is that the information can be held in the Log Files...if Logging is enabled. As I am only interested in the statements presented against certain tables... Turning on logging gives me more than I need or care to look through. I could write a script to parses the Log Files into a Database Table but would prefer to avoid enabling the file logging of statements if possible. Thanks for any reference or help you may be able to provide. Jim signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [pgsql-hackers] Daily digest v1.5568 (24 messages)
I wonder if this idea might be taken a little further, to allow read-only tablespaces? This would allow old partitions in very large databases to be kept on read-only media, and would allow normal backups to ignore this unchanging set of data. It also allows for certain specific optimisations for this type of data, as the MVCC rules are now relaxed. Inclusion of a row in a read-only index is now enough to guarantee the visibility of that row to all backends, and fetches based solely on the index now become possible. FWIW, Oracle does something like this. Just a thought. __ Marc On Mon, 2005-11-21 at 09:53 -0400, [EMAIL PROTECTED] wrote: Date: Sun, 20 Nov 2005 22:39:49 +0200 (EET) From: Heikki Linnakangas [EMAIL PROTECTED] To: pgsql-hackers@postgresql.org Subject: Data directory on read-only media Message-ID: [EMAIL PROTECTED] Hi, I've been thinking about running postgres from read-only media. It's handy for creating demo CDs etc. I hacked together a patch that allows you to run Postgres without write permissions to the data directory. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Supporting NULL elements in arrays
How about representing null array elements with a special-case cast-like
null? Something like {::null}
__
Marc
On Tue, 2005-11-08 at 23:30 -0400, [EMAIL PROTECTED]
wrote:
Date: Tue, 08 Nov 2005 19:21:34 -0500
From: Tom Lane [EMAIL PROTECTED]
To: pgsql-hackers@postgreSQL.org
Subject: Re: Supporting NULL elements in arrays
Message-ID: [EMAIL PROTECTED]
I wrote:
... the most straightforward thing to do is define an empty element
as meaning a NULL. But this might be objected to on a couple of
grounds:
I just thought of another, potentially fatal objection: it's ambiguous
whether '{}'::text[] should be taken to mean an empty (zero-length)
array or an array containing a single NULL element.
For backwards compatibility it should mean an empty array, but then
there's no way to represent ARRAY(NULL) in data dumps, which won't
do either.
The only workaround that comes to mind is to allow explicit
specification of what's meant: '[1:1]{}' would be needed to represent
the one-null case. Ugly.
Ideas anyone?
regards, tom lane
signature.asc
Description: This is a digitally signed message part
[HACKERS] Odd db lockup - investigation advice wanted
Last week I managed to lock-up and then crash a development database.
I'm going to try to reproduce it today and would like to know what I can
do to further investigate the problem.
I am running Linux 2.6.9-11.ELsmp X86_64 on a Quad Dual-Core Opteron
I have the following postgres RPMs installed:
postgresql-libs-7.4.7-2.RHEL4.1
postgresql-contrib-8.0.3-1PGDG
postgresql-libs-8.0.3-1PGDG
postgresql-server-8.0.3-1PGDG
postgresql-8.0.3-1PGDG
PGDATA is installed on a Netapp network storage device.
We are using slony 1.1.0 for replication.
The (provider) database locked-up after I killed a slony client process
(kill -9) on the subscriber. Psql connections would not respond to \d
and simply locked up. I was able to run a query to check for blocking
locks - this returned no rows. There was a significant test load on the
database at the time.
I stopped the database but was unable to restart it. I was unable to
kill a number of postgres processes and could not release postgres
shared memory. Having decided that the database was toast, I discovered
that I could not even delete the database files, and eventually the only
solution was a full reboot.
This hardware was destined to be put into production in the next two
weeks but this crash has shaken our confidence somewhat. Any advice on
how to further investigate this would be much appreciated
Here is an exerpt from the logs at the time of the failure:
LOG: duration: 4143.996 ms statement: execute wibble_transaction
( '2012416', '3410660', '2005-11-04 17:39:49 -0600', '20005', '3', '0',
'{22000,22011,22001,22002,22003,22004,22005,22006,22007,22008,22009,22010}',
'{8,0,0,0,2,1,0,7,2,23,26,0}' )
LOG: duration: 4814.012 ms statement: execute write_wibble
( '2048847',
'{{15000,17660,0},{15001,3522,0},{15002,0,0},{15003,3851,0},{15004,0,0},{15005,0,0},{15006,0,0},{15007,0,0},{15011,0,0},{15012,0,0},{15013,0,0},{15014,0,0},{15015,0,0},{15016,0,0},{15017,0,0},{15018,0,0},{15019,0,0},{15020,0,0},{15021,0,0},{15022,0,0},{15023,0,0},{15024,0,0},{15025,0,0},{15026,0,0},{15048,0,0},{15030,0,0},{15031,0,0},{15032,0,0},{15033,0,0},{15035,0,0},{15036,0,0},{15037,0,0},{15038,0,0},{15040,0,0},{15041,1,0},{15042,0,0},{15043,0,0},{15046,0,0},{15047,0,0},{15049,1000,0},{15050,0,0},{15051,0,0},{15052,0,0}}'
)
LOG: unexpected EOF on client connection
LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: received immediate shutdown request
WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
My lock checking query:
select d.datname || '.' || c.relname as object,
l.transaction as trans, l.pid, l.mode,
case when b.blocked then b.blocker else null end as blocker
from (
select w.pid as pid, h.pid as blocker, 't'::bool as blocked
from pg_locks h, pg_locks w
where h.granted
andnot w.granted
and ( (h.relation = w.relation and h.database = w.database)
or h.transaction = w.transaction)
union
select h.pid, null, 'f'::bool as blocked
from pg_locks h, pg_locks w
where h.granted
andnot w.granted
and ( (h.relation = w.relation and h.database = w.database)
or h.transaction = w.transaction)
) b,
pg_locks l
left outer join pg_database d
on d.oid = l.database
left outer join pg_class c
on c.oid = l.relation
where l.pid = b.pid
order by l.pid;
Thanks for any suggestions.
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] dynamic loading of .so (originally from
Bruce, There are two problems, though maybe I came to the wrong solution. I'm not averse to changing it. 1) Veil starts from a user process and not from the postmaster. This means that any shared memory segments created can not necessarily be mapped to the same address space in each process, which makes using such shared memory a little more painful than just referencing pointers. 2) There is no simple way to close the shared memory segement when postgres shuts down. In an earlier version of Veil I did allocate my own shared memory and could revive the code if we can overcome problem number 2. I'd like to also overcome problem 1 as it makes the code extremely ugly but it's no show stopper. Any thoughts? __ Marc On Mon, 2005-10-24 at 09:24 -0400, Bruce Momjian wrote: Uh, why does Veil have to allocate space from the backend shared memory pool rather than allocating its own shared memory segment? --- Marc Munro wrote: -- Start of PGP signed section. Tom, My project, Veil, steals some of this shared memory for itself. I wan't aware of the slop factor and was pleased that it just worked. I guess I should have been asking questions of this group. Sorry. I would like Veil to be a good citizen and place a legitimate request for its shared memory (probably about 16K normally). Veil is loaded as a shared library, which I would assume means that it is not present during database startup, making contributing to the sizing calculation and racing the lockmgr a little tricky. I see a number of possibilities: - Have a GUC to allocate shmem space for add-ons - Have add-ons register themselves and provide hooks for sizing and initial space allocation - Let add-ons race with the lockmgr for the slop (ie leave as it is) Thoughts? I would be happy to work on this and provide whatever patches are necessary. Thanks. __ Marc Munro On Mon, 2005-10-17 at 10:34 -0300, [EMAIL PROTECTED] wrote: Date: Mon, 17 Oct 2005 00:42:17 -0400 From: Tom Lane [EMAIL PROTECTED] To: Douglas McNaught [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], pgsql-general@postgresql.org pgsql-general@postgresql.org Subject: Re: dynamic loading of .so Message-ID: [EMAIL PROTECTED] Douglas McNaught [EMAIL PROTECTED] writes: [EMAIL PROTECTED] writes: are there any way to make them global for all the instances? Put them in shared memory. This probably isn't trival, as all the shared memory is allocated up front and used for existing purposes (at least, as I understand it). There's a slop factor of 100KB or so in the shared memory size calculation, which means that an add-on library that requests space soon enough could probably get away with allocating up to a few KB without causing any problems. (The slop is not totally useless, since for instance the lock manager might eat it up if more locks get requested than expected.) In the long run it might be interesting to add hooks that allow preloaded libraries to contribute to the shmem sizing calculation and then to snarf up the space they've requested before it can get eaten by the lockmgr. regards, tom lane -- End of PGP section, PGP failed! signature.asc Description: This is a digitally signed message part
Re: [HACKERS] dynamic loading of .so (originally from pgsql-general)
Tom, My project, Veil, steals some of this shared memory for itself. I wan't aware of the slop factor and was pleased that it just worked. I guess I should have been asking questions of this group. Sorry. I would like Veil to be a good citizen and place a legitimate request for its shared memory (probably about 16K normally). Veil is loaded as a shared library, which I would assume means that it is not present during database startup, making contributing to the sizing calculation and racing the lockmgr a little tricky. I see a number of possibilities: - Have a GUC to allocate shmem space for add-ons - Have add-ons register themselves and provide hooks for sizing and initial space allocation - Let add-ons race with the lockmgr for the slop (ie leave as it is) Thoughts? I would be happy to work on this and provide whatever patches are necessary. Thanks. __ Marc Munro On Mon, 2005-10-17 at 10:34 -0300, [EMAIL PROTECTED] wrote: Date: Mon, 17 Oct 2005 00:42:17 -0400 From: Tom Lane [EMAIL PROTECTED] To: Douglas McNaught [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], pgsql-general@postgresql.org pgsql-general@postgresql.org Subject: Re: dynamic loading of .so Message-ID: [EMAIL PROTECTED] Douglas McNaught [EMAIL PROTECTED] writes: [EMAIL PROTECTED] writes: are there any way to make them global for all the instances? Put them in shared memory. This probably isn't trival, as all the shared memory is allocated up front and used for existing purposes (at least, as I understand it). There's a slop factor of 100KB or so in the shared memory size calculation, which means that an add-on library that requests space soon enough could probably get away with allocating up to a few KB without causing any problems. (The slop is not totally useless, since for instance the lock manager might eat it up if more locks get requested than expected.) In the long run it might be interesting to add hooks that allow preloaded libraries to contribute to the shmem sizing calculation and then to snarf up the space they've requested before it can get eaten by the lockmgr. regards, tom lane signature.asc Description: This is a digitally signed message part
Re: User-assigned LWLocks (was Re: [HACKERS] Announcing Veil)
For my part, I don't see any current need for extra locking here. Veil ensures that only one session ever calls LWLockAssign(), and as the Veil LWLock is allocated on the first piece of user-invoked SQL to call a Veil function, I see no scope for races between Veil and the rest of Postgres. Maybe the correct thing to do is only allow 1 user-defined LWLock for now, and place a comment with the definition of NUM_USER_DEFINED_LWLOCKS to warn that locking should be implemented if more than 1 is ever needed. __ Marc On Fri, 2005-10-07 at 16:21 -0400, Tom Lane wrote: Marc Munro [EMAIL PROTECTED] writes: On the use of LWLockAssign():can anyone tell me if I should protect the call using the ShmemLock spinlock? Hmm ... the comment for LWLockAssign is not meant to imply that the caller could do that; in the event of being out of LWLocks, the code would elog(FATAL) without releasing the spinlock, which would lock up the whole database. If we were to do it that way we'd need the spinlock handling to be done inside LWLockAssign. This would not be that bad, just a marginal slowdown during database startup, but given the low demand for this feature I'm not very eager to do it. The alternative though would seem to be to adopt some convention about another LWLock to take while trying to assign a new LWLock post-startup. None of the existing locks seem very appropriate for this, and putting the responsibility on callers might be unwise anyway. Thoughts? regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Announcing Veil
In response to both Bruce and Tom, Thanks for this. I am very happy that this patch will be going in. Thanks also for pointing out the correct header to use. As Tom points out, this will do nothing for users of 7.4 and 8.0. For these versions I propose to continue to use MMCacheLock. As far as I can see, this is safe in 7.4, and otherwise unused in 8.0. On the use of LWLockAssign():can anyone tell me if I should protect the call using the ShmemLock spinlock? __ Marc Munro On Fri, 2005-10-07 at 00:10 -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: With only one known request for a user-allocate d lock, it's hard to justify the overhead of a GUC variable. True, but are people going to recompile PostgreSQL to use this feature? Seems they would have to. How you figure that? The proposed default value was 4, which seems fine to me, given that the known worldwide demand amounts to 1. Oh, so you are going to give him a few slots. I thought we were going to default to 0 and he was going to have to bump it up to use his software. That sounds fine to me. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Announcing Veil
Tom,
Thanks for your reponse. Unless I am missing your point, to add more
locks we require a minor code change to the postgres server. I am happy
to submit a patch but this will not help Veil work with existing
versions of Postgres. I am aiming for compatibility with 7.4 onward.
Your views on this would be appreciated.
Assuming that simply allocating a few extra LWLocks for user-defined
functions is acceptable, here are some patches:
--cut---
*** ./src/backend/storage/lmgr/lwlock.c Sat Aug 20 16:26:24 2005
--- lwlock.cWed Oct 5 08:20:31 2005
***
*** 120,126
*/
numLocks += 2 * NUM_SLRU_BUFFERS;
! /* Perhaps create a few more for use by user-defined modules? */
return numLocks;
}
--- 120,127
*/
numLocks += 2 * NUM_SLRU_BUFFERS;
! /* Create a few more for use by user-defined modules. */
! numLocks += NUM_USER_DEFINED_LWLOCKS;
return numLocks;
}
--cut---
*** ./src/include/storage/lwlock.h Sat Aug 20 16:26:34 2005
--- lwlock.hWed Oct 5 08:22:26 2005
***
*** 53,58
--- 53,62
MaxDynamicLWLock = 10
} LWLockId;
+ /*
+ * Allocate a few LWLocks for user-defined functions.
+ */
+ #define NUM_USER_DEFINED_LWLOCKS 4
typedef enum LWLockMode
{
--cut---
__
Marc Munro
On Tue, 2005-10-04 at 22:51 -0400, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
Since I was unable to dynamically assign a LWLock using
LWLockAssign (none available), I have fairly arbitrarily overloaded the
use of existing LWLocks. When the flames die down perhaps we can
discuss making a small number (one would be enough for me) of LWLocks
available.
Perhaps you missed the comment in NumLWLocks()?
regards, tom lane
signature.asc
Description: This is a digitally signed message part
[HACKERS] Announcing Veil
This is to announce the first, Alpha, release of Veil, a database security add-on for Postgres. It allows databases to be created with efficient row-level access controls; different users have access to different subsets of data. Veil is hosted on pgfoundry at http://veil.projects.postgresql.org/ Documentation is here: http://veil.projects.postgresql.org/curdocs/index.html If there is sufficient interest, I would ultimately like Veil to be added to contrib - we will have to see how things pan out. I am announcing this to the postgres hackers list as I hope for review comments from a qualified community. All comments will be welcomed. If you would like somewhere controversial to start your review, take a look at veil_shmem.c which hooks into the postgres shared memory subsystem. Since I was unable to dynamically assign a LWLock using LWLockAssign (none available), I have fairly arbitrarily overloaded the use of existing LWLocks. When the flames die down perhaps we can discuss making a small number (one would be enough for me) of LWLocks available. Thank you. __ Marc Munro signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Incremental backup
Kevin, Hi. I was looking into PITR for PostgreSQL myself about a year back but life intervened. I am an Oracle DBA so may be able to help you with an understanding of how Oracle does this. You wrote: Oracle has something they call rollback segments which I assume are separate bits of data that have enough information to reverse changes that were made to the database during a transaction, and I figured PITR would (or could) apply particular saved rollback segments to the current state in order to roll back a table, tablespace, or database to the state it was in at a particular point in time. As it is, it sounds like PITR is a bit less refined than I expected. Actually Oracle uses its redo logs (like PostgreSQL' WAL) and archivelogs (copies of old redo logs) rather than rollback segments. The way it works, more or less, is that you start from a hot backup, and roll-forward using archivelogs and redo logs to the point in time to which you wish to recover. The whole point of this is not so much that we can restore our database to some point in the past, as that we can recover to just before some disaster struck, from a starting point of a previous hot backup. In fact, the whole PITR thing for Oracle seems to be simply an enabling technology for hot backups. This seems to me to be one of the killer enterprise features that PostgreSQL currently lacks. So the relevant question is: how is *our* PITR going to work? In particular, how is it going to interact with our WAL files and the table store? If I'm not mistaken, right now (well, as of 7.2 anyway) we round robin through a fixed set of WAL files. For PITR, I assume we'd need an archivelog function that would copy the WAL files as they're checkpointed to some other location (with destination names that reflect their order in time), just for starters. I believe that three things are needed: 1) filesystem-based hot backups. It may be possible to simply copy the database files even though they would be open. Oracle places each tablespace into a hot-backup mode prior to taking the copy. In this mode, updates to the files are queued-up to be applied once the tablespace is taken out of hot-backup mode. This all seems quite tricky and will slow the database down. 2) Auto-archiving of WAL files. Just as you suggest. 3) A recovery controller that can figure out the state of the restored database, and manage the reapplication of archived and current WAL files. It'd be *awfully* nice if you could issue a command to roll a table (or, perhaps, a tablespace, if you've got a bunch of foreign keys and such) back to a particular point in time, from the command line, with no significant advance preparation (so long as the required files are still around, and if they're not then abort the operation with the appropriate error message). But it doesn't sound like that's what we're talking about when we talk about PITR... Nice but lots of work for questionable benefit. If your application needs to keep track of history there are better ways to do it. If not, then PITR should be seen as an exceptional circumstance and so need not be so easy to manage. I wouldn't expect the O* docs to be particularly revealing about how the database manages PITR at the file level, but if it does, would you happen to know where so I can look at it? What I've seen so far is very basic and not very revealing at all... The best Oracle documentation on this is available through Oracle technet (technet.oracle.com) for which you will have to need to register. Look for documentation-Oracle 9i Documentation-list of books-Backup and Recovery Concepts. -- Marc[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
