[HACKERS] bug in GUC
Hackers, I think there a bug in the GUC mechanism. The custom variables patch added several malloc() and a strdup() call, and they are never checked for an out of memory condition. -- Alvaro Herrera () "El que vive para el futuro es un iluso, y el que vive para el pasado, un imbécil" (Luis Adler, "Los tripulantes de la noche") ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] warning missing
"Greg Stark" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Thomas Hallgren <[EMAIL PROTECTED]> writes: > > > Try to use a similar construct in a more elaborate OO-language (like Java, C#, > > etc.) and you will get an error like: > > Just as a point of reference, Java and C# are not "more elaborate" object > systems. For Java at least being *less* elaborate was an explicit design goal. > I mean more elaborate from a n OO semantics standpoint. I.e. it enforces OO much more, provides better data hiding, the ability to use interfaces (and thereby enforce interface/implementation separation), package protection, etc. etc. To elaborate something doesn't necessarily mean adding more kludges to a language. > The designers thought C++ had too many features and gave programmers too much > rope to hang themselves. They thought by removing major OO features that > confuse people the resulting language would be 90% as functional with 10% of > the problems. > > If you want a *more* elaborate OO language than C++ you would have to go to, > say, Common Lisp. But I doubt it would support your argument. > I'm not an expert on Common Lisp but I think it would. At least if you'd use CLOS and defclass. There's no way to hide readers/writers/accessors that you inherit. >From an OO semantics point of view, I still regard Java and C# much more elaborate than both C++ and Common Lisp. The latter lacks interfaces and different levels of protection. Kind regards, Thomas Hallgren ---(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] pg_get_indexdef
Should pg_get_indexdef return its TABLESPACE clause? Already done. Cool. I'd considered it before when I was coding psql stuff, but then I forgot to bring it up again on the list... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_largeobject and tablespaces
If nothing else comes to mind, a reasonable compromise for 7.5 would be to forbid moving any system catalog except pg_largeobject and its indexes ... Plus pg_dump support for it :/ Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] warning missing
Thomas Hallgren <[EMAIL PROTECTED]> writes: > Try to use a similar construct in a more elaborate OO-language (like Java, C#, > etc.) and you will get an error like: Just as a point of reference, Java and C# are not "more elaborate" object systems. For Java at least being *less* elaborate was an explicit design goal. The designers thought C++ had too many features and gave programmers too much rope to hang themselves. They thought by removing major OO features that confuse people the resulting language would be 90% as functional with 10% of the problems. If you want a *more* elaborate OO language than C++ you would have to go to, say, Common Lisp. But I doubt it would support your argument. Common Lisp goes pretty far out of its way to make sure you can do whatever you dream of under the sun. In any case it would make a weak argument given the slim portion of programmers that know Common Lisp. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.5-dev, pg_dumpall, dollarquoting
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> 2) pg_restore needs to be more tolerant with certain kinds of errors. > Hmmm, dunno about this - it wasn't on my radar really. I'll experiment > with it, but I don't think I'm going to have time before June 30th :( I think we dealt with this already: pg_restore has been taught that the correct response to a SQL command error is to report it and forge ahead, not curl up and die. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Fixing pg_dump
OK, I think it might save me some time if I get some guidance on how we should modify pg_dump to fix the owner/grants issue. I intend to make new archives created with 7.5 pg_dump have the fix, and restoring pre 7.5 binary dumps will have exactly the previous behaviour. The reason for this is that extracting the acls and owners to the end requires scanning the entire archive twice - not necessarily something we want to do (is it?) So, I have to make acls and owners completely independent DumpableObjects, right? Then I can get them sorted to the end, etc., etc. I also intend to implement a flag that makes it use the sql standard set session_authorization behaviour. Is all that the way to go? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_get_indexdef
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Should pg_get_indexdef return its TABLESPACE clause? Already done. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_largeobject and tablespaces
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > With our new tablespace set up, is it ever possible for someone to move > pg_largeobject to another tablespace? Assuming that ALTER TABLE SET TABLESPACE gets in, my preferred answer is to apply that operation to pg_largeobject. We do need to think a bit about what the safety constraints on ALTER TABLE SET TABLESPACE should be. To allow the above, we cannot completely forbid moving system catalogs. However, it will not work to allow moving *every* system catalog ... for instance, if you were to move pg_class itself, things would be more than slightly broken, because the backend could not find pg_class to learn where the system catalogs are. It might be that we only need to forbid moving the "nailed" system relations, but I haven't thought it through yet. If nothing else comes to mind, a reasonable compromise for 7.5 would be to forbid moving any system catalog except pg_largeobject and its indexes ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PREPARE and transactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Merlin Moncure wrote: > I have to jump through hoops to keep track of what statements are > already prepared to keep from bouncing the current transaction. Christopher Kings-Lynne wrote: > * PREPARE OR REPLACE... > > This would be an incredibly useful command since there's no > way of _checking_ in advance that a name is already used as a > prepared statement... A check would be nice (and I've asked about it before) but it's really not a lot of jumping through hoops since each connection has it's own "namespace" of prepared statements. Since they last until an explicit deallocate, the simple use of unique names makes it fairly easy on the application side. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200406232330 -BEGIN PGP SIGNATURE- iD8DBQFA2krxvJuQZxSWSsgRAhLSAJ9othitQerDlB9+J65rVl3EbRT9+QCeJfzH vFdWCDLvxU/zkFMLEDjpydU= =OzCx -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_get_indexdef
Should pg_get_indexdef return its TABLESPACE clause? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.5-dev, pg_dumpall, dollarquoting
1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2 database with confusing dependancies (e.g. functions depend on views which depend on multiple tables and other views containing other functions), some objects (almost always functions) still get silently dropped from the dump file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2), but nobody wanted to work on it -- especially as it's only possible to demonstrate with a sufficiently complex early 7.2 database. I have a good test database for this, I will test with CVS. Nothing gets silently dropped. It will cause an ERROR on creation and then keep going, but it won't silently drop it... (unless it's a binary dump thing...) Anyway, pg_dump in CVS does correct dump ordering based on a topological sort of the pg_depend relation (thank Tom for that). It will of course only work on a 7.3 or higher backend. 2) pg_restore needs to be more tolerant with certain kinds of errors. For example, if an object already exists in the target database, due to being from template1, it should be possible to tell pg_restore to ignore the error with a switch.Currently, this issue prevents me from using pg_restore on some systems, where the restore isn't run as the superuser. Another switch, telling pg_restore to attempt to ignore all errors and restore anyway, would also be keen (though I can see potential abuse issues). Has this already been addressed in CVS? Hmmm, dunno about this - it wasn't on my radar really. I'll experiment with it, but I don't think I'm going to have time before June 30th :( Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Putting OIDs etc back into pg_dump?
Of course, OIDs have been removed from a standard pg_dump so different installations can diff their schemas (and a good thing too). So - should this information be: 1. Logged nightly via standard logging procedures. 2. Stored in a format=custom dump but not for textual schemas. 3. Stored in a different file, that is updated on occasion 4. Not stored at all, it's pointless. 5. Not stored at all, PITR means this is academic now. If you dump with oids, it has the latest oid in the dump file... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pg_largeobject and tablespaces
With our new tablespace set up, is it ever possible for someone to move pg_largeobject to another tablespace? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PREPARE and transactions
Part of the problem is that PREPARE has no provision to overwrite an existing plan (CREATE OR REPLACE). I run into this all the time because I make heavy use of prepared statements to emulate an ISAM file system. I have to jump through hoops to keep track of what statements are already prepared to keep from bouncing the current transaction. Bruce - TODO?: * PREPARE OR REPLACE... This would be an incredibly useful command since there's no way of _checking_ in advance that a name is already used as a prepared statement... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] DBT-2 results using tablespaces
Hello, Just wanted to share some data I've generated with dbt-2 using tablespaces from a CVS export of PostgreSQL on June 22. I have results and details on how I have the disks laid out with LVM2 here: http://www.osdl.org/projects/dbt2dev/results/dev4-006/538/ The performance appears to be on par with results I've been getting with PostgreSQL 7.4.1. For a comparison, you can see those results here: http://www.osdl.org/projects/dbt2dev/results/dev4-006/526/ The volume where the database log resides is identicle between the two tests. The disk layout has only changed for where the tables and indexes reside. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(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] warning missing
Gaetano Mendola wrote: Thomas Hallgren wrote: Speaking in generic OO terms, using inheritance, you cannot remove attributes that are present in the generalisation. If B inherits A, an instance of B is per definition also an instance of A. Thus, you must alwasy be able to cast a B into an A. In short, If you don't want the OID, you cannot inherit a something that has an OID. This is not completely true: struct B { void foo(); }; class D : public B { private: void foo(); public: void bar(); }; as you can see a D "is a" B but the publich foo() doesn't appartaint to D, at least an user of D could not use foo() C++ is not exactly the model for OO semantics. It's a fairly wierd addition to C resulting in a hybrid language where quite a few constructs violates sane OO. Try to use a similar construct in a more elaborate OO-language (like Java, C#, etc.) and you will get an error like: "foo() in D cannot override foo() in B; attempting to assign weaker access privileges; was public" which makes a lot more sense. Kind regards, Thomas Hallgren PS. This discussion doesn't really belong here. I'd be happy to continue it off the list though. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PREPARE and transactions
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes: > Even if the spec doesn't help, I think a statement prepared within a > transaction should definitely be deallocated at the end of the transaction. Uh, you do realize that Postgres does *everything* within a transaction? The above proposal would render prepared statements practically useless. regards, tom lane ---(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] BLOBs and a virtual file system
On Wed, 23 Jun 2004 15:07:42 -0400 Peter Martini <[EMAIL PROTECTED]> wrote: > I'm not sure what you mean by breaking blobs transactional integrity. > Do you mean by allowing filesystem type access, the blobs won't be > properly locked and updated during a transaction? If so, that's > exactly what I'm trying to achieve - a compromise between forcing the > files to be stored solely in the database (maintaining integrity but > sacrificing functionality) or referencing their names to gain > functionality, but at a greater cost. > More to the point, wouldn't file locking mechanisms, and the > possibility of limiting file visibility through the virtual > filesystem, allow transactional integrity to be maintained? If not, > could you explain where the problem is so I can look further into it? I believe the problem is that the blob could be in several different states inside of several different transactions. How do you determine which you show in the filesystem? Even if the file system is read only you still have this problem of "which" of the many possible BLOBs to reveal. - Frank Wiles <[EMAIL PROTECTED]> http://frank.wiles.org - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] warning missing
Thomas Hallgren wrote: Speaking in generic OO terms, using inheritance, you cannot remove attributes that are present in the generalisation. If B inherits A, an instance of B is per definition also an instance of A. Thus, you must alwasy be able to cast a B into an A. In short, If you don't want the OID, you cannot inherit a something that has an OID. This is not completely true: struct B { void foo(); }; class D : public B { private: void foo(); public: void bar(); }; as you can see a D "is a" B but the publich foo() doesn't appartaint to D, at least an user of D could not use foo() Having said that, I think a warning is motivated. The warning should state that attributes (columns) present in the generalisation (the parent table) cannot be hidden. Right. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BLOBs and a virtual file system
I'm not sure what you mean by breaking blobs transactional integrity. Do you mean by allowing filesystem type access, the blobs won't be properly locked and updated during a transaction? If so, that's exactly what I'm trying to achieve - a compromise between forcing the files to be stored solely in the database (maintaining integrity but sacrificing functionality) or referencing their names to gain functionality, but at a greater cost. More to the point, wouldn't file locking mechanisms, and the possibility of limiting file visibility through the virtual filesystem, allow transactional integrity to be maintained? If not, could you explain where the problem is so I can look further into it? Thanks, Peter On Wed, 2004-06-23 at 11:48, Tom Lane wrote: > Peter Martini <[EMAIL PROTECTED]> writes: > > Lately I've been planning work on a patch to postgres and linux on my > > system to allow access to BLOBs as a virtual filesystem, so I can see > > any file I put in there even through network shares while avoiding > > duplication / broken link issues. Does this sound like something worth > > doing / is there a better way to safely reference files from both inside > > and outside postgres? > > How would you do that without breaking transactional integrity for > blobs? There'd be no way to deal with multiple row versions in such > a representation. > > 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] creating a cluster
On Jun 23, 2004, at 11:36 AM, Tom Lane wrote: David Garamond <[EMAIL PROTECTED]> writes: Alvaro Herrera wrote: On Mon, Jun 21, 2004 at 09:16:35PM -0400, Alexander Cohen wrote: Does anyone have any new ways to create clusters without using initdb or bootstrap mode? I need to be able to create one without those 2 things. Any ideas? initdb'ing somewhere else and copying the resulting directory? Btw, I've been doing this for a binary distribution on Windows (Cygwin) and Linux. Yeah, that would work fine as long as the "somewhere else" is using an identical Postgres build. I found out in off-list conversation that Alexander wants to build a hacked-up version of Postgres with all bootstrap code removed (and, I suppose, a bunch of other changes too). Seems to me that file-level compatibility would be difficult to guarantee under such circumstances, so I told him he ought to put back the bootstrap support ... it's not like it's large ... For the meantime, i ended up compiling a normal version of postgres and using that with initdb, then switching it over to my "hacked-up" version. It works, and thats all i need for now! Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PREPARE and transactions
> Now, here's a scenario that has us worried: > > BEGIN > PREPARE foo AS ... > ... [error] > DEALLOCATE foo [fails: already aborted by previous error] > ABORT > BEGIN > PREPARE foo AS ... [fails: foo is already defined!] > EXECUTE foo [fails: already aborted by previous error] > COMMIT[fails: already aborted by previous error] Part of the problem is that PREPARE has no provision to overwrite an existing plan (CREATE OR REPLACE). I run into this all the time because I make heavy use of prepared statements to emulate an ISAM file system. I have to jump through hoops to keep track of what statements are already prepared to keep from bouncing the current transaction. However, at least for me, nested x basically solves this problem. I'll just always wrap the prepare statement with a sub-transaction and commit/rollback as necessary. This is odd because the rollback does nothing other than guard the following statements from the prepare failure to execute. So, you do: BEGIN BEGIN PREPARE foo AS ... COMMIT/ROLLBACK ... [error] DEALLOCATE foo[fails: already aborted by previous error] ABORT BEGIN BEGIN PREPARE foo AS ... [fails: foo is already defined!] COMMIT/ROLLBACK EXECUTE foo [will now always run if prepare is aborted] COMMIT [commit executes] To me, this is good style and it looks like nested x is going to make 7.5. I have no opinion on whether rollback should affect prepare/deallocate. Merlin ---(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] 7.5-dev, pg_dumpall, dollarquoting
KL- > Would you be able to specify exactly the deficiences? It's my mission > at the moment to make pg_dump 7.5 known-issue free :) Well, since you asked: (please excuse me if I'm covering old ground. I was off Hackers for almost a month this spring) 1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2 database with confusing dependancies (e.g. functions depend on views which depend on multiple tables and other views containing other functions), some objects (almost always functions) still get silently dropped from the dump file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2), but nobody wanted to work on it -- especially as it's only possible to demonstrate with a sufficiently complex early 7.2 database. I have a good test database for this, I will test with CVS. 2) pg_restore needs to be more tolerant with certain kinds of errors. For example, if an object already exists in the target database, due to being from template1, it should be possible to tell pg_restore to ignore the error with a switch.Currently, this issue prevents me from using pg_restore on some systems, where the restore isn't run as the superuser. Another switch, telling pg_restore to attempt to ignore all errors and restore anyway, would also be keen (though I can see potential abuse issues). Has this already been addressed in CVS? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PREPARE and transactions
We were discussing prepared statement support for libpqxx just now (Bruce, Peter Eisentraut & myself are manning the postgres booth at LinuxTag 2004 in Karlsruhe, Germany), when we ran into a problem that came up two months ago. That discussion follows: Post by Alvaro Herrera: > Hackers, > > Is this expected? If so, why? I'd expect the prepared stmt to be > deallocated. > > alvherre=# begin; > BEGIN > alvherre=# prepare tres as select 3; > PREPARE > alvherre=# rollback; > ROLLBACK > alvherre=# execute tres; > ?column? > -- > 3 > (1 fila) Followup by Tom Lane: > prepare.c probably should have provisions for rolling back its state to > the start of a failed transaction ... but it doesn't. > > Before jumping into doing that, though, I'd want to have some > discussions about the implications for the V3 protocol's notion of > prepared statements. The protocol spec does not say anything that > would suggest that prepared statements are lost on transaction rollback, > and offhand it seems like they shouldn't be because the protocol is > lower-level than transactions. Now, here's a scenario that has us worried: BEGIN PREPARE foo AS ... ... [error] DEALLOCATE foo[fails: already aborted by previous error] ABORT BEGIN PREPARE foo AS ...[fails: foo is already defined!] EXECUTE foo [fails: already aborted by previous error] COMMIT [fails: already aborted by previous error] You could say that the DEALLOCATE in the first transaction should have been outside the transaction, i.e. after the ABORT. But that would mean that the client is expected to roll back, manually, individual changes made in an aborted transaction. If that's what we expect from the client, what's the point in having transactions in the first place? Lots of variations of the scenario spring to mind. Imagine the second transaction were not a transaction at all: the second PREPARE would fail, and the EXECUTE may go execute the wrong statement. A partial fix would be to allow identical redefinitions of a prepared statement, optionally with reference counting to determine when it should be deallocated. But instances of the same transaction may want to include a pseudo-constant in the fixed part of the query text that changes between instances of the transaction. Even if the spec doesn't help, I think a statement prepared within a transaction should definitely be deallocated at the end of the transaction. If it turns out that this wastes a lot of opportunities for reuse, the prepared plans can always be cached across definitions. Jeroen ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] BLOBs and a virtual file system
Peter Martini writes: Lately I've been planning work on a patch to postgres and linux on my system to allow access to BLOBs as a virtual filesystem, so I can see any file I put in there even through network shares while avoiding duplication / broken link issues. Does this sound like something worth doing / is there a better way to safely reference files from both inside and outside postgres? I have been looking into using the Tcl VFS features for this kind of thing. I work with AOLserver and PostgreSQL so I have Tcl and PostgreSQL available to me. http://nnsa.dl.ac.uk/MIDAS/manual/ActiveTcl8.4.4.0-html/tclvfs/doc/vfs.html I haven't thought about accessing the VFS from inside PostgreSQL though. Dave Bauer [EMAIL PROTECTED] ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.5-dev, pg_dumpall, dollarquoting
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: pg_dumpall lacks the "-X disable-dollar-quoting" switch. I can add it - do the other hackers want it? It should be there --- in general pg_dumpall should be able to pass down any pg_dump switch that makes sense. Perhaps rather than replicating every pg_dump option pg_dumpall needs a single option that can encapsulate them? e.g. --dump-options="--foo --bar --baz" Just a thought cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] creating a cluster
David Garamond <[EMAIL PROTECTED]> writes: > Alvaro Herrera wrote: >> On Mon, Jun 21, 2004 at 09:16:35PM -0400, Alexander Cohen wrote: >>> Does anyone have any new ways to create clusters without using initdb >>> or bootstrap mode? I need to be able to create one without those 2 >>> things. Any ideas? >> >> initdb'ing somewhere else and copying the resulting directory? > Btw, I've been doing this for a binary distribution on Windows (Cygwin) > and Linux. Yeah, that would work fine as long as the "somewhere else" is using an identical Postgres build. I found out in off-list conversation that Alexander wants to build a hacked-up version of Postgres with all bootstrap code removed (and, I suppose, a bunch of other changes too). Seems to me that file-level compatibility would be difficult to guarantee under such circumstances, so I told him he ought to put back the bootstrap support ... it's not like it's large ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BLOBs and a virtual file system
Peter Martini <[EMAIL PROTECTED]> writes: > Lately I've been planning work on a patch to postgres and linux on my > system to allow access to BLOBs as a virtual filesystem, so I can see > any file I put in there even through network shares while avoiding > duplication / broken link issues. Does this sound like something worth > doing / is there a better way to safely reference files from both inside > and outside postgres? How would you do that without breaking transactional integrity for blobs? There'd be no way to deal with multiple row versions in such a representation. 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] warning missing
"Gaetano Mendola" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: > > > Gaetano Mendola <[EMAIL PROTECTED]> writes: > > > >>I think a warning is missing if I create a table without OIDS that > >>inherits from a table with oids: > > > > > >>don't you think a warning shall to be raised here ? > > > > > > Nope ... this is not different from the behavior for merging duplicate > > column definitions. You get an OID column if the child table definition > > *or* any parent table requests OIDs. > > Mmm, you are not convincing me, don't we break the principle of minor surprise? > > I mean if you duplicate a column name that column is present on the inherited > table, but if I say: I don't want the column OID and I found it, this then > surprise me. > Speaking in generic OO terms, using inheritance, you cannot remove attributes that are present in the generalisation. If B inherits A, an instance of B is per definition also an instance of A. Thus, you must alwasy be able to cast a B into an A. In short, If you don't want the OID, you cannot inherit a something that has an OID. Having said that, I think a warning is motivated. The warning should state that attributes (columns) present in the generalisation (the parent table) cannot be hidden. Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] BLOBs and a virtual file system
Lately I've been planning work on a patch to postgres and linux on my system to allow access to BLOBs as a virtual filesystem, so I can see any file I put in there even through network shares while avoiding duplication / broken link issues. Does this sound like something worth doing / is there a better way to safely reference files from both inside and outside postgres? ---(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] COPY security fix
Bruce Momjian <[EMAIL PROTECTED]> writes: > Would someone remind me --- is the binary COPY security fix we did for > 7.4 also needed for earlier releases like 7.3.X? No, because there is no on-the-wire binary copy before 7.4, and copy from file is superuser-only anyway. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Weird NOT IN condition in SELECT (PostgreSQL
Richard Huxton said: > Ferruccio Zamuner wrote: >> Hi, >> >> I've following select, and I expect to receive a single record as result >> from it: >> >> select c.id >> from copie as c >> where c.enum=46857 and >> c.condizio_prestito = 'A' and >> c.id not in (select id_copia from testi_fermi_prenotati) and >> c.id not in (select id_copia from prestiti); >> >> but it doesn't give me any records at all. >> CREATE TABLE prestiti ( >> id_copia integer references copie(id) > >> CREATE TABLE testi_fermi_prenotati ( >> id_copia integer NOT NULL references copie(id), > > Are you sure you don't have any null values in prestiti.id_copia? Yes, there is a null value and it has not to be there. Thank you. Bye,\ferz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.5-dev, pg_dumpall, dollarquoting
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> pg_dumpall lacks the "-X disable-dollar-quoting" switch. > I can add it - do the other hackers want it? It should be there --- in general pg_dumpall should be able to pass down any pg_dump switch that makes sense. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] creating a cluster
On Jun 23, 2004, at 10:18 AM, David Garamond wrote: Alvaro Herrera wrote: On Mon, Jun 21, 2004 at 09:16:35PM -0400, Alexander Cohen wrote: Does anyone have any new ways to create clusters without using initdb or bootstrap mode? I need to be able to create one without those 2 things. Any ideas? initdb'ing somewhere else and copying the resulting directory? Btw, I've been doing this for a binary distribution on Windows (Cygwin) and Linux. Primarily because initdb-ing + doing a bunch of SQL commands to the db takes a long time on Cygwin. Seems fine so far. And how do you take care of users for your distribution. If you created the cluster on your computer, does it not have your user name as the main root user? That needs to be changed when copying over the cluster, how do i that? Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Weird NOT IN condition in SELECT (PostgreSQL 7.4.3
Ferruccio Zamuner wrote: Hi, I've following select, and I expect to receive a single record as result from it: select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti); but it doesn't give me any records at all. Following there is table descriptions: CREATE TABLE copie ( id serial NOT NULL primary key, CREATE TABLE prestiti ( id_copia integer references copie(id) CREATE TABLE testi_fermi_prenotati ( id_copia integer NOT NULL references copie(id), Are you sure you don't have any null values in prestiti.id_copia? -- Richard Huxton Archonet Ltd ---(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] creating a cluster
Alvaro Herrera wrote: On Mon, Jun 21, 2004 at 09:16:35PM -0400, Alexander Cohen wrote: Does anyone have any new ways to create clusters without using initdb or bootstrap mode? I need to be able to create one without those 2 things. Any ideas? initdb'ing somewhere else and copying the resulting directory? Btw, I've been doing this for a binary distribution on Windows (Cygwin) and Linux. Primarily because initdb-ing + doing a bunch of SQL commands to the db takes a long time on Cygwin. Seems fine so far. -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested)
Hi, I've following select, and I expect to receive a single record as result from it: select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti); but it doesn't give me any records at all. Following there is table descriptions: CREATE TABLE copie ( id serial NOT NULL primary key, enum integer, id_biblioteca integer NOT NULL, serie text, collocazione text, note text, condizio_prestito character(1) DEFAULT 'A'::bpchar, ctime date DEFAULT now(), mtime date, inventario integer ); CREATE TABLE prestiti ( id serial NOT NULL, inizio date DEFAULT now(), id_libro text, id_utente text, fine date, scadenza date NOT NULL, stato smallint DEFAULT 1, id_copia integer references copie(id) ); CREATE TABLE testi_fermi_prenotati ( id_copia integer NOT NULL references copie(id), id_prenotazione integer NOT NULL, fermato_il timestamp without time zone DEFAULT now() NOT NULL, scadenza timestamp without time zone, stato character(1) DEFAULT 'a'::bpchar ); and some usefull query results: prove=> select * from copie where enum=46857; id | enum | id_biblioteca | serie | collocazione | note | condizio_prestito | ctime| mtime | inventario ---+---+---+---+--+--+---++---+ 37163 | 46857 | 1 | | F RRN MAY| | A | 2004-05-03 | | 41576 (1 row) (SEE THE FOLLOWING QUERY AND RESULT) prove=> select * from prestiti where id_copia=37163; id | inizio | id_libro | id_utente | fine | scadenza | stato | id_copia ++--+---+--+--+---+-- (0 rows) prove=> select * from testi_fermi_prenotati where id_copia=37163; id_copia | id_prenotazione | fermato_il | scadenza | stato --+-++--+--- (0 rows) prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti); id (0 rows) prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati); id --- 37163 (1 row) prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from prestiti); id (0 rows) prove=> explain analyze select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti); QUERY PLAN - Seq Scan on copie c (cost=0.00..14587.17 rows=1 width=4) (actual time=15.82..15.82 rows=0 loops=1) Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) AND (subplan)) SubPlan -> Seq Scan on testi_fermi_prenotati (cost=0.00..0.00 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=1) -> Seq Scan on prestiti (cost=0.00..23.41 rows=1241 width=4) (actual time=0.01..10.21 rows=1241 loops=1) Total runtime: 15.95 msec (6 rows) prove=> explain select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti); QUERY PLAN -- Seq Scan on copie c (cost=0.00..14587.17 rows=1 width=4) Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) AND (subplan)) SubPlan -> Seq Scan on testi_fermi_prenotati (cost=0.00..0.00 rows=1 width=4) -> Seq Scan on prestiti (cost=0.00..23.41 rows=1241 width=4) (5 rows) If you want to play with these data: http://diff.homeunix.net/anomalia.sql.gz then gzip -d anomalias.sql createdb anydb psql -f anomalia.sql anydb Best wishes, \ferz ---(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] Putting OIDs etc back into pg_dump?
There was a recent post on -performance where someone had run pg_resetxlog and nuked their transaction counter. Whenever someone has filesystem-level problems like this they need to know: - "current" transaction ID - OIDs of system objects Of course, OIDs have been removed from a standard pg_dump so different installations can diff their schemas (and a good thing too). So - should this information be: 1. Logged nightly via standard logging procedures. 2. Stored in a format=custom dump but not for textual schemas. 3. Stored in a different file, that is updated on occasion 4. Not stored at all, it's pointless. 5. Not stored at all, PITR means this is academic now. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] COPY security fix
Would someone remind me --- is the binary COPY security fix we did for 7.4 also needed for earlier releases like 7.3.X? -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PITR Recovery
On Thu, 2004-06-17 at 22:47, Simon Riggs wrote: > On Wed, 2004-06-16 at 02:49, Tom Lane wrote: > > Simon Riggs <[EMAIL PROTECTED]> writes: > > > -finalaction refers to what to do when target is reached - the purpose > > > of this is to allow recovery of a database to occur when we don't have > > > enough space for all of the xlogs at once, so we need to do recovery in > > > batches. > > > > It seems to me that this is the only *essential* feature out of what > > you've listed, and the others are okay to add later. So I question > > your priorities: > > > > > In time for beta freeze, I think it is possible to do a limited subset > > > of the above: > > > - implement DATABASE only (whole instance, not specific database) > > > - implement END OF LOGS and TO TIMESTAMP > > > - implement THEN START only > > > - implement using simple C, rather than bison > > > > which seem to include everything except the one absolute must-have > > for any serious installation. > > > > OK. At first, I disagreed, for many reasons. > > I discussion with Bruce, I believe a fairly neat streaming solution is > possible. > > During recovery, as each request for a new xlog is made, we can make a > system(3) call to a user defined recovery_program to retrieve the next > xlog and out it in place. As each xlog is closed the file will be > removed. The result of this would be to stream the data files through > recovery, so no more than 1-2 files would ever be required to perform > what could be (and is touted as this by other vendors) an infinite > recovery. > > The result is that a backup tape (or other tape silo) could stream data > straight through to recovery, and would completely circumvent and > concern about insufficient disk space for recovery. > > This would involve changes to XLogFileOpen() in xlog.c and far less > complex than I had imagined such functionality could be. > > This could be specified to PostgreSQL by using: > - restore_program='cp %s %s' or similar > > I'll work more on the design, but not tonight. > Technically straightforward, though more complex I thought, but streaming the xlog files during recovery works in prototype - great idea Bruce and thanks for pushing for a solution in that area, Tom. [It looks like we do need to have a separate command file dedicated to recovery options, otherwise there's no way to tell difference between crash and full media recovery - but I'll lose the pompous syntax.] I'll include this (actually very few new/changed lines) and the xlog refactoring (lots of moved lines, but few changes) in a single patch. These changes are dependent upon, but otherwise independent of the PITR Archival path submitted on 15th. If anybody has comments on that patch, please pass them through ASAP, otherwise I may be building on sand. My plan is to get this out ASAP (tonight, hopefully), then build on it with a few extra tweaks, so we have a full set of options for PITR by 29th. Thanks, Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] PostgreSQL guru needed for Enterprise Groupware System
I just noticed this help wanted on SourceForge: http://sourceforge.net/people/viewjob.php?group_id=81764&job_id=18927 Maybe someone could give them a hand - project is pretty highly rated. 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] [pgsql-hackers-win32] initdb regression ?
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > Of Laurent Ballester > Sent: 22 June 2004 23:41 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: [pgsql-hackers-win32] initdb regression ? > > Hello, > > I am compile with msys and running a recent snapshot date on > june 22, setting PGDATA environment variable. After > initialise database with initdb (without parameters), I run > pgAdmin III and just after connect to the server I have an > error popup : ERROR column "datpath" does not exist. > > Postgresql tools like createdb or psql works correctly. > I run PostgreSQL and pgAdmin III on Windows platform. > > Any idea ? Yes, you need a CVS snapshot of pgAdmin (http://pgadmin.postgresql.org/snapshots/win32/) for 7.5 support. Regards, Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.5-dev, pg_dumpall, dollarquoting
Hi Stefan, since we have a lot of databases here that suffer from pg_dump's deficits in 7.3 and 7.4 regarding dependencies, we tried pg_dump from the upcoming 7.5 release. Would you be able to specify exactly the deficiences? It's my mission at the moment to make pg_dump 7.5 known-issue free :) This version works much better, but it is not possible to dump a complete cluster using pg_dumpall in a 7.3 or 7.4 compatible way because pg_dumpall lacks the "-X disable-dollar-quoting" switch. Would it be possible to modify pg_dumpall to accept the same commands as pg_dump (at least those that make sense) - or am I missing something here ? I can add it - do the other hackers want it? The main problem stefan is that 7.5 dump is seriously only guaranteed to restore to 7.5. You can use the 7.5 dump to upgrade to 7.5, but not to restore to 7.4. At the moment, dollar quoting is your main problem, but there's also the set default_with_oids stuff, plus I'm also hopefully soon going to fix the dumping owners issue, which will most likely make pg_dump 7.5 less likely to restore to 7.4... 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