Re: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
On Fri, 2002-08-09 at 06:07, Lamar Owen wrote: On Thursday 08 August 2002 05:36 pm, Nigel J. Andrews wrote: Matt Kirkwood wrote: I just spent some of the morning helping a customer build Pg 7.2.1 from source in order to get Linux largefile support in pg_dump etc. They possibly would have kept using the binary RPMs if they had this feature. And you added this by doing what, exactly? I'm not familiar with pg_dump largefile support as a standalone feature. As far as I can make out from the libc docs, largefile support is automatic if the macro _GNU_SOURCE is defined and the kernel supports large files. Is that a correct understanding? or do I actually need to do something special to ensure that pg_dump supports large files? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ...ask, and ye shall receive, that your joy may be full. John 16:24 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Locale number format confusion
On Thu, Aug 08, 2002 at 11:19:04PM +0200, Peter Eisentraut wrote: It seems we need a smart plan for handling the decimal point vs. comma issue. Observe: (lc_numeric = de_DE) ^^ It seems like hellish toy... :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] CREATE TEMP TABLE .... ON COMMIT
Attached is a revised version of my previous
ON COMMIT DROP
patch. This patch implements:
ON COMMIT { DROP | PRESERVE ROWS | DELETE ROWS }
The latter two are SQL99.
Sample usage:
---
template1=# begin;
BEGIN
template1=# create temp table a (a int) on commit drop;
CREATE
template1=# create temp table b (a int) on commit preserve rows;
CREATE
template1=# create temp table c (a int) on commit delete rows;
CREATE
template1=# insert into a values(1);
INSERT 24793 1
template1=# insert into b values(1);
INSERT 24794 1
template1=# insert into c values(1);
INSERT 24795 1
template1=# commit;
COMMIT
template1=# select * from a;
ERROR: Relation a does not exist
template1=# select * from b;
a
---
1
(1 row)
template1=# select * from c;
a
---
(0 rows)
template1=# create temp table a (a int) on commit drop;
ERROR: You must be inside a transaction to use ON COMMIT
---
Gavin
temprel6.diff.gz
Description: GNU Zip compressed data
temprel-doc.diff.gz
Description: GNU Zip compressed data
---(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: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
On Fri, 9 Aug 2002, Helge Bahmann wrote: As far as I can make out from the libc docs, largefile support is automatic if the macro _GNU_SOURCE is defined and the kernel supports large files. Is that a correct understanding? or do I actually need to do something special to ensure that pg_dump supports large files? in this case you still have to use large file functions in the code explicitly the easiest way to get large file support is to pass -D_FILE_OFFSET_BITS=64 to the preprocessor, and I think I remember doing this once for pg_dump see /usr/include/features.h There is some commentary on this in my /usr/doc/libc6/NOTES.gz, which I presume Oliver has already found since I found it after reading his posting. It gives a bit more detail that the header file for those who want to check this out. I for one was completely unaware of those 64 bit functions. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(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_stat_reset() weirdness
Christopher Kings-Lynne [EMAIL PROTECTED] writes: If you apply the pg_stat_reset() function patch you get this regression failure. Is this because it's returning a bool I guess? Shall I just fix the regression test to exclude this function? No, you should fix the function definition. The sanity checks are there for a reason. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Please, apply another patch to contrib/ltree
Fixed very stupid but important bug: mixing calls of some founctions from contrib/tsearch and contrib/ltree :) -- Teodor Sigaev [EMAIL PROTECTED] patch_ltree.gz Description: application/gzip ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SECURITY] DoS attack on backend possible (was: Re: [HACKERS][COMMITTERS] pgsql-server/src)
Florian Weimer [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: Thomas can correct me if I'm mistaken, but I believe these changes apply to the new integer datetime code No, it's possible to crash the backend in 7.2, too. And 7.2.1, of course. Let me ask again: Do you plan to address this in an update for 7.2.1? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
Hi Florian, Is it possible to crash a 7.2.1 backend without having an entry in the pg_hba.conf file? i.e. Is every PostgreSQL 7.2.1 installation around vulnerable to a remote DoS (or worse) from any user anywhere, at this moment in time? Regards and best wishes, Justin Clift Florian Weimer wrote: Florian Weimer [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: Thomas can correct me if I'm mistaken, but I believe these changes apply to the new integer datetime code No, it's possible to crash the backend in 7.2, too. And 7.2.1, of course. Let me ask again: Do you plan to address this in an update for 7.2.1? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(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] Wanted: RelationIsVisible interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 (Moved from Re: [PATCHES] small psql patch - show Schema name for \dt \dv \dS) I need something other than RelationIsVisible for psql for the case when someone says \d foo - I need to be able to decide which foo table I should display: pg_temp_1.foo, public.foo, greg.foo, Au contraire, RelationIsVisible is *exactly* what you need. I'm envisioning that where we currently have, say, select ... from pg_class p, ... where relname like 'foo%' and ... we'd write something like select ... from pg_class p, ... where relname like 'foo%' and pg_relation_is_visible(p.oid) and ... I see what you are saying, and this is fine for those cases in which the user has supplied the schema, but I don't see how it solves the ordering problem. If I have a table public.foo and greg.foo, and both are visible, how will psql know which one to show? It should be showing the first in the schema search path, in other words, the one that will get effected by a SQL statement such as SELECT * FROM foo; This is of course the original temp table problem with psql. At any rate, I will play with the RelationIsVisible stuff when somebody writes a SQL interface to it (or I write it myself, but that will take quite a long time :) Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200208091839 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE9VESovJuQZxSWSsgRAlHXAJkBqylRDegbwhNxsYr/CPIFYvnRQQCgxC0G VUDWfnprg1+pcKNZvE1gEzY= =w7hd -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Problem with lower() function
Hi, We have a problem with lower() function working differently for two
different data types
table: yuva_test
column_name data_type
yt_name1varchar(255)
yt_name2char(1)
The data is
yt_name1yt_name2
yuvaF
bharat F
1234556 F
234 F
etc.
When we run the query select * from yuva_test
lower(yt_name1)=lower('1234556') and lower(yt_name2)=lower('F') it takes
forever to return, but if try select * from yuva_test yt_name1='1234556'
and lower(yt_name2)=lower('F') it returns immediately.
What is happenning here? Why lower behaving differently for varchar(255) and
char(1), and how to make first query to work without changing schema.
Thanks
Yuva
---(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] Proposal for psql wildcarding behavior w/schemas
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's my thoughts on the matter: More than one period throws an error (something pleasant, mentioning that we do not quite support cross-database queries yet). I'll assume we are switching to the explicit wildcard system for this. (I think the proposed *? wildcards are a great idea). Basically, for those functions (esp. \d) that can return a list or a single item, the wildcard indicates that we want a list, and a lack of wildcard indicates we want a single item. \d foo.bazShows details about the table bar in the schema foo \d bazAttempts to find a visible baz in the schemas, shows first one that matches. \d baz* Shows all tables starting with baz, in all available schemas \d *.baz* Same as above \d baz.* Shows all tables in the baz schema \d foo.baz* Shows all tables starting with baz in the foo schema \d *.baz Similar to \d baz, but the wildcard forces all matches to be shown. \d foo*.baz Shows all tables named baz in schemas staring with foo \d *.*Same as plain old \d (which is actually a special case now) \d *oo. Matches all tables in schemas ending in oo \d *oo.* Same as above - a plain * on either side of the dot is usually superfluous, except that it forces a list of matches. The only one I have a problem with is the '\d baz' which some may argue should continue the current behavior and return details about the first baz table it encounters. On the other hand, people really need to start thinking about schemas and the consequences of having more than one table named baz. Still, typing in the schema could get tedious very quickly. Perhaps it should only return a list if more than one table was found, otherwise it will return information about that table. The good thing about the above is that \d and \dt will actually have the same functionality. The current behavior can be a bit confusing, in that some functions have implicit wildcards (\dt and friends) and some do not (\d). Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200208091849 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE9VEb6vJuQZxSWSsgRAsHNAKDCXz4vUY1A79EaVmfPIfcNS7VyxgCaA5Tx HrNmY0vITbb2G1fZFBCPgBM= =qhYp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Wanted: RelationIsVisible interface
Greg Sabino Mullane wrote: At any rate, I will play with the RelationIsVisible stuff when somebody writes a SQL interface to it (or I write it myself, but that will take quite a long time :) Looks like Tom just committed this; see: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/namespace.c.diff?r1=1.29r2=1.30 Datum pg_table_is_visible(PG_FUNCTION_ARGS); Datum pg_type_is_visible(PG_FUNCTION_ARGS); Datum pg_function_is_visible(PG_FUNCTION_ARGS); Datum pg_operator_is_visible(PG_FUNCTION_ARGS); Datum pg_opclass_is_visible(PG_FUNCTION_ARGS); They all accept an oid and return bool. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal: stand-alone composite types
Joe Conway writes: 3. Modify CREATE FUNCTION to allow the implicit creation of a dependent composite type, e.g.: Forgive this blunt question, but: Why? Of course I can see the answer, it's convenient, but wouldn't the system be more consistent overall if all functions and types are declared explicitly? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: stand-alone composite types
Peter Eisentraut wrote: Joe Conway writes: 3. Modify CREATE FUNCTION to allow the implicit creation of a dependent composite type, e.g.: Forgive this blunt question, but: Why? Now's a *great* time for a blunt question because I haven't started actively working on this yet. Much better than after I'm done. ;-) Of course I can see the answer, it's convenient, but wouldn't the system be more consistent overall if all functions and types are declared explicitly? And of couse you are correct. It is almost purely convenience. My reasoning was this: if I am creating a function which returns a composite type, then the fact that a named composite type exists is superfluous to me. It would be more natural for me to do: CREATE FUNCTION foo() RETURNS SETOF (f1 int, f2 text); than to do: CREATE TYPE some_arbitrary_name AS (f1 int, f2 text); CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name; But I admit it is only a nice-to-have, not a need-to-have. How do others feel? Do we want to be able to implicitly create a composite type during function creation? Or is it unneeded bloat? I prefer the former, but don't have a strong argument against the latter. Joe ---(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] Proposal: stand-alone composite types
I think it buys the same as SERIAL does for sequences. Is it likely to have more than one function using a complex type like that? If not, then allowing it's creation (not enforcing) could be useful. On Fri, 2002-08-09 at 19:03, Joe Conway wrote: Peter Eisentraut wrote: Joe Conway writes: 3. Modify CREATE FUNCTION to allow the implicit creation of a dependent composite type, e.g.: Forgive this blunt question, but: Why? Now's a *great* time for a blunt question because I haven't started actively working on this yet. Much better than after I'm done. ;-) Of course I can see the answer, it's convenient, but wouldn't the system be more consistent overall if all functions and types are declared explicitly? And of couse you are correct. It is almost purely convenience. My reasoning was this: if I am creating a function which returns a composite type, then the fact that a named composite type exists is superfluous to me. It would be more natural for me to do: CREATE FUNCTION foo() RETURNS SETOF (f1 int, f2 text); than to do: CREATE TYPE some_arbitrary_name AS (f1 int, f2 text); CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name; But I admit it is only a nice-to-have, not a need-to-have. How do others feel? Do we want to be able to implicitly create a composite type during function creation? Or is it unneeded bloat? I prefer the former, but don't have a strong argument against the latter. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: stand-alone composite types
Rod Taylor wrote: I think it buys the same as SERIAL does for sequences. That's a great analogy. Is it likely to have more than one function using a complex type like that? If not, then allowing it's creation (not enforcing) could be useful. That's what I was thinking. In cases where you want to use the type for several functions, use CREATE TYPE. If you only need the type for one function, let the function creation process manage it for you. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: stand-alone composite types
-Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED]] Sent: Friday, August 09, 2002 4:04 PM To: Peter Eisentraut Cc: pgsql-hackers Subject: Re: [HACKERS] Proposal: stand-alone composite types Peter Eisentraut wrote: Joe Conway writes: 3. Modify CREATE FUNCTION to allow the implicit creation of a dependent composite type, e.g.: Forgive this blunt question, but: Why? Now's a *great* time for a blunt question because I haven't started actively working on this yet. Much better than after I'm done. ;-) Of course I can see the answer, it's convenient, but wouldn't the system be more consistent overall if all functions and types are declared explicitly? And of couse you are correct. It is almost purely convenience. My reasoning was this: if I am creating a function which returns a composite type, then the fact that a named composite type exists is superfluous to me. It would be more natural for me to do: CREATE FUNCTION foo() RETURNS SETOF (f1 int, f2 text); than to do: CREATE TYPE some_arbitrary_name AS (f1 int, f2 text); CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name; Actually, the second looks a lot more natural to me. As in most programming languages, you define the type/class first, and then you can use the type/class as an object. Further, I don't want to have to remember the implementation details, unless I need to dig into them. So: CREATE TYPE locator AS (First_Name varchar, Last_Name varchar, City varchar, State_Province char(2), Country varchar); CREATE FUNCTION CustomerList(varchar specification) RETURNS SETOF locator; Seems far more natural and convenient to me. ---(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] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
On Fri, 9 Aug 2002, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: template1=# create temp table a (a int) on commit drop; ERROR: You must be inside a transaction to use ON COMMIT Surely that's only for ON COMMIT DROP, if you intend to offer the others? I should have provided details of this in the email. SQL99 details the baviour as follows: If TEMPORARY is specified and ON COMMIT is omitted, then ON COMMIT DELETE ROWS is implicit This might give users a bit of a surprise so the effective behaviour is ON COMMIT PRESERVE ROWS. As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. regards, tom lane Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: stand-alone composite types
Is it likely to have more than one function using a complex type like that? If not, then allowing it's creation (not enforcing) could be useful. That's what I was thinking. In cases where you want to use the type for several functions, use CREATE TYPE. If you only need the type for one function, let the function creation process manage it for you. So long as the type dissapears with the drop of the function. But don't make stuff you don't clean up :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem with lower() function
Thank you, we figured out that, the query with lower() is taking too long
and we thought it is having some problem. The actual table in our database
has more than 10 million rows and query is taking too long.
Thanks
Yuva
-Original Message-
From: Yuva Chandolu
Sent: Friday, August 09, 2002 3:41 PM
To: '[EMAIL PROTECTED]'
Subject: [HACKERS] Problem with lower() function
Hi, We have a problem with lower() function working differently for two
different data types
table: yuva_test
column_name data_type
yt_name1varchar(255)
yt_name2char(1)
The data is
yt_name1yt_name2
yuvaF
bharat F
1234556 F
234 F
etc.
When we run the query select * from yuva_test
lower(yt_name1)=lower('1234556') and lower(yt_name2)=lower('F') it takes
forever to return, but if try select * from yuva_test yt_name1='1234556'
and lower(yt_name2)=lower('F') it returns immediately.
What is happenning here? Why lower behaving differently for varchar(255) and
char(1), and how to make first query to work without changing schema.
Thanks
Yuva
---(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
---(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] strange performance anomalies
Hi, We recently put up a new 7.2.1 installation on Solaris 8 that serves a 24x7 e-commerce site. The system seems to run pretty well most of the time but we see a consistent form of performance anomaly. Watching pg_stat_activity the system spends most of it's time running smoothly with queries clearing through sub-second. We have a production job we run which immediately sent the site into a tailspin though. Starting that job caused hundreds of select statements to queue up in the pg_stat_activity view. This seems odd since MVCC would lead us to believe that shouldn't happen. Readers shouldn't block wholesale like that unless we're using DDL on the table or doing a vacuum per the online docs at http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/locking-tables.html Nevertheless, turning off foreign key constraint checking via: update pg_class set reltriggers = 0 where relname = tablename cleared up the problem, load fell off to normal in seconds. So how is it that fk contraints apparently cause what look like table-level locks? Or, at the very least, cause a heck of a lot of select statements to go into a holding pattern for some reason? (On a side note, it seems the current locking behavior might also violate the I in ACID by causing an independent delete transaction to actually see or be affected by the content of the transaction using FK constraint locking before it's clear that the transaction will commit). At any rate, being somewhat new to tuning at this load level for PG I'm not sure if I'm supposed to be tinkering with max_lock_per_transaction here. Could this be evidence of a lock starvation issue or something? Guessing here and any input would be appreciated. Thanks in advance! ss ---(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] Proposal: stand-alone composite types
That's what I was thinking. In cases where you want to use the type for several functions, use CREATE TYPE. If you only need the type for one function, let the function creation process manage it for you. It would be nice then to have some mechanism for converting the automatic type to a named type which could be used elsewhere. Otherwise one would need to garbage collect the separate stuff later, which would probably go into the not so convenient category of features... - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: stand-alone composite types
Thomas Lockhart wrote: That's what I was thinking. In cases where you want to use the type for several functions, use CREATE TYPE. If you only need the type for one function, let the function creation process manage it for you. It would be nice then to have some mechanism for converting the automatic type to a named type which could be used elsewhere. Otherwise one would need to garbage collect the separate stuff later, which would probably go into the not so convenient category of features... Well I think that could be handled with the new dependency tracking system. Same as the SERIAL/sequence analogy -- when you drop the function, the type would automatically and transparently also get dropped. Joe ---(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] PITR, checkpoint, and local relations
Tom Lane wrote: J. R. Nield [EMAIL PROTECTED] writes: Uh, why? Why not just force a checkpoint and remember the exact location of the checkpoint within the current log file? If I do a backup with PITR and save it to tape, I need to be able to restore it even if my machine is destroyed in a fire, and all the logs since the end of a backup are destroyed. And for your next trick, restore it even if the backup tape itself is destroyed. C'mon, be a little reasonable here. The backups and the log archive tapes are *both* critical data in any realistic view of the world. Tom, just because he doesn't agree with you doesn't mean he is unreasonable. I think it is an admirable goal to allow the PITR backup to restore a consistent copy of the database _without_ needing the logs. In fact, I consider something that _needs_ the logs to restore to a consistent state to be broken. If you are doing offsite backup, which people should be doing, requiring the log tape for restore means you have to recycle the log tape _after_ the PITR backup, and to restore to a point in the future, you need two log tapes, one that was done during the backup, and another current. If you can restore the PITR backup without a log tape, you can take just the PITR backup tape off site _and_ you can recyle the log tape _before_ the PITR backup, meaning you only need one tape for a restore to a point in the future. I think there are good reasons to have the PITR backp be restorable on its own, if possible. -- 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] [COMMITTERS] pgsql-server/src backend/tcop/postgres.cbacke
Thomas, would you remind me of the concusions because I thought everyone involved felt that it should be an initdb-only option, but I still see it in CVS. --- Thomas Lockhart wrote: Thomas, have you commented on the objections to this patch? If so, I didn't see it. Yes, there was quite a long thread on this. - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Please, apply another patch to contrib/ltree
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Teodor Sigaev wrote: Fixed very stupid but important bug: mixing calls of some founctions from contrib/tsearch and contrib/ltree :) -- Teodor Sigaev [EMAIL PROTECTED] [ application/gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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] [COMMITTERS] pgsql-server/src backend/tcop/postgres.cbacke
Thomas, would you remind me of the concusions because I thought everyone involved felt that it should be an initdb-only option, but I still see it in CVS. ?? Concussions as in brain bruises? ;) I'm not sure I understand the question. I assume that we are talking about the WAL log location feature I implemented recently. It is an initdb-only option, and defaults to the current behavior *exactly*. The new feature is to allow an argument to initdb to locate the WAL file to another location. That location can be specified on the command line, or through an environment variable. Neither form precludes use of the other, and either form can be considered best practice depending on your opinion of what that is. The postmaster also recognizes the command line option and environment variable. The only suggestion I got as an alternative involved soft links, which is not portable, which is not robust, and which is not used anywhere else in the system. If we moved toward relying on soft links for distributing resources we will be moving in the wrong direction for many reasons, some of which I've mentioned previously. GUC parameters were also mentioned as a possibility, and the infrastructure does not preclude that at any time. I don't recall that there were very many folks involved. There were several opinions, though most were from folks who were not thinking of implementing disk management features. Some opinions dealt with details, and some seemed to deal with the wisdom of allowing anything other than a one partition model of the database, which is nothing if not short sighted. Current default behavior is as first implemented, and the new feature allows locating the WAL logs in another area. For the current state of the art, that seems competitive with features found in other database products, and an essential step in teaching PostgreSQL to work with very large databases. I had thought to extend the capabilities to allow resource allocation for individual tables and indices, which has *long* been identified as a desired capability by folks who are managing large systems. It seemed reasonable to have done in time for 7.3. I'm rethinking that, not because it shouldn't happen, but because the process of discussing these issues has become so argumentative, divisive, impolite, and unpleasant. Which is a shame imho... - Thomas ---(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] Wanted: RelationIsVisible interface
OK, let's get started updating all our db access routines. We only have 22 days left until beta. --- Joe Conway wrote: Greg Sabino Mullane wrote: At any rate, I will play with the RelationIsVisible stuff when somebody writes a SQL interface to it (or I write it myself, but that will take quite a long time :) Looks like Tom just committed this; see: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/catalog/namespace.c.diff?r1=1.29r2=1.30 Datum pg_table_is_visible(PG_FUNCTION_ARGS); Datum pg_type_is_visible(PG_FUNCTION_ARGS); Datum pg_function_is_visible(PG_FUNCTION_ARGS); Datum pg_operator_is_visible(PG_FUNCTION_ARGS); Datum pg_opclass_is_visible(PG_FUNCTION_ARGS); They all accept an oid and return bool. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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
Re: [HACKERS] Proposal: stand-alone composite types
Joe Conway wrote: Thomas Lockhart wrote: That's what I was thinking. In cases where you want to use the type for several functions, use CREATE TYPE. If you only need the type for one function, let the function creation process manage it for you. It would be nice then to have some mechanism for converting the automatic type to a named type which could be used elsewhere. Otherwise one would need to garbage collect the separate stuff later, which would probably go into the not so convenient category of features... Well I think that could be handled with the new dependency tracking system. Same as the SERIAL/sequence analogy -- when you drop the function, the type would automatically and transparently also get dropped. All this type extension stuff is complex. If we can make it easier for people to get started with it, we should. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql-server/src backend/tcop/postgres.cbacke
Thomas Lockhart wrote: Thomas, would you remind me of the concusions because I thought everyone involved felt that it should be an initdb-only option, but I still see it in CVS. ?? Concussions as in brain bruises? ;) Uh, conclusions. Sorry. New keyboard desk in new house. :-) I'm not sure I understand the question. I assume that we are talking about the WAL log location feature I implemented recently. It is an initdb-only option, and defaults to the current behavior *exactly*. Yep. What bothers me is the clutter to the other commands that allow XLOG location specification when you would never really want to specify it except as part of initdb. I just see those extra flags as cruft/confusion. Look at pg_ctl: pg_ctl start [-w] [-D DATADIR] [-s] [-X PGXLOG] [-l FILENAME] [-o OPTIONS] Which option doesn't make sense? -X. It is way beyond the functionality of the command. The new feature is to allow an argument to initdb to locate the WAL file to another location. That location can be specified on the command line, or through an environment variable. Neither form precludes use of the other, and either form can be considered best practice depending on your opinion of what that is. The postmaster also recognizes the command line option and environment variable. The only suggestion I got as an alternative involved soft links, which is not portable, which is not robust, and which is not used anywhere else in the system. If we moved toward relying on soft links for distributing resources we will be moving in the wrong direction for many reasons, some of which I've mentioned previously. GUC parameters were also mentioned as a possibility, and the infrastructure does not preclude that at any time. I don't think anyone agreed with your concerns about symlinks. If you want to be careful, do the ln -s in initdb and exit on failure, and tell them not to use -X on that platform, though we use symlinks for postmaster/postgres identification, so I know the only OS that doesn't support symlinks is Netware, only because Netware folks just sent in a patch to add a -post flag to work around lack of symlinks. (I have asked for clarification from them.) I actually requested a vote, and got several people who wanted my compromise (PGXLOG or initdb -X flag only), and I didn't see anyone who liked the addition of -X into non-initdb commands. Should I have a specific vote? OK, three options: 1) -X, PGXLOG in initdb, postmaster, postgres, pg_ctl 2) -X, PGXLOG in initdb only 3) nothing I remember a number of people liking 2, but we can vote again. I don't recall that there were very many folks involved. There were several opinions, though most were from folks who were not thinking of implementing disk management features. Some opinions dealt with details, and some seemed to deal with the wisdom of allowing anything other than a one partition model of the database, which is nothing if not short sighted. Current default behavior is as first implemented, and the new feature allows locating the WAL logs in another area. For the current state of the art, that seems competitive with features found in other database products, and an essential step in teaching PostgreSQL to work with very large databases. I had thought to extend the capabilities to allow resource allocation for individual tables and indices, which has *long* been identified as a desired capability by folks who are managing large systems. It seemed reasonable to have done in time for 7.3. I'm rethinking that, not because it shouldn't happen, but because the process of discussing these issues has become so argumentative, divisive, impolite, and unpleasant. Which is a shame imho... I clearly want tablespaces, and it would be great for 7.3, and I don't think it is a huge job. However, I think it will require symlinks to be usable, and you probably do not, so it may be an issue. As for the argumentativeness, we do have folks with some strong opinions, and I guess on the PGXLOG issue, I am one of them. Maybe that is bad? Are people expressing themselves badly? If so, I would like to hear details either on list or privately so I can address them. -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Wanted: RelationIsVisible interface
Greg Sabino Mullane [EMAIL PROTECTED] writes: we'd write something like select ... from pg_class p, ... where relname like 'foo%' and pg_relation_is_visible(p.oid) and ... I see what you are saying, and this is fine for those cases in which the user has supplied the schema, but I don't see how it solves the ordering problem. If I have a table public.foo and greg.foo, and both are visible, how will psql know which one to show? By definition, only one can be visible: a table named foo hides any other foo that's later in the search path. This is exactly the point that the is_visible tests cover, while there isn't any easy way to handle it in SQL by comparison of a relnamespace value to a current_schemas list --- that comparison doesn't tell you anything about possible conflicting names elsewhere in the search path. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: stand-alone composite types
Peter Eisentraut [EMAIL PROTECTED] writes: Joe Conway writes: 3. Modify CREATE FUNCTION to allow the implicit creation of a dependent composite type, e.g.: Forgive this blunt question, but: Why? Of course I can see the answer, it's convenient, but wouldn't the system be more consistent overall if all functions and types are declared explicitly? I was wondering about that too, in particular: what name are you going to give to the implicit type, and what if it conflicts? The already-accepted mechanism for anonymous function-result types for RECORD functions doesn't have that problem, because it has no need to create a catalog entry for the anonymous type. But I'm not sure what to do for record types that need to be present in the catalogs. 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] Proposal for psql wildcarding behavior w/schemas
Greg Sabino Mullane [EMAIL PROTECTED] writes: Here's my thoughts on the matter: More than one period throws an error (something pleasant, mentioning that we do not quite support cross-database queries yet). The just-committed implementation doesn't throw an error, but silently discards name fragments to the left of the last two --- for example, foo.bar.baz is silently treated as bar.baz. This could probably be improved, but I haven't quite figured out how psql deals with error recovery... Otherwise I agree with your comments, except for \d baz* Shows all tables starting with baz, in all available schemas Make that shows visible tables whose names begin with baz. There is a subtle difference. \d *.baz* Same as above This shows tables whose names begin with baz, in any schema in the database --- without regard to visibility. \d *.*Same as plain old \d (which is actually a special case now) \d without an argument is still a special case: it transforms to \dtvs with no argument. Other than that little usability kluge, the general rule is that for any object-type x, \dx is the same as \dx *, which is *not* the same as \dx *.* ... the former shows all visible objects, the latter all objects in the database. The current behavior can be a bit confusing, in that some functions have implicit wildcards (\dt and friends) and some do not (\d). As of cvs tip, all the \d family take wildcards. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
Joe Conway wrote:
more work than I had time for when I wrote the current replace(). But as
I said, if there is support for getting this into the backend, I'll add
it to my todo list:
- Create new backend function replace()
- Either create new backend functions, or merge into existing functions:
to_hex() and extract_tok()
I'm just starting to take a look at this again. While studying the
current text_substr() function I found two behaviors which conflict with
specific SQL92/SQL99 requirements, and one bug. First the spec
compliance -- SQL92 section 6.7/SQL99 section 6.18 say:
If character substring function is specified, then:
a) Let C be the value of the character value expression, let LC be the
length of C, and let S be the value of the start position.
b) If string length is specified, then let L be the value of string
length and let E be S+L. Otherwise, let E be the larger of LC + 1
and S.
c) If either C, S, or L is the null value, then the result of the
character substring function is the null value.
d) If E is less than S, then an exception condition is raised: data
exception-substring error.
e) Case:
i) If S is greater than LC or if E is less than 1, then the result of
the character substring function is a zero-length string.
ii) Otherwise,
1) Let SI be the larger of S and 1. Let El be the smaller of E and
LC+l. Let Ll be El-Sl.
2) The result of the character substring function is a character
string containing the Ll characters of C starting at character
number Sl in the same order that the characters appear in C.
The only way for d) to be true is when L 0. Instead of an error, we do:
test=# select substr('hello',2,-1);
substr
ello
(1 row)
The other spec issue is wrt para e)i). If E (=S+L) 1, we should return
a zero-length string. Currently I get:
test=# select substr('hello',-4,3);
substr
hello
(1 row)
Neither behavior is documented (unless it's somewhere other than:
http://developer.postgresql.org/docs/postgres/functions-string.html ).
The bug is this one:
test=# create DATABASE testmb with encoding = 'EUC_JP';
CREATE DATABASE
test=# \c testmb
You are now connected to database testmb.
testmb=# select substr('hello',6,2);
substr
~
(1 row)
testmb=# \c test
You are now connected to database test.
test=# select substr('hello',6,2);
substr
(1 row)
The multibyte database behavior is the bug. The SQL_ASCII behavior is
correct (zero-length string):
test=# select substr('hello',6,2) is null;
?column?
--
f
(1 row)
Any objection if I rework this function to meet SQL92 and fix the bug?
Or is the SQL92 part not desirable because it breaks backward
compatability?
In any case, can the #ifdef MULTIBYTE's be removed now in favor of a
test for encoding max length?
Joe
---(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] Proposal: stand-alone composite types
Tom Lane wrote: I was wondering about that too, in particular: what name are you going to give to the implicit type, and what if it conflicts? The already-accepted mechanism for anonymous function-result types for RECORD functions doesn't have that problem, because it has no need to create a catalog entry for the anonymous type. But I'm not sure what to do for record types that need to be present in the catalogs. I was intending to use the same naming method used for SERIAL sequences. But since the poll from this afternoon only showed weak support and relatively strong objections, I'm OK with putting this aside for now. If enough people seem interested once they start using table functions in 7.3, we can always resurrect this idea. The most important changes (IMHO) were the anonymous type and CREATE TYPE x AS() pieces anyway, so I'm happy where we are (at least once the stand-alone composite type patch is applied ;) ). Onward and upward... Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] CREATE TEMP TABLE .... ON COMMIT
Gavin Sherry [EMAIL PROTECTED] writes: As for your question (and, perhaps, SQL99) I don't seen how it makes any sense to specify ON COMMIT outside of a transaction block. Surely it does. CREATE TEMP TABLE foo(...) ON COMMIT DELETE ROWS; BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again BEGIN; insert some rows in foo; process rows in foo; COMMIT; -- foo is now empty again repeat until application quit... What am I missing? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Linux Largefile Support In Postgresql RPMS
Lamar Owen wrote: And you added this by doing what, exactly? I'm not familiar with pg_dump largefile support as a standalone feature. Enabling largefile support for the utilities was accomplished by : CFLAGS=-O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 ./configure ... It seemed to me that the ability to dump databases 2G without gzip, split etc was a good thing. What do you think ? You have this wrong. The distributions do periodically sync up with my revision, and I with theirs, but they do their own packaging. I see so if you enabled such support, they they would probably sync that too ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Linux Largefile Support In Postgresql RPMS
Ralph Graulich wrote: Hi, just my two cents worth: I like having the files sized in a way I can handle them easily with any UNIX tool on nearly any system. No matter wether I want to cp, tar, dump, dd, cat or gzip the file: Just keep it at a maximum size below any limits, handy for handling. Good point... however I was thinking that being able to dump the entire database without resporting to gzips and splits was handy... For example, Oracle suggests it somewhere in their documentation, to keep datafiles at a reasonable size, e.g. 1 GB. Seems right to me, never had any problems with it. Yep, fixed or controlled sizes for data files is great... I was thinking about databases rather than data files (altho I may not have made that clear in my mail) best wishes Mark ---(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
