Re: [HACKERS] parallel pg_restore
Joshua D. Drake wrote: Andrew Dunstan wrote: There are in fact very few letters available, as we've been fairly profligate in our use of option letters in the pg_dump suite. j and m happen to be two of those that are available. --max-workers Perhaps, but please do not use that as justification for using -m. That would be equally silly as abbreviating number of workers to -n. -- Sincerely, Stephen R. van den Berg. Experience is something you don't get until just after you need it. -- 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] parallel pg_restore
On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote: j and m happen to be two of those that are available. I honestly don't have a terribly strong opinion about what it should be called. I can live with jobs or multi-threads. Perhaps we can use -j for jobs and -m for memory, so we can set memory available across all threads with a single total value. I can live with jobs or multi-threads also, whichever we decide. Neither one is confusing to explain. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] macport for libpqxx
Hi Darren
On Mon, Sep 22, 2008 at 8:05 PM, Darren Weber
[EMAIL PROTECTED] wrote:
Alternatively, the libpqxx docs say you can set ${PG_CONFIG} to the
path to pg_config, so perhaps you can set that in configure.env (that
sounds like the cleanest option).
I tried this, but it's not working. I cannot find the documentation
of this option - where did you see it?
In the README: http://pqxx.org/development/libpqxx/browser/trunk/README
I'll take this up on the libpqxx-general email list,
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
Gborg is long gone (though the lists are still there at present). I
don't know if the libpqxx guys have moved yet, or are still using the
old lists.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: move column defaults into pg_attribute along with attacl
Hi, Tom Lane wrote: Yah. However, I started to look at doing this and immediately hit a stumbling block: we need a representation in pg_depend for a column's default expression (as distinct from the column itself). Just to understand the issue here: what's the reason for having an OID for the default value and possible another one for a ACLs, but none for the attribute itself? Why don't we just have a unique OID for pg_attribute (i.e. drop the BKI_WITHOUT_OIDS of pg_attribute) and merge in the default values and ACLs? Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Committed. Tom Lane wrote: * You should try to get rid of LOCALE_NAME_BUFLEN altogether. Definitely the comment about it in pg_control.h is now obsolete. Yep. I removed LOCALE_NAME_BUFLEN. The real max length of a locale name is now NAMEDATALEN, because it's stored in a name field in pg_database. NAMEDATALEN is only 64 bytes, whereas LOCALE_NAME_BUFLEN was 128. 64 bytes should be enough for en_GB.UTF8 style locale names, but I wonder if it's enough for the longer names used on Windows? Could someone confirm that, please? An important restriction, however, is that each database's character set must be compatible with the database's envarLC_CTYPE/ setting. Also I wonder whether we shouldn't say that it must be compatible with LC_CTYPE *and* LC_COLLATE. I think we should, but that's in fact not what is tested. Before the patch as well, we only tested that the encoding matches LC_CTYPE, but you could set LC_COLLATE to anything. I'll work on a subsequent patch to tighten that. * This makes sense, but then shouldn't we make the identical restriction for encoding? +The literalCOLLATE/ and literalCTYPE/ settings must match +those of the template database, except when template0 is used as +template. This is because literalCOLLATE/ and literalCTYPE/ It wouldn't be as bullet-proof for encoding, because we'd still have the problem that the encoding in the shared system tables would be ill-defined. That's a pre-existing problem, though. We could simply remove support for per-database encodings altogether and fix it at initdb time, as Martijn suggest earlier, but now that we have per-database locales, per-database encodings is a lot more useful as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
On Mon, Sep 22, 2008 at 10:22:35AM +0300, Heikki Linnakangas wrote: BTW, the original patch didn't have any provision for creating rows in pg_collation reflecting the locales available in the OS, but I think we'd need that. Otherwise the DBA would need to manually run CREATE COLLATION for every collation they want users to be able to use. Assuming we do that, the situation that we can't find a row with given LC_COLLATE and LC_CTYPE should not arise in practice. You're assuming collations are denumerable. They're not. There is no way to find the list of available collations/locales. You may be able to guess a few but certainly not all of them. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP patch: Collation support
On Tue, Sep 23, 2008 at 10:20 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Committed. *adds yet another item to his pgAdmin todo list* :-( Tom Lane wrote: * You should try to get rid of LOCALE_NAME_BUFLEN altogether. Definitely the comment about it in pg_control.h is now obsolete. Yep. I removed LOCALE_NAME_BUFLEN. The real max length of a locale name is now NAMEDATALEN, because it's stored in a name field in pg_database. NAMEDATALEN is only 64 bytes, whereas LOCALE_NAME_BUFLEN was 128. 64 bytes should be enough for en_GB.UTF8 style locale names, but I wonder if it's enough for the longer names used on Windows? Could someone confirm that, please? The longest I can find is: Serbian (Cyrillic)_Bosnia and Herzegovina at 42 characters. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
On Mon, Sep 22, 2008 at 06:11:04PM +0300, Heikki Linnakangas wrote: This patch should allow to use both system catalog and ICU. Not without another patch that actually introduces ICU support. What that would look like, how that would be stored in the catalogs, and whether we want that is whole another topic. Without that, the STRCOLFN part of the original patch is pointless, and I would've ripped that out anyway even if we decided to add the pg_collation catalog in this release. Eh? How you store collations names is easy. Every collation has a textual name which is what we store in the catalog. I'm not sure why you'd think it'd be any more complicated than that. And it has precisely nothing to do with ICU and everything to do with being able to support multiple source of collation information. We already have two: builtin (strcmp/memcmp) and C library (strcoll). Letting peope add more is the goal. I'm sure once the catalog exists the existing ICU-for-Postgres patch will be adjusted to use it. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP patch: Collation support
Martijn van Oosterhout wrote: On Mon, Sep 22, 2008 at 10:22:35AM +0300, Heikki Linnakangas wrote: BTW, the original patch didn't have any provision for creating rows in pg_collation reflecting the locales available in the OS, but I think we'd need that. Otherwise the DBA would need to manually run CREATE COLLATION for every collation they want users to be able to use. Assuming we do that, the situation that we can't find a row with given LC_COLLATE and LC_CTYPE should not arise in practice. You're assuming collations are denumerable. They're not. There is no way to find the list of available collations/locales. You may be able to guess a few but certainly not all of them. locale -a manages to do it somehow... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
On Tue, Sep 23, 2008 at 01:32:38PM +0300, Heikki Linnakangas wrote: Martijn van Oosterhout wrote: You're assuming collations are denumerable. They're not. There is no way to find the list of available collations/locales. You may be able to guess a few but certainly not all of them. locale -a manages to do it somehow... Sure, by (on glibc) opening the binary archive and parsing it and then trying to reverse lookup the alias list. We could ofcourse program something for each platform to determine a list but who is going to maintain that? How do you handle the list changing? Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP patch: Collation support
Martijn van Oosterhout napsal(a): On Mon, Sep 22, 2008 at 06:11:04PM +0300, Heikki Linnakangas wrote: This patch should allow to use both system catalog and ICU. Not without another patch that actually introduces ICU support. What that would look like, how that would be stored in the catalogs, and whether we want that is whole another topic. Without that, the STRCOLFN part of the original patch is pointless, and I would've ripped that out anyway even if we decided to add the pg_collation catalog in this release. Eh? How you store collations names is easy. Every collation has a textual name which is what we store in the catalog. I'm not sure why you'd think it'd be any more complicated than that. And it has precisely nothing to do with ICU and everything to do with being able to support multiple source of collation information. We already have two: builtin (strcmp/memcmp) and C library (strcoll). Letting peope add more is the goal. pg_collation catalog is also important for pg_dump, because system collation names are not compatible over OS and pg_dump output should be portable. pg_collation adds abstract layer which solve this problem. I'm sure once the catalog exists the existing ICU-for-Postgres patch will be adjusted to use it. Yes, I agree with Martijn. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Zdenek Kotala wrote: pg_collation catalog is also important for pg_dump, because system collation names are not compatible over OS and pg_dump output should be portable. pg_collation adds abstract layer which solve this problem. That's a valid point. We'll still need a way to map OS locale to whatever internal names we invent for them, though, so I'm not sure if the pg_collation catalog helps much, but just moves the problem elsewhere. The pg_dump output of the CREATE COLLATION statements still wouldn't be portable from one OS to another. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Initial prefetch performance testing
[resending due to the attachment being too large for the -hackers list -- weren't we going to raise it when we killed -patches?] Greg Smith [EMAIL PROTECTED] writes: Using the maximum prefetch working set tested, 8192, here's the speedup multiplier on this benchmark for both sorted and unsorted requests using a 8GB file: OSSpindlesUnsorted X Sorted X 1:Linux 1 2.3 2.1 2:Linux 1 1.5 1.0 3:Solaris 1 2.6 3.0 4:Linux 3 6.3 2.8 5:Linux (Stark) 3 5.3 3.6 6:Linux 10 5.4 4.9 7:Solaris*48 16.99.2 Incidentally I've been looking primarily at the sorted numbers because they parallel bitmap heap scans. (Note that the heap scan is only about half the i/o of a bitmap index scan + heap scan so even if it's infinitely faster it'll only halve the time spent in the two nodes.) Hm, I'm disappointed with the 48-drive array here. I wonder why it maxed out at only 10x the bandwidth of one drive. I would expect more like 24x or more. I wonder if Solaris's aio has an internal limit on how many pending i/o requests it can handle. Perhaps it's a tunable? Unfortunately I don't see a convenient low-invasive way to integrate aio into Postgres. posix_fadvise we can just issue the advice and then forget about it. But aio we would pretty much have to pick a target buffer, pin it, issue the aio and then remember the pin later when we need to read the buffer. That would require restructuring the code significantly. I'm quite surprised Solaris doesn't support posix_fadvise -- perhaps it's in some other version of Solaris? Here's a graph of results from this program for various sized arrays on a single machine: http://wiki.postgresql.org/images/a/a3/Results.svg Each colour corresponds to an array of a different number of spindles ranging from 1 to 15 drives. The X axis is how much prefetching was done and the Y axis is the bandwidth obtained. There is a distinct maximum and then dropoff and it would be great to get some data points for larger arrays to understand where that maximum goes as the array gets larger. Conclusion: on all the systems I tested on, this approach gave excellent results, which makes me feel confident that I should see a corresponding speedup on database-level tests that use this same basic technique. I'm not sure whether it might make sense to bundle this test program up somehow so others can use it for similar compatibility tests (I'm thinking of something similar to contrib/test_fsync), will revisit that after the rest of the review. Next step: I've got two data sets (one generated, one real-world sample) that should demonstrate a useful heap scan prefetch speedup, and one test program I think will demonstrate whether the sequential scan prefetch code works right. Now that I've vetted all the hardware/OS combinations I hope I can squeeze that in this week, I don't need to test all of them now that I know which are the interesting systems. I have an updated patch I'll be sending along shortly. You might want to test with that? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_type.h regression?
Tom Lane [EMAIL PROTECTED] writes: Greg Sabino Mullane [EMAIL PROTECTED] writes: Looks like the box-array semicolon got changed to a comma at some point - attached patch changes it back (\054 to \073) [ scratches head... ] I seem to have done that in rev 1.198, but I don't recall why. It's late here though ... I think my reasoning was that all array types should have typdelim = ',' for consistency. It doesn't actually matter because nothing looks at the value ... the element type's delimiter is what array_in/out use. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: make SQL interval-literal syntax work per spec
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom, which Interval TODO items did you complete with this patch? http://wiki.postgresql.org/wiki/Todo#Dates_and_Times I think we've at least mostly fixed * Support ISO INTERVAL syntax if units cannot be determined from the string, and are supplied after the string * Add support for year-month syntax, INTERVAL '50-6' YEAR TO MONTH There might be a few glitches left but they are at much smaller grain than the TODO is talking about. Thanks, marked as done. ... while I'm looking: I am not sure that I think either of these TODO items are sane or standards-compliant: * Interpret INTERVAL '1 year' MONTH as CAST (INTERVAL '1 year' AS INTERVAL MONTH), and this should return '12 months' * Support precision, CREATE TABLE foo (a INTERVAL MONTH(3)) OK, I have removed the items; we can always re-add them if they are requested. Thanks for the review. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Initial prefetch performance testing
Greg Smith napsal(a): On Mon, 22 Sep 2008, Gregory Stark wrote: I'm quite surprised Solaris doesn't support posix_fadvise -- perhaps it's in some other version of Solaris? Solaris has only fake variant of posix_fadvise. See http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c UFS has own optimization. For example if it detects sequential scan then file cache is limited on 80kB. Or it also has prefetching. ZFS has intelligent read patter recognitions algorithms and other improvements. Zdenek -- 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] Proposal: move column defaults into pg_attribute along with attacl
Markus Wanner [EMAIL PROTECTED] writes: Just to understand the issue here: what's the reason for having an OID for the default value and possible another one for a ACLs, but none for the attribute itself? Well, as far as the dependency system goes this way is more convenient. If pg_attribute entries had their own OIDs it would be fairly hard to implement DROP TABLE except with an intermediate step of dropping each of the columns one by one, because you'd pretty much have to have explicit pg_depend entries linking each column to its table, and that behavior is what you'd get from the dependency traversal. So that's why we didn't add OIDs (back) to pg_attribute when we invented the dependency system. Default values would need their own OIDs, or at least some distinct representation in pg_depend, in any case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Tue, Sep 23, 2008 at 01:32:38PM +0300, Heikki Linnakangas wrote: locale -a manages to do it somehow... Sure, by (on glibc) opening the binary archive and parsing it and then trying to reverse lookup the alias list. We could ofcourse program something for each platform to determine a list but who is going to maintain that? How do you handle the list changing? exec(locale -a) ... I suppose we'd need something else for Windows, but I'm sure there's a way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch
Jeff Davis [EMAIL PROTECTED] writes: Here is a patch that is an initial attempt to reorganize the parse tree representation. Oh dear, we seem to have spent yesterday doing the same work :-( I'll go over this and try to merge it with my own WIP. * There are a couple of other rough points in places where it's hard to traverse up the parse tree or query tree. Yeah, I'd been running into that issue too. Adding an explicit pointer to the CTE into the RTE doesn't work because it renders the parse tree un-copiable (at least without something a lot more sophisticated than copyObject; and saving/loading rule parsetrees would be tough too). What I've got at the moment is that creation of an RTE_CTE RTE copies the CTE's lists of output column types/typmods into the RTE. This eliminates the need for expandRTE and a couple of other places to be able to find the CTE; everything they need is in the RTE. So far as I can see, everyplace else that might need to find the CTE from the RTE is in places that either have a ParseState available, or have some comparable structure that could provide a way to search upwards for CTEs (eg, in ruleutils the deparse context will need to track uplevel CTE lists as well as rtables). It is a bit tedious though. Can anyone think of another way that would still preserve the notion of multiple RTEs being links to the same CTE rather than independent subqueries? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch
Tom Lane [EMAIL PROTECTED] writes: Yeah, I'd been running into that issue too. Adding an explicit pointer to the CTE into the RTE doesn't work because it renders the parse tree un-copiable (at least without something a lot more sophisticated than copyObject; and saving/loading rule parsetrees would be tough too). Well the alternative to direct pointers is as you did with subqueries, turning the set into a flat array and storing indexes into the array. I'm not sure if that applies here or not. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- 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] Proposal: move column defaults into pg_attribute along with attacl
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Tom Lane wrote: Well, as far as the dependency system goes this way is more convenient. If pg_attribute entries had their own OIDs it would be fairly hard to implement DROP TABLE except with an intermediate step of dropping each of the columns one by one, because you'd pretty much have to have explicit pg_depend entries linking each column to its table, and that behavior is what you'd get from the dependency traversal. So, we do not want attributes to be dependent on the relation, because that complicates DROP TABLE. On the other hand, we want defaults (and possibly ACLs) to be dependent, so that the dependency system cleans them up when dropping the table. It that correct? ISTM that we should at least combine defaults and ACLs then, as proposed by Stephen. Regards Markus Wanner -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEUEARECAAYFAkjY7K8ACgkQsPwMloDjyo/UGACeI2YA2bAV+NAt3NXNCP641NXP phAAmPuQRUxkNRQOsVwQAKLNlayuPg4= =dwXa -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planner question
Tom Raney wrote: RELOPTINFO (tenk1): rows=1 width=244 path list: SeqScan(tenk1) rows=1 cost=0.00..434.00 IdxScan(tenk1) rows=1 cost=0.00..583.25 pathkeys: ((tenk1.unique2, onek.unique2)) --- cheapest startup path: SeqScan(tenk1) rows=1 cost=0.00..434.00 cheapest total path: SeqScan(tenk1) rows=1 cost=0.00..434.00 Hm, I don't recognize this output format, is it coming from some custom code? Yes, it is. I thought it was easier to read the OPTIMIZER_DEBUG output with the relation names instead of the relation indexes. I will post a patch against CVS HEAD if you think it will help others. Personally I would like to see optimizer debug become a configuration parameter rather than a compile-time parameter. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Tom Lane wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: On Tue, Sep 23, 2008 at 01:32:38PM +0300, Heikki Linnakangas wrote: locale -a manages to do it somehow... Sure, by (on glibc) opening the binary archive and parsing it and then trying to reverse lookup the alias list. We could ofcourse program something for each platform to determine a list but who is going to maintain that? How do you handle the list changing? exec(locale -a) ... I suppose we'd need something else for Windows, but I'm sure there's a way. IIRC, the data is in the registry. Should be enumerable somehow - we'll have to do it platform specific of course, but it's not the first time we'd do that for windows... //Magnus -- 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] Initial prefetch performance testing
Greg Smith [EMAIL PROTECTED] writes: On Mon, 22 Sep 2008, Gregory Stark wrote: Hm, I'm disappointed with the 48-drive array here. I wonder why it maxed out at only 10x the bandwidth of one drive. I would expect more like 24x or more. The ZFS RAID-Z implementation doesn't really scale that linearly. It's rather hard to get the full bandwidth out of a X4500 with any single process, and I haven't done any filesystem tuning to improve things--everything is at the defaults. Well random access i/o will fall pretty far short of the full bandwidth. Actually this is a major issue, our sequential_page_cost vs random_page_cost dichotomy doesn't really work when we're prefetching pages. In my experiments an array capable of supplying about 1.4GB/s in sequential i/o could only muster about 40MB/s of random i/o with prefetching and only about 5MB/s without. For this machine we would have quite a dilemma setting random_page_cost -- do we set it to 280 or 35? Perhaps access paths which expect to be able to prefetch most of their accesses should use random_page_cost / effective_spindle_count for their i/o costs? But then if people don't set random_page_cost high enough they could easily find themselves with random fetches being costed as less expensive than sequential fetches. And I have a feeling it'll be a hard sell to get people to set random_page_cost in the double digits let alone triple digits. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_type.h regression?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 [ scratches head... ] I seem to have done that in rev 1.198, but I don't recall why. It's late here though ... I think my reasoning was that all array types should have typdelim = ',' for consistency. It doesn't actually matter because nothing looks at the value ... the element type's delimiter is what array_in/out use. Ah, okay, that makes sense. Thanks for the explanation, I'll tweak my code to do it the right way by looking at the base type. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200809230934 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkjY8GIACgkQvJuQZxSWSsiOxgCgmpuxlkzQYlJNCdNCGc7houn7 hwQAoKfyLX3t5ArtEzaytD+nWOCl/br1 =QNIJ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Yeah, I'd been running into that issue too. Adding an explicit pointer to the CTE into the RTE doesn't work because it renders the parse tree un-copiable (at least without something a lot more sophisticated than copyObject; and saving/loading rule parsetrees would be tough too). Well the alternative to direct pointers is as you did with subqueries, turning the set into a flat array and storing indexes into the array. I'm not sure if that applies here or not. I think that just changes the problem into where can I find the array? ... The real issue here is that simplicity of copying etc requires that child nodes in a parse tree never have back-links leading up to their parent. If we were willing to drop that requirement for Query then we'd not need any auxiliary data structures to chase up to upper-level rtables or CTEs. I'm not sure this cure is better than the disease though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: move column defaults into pg_attribute along with attacl
Markus Wanner [EMAIL PROTECTED] writes: ISTM that we should at least combine defaults and ACLs then, as proposed by Stephen. Huh? Maybe I missed something, but I didn't think that was suggested anywhere. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Initial prefetch performance testing
Gregory Stark [EMAIL PROTECTED] writes: Perhaps access paths which expect to be able to prefetch most of their accesses should use random_page_cost / effective_spindle_count for their i/o costs? But then if people don't set random_page_cost high enough they could easily find themselves with random fetches being costed as less expensive than sequential fetches. And I have a feeling it'll be a hard sell to get people to set random_page_cost in the double digits let alone triple digits. Well, we could use something like Max(random_page_cost / effective_spindle_count, seq_page_cost) to ensure the result remains somewhat sane. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: move column defaults into pg_attribute along with attacl
Tom, * Tom Lane ([EMAIL PROTECTED]) wrote: Markus Wanner [EMAIL PROTECTED] writes: ISTM that we should at least combine defaults and ACLs then, as proposed by Stephen. Huh? Maybe I missed something, but I didn't think that was suggested anywhere. I had suggested a single table, with an OID, which would house anything that needed a seperate OID for columns (defaults and ACLs currently) in [EMAIL PROTECTED] It's not a completely thought-through solution, just something that struck me as a more general way of handling these situations (assuming we have more in the future and don't want to give each one its own table). If putting them together implies we have to complicate things to add some way to seperate them then it might not be worth it. Having a seperate table for each means we can use the table's OID to seperate them though. I still dislike this possible continued growth of the catalogs. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_type.h regression?
Greg Sabino Mullane [EMAIL PROTECTED] writes: I think my reasoning was that all array types should have typdelim = ',' for consistency. It doesn't actually matter because nothing looks at the value ... the element type's delimiter is what array_in/out use. Ah, okay, that makes sense. Thanks for the explanation, I'll tweak my code to do it the right way by looking at the base type. Well, plan B is that we could redefine things as all array types should have typdelim equal to their base type's typdelim. The backend still wouldn't care, but if there are other clients out there confusing the two, maybe this is important to worry about. DefineType currently always sets the array's typdelim to ',', so I figured that the built-in types should match that. But we could easily change both of them to do the other. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: move column defaults into pg_attribute along with attacl
Stephen Frost [EMAIL PROTECTED] writes: Huh? Maybe I missed something, but I didn't think that was suggested anywhere. I had suggested a single table, with an OID, which would house anything that needed a seperate OID for columns (defaults and ACLs currently) in [EMAIL PROTECTED] [ squint... ] But the default needs its *own* OID, because it is a distinct entity for dependency purposes. I think you're just confusing two separate issues there. If we did drop the object/subobject model and just give attributes their own OIDs, we'd still need to give a separate OID to each default; but ACLs wouldn't have their own OIDs. The DROP issue I was complaining about could certainly be solved with some uglification of the dependency-chasing code, so as far as the backend is concerned it might be about a wash. But there is enough client-side code out there that roots around in pg_depend for information we don't store any other way that I'm pretty hesitant to change the pg_depend representation now. I think adding a subobject column to pg_shdepend is probably the best answer --- we only didn't do that to start with because we thought it wasn't needed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Initial prefetch performance testing
Greg Smith [EMAIL PROTECTED] writes: I have an updated patch I'll be sending along shortly. You might want to test with that? Obviously I've got everything setup to test right now, am currently analyzing your earlier patch and the sequential scan fork that derived from it. If you've got a later version of the bitmap heap scan one as well, I'll replace the one I had been planning to test (your bitmap-preread-v9) with that one when it's available. Well here you go. It includes: . Bitmap heap scans (as before) . Index scans . Setting POSIX_FADV_SEQUENTIAL for bulk sequential scans . Improved (ie, debugged) autoconf tests for posix_fadvise (and posix_fallocate though I don't have any code using it yet) The bitmap heap scans are still prefetching the number of buffers I estimate based on effective_spindle_count according to my magic formula. We've been discussing throwing that out, I just haven't modified this to do that yet. Index scans prefetch all pages for matching index tuples on the leaf page when we do page-at-a-time scans. I haven't bothered doing the gradual ramp-up or keeping a ring of the optimal size prefetched. The sequential scan stuff is based on Zoltan's posts but done in a different way. It passes an i/o access strategy to smgr and fd.c which keeps track of what the previous strategy was and calls posix_fadvise if it's changed. This will correctly handle queries which reference the same table twice even if one reference is a sequential scan and the other is an index lookup. I have *not* been able to observe any significant effect from POSIX_FADV_SEQUENTIAL but I'm not sure what circumstances it was a problem. It sounds like it's a peculiar situation which is not easy to reliably reproduce. bitmap-preread-v18.diff.gz Description: Binary data -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Heikki Linnakangas napsal(a): Zdenek Kotala wrote: pg_collation catalog is also important for pg_dump, because system collation names are not compatible over OS and pg_dump output should be portable. pg_collation adds abstract layer which solve this problem. That's a valid point. We'll still need a way to map OS locale to whatever internal names we invent for them, though, so I'm not sure if the pg_collation catalog helps much, but just moves the problem elsewhere. It is true. For names we can for example use RFC479 0IANA register) http://tools.ietf.org/html/rfc4790#section-7 or use UNICODE terminology CLDR. The pg_dump output of the CREATE COLLATION statements still wouldn't be portable from one OS to another. I don't think so that pg_collation catalog should be dumped (maybe only with extra switch). Zdenek -- 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] Initial prefetch performance testing
On Tue, 23 Sep 2008, Gregory Stark wrote: I have *not* been able to observe any significant effect from POSIX_FADV_SEQUENTIAL but I'm not sure what circumstances it was a problem. It sounds like it's a peculiar situation which is not easy to reliably reproduce. Zoltan, Hans-Juergen: would it be possible for you to try the latest bitmap-preread-v18.diff.gz patch Greg Stark just sent over to the list? It's at http://archives.postgresql.org/message-id/[EMAIL PROTECTED] as well. That's a refinement of the original strategy you used, and I'd be curious to hear whether it still works usefully on the troublesome workload you submitted your original patch against. Since none of the rest of us have been successful so far replicating the large speed-up on multiple concurrent sequential scans you reported, I think you're the best candidate to see if there was any regression because of how the patch was refactored. I'm excited to see index scans in the new patch as well, since I've got 1TB of test data that gets navigated that way I can test with. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_type.h regression?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Well, plan B is that we could redefine things as all array types should have typdelim equal to their base type's typdelim. The backend still wouldn't care, but if there are other clients out there confusing the two, maybe this is important to worry about. +1 DefineType currently always sets the array's typdelim to ',', so I figured that the built-in types should match that. But we could easily change both of them to do the other. Thoughts? I'd slightly lean towards keeping it the way it has been (semicolon in both), since it doesn't matter to the backend, and who knows what else it may break. Kind of silly to spend too many cycles on this, of course, as it's really only box at the moment that uses a non-standard delimiter. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200809231133 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkjZDGcACgkQvJuQZxSWSsi6uACg1xS7oQu5JCzM9cWsvHAsMO66 GFkAnAtMFmSIEC+tQYqJ/4KDhco9ZXKK =hR1M -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Infrastructure changes for recovery
On Mon, 2008-09-22 at 23:06 +0100, Simon Riggs wrote:
On Thu, 2008-09-18 at 10:09 -0400, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
On Thu, 2008-09-18 at 09:06 -0400, Tom Lane wrote:
Do we really need a checkpoint there at all?
Timelines only change at shutdown checkpoints.
Hmm. I *think* that that is just a debugging crosscheck rather than a
critical property. But yeah, it would take some close investigation,
which maybe isn't warranted if you have a less-invasive solution.
OK, new patch, version 6. Some major differences to previous patch.
Ready for serious review prior to commit. I will be performing further
testing also.
Version 7
I've removed the concept of interrupting a restartpoint half way
through, I found a fault there. It was more ugly than the alternative
and less robust. The code now waits at the end of recovery if we are in
the middle of a restartpoint, but forces a do-it-more-quickly also. That
means we won't always get a fast start even though we skip the shutdown
checkpoint, but at least we're sure there's no chance of breakage
because of concurrent activiy, state changes etc..
I'm happy with this now.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Index: src/backend/access/transam/multixact.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/multixact.c,v
retrieving revision 1.28
diff -c -r1.28 multixact.c
*** src/backend/access/transam/multixact.c 1 Aug 2008 13:16:08 - 1.28
--- src/backend/access/transam/multixact.c 22 Sep 2008 19:28:56 -
***
*** 1543,1549
* SimpleLruTruncate would get confused. It seems best not to risk
* removing any data during recovery anyway, so don't truncate.
*/
! if (!InRecovery)
TruncateMultiXact();
TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(true);
--- 1543,1549
* SimpleLruTruncate would get confused. It seems best not to risk
* removing any data during recovery anyway, so don't truncate.
*/
! if (!IsRecoveryProcessingMode())
TruncateMultiXact();
TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(true);
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.317
diff -c -r1.317 xlog.c
*** src/backend/access/transam/xlog.c 11 Aug 2008 11:05:10 - 1.317
--- src/backend/access/transam/xlog.c 23 Sep 2008 14:56:37 -
***
*** 66,76
bool fullPageWrites = true;
bool log_checkpoints = false;
int sync_method = DEFAULT_SYNC_METHOD;
-
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
#endif
-
/*
* XLOGfileslop is the maximum number of preallocated future XLOG segments.
* When we are done with an old XLOG segment file, we will recycle it as a
--- 66,74
***
*** 119,124
--- 117,123
/* Are we doing recovery from XLOG? */
bool InRecovery = false;
+ bool reachedSafeStopPoint = false;
/* Are we recovering using offline XLOG archives? */
static bool InArchiveRecovery = false;
***
*** 131,137
static bool recoveryTarget = false;
static bool recoveryTargetExact = false;
static bool recoveryTargetInclusive = true;
- static bool recoveryLogRestartpoints = false;
static TransactionId recoveryTargetXid;
static TimestampTz recoveryTargetTime;
static TimestampTz recoveryLastXTime = 0;
--- 130,135
***
*** 286,295
--- 284,295
/*
* Total shared-memory state for XLOG.
*/
+ #define XLOGCTL_BUFFER_SPACING 128
typedef struct XLogCtlData
{
/* Protected by WALInsertLock: */
XLogCtlInsert Insert;
+ char InsertPadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogCtlInsert)];
/* Protected by info_lck: */
XLogwrtRqst LogwrtRqst;
***
*** 297,305
--- 297,312
uint32 ckptXidEpoch; /* nextXID epoch of latest checkpoint */
TransactionId ckptXid;
XLogRecPtr asyncCommitLSN; /* LSN of newest async commit */
+ /* add data structure padding for above info_lck declarations */
+ char InfoPadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogwrtRqst)
+ - sizeof(XLogwrtResult)
+ - sizeof(uint32)
+ - sizeof(TransactionId)
+ - sizeof(XLogRecPtr)];
/* Protected by WALWriteLock: */
XLogCtlWrite Write;
+ char WritePadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogCtlWrite)];
/*
* These values do not change after startup, although the pointed-to pages
***
*** 311,316
--- 318,342
int XLogCacheBlck; /* highest allocated xlog buffer index */
TimeLineID ThisTimeLineID;
+ /*
+ * IsRecoveryProcessingMode shows whether the postmaster is in a
+ * postmaster state earlier than PM_RUN, or not. This is a globally
+ * accessible state to allow EXEC_BACKEND
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Robert Haas wrote: It's too early to vote. :-) The second and third option have prerequisite. The purpose of them is to match granularity of access controls provided by SE-PostgreSQL and native PostgreSQL. However, I have not seen a clear reason why these different security mechanisms have to have same granuality in access controls. Have you seen a clear reason why they should NOT have the same granularity? Agreed. If we implement SE-PostgreSQL row-level security first, we might find that we have to replace the code once we implement SQL-level row-level security. If we do SQL-level security first, we can then adjust it to match what SE-PostgreSQL needs. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
KaiGai Kohei wrote: [1] Make a consensus that different security mechanisms have differences in its decision making, its gulanuality and its scope I think it is the most straightforward answer. As operating system doing, DAC and MAC based access controls should be independently applied on accesses from users, and this model is widely accepted. These facilities can also have different results, gulanualities and scopes. [2] Make a new implementation of OS-independent fine grained access control If it is really really necessary, I may try to implement a new separated fine-grained access control mechanism due to the CommitFest:Nov. However, we don't have enough days to develop one more new feature from the scratch by the deadline. I reconsidered the above two options have no differences fundamentally. In other word, making a new enhanced security implementation based on requirements also means making a consensus various security mechanism can have its individual rules including guranuality of access controls. So, I'll decide to try to implement fine-grained-only security mechanism also, because someone have such a requirememt. However, its schedule is extremely severe, if is has to be submitted due to the deadline of CommitFest:Nov. It is my hope to concentrate development of SE-PostgreSQL in v8.4 development cycle, and I think the above fine-grained-only one should be pushed to v8.5 cycle. Well, those might be your priorities, but I don't think they are the community's priorities. I think the community's priorities are to add security at the SQL level, and then we can see clearly what SE-PostgreSQL requires. This has been discussed before so it should not come as a surprise. What you can do is to do things in this order: 1) Add SE-PostgreSQL capabilities that layer over existing Postgres SQL-level permissions 2) Implement fine-grained permissions at the SQL level 3) Add SE-PostgreSQL capabilities for fine-grained permissions Perhaps you can only get #1 done for 8.4; I don't know, but I knew months ago that #2 had to be done before #3, and I think that was communicated. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL future ideas
[EMAIL PROTECTED] (Gevik Babakhani) writes: It might look like an impossible goal to achieve.. But if there is any serious plan/idea/ammo for this, I believe it would be very beneficial to the continuity of PG. Actually, I imagine that such a rewrite would run a very considerable risk of injuring the continuity of PostgreSQL VERY BADLY, to the point of causing community fractures and forks of the codebase. When you write something in C++, you have to pick a subset of the language that is supported fairly identically (in semantics) by all of the compilers that you wish to support. Seeing as how PostgreSQL is already a mature system written in C, a rewrite into C++, *which is a different language* that is NOT simply a superset of C functionality, would require substantial effort, lead to fractious disagreements, and would, without ANY doubt, fracture the code base into *AT LEAST* two versions, namely: a) The existing C code base, and b) One (possibly more) C++ rewrites This does not strike me as a particularly useful exercise. If I intended such a rewrite, I'd much rather consider using something *interestingly* different from C, like Erlang or Eiffel or Haskell. -- cbbrowne,@,linuxdatabases.info http://linuxfinances.info/info/sgml.html For a good prime call: 391581 * 2^216193 - 1 -- [EMAIL PROTECTED] (Szymon Rusinkiewicz) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby Design
Hot Standby design has been growing on the PostgreSQL Wiki for some weeks now. I've updated the design to reflect all feedback received so far on various topics. http://wiki.postgresql.org/wiki/Hot_Standby It's not hugely detailed in every area, but it gives a flavour of the topics and issues related to them. Comments or questions welcome here, or I will discuss specific areas in more detail as I tackle those topics. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED
I'm trying to upgrade my copy of postgresql from 8.2.x to 8.3.4 on a Windows Vista SP1 laptop. I build postgres using mingw/msys and have had no issues with 8.1.x and 8.2.x. However, with 8.3.4 I run into problems. First, building fails: c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sspi.h:60: error: syntax error before SECURITY_STRING In file included from c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/security.h:39, from ../../../../src/include/libpq/libpq-be.h:50, This also happens from libpq-int.h. The solution in both cases is to add an additional header file: #ifdef ENABLE_SSPI #define SECURITY_WIN32 #include ntsecapi.h --- Add this include #include security.h #undef SECURITY_WIN32 That fixes the build issue. Second, once I've successfully built and installed postgres, I run into a bigger problem. When using initdb, I get this error: creating template1 database in c:/Data/postgres30/base/1 ... FATAL: could not create shared memory segment: 5 DETAIL: Failed system call was CreateFileMapping(size=1802240, name=Global\PostgreSQL:c:/Data/postgres). A bit of googling and reading MSDN docs shows that applications that don't run in Session 0 on Vista are not allowed to create shared memory in the Global namespace. Since initdb is invoked from the command line, it runs in Session 1. To get around this, you can give the user running and application the Create Global objects right using the Local Security Policy. Needless to say I did that without any luck. I then installed the pre-built binaries for Vista using the official windows installer. Calling initdb in the same way with the same user works. With 8.3.x the installer uses binaries built with VC 2005 instead of mingw - so clearly there are lots of differences. But I'm wondering if there is some difference in the way security is setup - maybe the addition of a manifest file to initdb that allows it to create global shared memory? I also assume it has to do with the way DACLs are setup, as described in this thread: http://archives.postgresql.org/pgsql-patches/2008-02/msg00074.php Or maybe its the way the executables are installed - I see that the installer makes SYSTEM their owner which of course doesn't happen with make install on MingW/msys. So I'm stumped - the same user running initdb built with VC++ works but running initdb with MingW fails. Any help much appreciated... Thanks, Charlie -- Charlie Savage http://cfis.savagexi.com smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] PostgreSQL future ideas
Gevik Babakhani wrote: Has there been any idea to port PG to a more modern programming language like C++? Of course there are some minor obstacles like a new OO design, this being a gigantic task to perform and rewriting almost everything etc... I am very interested to hear your opinion. Gevik, of course you're free to fork the project and try this yourself. I'd caution you that neither OO nor C++ are particularly modern (Stroustrup's objects-on-C work dates back to the 1970's). And that of the OO languages, C++ is one of the worst in terms of OO capabilities. If your theory favoring a modern language is thinking that this'll give you efficiencies (either in development time or runtime), you might consider Erlang instead. It's Functional and Concurrency and Fault Tolerance oriented features would IMHO be more useful for large reliable servers than anything C++ has to offer. -- 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] parallel pg_restore
On Tue, 23 Sep 2008 09:14:33 +0200 Stephen R. van den Berg [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: Andrew Dunstan wrote: There are in fact very few letters available, as we've been fairly profligate in our use of option letters in the pg_dump suite. j and m happen to be two of those that are available. --max-workers Perhaps, but please do not use that as justification for using -m. That would be equally silly as abbreviating number of workers to -n. Actually I came up with it because it coincides with existing terminology. Autovacuum has the concept of max_workers. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ -- 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] parallel pg_restore
On Tue, 23 Sep 2008 08:44:19 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote: j and m happen to be two of those that are available. I honestly don't have a terribly strong opinion about what it should be called. I can live with jobs or multi-threads. Perhaps we can use -j for jobs and -m for memory, so we can set memory available across all threads with a single total value. I can live with jobs or multi-threads also, whichever we decide. Neither one is confusing to explain. Memory? Where did that come from. Andrew is that in your spec? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ -- 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] parallel pg_restore
On Tue, 2008-09-23 at 12:43 -0700, Joshua Drake wrote: On Tue, 23 Sep 2008 08:44:19 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote: j and m happen to be two of those that are available. I honestly don't have a terribly strong opinion about what it should be called. I can live with jobs or multi-threads. Perhaps we can use -j for jobs and -m for memory, so we can set memory available across all threads with a single total value. I can live with jobs or multi-threads also, whichever we decide. Neither one is confusing to explain. Memory? Where did that come from. Andrew is that in your spec? No, but it's in mine. As I said upthread, no point in making it more parallel than memory allows. Different operations need more/less memory than others, so we must think about that also. We can quickly work out how big a table is, so we can work out how much memory it will need to perform sorts for index builds and thus how many parallel builds can sensibly take place. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL future ideas
Chris, This does not strike me as a particularly useful exercise. If I intended such a rewrite, I'd much rather consider using something *interestingly* different from C, like Erlang or Eiffel or Haskell. And if you were going to do *that*, you'd also rewrite the database to operate entirely in-memory over a cluster of anonymous servers. At which point the only thing left of PostgreSQL would be the parser. Hmmm, this is sounding familiar somehow ... -- --Josh Josh Berkus PostgreSQL San Francisco -- 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] EXEC_BACKEND
Simon Riggs wrote: On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We keep talking about EXEC_BACKEND mode, though until recently I had misunderstood what that meant. I also realised that I have more than once neglected to take it into account when writing a patch - one recent patch failed to do this. I can't find anything coherent in docs/readme/comments to explain why it exists and what its implications are. It exists because Windows doesn't have fork(), only the equivalent of fork-and-exec. Which means that no state variables will be inherited from the postmaster by its child processes, and any state that needs to be carried across has to be handled explicitly. You can define EXEC_BACKEND in a non-Windows build, for the purpose of testing code to see if it works in that environment. OK, if its that simple then I see why its not documented. Thanks. I thought there might be more to it than that. I added a little documentation at the top of postmaster.c::backend_forkexec(). -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/postmaster/postmaster.c === RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v retrieving revision 1.564 diff -c -c -r1.564 postmaster.c *** src/backend/postmaster/postmaster.c 23 Sep 2008 09:20:36 - 1.564 --- src/backend/postmaster/postmaster.c 23 Sep 2008 20:33:14 - *** *** 3286,3291 --- 3286,3295 /* * backend_forkexec -- fork/exec off a backend process * + * Some operating systems (WIN32) don't have fork() so we have to simulate + * it by storing parameters that need to be passed to the child and + * then create a new child process. + * * returns the pid of the fork/exec'd process, or -1 on failure */ static pid_t -- 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] EXEC_BACKEND
Bruce Momjian wrote: Simon Riggs wrote: On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We keep talking about EXEC_BACKEND mode, though until recently I had misunderstood what that meant. I also realised that I have more than once neglected to take it into account when writing a patch - one recent patch failed to do this. I can't find anything coherent in docs/readme/comments to explain why it exists and what its implications are. It exists because Windows doesn't have fork(), only the equivalent of fork-and-exec. Which means that no state variables will be inherited from the postmaster by its child processes, and any state that needs to be carried across has to be handled explicitly. You can define EXEC_BACKEND in a non-Windows build, for the purpose of testing code to see if it works in that environment. OK, if its that simple then I see why its not documented. Thanks. I thought there might be more to it than that. I added a little documentation at the top of postmaster.c::backend_forkexec(). Doesn't that make more sense in say, the Developer FAQ? //Magnus -- 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] EXEC_BACKEND
Magnus Hagander wrote: Bruce Momjian wrote: Simon Riggs wrote: On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We keep talking about EXEC_BACKEND mode, though until recently I had misunderstood what that meant. I also realised that I have more than once neglected to take it into account when writing a patch - one recent patch failed to do this. I can't find anything coherent in docs/readme/comments to explain why it exists and what its implications are. It exists because Windows doesn't have fork(), only the equivalent of fork-and-exec. Which means that no state variables will be inherited from the postmaster by its child processes, and any state that needs to be carried across has to be handled explicitly. You can define EXEC_BACKEND in a non-Windows build, for the purpose of testing code to see if it works in that environment. OK, if its that simple then I see why its not documented. Thanks. I thought there might be more to it than that. I added a little documentation at the top of postmaster.c::backend_forkexec(). Doesn't that make more sense in say, the Developer FAQ? I figured I should put it where it is used; the developer's FAQ is for more generalized issues, I feel. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] EXEC_BACKEND
On Tue, 2008-09-23 at 16:35 -0400, Bruce Momjian wrote: Simon Riggs wrote: I can't find anything coherent in docs/readme/comments to explain why it exists and what its implications are. It exists because Windows doesn't have fork(), only the equivalent of fork-and-exec. Which means that no state variables will be inherited from the postmaster by its child processes, and any state that needs to be carried across has to be handled explicitly. You can define EXEC_BACKEND in a non-Windows build, for the purpose of testing code to see if it works in that environment. OK, if its that simple then I see why its not documented. Thanks. I thought there might be more to it than that. I added a little documentation at the top of postmaster.c::backend_forkexec(). Thanks. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] parallel pg_restore
Simon Riggs wrote: On Tue, 2008-09-23 at 12:43 -0700, Joshua Drake wrote: On Tue, 23 Sep 2008 08:44:19 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote: j and m happen to be two of those that are available. I honestly don't have a terribly strong opinion about what it should be called. I can live with jobs or multi-threads. Perhaps we can use -j for jobs and -m for memory, so we can set memory available across all threads with a single total value. I can live with jobs or multi-threads also, whichever we decide. Neither one is confusing to explain. Memory? Where did that come from. Andrew is that in your spec? No, but it's in mine. As I said upthread, no point in making it more parallel than memory allows. Different operations need more/less memory than others, so we must think about that also. We can quickly work out how big a table is, so we can work out how much memory it will need to perform sorts for index builds and thus how many parallel builds can sensibly take place. If that ever happens it will certainly not be in this go round. In fact, we have some anecdotal evidence that the point of dimishing returns is not reached until a fairly high degree of parallelism is used (Joshua's and my client has been using 24 threads, I believe). In any case, my agenda goes something like this: * get it working with a basic selection algorithm on Unix (nearly done - keep your eyes open for a patch soon) * start testing * get it working on Windows * improve the selection algorithm * harden code If we get all that done by November we'll have done well. And we know that in some cases just this much can lead to reductions in restore time of the order of 80%. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel pg_restore
On Tue, 2008-09-23 at 16:50 -0400, Andrew Dunstan wrote: If we get all that done by November we'll have done well. And we know that in some cases just this much can lead to reductions in restore time of the order of 80%. Agreed. Go for it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_settings.sourcefile patch is a security breach
Magnus Hagander wrote: Tom Lane wrote: We go to some lengths to prevent non-superusers from examining data_directory and other values that would tell them exactly where the PG data directory is in the server's filesystem. The recently applied patch to expose full pathnames of GUC variables' source files blows a hole a mile wide in that. Possible answers: don't show the path, only the file name; or show sourcefile/sourceline as NULL to non-superusers. My vote goes for showing it as NULL to non-superusers. If we remove the path, that makes it pretty darn useless for admin tools - which was the main reason it was added in the first place.. And showing full path for superuser, just filename for non-superusers just seems to be way too ugly to consider :-) I've applied a patch that does this. //Magnus -- 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] 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED
Charlie Savage wrote: I'm trying to upgrade my copy of postgresql from 8.2.x to 8.3.4 on a Windows Vista SP1 laptop. I build postgres using mingw/msys and have had no issues with 8.1.x and 8.2.x. However, with 8.3.4 I run into problems. First, building fails: c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sspi.h:60: error: syntax error before SECURITY_STRING In file included from c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/security.h:39, from ../../../../src/include/libpq/libpq-be.h:50, This also happens from libpq-int.h. The solution in both cases is to add an additional header file: #ifdef ENABLE_SSPI #define SECURITY_WIN32 #include ntsecapi.h --- Add this include #include security.h #undef SECURITY_WIN32 That fixes the build issue. What the... This works fine on the mingw versions on the buildfarm :-( Have you done anything special to your mingw install? Second, once I've successfully built and installed postgres, I run into a bigger problem. When using initdb, I get this error: creating template1 database in c:/Data/postgres30/base/1 ... FATAL: could not create shared memory segment: 5 DETAIL: Failed system call was CreateFileMapping(size=1802240, name=Global\PostgreSQL:c:/Data/postgres). A bit of googling and reading MSDN docs shows that applications that don't run in Session 0 on Vista are not allowed to create shared memory in the Global namespace. Since initdb is invoked from the command line, it runs in Session 1. Where did you find that information? I've been specifically looking for it, but my searches didn't turn up anything conclusive. The latest versions contain a fix for the global namespace code. Dave noticed that this caused issues on vista and thus manually reverted the patch in the official binary installer. But since we haven't (hadn't) yet found documentation as to *why* it was failing, the patch has not yet been reverted in the main source tree. This is why it's working, probably, and it's not related to how it's built. If you want to revert the patch in your local tree, this is the one: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/win32_shmem.c?r1=1.4r2=1.5 Specifically, you can reintroduce the old bug (that I think is hat made it work on Vista) by removing the +18 in the lowest loop there. //Magnus -- 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] 0x1A in control file on Windows
Tom Lane wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found a bug that pg_controldata ends with error if control files contain 0x1A (Ctrl+Z) on Windows. We probably need to add PG_BINARY when we open control files because 0x1A is an end-of-file marker on Windows. Well, why is that a bug? If the platform is so silly as to define text files that way, who are we to argue? The problem is that our pg_controldata might have binary values that contain 0x1a that will be confused by the operating system as end-of-file. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Toasted table not deleted when no out of line columns left
On Mon, 2008-09-22 at 07:53 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I think the issue is identifying the problem. Reading the title of the post, I think Tom says no to *deleting* the toast table. He also says no to cleaning the table as part of DROP COLUMN. That still leaves you an opening for an out-of-line command/function to perform a clean, As i understood the initial post, the situation is even worse for TOAST tables than for ordinary tables - there is _NO_ way, except cluster or explicit (CREATE TABLE new AS SELECT + create indexes + drop old table + rename new to old) to clean up toast. For removing an inline column you can let a (update pk_id=pk_id limit 1000 ; vacuum) script run in background for a few weeks and get your space back. ... see CLUSTER ... regards, tom lane CLUSTER is something, you could use, if you had a mostly idle database and a lot of time. On real-life databases where this actually matters, you usually have neither. Hannu -- 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] Toasted table not deleted when no out of line columns left
On Sun, 2008-09-21 at 12:05 -0400, Tom Lane wrote: Zoltan Boszormenyi [EMAIL PROTECTED] writes: we came across a database where a table had a toasted table, keeping huge amounts of disk space allocated. However, the table's current definition didn't explain why there was a toasted table. Then upon some experiments, it struck me. There _was_ a toasted field but as the schema was modified, the fields was dropped, leaving only inline stored fields. VACUUM [FULL] [ANALYZE] didn't cleaned up the space that was used by the toasted table. My tests were done on 8.3.3. This is not a bug; it is operating as designed. Observe the statement in the NOTES section of the ALTER TABLE page: The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. And it seems that it is never reclaimed (instead of reclaimed over time as claimed in docs) if the column happens to have been toasted. ... and it goes on to point out how to force immediate space reclamation if you need that. These statements apply independently of whether any particular value is toasted or not. Are you sure ? how do you explain the above VACUUM [FULL] [ANALYZE] didn't cleaned up the space claim ? Is it just not true ? Or an overlooked corner case / implementation detail ? Hannu -- 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] Subtransaction commits and Hot Standby
On Thu, 2008-09-18 at 15:59 +0100, Simon Riggs wrote: On Tue, 2008-09-16 at 10:11 -0400, Alvaro Herrera wrote: I wonder if the improved clog API required to mark multiple transactions as committed at once would be also useful to TransactionIdCommitTree which is used in regular transaction commit. I've hacked together this concept patch (WIP). Not fully tested yet, but it gives a flavour of the API rearrangements required for atomic clog updates. It passes make check, but that's not saying enough for a serious review yet. I expect to pick this up again next week. I've tested this some more and am much happier with it now. Also added README details; there are no user interface or behaviour changes. The patch removes the need for RecordSubTransactionCommit() which * reduces response times of subtransaction queries because we are able to apply these changes in batches at commit time. This requires a batch-style API that now works atomically, so there is much change in transam.c * reduces the path length for visibility tests for all users viewing concurrent subtransaction activity since we are much less likely to waste time following a long trail to an uncommitted higher-level transaction * removes the need for additional WAL logging to implement subtransaction commits for Hot Standby So half the patch is refactoring, half rearranging of clog access functions to support batched-access. An early review would greatly help my work on Hot Standby. Thanks. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support Index: src/backend/access/transam/README === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/README,v retrieving revision 1.11 diff -c -r1.11 README *** src/backend/access/transam/README 21 Mar 2008 13:23:28 - 1.11 --- src/backend/access/transam/README 23 Sep 2008 21:23:02 - *** *** 342,351 an XID. A transaction can be in progress, committed, aborted, or sub-committed. This last state means that it's a subtransaction that's no longer running, but its parent has not updated its state yet (either it is ! still running, or the backend crashed without updating its status). A ! sub-committed transaction's status will be updated again to the final value as ! soon as the parent commits or aborts, or when the parent is detected to be ! aborted. Savepoints are implemented using subtransactions. A subtransaction is a transaction inside a transaction; its commit or abort status is not only --- 342,360 an XID. A transaction can be in progress, committed, aborted, or sub-committed. This last state means that it's a subtransaction that's no longer running, but its parent has not updated its state yet (either it is ! still running, or the backend crashed without updating its status). Prior ! to 8.4 we updated the status to sub-committed in clog as soon as ! sub-commit had happened. It was later realised that this is not actually ! required for any purpose and the action can be deferred until transaction ! commit. The main role of marking transactions as sub-committed is to ! provide an atomic commit protocol when transaction status is spread across ! multiple clog pages. As a result whenever transaction status spreads ! across multiple pages we must use a two-phase commit protocol. The first ! phase is to mark the subtransactions as sub-committed, then we mark the ! top level transaction and all its subtransactions committed (in that order). ! So in 8.4 sub-committed state still exists, but as a transitory state as ! part of final commit. Subtransaction abort is always marked in clog as ! soon as it occurs, to allow locks to be released. Savepoints are implemented using subtransactions. A subtransaction is a transaction inside a transaction; its commit or abort status is not only Index: src/backend/access/transam/clog.c === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/clog.c,v retrieving revision 1.47 diff -c -r1.47 clog.c *** src/backend/access/transam/clog.c 1 Aug 2008 13:16:08 - 1.47 --- src/backend/access/transam/clog.c 23 Sep 2008 20:41:17 - *** *** 80,89 static bool CLOGPagePrecedes(int page1, int page2); static void WriteZeroPageXlogRec(int pageno); static void WriteTruncateXlogRec(int pageno); ! ! ! /* ! * Record the final state of a transaction in the commit log. * * lsn must be the WAL location of the commit record when recording an async * commit. For a synchronous commit it can be InvalidXLogRecPtr, since the --- 80,105 static bool CLOGPagePrecedes(int page1, int page2); static void WriteZeroPageXlogRec(int pageno); static void WriteTruncateXlogRec(int pageno); ! static void TransactionIdSetPageStatus(TransactionId xid, int nsubxids, ! TransactionId *subxids,
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Bruce, I think the community's priorities are to add security at the SQL level, and then we can see clearly what SE-PostgreSQL requires. This has been discussed before so it should not come as a surprise. Well, I'm not that clear on exactly the SE implementation, but I spent a fair amount of time with Trusted Solaris and I can tell you that a multilevel security implementation would work in a different way from SQL row-level permissions. Multilevel frameworks have concepts of data hiding and data substitution based on labels. That is, if a user doesn't have permissions on data, he's not merely supposed to be denied access to it, he's not even supposed to know that the data exists. In extreme cases (think military / CIA use) data at a lower security level should be substitited for the higher security level data which the user isn't allowed. Silently. So it's quite possible that the SE and/or multilevel framework could remain parallel-but-different from SQL-level permissions, which would not include data hiding or data substitution. -- --Josh Josh Berkus PostgreSQL San Francisco -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Josh Berkus wrote: Bruce, I think the community's priorities are to add security at the SQL level, and then we can see clearly what SE-PostgreSQL requires. This has been discussed before so it should not come as a surprise. Well, I'm not that clear on exactly the SE implementation, but I spent a fair amount of time with Trusted Solaris and I can tell you that a multilevel security implementation would work in a different way from SQL row-level permissions. Multilevel frameworks have concepts of data hiding and data substitution based on labels. That is, if a user doesn't have permissions on data, he's not merely supposed to be denied access to it, he's not even supposed to know that the data exists. In extreme cases (think military / CIA use) data at a lower security level should be substitited for the higher security level data which the user isn't allowed. Silently. So it's quite possible that the SE and/or multilevel framework could remain parallel-but-different from SQL-level permissions, which would not include data hiding or data substitution. True, but think we would like to have all the SQL-level stuff done first, or at least decide we don't want it at the SQL level, before moving forward with adding fine-grained controls. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Bruce Momjian wrote: True, but think we would like to have all the SQL-level stuff done first, or at least decide we don't want it at the SQL level, before moving forward with adding fine-grained controls. This makes no sense. We've been sitting for years on the per-row privilege stuff, and there haven't been many takers. It doesn't look like somebody is going to write it for 8.4, which means delaying the inclusion of SE-Pgsql stuff just because that other thing is not done does not favor anyone. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED
Have you done anything special to your mingw install? The only thing different is that I upgraded to the latest mingw and msys packages a couple of weeks ago. The other thing I can think of is that I installed openssl and related packages (the official ones from the msys project). Would that force ENABLE_SSPI to be set or is it always set (and is it set in the build farm)? Second, once I've successfully built and installed postgres, I run into a bigger problem. When using initdb, I get this error: creating template1 database in c:/Data/postgres30/base/1 ... FATAL: could not create shared memory segment: 5 DETAIL: Failed system call was CreateFileMapping(size=1802240, name=Global\PostgreSQL:c:/Data/postgres). A bit of googling and reading MSDN docs shows that applications that don't run in Session 0 on Vista are not allowed to create shared memory in the Global namespace. Since initdb is invoked from the command line, it runs in Session 1. Where did you find that information? I've been specifically looking for it, but my searches didn't turn up anything conclusive. Yeah, information is scattered and came mostly through reading MSDN forum posts. It seems a number of people have been bitten by this change to createFileMapping in Vista. My searches: http://www.google.com/search?hl=enq=createfilemapping+access_denied+vistabtnG=Search http://www.google.com/search?hl=enq=createfilemapping+SeCreateGlobalPrivilege+btnG=Search In particular, the session 0 information is documented at: http://msdn.microsoft.com/en-us/library/aa366537(VS.85).aspx Quoting: The name can have a Global\ or Local\ prefix to explicitly create the object in the global or session namespace...creating a file mapping object in the global namespace from a session other than session zero requires the SeCreateGlobalPrivilege privilege. And more here: http://msdn.microsoft.com/en-us/library/aa480152.aspx#appcomp_topic12 And this blog post might be helpful: http://www.celceo.com/blogs/windows-insight/2007/09/global-createfilemapping-under.html Towards the bottom it says: Additionally, of course, we need to create everything with the appropriate security attributes. Null Dacls don't grant global access in Vista, so we need to create a real security descriptior and add an ACE with GENERIC_ALL privileges for the world SID. So - clearly initdb needs the SeCreateGlobalPrivilege permission. But I gave the user that runs initdb that permission using the Local Security Policy Editor, so something else is going on. My first guess was that the initdb code had to also request that permission also, but that theory seems unlikely to be correct since the official win32 binaries seem to work fine (I'm assuming they are the exact same code??) The latest versions contain a fix for the global namespace code. Dave noticed that this caused issues on vista and thus manually reverted the patch in the official binary installer. But since we haven't (hadn't) yet found documentation as to *why* it was failing, the patch has not yet been reverted in the main source tree. This is why it's working, probably, and it's not related to how it's built. Ah - so the trunk code is different than the binary release code? That would explain it. And in that case, then maybe the solution is setting up the security descriptor for calling createFileMapping? If you want to revert the patch in your local tree, this is the one: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/port/win32_shmem.c?r1=1.4r2=1.5 Specifically, you can reintroduce the old bug (that I think is hat made it work on Vista) by removing the +18 in the lowest loop there. Ok, I just reverted the whole patch (so all 4 changes), did a make clean ; make; make install. And no dice, I'm still having the same issue. Thanks, Charlie -- Charlie Savage http://cfis.savagexi.com smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Alvaro Herrera wrote: Bruce Momjian wrote: True, but think we would like to have all the SQL-level stuff done first, or at least decide we don't want it at the SQL level, before moving forward with adding fine-grained controls. This makes no sense. We've been sitting for years on the per-row privilege stuff, and there haven't been many takers. It doesn't look like somebody is going to write it for 8.4, which means delaying the inclusion of SE-Pgsql stuff just because that other thing is not done does not favor anyone. Well, does it make sense to add column-level privileges just for SE-Linux? I don't think that is wise. My logic is to build the lower levels first (SQL), then the higher levels. If that was done when the issue was originally suggested months ago it would be done but now. I don't see the rush to do things backwards just to get SE-Linux capability in 8.4, but of course that is just my opinion. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] parallel pg_restore
On Tue, 23 Sep 2008 16:50:43 -0400 Andrew Dunstan [EMAIL PROTECTED] wrote: Simon Riggs wrote: On Tue, 2008-09-23 at 12:43 -0700, Joshua Drake wrote: On Tue, 23 Sep 2008 08:44:19 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote: j and m happen to be two of those that are available. I honestly don't have a terribly strong opinion about what it should be called. I can live with jobs or multi-threads. Perhaps we can use -j for jobs and -m for memory, so we can set memory available across all threads with a single total value. I can live with jobs or multi-threads also, whichever we decide. Neither one is confusing to explain. Memory? Where did that come from. Andrew is that in your spec? No, but it's in mine. As I said upthread, no point in making it more parallel than memory allows. Different operations need more/less memory than others, so we must think about that also. We can quickly work out how big a table is, so we can work out how much memory it will need to perform sorts for index builds and thus how many parallel builds can sensibly take place. If that ever happens it will certainly not be in this go round. In fact, we have some anecdotal evidence that the point of dimishing returns is not reached until a fairly high degree of parallelism is used (Joshua's and my client has been using 24 threads, I believe). Against 8 cores but yes. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: True, but think we would like to have all the SQL-level stuff done first, or at least decide we don't want it at the SQL level, before moving forward with adding fine-grained controls. This makes no sense. We've been sitting for years on the per-row privilege stuff, and there haven't been many takers. It doesn't look like somebody is going to write it for 8.4, which means delaying the inclusion of SE-Pgsql stuff just because that other thing is not done does not favor anyone. Well, does it make sense to add column-level privileges just for SE-Linux? I don't think that is wise. My logic is to build the lower levels first (SQL), then the higher levels. If that was done when the issue was originally suggested months ago it would be done but now. I don't see the rush to do things backwards just to get SE-Linux capability in 8.4, but of course that is just my opinion. As I mentioned before, it is quite natural that different security mechanism *can* have different granualities, different decisions and so on. (No need to say, it *never* prevent they have same ones.) However, I can follow the direction of the community. If it is necessary to get merged SE-PostgreSQL feature in v8.4 cycle, I'll begin to design and implement the fine-grained-only feature sooon. In my hope, could you make progress reviewing SE-PostgreSQL feature during last half of the September and the October? It is necessary for load balancing of folks. Anyway, we have just only 35 days. If possible, I wanted to get such a funfamental suggestion more ealier. :( Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] Common Table Expressions (WITH RECURSIVE) patch
Attached is the result of a couple of days hacking on the WITH RECURSIVE patch. This moves us a lot closer to having sanity in the parsing phase, though I'm still quite unhappy with the second half of the processing in parse_cte.c. I added some infrastructure to make the first half's search for CTE references reasonably bulletproof, but the second half needs to be converted to use the same infrastructure, and I didn't do that yet because I didn't understand what it was doing. In particular, what the heck is the exception in findCteName that allows some other CTE's non_recursive_term to be stored into the non_recursive_term for the current one? That seems mighty broken. There are a number of unfinished corner cases (look for XXX in the patch) but they aren't in the way of further progress. The next big thing seems to be to figure out exactly how to do multiple references to CTE outputs, so that we can de-bogotify the planner. regards, tom lane bintORC4R6tx5.bin Description: cte-0923.patch.gz -- 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] 0x1A in control file on Windows
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Well, why is that a bug? If the platform is so silly as to define text files that way, who are we to argue? The problem is that our pg_controldata might have binary values that contain 0x1a that will be confused by the operating system as end-of-file. pg_controldata is certainly already being read as binary. The discussion here is about *text* files, particularly configuration files. Why should we not adhere to the platform standard about what a text file is? If you need a positive reason why this might be a bad idea, consider the idea that someone is examining postgresql.conf with a text editor that stops reading at control-Z. He might not be able to see items that the postmaster is treating as valid. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Toasted table not deleted when no out of line columns left
Hannu Krosing [EMAIL PROTECTED] writes: On Sun, 2008-09-21 at 12:05 -0400, Tom Lane wrote: The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. And it seems that it is never reclaimed (instead of reclaimed over time as claimed in docs) if the column happens to have been toasted. Utterly false. The toasted values will be deletable after their parent rows have been updated. This is exactly the same as for space in the parent row itself. how do you explain the above VACUUM [FULL] [ANALYZE] didn't cleaned up the space claim ? He didn't do any updates in the parent table. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_type.h regression?
Greg Sabino Mullane [EMAIL PROTECTED] writes: DefineType currently always sets the array's typdelim to ',', so I figured that the built-in types should match that. But we could easily change both of them to do the other. Thoughts? I'd slightly lean towards keeping it the way it has been (semicolon in both), since it doesn't matter to the backend, and who knows what else it may break. Well, the problem is that we'd *not* be keeping it the same for user-defined types. But the odds that that's really an issue are admittedly small. I thought of an argument for changing to array-uses-element's-typdelim, though. If we ever go over to a scheme where arrays are one-dimensional and you build up N-dimensional arrays using arrays of array objects (where the outer array treats the inner as a black box), then we would need the typdelims to match to preserve the current I/O behavior. I'm not really enamored of making such a change myself, but I seem to recall a couple of people advocating for it. So, if no objections I'll revert the change to _box's typdelim and change DefineType to copy the element typdelim to the array type. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 0x1A in control file on Windows
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Well, why is that a bug? If the platform is so silly as to define text files that way, who are we to argue? The problem is that our pg_controldata might have binary values that contain 0x1a that will be confused by the operating system as end-of-file. pg_controldata is certainly already being read as binary. Umm, no, it is in the backend I believe but not in the utilities. Hence the original bug report. We need to add the binary flag in pg_controldata.c and pg_resetxlog.c. The discussion here is about *text* files, particularly configuration files. Why should we not adhere to the platform standard about what a text file is? If you need a positive reason why this might be a bad idea, consider the idea that someone is examining postgresql.conf with a text editor that stops reading at control-Z. He might not be able to see items that the postmaster is treating as valid. Yes, exactly right. We certainly can't just open everything in binary mode. Magnus did say that all the current config files are opened in text mode as far as he could see. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 0x1A in control file on Windows
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: pg_controldata is certainly already being read as binary. Umm, no, it is in the backend I believe but not in the utilities. Hence the original bug report. We need to add the binary flag in pg_controldata.c and pg_resetxlog.c. Ah, okay, that's surely a bug. But I think the discussion here was about adding PG_BINARY to *all* open requests. That I don't like. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Updates of SE-PostgreSQL 8.4devel patches
I updated the series of patches for SE-PostgreSQL 8.4devel. [1/5] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1043.patch [2/5] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1043.patch [3/5] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1043.patch [4/5] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1043.patch [5/5] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1043.patch The newly added fifth patch contains the initial version of SE-PostgreSQL testcases, and sepostgresql-devel security policy got several new rules to invoke the test cases. In addition, I also fixed the following items. - bugfix: A case when we insert a tuple with FK refering invisible PK. - mispatch: The previous patch modified unrelated document files due to my misoperation when create patches. - rebase: I rebased toward to latest CVS HEAD. I hope to make progress reviewing process in parallel with the upcoming fine-grained security patch which is currently in designing. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] Updates of SE-PostgreSQL 8.4devel patches
The following proposal is idea which I have been considered for several days.
A design of PostgreSQL fine-grained security
* Target
This feature provide a row-level access control feature based on
database acl. Any tuple can have its access control list as table
having, and it is checked when the executor scan the tuple.
The violated tuples are filtered from the result set.
This feature does not provide a column-level access control feature,
because its effort is already in development, and it has SQL standards
to be refered.
But there is no standard for row-level security as far as I know,
so these features should be provided in separated.
* Security architecture
Its access control policy is based on database acl which is a kind of
discretional access control (DAC). It implicitly allows resource owner
or privileged users to change its access rights.
As an existing mechanism doing, privileged database roles can ignore
row-level access controls provided by this feature.
The resource owner of tuple should be a same as table owner, because
we have to massive number of pg_depend entries if individual tuple has
its owner. In addition, here is one more reason related to kinds of
permissions.
Three kind of permissions are provided for tuples. These are SELECT,
UPDATE and DELETE. The violated tuples are filtered out from the result
set of DML statement.
The INSERT permission is not provided, because an object does not exist
when the permission should be checked. All insertion of tuples are controled
by database acl of table. Since table owner is always same as tuple's one,
there is no administrative matter.
When we insert a tuple without any explicit acl, an empty acl is assigned.
It allows any kinds of accesses. Only owner can insert a tuple with explicit
acl.
* Implementation
This feature is implemented as a guest of PGACE security framework due to
the following two reasons.
The one is we don't have a standard for row-level security to be refered,
so it is more appropriate to be implemented as an enhanced security
mechanism.
The other is it provides several useful foundation to implement enhanced
security feature, like security system column support. We have to store
a database acl for each tuples which have characteristics massive objects
tend to share a small number of acls. The PGACE enables to represent it
with minimum cost.
The following image shows a concept of security system column.
kaigai=# SELECT pg_tuple_acl, * FROM drink;
++++---+
| pg_tuple_acl | id | name | price |
++++---+
| {kaigai=rwd/kaigai,=ar/kaigai} | 1 | coke | 130 |
| {} | 2 | juice | 150 |
| {kaigai=rwd/kaigai,=rw/kaigai} | 3 | coffee | 200 |
|: | : |: | : |
The security system column is writable. The owner can set per-tuple acl
with UPDATE or INSERT statement. The acl statement is a bit complicated,
so the following two functions helps to modify acl.
pg_tuple_acl_grant(text original, text role, text permissions)
pg_tuple_acl_revoke(text original, text role, text permissions)
For example:
UPDATE drink SET pg_tuple_acl = pg_tuple_acl_grant(pg_tuple_acl, 'bob',
'select,update');
WHERE id in (5,6,7);
One limitation is we can use this feature exclusively with SE-PostgreSQL.
But, I think user's requirements are different.
* Special cases
This feature does not allow to assign ACLs to tuples within system catalogs
to prevent inconsistency with existing access control mechanism.
When a user tries to insert a tuple with duplicate PK, it is failed
independent from its visibility.
When a user tries to insert a new tuple with FK, the refered PK have to
be visible for the owner of refered table, because FK triggers are invoked
with owner's identifier. In similar case, when a user tries to update or
delete a tuple with PK, the owner of refering table have to be able to
perform pre-defined action (like SET NULL).
* Backup/Restore
I'll add '--enable-tuple-acl' option to pg_dump/pg_dumpall.
It enables to dump tables with defined acls, and they can be restored via
writable security system column.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei [EMAIL PROTECTED]
--
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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: True, but think we would like to have all the SQL-level stuff done first, or at least decide we don't want it at the SQL level, before moving forward with adding fine-grained controls. This makes no sense. We've been sitting for years on the per-row privilege stuff, and there haven't been many takers. It doesn't look like somebody is going to write it for 8.4, which means delaying the inclusion of SE-Pgsql stuff just because that other thing is not done does not favor anyone. Well, does it make sense to add column-level privileges just for SE-Linux? That's the wrong question. The question here is: does it make sense to have per-row permissions implemented on top of an abstraction layer whose sole current implementation is SE-Linux? I think the answer is yes, because (as others have said) if we ever want to have SQL-level per-row permissions, then we can implement them with no change to the patch currently in discussion. (Note that it has been said that this abstraction layer could easily be ported to work on TrustedSolaris, and probably other OS-level security mechs) I don't think that is wise. My logic is to build the lower levels first (SQL), then the higher levels. Why are you saying that SQL is the lower level? I don't think there's a lower and upper layer here. Neither can be built on top of the other one, because they are orthogonal. If that was done when the issue was originally suggested months ago it would be done but now. I don't see the rush to do things backwards just to get SE-Linux capability in 8.4, but of course that is just my opinion. :-) My opinion here is that doing it is not backwards. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Josh Berkus [EMAIL PROTECTED] writes: Multilevel frameworks have concepts of data hiding and data substitution based on labels. That is, if a user doesn't have permissions on data, he's not merely supposed to be denied access to it, he's not even supposed to know that the data exists. In extreme cases (think military / CIA use) data at a lower security level should be substitited for the higher security level data which the user isn't allowed. Silently. Yeah, that's what I keep hearing that the spooks think they want. I can't imagine how it would play nice with SQL-standard integrity constraints. Data that apparently violates a foreign-key constraint, for example, would give someone a pretty good clue that there's something there he's not being allowed to see. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Well, does it make sense to add column-level privileges just for SE-Linux? That's the wrong question. The question here is: does it make sense to have per-row permissions implemented on top of an abstraction layer whose sole current implementation is SE-Linux? Er, Bruce was asking about per-column, not per-row. There's a patch listed on CommitFest:2008-09 to introduce per-column permissions, but it's apparently still WIP. How much does that overlap/conflict with these patches? I think the answer is yes, because (as others have said) if we ever want to have SQL-level per-row permissions, then we can implement them with no change to the patch currently in discussion. If that's true, it weighs somewhat in favor of accepting this patch, but how sure are we that it's really the case? If you only have one implementation sitting on top of your abstraction layer, it's hard to know whether you've implemented a general framework for doing X or merely an interface that happens to suit the particular flavor of X that you want to do today. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL future ideas
Robert Haas wrote: C isn't going anywhere anytime soon. Look at its history, it has survived its 'replacements' over and over again. The most popular kernels, shells and applications are all still written in C (new and old). Where are the warning signs that it is dwindling? To add to this: It's easy to underestimate the effect that writing in almost anything else has on performance. I once had a job working on a research operating system written in C++. It was about 10x slower than whichever flavor of BSD we were using at the time. There were a lot of reasons for this, but I remember that overuse of heavy-weight template classes was definitely one of them (not to mention a huge source of code bloat). Ripping that logic out and replacing it with something more, erm, C-like paid huge dividends. There's no problem with using a higher-level language for your application programming - I do almost all of my coding these days in Perl or, as it happens, PL/pgsql. But you really don't want that programming language to itself be written in another high-level language. Core system components like your kernel and database and compiler need to be fast, and it's pretty hard to get that in anything other than C. You could probably make C++ do the job passably well, but only if you avoid using some of the more inefficient language features... in other words, only if you make it look as much like C as possible. Agreed. If we went with C++ we would need to be able to turn _off_ some C++ features to keep performance reasonable. I can see trying to use a _few_ C++ features, but in general it isn't worth the effort. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Robert Haas wrote: I think the answer is yes, because (as others have said) if we ever want to have SQL-level per-row permissions, then we can implement them with no change to the patch currently in discussion. If that's true, it weighs somewhat in favor of accepting this patch, but how sure are we that it's really the case? If you only have one implementation sitting on top of your abstraction layer, it's hard to know whether you've implemented a general framework for doing X or merely an interface that happens to suit the particular flavor of X that you want to do today. Yes, that is my point, and SE-Linux is just Linux, meaning it is OS-specific, making it even less generally useful. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] stored procedure
Hey does anybody know how to call stored procedure written in pgsql using hibernate concecpt with java application... I wanted to know how mapping takes place. Please could anybody help me out
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Multilevel frameworks have concepts of data hiding and data substitution based on labels. That is, if a user doesn't have permissions on data, he's not merely supposed to be denied access to it, he's not even supposed to know that the data exists. In extreme cases (think military / CIA use) data at a lower security level should be substitited for the higher security level data which the user isn't allowed. Silently. Yeah, that's what I keep hearing that the spooks think they want. I can't imagine how it would play nice with SQL-standard integrity constraints. Data that apparently violates a foreign-key constraint, for example, would give someone a pretty good clue that there's something there he's not being allowed to see. Please note that SE-PostgreSQL does not adopt following technology because of its complexity. When user tries to update a PK refered by invisible FK, it generate an error. Thus, it is theoretically possible to estimate the invisible PKs by attacks with repeating. In extream case, a technology called as polyinstantiation is used. http://en.wikipedia.org/wiki/Polyinstantiation It allows several tuples with different security level to have same primary key. When a higher-level user updates a tuple with lower security level, DBMS makes a new tuple with higher-level and the original one is kept unchanged. It does not prevent to leak a infomation belonging with higher security level. IIRC, FK has to refer a PK with same or lower security level to keep consistency of its visibility in polyinstantiated tables. If a lower level user modifies a PK with in same level, DBMS makes a copy of PK with higher-level. This operating does not affect higher FKs, but FK integrities are kept. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] stored procedure
On Tue, Sep 23, 2008 at 11:53 PM, chetan N [EMAIL PROTECTED] wrote: Hey does anybody know how to call stored procedure written in pgsql using hibernate concecpt with java application... I wanted to know how mapping takes place. Please could anybody help me out This is the wrong mailing list. -hackers is reserved for issues dealing with postgresql development. Postgres doesn't have stored procedures. It has functions. You invoke them like this: select func(); select * from func(); merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Yeah, that's what I keep hearing that the spooks think they want. I can't imagine how it would play nice with SQL-standard integrity constraints. Data that apparently violates a foreign-key constraint, for example, would give someone a pretty good clue that there's something there he's not being allowed to see. Right, so you don't let that happen. If you're giving Mr. X access to the cities table, and decide to restrict him only to cities in Europe, then if you give him access to the informants table, you'll probably restrict that to only informants located in cities that are in Europe, so, no problem. It's easy to come up with cases where there is a problem but just because there can be problems doesn't mean the technology isn't basically useful. I don't think there's much point in second-guessing the NSA: they are smart and have thought about this more than we have. But I do think it's worthwhile to ask whether it makes sense to introduce a bunch of features that are only usable to people running SELinux. Column-level permissions are the best example of this: it's very easy to imagine people wanting that feature, but NOT being willing to run SELinux to get it. It's more arguable whether data hiding falls into the same category or not, because if you're doing data hiding then arguably you're a security nut and more likely to be running (or willing to run) SELinux. Against that, my boss made me do data hiding but we have no interest in SELinux, so that's one data point the other way, though not one I'd take all that seriously. So far there has been no detailed discussion of any of the new security features that are in this patch (column-level security, row-level security, large object security, etc). For each of those features, we need to answer the following questions: 1. Do we want this feature as a part of PostgreSQL at all? 2. If #1 is yes, do we eventually want to expose this feature via a SQL interface, or some other interface substantially unlike SE-PostgreSQL? 3. If #2 is yes, will accepting this patch get us closer to that goal or further away from it? I suspect that for most of the features the answer for #1 will be yes, but the other two questions need some more careful examination. ...Robert -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Robert Haas [EMAIL PROTECTED] writes: That's the wrong question. The question here is: does it make sense to have per-row permissions implemented on top of an abstraction layer whose sole current implementation is SE-Linux? Er, Bruce was asking about per-column, not per-row. There's a patch listed on CommitFest:2008-09 to introduce per-column permissions, but it's apparently still WIP. How much does that overlap/conflict with these patches? Yeah, Stephen Frost is working on that and still has a ways to go. I think he might get it done in time for 8.4 (ie, in time for the November commitfest) but it's far from certain. Per-column permissions are part of the SQL standard, and so I think we have to implement that without depending on any OS-specific infrastructure. So on that end I agree with Bruce's position that we should do the SQL version first and then think about extensions for SELinux. Per-row is not in the spec and so we can design that as we please. But as I mentioned a moment ago, I don't see how it can possibly play nice with foreign keys ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby Design
On Tuesday 23 September 2008 14:15:34 Simon Riggs wrote:
Hot Standby design has been growing on the PostgreSQL Wiki for some
weeks now.
I've updated the design to reflect all feedback received so far on
various topics.
http://wiki.postgresql.org/wiki/Hot_Standby
It's not hugely detailed in every area, but it gives a flavour of the
topics and issues related to them.
Comments or questions welcome here, or I will discuss specific areas in
more detail as I tackle those topics.
very nice work. very in depth. unfortunatly, this means it is long and the
hour is late... so here are some scattered thoughts i had while reading it :
* However, some WAL redo actions will be for DDL actions. These DDL actions
are repeating actions that have already committed on the primary node, so
they must not fail on the standby node. These DDL locks take priority and
will automatically cancel any read-only transactions that get in their way.
Some people will want the option to stack-up ddl transactions behind
long-running queries (one of the main use cases of a hot slave is reporting
stye and other long running queries)
* Actions not allowed on Standby are:
DML - Insert, Update, Delete, COPY FROM, Truncate
copy from suprised me a bit, since it is something i could see people wanting
to do... did you mean COPY TO in this case?
* Statspack functions should work OK, so tools such as pgAdminIII should work.
pgAgent will not.
I presume this means the backend kill function would work? Also, can you go
into why pgAgent would not work? (I presume it's because you can't update
information that needs to be changed when jobs run, if thats the case it
might be worth thinking about making pgAgent work across different clusters)
* Looking for suggestions about what monitoring capability will be required.
one place to start might be to think about which checks in check_nagios might
still apply. Possibly also looking to systems like slony for some
guidence... for example everyone will want some way to check how far the lag
is on a stnadby machine.
* The following commands will not be accepted during recovery mode
GRANT, REVOKE, REASSIGN
How is user management done on a standby? can you have users that dont exist
on the primary (it would seem not).
... more to come i'm sure, but fading out... thanks again for the work so far
Simon.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Bruce Momjian wrote: Robert Haas wrote: I think the answer is yes, because (as others have said) if we ever want to have SQL-level per-row permissions, then we can implement them with no change to the patch currently in discussion. If that's true, it weighs somewhat in favor of accepting this patch, but how sure are we that it's really the case? If you only have one implementation sitting on top of your abstraction layer, it's hard to know whether you've implemented a general framework for doing X or merely an interface that happens to suit the particular flavor of X that you want to do today. Yes, that is my point, and SE-Linux is just Linux, meaning it is OS-specific, making it even less generally useful. I believe the upcomig fine-grained security patch enables to make clear the security framework is NOT specific for SELinux only. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Robert Haas [EMAIL PROTECTED] writes: I don't think there's much point in second-guessing the NSA: they are smart and have thought about this more than we have. No doubt, but have they told us what we'd need to know to make a non-broken implementation? I haven't seen anything about how a SQL database ought to work to play nice with SELinux or similar controls. I don't doubt that somebody inside Fort Meade has a good design for this, but I have no confidence that we do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)
Tom Lane wrote: Robert Haas [EMAIL PROTECTED] writes: That's the wrong question. The question here is: does it make sense to have per-row permissions implemented on top of an abstraction layer whose sole current implementation is SE-Linux? Er, Bruce was asking about per-column, not per-row. There's a patch listed on CommitFest:2008-09 to introduce per-column permissions, but it's apparently still WIP. How much does that overlap/conflict with these patches? Yeah, Stephen Frost is working on that and still has a ways to go. I think he might get it done in time for 8.4 (ie, in time for the November commitfest) but it's far from certain. Per-column permissions are part of the SQL standard, and so I think we have to implement that without depending on any OS-specific infrastructure. Yes, I agree this position. The OS-specific infrastructure works orthogonally with native SQL standard access controls, as DAC/MAC works orthogonally on operating system. So on that end I agree with Bruce's position that we should do the SQL version first and then think about extensions for SELinux. A proposal of fine-grained security without OS is here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01528.php I'll pay my effort to submit a series of patches due to end of the Oct. Per-row is not in the spec and so we can design that as we please. But as I mentioned a moment ago, I don't see how it can possibly play nice with foreign keys ... As I noted in above message, it handles PK/FK constraints as follows: - When a user tries to insert/update a tuple with duplicate PK, it is failed independent from its visibility. - When a user tries to insert/update a tuple with FK, the refered PK have to be visible. - When a user tries to update/delete a tuple with PK which is refered by invisible FK, it is failed independent from its visibility. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] parallel pg_restore
Simon Riggs wrote: On Tue, 2008-09-23 at 16:50 -0400, Andrew Dunstan wrote: If we get all that done by November we'll have done well. And we know that in some cases just this much can lead to reductions in restore time of the order of 80%. Agreed. Go for it. Just as an FYI, by far the number one bottle neck on the multiple work restores I was doing was CPU. RAM and IO were never the problem. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Magnus Hagander wrote: exec(locale -a) ... I suppose we'd need something else for Windows, but I'm sure there's a way. IIRC, the data is in the registry. Should be enumerable somehow - we'll have to do it platform specific of course, but it's not the first time we'd do that for windows... There is EnumSystemLocales API function in Windows. -- Regards Petr Jelinek (PJMODOS) -- 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] Common Table Expressions (WITH RECURSIVE) patch
I am re-sending this message to -hackers from yesterday, because the first time it didn't appear to make it through. This time I gzipped the patch. This is just for the archives (and to give context to the replies), and this message is superseded by Tom's patch here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01521.php On Thu, 2008-09-18 at 12:55 +0900, Tatsuo Ishii wrote: Tom, thanks for the review. Here is an in-progress report. Patches against CVS HEAD attached. (uncommented items are work-in-progress). Here is a patch that is an initial attempt to reorganize the parse tree representation. The goal of this patch is to separate the RTEs from the CTEs, so that we can, for example, have multiple RTEs refer to the same CTE. This will hopefully allow us to materialize a volatile query once, and have several RTEs refer to that same value, which will meet the SQL standard. Notes: * It makes a p_cte_table in the ParseState, which is a list of CteTblEntries. This replaces p_ctenamespace, which was a list of RangeSubselects. * It copies the p_cte_table into Query.cte_table * I introduced a new type of RTE, RTE_CTE, which holds a cte_index and cte_levelsup. This is used to find the CTE that the RTE references. Weak points: * It does not change the behavior of recursive queries. That is a little more complicated, so I wanted to wait for feedback on my patch so far. * I don't understand set_subquery_pathlist, or that general area of the code. I made a new set_cte_pathlist, that is basically the same thing, except I used a hack dummy_subquery variable in the RTE to pass along a pointer to the subquery of the CTE. I think this dummy variable can be removed, but I just don't understand that part of the code well enough to know what should happen. And if it does require a subquery at that point, I'll need to find a way of locating the right cte_table from inside that function. Any advice here would be appreciated. * There are a couple of other rough points in places where it's hard to traverse up the parse tree or query tree. I can probably work around these weak points, but I wanted to send the patch to avoid a lot of conflicts or problems later. Tell me whether you think this is moving in the right direction. Regards, Jeff Davis cte_fixparse.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch: Collation support
Petr Jelinek wrote: Magnus Hagander wrote: exec(locale -a) ... I suppose we'd need something else for Windows, but I'm sure there's a way. IIRC, the data is in the registry. Should be enumerable somehow - we'll have to do it platform specific of course, but it's not the first time we'd do that for windows... There is EnumSystemLocales API function in Windows. Ha, right. We even use it in the installer :-) Bottom line remains: we can easily do this in a Windows-specific way if we need to. //Magnus -- 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] 0x1A in control file on Windows
Andrew Dunstan wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Well, why is that a bug? If the platform is so silly as to define text files that way, who are we to argue? The problem is that our pg_controldata might have binary values that contain 0x1a that will be confused by the operating system as end-of-file. pg_controldata is certainly already being read as binary. Umm, no, it is in the backend I believe but not in the utilities. Hence the original bug report. We need to add the binary flag in pg_controldata.c and pg_resetxlog.c. Right. I'll go ahead and put that part in (I find two locations - the one in the original patch, and the extra one Heikki noticed). The discussion here is about *text* files, particularly configuration files. Why should we not adhere to the platform standard about what a text file is? If you need a positive reason why this might be a bad idea, consider the idea that someone is examining postgresql.conf with a text editor that stops reading at control-Z. He might not be able to see items that the postmaster is treating as valid. Yes, exactly right. We certainly can't just open everything in binary mode. Magnus did say that all the current config files are opened in text mode as far as he could see. The point being that the config files are opened with AllocateFile(), which in turn calls fopen(). It doesn't use open(). The proposal was only to make all *open()* calls do it binary. I was under the impression that on Unix, that's what open() did, so we should behave the same? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
