Re: [HACKERS] [GENERAL] PG vs MySQL (fwd)
He brings up two good points here ... first one being, where exactly, in the docs, do we mention getting the OID in either pg_database, or pg_class, to determine a directory, or file name? I just checked the pg_database catalog page, and it doesn't ... Second point, of course being ... how do you find a database if the server isn't running? Could we maybe have a file in each directory similar to PG_VERSION calld PG_DATABASE that just contains the name of the database, that you could grep through for the database? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Forwarded message -- Date: Sun, 28 Mar 2004 22:37:41 -0600 (CST) From: Mike Nolan [EMAIL PROTECTED] To: Marc G. Fournier [EMAIL PROTECTED] Cc: Alex [EMAIL PROTECTED], Frank Finner [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [GENERAL] PG vs MySQL Perhaps, but it isn't obvious which directory has which database. I'm not not sure which system catalogs provide that information, something that wasn't obvious from the online docs, either. SELECT oid FROM pg_database WHERE datname = 'database'; Thanks. That should be easier to find in the documentation, perhaps it should be mentioned in the docs for the pg_database system catalog. From an ISP's or DBA's point of view, it would be preferable if there was a way to determine which directory held which database without having to actually log into the database. I can envision circumstances under which postmaster might not be running when that information is needed. -- Mike Nolan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_advisor schema proof of concept
Hello Andreas, No problem, as long as referencing data is contained in the advice tables (i.e. referencing the 'offending' object), not just text so the advice can be shown as attribute of each object. What do you mean by 'referencing data'? Things like oid attributes referencing pg_class or pg_constraint or pg_index? -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] GIST code doesn't build on strict 64-bit machines
Tom Lane wrote:
I've just found out the hard way that Postgres doesn't even build on
recent gcc releases for 64-bit HPPA. The reason is that the compiler
now notices and complains about alignment errors that will lead to
core dump at runtime, and GIST has got some. The particular code that
fails to compile is in gist.c:
gistentryinit(((GISTENTRY *) VARDATA(evec))[1],
((GISTENTRY *) VARDATA(evec))[0].key, r, NULL,
(OffsetNumber) 0, ((GISTENTRY *)
VARDATA(evec))[0].bytes, FALSE);
Since VARDATA() is at a 4-byte offset from the start of the datum, this
is trying to overlay a GISTENTRY struct at a 4-byte boundary. When
compiling in 64-bit mode, Datum is 8 bytes, and so the GISTENTRY struct
is not sufficiently well aligned. Unlike Intel machines, the HP chips
*require* 8-byte loads and stores to be 8-byte-aligned.
Hm.
evec is defined as
storage = (char *) palloc(n * sizeof(GISTENTRY) + MAXALIGN(VARHDRSZ));
evec = (bytea *) (storage + MAXALIGN(VARHDRSZ) - VARHDRSZ);
VARDATA is defined as:
#define VARDATA(__PTR) VARATT_DATA(__PTR)
#define VARATT_DATA(PTR) (((varattrib *)(PTR))-va_content.va_data)
and VARHDRSZ is
#define VARHDRSZ((int32) sizeof(int32))
Look follow:
VARATT_SIZEP(evec) = 2 * sizeof(GISTENTRY) + VARHDRSZ;
#define VARATT_SIZEP(_PTR) (((varattrib *)(_PTR))-va_header)
So, if ((varattrib *)evec)-va_content.va_data - (char*)evec == 4 then
((GISTENTRY *) VARDATA(evec))[i] is 8-byte aligned, but evec - no.
But if ((varattrib *)evec)-va_content.va_data - (char*)evec == 8 then
both evec and ((GISTENTRY *) VARDATA(evec))[i] isn't 8-byte aligned.
I don't afraid to say some rubbish :)
I wrote simple test:
#include stdio.h
#include c.h
typedef struct {
int32 len;
chardata[1];
} TST;
int main(int argn, char *argv[]) {
TST t;
TST *ptr = t;
printf(%d\n, (ptr-data - (char*)ptr));
return 0;
}
It prints 4 for my Intel systems and for Alpha system, but I havn't access to
HPUX. If result is equal to 8 on HPUX, then I suppose that replacing to
evec = (bytea *) storage
will resolve our problem (but VARHDRSZ has inconsistent definition).
But if result is 4 then we should use
evec = (bytea *) storage
and replace all VARDATA macro to something like
#define MY_VARDATA(PTR) ( ((char*)PTR) + MAXALIGN(VARHDRSZ) )
But all of this is strage for me, because we already faced to problem with
8-bytes strict aliasing in GiST code, and we had resolved problem on Sun and
Alpha boxes. What was it changed?
I suppose that a correct fix involves doing MAXALIGN(VARDATA(evec)),
but I do not know what places need to change to support this.
Its only union and picksplit user-defined methods in contrib modules.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Fuzzy cost comparison to eliminate redundant planning
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Right. There are potentially some ranges of LIMIT for which it could win, I believe. What if we take the total cost and divide it by the number of rows returned --- then we have a per-row cost for each plan. Then we subtract the two, and that difference compared to the difference in startup costs tell us how many rows could potentially use this plan. Here, plan B loses everywhere: to A at lower percentages and to C at higher ones. But I don't see how to eliminate B on the basis of one-at-a-time comparisons. It seems like getting rid of B would require turning add_path into an O(N^3) instead of O(N^2) algorithm ... which would almost certainly eat more cycles than it'd save. Nice charts. :-) I agree we don't want anything that is O(high), but I was thinking of something that would be more agressive than 1%, which works well for lots of self joins, but I am not sure how well for other cases. Consider these plans that return 10 rows: total startup total per row retrieved plan1 1 1 .1 plan2 5 0.5 .5 plan3 10 0 1 Now, the difference between plan1 and plan2 total is 500%, yet it is a useless plan. If you want to retrieve one row, you pick plan3, if you want 2 or more rows, you pick plan1. If the per-row total cost plus the startup cost is less than another's, we can throw it away. In fact, when we go check for cheapest startup cost to keep, do we at least assume we have one row fetched? What if instead of doing total cost 1% difference, we compute total-per-row + startup being 1% different? Does that catch more similar cost plans? Seems it would. In your example, how many rows were returned? I can see how this would have handled that case. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Row sampling..
Hi All, I'm trying to gain a good understanding of how PostgreSQL determines what to sample when doing a stats analysis on a table. Using PostgreSQL 7.4's pg_stats table I can get a good overall understanding of variations in the table, but I need to know how PostgreSQL makes it's choices on what rows to sample. The other thing I also noted, is that I can change the stats of a column to be as high as 1000, but PostgreSQL still may not sample all 1000 elements.. can someone help me gain a good understanding of that area of Postgres so that I can make better choices on optimizing? -- Chris Bowlby [EMAIL PROTECTED] PostgreSQL Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GIST code doesn't build on strict 64-bit machines
Teodor Sigaev [EMAIL PROTECTED] writes: But all of this is strage for me, because we already faced to problem with 8-bytes strict aliasing in GiST code, and we had resolved problem on Sun and Alpha boxes. What was it changed? It looks to me like the HP compiler is expecting that the constant offset part of a doubleword load or store instruction should be a multiple of 8. The offset-the-evec hack you're using falls foul of that even though the ultimate runtime address would be legal. I'm not sure whether this is a constraint of the actual HPPA instruction format, or just overly anal compile-time testing. gcc doesn't seem to have a problem, but it's quite possibly not generating the most efficient instruction sequence, either. I suppose that a correct fix involves doing MAXALIGN(VARDATA(evec)), but I do not know what places need to change to support this. Its only union and picksplit user-defined methods in contrib modules. If I recall correctly, we decided to go with the present hack because we found the problem just before a release date and there wasn't time to do it more cleanly. It seems to me that there is time to fix it right for 7.5 ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Fuzzy cost comparison to eliminate redundant planning
Bruce Momjian [EMAIL PROTECTED] writes: I agree we don't want anything that is O(high), but I was thinking of something that would be more agressive than 1%, which works well for lots of self joins, but I am not sure how well for other cases. That assumption is without foundation. The particular case we are looking at in Eric's example has a problem only because there is one cpu_operator_cost more or less in the estimated startup costs. I believe that the problem was actually created recently (7.4 or possibly 7.3) by planner changes that account for expression evaluation costs more completely than we used to do. This is important when an expression involves an expensive sub-select, but for typical cases it simply results in very small deltas between startup costs of otherwise-similar plans. 1% fuzz is plenty to fix that. Before asserting that we need more flexibility, please point to some real cases where it's needed. Your argument depends on numbers pulled out of the air that don't necessarily have anything to do with the planner's actual behavior. What if instead of doing total cost 1% difference, we compute total-per-row + startup being 1% different? Doesn't seem to me to have useful properties... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] pg_dump 7.4 bug
If you do this sequence of events, you get a failure to restore: 1. As superuser, do this: test2=# CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler test2-# AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2-# LANGUAGE c; CREATE FUNCTION 2. Drop privs. test2=# alter user chriskl with nocreateuser; So, now we're a regular joe user. 3. pg_dump now gives this: SET SESSION AUTHORIZATION 'chriskl'; SET search_path = public, pg_catalog; -- -- TOC entry 37 (OID 853309) -- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: public; Owner: chriskl -- CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '$libdir/plpgsql.so', 'plpgsql_call_handler' LANGUAGE c; 4. Now, trying to restore this as the joe user gives: test2= CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler test2- AS '$libdir/plpgsql.so', 'plpgsql_call_handler' test2- LANGUAGE c; ERROR: permission denied for language c This caused me pain in the 7.4 upgrade I just performed... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Row sampling..
Chris Bowlby [EMAIL PROTECTED] writes: I need to know how PostgreSQL makes it's choices on what rows to sample. Randomly. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump 7.4 bug
Christopher Kings-Lynne [EMAIL PROTECTED] writes: If you do this sequence of events, you get a failure to restore: This is not a pg_dump bug. Possibly ALTER USER should refuse to drop someone's superuserness if there is content in the database that depends on his superuserness, but I don't see how to enforce that. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] GIST code doesn't build on strict 64-bit machines
I suppose that a correct fix involves doing MAXALIGN(VARDATA(evec)),
but I do not know what places need to change to support this.
Its only union and picksplit user-defined methods in contrib modules.
If I recall correctly, we decided to go with the present hack because we
found the problem just before a release date and there wasn't time to do
it more cleanly. It seems to me that there is time to fix it right for
7.5 ...
Yes, you are right.
I suggest to replace bytea by struct
typedef struct {
int32 n; /* number of GISTENTRY */
GISTENTRY vector[1];
} GistEntryVector;
#define GEVHDRSZ (MAXALIGN(sizeof(int32))
so, allocation will be:
evec = palloc( GEVHDRSZ + sizeof(GISTENTRY)*n );
MAXALIGN guarantee that allocated memory will be no less than required (it may
be greater for 4 bytes).
And change interface to user defined structures from
Datum union(bytea *entryvec, int *size)
Datum picksplit(bytea *entryvec, GIST_SPLITVEC *v)
to
Datum union(GistEntryVector *entryvec, int *size)
Datum picksplit(GistEntryVector *entryvec, GIST_SPLITVEC *v)
In this function it's need to use entryvec-n and entryvec-vector
We can do even
Datum union(int32 n, GISTENTRY *entryvec, int *size)
Datum picksplit(int32 n, GISTENTRY *entryvec, GIST_SPLITVEC *v)
It seems to me that first case is clearer. Of course, I change all contrib
modules to new interface.
What do you think?
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] GIST code doesn't build on strict 64-bit machines
Teodor Sigaev [EMAIL PROTECTED] writes:
I suggest to replace bytea by struct
typedef struct {
int32 n; /* number of GISTENTRY */
GISTENTRY vector[1];
} GistEntryVector;
Yes, I was thinking the same thing.
#define GEVHDRSZ (MAXALIGN(sizeof(int32))
so, allocation will be:
evec = palloc( GEVHDRSZ + sizeof(GISTENTRY)*n );
MAXALIGN guarantee that allocated memory will be no less than required (it may
be greater for 4 bytes).
That would work, or you could use offsetof(GistEntryVector, vector[0]).
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL block size vs. LVM2 stripe width
Hi Manfred, On 27 Mar, Manfred Koizar wrote: Mark, how often did you run your tests? Are the results reproduceable? In this case, I've only done 1 per each combination. I've found the results for this test to be reproduceable. On Fri, 26 Mar 2004 14:00:01 -0800 (PST), [EMAIL PROTECTED] wrote: Linux-2.6.3, LVM2 Stripe Width (going across) PostgreSQL BLCKSZ (going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB261726562652266426672642 4 KB439344864577455745114448 8 KB433744234471457641113642 16 KB 441244954532453629852312 32 KB 370537843886392529362362 Unless someone can present at least an idea of a theory why a BLCKSZ of 8 KB is at a local minimum (1 or 2% below the neighbouring values) for stripe widths up to 64 KB I'm not sure whether we can trust these numbers. Before I hit the send button, I did a quick check of the link you provided. The links in the table contain the following test numbers: 16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB 72 71 70 69 66 65 4 KB 64 63 62 61 60 58 8 KB 54 53 52 51 50 49 16 KB79 78 77 76 75 74 32 KB86 85 84 83 82 80 Does this mean that you first ran all test with 8 KB, then with 4, 2, 16 and 32 KB BLCKSZ? If so, I suspect that you are measuring the effects of something different. Yes, that's correct, but why do you suspect that? Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Increasing security in a shared environment ...
Christopher Kings-Lynne wrote: The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. This is from a PgSQL vs MySQL thread on -general ... how hard would it be make it so that a non-superuse user can't do a \l and see everyone's databases? Or, when doing a \d in a database you are able to connect to, it would only show those tables that you are authorized for? Well, you can just go SELECT * FROM pg_database; so fixing \l won't do anything. I too would like to see more security in this respect, but it will be difficult if not impossible to implement methinks... I just played around briefly with removing *all* public access to a couple of catalog tables - pg_class and pg_attrdef. Obviously this breaks things like \d and friends. I'm not sure how much else it might break - certainly a non-privileged user was still able to select from a table, and create and drop a table. Maybe we should look at some paranoid settings for the catalog tables as an option for create database. My previous answer to this question has been use a middleware layer that exposes just the operations you want exposed. But this issue has come up a few times so maybe some more thought is needed. Of course, we are only talking about metadata here, not user table contents, but maybe some people have a justifiable need to hide even the metadata. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Increasing security in a shared environment ...
Hi Christopher, The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. Seem reasonable. Why not prevent normal users to dig on the pg_catalog? What is the impact of it? Well, you can just go SELECT * FROM pg_database; so fixing \l won't do anything. I too would like to see more security in this respect, but it will be difficult if not impossible to implement methinks... Why is it impossible? -- Euler Taveira de Oliveira euler (at) ufgnet.ufg.br Desenvolvedor Web e Administrador de Sistemas UFGNet - Universidade Federal de Goiás ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Andrew Dunstan wrote: My previous answer to this question has been use a middleware layer that exposes just the operations you want exposed. But this issue has come up a few times so maybe some more thought is needed. Of course, we are only talking about metadata here, not user table contents, but maybe some people have a justifiable need to hide even the metadata. You could almost look at it from a security perspective ... if any user can see all databases, then its simple enough to try and connect to them all and see which ones are open ... its not hard to 'mis-configure' pg_hba.conf without realizing it, leaving things open when you meant for them to be closed ... it would be an added layer of protection ... Does anyone know how ppl like Oracle handle this? Are system catalogs like this open to all users? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Increasing security in a shared environment ...
It's rumoured that Euler Taveira de Oliveira once said: Hi Christopher, The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. Seem reasonable. Why not prevent normal users to dig on the pg_catalog? What is the impact of it? Because they can't use tools like pgAdmin or phpPgAdmin unless they can at least read all the catalogs. Regards, Dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Increasing security in a shared environment ...
Andrew Dunstan [EMAIL PROTECTED] writes: I just played around briefly with removing *all* public access to a couple of catalog tables - pg_class and pg_attrdef. Obviously this breaks things like \d and friends. I'm not sure how much else it might break - pg_dump, for starters ... I'm not sure that hiding the contents of the current database's catalog is all that useful a goal in practice. If you have two users sharing a database then probably you *want* them to be able to exchange some amount of information. I can see the use-case for hiding contents of the shared tables (pg_database, pg_shadow, pg_group) in installations where different users have different databases but you want to run just one common postmaster. Even there, though, it doesn't seem all that essential --- its only usefulness is security by obscurity. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Dave Page wrote: It's rumoured that Euler Taveira de Oliveira once said: Hi Christopher, The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. Seem reasonable. Why not prevent normal users to dig on the pg_catalog? What is the impact of it? Because they can't use tools like pgAdmin or phpPgAdmin unless they can at least read all the catalogs. k, but what I'm suggesting shouldn't prevent that, should it? They should only be able to see those resources that they have permissions to see, not all of them ... no? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Increasing security in a shared environment ...
It's rumoured that Marc G. Fournier once said: On Mon, 29 Mar 2004, Dave Page wrote: k, but what I'm suggesting shouldn't prevent that, should it? They should only be able to see those resources that they have permissions to see, not all of them ... no? Wouldn't that require per-row permissions? ie. you can only see the pg_attribute rows for relations that you have some access to? Regards Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Better support for whole-row operations and composite types
We have a number of issues revolving around the fact that composite types (row types) aren't first-class objects. I think it's past time to fix that. Here are some notes about doing it. I am not sure all these ideas are fully-baked ... comments appreciated. When represented as a Datum, the format of a row-type object needs to be something like this: * overall length: int4(this makes the Datum a valid varlena item) * row type id: Oid (either a composite type id or RECORDOID) * row type typmod: int4(see below for usage) -- pad if needed to MAXALIGN boundary * heap tuple representation, beginning with a HeapTupleHeaderData struct If we do it exactly as above then we will be wasting some space, because the xmin/xmax/cmax and ctid fields of HeapTupleHeaderData are of no use in a row that isn't actually a table member row. It is very tempting to overlay the length and rowtype fields with the HeapTupleHeaderData struct. This would save some code as well as space --- see discussion below. Only named composite types, not RECORD, will be allowed to be used as table column types. This ensures that any row object stored on disk will have a valid composite type ID embedded in it, so that the row structure can be retrieved when the row is read. However, we want to be able to support row objects in memory that are of transient record types (for example, the output of a function returning RECORD will have a record type determined by the query itself). I propose that we handle this case by setting the type id to RECORDOID and using the typmod to identify the particular record type --- the typmod will essentially be an index into a backend-local cache of record types. More detail below. We'll add tdtypeid and tdtypmod fields to TupleDesc structs. This will make it easy to set the embedded type information correctly when manufacturing a row datum using a TupleDesc. For TupleDescs associated with relations, tdtypeid is just the relation's row type OID, and tdtypmod is -1. For TupleDescs representing transient row types, we initially set tdtypeid to RECORDOID and tdtypmod to -1 (indicating a completely anonymous row type). If the row type actually needs to be identifiable then we establish a cache entry for it and set the typmod to an index for the cache entry. I think this will only need to happen when the query contains a function-returning-RECORD or a whole-row variable referencing what would otherwise be an anonymous row type, such as a JOIN result. Composite types, as well as the RECORD type, will be marked in pg_type as pass-by-ref, varlena (typlen -1), typalign 'd'. (We will use the maximum alignment always to avoid any dependency on types of the contained columns.) The present function call and return conventions involving TupleTableSlots will be replaced by simply passing and returning these row objects as pass-by-reference Datums. In the case of functions returning rowtypes, we'll continue to support the present ReturnSetInfo convention for returning a separate TupleDesc describing the result type --- but this will just be a crosscheck. We will be able to make generic I/O routines for composite types, comparable to those used now for arrays. Not sure what a convenient external format would look like. (Possibly use the same conventions as for a 1-D array?) We will need to make the convention that the type OID of a composite type is passed to the input routine, in the same way that an array input routine gets the typelem OID; else the input routine won't know what to do. We could also think about allowing functions that are declared as accepting RECORD (ie, polymorphic-across-row-types functions). They would use the same methods already used by polymorphic functions to find out the true types of their inputs. (Might be best to invent a separate pseudotype, say ANYRECORD, rather than overloading RECORD for this purpose.) The recently developed SRF API is a bit unfortunate since it exposes the assumption that a TupleTableSlot must be involved in returning a tuple. If we don't overlay the Datum header with HeapTupleHeader then I think we have to make TupleGetDatum copy the passed tuple and insert the row type info from the slot's tupledesc, which'd be pretty inefficient because it means making an extra copy of the row data. But if we do overlay the header fields, then I think we can set up backwards-compatibility definitions in which the slot is simply ignored. Specifically: TupleDescGetSlot: no-op, returns NULL TupleGetDatum: ignore slot, return tuple t_data pointer as datum This will work because heap_formtuple and BuildTupleFromCStrings can return a HeapTuple whose t_data part is already a valid row Datum, simply by setting the appropriate length and type fields in it. (If the tuple is ever stored to disk as a regular table row, these fields will be overwritten with xmin/cmin info at that time.) To convert a row Datum into something
Re: [HACKERS] Increasing security in a shared environment ...
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I just played around briefly with removing *all* public access to a couple of catalog tables - pg_class and pg_attrdef. Obviously this breaks things like \d and friends. I'm not sure how much else it might break - pg_dump, for starters ... Right. So I played around a little more and restored read priv but only for the db owner, which seemed to work, and makes some sense to me. I'm not sure that hiding the contents of the current database's catalog is all that useful a goal in practice. If you have two users sharing a database then probably you *want* them to be able to exchange some amount of information. It's that probably that niggles a bit. I don't know what usage patterns other people have, and since my typical use is exactly *one* user other than the owner/dba, and all access is mediated by my middleware, none of this affects me. ISTM we need to cater for as broad a set of usage patterns as is reasonable. I can see the use-case for hiding contents of the shared tables (pg_database, pg_shadow, pg_group) in installations where different users have different databases but you want to run just one common postmaster. Even there, though, it doesn't seem all that essential --- its only usefulness is security by obscurity. That phrase to me denotes something they could easily discover if only they knew about it. How would they discover the contents of these, assuming they did know about them and we blocked access? What is not clear to me is how we would even decide which databases to hide, if it is not an all or nothing deal. Marc's phrase those resources that they have permissions to see doesn't define it nearly nicely enough. Say I block access to db foo to all users but bar via pg_hba.conf. Would we then want to prevent all other users from seeing foo in the list of databases? Things like that are why I started exploring a somewhat broader approach. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Marc G. Fournier wrote: On Mon, 29 Mar 2004, Dave Page wrote: It's rumoured that Euler Taveira de Oliveira once said: Hi Christopher, The \l command should only list databases that the current user is authorized for, the \du command should only list users authorized for the current database (and perhaps only superusers should get even that much information), etc. Perhaps it is possible to set PG to do this, but that should probably be the default. Seem reasonable. Why not prevent normal users to dig on the pg_catalog? What is the impact of it? Because they can't use tools like pgAdmin or phpPgAdmin unless they can at least read all the catalogs. k, but what I'm suggesting shouldn't prevent that, should it? They should only be able to see those resources that they have permissions to see, not all of them ... no? I think an auto-filtering system for \l and other backslash commands as needed, makes a lot more sense than trying to deny access to the catalogs. Obscuring them for security reasons is no win, really. Obscuring them so user number 1,000,000 in his own database doesn't have to look at user numbers 1 through 999,999 to see his database go by. While I'm not sure I'd build a 1,000,000 user database, somewhere between the 80 we currently have at work and a few thousand you'd go nuts if you saw a bunch of data that didn't belong to you every time you hit \l. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] int2[] vs int2vector in pg_catalog?
Fabien COELHO [EMAIL PROTECTED] writes: I'm wondering for the rationnal of the types used in various tables in pg_catalog (v 7.4.2) so as to represent the very same thing: History and backwards compatibility, mostly. From the standpoint of the backend I don't think there is any fundamental reason why we couldn't change pg_index.indkey and indclass into varlena arrays, but I'd be worried about breaking existing client-side code that looks at those columns. In particular the question of whether indexing starts at 0 or 1 would be nasty. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Increasing security in a shared environment ...
On Mon, 29 Mar 2004, Andrew Dunstan wrote: It's that probably that niggles a bit. I don't know what usage patterns other people have, and since my typical use is exactly *one* user other than the owner/dba, and all access is mediated by my middleware, none of this affects me. ISTM we need to cater for as broad a set of usage patterns as is reasonable. In my case, I have a dozen clients running OpenACS, all sharing the postmaster instance ... now, at the pg_hba.conf level, the database is restrict to userid @ IP ... so, I'm generally not too concerned about ClientA being able to access ClientBs database ... but just in case an admin somehow makes a mistake with the pg_hba.conf file, not being to find out about other databases in the system would be nice ... What is not clear to me is how we would even decide which databases to hide, if it is not an all or nothing deal. Marc's phrase those resources that they have permissions to see doesn't define it nearly nicely enough. Say I block access to db foo to all users but bar via pg_hba.conf. Would we then want to prevent all other users from seeing foo in the list of databases? Things like that are why I started exploring a somewhat broader approach. by default, pgsql superuse would see everything usera, when doing a \l, would only see those databases that are owned by usera ... maybe have some sort of GRANT ALL ON database so that userb would see it listed to. userc, altho not owner of any database, would ahve grant access to usera's database, and see only that one ... inside of usera's database, even though userc had access to the database, a 'GRANT REVOKE' on a specific table would result in that table not being seen in a \d listing ... As to 'SELECT * FROM pg_database;' or 'SELECT * FROM pg_class' ... similar to pg_shadow ... move it to a different name and have a VIEW on the appropriate system tables that auto-adds something to the effect that the list is restricted to those with access to those tables ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Better support for whole-row operations and composite types
Tom Lane [EMAIL PROTECTED] writes: We have a number of issues revolving around the fact that composite types (row types) aren't first-class objects. I think it's past time to fix that. ... Only named composite types, not RECORD, will be allowed to be used as table column types. If I understand what you're talking about, you would be allowed to CREATE TYPE a composite type, like say, address and then use that as a datatype all over your database? And then if you find address needs a new field you can add it to the type and automatically have it added all over your database to any table column using that type? Speaking as a user, that would be **very** nice. I've often found myself wishing for just such a feature. It would simplify data model maintenance a whole heck of a lot. How will client programs see the data if i do a select *? In my ideal world it would be shipped over in a binary representation that a driver would translate to a perl hash / php array / whatever. But maybe it would be simpler to just ship them over the subcolumns with names like shipping.line_1 and shipping.country. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL block size vs. LVM2 stripe width
On 30 Mar, Manfred Koizar wrote: On Mon, 29 Mar 2004 08:50:42 -0800 (PST), [EMAIL PROTECTED] wrote: In this case, I've only done 1 per each combination. I've found the results for this test to be reproduceable. Pardon? I haven't repeated any runs for each combination, e.g. 1 test with 16kb lvm stripe width and 2kb BLCKSZ, 1 test with 16kb lvm stripe width and 4kb BLCKSZ... Linux-2.6.3, LVM2 Stripe Width BLCKSZ (going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB261726562652266426672642 4 KB439344864577455745114448 8 KB433744234471457641113642 16 KB 441244954532453629852312 32 KB 370537843886392529362362 Does this mean that you first ran all test with 8 KB, then with 4, 2, 16 and 32 KB BLCKSZ? If so, I suspect that you are measuring the effects of something different. Yes, that's correct, but why do you suspect that? Gut feelings, hard to put into words. Let me try: Nobody really knows what the optimal BLCKSZ is. Most probably it depends on the application, OS, hardware, and other factors. 8 KB is believed to be a good general purpose BLCKSZ. I wouldn't be surprised if 8 KB turns out to be suboptimal in one or the other case (or even in most cases). But if so, I would expect it to be either too small or too large. In your tests, however, there are three configurations where 8 KB is slower than both 4 KB and 16 KB. Absent any explanation for this interesting effect, it is easier to mistrust your numbers. If you run your tests in the opposite order, on the same hardware, in the same freshly formatted partitions, and you get the same results, that would be an argument in favour of their accurancy. Maybe we find out that those 1.5% are just noise. I did reformat each partition between tests. :) When I have tested for repeatability in the past I have found results to fluxuate up to 5%, so I would claim the 1.5% to be noise. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL block size vs. LVM2 stripe width
On Mon, 29 Mar 2004 08:50:42 -0800 (PST), [EMAIL PROTECTED] wrote: In this case, I've only done 1 per each combination. I've found the results for this test to be reproduceable. Pardon? Linux-2.6.3, LVM2 Stripe Width BLCKSZ (going down)16 KB 32 KB 64 KB 128 KB 256 KB 512 KB 2 KB261726562652266426672642 4 KB439344864577455745114448 8 KB433744234471457641113642 16 KB 441244954532453629852312 32 KB 370537843886392529362362 Does this mean that you first ran all test with 8 KB, then with 4, 2, 16 and 32 KB BLCKSZ? If so, I suspect that you are measuring the effects of something different. Yes, that's correct, but why do you suspect that? Gut feelings, hard to put into words. Let me try: Nobody really knows what the optimal BLCKSZ is. Most probably it depends on the application, OS, hardware, and other factors. 8 KB is believed to be a good general purpose BLCKSZ. I wouldn't be surprised if 8 KB turns out to be suboptimal in one or the other case (or even in most cases). But if so, I would expect it to be either too small or too large. In your tests, however, there are three configurations where 8 KB is slower than both 4 KB and 16 KB. Absent any explanation for this interesting effect, it is easier to mistrust your numbers. If you run your tests in the opposite order, on the same hardware, in the same freshly formatted partitions, and you get the same results, that would be an argument in favour of their accurancy. Maybe we find out that those 1.5% are just noise. Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Better support for whole-row operations and composite types
Greg Stark [EMAIL PROTECTED] writes: If I understand what you're talking about, you would be allowed to CREATE TYPE a composite type, like say, address and then use that as a datatype all over your database? And then if you find address needs a new field you can add it to the type and automatically have it added all over your database to any table column using that type? I believe that would work, though you might have some issues with cached plans. How will client programs see the data if i do a select *? TBD. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Win32 compatibility now broken for Microsoft and Intel Windows compilers even for Libpq in current snapshot
Title: Message E:\postgresql-snapshot\srcnmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 7.10.3077Copyright (C) Microsoft Corporation. All rights reserved. cd include if not exist pg_config.h copy pg_config.h.win32 pg_config.h cd .. cd interfaces\libpq nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 7.10.3077Copyright (C) Microsoft Corporation. All rights reserved. Building the Win32 static library... cl.exe @u:\tmp\nm1840.tmpgetaddrinfo.ce:\postgresql-snapshot\src\include\port.h(56) : error C2010: '.' : unexpected in macro formal parameter liste:\postgresql-snapshot\src\include\port.h(56) : error C2010: '.' : unexpected in macro formal parameter liste:\postgresql-snapshot\src\include\port.h(56) : error C2010: '.' : unexpected in macro formal parameter liste:\postgresql-snapshot\src\include\port.h(56) : error C2010: ')' : unexpected in macro formal parameter list..\..\port\getaddrinfo.c(47) : error C2079: 'sin' uses undefined struct 'sockaddr_in'..\..\port\getaddrinfo.c(54) : error C2065: 'AF_INET' : undeclared identifier..\..\port\getaddrinfo.c(55) : error C2065: 'SOCK_STREAM' : undeclared identifier..\..\port\getaddrinfo.c(60) : error C2065: 'AF_UNSPEC' : undeclared identifier..\..\port\getaddrinfo.c(71) : error C2224: left of '.sin_family' must have struct/union type..\..\port\getaddrinfo.c(76) : error C2224: left of '.sin_addr' must have struct/union type..\..\port\getaddrinfo.c(76) : warning C4013: 'htonl' undefined; assuming extern returning int..\..\port\getaddrinfo.c(76) : error C2065: 'INADDR_ANY' : undeclared identifier..\..\port\getaddrinfo.c(79) : error C2224: left of '.sin_addr' must have struct/union type..\..\port\getaddrinfo.c(79) : error C2198: 'inet_aton' : too few arguments for call through pointer-to-function..\..\port\getaddrinfo.c(87) : error C2079: 'hpstr' uses undefined struct 'hostent'..\..\port\getaddrinfo.c(91) : warning C4133: 'function' : incompatible types - from 'int *' to 'hostent *'..\..\port\getaddrinfo.c(98) : error C2065: 'h_errno' : undeclared identifier..\..\port\getaddrinfo.c(100) : error C2065: 'HOST_NOT_FOUND' : undeclared identifier..\..\port\getaddrinfo.c(100) : error C2051: case _expression_ not constant..\..\port\getaddrinfo.c(101) : error C2065: 'NO_DATA' : undeclared identifier..\..\port\getaddrinfo.c(101) : error C2051: case _expression_ not constant..\..\port\getaddrinfo.c(103) : error C2065: 'TRY_AGAIN' : undeclared identifier..\..\port\getaddrinfo.c(103) : error C2051: case _expression_ not constant..\..\port\getaddrinfo.c(105) : error C2065: 'NO_RECOVERY' : undeclared identifier..\..\port\getaddrinfo.c(105) : error C2051: case _expression_ not constant..\..\port\getaddrinfo.c(110) : error C2037: left of 'h_addrtype' specifies undefined struct/union 'hostent'..\..\port\getaddrinfo.c(113) : error C2224: left of '.sin_addr' must have struct/union type..\..\port\getaddrinfo.c(113) : error C2037: left of 'h_addr' specifies undefined struct/union 'hostent'..\..\port\getaddrinfo.c(113) : error C2037: left of 'h_length' specifies undefined struct/union 'hostent'..\..\port\getaddrinfo.c(113) : error C2168: 'memcpy' : too few actual parameters for intrinsic function..\..\port\getaddrinfo.c(119) : error C2224: left of '.sin_addr' must have struct/union type..\..\port\getaddrinfo.c(121) : error C2224: left of '.sin_addr' must have struct/union type..\..\port\getaddrinfo.c(121) : error C2065: 'INADDR_LOOPBACK' : undeclared identifier..\..\port\getaddrinfo.c(125) : error C2224: left of '.sin_port' must have struct/union type..\..\port\getaddrinfo.c(125) : warning C4013: 'htons' undefined; assuming extern returning int..\..\port\getaddrinfo.c(135) : error C2027: use of undefined type 'sockaddr_in' ..\..\port\getaddrinfo.c(47) : see declaration of 'sockaddr_in'..\..\port\getaddrinfo.c(142) : error C2027: use of undefined type 'sockaddr_in' ..\..\port\getaddrinfo.c(47) : see declaration of 'sockaddr_in'..\..\port\getaddrinfo.c(148) : error C2027: use of undefined type 'sockaddr_in' ..\..\port\getaddrinfo.c(47) : see declaration of 'sockaddr_in'..\..\port\getaddrinfo.c(238) : error C2037: left of 'sa_family' specifies undefined struct/union 'sockaddr'..\..\port\getaddrinfo.c(242) : warning C4013: 'inet_ntoa' undefined; assuming extern returning int..\..\port\getaddrinfo.c(242) : error C2037: left of 'sin_addr' specifies undefined struct/union 'sockaddr_in'..\..\port\getaddrinfo.c(242) : warning C4047: '=' : 'char *' differs in levels of indirection from 'int'..\..\port\getaddrinfo.c(243) : warning C4013: 'snprintf' undefined; assuming extern returning int..\..\port\getaddrinfo.c(253) : error C2037: left of 'sa_family' specifies undefined struct/union 'sockaddr'..\..\port\getaddrinfo.c(256) : warning C4013: 'ntohs' undefined; assuming extern returning int..\..\port\getaddrinfo.c(256) : error C2037: left of 'sin_port' specifies undefined
[HACKERS] hacking data directories
RedHat with PG 7.3.2 I'm recovering a harddrive failure where all of the database files were thrown into one directory. I'm trying to sort out which ones go with what. (Stop laughing.) I've identified template1 and template0 files, but I don't need to recover those... The rest of the files seem to be database data files and it is possible the the zero length files were directories, but I'm not sure. Does anyone have any tools or hints? I can determine a lot by just looking at them, but hints would help. If necessary, I could write a little C program to extract the headers if that is what is necessary. All advice is greatly appreciated. Thanks, elein [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] hacking data directories
On Mon, Mar 29, 2004 at 03:55:22PM -0800, elein wrote: Does anyone have any tools or hints? I can determine a lot by just looking at them, but hints would help. If necessary, I could write a little C program to extract the headers if that is what is necessary. Try RedHat's pg_filedump, at http://sources.redhat.com/rhdb/ -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Use it up, wear it out, make it do, or do without ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] PostgreSQL and FreeBSD SoftUpdates
Hi guys, Does anyone else have this problem? We have softupdates turned on on our data dir. (Soon to be turned off due to these issues). The partition is 12GB. 'df' says that we're using 12 and a bit GB but 'du' says we're using 2GB (which we really are). It seems that perhaps softupdates is caching some stuff, or preventing something from being written properly, etc. The funny thing is that this was never a problem until we upgraded to 7.4. Has something changed in the way file writes or syncs are done? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump 7.4 bug
If you do this sequence of events, you get a failure to restore: This is not a pg_dump bug. Possibly ALTER USER should refuse to drop someone's superuserness if there is content in the database that depends on his superuserness, but I don't see how to enforce that. How about we allow changing owner of lanugages so I can fix this problem? Is it safe for me to just update the catalogs? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
what version of FreeBSD are you using? I'm running 4.9-STABLE with softupdates on my db file system ... FreeBSD goddard.calorieking.com 4.9-STABLE FreeBSD 4.9-STABLE #2: Mon Jan 26 23:23:17 EST 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GODDARD i386 We're not 100% sure it's softupdates, but we can't see anything else that it could be. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
what version of FreeBSD are you using? I'm running 4.9-STABLE with softupdates on my db file system ... On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote: Hi guys, Does anyone else have this problem? We have softupdates turned on on our data dir. (Soon to be turned off due to these issues). The partition is 12GB. 'df' says that we're using 12 and a bit GB but 'du' says we're using 2GB (which we really are). It seems that perhaps softupdates is caching some stuff, or preventing something from being written properly, etc. The funny thing is that this was never a problem until we upgraded to 7.4. Has something changed in the way file writes or syncs are done? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump 7.4 bug
Christopher Kings-Lynne [EMAIL PROTECTED] writes: How about we allow changing owner of lanugages so I can fix this problem? Is it safe for me to just update the catalogs? Sure. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote: what version of FreeBSD are you using? I'm running 4.9-STABLE with softupdates on my db file system ... FreeBSD goddard.calorieking.com 4.9-STABLE FreeBSD 4.9-STABLE #2: Mon Jan 26 23:23:17 EST 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GODDARD i386 We're not 100% sure it's softupdates, but we can't see anything else that it could be. Right off the top of my head, it almost sounds like a file is being held open after its been deleted ... we went through that with the new aspseek a little while back, where 170gig just disappeared overnight, but du showed hardly any disk space being used ... Does restarting the database server (not rebooting, just restarting the postmaster) free up the disk space? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
Right off the top of my head, it almost sounds like a file is being held open after its been deleted ... we went through that with the new aspseek a little while back, where 170gig just disappeared overnight, but du showed hardly any disk space being used ... Does restarting the database server (not rebooting, just restarting the postmaster) free up the disk space? No - have to reboot. That's probably because of softupdates though. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_dump end comment
This might seem a bit silly, but is there any chance we could add a comment at the end of pg_dump text output that says '-- End of dump'? Would make it useful for checking that you actually have a complete dump... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
On Tue, 30 Mar 2004, Christopher Kings-Lynne wrote: Right off the top of my head, it almost sounds like a file is being held open after its been deleted ... we went through that with the new aspseek a little while back, where 170gig just disappeared overnight, but du showed hardly any disk space being used ... Does restarting the database server (not rebooting, just restarting the postmaster) free up the disk space? No - have to reboot. That's probably because of softupdates though. 'k, *shouldn't* require a reboot ... but, what I'd try is to do what you've thought .. disable softupdates and see if you can recreate ... if killing off the process auto-reclaims the space fast, then it sounds like a stale file being held open (log file being rotated improperly?) ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
'k, *shouldn't* require a reboot ... but, what I'd try is to do what you've thought .. disable softupdates and see if you can recreate ... if killing off the process auto-reclaims the space fast, then it sounds like a stale file being held open (log file being rotated improperly?) ... Log file's on a different partition... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL and FreeBSD SoftUpdates
Right off the top of my head, it almost sounds like a file is being held open after its been deleted ... we went through that with the new aspseek a little while back, where 170gig just disappeared overnight, but du showed hardly any disk space being used ... Does restarting the database server (not rebooting, just restarting the postmaster) free up the disk space? No - have to reboot. That's probably because of softupdates though. 'k, *shouldn't* require a reboot ... but, what I'd try is to do what you've thought .. disable softupdates and see if you can recreate ... if killing off the process auto-reclaims the space fast, then it sounds like a stale file being held open (log file being rotated improperly?) ... Install the latest version of lsof(8) and see if there are any stale files being held open. I've got databases on FreeBSD 4.X and 5.X with softupdates on both and haven't had a problem. I'm wondering if your database is doing something exotic that hasn't been tickled. The first thing that comes to mind is, are you using deferred constraints? Second, if it is a soft updates issue, then a reboot isn't necessary (as Marc says)... you should be able to stop the database and type df -k sync sleep 30 df -k see space being freed up. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump end comment
Christopher Kings-Lynne [EMAIL PROTECTED] writes: This might seem a bit silly, but is there any chance we could add a comment at the end of pg_dump text output that says '-- End of dump'? Sure --- while you're at it, put a beginning of dump at the start. Is it worth adding the database name and/or other command-line parameters given to pg_dump? One thing to think about is the difference between a bare pg_dump and a pg_dump/pg_restore sequence. Should these always generate identical text output? (They do as of CVS tip, I believe, though this was not always true before.) Possibly this is all gilding the lily though... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump end comment
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: This might seem a bit silly, but is there any chance we could add a comment at the end of pg_dump text output that says '-- End of dump'? Sure --- while you're at it, put a beginning of dump at the start. Is it worth adding the database name and/or other command-line parameters given to pg_dump? One thing to think about is the difference between a bare pg_dump and a pg_dump/pg_restore sequence. Should these always generate identical text output? (They do as of CVS tip, I believe, though this was not always true before.) Possibly this is all gilding the lily though... I like an end-of-dump marker for folks who want to check if the dump got truncated somehow. I can see how to do that for text dumps, but what about for tar or custom dumps? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
