Re: [PATCHES] [HACKERS] 8.2 features?

2006-07-18 Thread Christopher Kings-Lynne
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?

2006-07-18 Thread Christopher Kings-Lynne

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?

2006-07-18 Thread Christopher Kings-Lynne

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

2006-07-02 Thread Christopher Kings-Lynne
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

2006-06-01 Thread Christopher Kings-Lynne
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

2006-06-01 Thread Christopher Kings-Lynne
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

2006-03-13 Thread Christopher Kings-Lynne

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

2006-03-12 Thread Christopher Kings-Lynne

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

2006-02-06 Thread Christopher Kings-Lynne
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

2006-01-12 Thread Christopher Kings-Lynne

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

2005-12-22 Thread Christopher Kings-Lynne

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

2005-12-20 Thread Christopher Kings-Lynne

   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

2005-12-20 Thread Christopher Kings-Lynne



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

2005-11-21 Thread Christopher Kings-Lynne
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

2005-11-20 Thread Christopher Kings-Lynne
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

2005-11-17 Thread Christopher Kings-Lynne
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

2005-11-16 Thread Christopher Kings-Lynne

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

2005-11-16 Thread Christopher Kings-Lynne

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

2005-10-27 Thread Christopher Kings-Lynne

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

2005-10-19 Thread Christopher Kings-Lynne
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

2005-10-19 Thread Christopher Kings-Lynne

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

2005-07-24 Thread Christopher Kings-Lynne

 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

2005-07-04 Thread Christopher Kings-Lynne

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)

2005-07-01 Thread Christopher Kings-Lynne
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)

2005-07-01 Thread Christopher Kings-Lynne


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

2005-06-29 Thread Christopher Kings-Lynne

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

2005-06-29 Thread Christopher Kings-Lynne

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

2005-06-29 Thread Christopher Kings-Lynne

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

2005-06-15 Thread Christopher Kings-Lynne
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

2005-06-15 Thread Christopher Kings-Lynne

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

2005-06-15 Thread Christopher Kings-Lynne

* 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

2005-06-11 Thread Christopher Kings-Lynne

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 ...

2005-06-04 Thread Christopher Kings-Lynne
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)

2005-06-01 Thread Christopher Kings-Lynne
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()

2005-05-10 Thread Christopher Kings-Lynne
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

2005-05-07 Thread Christopher Kings-Lynne
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

2005-05-07 Thread Christopher Kings-Lynne
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

2005-04-05 Thread Christopher Kings-Lynne
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

2005-03-28 Thread Christopher Kings-Lynne
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

2005-03-24 Thread Christopher Kings-Lynne
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

2005-03-24 Thread Christopher Kings-Lynne
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

2005-03-24 Thread Christopher Kings-Lynne
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

2005-03-24 Thread Christopher Kings-Lynne
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

2005-03-24 Thread Christopher Kings-Lynne
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.

2005-02-24 Thread Christopher Kings-Lynne
  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

2005-02-22 Thread Christopher Kings-Lynne
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

2005-02-10 Thread Christopher Kings-Lynne
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

2005-01-28 Thread Christopher Kings-Lynne
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

2004-12-25 Thread Christopher Kings-Lynne
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

2004-12-02 Thread Christopher Kings-Lynne
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

2004-12-02 Thread Christopher Kings-Lynne
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

2004-11-25 Thread Christopher Kings-Lynne
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

2004-11-23 Thread Christopher Kings-Lynne
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

2004-09-05 Thread Christopher Kings-Lynne
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.

2004-08-17 Thread Christopher Kings-Lynne
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.

2004-08-17 Thread Christopher Kings-Lynne
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.

2004-08-17 Thread Christopher Kings-Lynne
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...

2004-08-07 Thread Christopher Kings-Lynne
$ 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

2004-08-06 Thread Christopher Kings-Lynne
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

2004-08-04 Thread Christopher Kings-Lynne
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

2004-08-02 Thread Christopher Kings-Lynne
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

2004-08-02 Thread Christopher Kings-Lynne
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

2004-08-01 Thread Christopher Kings-Lynne
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

2004-08-01 Thread Christopher Kings-Lynne
* 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

2004-08-01 Thread Christopher Kings-Lynne
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

2004-07-29 Thread Christopher Kings-Lynne
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

2004-07-28 Thread Christopher Kings-Lynne
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

2004-07-27 Thread Christopher Kings-Lynne
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

2004-07-26 Thread Christopher Kings-Lynne
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

2004-07-26 Thread Christopher Kings-Lynne
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

2004-07-25 Thread Christopher Kings-Lynne
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

2004-07-25 Thread Christopher Kings-Lynne
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

2004-07-20 Thread Christopher Kings-Lynne
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

2004-07-20 Thread Christopher Kings-Lynne
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

2004-07-20 Thread Christopher Kings-Lynne
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

2004-07-19 Thread Christopher Kings-Lynne
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

2004-07-19 Thread Christopher Kings-Lynne
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

2004-07-19 Thread Christopher Kings-Lynne
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

2004-07-17 Thread Christopher Kings-Lynne
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

2004-07-17 Thread Christopher Kings-Lynne
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

2004-07-14 Thread Christopher Kings-Lynne
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

2004-07-14 Thread Christopher Kings-Lynne
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

2004-07-14 Thread Christopher Kings-Lynne
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

2004-07-14 Thread Christopher Kings-Lynne
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

2004-07-14 Thread Christopher Kings-Lynne
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

2004-07-11 Thread Christopher Kings-Lynne
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

2004-07-11 Thread Christopher Kings-Lynne
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

2004-07-11 Thread Christopher Kings-Lynne
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

2004-07-11 Thread Christopher Kings-Lynne
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

2004-07-11 Thread Christopher Kings-Lynne
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

2004-07-10 Thread Christopher Kings-Lynne
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

2004-07-08 Thread Christopher Kings-Lynne
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

2004-07-08 Thread Christopher Kings-Lynne
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

2004-07-07 Thread Christopher Kings-Lynne
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

2004-07-04 Thread Christopher Kings-Lynne
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

2004-06-28 Thread Christopher Kings-Lynne
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

2004-06-25 Thread Christopher Kings-Lynne
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

2004-06-24 Thread Christopher Kings-Lynne
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

2004-06-22 Thread Christopher Kings-Lynne
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

2004-06-21 Thread Christopher Kings-Lynne
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


  1   2   >