Re: [HACKERS] 2-phase commit
Bruce Momjian [EMAIL PROTECTED] writes: From our previous discussion of 2-phase commit, there was concern that the failure modes of 2-phase commit were not solvable. However, I think multi-master replication is going to have similar non-solvable failure modes, yet people still want multi-master replication. No. The real problem with 2PC in my mind is that its failure modes occur *after* you have promised commit to one or more parties. In multi-master, if you fail you know it before you have told the client his data is committed. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Trouble with error message encoding
I have encoding problems using translated error messages (7.4beta1). When database encoding is set to SQL_ASCII, all mesages arrive to client correctly respecting the CLIENT_ENCODING, but if I create database WITH ENCODING='unicode' or WITH ENCODING='latin2', messages are displayed correctly only when CLIENT_ENCODING is same as database encoding. I checked, and this is working this way also in 7.3. Is that known problem, or maybe I'm doing something wrong? Regards ! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] TCP/IP with 7.4 beta2 broken?
Bruce Momjian said: Are all the IPv6 issues resolved in current CVS? This one appears to be, at any rate. cheers andrew - -- Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. Well, like I said, it's not real pretty. But the same is already true of postgresql.conf.sample --- initdb edits that. I don't see that having it edit pg_hba.conf.sample too is so bad. What if some clever installer/administrator deliberately alters their installed sample file? I don't think it would hurt them. The editing will consist of a sed script to comment or uncomment the line containg ::1, it wouldn't touch anything else. Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. Bruce and I talked about that alternative too, but we felt that it made more sense to keep the processing of pg_hba.conf.sample parallel to what happens to postgresql.conf.sample. Further down the road we might need initdb-time checks to decide what to do to the sample file, just as we already need for postgresql.conf.sample. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is it a memory leak in PostgreSQL 7.4beta?
I said: This doesn't seem to quite square with your explanation though --- surely the number should go to 8000 and change? The man page for top says these numbers are in kilobytes ... but if they were really measured in, say, 4K pages, then we'd be talking about 26M of shared memory touched, which might be plausible when you consider shared libraries. Never mind --- further testing shows that top does report in kilobytes. I made a silly mistake in writing my test query that prevented it from using as many buffers as I expected. When I write something that really does use all 1000 buffers, SHARE goes to 10392, which is right about what you'd expect. So I think this mystery is solved. Back to chasing real bugs ... 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
[HACKERS] Vote: Adding flex/bison derived files in WIN32_DEV
Because MinGW/Msys doesn't come with flex/bison by default, I have added those derived files to the WIN32_DEV branch in CVS. It makes it easier for people to install _just_ MinGW and compile PostgreSQL on Win32. The branch will live for only 1-2 months until we start 7.5 development. Those files will not be moved into the main branch. Should those files be in WIN32_DEV CVS, or should they be removed, and require people to install bison/flex on MinGW. Even though they will be updated infrequently, there is concern about CVS file bloat. -- 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 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] TCP/IP with 7.4 beta2 broken?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Are all the IPv6 issues resolved in current CVS? AFAIK, yes ... but I don't run IPv6 here, so I might not be the best authority on the subject ... Completed unless more problem reports arrive --- that's great. -- 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] TCP/IP with 7.4 beta2 broken?
Hi, I just checked out the current CVS-version and everything is fine now on my SuSE 8.2-box. I set tcpip_socket in postgresql.conf to true and was able to connect to localhost and 127.0.0.1. I added my local IPv4-netaddress to pg_hba.conf and was able to connect to my networkadress. Thank you. Tommi Am Mittwoch, 10. September 2003 06:21 schrieb Bruce Momjian: Are all the IPv6 issues resolved in current CVS? --- Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: OK, now we are getting somewhere. I see that this would work. It's a bit ugly, though - with this plan the sample file in both CVS and the installation won't necessarily be what actually get put in place. Well, like I said, it's not real pretty. But the same is already true of postgresql.conf.sample --- initdb edits that. I don't see that having it edit pg_hba.conf.sample too is so bad. What if some clever installer/administrator deliberately alters their installed sample file? I don't think it would hurt them. The editing will consist of a sed script to comment or uncomment the line containg ::1, it wouldn't touch anything else. Could we get the configure script to do it instead, since it too should know about ip6 capability? (I guess then we'd have pg_hba.conf.sample.in). That strikes me as being a lot cleaner. Bruce and I talked about that alternative too, but we felt that it made more sense to keep the processing of pg_hba.conf.sample parallel to what happens to postgresql.conf.sample. Further down the road we might need initdb-time checks to decide what to do to the sample file, just as we already need for postgresql.conf.sample. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(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] Vote: Adding flex/bison derived files in WIN32_DEV
On Wed, 10 Sep 2003, Bruce Momjian wrote: Because MinGW/Msys doesn't come with flex/bison by default, I have added those derived files to the WIN32_DEV branch in CVS. It makes it easier for people to install _just_ MinGW and compile PostgreSQL on Win32. The branch will live for only 1-2 months until we start 7.5 development. Those files will not be moved into the main branch. Should those files be in WIN32_DEV CVS, or should they be removed, and require people to install bison/flex on MinGW. Even though they will be updated infrequently, there is concern about CVS file bloat. I think a more appropriate question is how many ppl are working on WIN32_DEV *from* CVS that don't have flex/bison available ... if nobody, having those files in CVS is *really* useless and the whole argument is moot ... ---(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] Stats Collector Error 7.4beta1 and 7.4beta2
Alvaro Herrera [EMAIL PROTECTED] writes: On Wed, Sep 10, 2003 at 07:27:02AM -0400, Andrew Dunstan wrote: If someone can spoof the packet address isn't there also a possibility that they can read your packets and see your random signature? Spoofing the packet source address is not quite the same as sniffing a connection, which should be encrypted if you do not trust your environment AFAIU. Remember this is a local-loopback connection; the packets will never leave your own kernel. If the attacker can sniff the packets then he is already into your kernel, in which case game over. But depending on how careful your kernel is, it's possible that an attacker who doesn't yet own your machine could inject forged packets with a local source address. So I think that indeed there are scenarios where a random-signature check would be more secure than a source-address check. The question is whether any of this is worth worrying about in PG. ISTM the correct solution to such a risk is to tighten your kernel's packet filtering, not harden one piece of one application. 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] 2-phase commit
From our previous discussion of 2-phase commit, there was concern that the failure modes of 2-phase commit were not solvable. However, I think multi-master replication is going to have similar non-solvable failure modes, yet people still want multi-master replication. No. The real problem with 2PC in my mind is that its failure modes occur *after* you have promised commit to one or more parties. In multi-master, if you fail you know it before you have told the client his data is committed. Hmm ? The appl cannot take the first phase commit as its commit info. It needs to wait for the second phase commit. The second phase is only finished when all coservers have reported back. 2PC is synchronous. The problems with 2PC are when after second phase commit was sent to all servers and before all report back one of them becomes unreachable/down ... (did it receive and do the 2nd commit or not) Such a transaction must stay open until the coserver is reachable again or an administrator committed/aborted it. It is multi master replication that usually has an asynchronous mode for performance, and there the trouble starts. Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Vote: Adding flex/bison derived files in WIN32_DEV
Marc G. Fournier wrote: On Wed, 10 Sep 2003, Bruce Momjian wrote: Because MinGW/Msys doesn't come with flex/bison by default, I have added those derived files to the WIN32_DEV branch in CVS. It makes it easier for people to install _just_ MinGW and compile PostgreSQL on Win32. The branch will live for only 1-2 months until we start 7.5 development. Those files will not be moved into the main branch. Should those files be in WIN32_DEV CVS, or should they be removed, and require people to install bison/flex on MinGW. Even though they will be updated infrequently, there is concern about CVS file bloat. I think a more appropriate question is how many ppl are working on WIN32_DEV *from* CVS that don't have flex/bison available ... if nobody, having those files in CVS is *really* useless and the whole argument is moot ... I don't know. We would have to ask on the Win32 list, but the files were added specifically because several people asked about those missing files, and didn't/couldn't get bison/flex. Now that we have snapshots, I don't know how many have switched to those. -- 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 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] massive quotes?
Bruce Momjian wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. --- Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Regards, Andreas ---(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] massive quotes?
On Wed, Sep 10, 2003 at 07:04:13PM +0200, Andreas Pflug wrote: Bruce Momjian wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Oh, is it? Didn't people agree that other frontends (pgAdmin, phpPgAdmin, etc) have solutions for the problem already? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) In a specialized industrial society, it would be a disaster to have kids running around loose. (Paul Graham) ---(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] Trouble with error message encoding
Darko Prenosil writes: I have encoding problems using translated error messages (7.4beta1). When database encoding is set to SQL_ASCII, all mesages arrive to client correctly respecting the CLIENT_ENCODING, but if I create database WITH ENCODING='unicode' or WITH ENCODING='latin2', messages are displayed correctly only when CLIENT_ENCODING is same as database encoding. I checked, and this is working this way also in 7.3. Is that known problem, or maybe I'm doing something wrong? In general, the server encoding is S, the client encoding is C, and the messages are stored (in the source, or in the PO files) in encoding M. When the server sends a message to the client, it tries to convert a string of encoding M, thinking it is in encoding S, to encoding C. So, yes, there is a problem, but it's not easy to fix. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] massive quotes?
Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 07:04:13PM +0200, Andreas Pflug wrote: Bruce Momjian wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Oh, is it? Didn't people agree that other frontends (pgAdmin, phpPgAdmin, etc) have solutions for the problem already? I think there is agreement that these do. It would still look ugly in a programmatic interface like JDBC. Not that I use JDBC to set up functions, but I can imagine someone wanting to. But personally I could live with a nice enough psql-only fix. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Unixware Patch (Was: Re: Beta2 Tag'd and Bundled ...)
Philip Yarra [EMAIL PROTECTED] writes: On Wed, 10 Sep 2003 02:15 pm, Bruce Momjian wrote: This would be a pretty short list unless I count wrong! This excludes all releases of FreeBSD (and I'm willing to bet other BSDs), Solaris (at least the old version I have), OSF, Linux, and who knows what else? MacOS X? Uhm I stopped reading this thread a while back. Linux has all the reentrant functions required like strerror_r, getpwnam_r, etc. Why do we think it wouldn't pass? Are these non-threadsafe functions really going to be so heavily-used that we can't live with the wrappers? I mean, AFAIK these threading issues are only in ECPG and libpq - it's not like re-writing the backend code is required. It's only libpq and ECPG where thread-safety is at all an issue. -- greg ---(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] Notices for redundant operations
I wrote: I found a few notices and warnings that inform you that the command you are executing has no effect because the object is already in the state you want it. I think these are useless, and there is also some inconsistency. Does someone want to defend keeping them? I take it that people have grown to agree that these notices aren't really useful. So this is the last call before they'll disappear. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Stats Collector Error 7.4beta1 and 7.4beta2
On Wed, Sep 10, 2003 at 12:49:31 -0400, Tom Lane [EMAIL PROTECTED] wrote: The question is whether any of this is worth worrying about in PG. ISTM the correct solution to such a risk is to tighten your kernel's packet filtering, not harden one piece of one application. On linux at least, it is pretty easy to make sure packets claiming to be from loopback are dropped if they don't come in on the loopback interface. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] massive quotes?
On Wed, Sep 10, 2003 at 07:04:13PM +0200, Andreas Pflug wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Oh, is it? Didn't people agree that other frontends (pgAdmin, phpPgAdmin, etc) have solutions for the problem already? I would really prefer a general SQL block quoting mechanism. Although I can use Perl to escape all the quotes in a function block, it'd be really nice to be able to do everything in SQL. May I bring up here documents again? They have the advantage over the COPY-like mechanism of being general, e.g.: INSERT INTO sometable (field1, field2) VALUES (1234, EOF A really long text block's place in the world EOF ); as well as being very nice in a function definition. What do others think of that? Jon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] quirk of array type processing
I'm not sure if this should be considered a bug or not. At least in my case it doesn't cause a problem. I think it could always be worked around with explicit casts in any case, it just seems... wrong: db= create or replace function array_length(integer[]) returns integer as 'SELECT array_upper($1)-array_lower($1)+1' language sql strict immutable; ERROR: function array_upper(integer[]) does not exist HINT: No function matches the given name and argument types. You may need to add explicit typecasts. db= create or replace function array_length(anyarray) returns integer as 'SELECT array_upper($1)-array_lower($1)+1' language sql strict immutable; CREATE FUNCTION -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] quirk of array type processing
On second thought, it does cause a problem: db= create table aa (aa integer[]); CREATE TABLE db= select distinct(array_length(aa)) from aa; ERROR: function array_upper(integer[]) does not exist HINT: No function matches the given name and argument types. You may need to add explicit typecasts. CONTEXT: SQL function array_length during inlining -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] How to install and unistall
I've notice when trying to uninstall by using the command gmake uninstall, I still could access my database, and also all my directories of postgresql and bin files are still there! I am not sure if I should first drop my database, but even when I did this all my postgres directoires remain there. Any suggestions?? __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Broken(?) 'interval' problems. [Was: ISO 8601 Time Intervals]
Tom wrote: At this point it should move to pghackers, I think. (responding to a patch for ISO 8601 Time Intervals in pgsql-patches) Looks like I'll take a shot at more broadly hacking the postgresql time interval code. Before doing so, I wanted to ask opinions regarding what the right behavior is of various timestamp/interval operations. I think the best way ask the specific questions is to ask a quiz highlighting some of the unexpected behavior with the current implementation. 1. What should this expression give: select '0.01 years'::interval '0.01 months'::interval; A) False- the first is 0 months, the second is about 25000 seconds. B) True - one is about 30 seconds, the other is about 25000. C) An error - fractional dates are asking for trouble. D) Something else -- please tell me. 2. If I have this expression: select '2003-01-31'::timestamp + '2 months', '2003-01-31'::timestamp + '1 month' + '1 month' '2003-01-31'::timestamp + '0.5 months'::interval * 4; would I expect the results to: A) All be different. The first is 89 days, (Mar 31, because it's the last day of Mar). the second86 days, (Mar 28, because February clips the date) and the third 90 days (Apr 01, because half-months are 15 days). B) All should be the same. Two months is two months no matter how you slice it. C) An error - with fractional months being undefined. D) Something else -- please tell me. 3. Or odd behavior with time-zones. select '2002-01-01'::timestamp + '6 months', '2002-01-01'::timestamp + '181 days', '2002-01-01'::timestamp + '4344 hours'; Note that those months have 181 days, and 4344 is 181 days * 24 hours. I would expect: A) The first one represents midnight on 2002-07-01. The second two one hour different (1AM) to make up for the missed hour on daylight savings. B) The first two expressions (Days and Months) are both calendar time so they'd both be midnight. Only the third one would be 1AM. D) Something else -- please tell me. To give away the answers... (A) Appears to be current behavior. (B) Is one possible proposal that started being discussed on PGPatches. (C) Is one other possible proposal that mentioned on PGPatches. (D) Would be appreciated. I'd love to hear what any specs, especially the SQL spec has to say for it. Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] quirk of array type processing
On third though ignore this whole thread. I can't read. sigh. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] wish: limit number of connections per database
Dear List, I already asked my question on the admin list, but got no answer. As I even could not find any related information anywhere, I think my question is a wish: Would it be possible to set the maximum number of connections to each database individually? I need this because the server will be shared between several users and I want to avoid that that somebody uses the maximum number of connections possible to the server, locking out others. I am not familiar with the internals of postgresql, but maybe a column in pg_database could store the limit if any. A similar solution would be if the number of connections could be limited per users, a similar column in pg_user could store the limit. Or this limit could be configured as an additional column in pg_hba.conf. How hard would it be to implement such a feature? As there is a check on each new connection to the server, maybe it would not be too difficult to check one constraint more :-) I don't have much time to do it, but if you think it is not too hard and my limited knowledge in C is enough I would help doing it gladly. These feature would be good even as a hidden feature, configurable only through psql after startup (file configuration support added later). thanks, attila ---(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] How to install and unistall
Nico King writes: I've notice when trying to uninstall by using the command gmake uninstall, I still could access my database, It only removes the files, it does not stop the server. and also all my directories of postgresql and bin files are still there! It only removes files, not directories. I am not sure if I should first drop my database, but even when I did this all my postgres directoires remain there. Stop your server, remove the data directory, and make uninstall. That you get you rid of most things. -- Peter Eisentraut [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
Re: [HACKERS] [BUGS] pg_dump/all doesn't output schemas correctly (v7.3.4)
I can confirm that this bug still exists in current CVS. The problem is that CREATE SCHEMA AUTHORIZATION test is translated into SET SESSION AUTHORIZATION 'test'; CREATE SCHEMA test;. While this does allow the schema to be owned by 'test', it assumes 'test' has permissions to create the schema, which might not be true. My guess is that the AUTHORIZATION option creates the schema as owned by that user --- manual says: AUTHORIZATION clause is used, all the created objects will be owned by that user. but then we forget and just create the schema as that user. I looked at the pg_dump code but can't quite see where the problem lies. --- Ben Grimm wrote: I haven't tried the 7.4 beta, so it may be fixed there - but in 7.3.4, pg_dumpall doesn't generate the commands to create schemas in the right order. This bug may have been reported before, but I saw no response to it in the lists. Try this in a fresh database after an initdb: template1=# create user test nocreatedb nocreateuser; CREATE USER template1=# create database testdb; CREATE DATABASE template1=# \c testdb You are now connected to database testdb. testdb=# create schema authorization test; CREATE SCHEMA testdb=# set session authorization test; SET testdb=# set search_path=test; SET testdb=# create table abc (); CREATE TABLE template1=# \q testdb=# \q $ pg_dumpall -U postgres -- -- PostgreSQL database cluster dump -- \connect template1 -- -- Users -- DELETE FROM pg_shadow WHERE usesysid (SELECT datdba FROM pg_database WHERE datname = 'template0'); CREATE USER test WITH SYSID 100 NOCREATEDB NOCREATEUSER; -- -- Groups -- DELETE FROM pg_group; -- -- Database creation -- CREATE DATABASE testdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'SQL_ASCII'; \connect template1 -- -- PostgreSQL database dump -- -- -- TOC entry 2 (OID 1) -- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner: -- COMMENT ON DATABASE template1 IS 'Default template database'; \connect testdb -- -- PostgreSQL database dump -- SET SESSION AUTHORIZATION 'test'; -- -- TOC entry 2 (OID 16977) -- Name: test; Type: SCHEMA; Schema: -; Owner: test -- * This will fail because user 'test' has not been granted create on the database (which pg_dump also fails to output, but that's a separate bug) It should create the schema as the superuser, then switch to the use to create tables within that schema. * CREATE SCHEMA test; SET search_path = test, pg_catalog; -- -- TOC entry 3 (OID 16978) -- Name: abc; Type: TABLE; Schema: test; Owner: test -- CREATE TABLE abc ( ); -- -- Data for TOC entry 4 (OID 16978) -- Name: abc; Type: TABLE DATA; Schema: test; Owner: test -- COPY abc FROM stdin; \. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Another small bug (pg_autovacuum)
I assume the attached patch is what you want done to fix this. Applied.
It quotes table names for vacuum and analyze, and uppercases the
keywords for clarity.
---
Matthew T. O'Connor wrote:
Ouch... sorry, my fault. I'll fix this tomorrow (Friday) and submit a
patch, or if you want to submit a patch that would be fine. All you
have to do is change the the sql statements to put quotes around the
relation name.
Thanks for catching this.
Matthew T. O'Connor
On Thu, 2003-09-04 at 18:39, Adam Kavan wrote:
Now that I have pg_autovacuum working I've bumped into another small
bug. When pg_autovacuum goes to vacuum or analyze one of my tables it runs...
analyze public.ConfigBackup
Because ConfigBackup is mixed case it cannot find the relation. I fixed
this by going to the function init_table_info and increasing the malloc for
new_tbl-table_name by 2 and adding 's to either side of the table
name. Is there anything wrong with this approach? Is there a config I can
set to make this non-case sensitive?
Thanks again for your time.
--- Adam Kavan
--- [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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
--
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
Index: contrib/pg_autovacuum/pg_autovacuum.c
===
RCS file: /cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.c,v
retrieving revision 1.3
diff -c -c -r1.3 pg_autovacuum.c
*** contrib/pg_autovacuum/pg_autovacuum.c 4 Aug 2003 00:43:11 - 1.3
--- contrib/pg_autovacuum/pg_autovacuum.c 10 Sep 2003 19:57:15 -
***
*** 581,587
{
PGresult *res = NULL;
! res = send_query(vacuum, dbi);
/* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
PQclear(res);
return 1;
--- 581,587
{
PGresult *res = NULL;
! res = send_query(VACUUM, dbi);
/* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
PQclear(res);
return 1;
***
*** 733,739
PGresult *res = NULL;
int ret = 0;
! res = send_query(show stats_row_level, dbi);
ret =
strcmp(on, PQgetvalue(res, 0, PQfnumber(res, stats_row_level)));
PQclear(res);
--- 733,739
PGresult *res = NULL;
int ret = 0;
! res = send_query(SHOW stats_row_level, dbi);
ret =
strcmp(on, PQgetvalue(res, 0, PQfnumber(res, stats_row_level)));
PQclear(res);
***
*** 1082,1088
*/
if
((tbl-curr_vacuum_count - tbl-CountAtLastVacuum) = tbl-vacuum_threshold)
{
! snprintf(buf,
sizeof(buf), vacuum analyze %s, tbl-table_name);
if
(args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
--- 1082,1088
*/
if
((tbl-curr_vacuum_count - tbl-CountAtLastVacuum) = tbl-vacuum_threshold)
{
! snprintf(buf,
sizeof(buf), VACUUM ANALYZE \%s\, tbl-table_name);
if
(args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
***
*** 1096,1102
}
else if
((tbl-curr_analyze_count - tbl-CountAtLastAnalyze) =
Re: [HACKERS] Broken(?) 'interval' problems. [Was: ISO 8601 Time Intervals]
On Wed, Sep 10, 2003 at 11:48:58 -0700, Ron Mayer [EMAIL PROTECTED] wrote: Looks like I'll take a shot at more broadly hacking the postgresql time interval code. Before doing so, I wanted to ask opinions regarding what the right behavior is of various timestamp/interval operations. Can you document which part of a mixed interval (with both months and seconds parts) gets added first to a timestamp? I haven't ever run accross anything which says which gets done first. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FK type mismatches?
Peter Eisentraut wrote: Tom Lane writes: If we follow Peter's recently proposed guideline, this would have to be a NOTICE not a WARNING, because the command absolutely is doing what you told it to do. Peter, does that make you uncomfortable? The message itself makes me a bit uncomfortable right now, but a NOTICE absolutely not. Added to TODO: * Issue NOTICE if foreign key data type doesn't match primary key -- 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] massive quotes?
Andrew Dunstan wrote: Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 07:04:13PM +0200, Andreas Pflug wrote: Bruce Momjian wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Oh, is it? Didn't people agree that other frontends (pgAdmin, phpPgAdmin, etc) have solutions for the problem already? I think there is agreement that these do. It would still look ugly in a programmatic interface like JDBC. Not that I use JDBC to set up functions, but I can imagine someone wanting to. But personally I could live with a nice enough psql-only fix. I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] wish: limit number of connections per database
On Wed, 2003-09-10 at 20:22, bognár, attila wrote: Would it be possible to set the maximum number of connections to each database individually? I need this because the server will be shared between several users and I want to avoid that that somebody uses the maximum number of connections possible to the server, locking out others. I assume users shouldn't be allowed to use other users' databases? If so, why not have a separate postmaster (and a separate database cluster) for each user? Each one would connect on a different port, and each one could be separately configured. -- 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 Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Trouble with error message encoding
- Original Message -
From: Peter Eisentraut [EMAIL PROTECTED]
To: Darko Prenosil [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, September 10, 2003 7:20 PM
Subject: Re: [HACKERS] Trouble with error message encoding
Darko Prenosil writes:
I have encoding problems using translated error messages (7.4beta1).
When database encoding is set to SQL_ASCII, all mesages arrive to client
correctly respecting the CLIENT_ENCODING, but if I create database WITH
ENCODING='unicode' or WITH ENCODING='latin2', messages are displayed
correctly only when CLIENT_ENCODING is same as database encoding.
I checked, and this is working this way also in 7.3. Is that known
problem, or
maybe I'm doing something wrong?
In general, the server encoding is S, the client encoding is C, and the
messages are stored (in the source, or in the PO files) in encoding M.
When the server sends a message to the client, it tries to convert a
string of encoding M, thinking it is in encoding S, to encoding C. So,
yes, there is a problem, but it's not easy to fix.
I found quick and I believe dirty solution for this problem, so I need
opinion from hackers.
Here is the idea: there is problem to find out in which encoding is using mo
file, but we can force gettext to serve known encoding for example utf8.
After that we can always convert from unicode to client encoding.
In /src/backend/main/main.c :
#ifdef ENABLE_NLS
bindtextdomain(postgres, LOCALEDIR);
bind_textdomain_codeset(postgres, utf8);
textdomain(postgres);
#endif
in /src/backend/utils/error/elog.c
#define EVALUATE_MESSAGE(targetfield, appendval) \
{ \
char *fmtbuf; \
StringInfoData buf; \
/* Internationalize the error format string */ \
fmt = gettext(fmt); \
fmt = pg_server_to_client((unsigned char*)fmt, strlen(fmt)); \
Of course this is working only for backend messages, but this was enough for
testing.
I did a quick test on database created with 'latin2' and I got correctly
encoded messages for latin2, unicode and sql_ascii client encoding.
I realize that this way message is translated 2 times: by gettext and
pg_server_to_client, but after all we want as less error messages as
possible :-)
Sorry if the whole Idea is stupid, but I could not resist.
Regards !
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] massive quotes?
On Wed, Sep 10, 2003 at 10:35:18PM +0200, Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Oh, and you will be feeding those script to the backend through what? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] massive quotes?
Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 10:35:18PM +0200, Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Oh, and you will be feeding those script to the backend through what? Virtually any client. May be psql, or may be pgAdmin2/pgAdmin3 (the latter featuring syntax highlighting), or other tools the let you execute generic queries. While I'm an old command liner, I rarely use cmd line tools for db administration/querying. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] massive quotes?
On Wed, 10 Sep 2003, Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 10:35:18PM +0200, Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Oh, and you will be feeding those script to the backend through what? I don't know what he'd be using, but I use Perl/DBI for things like that. Sure, I could spawn psql instances, but it's a lot less efficient and is quite different from using DBI directly. Jon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Dropping users
The problem with checking ownership of objects before dropping users is that there's no way to check what objects depend on a user on another database. But what if this information is stored in a shared relation? Like pg_depend but only for shared objects. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Someone said that it is at least an order of magnitude more work to do production software than a prototype. I think he is wrong by at least an order of magnitude. (Brian Kernighan) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 64-bit pgsql
Jeroen Ruigrok/asmodai wrote:
-On [20030905 20:52], Tom Lane ([EMAIL PROTECTED]) wrote:
Alternatively, find out what symbols your compiler predeclares.
If my theory is right then your pg_config_os.h file is failing to
define HAS_TEST_AND_SET; why?
Indeed, pg_config_os.h does not set anything for __ia64__.
When I added definitions for Itanium and Opteron to the
src/include/port/freebsd.h (attached) I get the following:
This post brings up a problem with our configuration system. Right now,
we test for compiler flags and set HAS_TEST_AND_SET based on the CPU in
each include/port/{os}.h file. However, this requires us to know about
each CPU enabled on each OS, and requires us to add duplicate
CPU-specific code for each platform. See below for FreeBSD:
--- freebsd.h.orig Fri Sep 5 21:38:06 2003
+++ freebsd.h Fri Sep 5 21:41:38 2003
@@ -44,5 +44,14 @@
#if defined(__powerpc__)
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
+#endif
+#if defined(__ia64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
+#endif
+
+#if defined(__x64_64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
#endif
For example, we will need the last two defines for all platforms that
support Intel. I wonder if we should have the HAS_TEST_AND_SET defined
in s_lock.h where the actual test-and-set is defined. This would
eliminate redundancy, and fix the FreeBSD problem reported with
Opteron/Itanium. I think we still need an slock_t typedef, but it is
probably the same for all CPU's on that platform.
--
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] massive quotes?
Jon Jensen [EMAIL PROTECTED] writes: On Wed, 10 Sep 2003, Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 10:35:18PM +0200, Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Oh, and you will be feeding those script to the backend through what? I don't know what he'd be using, but I use Perl/DBI for things like that. Sure, I could spawn psql instances, but it's a lot less efficient and is quite different from using DBI directly. Could the function bodies be shipped over using the new FE protocol as parameters? That would eliminate the quoting and simplify matters for DBI and other drivers as well. Then we just need a generic interface in plsql to handle passing parameters using the new FE protocol. This would help not only when defining function bodies but also when doing inserts/updates of large pieces of text. -- greg ---(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] Broken(?) 'interval' problems. [Was: ISO 8601 Time Intervals]
Bruno wrote: Can you document which part of a mixed interval (with both months and seconds parts) gets added first to a timestamp? I haven't ever run across anything which says which gets done first. In the existing code, the sql spec, or the proposed implementation? In the existing code, I think everything with + gets done in in the same order (left-to-right?), regardless of if the fields are timestamps or intervals. This leads to cool crazy behavior like getting different answers for this: logs=# select '.5 months'::interval + '.5 months'::interval + '2003-01-01'::timestamp; ?column? - 2003-01-01 00:00:00 (1 row) logs=# select '2003-01-01'::timestamp + '.5 months'::interval + '.5 months'::interval; ?column? 2003-01-31 00:00:00-08 (1 row) With addition not being commutative, all sorts of pain can result. The thing I'm proposing, is to define a form of time-math that is as consistant as possible. There are at least two reasonable ways of doing this -- using calendar time, or using absolute time. ISO 8601 makes such distinctions between day which it defines as 24 hours, and calendar day which it defines as 24 hours +/- leap minutes and seconds. The way this would work, we could: (1) Using calendar time: When doing math on 'intervals' and 'timestamps', we would keep the fundementally different units separate until the end. This means keeping separate track of years months in units of months weeks daysin units of days hours and less in units of seconds through out the calculation. This means you could have an intervals of '.5 months' without it converting to 15 days until the very end. (2) Using absolute time: Interval math could take a odd shortcut of turning everything to seconds early in the calculation and converting back at the end. I actually think each of the two are useful for different applications; so I'm really tempted to create a GUC parameter date_math = 'absolute' or 'calendar' to select between the two. ---(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] massive quotes?
Greg Stark wrote: Could the function bodies be shipped over using the new FE protocol as parameters? That would eliminate the quoting and simplify matters for DBI and other drivers as well. Oh no, not this discussion again. A whole script containing any number of valid statements must be executable without interpreting the script. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] massive quotes?
On Thu, Sep 11, 2003 at 01:05:47AM +0200, Andreas Pflug wrote: Greg Stark wrote: Could the function bodies be shipped over using the new FE protocol as parameters? That would eliminate the quoting and simplify matters for DBI and other drivers as well. Oh no, not this discussion again. :-) A whole script containing any number of valid statements must be executable without interpreting the script. Yes, but executable by what? You said you are a command liner. Then you will probably want to execute the script using psql. Then the proposed solution is fine, because the \beginliteral and \endliteral will be interpreted correctly. Now, you also said you wanted to use pgAdmin to administer the database. Is it able to execute the complete script, or you have to fiddle around with the mouse? If the latter, then there's no point in trying to execute the script; and I suppose pgAdmin is already capable of taking an non-massively-quoted function body and quote it correctly before passing the CREATE FUNCTION to the server. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Et put se mouve (Galileo Galilei) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] massive quotes?
Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Sep 11, 2003 at 01:05:47AM +0200, Andreas Pflug wrote: A whole script containing any number of valid statements must be executable without interpreting the script. How is that relevant? It's still parseable with parameter placeholders in place of literal parameters. Yes, but executable by what? You said you are a command liner. Then you will probably want to execute the script using psql. Then the proposed solution is fine, because the \beginliteral and \endliteral will be interpreted correctly. Presumably \beginliteral \endliteral would be psql's way of specifying parameters to ship over as parameters. Now, you also said you wanted to use pgAdmin to administer the database. Is it able to execute the complete script, or you have to fiddle around with the mouse? If the latter, then there's no point in trying to execute the script; and I suppose pgAdmin is already capable of taking an non-massively-quoted function body and quote it correctly before passing the CREATE FUNCTION to the server. It probably is, but that's not what I was thinking of. I was thinking it wouldn't have to poke around inside the string at all, it would pass it as an out-of-band parameter using the new FE protocol. This helps with DBI too, since you can already do that. $dbh-do(CREATE FUNCTION foo as ? LANGUAGE SQL, $func); Is a whole lot cleaner for the front-end to do than trying to quote the parameters and interpolate them into a query. -- greg ---(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] massive quotes?
Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Yes, I agree that a psql-specific solution is not ideal, quite aside from the problem of making it look nice. It would be better than nothing, though. Something that is done at the language level will be portable across frontends, while something psql-specific will not. Also, pg_dump -s will kindly restore all the quotes for you, so if you ever edit its output (as I do sometimes) you'll have to convert stuff all over again, although I guess pg_dump could be taught to re-unescape things. But then that seems almost as much trouble as teaching the backend a bit of new syntax. For those reasons as well as the aesthetic ones I'd prefer a solution at the language level. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Broken(?) 'interval' problems. [Was: ISO 8601 Time Intervals]
On Wed, Sep 10, 2003 at 15:43:56 -0700, Ron Mayer [EMAIL PROTECTED] wrote: Bruno wrote: Can you document which part of a mixed interval (with both months and seconds parts) gets added first to a timestamp? I haven't ever run across anything which says which gets done first. In the existing code, the sql spec, or the proposed implementation? In whatever is going to get implemented. In the existing code, I think everything with + gets done in in the same order (left-to-right?), regardless of if the fields are timestamps or intervals. That isn't what I was asking about. An interval has two parts. One part is the number of months in the interval and the other part is the number of seconds (or perhaps milliseconds). Often a single interval will only have one of these parts be nonzero. However if both parts are nonzero it makes a difference in which part gets added first. For example '2003-02-28'::date + '1 month 1 day'::interval might be either 2003-03-29 or 2003-04-01. In 7.4 it is currently 2003-03-29, but since it isn't documented it isn't clear if that will be true in future versions. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Unixware Patch (Was: Re: Beta2 Tag'd and Bundled ...)
On Wed, 10 Sep 2003 02:39 pm, Tom Lane wrote: A thread-safe implementation of libpq is of zero value to an application unless it also has thread-safe implementations of the other libraries it depends on. Not necessarily so - we've managed okay so far (several years) working on platforms that don't fall into that short list. It can be done. Thus far we have had to use Sybase or Informix because they do support thread-safe C interfaces. Any app that might want to use libpq is going to hit those same bugs, and so in the long run the only useful answer is for the platform to fix its libc. The useful answer (so far) is to not use PostgreSQL for these applications, but to stick with a database that does support a threadsafe C interface. I think that's a pity. I agree, it would make life easier if vendors supported threadsafe libc functions. The real bottom line here is: who is going to try to build threaded apps on platforms with un-thread-safe libc? The company I work for. I got involved in this issue so we could port from Sybase and Informix to PostgreSQL. I assume there are other people out there who'd be interested as well. And why should we be the ones to try to save them from suffering the pain they deserve? 1) Leave users to cope with their own code issues, but make sure the database's C interface isn't one of them. 2) Because it's good enough for (Oracle|Informix|Sybase) So moving forward: do we try Bruce's idea of libpq_r and ecpg_r? If people want to risk the overhead of wrapped libc calls, they can build the threaded lib versions and link against those. Would that be acceptable to people? Regards, Philip. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Broken(?) 'interval' problems. [Was: ISO 8601 Time Intervals]
Bruno wrote: ...An interval has two parts... the number of months...and...the number of seconds...if both parts are nonzero it makes a difference in which part gets added first. For example '2003-02-28'::date + '1 month 1 day'::interval might be either 2003-03-29 or 2003-04-01. In 7.4 it is currently 2003-03-29, Ah.. I understand. At least one other application that does date math, MSFT Excel also claims 2003-03-29 when I use the expression =DATE(YEAR(B3),MONTH(B3)+1,DAY(B3)+1) so I think that's a reasonable rule to keep. Anyone, please let me know if there are good reasons such as standards or other major applications that behave otherwise. Thanks for this other interesting case that I need to worry about! And yes, I'll document it as well. :-) Ron PS: I'm not receiving some emails I send to hackers. If you need a timely answer please cc me -- though I will follow the thread on archives as well to catch anything I miss. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] massive quotes?
Jon Jensen [EMAIL PROTECTED] writes: On Wed, 10 Sep 2003, Alvaro Herrera wrote: On Wed, Sep 10, 2003 at 10:35:18PM +0200, Andreas Pflug wrote: I never agreed that a client solution would be satisfying. While frontends might try to hide some uglyness of the syntax to the user for single functions, editing large scripts with many functions is still suffering from massive quotes. Oh, and you will be feeding those script to the backend through what? I don't know what he'd be using, but I use Perl/DBI for things like that. Sure, I could spawn psql instances, but it's a lot less efficient and is quite different from using DBI directly. But Perl/DBI does escaping for you, so all you'd have to do is: $sth = $dbh-prepare (CREATE FUNCTION foo(x text) RETURNS text AS ? LANGUAGE 'plpgsql'); $sth-execute($function_body); where $function_body is the unescaped form of the function. So there's no need for a COPY-style mechanism, you can use the current CREATE FUNCTION syntax without having to escape everything yourself. The same argument applies to JDBC. -Doug ---(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] massive quotes?
Andreas Pflug [EMAIL PROTECTED] writes: Bruce Momjian wrote: I assume we never came to a final conclusion on how to do CREATE FUNCTION without double-quoting. Many discussions, but no final conclusion in sight, it seems. That \beginliteral stuff is psql centric, where a sql syntax solution is needed. Some people think a sql syntax solution is needed, and some do not. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Another small bug (pg_autovacuum)
On Wed, 2003-09-10 at 15:57, Bruce Momjian wrote:
I assume the attached patch is what you want done to fix this. Applied.
It quotes table names for vacuum and analyze, and uppercases the
keywords for clarity.
Yeah, this is basically what I meant, sorry I didn't get to it quicker.
However, I tested it out a little and the patch you made doesn't work
because it produces commands like:
VACUUM ANALYZE public.FooBar
Which doesn't work, so I made my own patch that creates commands like:
VACUUM ANALYZE public.FooBar
This allows for mixed case schema names as well as tables.
Adam, can you please give this a test as you are the person who caught
the bug in the first place.
Thanks,
Matthew T. O'Connor
*** pg_autovacuum.c.orig 2003-09-10 23:13:51.95072 -0400
--- pg_autovacuum.c 2003-09-10 23:59:25.672571940 -0400
***
*** 88,103
new_tbl-table_name = (char *)
malloc(strlen(PQgetvalue(res, row, PQfnumber(res, relname))) +
! strlen(new_tbl-schema_name) + 2);
if (!new_tbl-table_name)
{
log_entry(init_table_info: malloc failed on new_tbl-table_name);
fflush(LOGOUTPUT);
return NULL;
}
! strcpy(new_tbl-table_name, new_tbl-schema_name);
! strcat(new_tbl-table_name, .);
strcat(new_tbl-table_name, PQgetvalue(res, row, PQfnumber(res, relname)));
new_tbl-CountAtLastAnalyze =
(atol(PQgetvalue(res, row, PQfnumber(res, n_tup_ins))) +
--- 88,108
new_tbl-table_name = (char *)
malloc(strlen(PQgetvalue(res, row, PQfnumber(res, relname))) +
! strlen(new_tbl-schema_name) + 6);
if (!new_tbl-table_name)
{
log_entry(init_table_info: malloc failed on new_tbl-table_name);
fflush(LOGOUTPUT);
return NULL;
}
!
! /* Put both the schema and table name in quotes so that
! we can work with mixed case table names */
! strcpy(new_tbl-table_name, \);
! strcat(new_tbl-table_name, new_tbl-schema_name);
! strcat(new_tbl-table_name, \.\);
strcat(new_tbl-table_name, PQgetvalue(res, row, PQfnumber(res, relname)));
+ strcat(new_tbl-table_name, \);
new_tbl-CountAtLastAnalyze =
(atol(PQgetvalue(res, row, PQfnumber(res, n_tup_ins))) +
***
*** 581,587
{
PGresult *res = NULL;
! res = send_query(vacuum, dbi);
/* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
PQclear(res);
return 1;
--- 586,592
{
PGresult *res = NULL;
! res = send_query(VACUUM, dbi);
/* FIXME: Perhaps should add a check for PQ_COMMAND_OK */
PQclear(res);
return 1;
***
*** 733,739
PGresult *res = NULL;
int ret = 0;
! res = send_query(show stats_row_level, dbi);
ret =
strcmp(on, PQgetvalue(res, 0, PQfnumber(res, stats_row_level)));
PQclear(res);
--- 738,744
PGresult *res = NULL;
int ret = 0;
! res = send_query(SHOW stats_row_level, dbi);
ret =
strcmp(on, PQgetvalue(res, 0, PQfnumber(res, stats_row_level)));
PQclear(res);
***
*** 1082,1088
*/
if ((tbl-curr_vacuum_count - tbl-CountAtLastVacuum) = tbl-vacuum_threshold)
{
! snprintf(buf, sizeof(buf), vacuum analyze %s, tbl-table_name);
if (args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
--- 1087,1093
*/
if ((tbl-curr_vacuum_count - tbl-CountAtLastVacuum) = tbl-vacuum_threshold)
{
! snprintf(buf, sizeof(buf), VACUUM ANALYZE %s, tbl-table_name);
if (args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
***
*** 1096,1102
}
else if ((tbl-curr_analyze_count - tbl-CountAtLastAnalyze) = tbl-analyze_threshold)
{
! snprintf(buf, sizeof(buf), analyze %s, tbl-table_name);
if (args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
--- 1101,1107
}
else if ((tbl-curr_analyze_count - tbl-CountAtLastAnalyze) = tbl-analyze_threshold)
{
! snprintf(buf, sizeof(buf), ANALYZE %s, tbl-table_name);
if (args-debug = 1)
{
sprintf(logbuffer, Performing: %s, buf);
---(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] constraint modification on todo list
-On [20030909 00:42], Tom Lane ([EMAIL PROTECTED]) wrote: IIRC, Oracle does not have rollback-able DDL. That might imply that the reason they have MODIFY CONSTRAINT is that in Oracle you can't use the above way to eliminate the window. Can you put ALTERs inside transactions at all in Oracle? As one of the Oracle gurus at work told me: DDL does an implicit commit, so no rollback possible. It also shouldn't be necessary, because you cannot change a table which is in use. It attempts to do a table lock and it fails. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ We should take care not to make the intellect our god; it has, of course, powerful muscles, but no personality... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
