Btw -unfound?? I think the English there might need to be improved :)
Chris
On 1/11/07, Richard Huxton dev@archonet.com wrote:
Warren Guy wrote:
Hi everyone
Was running a VACUUM on a database on a partition which was running out
of disk space. During VACUUM the server process died and
* Shouldn't the xml type support binary I/O? Right now it is the only
standard datatype that doesn't. I have no idea whether there is an
appropriate representation besides text, but if not we could define the
binary representation to be the same as text.
There is an effort to develop a binary
.
---
Christopher Kings-Lynne wrote:
Hi guys,
I've attached as much as I've done so far on the GIN docs. It's not a
lot, but I'm afraid with the feature freeze in effect, I'm just not
going to have the ability to get them done by the RC date
Are my mails getting through? Did anyone see my mail about the GIN docs?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hi guys,
I've attached as much as I've done so far on the GIN docs. It's not a
lot, but I'm afraid with the feature freeze in effect, I'm just not
going to have the ability to get them done by the RC date.
The main problem was I just strugged to fully understand it all :(
Anyway, hopefully
see. Collecting the statistics thereafter isn't that hard, but there
needs to be a way to not collect an exponential volume of statistics on
all column combinations.
You could collect them on all FK relationships - is that enough?
Chris
---(end of
We have three candidates already -- debbugs, RT and Gnats. The first
has the advantage that was written by hackers, for hackers, so it
doesn't have any of the insane for end users stuff which annoys so
many people around here ;-) (On the other hand it does have some web
stuff for generating
Trac does support PostgreSQL...
The thing I don't understand at this point is what exactly is the
nature of the integration with the SCM.
I don't see it being likely that there will be a deep integration of
the PostgreSQL SCM (whatever the SCM platform) with Trac; that's way
too much change to
What version of Bison is currently required to compile HEAD? 1.75
doesn't seem to work...
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
It would be the most practical way for a DBA to monitor an application. But
it's not going to be convenient for clients like pgadmin or psql. Even a web
server may want to, for example, stream ajax code updating a progress bar
until it has results and then stream the ajax to display the results.
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
I've already added adddepends to pgFoundry (as Old PG Upgrade), since
people spoke up for it. I will assign one of them as admin of the
project (not sure who yet).
How is addepends in any way old pg upgrade??
---(end of broadcast)---
TIP 5:
No, I don't believe you can do this with CVS at all. We'd need something
like SVN/WebDAV to be able to grant write access just to specific parts
of the tree to different people.
You just use an on-commit script like cvsacl.
---(end of
1) Rod Taylor is not interested in maintaining it anymore;
2) It currently throws errors on 8.2 (and probably earlier);
3) With KL's improvements to pg_dump for 8.0, about half of its
functionality is no longer necessary.
So, speak up if someone thinks there's some reason to save adddepends
Think is, 8.1 does a much better job of upgrading 7.2 datatabases than
7.3 or 7.4 did anyway. I just tested using a database created in 7.1
and upgraded to 7.2 which has a baroque and unnecessarily complex schema
(legacy production applicaiton) which breaks on 7.4 without adddepends.
I was
If there is interest - I'm sure Nathan and I would be willing to put
it on pgfoundry, and at some point give it up for inclusion into
PostgreSQL.
One requirement would be that it runs on Windows. Is that something you
have tested?
In case it influences anyone, MySQL 5 already has built-in
It looks like we still don't have any docs for GIN in the tree so I
don't know if those timings are expected or not ...
Ummm my bad. Sorry...
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
The example is a very active web site, the flow is this:
query for session information
process HTTP request
update session information
This happens for EVERY http request. Chances are that you won't have
concurrent requests for the same row, but you may have well over 100 HTTP
server
Kings-Lynne
Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444
[EMAIL PROTECTED]
www.calorieking.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
http://lwn.net/Articles/178199/
Check out the article on sync_file_range():
long sync_file_range(int fd, loff_t offset, loff_t nbytes, int flags);
This call will synchronize a file's data to disk, starting at the given
offset and proceeding for nbytes bytes (or to the end of the file if
I forget whether the developer managed to get it working without doing any
table rewriting. In theory the table just needs to know that records that are
missing that column in the null bitmap should behave as if they have the
default value. But I seem to recall some headaches with that approach.
We got a lot requests about including stemmers and ispell dictionaries
for all accessible languages into tsearch2. I understand that tsearch2
will be closer to end user. But sources of snowball stemmers is about
800kb, each ispell dictionaries will takes about 0.5-2M. All sizes are
sized with
Perhaps we can put together the source code for all languages modules
available and provide scripts to fetch ispell data or to generate the
snowball stemmers. A debian package maintainer would have to fetch all
the data to generate all language packages. Someone else might just want
to
I'd be willing to help with such a project. I have experience with
tsearch2 as well as with gentoo and debian packaging. I can't help
with rpm, though.
I could help with a FreeBSD package I suppose.
Although I should probably finish up those damn GIN docs first :)
Do any of you guys use linkedin.com and want to add me to your contacts
network?
Ironically, I don't use LinkedIn, even though they use PostgreSQL (not
exclusively, though).
Really? How do you know that? Are any of their people part of the
community?
Chris
Here's a question. I wish to add a function to libpq to escape
PostgreSQL identifiers. Will this function be subject to the same
security/encoding issues as PQescapeString?
Chris
--
Christopher Kings-Lynne
Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444
[EMAIL
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Here's a question. I wish to add a function to libpq to escape
PostgreSQL identifiers. Will this function be subject to the same
security/encoding issues as PQescapeString?
Is this of any general-purpose use? How many apps are really
Hi hackers,
I sent this to the hackers list, as this is the people I wish to contact.
Do any of you guys use linkedin.com and want to add me to your contacts
network?
I'm keen to get into a network of PostgreSQL-related people - for future
jobs/contracts purposes.
My linkedin.com email is
Thank you for your help. I found that an implicit index is created for
the primary key in the current version. However, it is not done in 7.x
version.
It absolutely is created in all 7.x versions of PostgreSQL.
---(end of broadcast)---
TIP 1:
--
Christopher Kings-Lynne
Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444
[EMAIL PROTECTED]
www.calorieking.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
And MySQL is much closer to being a competitor now than they were in
4.1. And feature-wise they'll probably equal PostgreSQL in the next
release. Will the features be anywhere near as robust or well thought
out? No. But in a heck of a lot of companies that doesn't matter.
Don't forget that they
We also need better support for non C locales in tsearch. As I was porting
mysql's sakila sample database I was reminded just how painful it is when you
initdb in a non-supported locale (which is probably the default on the
majority of distros out there)
In 8.2 tsearch2 supports utf8...
If you want to get users to swtich to your software from your competitors, you
have to eliminate barriers, and a big one for any database is getting locked
into a specific one. People aren't going to take the time to try switching
to postgresql if they can't easily make it back to thier former
Having tinkered a little with PQA, yes, actually. The issue is that the
message text can easily be multi-line and contain a vast variety of
special characters. The issue is figuring out where the prefix, the tag
and the message begin and end. And our text log format makes that a PITA.
Try
We could, but it'd probably break about as many apps as it fixed.
I wonder whether php shouldn't be complaining about this, instead
--- doesn't php have its own ideas about controlling where the
transaction commit points are?
All PHP does is when the connection is returned to the pool, if it is
Yes. There is a patch pending on -patches which fix almost all of these
in HEAD. (There are a few tiny things related to perl and NLS that
aren't included in it ATM. And I'm just assuming you're seeing the same
problems as I was but I didn't base my work off vcproject). I'm also
working on a
You mean they have a tool that parses GNU Makefiles and generate VC
project files? Sure, that might be interesting. I've seen I think two
others, and tried, but they fell over badly because the pg build system
was too complicated. But I beleive I'm still allowed to loko at GPL
stuff and get ideas
It's slightly annoying to have to read the flat file twice, but
for reasonable numbers of databases per installation I don't think
this will pose any material performance penalty. The file will
certainly still be sitting in kernel disk cache.
Dropping a db isn't exactly a common occurrence
Sure, but there's no reason that would couldn't allow that with a true
black-box SERIAL, either. In fact, you can do it today if you want,
just by creating a wrapper around nextval(pg_get_serial_sequence()).
Or just use lastval()
Chris
---(end of
For the sake of saying again, I already have a recursive-descent
parser for PostgreSQL written in a PCCTS grammar. It's something I
started writing years ago, but I'd be willing to consider open
sourcing it if the PostgreSQL community will really entertain the
thought of switching.
Those messages were at LOG level because otherwise it's difficult to be
sure from the log that autovac is running at all.
OK, so what do we want to do? Clearly outputing something everytime
pg_autovacuum touches a database isn't ideal. By default, the server
logs should show significant
I suggest that maybe the cleanest solution is to not use log level at
all for this, but to invent a separate autovacuum_verbosity setting
that controls how many messages autovac tries to log, using the above
scale. Anything it does try to log can just come out at LOG message
setting.
+1
What changed between Try 1 and Try 2?
Teodor Sigaev wrote:
We (me and Oleg) are glad to present GIN to PostgreSQL. If community
will agree, we will commit it to HEAD branch.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Oh I can't read - ignore me :)
Teodor Sigaev wrote:
Changes from previous patch:
* add support for tsearch2
* add 'fuzzy' limit
* fixes
---(end of broadcast)---
TIP 4: Have you searched our list archives?
I havn't been able to find any more serious issues in the Coverity
report, now that they've fixed the ereport() issue. A number of the
issues it complains about are things we already Assert() for. For the
rest, as long as the following assumptions are true we're done (well,
except for ECPG). I
I think Martin Oosterhout's nearby email on coverity bug reports might make a
good SoC project, but should it also be added to the TODO list?
I may as well put up phpPgAdmin for it. We have plenty of projects
available in phpPgAdmin...
Chris
---(end of
Hi,
The phpPgAdmin project has been pretty quiet for some time now. We have
decided to try to build up our developer base again by recruiting some
new, interested, PHP developers.
The core team is still around to offer guidance, suggestions, releases,
etc. however no-one seems to have much
Oooh. Based on emails I've received I should point out that phpPgAdmin
is a FREE, VOLUNTEER project! It's not a paid job offer!
Christopher Kings-Lynne wrote:
Hi,
The phpPgAdmin project has been pretty quiet for some time now. We have
decided to try to build up our developer base again
I need a rownum column, like Oracle. I have searched the mailing lists
and I don't see a satisfactory solution, so I was wondering write a UDF
to implement it, the requirements are:
+1
I would _love_ to see rownums in PostgreSQL :)
Chris
---(end of
2: Don't 'kill -9' the postmaster
--
Christopher Kings-Lynne
Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444
[EMAIL PROTECTED]
www.calorieking.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map
I'm glad to see work being done on domains. I'm definitely learning from
the discussion.
I wonder if we should implement 'GRANT USAGE ON DOMAINS' for spec
compliance sometime...
Chris
---(end of broadcast)---
TIP 3: Have you checked our
Martin's proposal at least looks sensible; he just hasn't quite made the
case that it's worth doing. If you're running a system that hardly ever
crashes, you might be willing to accept index rebuilds during crash
recovery, especially for indexes on relatively small, but frequently
updated,
To the GP, adding -lncurses (or rather the static equivalent) to your
link line should solve it. But if you include any other libraries like
ssl or kerberos be prepared to add a lot more.
With -lncurses or -lcurses I still can't get this to work. I add it to
the ${CC} line, right?
Chris
What is the virtue of this in any case? I can see considerable use for a
statically linked pg_dump, to help with upgrading, but not too much for
statically linked anything else, especially since we are now pretty
relocatable on most platforms at least.
Upgraded db server to 8.1, but don't
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
Yeah. AFAICS the transformation Chris suggested is valid. I'm really
dubious that it's worth expending planner cycles to look for it though.
LIKE is something that everybody and his brother uses, but who uses this
position()=0 locution?
One of our junior developers :) Which is why I noticed
The docs are correct so my initial point was correct. position('ch' in
user) = 0 is equivalent to user NOT LIKE '%ch%' and there's no way
you can index that.
Well = 1 then.
Chris
---(end of broadcast)---
TIP 1: if posting/reading through
Hm, good point. We could put 'em in pg_sequence, except that most of
the operations on pg_sequence rows will be nontransactional, and that
doesn't seem to square nicely with transactional updates on ACLs.
Maybe we need two catalogs just to separate the transactional and
nontransactional data for
Hi guys,
I've been trying to build the cvs checkout of 8.1.3 on my freebsd 4.9
box with a STATIC psql utility. I keep getting failures trying to hook
in libreadline I think:
lreadline -lcrypt -lcompat -lm -lutil -o psql
/usr/lib/libreadline.a(terminal.o): In function `_rl_get_screen_size':
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
contrib/vacuumlo perhaps?
Michael Fuhr wrote:
On Sat, Mar 04, 2006 at 12:08:52PM +0530, Md.Abdul Aziz wrote:
I am a presently working on a module which enhances postgre to
store audio files,while storing the aduido file in the databese i used
liod,now the problem is i am able to unlink but
E - Era name (like, Japanese Imperial) (kind of pointless)
EE - Full era name
Some stuff here:
http://java.sun.com/javase/6/docs/guide/intl/calendar.doc.html
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
No-one has a comment on this?
Christopher Kings-Lynne wrote:
I just tried using ipcclean in 8.1.3. It doesn't work when I su to the
pgsql user. This part of the script:
if [ $USER = 'root' -o $LOGNAME = 'root' ]
Always fails because even tho $USER is set to 'pgsql' when su'ed,
$LOGNAME
if [ $USER = 'root' -o $LOGNAME = 'root' ]
Always fails because even tho $USER is set to 'pgsql' when su'ed,
$LOGNAME is still root.
This is on FreeBSD 4.9
It seems to work on Linux; apparently there are different behaviors of su. Do
you have a suggestion for resolving this?
Well all I
I wonder if there could be a potential problem with using this approach
-
checking on $USER == root.
Although it is a common practice, I think a superuser does not have to
be root.
Yes, like the 'toor' account in FreeBSD... (disabled by default though)
Chris
---(end
I could not find anything in the Frontend/Backend protocol docs about
character encoding in the StartupMessage. Assuming it is legal for a
database or user name to have unicode characters, how is this handled
when nothing yet has been said about the client encoding?
A similar badness is that
I just tried using ipcclean in 8.1.3. It doesn't work when I su to the
pgsql user. This part of the script:
if [ $USER = 'root' -o $LOGNAME = 'root' ]
Always fails because even tho $USER is set to 'pgsql' when su'ed,
$LOGNAME is still root.
This is on FreeBSD 4.9
Chris
I don't see any very nice solution at the moment. Once we get support
for per-column locales, it might be possible to declare that the shared
catalogs are always in UTF8 encoding and get the necessary
conversions to happen automatically.
At the very least, could we always convert dbnames and
In my opinion we should cater for such a situation, and two possible
solutions come to my mind for this:
I've done exactly this before, and had to use single user mode to
recover. Annoying.
1. Place a restriction that there should be more than one superuser
before you can issue a
No, there's no need for that. It means that the RI stuff would have to
take whatever steps we agree on to determine the exact comparison
operator to use, and then be sure to emit SQL that will select exactly
that operator --- this involves using the OPERATOR(foo.=) syntax to
remove
Not really, but you can check out the sample databases project:
http://pgfoundry.org/projects/dbsamples/
Chris
Ron wrote:
I assume we have such?
Ron
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Relating to this. If anyone can find govt or other free db's and
convert them into pgsql format, I will host them on the dbsamples page.
The dbsamples are _really_ popular!
Chris
Scott Marlowe wrote:
On Fri, 2006-02-17 at 10:51, Ron wrote:
I assume we have such?
Depends on what you
Ouch! That confirms my problem. I generated the random test case because
it was easier than including the dump of my tables, but you can
appreciate that tables 20 times the size are basically crippled when it
comes to creating an index on them.
I have to say that I restored a few gigabyte
http://www.flamingspork.com/blog/2006/02/16/enterprisedb-where-is-the-source/
Any comments on this? Is he referring to EnterpriseDB extensions that
they don't make public?
Chris
---(end of broadcast)---
TIP 1: if posting/reading through
This generalizes to any scale factor you care to use, eg fortnights...
so I don't see a pressing need to add microseconds.
Perhaps an argument for adding microseconds to interval declarations is
that you can extract them using extract()... Those two lists of allowed
scales should be the
[OT]
So Debian has a patch that is not in 8.1.2? I can't believe that they
are doing that -- personally I'm against to add any patch into binaries
that is not in the core.
[/OT]
And it's days like these that make me happy to be running Debian. My
thanks go to Martin for his excellent work.
This would apply to only a single relation, so would be just as
efficient a write to the database as to WAL. The proposed route is to
sync to the database, but not to WAL, thus halving the required I/O.
Yes, its designed for large data loads.
A question - would setting fsync=off while
* Allow WAL logging to be turned off for a table, but the table
might be dropped or truncated during crash recovery [walcontrol]
Allow tables to bypass WAL writes and just fsync() dirty pages on
commit. This should be implemented using ALTER TABLE,
I believe pgAdmin only supports PostgreSQL 7.3 and above.
Chris
Flavio Caiuby wrote:
Dear hackers
I have downloaded and instaled pgadim2 (and pgadmin3 corrected for my
Windows98 -second edition) .When I
try to conect my web server, where I have an AVL program
to nurse and inspect an
Hey guys,
When do you reckon 8.1.3 will be released? That has the massive speedup
on GiST index creation, right?
I'm planning on a major upgrade soon, but the greatest time in reload is
taken up by index creation time, so I'll hang out for 8.1.3.
Any ETA?
Chris
If you want it to be dumped by pg_dump (which is debatable IMHO) then
it MUST NOT be a syntax extension, it has to be driven by a GUC
variable, else we have compatibility problems with the dumps. We just
went through this with WITH/WITHOUT OIDS.
Compatibility problems? CREATE INDEX isn't an
It could read all the SET variables in at startup?
Peter Eisentraut wrote:
Some time ago, the tab completion code for the SET command was changed to read
the list of available settings from the pg_settings table. This means that
by the time you're done completing SET TRANSACTION ISOLATION,
I believe psql keeps the password in memory.
\c seems to be able to change databases without asking for the password
again.
What if that role has a maximum of one connection, etc.?
Chris
---(end of broadcast)---
TIP 5: don't forget to increase
I had this code in a script:
UPDATE food_foods SET included=true WHERE verification_status = 'I';
UPDATE food_foods SET included=false WHERE verification_status IS NULL;
I tried replacing it with:
UPDATE food_foods SET included=(verification_status = 'I');
However, that set included to true
Andrew Dunstan wrote:
Michael Fuhr wrote:
On Mon, Jan 30, 2006 at 12:20:25PM +0900, Michael Glaesemann wrote:
On Jan 30, 2006, at 12:23 , Andrew Dunstan wrote:
A nicer idea would be something like a utility could we ship that will
download, build and install module foo for you.
I did a dump of a 7.4.11 database using the 8.1.2 pg_dumpall. I got
this at the top of the dump:
...
...
CREATE ROLE support;
ALTER ROLE support WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
LOGIN PASSWORD 'md5';
...
...
CREATE ROLE support;
ALTER ROLE
What would be the cause of this error after upgrading from pgsql 7.4 to 8.1?
usatest=# SELECT lexize[1] FROM lexize('en_stem', 'bacon');
ERROR: cache lookup failed for function 861011
Does tsearch2 need to somehow be tweaked after the upgrade?
Chris
---(end of
How about an option to map groups whose names conflict with user names
using a prefix mechanism?
e.g. --map-conflicting-groups=gr_
Then in Christopher's example his support group would become the role
gr_support.
No bad, have to change some application code then as well...
Chris
Yes. Representation of the DNA is probably best. But - that's a lot of
data to use as a key in multiple tables. :-)
No then you have problems with identical twins :)
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Oracle does, but you pay in other ways. Instead of keeping dead tuples
in the main heap, they shuffle them off to an 'undo log'. This has some
downsides:
Rollbacks take *forever*, though this usually isn't much of an issue
unless you need to abort a really big transaction.
It's a good point
Default schema really has to be public to help the newbies out there.
All contribs should come with some sort of standard uninstall.sql script
though.
Chris
Mark Kirkwood wrote:
David Fetter wrote:
Folks,
I'm picturing something like this:
make install
Why? SERIAL implies NOT NULL (although PRIMARY KEY does as well, of
course).
Ah yes you're right. I mixed up with the fact that SERIAL no longer
implies UNIQUE...
Chris
---(end of broadcast)---
TIP 6: explain analyze is your friend
I hope you mean 'redundant with PRIMARY KEY in example'...
Works out the same way though.
Chris
Neil Conway wrote:
Log Message:
---
Minor doc tweak: NOT NULL is redundant with SERIAL in example.
Modified Files:
--
pgsql/doc/src/sgml/ref:
create_domain.sgml
I've gotten interested again in the issue of row comparisons, eg
(a, b, c) = (1, 2, 3)
We've discussed this before, the most comprehensive thread being
http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php
but nothing's gotten done. Unless someone's already working on
Now, since COLLATE support is still in progress, I'm not sure how much
any of this helps you. I'm up to modifying the scankeys but it's hard
when you jave to keep rgrepping the tree to work out what is called
from where...
src/tools/make_ctags is your friend...
Chris
Where are we on this? In general I agree with Tom, but I have no time to
do the work. Unless someone has an immediate implementation, I suggest
that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt,
which is the minimum needed to unbreak Windows builds, while this gets
sorted
So, can I specify the password to pg_connect() as
'md5127349123742342344234'?
Certainly not. We'd hardly be worrying about obscuring the original
password if the encrypted version were enough to get in with.
AndrewSN can't post at the moment, but asked me to post this for him:
Knowing the
I'm not sure whether we'd need any additional planner knobs to control
this. I think that the existing join_collapse_limit GUC variable should
continue to exist, but its effect on left/right joins will be the same as
for inner joins. If anyone wants to force join order for outer joins more
than
IIRC the whole point of this exercise was to avoid passing the password
to the server in the first place. Unless you are talking about a PHP
md5() password of course ...
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
1 - 100 of 2006 matches
Mail list logo
