Re: [PATCHES] [HACKERS] 8.2 features?
The major downside is that somewhere between 9000 and 1 VALUES-targetlists produces ERROR: stack depth limit exceeded. Perhaps for the typical use-case this is sufficient though. I'm open to better ideas, comments, objections... If the use case is people running MySQL dumps, then there will be millions of values-targetlists in MySQL dumps. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Strange. Last time I checked I thought MySQL dump used 'multivalue lists in inserts' for dumps, for the same reason that we use COPY ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Ah no, I'm mistaken. It's not by default in mysqldump, but it does seem recommended. This is from man mysqldump: -e|--extended-insert Allows utilization of the new, much faster INSERT syntax. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] PQescapeIdentifier
Hang on a second. Has someone considered the encoding issues this might suffer from, same as PQescapeString? I remember we discussed it briefly and I mentioned it's outta my league to prove one way or the other... Bruce Momjian wrote: Christopher Kings-Lynne wrote: TODO item done for 8.2: * Add PQescapeIdentifier() to libpq Someone probably needs to check this :) Updated patch applied. Thanks. Index: doc/src/sgml/libpq.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.211 diff -c -c -r1.211 libpq.sgml *** doc/src/sgml/libpq.sgml 23 May 2006 22:13:19 - 1.211 --- doc/src/sgml/libpq.sgml 26 Jun 2006 23:54:12 - *** *** 2279,2284 --- 2279,2347 /para /sect2 + sect2 id=libpq-exec-escape-identifier + titleEscaping Identifier for Inclusion in SQL Commands/title + +indexterm zone=libpq-exec-escape-identifierprimaryPQescapeIdentifier// +indexterm zone=libpq-exec-escape-identifierprimaryescaping strings// + + para + functionPQescapeIdentifier/function escapes a string for use + as an identifier name within an SQL command. For example; table names, + column names, view names and user names are all identifiers. + Double quotes () must be escaped to prevent them from being interpreted + specially by the SQL parser. functionPQescapeIdentifier/ performs this + operation. + /para + + tip + para + It is especially important to do proper escaping when handling strings that + were received from an untrustworthy source. Otherwise there is a security + risk: you are vulnerable to quoteSQL injection/ attacks wherein unwanted + SQL commands are fed to your database. + /para + /tip + + para + Note that it is still necessary to do escaping of identifiers when + using functions that support parameterized queries such as functionPQexecParams/ or + its sibling routines. Only literal values are automatically escaped + using these functions, not identifiers. + + synopsis + size_t PQescapeIdentifier (char *to, const char *from, size_t length); + /synopsis + /para + + para + The parameter parameterfrom/ points to the first character of the string + that is to be escaped, and the parameterlength/ parameter gives the + number of characters in this string. A terminating zero byte is not + required, and should not be counted in parameterlength/. (If + a terminating zero byte is found before parameterlength/ bytes are + processed, functionPQescapeIdentifier/ stops at the zero; the behavior + is thus rather like functionstrncpy/.) + parameterto/ shall point to a + buffer that is able to hold at least one more character than twice + the value of parameterlength/, otherwise the behavior is + undefined. A call to functionPQescapeIdentifier/ writes an escaped + version of the parameterfrom/ string to the parameterto/ + buffer, replacing special characters so that they cannot cause any + harm, and adding a terminating zero byte. The double quotes that + may surround productnamePostgreSQL/ identifiers are not + included in the result string; they should be provided in the SQL + command that the result is inserted into. + /para + para + functionPQescapeIdentifier/ returns the number of characters written + to parameterto/, not including the terminating zero byte. + /para + para + Behavior is undefined if the parameterto/ and parameterfrom/ + strings overlap. + /para + /sect2 sect2 id=libpq-exec-escape-bytea titleEscaping Binary Strings for Inclusion in SQL Commands/title Index: src/interfaces/libpq/exports.txt === RCS file: /cvsroot/pgsql/src/interfaces/libpq/exports.txt,v retrieving revision 1.11 diff -c -c -r1.11 exports.txt *** src/interfaces/libpq/exports.txt28 May 2006 22:42:05 - 1.11 --- src/interfaces/libpq/exports.txt26 Jun 2006 23:54:20 - *** *** 130,132 --- 130,134 PQencryptPassword 128 PQisthreadsafe129 enlargePQExpBuffer130 + PQescapeIdentifier131 + Index: src/interfaces/libpq/fe-exec.c === RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v retrieving revision 1.186 diff -c -c -r1.186 fe-exec.c *** src/interfaces/libpq/fe-exec.c 28 May 2006 21:13:54 - 1.186 --- src/interfaces/libpq/fe-exec.c 26 Jun 2006 23:54:21 - *** *** 2516,2521 --- 2516,2557 } /* + * Escaping arbitrary strings to get valid SQL identifier strings. + * + * Replaces with . + * + * length is the length of the source string. (Note: if a terminating NUL + * is encountered sooner, PQescapeIdentifier stops short of length; the behavior + * is thus rather like strncpy.) + * + * For safety the buffer at to must
Re: [PATCHES] [PATCH] Magic block for modules
Marko's suggestion on producing a list of installed modules comes to mind, and I suspect tools like pgadmin or ppa will want to be able to show this information. My request for phpPgAdmin is to somehow be able to check if the .so file for a module is present. For instance, I'd like to 'enable slony support' if the slony shared library is present. PPA's slony support automatically executes the .sql files, so all I need to know is if the .so is there. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [PATCH] Magic block for modules
For instance, I'd like to 'enable slony support' if the slony shared library is present. PPA's slony support automatically executes the .sql files, so all I need to know is if the .so is there. I really think this is backwards: you should be looking for the .sql files. Every module will have a .sql file, not every one will need a .so file. See followup thread in -hackers where we're trying to hash out design details. Not in this case. Basically Slony has the concept of installing a node into a server. You can have multiple ones of them - different schemas. So, I'd like to be able to detect that the .so is there, and then offer an install node feature where WE execute the SQL on their behalf, with all the complicated string substitions already done. The trick is that Slony currently requires you to use a command line tool to execute these scripts for you. At the moment, people have to indicate in our config while that Slony is available, and also point us to where the Slony SQL scripts are located. We do the rest. It's not too important, but it's just an idea. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Proposed patch for error locations
from all libpq-using applications not just psql. We could make this conditional on the error verbosity --- in terse mode the LINE N output wouldn't appear, and at character N still would. Applications should already be expecting multiline outputs from PQerrorMessage if they're in non-terse mode, so this ought to be OK. Comments? Sounds like it'd be handy in phpPgAdmin... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] pg_freespacemap question
The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] drop if exists remainder
Here's a first draft patch for DROP ... IF EXISTS for the remaining cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS, FUNCTION, AGGREGATE, OPERATOR, CAST and RULE. At what point does this stop being useful and become mere bloat? The only case I can ever recall being actually asked for was the TABLE case ... Chris KL said it should be done for all on the grounds of consistency. But I will happily stop right now if that's not the general view - I'm only doing this to complete something I started. Well, my use-case was to be able to wrap pg_dump -c output in begin/commit tags and being able to run and re-run such dumps without errors. Basically I don't like 'acceptable errors' when restoring dumps :) They just confuse newer users especially. I also just like consistency :) Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] patch to create system view that lists cursors
I think it is worth distinguishing more clearly between portals that should be displayed to the user and those that should not (which might be labelled internal cursors, perhaps). The tests above seem fairly ad-hoc. With all this system view love going on, is there any point having a 'pg_savepoints' view to see what savepoints you've made? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Disparity in search_path SHOW and SET
Agreed. I have gotten confused on how to set $user in the past. I have developed the following patch that sets the default with the double quotes around it, and it works fine. The patch also contains updated documentation. Just be careful about pg_dump's special handling of search_path in user and db variables... Make sure you haven't broken it. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] PLpgSQL: list of scalars as row for assign stmt, fore
x, y := r; That strikes me as a really bad idea. It weakens both syntax and semantic error checking, to accomplish how much? Could use PHP-style thingy: LIST(x, y) := r; Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] PLpgSQL: list of scalars as row for assign stmt, fore
Pavel Stehule wrote: x, y := r; That strikes me as a really bad idea. It weakens both syntax and semantic error checking, to accomplish how much? Could use PHP-style thingy: LIST(x, y) := r; Chris It's inconsystency :-(. EXECUTE INTO and SELECT INTO use scalar of vectors without anything. ROW(x, y) := r; :) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] In pg_dump no owner mode don't dump owner names in
This fixes pg_dump so that when using the '-O' no owners option it does not print the owner name in the object comment. Why is that a good idea? At the moment I want to dump sample databases for a project. I don't want my name or username appearing anywhere in them. I dump without owners or privileges since they're irrelevant and will refer to users that just don't exist. So, at the moment I need to run the dumps through sed to remove the reference to the owner usernames in the dumps, otherwise everyone can see half the users in our database... Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] In pg_dump no owner mode don't dump owner names in comments
This fixes pg_dump so that when using the '-O' no owners option it does not print the owner name in the object comment. eg: -- -- Name: actor; Type: TABLE; Schema: public; Owner: chriskl; Tablespace: -- Becomes: -- -- Name: actor; Type: TABLE; Schema: public; Owner: -; Tablespace: -- This makes it far easier to do 'user independent' dumps. Especially for distribution to third parties. Chris Index: src/bin/pg_dump/pg_backup_archiver.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.117 diff -c -r1.117 pg_backup_archiver.c *** src/bin/pg_dump/pg_backup_archiver.c15 Oct 2005 02:49:38 - 1.117 --- src/bin/pg_dump/pg_backup_archiver.c21 Nov 2005 07:19:39 - *** *** 2404,2410 ahprintf(AH, -- %sName: %s; Type: %s; Schema: %s; Owner: %s, pfx, te-tag, te-desc, te-namespace ? te-namespace : -, !te-owner); if (te-tablespace) ahprintf(AH, ; Tablespace: %s, te-tablespace); ahprintf(AH, \n); --- 2404,2410 ahprintf(AH, -- %sName: %s; Type: %s; Schema: %s; Owner: %s, pfx, te-tag, te-desc, te-namespace ? te-namespace : -, !ropt-noOwner ? - : te-owner); if (te-tablespace) ahprintf(AH, ; Tablespace: %s, te-tablespace); ahprintf(AH, \n); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] drop if exists
If the consensus is to add this to all of them, then I propose to apply the patch I have (with a slight fix for an oversight in the case of domains, plus docs and tests) for the 8 cases and start working on the remaining 13 as time permits. To be honest, I have not even looked at those 13 cases. I agree. I can have a crack at the others as well. It's in my area of ability I hope ;) (Except grammar janking) Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] drop if exists
Will we get this functionality for ALL objects? Bruce Momjian wrote: Removed from queue. Andrew is committing it. --- Andrew Dunstan wrote: OK, now it looks like this: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE andrew=# create table blurflx ( x text); CREATE TABLE andrew=# drop table if exists blurflx; DROP TABLE andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# revised patch attached. cheers andrew Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: andrew=# drop table blurflx; ERROR: table blurflx does not exist andrew=# drop table if exists blurflx; DROP TABLE If I read MySQL's documentation correctly, they emit a NOTE (equivalent of a NOTICE message I suppose) when IF EXISTS does nothing because the table doesn't exist. Seems like we should do likewise --- your second example here seems actively misleading. That is, I'd rather see andrew=# drop table if exists blurflx; NOTICE: table blurflx does not exist, skipping DROP TABLE regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: 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: [PATCHES] drop if exists
I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. Everything should be covered, otherwise it's just annoying for users... Chris ---(end of broadcast)--- TIP 1: 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: [PATCHES] TODO Item - Add system view to show free space map
Want to host it on pgfoundry until 8.2 is released? Mark Kirkwood wrote: This patch implements a view to display the free space map contents - e.g : regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes FROM pg_freespacemap m INNER JOIN pg_class c ON c.relfilenode = m.relfilenode LIMIT 10; relname | relblocknumber | blockfreebytes ++ sql_features| 5 | 2696 sql_implementation_info | 0 | 7104 sql_languages | 0 | 8016 sql_packages| 0 | 7376 sql_sizing | 0 | 6032 pg_authid | 0 | 7424 pg_toast_2618 | 13 | 4588 pg_toast_2618 | 12 | 1680 pg_toast_2618 | 10 | 1436 pg_toast_2618 | 7 | 1136 (10 rows) [I found being able to display the FSM pretty cool, even if I say so myself]. It is written as a contrib module (similar to pg_buffercache) so as to make any revisions non-initdb requiring. The code needs to know about several of the (currently) internal data structures in freespace.c, so I moved these into freespace.h. Similarly for the handy macros to actually compute the free space. Let me know if this was the wrong way to proceed! Additionally access to the FSM pointer itself is required, I added a function in freespace.c to return this, rather than making it globally visible, again if the latter is a better approach, it is easily changed. cheers Mark P.s : Currently don't have access to a windows box, so had to just 'take a stab' at what DLLIMPORTs were required. diff -Ncar pgsql.orig/contrib/pg_freespacemap/Makefile pgsql/contrib/pg_freespacemap/Makefile *** pgsql.orig/contrib/pg_freespacemap/Makefile Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/Makefile Thu Oct 27 17:52:10 2005 *** *** 0 --- 1,17 + # $PostgreSQL$ + + MODULE_big = pg_freespacemap + OBJS = pg_freespacemap.o + + DATA_built = pg_freespacemap.sql + DOCS = README.pg_freespacemap + + ifdef USE_PGXS + PGXS := $(shell pg_config --pgxs) + include $(PGXS) + else + subdir = contrib/pg_freespacemap + top_builddir = ../.. + include $(top_builddir)/src/Makefile.global + include $(top_srcdir)/contrib/contrib-global.mk + endif diff -Ncar pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap pgsql/contrib/pg_freespacemap/README.pg_freespacemap *** pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/README.pg_freespacemapThu Oct 27 18:06:20 2005 *** *** 0 --- 1,98 + Pg_freespacemap - Real time queries on the free space map (FSM). + --- + + This module consists of a C function 'pg_freespacemap()' that returns + a set of records, and a view 'pg_freespacemap' to wrapper the function. + + The module provides the ability to examine the contents of the free space + map, without having to restart or rebuild the server with additional + debugging code. + + By default public access is REVOKED from both of these, just in case there + are security issues lurking. + + + Installation + + + Build and install the main Postgresql source, then this contrib module: + + $ cd contrib/pg_freespacemap + $ gmake + $ gmake install + + + To register the functions: + + $ psql -d database -f pg_freespacemap.sql + + + Notes + - + + The definition of the columns exposed in the view is: + +Column | references | Description + +--+ +blockid| | Id, 1.. max_fsm_pages +relfilenode| pg_class.relfilenode | Refilenode of the relation. +reltablespace | pg_tablespace.oid| Tablespace oid of the relation. +reldatabase| pg_database.oid | Database for the relation. +relblocknumber | | Offset of the page in the relation. +blockfreebytes | | Free bytes in the block/page. + + + There is one row for each page in the free space map. + + Because the map is shared by all the databases, there are pages from + relations not belonging to the current database. + + When the pg_freespacemap view is accessed, internal free space map locks are + taken, and a copy of the map data is made for the view to display. + This ensures that the view produces a consistent set of results, while not + blocking normal activity longer than necessary. Nonetheless there + could be some impact on
[PATCHES] Error in ORDER BY on check constraints in psql
Everything is sorted by object name in \d table except check constraints for some reason. It seems it's ordering by the wrong column. Seems like a bug to me. Attached is the trivial patch. Chris Index: src/bin/psql/describe.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.127 diff -c -r1.127 describe.c *** src/bin/psql/describe.c 15 Oct 2005 02:49:40 - 1.127 --- src/bin/psql/describe.c 20 Oct 2005 04:58:58 - *** *** 1040,1046 pg_catalog.pg_get_constraintdef(r.oid, true), conname\n FROM pg_catalog.pg_constraint r\n ! WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1, oid); result2 = PSQLexec(buf.data, false); if (!result2) --- 1040,1046 pg_catalog.pg_get_constraintdef(r.oid, true), conname\n FROM pg_catalog.pg_constraint r\n ! WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 2, oid); result2 = PSQLexec(buf.data, false); if (!result2) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Error in ORDER BY on check constraints in psql
That was probably done deliberately, back in the day when constraints tended to have uselessly random names like $1 --- sorting by the constraint text was more helpful. I agree that now sorting by name seems like the better thing. Even in the $x case, it's better to have them sorted in that order (ie. the order they were created...) I think there's nothing wrong with the ORDER BY 1 part ... it's the fact that the columns are selected in a different order than they'll be used that seems bizarre to me. I fixed it like this instead. Ah, the way that requires effort :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Autovacuum loose ends
We have to consider what happens at stat reset -- AFAICS there's no problem, because as soon as the table sees some activity, it will be picked up by pgstat. However, it would be bad if stats are reset right after some heavy activity on a table. Maybe the only thing we need is documentation. What's the use-case for having the stat reset feature at all? I believe I was the root cause of the pg_stat_reset() function. The idea at the time was that if you decide to do a round of index optimisation, you want to be able to search for unused indexes and heavily seq. scanned tables. If you reset the stats you have 'clean' data to work with. For instance, you can get 24 hours of clean stats data. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Dbsize backend integration
You are into the cycle we were in. We discussed pg_object size (too vague) and pg_index_size (needs pg_toast_size too, and maybe toast indexes; too many functions). Yeah, I read those discussions, and think you were better off then than you are now, which is why I went back to it somewhat. To be honest, the amount of effort being expended on this naming discussion far outweighs the benefits. Maybe it's time for a core member to step in and just resolve it - one way or the other? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
ALTER TABLE table ENABLE TRIGGER trigname ALTER TABLE table DISABLE TRIGGER trigname Bruce said to allow them only super-user, but currently this patch allows also the table owner. The table owner can drop and create triggers - so why shouldn't they be able to enable and disable them? Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Satoshi Nagayasu wrote: The table owner can drop and create triggers - so why shouldn't they be able to enable and disable them? For convenience or easy operation. I believe the user doesn't like to create same triggers again and again. I said why _shouldn't_. I was agreeing with you. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Dump comments on large objects in text mode
Hi, Attached patch enables dumping of LOB comments when in text mode. I really don't get the binary/custom format LOB stuff (and don't have time to investigate), so perhaps someone else can do that. Having it in text format is still an improvement though. Chris blobcomments.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Dump comments on large objects in text mode
Attached patch enables dumping of LOB comments when in text mode. I really don't get the binary/custom format LOB stuff (and don't have time to investigate), so perhaps someone else can do that. That's pretty icky :-(. I think the right way is more like this. Hehe - in the world of open source, convincing someone to implement something is almost as good as doing it yourself :) Thanks for that, pg_dump is really going great guns now... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Dump comments on large objects in text mode
The trick in hacking pg_dump is to understand which layer you need to modify. The whole thing seems overly complex to me :-( ... but redesigning it is a project for another release cycle. I just find the whole BLOB handling very tricky to understand :( I vote that we combine pg_dumpall and pg_dump into a single app in the future... It should be possible to make them both work backward compatibly. Means we can possibly gain custom format full cluster dumps. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Escape handling in strings
I'm still really iffy about this. I think it will really hurt pgsql due to backward compatibility :( (If I'm understanding how the proposed change works...) Chris Bruce Momjian wrote: A summary of my proposal to add a new E'' string for escape and have non-E escapes not handle backslashes specially is at: http://candle.pha.pa.us/cgi-bin/pgescape Attached is a patch that emits warnings for \ and \', perhaps for 8.1. The change to scan.l is the place this is done. The rest of the patch is adjustments to prevent our own code from generating warnings. It shows a good example of how users would have to change their code. It passes all regression tests, contrib regression, and initdb runs without warning. Index: contrib/tsearch2/expected/tsearch2.out === RCS file: /cvsroot/pgsql/contrib/tsearch2/expected/tsearch2.out,v retrieving revision 1.11 diff -c -c -r1.11 tsearch2.out *** contrib/tsearch2/expected/tsearch2.out 14 Sep 2004 03:58:54 - 1.11 --- contrib/tsearch2/expected/tsearch2.out 16 Jun 2005 01:36:54 - *** *** 47,83 '1' '2' (1 row) ! SELECT '\'1 2\''::tsvector; tsvector -- '1 2' (1 row) ! SELECT '\'1 \\\'2\''::tsvector; tsvector -- '1 \'2' (1 row) ! SELECT '\'1 \\\'2\'3'::tsvector; tsvector - '3' '1 \'2' (1 row) ! SELECT '\'1 \\\'2\' 3'::tsvector; tsvector - '3' '1 \'2' (1 row) ! SELECT '\'1 \\\'2\' \' 3\' 4 '::tsvector; tsvector -- '4' ' 3' '1 \'2' (1 row) ! select '\'w\':4A,3B,2C,1D,5 a:8'; ?column? --- 'w':4A,3B,2C,1D,5 a:8 --- 47,83 '1' '2' (1 row) ! SELECT '''1 2'''::tsvector; tsvector -- '1 2' (1 row) ! SELECT E'''1 \\''2'''::tsvector; tsvector -- '1 \'2' (1 row) ! SELECT E'''1 \\''2''3'::tsvector; tsvector - '3' '1 \'2' (1 row) ! SELECT E'''1 \\''2'' 3'::tsvector; tsvector - '3' '1 \'2' (1 row) ! SELECT E'''1 \\''2'' '' 3'' 4 '::tsvector; tsvector -- '4' ' 3' '1 \'2' (1 row) ! select '''w'':4A,3B,2C,1D,5 a:8'; ?column? --- 'w':4A,3B,2C,1D,5 a:8 *** *** 126,138 '1' (1 row) ! SELECT '\'1 2\''::tsquery; tsquery - '1 2' (1 row) ! SELECT '\'1 \\\'2\''::tsquery; tsquery - '1 \'2' --- 126,138 '1' (1 row) ! SELECT '''1 2'''::tsquery; tsquery - '1 2' (1 row) ! SELECT E'''1 \\''2'''::tsquery; tsquery - '1 \'2' *** *** 330,342 '1' '2' '4' ( '5' | !'6' ) (1 row) ! SELECT '1(\'2\'(\' 4\'(\\|5 | \'6 \\\' !|\')))'::tsquery; tsquery -- '1' '2' ' 4' ( '|5' | '6 \' !|' ) (1 row) ! SELECT '\'the wether\':dc \' sKies \':BC a:d b:a'; ?column? -- 'the wether':dc ' sKies ':BC a:d b:a --- 330,342 '1' '2' '4' ( '5' | !'6' ) (1 row) ! SELECT E'1(''2''('' 4''(\\|5 | ''6 \\'' !|'')))'::tsquery; tsquery -- '1' '2' ' 4' ( '|5' | '6 \' !|' ) (1 row) ! SELECT '''the wether'':dc '' sKies '':BC a:d b:a'; ?column? -- 'the wether':dc ' sKies ':BC a:d b:a *** *** 382,388 23 | entity | HTML Entity (23 rows) ! select * from parse('default', '345 [EMAIL PROTECTED] \' http://www.com/ http://aew.werc.ewr/?ad=qwedw 1aew.werc.ewr/?ad=qwedw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwedw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwedw 6aew.werc.ewr:8100/?ad=qwedw 7aew.werc.ewr:8100/?ad=qwedw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 [EMAIL PROTECTED] qwe-wer asdf frqwer jf sdjkwe hjwer werrwe ewr1 ewri2 a href=qweqwe /usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 i b wow jqw qwerty'); tokid |token --- 382,388 23 | entity | HTML Entity (23 rows) ! select * from parse('default', '345 [EMAIL PROTECTED] '' http://www.com/ http://aew.werc.ewr/?ad=qwedw 1aew.werc.ewr/?ad=qwedw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwedw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwedw 6aew.werc.ewr:8100/?ad=qwedw 7aew.werc.ewr:8100/?ad=qwedw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 [EMAIL PROTECTED] qwe-wer asdf frqwer jf sdjkwe hjwer werrwe ewr1
Re: [PATCHES] Escape handling in strings
Yep, you probably are. The hurt is backward compatibility, but the gain is greater portability with other database systems. It's just going to break millions of PHP scripts :( Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Escape handling in strings
* Allow backslash handling in quoted strings to be disabled for portability The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not SQL-spec compliant, so allow such handling to be disabled. However, disabling backslashes could break many third-party applications and tools. Now, if we don't address it, we might as well remove the TODO item and say we are never going to change it, because right now, we have a plan, and I think the longer we go the harder it will be. And if we don't change it, it makes it quite hard for people to port applications to PostgreSQL. Fundamental queries like: SELECT * FROM files WHERE filename = 'C:\tmp' do not work. When a query with a single table and single WHERE clause isn't portable, it seems like a problem. If this was isolated to CREATE TABLE or something, it wouldn't be a big deal. Why not compromise? Allow ONLY \' in normal strings? That'd deal with the majority of compatibility issues. Or, like you say, make it a GUC :( Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] indxpath.c refactoring
I don't see anyone very excited about r-tree these days; have you noticed that no one has stepped up to repair the known semantic errors? I wouldn't spend any time on it in the prover. That sort of thing is always self-fulfilling. If rtrees were fast, logged and rad, then more people would use them :) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. Chris Bruce Momjian wrote: What did we decide on RESET CONNECTION. Do we want an SQL command or something only the protocol can do? --- Oliver Jowett wrote: (cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] patch for between symmetric, asymmetric (from TODO)
The implementation in this patch has the same problems as all the previously rejected attempts: it evaluates its arguments twice. You need to make BETWEEN SYMMETRIC into a separate node type that evaluates each argument only once. And that's also been submitted. The problem then is making the optimizer recognise it as a range query... Chris ---(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: [PATCHES] lastval()
I like the concept, but I haven't looked at the code -- I'd be happy to review the implementation, although I won't waste my time if most people are opposed to the idea itself. It'd make implementing various PHP userland functions a real breeze... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Update psql and pg_dump for new COPY api
Actually, better not apply this - I think I've found some problems in it. Chris On Fri, 6 May 2005, Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Christopher Kings-Lynne wrote: This patch updates psql and pg_dump to use the new copy api. Probably needs some review. I have tested it with dos and unix newlines, etc. Chris Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: [PATCHES] Dealing with CLUSTER failures
Seems like an idea to me... On another note, what about the problem I pointed out where it's not possible to drop the default on a serial column after you alter it to a varchar, for example... Chris On Sat, 7 May 2005, Bruce Momjian wrote: Christopher Kings-Lynne wrote: I don't think that's a bug. You may not intend ever to cluster on that index again, and if you try it will tell you about the problem. Except it breaks the 'cluster everything' case: test=# cluster; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column a NOT NULL. I looked over this item, originally posted as: http://archives.postgresql.org/pgsql-hackers/2005-03/msg01055.php It seems that if you use an index method that doesn't support NULLs, you can use ALTER to set the column as NOT NULL, then CLUSTER, and then set it to allow NULLs, and when you CLUSTER all tables, the cluster errors out on that table. I thought about removing the cluster bit when you do the alter or something like that, but it seems too confusing and error-prone to be sure we get every case. I think the main problem is that while cluster is a performance-only feature, we error out if we can't cluster one table, basically treating it as though it needs transaction semantics. It doesn't. This patch throws an ERROR of you cluster a specific index that can't be clustered, but issues only a WARNING if you are clustering all tables. This allows it to report the failed cluster but keep going. I also modified the code to print the index name in case of failure, because without that the user doesn't know the failing index name in a database-wide cluster failure. Here is an example: test= cluster test_gist_idx on test; ERROR: cannot cluster on index test_gist_idx because access method does not handle null values HINT: You may be able to work around this by marking column a NOT NULL. test= cluster; WARNING: cannot cluster on index test_gist_idx because access method does not handle null values HINT: You may be able to work around this by marking column a NOT NULL. CLUSTER You can see the ERROR for a specific index, and WARNING for full database cluster. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: [PATCHES] Update psql and pg_dump for new COPY api
Thinking about this - with the new PQputCopyEnd, the sending of \. can probably also be removed. Chris Christopher Kings-Lynne wrote: This patch updates psql and pg_dump to use the new copy api. Probably needs some review. I have tested it with dos and unix newlines, etc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [WIP] shared row locks
I implemented the user-visible side of this (FKs in particular) using a new FOR SHARE clause to SELECT. This is of course open to suggestions. Inside the grammar I hacked it using the productions for FOR UPDATE, and stashed a String as the first node of the relid List. Well MySQL uses IN SHARE MODE... http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Update psql and pg_dump for new COPY api
This patch updates psql and pg_dump to use the new copy api. Probably needs some review. I have tested it with dos and unix newlines, etc. Chris ? src/bin/initdb/.deps ? src/bin/initdb/initdb ? src/bin/ipcclean/ipcclean ? src/bin/pg_config/.deps ? src/bin/pg_config/pg_config ? src/bin/pg_controldata/.deps ? src/bin/pg_controldata/pg_controldata ? src/bin/pg_ctl/.deps ? src/bin/pg_ctl/pg_ctl ? src/bin/pg_dump/.deps ? src/bin/pg_dump/file ? src/bin/pg_dump/pg_dump ? src/bin/pg_dump/pg_dumpall ? src/bin/pg_dump/pg_restore ? src/bin/pg_resetxlog/.deps ? src/bin/pg_resetxlog/pg_resetxlog ? src/bin/psql/.deps ? src/bin/psql/dos.sql ? src/bin/psql/dump.sql ? src/bin/psql/dump2.sql ? src/bin/psql/file.sql ? src/bin/psql/psql ? src/bin/psql/test.sql ? src/bin/scripts/.deps ? src/bin/scripts/clusterdb ? src/bin/scripts/createdb ? src/bin/scripts/createlang ? src/bin/scripts/createuser ? src/bin/scripts/dropdb ? src/bin/scripts/droplang ? src/bin/scripts/dropuser ? src/bin/scripts/vacuumdb Index: src/bin/pg_dump/pg_dump.c === RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.404 diff -c -r1.404 pg_dump.c *** src/bin/pg_dump/pg_dump.c 14 Mar 2005 18:57:33 - 1.404 --- src/bin/pg_dump/pg_dump.c 25 Mar 2005 05:44:27 - *** *** 776,783 * to be dumped. */ - #define COPYBUFSIZ8192 - static int dumpTableData_copy(Archive *fout, void *dcontext) { --- 776,781 *** *** 790,796 PGresult *res; int ret; boolcopydone; ! charcopybuf[COPYBUFSIZ]; const char *column_list; if (g_verbose) --- 788,794 PGresult *res; int ret; boolcopydone; ! char*copybuf; const char *column_list; if (g_verbose) *** *** 836,863 while (!copydone) { ! ret = PQgetline(g_conn, copybuf, COPYBUFSIZ); ! ! if (copybuf[0] == '\\' ! copybuf[1] == '.' ! copybuf[2] == '\0') ! { ! copydone = true;/* don't print this... */ ! } ! else ! { ! archputs(copybuf, fout); ! switch (ret) ! { ! case EOF: ! copydone = true; ! /* FALLTHROUGH */ ! case 0: ! archputs(\n, fout); ! break; ! case 1: ! break; ! } } /* --- 834,855 while (!copydone) { ! ret = PQgetCopyData(g_conn, copybuf, false); ! switch (ret) { ! case -1: ! copydone = true; ! break; ! case 0: ! case -2: ! write_msg(NULL, SQL command to dump the contents of table \%s\ failed: PQgetCopyData() failed.\n, classname); ! write_msg(NULL, Error message from server: %s, PQerrorMessage(g_conn)); ! write_msg(NULL, The command was: %s\n, q-data); ! exit_nicely(); ! break; ! default: ! archputs(copybuf, fout); ! PQfreemem(copybuf); ! break; } /* *** *** 903,917 } archprintf(fout, \\.\n\n\n); - ret = PQendcopy(g_conn); - if (ret != 0) - { - write_msg(NULL, SQL command to dump the contents of table \%s\ failed: PQendcopy() failed.\n, classname); - write_msg(NULL, Error message from server: %s, PQerrorMessage(g_conn)); - write_msg(NULL, The command was: %s\n, q-data); - exit_nicely(); - } - PQclear(res); destroyPQExpBuffer(q); return 1; --- 895,900 Index: src/bin/psql/copy.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/copy.c,v retrieving revision 1.56 diff -c -r1.56 copy.c *** src/bin/psql/copy.c 22 Feb 2005 04:40:54 - 1.56 --- src/bin/psql/copy.c 25 Mar 2005 05:44:28 - *** *** 580,589 return success; } - - #define COPYBUFSIZ 8192 /* size doesn't matter */ - - /* * handleCopyOut * receives data as a result of a
Re: [PATCHES] WIP: make EXPLAIN ANALYZE show time spent in triggers
The attached patch allows EXPLAIN ANALYZE to break out the time spent in triggers when EXPLAINing a statement that can fire triggers. Formerly this time was included in Total runtime but not otherwise accounted for. Very nice. An example is regression=# explain analyze delete from foo; QUERY PLAN Seq Scan on foo (cost=0.00..172.70 rows=11770 width=6) (actual time=0.063..86.650 rows=1 loops=1) Trigger RI_ConstraintTrigger_60781: time=3899.609 calls=1 Total runtime: 4218.309 ms (3 rows) Could we get plain EXPLAIN output as well: regression=# explain analyze delete from foo; QUERY PLAN Seq Scan on foo (cost=0.00..172.70 rows=11770 width=6) Trigger RI_ConstraintTrigger_60781: calls=11770 (2 rows) Also, have you considered statement level triggers? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] WIP: make EXPLAIN ANALYZE show time spent in triggers
Could we get plain EXPLAIN output as well: Plain EXPLAIN doesn't run the query, and therefore not the triggers. But the point of it is to estimate, right? Could it not estimate the number of time each trigger would be called. Surely that's the same as estimating the number of rows each clause will return? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] WIP: make EXPLAIN ANALYZE show time spent in triggers
Right. So what's the point? It seems like the printout would just be useless noise: it'd repeat N times the estimate of the top-level number of output rows. Well I guess the point would be to remind people that there are (potentially) expensive triggers that will run, so even though the analysis presented indicates that the tested query will be fast, it might not be... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WIP: make EXPLAIN ANALYZE show time spent in triggers
The attached patch allows EXPLAIN ANALYZE to break out the time spent in triggers when EXPLAINing a statement that can fire triggers. Formerly this time was included in Total runtime but not otherwise accounted for. Actually, should you make it talk about RULEs as well? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
I find this tiny (9-line) patch useful to help my clients know when FSM settings may need updating. Some of the more frequently asked questions here are in regards to FSM settings. One hint I've seen is to run vacuum verbose;. At the end of thousands of lines of INFO and DETAIL messages vacuum verbose has 2 separate lines with some numbers to compare (total pages needed and FSM size...pages) that help indicate too low fsm settings. I've gotten into the habit of always installing the following patch (below) that automatically does this comparison for me, and if max_fsm_pages is too small, it logs a warning as shown here: patched=# vacuum; WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832) VACUUM I think this patch is great. I can never figure out how to set those settings easily. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Change to -f in examples with input files
Uh, how is it more expressive? The only difference I see is the line numbers. Is that it? That could be a very big deal in case of error on a large file, so yes. In IRC, I always recommend that ppl use -f, since it's so much more useful :) Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
This one from Ben Calvert. It uses the (imho clearer) NOW() rather than 'NOW' in a PL/PgSQL function example. Applied, thanks. Why not use CURRENT_TIMSTAMP instead of NOW() everywhere in the docs. I mean, it's standard and NOW() isn't... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Continue transactions after errors in psql
I've attached a revised patch which fixes the problem, however I'm sure there is a better way. Thanks to Neil for putting up with me on irc :-) How about calling the savepoint pg_psql_savepoint instead, that way it follows our 'don't begin things with pg_' philosophy. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Allow pooled connections to list all prepared queries
I don't see this as all that helpful for a client interface that does the preparation itself. Possibly it could be used for libpq, but you mentioned DBI which should already know what it has or has not prepared. The idea of adding a network round trip to detect a prepared statement seems like a performance loss, not a gain. If this is just to avoid repreparing the same statement then perhaps something like PREPARE OR REPLACE would be more useful. Yes, PREPARE OR REPLACE was the other thing I thought would be useful. However, in my example that still means preparing everytime, wasting the benefits. You could have: PREPARE IF NOT EXISTS :P Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] PITR Archive Recovery plus WIP PITR
I am not fond of the timeline idea, especially for 7.5. Let's get usage cases submitted first. I can imagine timelines as causing significant confusion during restore, which is the last thing we want to do. I think that judgment is exactly backward. *Not* having timelines is what will cause serious and possibly fatal mistakes during restore: people will hand the wrong xlog files to restore and the software will be unable to recognize the inconsistency. We really need to get this right the first time. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] PITR Archive Recovery plus WIP PITR
Please ignore- seems some old mail of mine got sent waaay late... Christopher Kings-Lynne wrote: I am not fond of the timeline idea, especially for 7.5. Let's get usage cases submitted first. I can imagine timelines as causing significant confusion during restore, which is the last thing we want to do. I think that judgment is exactly backward. *Not* having timelines is what will cause serious and possibly fatal mistakes during restore: people will hand the wrong xlog files to restore and the software will be unable to recognize the inconsistency. We really need to get this right the first time. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] Improvement to pg_trgm readme
This adds mention of my latest tweak to the tsearch2/pg_trgm integration. It is much better to create a word list of unstemmed words than stemmed ones. Chris Index: contrib/pg_trgm/README.pg_trgm === RCS file: /projects/cvsroot/pgsql/contrib/pg_trgm/README.pg_trgm,v retrieving revision 1.1 diff -c -r1.1 README.pg_trgm *** contrib/pg_trgm/README.pg_trgm 31 May 2004 17:18:11 - 1.1 --- contrib/pg_trgm/README.pg_trgm 26 Nov 2004 01:31:39 - *** *** 100,110 The first step is to generate an auxiliary table containing all the unique words in the Tsearch2 index: ! CREATE TABLE words AS ! SELECT word FROM stat('SELECT vector FROM documents'); ! Where 'documents' is the table that contains the Tsearch2 index ! column 'vector', of type 'tsvector'. Next, create a trigram index on the word column: --- 100,114 The first step is to generate an auxiliary table containing all the unique words in the Tsearch2 index: ! CREATE TABLE words AS SELECT word FROM ! stat('SELECT to_tsvector(''simple'', bodytext) FROM documents'); ! Where 'documents' is a table that has a text field 'bodytext' ! that TSearch2 is used to search. The use of the 'simple' dictionary ! with the to_tsvector function, instead of just using the already ! existing vector is to avoid creating a list of already stemmed ! words. This way, only the original, unstemmed words are added ! to the word list. Next, create a trigram index on the word column: ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] htmlhelp generation
Um ... what's an htmlhelp? It's the kind of format the Windows'ish programs use for their internal help browsers. It consists of regular HTML plus some index files. pgAdmin needs it, and maybe the Windows binary package would like it as well. I've trivially generated them from docbook xml using the htmlhelp.xsl stylesheet that comes with docbook and the free html help compiler from MS. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Change recycled transaction log from LOG to DEBUG1
May as well downgrade the CLOG message as well then. Chris Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message On August 9, Tom Lane wrote regarding recycled transaction log file messages appearing in the server logs: This is normal behavior. At some point we'll probably knock down the priority of those messages from LOG to DEBUG... I'm all for it, for I am tired of seeing those messages in my logs as well. I'd make a link to Tom's original post, but once again archives.postgresql.org is not working. Index: xlog.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/access/transam/xlog.c,v retrieving revision 1.168 diff -c -r1.168 xlog.c *** xlog.c 30 Aug 2004 02:54:38 - 1.168 --- xlog.c 5 Sep 2004 15:26:43 - *** *** 2154,2160 true, XLOGfileslop, true)) { ! ereport(LOG, (errmsg(recycled transaction log file \%s\, xlde-d_name))); } --- 2154,2160 true, XLOGfileslop, true)) { ! ereport(DEBUG1, (errmsg(recycled transaction log file \%s\, xlde-d_name))); } -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200409051137 -BEGIN PGP SIGNATURE- iD8DBQFBOzNBvJuQZxSWSsgRApOvAKCPoVcSR29wft9RIxk4bi3R5SkahQCg0yKr ISROl8QXPuh8g16O+zRCeus= =dFk7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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: [PATCHES] Allow psql to work against non-tablespace servers (e.g.
One of the things I still intend to do is make psql work against all previous backends, so this patch is a good first step :) For example, we have web servers on database servers on different machines. Recompiling psql on the web servers is a PITA since it means recompiling PHP then recompiling all the updated stuff that PHP depends on - it's a nightmare. Ideally we could just update the server and then update web servers much later or never... If I can't use \db to see tablespaces, then I'll live :) Chris Bruce Momjian wrote: I don't see any other code in psql that allows it to run with older server versions so it doesn't make sense to me to fix things just for tablespaces, and doing it for everything seems like it would uglify the code too much. --- Greg Sabino Mullane wrote: Index: describe.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v retrieving revision 1.103 diff -c -r1.103 describe.c *** describe.c 15 Jul 2004 03:56:06 - 1.103 --- describe.c 11 Aug 2004 21:15:34 - *** *** 112,117 --- 112,123 PGresult *res; printQueryOpt myopt = pset.popt; + if (pset.sversion 70500) { + fprintf(stderr, _(This server version (%d) does not support tablespaces.\n), + pset.sversion); + return true; + } + initPQExpBuffer(buf); printfPQExpBuffer(buf, *** *** 706,713 /* Get general table info */ printfPQExpBuffer(buf, SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n ! relhasoids, reltablespace \n FROM pg_catalog.pg_class WHERE oid = `%s`, oid); res = PSQLexec(buf.data, false); if (!res) --- 712,720 /* Get general table info */ printfPQExpBuffer(buf, SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n ! relhasoids %s \n FROM pg_catalog.pg_class WHERE oid = `%s`, + pset.sversion = 70500 ? , reltablespace : , oid); res = PSQLexec(buf.data, false); if (!res) *** *** 729,735 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), t) == 0; tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), t) == 0; tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), t) == 0; ! tableinfo.tablespace = atooid(PQgetvalue(res, 0, 6)); PQclear(res); headers[0] = _(Column); --- 736,743 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), t) == 0; tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), t) == 0; tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), t) == 0; ! tableinfo.tablespace = (pset.sversion = 70500) ? ! atooid(PQgetvalue(res, 0, 6)) : 0; PQclear(res); headers[0] = _(Column); *** *** 932,939 footers = pg_malloc_zero(4 * sizeof(*footers)); footers[count_footers++] = pg_strdup(tmpbuf.data); ! add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace, ! footers, count_footers, tmpbuf); footers[count_footers] = NULL; } --- 940,947 footers = pg_malloc_zero(4 * sizeof(*footers)); footers[count_footers++] = pg_strdup(tmpbuf.data); ! add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace, ! footers, count_footers, tmpbuf); footers[count_footers] = NULL; } Index: settings.h === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/settings.h,v retrieving revision 1.18 diff -c -r1.18 settings.h *** settings.h 12 May 2004 13:38:45 - 1.18 --- settings.h 11 Aug 2004 21:15:34 - *** *** 41,47 FILE *cur_cmd_source; /* describe the status of the current main * loop */ bool cur_cmd_interactive; ! const char *progname; /* in case you renamed psql */ char *inputfile; /* for error reporting */ unsigned lineno; /* also for error reporting */ --- 41,47 FILE *cur_cmd_source; /* describe the status of the current main * loop */ bool cur_cmd_interactive; ! int sversion; /* backend server version */ const char *progname; /* in case you renamed psql */ char *inputfile; /* for error reporting */ unsigned lineno; /* also for error reporting */ Index: startup.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/startup.c,v retrieving revision 1.95 diff -c -r1.95 startup.c *** startup.c 3 Jun 2004 00:07:37 - 1.95 --- startup.c 11 Aug 2004 21:15:34 - *** *** 217,222 --- 217,225 SyncVariables(); + /* Grab the backend server version */ + pset.sversion = PQserverVersion(pset.db); + if (options.action == ACT_LIST_DB) { int success = listAllDbs(false); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Allow psql to work against non-tablespace servers (e.g.
If I get time, I will begin making psql backward-compatible to 7.3 and further, but the changes to do so will not be as small as this patch and I would rather they get evaluated separately. But do we want to do this? Is it worth doing, and maintaining? Yes please, I'll maintain it as well :) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Allow psql to work against non-tablespace servers (e.g.
At least we are guaranteed to compile against current libraries - apps outside the source tree never get that luxury and have worse compatibility problems. :) The issue is that no one has been asking for this functionality, and I can imagine it becoming quite a mess after a few releases. No messier than pg_dump (which yes, is quite messy :) ) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] That dump-comments-on-composite-type-columns patch...
$ pg_dump regression zzz.out pg_dump: SQL command failed pg_dump: Error message from server: ERROR: complex is a composite type pg_dump: The command was: COPY public.complex (r, i) TO stdout; $ That could be fixed by just checking the relkind when dumping table data, but hey. I suspect it had more subtle problems too, because dumpTableComments would have attached the comments to the dumpid associated with the TableInfo entry, which isn't the object that will get dumped. So it seems moderately likely that there would have been a potential for misordering of the output. Ok. I think it's probably a fundamentally bad idea to be putting composite types into pg_dump's TableInfo array, because they just really aren't tables at all. If you want to try again, I'd suggest writing a variant of dumpTableComment that takes a TypeInfo and the attribute-names query data obtained by dumpCompositeType. You mean unlike views, sequences and all other kinds of junk? :) OK, I can do this, but I don't think I'll have time for the first beta. Chris ps. Did you back out the moving of owner to commands as well? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] psql latex bugfixes
OK, it looks good. I don't have latex handy to build it, but it looks fine to me... Chris Bruce Momjian wrote: If you would like to review it I will apply it. --- Christopher Kings-Lynne wrote: Surely this is a really good bug fix and should be in 7.5? Bruce Momjian wrote: This has been saved for the 7.6 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 --- Roger Leigh wrote: I have noticed that the latex format in psql has some bugs: ? _ is not escaped, and causes TeX to abort, thinking it's a subscript outside of maths mode. Most of my table and field names use underscores, so this is a really nasty one. ? The column count is calculated using the contents of opt_align. But opt_align has one extra element, and so it's always one too many. I changed it to count the column headings, like all the other output formats. There may be a bug in computing opt_align that this patch does not address, but I'm not yet familiar enough with the psql source to fix this as well. ? The line drawing rules for each border setting (0-3) and expanded mode didn't always match the documented behaviour and what other formats (e.g. aligned) did. I made it as conformant as possible, and also tidied the alignment of the first line of the footer, which was incorrectly indented. I've attached some example output with this patch applied. Regards, Roger Index: src/bin/psql/print.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/print.c,v retrieving revision 1.48 diff -u -r1.48 print.c --- src/bin/psql/print.c23 May 2004 22:20:10 - 1.48 +++ src/bin/psql/print.c1 Aug 2004 22:54:22 - @@ -769,7 +769,7 @@ /*/ -/* LaTeX*/ +/* LaTeX*/ /*/ @@ -790,6 +790,9 @@ case '$': fputs(\\$, fout); break; + case '_': + fputs(\\_, fout); + break; case '{': fputs(\\{, fout); break; @@ -817,7 +820,6 @@ { unsigned int col_count = 0; unsigned int i; - const char *cp; const char *const * ptr; @@ -829,42 +831,39 @@ fputs(\n\\end{center}\n\n, fout); } + /* count columns */ + for (ptr = headers; *ptr; ptr++) + col_count++; + /* begin environment and set alignments and borders */ fputs(\\begin{tabular}{, fout); - if (opt_border == 0) - fputs(opt_align, fout); - else if (opt_border == 1) - { - for (cp = opt_align; *cp; cp++) - { - if (cp != opt_align) - fputc('|', fout); - fputc(*cp, fout); - } - } - else if (opt_border == 2) + + if (opt_border == 2) + fputs(| , fout); +for (i = 0; i col_count; i++) { - for (cp = opt_align; *cp; cp++) - { - fputc('|', fout); - fputc(*cp, fout); - } - fputc('|', fout); + fputc(*(opt_align + i), fout); + if (opt_border != 0 i col_count - 1) + fputs ( | , fout); } + if (opt_border == 2) + fputs( |, fout); + fputs(}\n, fout); if (!opt_barebones opt_border == 2) fputs(\\hline\n, fout); /* print headers and count columns */ - for (i = 0, ptr = headers; *ptr; i++, ptr++) + for (i = 0, ptr = headers; i col_count; i++, ptr++) { - col_count++; if (!opt_barebones) { if (i != 0) fputs( , fout); +fputs(\\textit{, fout); latex_escaped_print(*ptr, fout); +fputc('}', fout); } } @@ -888,7 +887,7 @@ if (opt_border == 2) fputs(\\hline\n, fout); - fputs(\\end{tabular}\n\n, fout); + fputs(\\end{tabular}\n\n\\noindent , fout); /* print footers */ @@ -951,8 +950,12 @@ if (!opt_barebones) { if (opt_border == 2) + { fputs(\\hline\n, fout); - fprintf(fout, \\multicolumn{2}{c}{Record %d} \n, record++); + fprintf(fout, \\multicolumn{2}{|c|}{\\textit{Record %d}} \n, record
Re: [PATCHES] More fixes for pg_dump
Applied. I notice though that the line numbers don't seem to match CVS tip, which suggests that you've got some patches still unapplied? If so, Bruce seems to have missed adding them to the patch queue. Hmmm, no... Not sure what happened there. The diff in the committers email seemed fine however. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Troff -ms output for psql
You mean 8.1 release :P Bruce Momjian wrote: This has been saved for the 7.6 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] psql latex bugfixes
Surely this is a really good bug fix and should be in 7.5? Bruce Momjian wrote: This has been saved for the 7.6 release: http:/momjian.postgresql.org/cgi-bin/pgpatches2 --- Roger Leigh wrote: I have noticed that the latex format in psql has some bugs: ? _ is not escaped, and causes TeX to abort, thinking it's a subscript outside of maths mode. Most of my table and field names use underscores, so this is a really nasty one. ? The column count is calculated using the contents of opt_align. But opt_align has one extra element, and so it's always one too many. I changed it to count the column headings, like all the other output formats. There may be a bug in computing opt_align that this patch does not address, but I'm not yet familiar enough with the psql source to fix this as well. ? The line drawing rules for each border setting (0-3) and expanded mode didn't always match the documented behaviour and what other formats (e.g. aligned) did. I made it as conformant as possible, and also tidied the alignment of the first line of the footer, which was incorrectly indented. I've attached some example output with this patch applied. Regards, Roger Index: src/bin/psql/print.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/print.c,v retrieving revision 1.48 diff -u -r1.48 print.c --- src/bin/psql/print.c23 May 2004 22:20:10 - 1.48 +++ src/bin/psql/print.c1 Aug 2004 22:54:22 - @@ -769,7 +769,7 @@ /*/ -/* LaTeX*/ +/* LaTeX*/ /*/ @@ -790,6 +790,9 @@ case '$': fputs(\\$, fout); break; + case '_': + fputs(\\_, fout); + break; case '{': fputs(\\{, fout); break; @@ -817,7 +820,6 @@ { unsigned int col_count = 0; unsigned int i; - const char *cp; const char *const * ptr; @@ -829,42 +831,39 @@ fputs(\n\\end{center}\n\n, fout); } + /* count columns */ + for (ptr = headers; *ptr; ptr++) + col_count++; + /* begin environment and set alignments and borders */ fputs(\\begin{tabular}{, fout); - if (opt_border == 0) - fputs(opt_align, fout); - else if (opt_border == 1) - { - for (cp = opt_align; *cp; cp++) - { - if (cp != opt_align) - fputc('|', fout); - fputc(*cp, fout); - } - } - else if (opt_border == 2) + + if (opt_border == 2) + fputs(| , fout); +for (i = 0; i col_count; i++) { - for (cp = opt_align; *cp; cp++) - { - fputc('|', fout); - fputc(*cp, fout); - } - fputc('|', fout); + fputc(*(opt_align + i), fout); + if (opt_border != 0 i col_count - 1) + fputs ( | , fout); } + if (opt_border == 2) + fputs( |, fout); + fputs(}\n, fout); if (!opt_barebones opt_border == 2) fputs(\\hline\n, fout); /* print headers and count columns */ - for (i = 0, ptr = headers; *ptr; i++, ptr++) + for (i = 0, ptr = headers; i col_count; i++, ptr++) { - col_count++; if (!opt_barebones) { if (i != 0) fputs( , fout); +fputs(\\textit{, fout); latex_escaped_print(*ptr, fout); +fputc('}', fout); } } @@ -888,7 +887,7 @@ if (opt_border == 2) fputs(\\hline\n, fout); - fputs(\\end{tabular}\n\n, fout); + fputs(\\end{tabular}\n\n\\noindent , fout); /* print footers */ @@ -951,8 +950,12 @@ if (!opt_barebones) { if (opt_border == 2) + { fputs(\\hline\n, fout); - fprintf(fout, \\multicolumn{2}{c}{Record %d} \n, record++); + fprintf(fout, \\multicolumn{2}{|c|}{\\textit{Record %d}} \n, record++); + } + else + fprintf(fout, \\multicolumn{2}{c}{\\textit{Record %d}} \n, record++); } if (opt_border = 1)
Re: [PATCHES] fix schema ownership on first connection preliminary
I'm also wondering about what side-effects this will have on pg_dump behavior. In particular, will pg_dump try to ALTER OWNER public, and if so will that be appropriate? We haven't previously needed to assume that we are restoring into a database with the same datowner as we dumped from... In my batch of pg_dump patches that have gone in, pg_dump will now do exactly that. The reason was so that if someone altered the owner of their public schema, it should be dumped like that. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Fix for OWNER TO breaking ACLs
* You had consistently changed the simple_heap_update calls to do the wrong thing. (I'm surprised it didn't blow up on you in your testing.) In a sequence like newtuple = heap_modifytuple(tup, rel, repl_val, repl_null, repl_repl); simple_heap_update(rel, newtuple-t_self, newtuple); CatalogUpdateIndexes(rel, newtuple); the second parameter to simple_heap_update *must* be newtuple-t_self not tup-t_self. The reason is that simple_heap_update stores the new physical location of the updated tuple back into that parameter, and then the CatalogUpdateIndexes call relies on newtuple-t_self to generate new index entries. The way you had it coded, it was generating new index entries pointing at the old version of the tuple ... Strange. I guess I must have been testing with a database that had short enough system catalogs that the indexes were never used? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] USING INDEX TABLESPACE
Applied. I also added some code to ruleutils.c to make pg_get_constraintdef output the USING INDEX TABLESPACE clause at need. We might want to think about getting pg_dump to rely more on pg_get_constraintdef and friends, instead of native wit ... Yes, that was really dumb of me not to think of that :/ Especially since I wrote the primary key and unique parts of pg_get_constraintdef... :P I would like to have pg_get_xxxdef for everything. Tables, whole databases, etc. :) Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] Fix for OWNER TO breaking ACLs
Attached is a patch that fixes the owner change command on objects that have privileges. It probably needs a once over review since it involves a decent amount of pointer arithmetic. Note that languages don't have owners, and hence don't need fixing. The owner change acl support is as follows: 1. If the acl is currently null, then don't do anything with the acl 2. If it is non null, then call aclnewowner() to get a new acl 3. aclnewowner() first looks for a grantee that is the new owner already and remembers this item 4. A new acl is generated as a copy of the old acl, exlcuding the item above if it exists. During the copy, any grantors or grantees in the new acl that refer to the old owner are changed to refer to the new owner. 5. The excluded acl item's rights (if it existed) are merged with those of the old owner and become the new owner's rights. Chris newowner.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] USING INDEX TABLESPACE
create table test (a integer primary key index tablespace loc); create table test (a integer unique index tablespace loc); create table test (a integer); alter table test add primary key(a) index tablespace loc; create table test (a integer); alter table test add unique(a) index tablespace loc; Crap! I left out the word 'using' on all those examples :/ create table test (a integer primary key using index tablespace loc); create table test (a integer unique using index tablespace loc); create table test (a integer); alter table test add primary key(a) using index tablespace loc; create table test (a integer); alter table test add unique(a) using index tablespace loc; Chris ---(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
[PATCHES] USING INDEX TABLESPACE
The attached patch allows the specification of the tablespace the index is to be created in for unique and pk constraints. It also fixes the dumping of such constraints so that they are restored into the correct tablespace, after they have been moved with SET TABLESPACE. This is currently an outstanding pg_dump bug. The syntax is exactly what Oracle uses as far as i can tell, and I think that we need 'using index' in there, because it's the index that's in a tablespace, NOT the constraint itself. Includes docs. eg: create table test (a integer primary key index tablespace loc); create table test (a integer unique index tablespace loc); create table test (a integer); alter table test add primary key(a) index tablespace loc; create table test (a integer); alter table test add unique(a) index tablespace loc; Chris conspc.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Function to kill backend
If you want to put in the function and document that it may cause problems, I won't object; it's not like we don't have other features that are poorly implemented :-(. But my vote would be to remove it. I'm down with removing it - people don't read documentation :/ Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] More fixes for pg_dump
This patch does two things to pg_dump: * Dumps comments on columns of composite types * Instead of putting all the OWNER TO commands at the end, it dumps then after each object. This is WAY more readable and nice. ACLs are still at the end. Chris pg_dump5.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] Function to kill backend
Would you use a kill operation in the way you describe above if you knew that it had, say, a 1% chance of causing a database-wide PANIC each time you used it? The odds of a problem are probably a great deal less than 1%, especially if the backend is sitting idle. But they're not nil, and I don't think we have the resources to make them nil in this release cycle. Therefore I'm uneager to provide this feature simply because of it might be nice to have arguments. There's a lot of other stuff that is higher on the priority list, IMHO anyway. Can we keep the cancel query function and just lose the kill one? Chris ---(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: [PATCHES] [HACKERS] Function to kill backend
The first time I used it was for precisely this reason - some buggy PHP code opened hundreds of connections to a dev server which then remained open doing nothing except wasting resources. It was particularly useful in that case as I didn't have access to the web server at the time. Shortly afterwards I added support to pgAdmin's server status tool which has proven quite handy (although I will admit, mainly for canceling ather than terminating). Yeah, I've added the kill and cancel commands to phppgadmin. I'm happy if kill is removed though, i don't want my newbie users panicing their machines. phpmyadmin has both kill and cancel since they're sql commands in mysql. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Yes, the reason it would be nice for me is that currently if you want to dump two specific, related tables from your db, there's no way to do it with pg_dump within the one transactions (ie. maintaining integrity). I guess I'm in favour of -t -t but not -T depending on the complexity of it. I'll review the patch if you like. One problem with this patch is that there's no way to dump multiple tables in different schemas. Does this matter? It's a bit non-orthogonal... Chris ---(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: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
No, it doesn't. I can look into that if you like. The patch was entirely to satisfy a need some of our customers have. The -T switch does fill a real need for our customers; our product has a couple of tables that aren't critical if they aren't backed up, but as the product evolves, we occasionally add more tables. So it's easier to use a -T switch to say what *not* to back up, than multiple -t switches to say what to back up. Well, since you wrote the patch, you'd be better off munging it. Read Tom's comments and see what you can come up with. There's been no decision made yet though on what changes to make however. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Point in Time Recovery
I'm in favour of how it is now, so long as the comment is clear. It's the Unix Way :) Chris I'd vote for it as a clarity factor too. Klaus Naumann wrote: On Tue, 20 Jul 2004 [EMAIL PROTECTED] wrote: FATAL: unrecognized configuration parameter archive_mode Have I missed something since it has been committed? Yes, Tom has removed this option in favorite of just setting archive_command to a value which then enables the PITR code also. But as I've seen this isn't discussed to the very end currently. My 2ct: I'd prefer to have archive_mode in the config as it really makes clear that this database is archiving. I fear users will not understand that giving a program for archival will also enable the PITR function. Greetings, Klaus ---(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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] Point in Time Recovery
Okay, we agree on that part at least; I'll take care of it. If anyone wants to argue for further copying during initdb, that can be added later. I reckon it should be copied into $PGDATA :) Otherwise, when I'm in a panic at recovery time, I'd have to figure out where the heck my package has installed the share conf file to, conf files usually aren't in share, etc., etc. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
I see one vote in favor of its inclusion on the grounds it is a bug not to support multiple -t parameters. However, is someone objects I will have to hold it for 7.6. It needs SGML doc additions which I will do myself. Well, I guess I'm against it based on the rules of feature freeze, even though it would be really useful for me :( I don't see how it's a bug to not support multiple parameters thought - that's really scraping the bottom of the barrel... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Well, I guess I'm against it based on the rules of feature freeze, even though it would be really useful for me :( It would have been a lot easier to approve it if it'd arrived on June 30 rather than July 6 :-(. However, I do believe that David originally submitted a slightly-too-late version of this in the previous release cycle, so maybe we could cut him a little slack and pretend this is a mistakenly-forgotten patch that we held over from 7.4. Yes, the reason it would be nice for me is that currently if you want to dump two specific, related tables from your db, there's no way to do it with pg_dump within the one transactions (ie. maintaining integrity). I guess I'm in favour of -t -t but not -T depending on the complexity of it. I'll review the patch if you like. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] pg_autovacuum integration attempt #2
The thing I was trying to do was use the GUC hook function to make sure that the required GUC variables are also set before GUC reports autovac as enabled. This seemed cleaner to me, but apparently it won't work since it seems that autovac_enabled is read from GUC before the stats variables, and there is no way to force the order in which they are read. Am I missing something? Can we please have it default to enabled :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] pg_autovacuum integration attempt #2
Can we please have it default to enabled :) We can but without also enabling statistics it will not work. Do we want to enable both by default? Weeell...it just seemed to me that we won't cut down on the support mails unless it's on by default... I mean at some point in the future, we WILL have to have it on by default, surely? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] Fixes for 3 bugs in pg_dump
This patch fixes the following bugs: * pg_dump --clean against a pre-7.3 server output drop commands in the form: DROP .foo; . These will now all be output as: DROP public.foo; * If you use ALTER USER to set user params on the cluster owner user, these would not be dumped. This patch will now dump ALTER USER commands for the cluster owner. If the -S command line switch is used to specify a superuser, then the cluster owner ALTER USER commands will be dumped for that user instead. * The DateStyle and search_path GUC variables must not be quoted when dumped. Chris pg_dump_fixes.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Fixes for 3 bugs in pg_dump
Actually, i'm not sure that making it respect -S is the right way to go. What we really need is an option that specifies the cluster owner on the new installation. I will revert that part of this patch and resubmit shortly... Chris Christopher Kings-Lynne wrote: This patch fixes the following bugs: * pg_dump --clean against a pre-7.3 server output drop commands in the form: DROP .foo; . These will now all be output as: DROP public.foo; * If you use ALTER USER to set user params on the cluster owner user, these would not be dumped. This patch will now dump ALTER USER commands for the cluster owner. If the -S command line switch is used to specify a superuser, then the cluster owner ALTER USER commands will be dumped for that user instead. * The DateStyle and search_path GUC variables must not be quoted when dumped. Chris ---(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 4: Don't 'kill -9' the postmaster
[PATCHES] Better fixes for pg_dump bugs
OK, This dump is a proper fix for the three bugs mentioned in the first email. It now just outputs an ALTER USER command for the cluster owner and has nothing to do with the '-S' switch. It also fixes the other two issues. Chris pg_dump_fixes2.txt.gz Description: application/gunzip ---(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: [PATCHES] add missing options to pg_dumpall
Hmmm, and I need to resurrect the -X use-set-session-authorization flag for pg_dumpall as well...patch coming soon... Chris Stefan Kaltenbrunner wrote: Bruce Momjian wrote: Patch applied. Thanks. thanks - that's wonderful news :-) However the patch as it went in has a minor cosmetic issues with the display of the --help output. Maybe something like the attached patch should be applied to restore the alphabetical option ordering and make the output more like the pg_dump output. Stefan Index: src/bin/pg_dump/pg_dumpall.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v retrieving revision 1.44 diff -u -r1.44 pg_dumpall.c --- src/bin/pg_dump/pg_dumpall.c 12 Jul 2004 14:35:45 - 1.44 +++ src/bin/pg_dump/pg_dumpall.c 14 Jul 2004 17:56:04 - @@ -310,26 +310,26 @@ printf(_(Usage:\n)); printf(_( %s [OPTION]...\n), progname); - printf(_(\nOptions:\n)); + printf(_(\nGeneral options:\n)); + printf(_( -i, --ignore-version proceed even when server version mismatches\n + pg_dumpall version\n)); + printf(_( --help show this help, then exit\n)); + printf(_( --versionoutput version information, then exit\n)); + printf(_(\nOptions controlling the output content:\n)); printf(_( -a, --data-only dump only the data, not the schema\n)); printf(_( -c, --clean clean (drop) databases prior to create\n)); printf(_( -d, --insertsdump data as INSERT, rather than COPY, commands\n)); printf(_( -D, --column-inserts dump data as INSERT commands with column names\n)); printf(_( -g, --globals-only dump only global objects, no databases\n)); - printf(_( -i, --ignore-version proceed even when server version mismatches\n - pg_dumpall version\n)); - printf(_( -s, --schema-onlydump only the schema, no data\n)); - printf(_( -S, --superuser=NAME specify the superuser user name to use in the dump\n)); printf(_( -o, --oids include OIDs in dump\n)); printf(_( -O, --no-owner do not output commands to set object ownership\n)); - printf(_( -v, --verboseverbose mode\n)); + printf(_( -s, --schema-onlydump only the schema, no data\n)); + printf(_( -S, --superuser=NAME specify the superuser user name to use in the dump\n)); printf(_( -x, --no-privileges do not dump privileges (grant/revoke)\n)); printf(_( -X disable-dollar-quoting, --disable-dollar-quoting\n disable dollar quoting, use SQL standard quoting\n)); printf(_( -X disable-triggers, --disable-triggers\n - disable triggers during data-only restore\n)); - printf(_( --help show this help, then exit\n)); - printf(_( --versionoutput version information, then exit\n)); + disable triggers during data-only restore\n)); printf(_(\nConnection options:\n)); printf(_( -h, --host=HOSTNAME database server host or socket directory\n)); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Better fixes for pg_dump bugs
Hehe - actually, don't commit this either, I keep finding more and more bugs in pg_dump... Chris Christopher Kings-Lynne wrote: OK, This dump is a proper fix for the three bugs mentioned in the first email. It now just outputs an ALTER USER command for the cluster owner and has nothing to do with the '-S' switch. It also fixes the other two issues. Chris ---(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 7: don't forget to increase your free space map settings
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Please remove that; if I thought either one was a good idea, I would have allowed it in the committed patch. Sequences are too small to be worth moving around, and may someday be reimplemented in a fashion that doesn't use up a separate disk file for each one. If we allow SET TABLESPACE on them we will be limiting our future flexibility for no useful gain. Why do we allow them to be created in tablespaces in the first place then? Seems like a bit of a misfeature? I mean we don't allow views in tablespaces... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Does this patch allow setting the tablespace of sequences as well? If so, then you will need to modify pg_dump of SERIAL sequences. Perhaps output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE definition to move the SERIAL sequence. The same argument applies if it allows moving indexes. (Unique and Primary Keys) Sequences no, toast tables no, indexes yes. So we need the latter part of the above-mentioned patch. Anyone? I'll do it. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
In fact, now that I think of it, the patch-as-committed already introduces some serious headaches for pg_dump: it can't know for sure what name will be assigned to constraint indexes (pkey and unique indexes) so it has no good way to emit ALTER TABLE SET TABLESPACE commands for those indexes. I guess I'll have to make it punt that the name will be what it currently is :( Otherwise, we need to extend the ADD CONSTRAINT syntax. That would be handy because the you could specify the TABLESPACE at creation time as well. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Otherwise, we need to extend the ADD CONSTRAINT syntax. Yeah, I was wondering if there was some minimal-impact way to do that. Oh, or we create ALTER CONSTRAINT :) Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Otherwise, we need to extend the ADD CONSTRAINT syntax. Yeah, I was wondering if there was some minimal-impact way to do that. Shall I hold off on doing any pg_dump changes then? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] OWNER TO mega patch #2
Already been applied by Tom, Bruce. Chris Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Christopher Kings-Lynne wrote: Hi, This is the final patch that adds OWNER TO commands to every object. This includes tablespaces. I have also added RENAME TO on tablespaces. Full docs changes are included (+ 3 new files that must be put in doc/src/sgml/ref). All new regression tests included also. Please review and apply this soon so that I can fix pg_dump to use these new commands before the 30th :) Cheers, Chris [ application/x-gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Updated ALTER TABLE ... SET TABLESPACE patch
Attached is an updated ALTER TABLE ... SET TABLESPACE patch. It uses the block by block copy mechanism proposed by Tom and handles i) ALTER TABLE index and ii) Copying of TOAST tables and the TOAST table's index. It doesn't handle copying of system tables (pg_largeobject) and, in the interests of code reuse, the patch fiddles with the code used by CLUSTER. This isn't great but I wanted to get a patch in before 1 July since I think the feature is very important -- even for the first release. Does this patch allow setting the tablespace of sequences as well? If so, then you will need to modify pg_dump of SERIAL sequences. Perhaps output a ALTER TABLE/SET TABLESPACE command after the CREATE TABLE definition to move the SERIAL sequence. The same argument applies if it allows moving indexes. (Unique and Primary Keys) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Digital Mars C++ - Clients
Other products, such as wxWindows, have seen quite a lot of Digital Mars users embrace it once it started supporting DMC++. That is an extremely poor allegory. wxWindows is a GUI TOOLKIT. PostgreSQL is an independent database server. 99.999% of our windows installations will be from the binary that we will be distributing. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] First attempt: support for '\dg' in psql
I have moved the mention of the dash higher in the TODO file. Maybe you could put the dash in a different color (red?) so it stands out more. Or maybe the whole item could go in a different color if done./ Yes, what about stroke through: Because neither works in the plain text version. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] plperl support for older perl versions
Hmmm, It doesn't apply cleanly for me... Chris Andrew Dunstan wrote: Would people with older versions of perl ( 5.6 I think ) please try the attached patch against what is now on cvs for plperl, and let me know if it compiles, links and runs? (Thanks to Abhijit Menon-Sen for pointing me in the right direction) Thanks andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] pg_dump owner and acl fix #2
OK, This version works wonderfully on my production database. Chris pg_dump_owners2.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] add missing options to pg_dumpall
Hi, This patch adds the following options to pg_dumpall, to be passed to pg_dump: -S, --superuser=NAME -O, --no-owner -X disable-dollar-quoting, --disable-dollar-quoting -X disable-triggers, --disable-triggers Chris pg_dumpall.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] pg_dump --clean w/ = 7.2 server
When running pg_dump --clean against a server that doesn't have schemas the namespace is blank and ends up producing a dump full off things like: DROP TABLE .tab; Since the person is dumping using 7.5 pg_dump, presumably they will be restoring to 7.5, and it should be: DROP TABLE public.tab; Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] OWNER TO mega patch #2
Hi, This is the final patch that adds OWNER TO commands to every object. This includes tablespaces. I have also added RENAME TO on tablespaces. Full docs changes are included (+ 3 new files that must be put in doc/src/sgml/ref). All new regression tests included also. Please review and apply this soon so that I can fix pg_dump to use these new commands before the 30th :) Cheers, Chris owner2.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] Show encoding in initdb messages
Does this: The files belonging to this database system will be owned by user chriskl. This user must also own the server process. The database cluster will be initialized with locale C. The database cluster will be initialized with default encoding UNICODE. creating directory /home/chriskl/local/data ... ok creating directory /home/chriskl/local/data/global ... ok This should save a lot of support requests, hopefully. Chris initdb.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings