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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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?
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.
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
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
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
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
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
* 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
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
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?
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
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
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
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
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
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
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
?
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;
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
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
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
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
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
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
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
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
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
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
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:
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
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:
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
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 :)
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
$ 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
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
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
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
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
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
* 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,
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
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:
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;
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
.
---
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
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
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
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
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
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
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
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;
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
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
1 - 100 of 163 matches
Mail list logo