Core's current plan is to bundle 8.1beta3 tomorrow evening (Tuesday PM,
North American east coast time) for announcement Wednesday. Any last
minute bug fixes out there?
Not a bug fix, but this bug still hasn't been looked at:
http://archives.postgresql.org/pgsql-hackers/2005-04/msg00499.php
Well, _bt_compare is used for every btree index in the system,
including all the system indexes. A fresh initdb already has several
dozen indexes already so your code has to deal with that.
Remember, _bt_compare compares strings, integers, floats, dates, etc
and your code needs to work for all
Argh! That's some sed coolness :)
Chris
Martijn van Oosterhout wrote:
On Fri, Oct 07, 2005 at 04:46:12PM +0800, Christopher Kings-Lynne wrote:
If you have huge plain text dumps, and just want to restore one table
it's usually painful. Attached is a small shell script that can take a
plain
If you have huge plain text dumps, and just want to restore one table
it's usually painful. Attached is a small shell script that can take a
plain text dump and extract a single table's COPY data commands from it.
If people think it's interesting and should be developed, I can pop it
on
Didn't Alvaro write some script that we were going to use to help people
apply SQL changes against their databases?
Chris
Tom Lane wrote:
Log Message:
---
Fix procedure for updating nextval() defaults so that it actually works.
Update release dates for pending back-branch releases.
CREATE SEQUENCE ai_id;
CREATE TABLE badusers (
id int DEFAULT nextval('ai_id') NOT NULL,
UserName varchar(30),
Date datetime DEFAULT '-00-00 00:00:00' NOT NULL,
Reason varchar(200),
Admin varchar(30) DEFAULT '-',
PRIMARY KEY (id),
KEY UserName (UserName),
KEY Date (Date)
);
Hi,
Is there any way of checking to see if a particular shared library is
installed?
eg. select is_shared_library('$libdir/tsearch2');
If not, can we please have one :D
This will greatly help in GUI apps like phpPgAdmin...
Chris
---(end of
Is there any way of checking to see if a particular shared library is
installed?
eg. select is_shared_library('$libdir/tsearch2');
How well do you know the library you are looking for? You could just try
creating a function from it and seeing if it fails.
I know it pretty well, but it
count(*) from pg_opclass where opcname = 'tsvector_ops';
or, more general,
# select count(*) from pg_proc where probin = '$libdir/tsearch2';
But not all modules adds new function...
Christopher Kings-Lynne wrote:
Hi,
Is there any way of checking to see if a particular shared library
heuristics that initdb could apply. We'd have to let all of these
degrade nicely, so that even if the user select the machine hog setting,
if we find we can only do something like the tiny setting that's what
s/he would get. Also, we might need to have some tolerably portable way
of finding
Oh, I remembered another of my personal feature requests for 8.2 :D
* Fix planning and execution of set operations so that they're not
tragically slow. eg. rewriting into outer joins, etc.
Chris
---(end of broadcast)---
TIP 1: if
I looked at EnterpriseDB a few months ago. The installation errored.
It left stuff in /var/opt, which I consider non-standard for a Red Hat
machine. The whole product just didn't feel clean to me. I admit
that's a pretty limited and subjective evaluation, especially for a beta
product, but I
I really really do not like proposals to introduce still another kind
of VACUUM. We have too many already; any casual glance through the
archives will show that most PG users don't have a grip on when to use
VACUUM FULL vs VACUUM. Throwing in some more types will make that
problem exponentially
* optional interface which sends a row typeoid along with each row in a result
set
Oh, and 'select rowid, * from table' which returns special rowid column
that just incrementally numbers each row.
Chris
---(end of broadcast)---
TIP 6:
PQstatus perhaps?
http://www.postgresql.org/docs/8.0/interactive/libpq-status.html
Chris
Sivakumar K wrote:
Do we have an API like mysql_ping to check whether the server is up and
running after the connection has been established?
I checked the PostgreSQL docs but of no use.
Is
We have gone a long way now, even though it was only a year ago. My
question for everyone on this list is: What are the few remaining big
features that you see missing for PostgreSQL?
Or, slightly different, what are people's most wanted features?
Oh, and MERGE :D
Chris
We have gone a long way now, even though it was only a year ago. My
question for everyone on this list is: What are the few remaining big
features that you see missing for PostgreSQL?
Or, slightly different, what are people's most wanted features?
* Recursive unions (ie. WITH recursive)
*
* Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
That one is easy and handy.
Chris
---(end of broadcast)---
TIP 6: explain analyze is your friend
ISTR this question coming up before, but I couldn't find an answer. Is
there a reason we don't build versions of pg_dump and pg_dumpall that
are statically linked against libpq so they can be run uninstalled as
part of a migration process? I should have thought that this would be
extremely
As with an automatic weapon, Perl absolutely *requires* discipline to
use properly. Unlike an automatic weapon, Perl is perfectly OK to use
day-to-day in civilian life :)
What on earth would be the proper use of an automatic weapon?
You obviously don't live in the US.
Yeah, hunting...
The core committee has agreed that we need to do a set of releases
in the back branches soon --- certainly 8.0 has accumulated a critical
mass of changes since 8.0.3, and probably there's enough to justify
updates of the 7.* branches too. We hope to get these out sometime
next week, after the
Yeah, I suppressed that alternative a few weeks ago, thinking that it
was not sensible since we don't really support having indexes owned
by anyone except the owner of the parent table. Not sure what to do
about the fact that pg_dump has been emitting it though. Maybe reduce
the error to a
My recollection is that that change was way too invasive to be
reasonable for a back-port. The solutions used for circular reference
situations (various ALTER commands) probably don't exist very far back
anyway.
Nah, all you need to do is take the 8.0 pg_dump, hard-code that
This is for RDF queries, not for SQL queries. For SQL, the SQL/XML standard
gives you a standard XML format for table representation. I have some code
for that if anyone is interested. I will put that up on pgFoundry one of
these days.
I'm interested in the SQL format so that I can implement
Looks like there's a standard XML way of returning query results:
http://www.w3.org/TR/2005/WD-rdf-sparql-XMLres-20050801/
Chris
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
It's been running for about an hour now, and it is up to 3.3G.
pg_dump tiger | gzip tiger.pgz
| bzip2 tiger.sql.bz2 :)
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I should point out that the bug I reported about dependencies and
changing the type of a serial column still exists.
Once you change a serial column to something else, you cannot ever
change the default IIRC...
Chris
Tom Lane wrote:
Marc G. Fournier [EMAIL PROTECTED] writes:
On Tue, 2
They usually claim to be the world's most POPULAR open source database...
Chris
ohp@pyrenet.fr wrote:
Who copied?
I've been to mysql site 2 mn ago (did'nt occur since at least 6 months)
title says : Mysql: The world most advanced opensource database.
Isn't it the title for postgresql?
It
Hmmm...could we allow truncate in cases where all the FK's on a table
refer only to that table itself?
Chris
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Does it know that the input to the sort routine is already sorted and
hence is a no-op?
Yes
No, but in most cases this will use an index and hence will assume that
the index is responsible for ordering.
regards, tom lane
---(end of
Hi guys,
Would it be useful to have a pg_get_prepared(name) function that returns
true or false depending on whether or not there is a prepared query of
that name?
Perhaps we could have a way of checking the parameter types of it as well?
(Also no-one replied to my PQescapeIdentifier
Volkan YAZICI wrote:
Hi,
On 7/15/05, Christopher Kings-Lynne [EMAIL PROTECTED] wrote:
Would it be useful to have a pg_get_prepared(name) function that returns
true or false depending on whether or not there is a prepared query of
that name?
(You're mentioning about PHP PostgreSQL API
This has been covered before, but to reiterate: why would you need this?
Any application worth its salt should be tracking which statements it
has already prepared (after all, they cannot span connections). Seems
a waste of resources to make a separate call to the database for
information you
Hannu Krosing wrote:
On K, 2005-07-13 at 16:08 +0800, Christopher Kings-Lynne wrote:
Hi,
Does PostgreSQL do the following optimisation:
SELECT * FROM diary WHERE date = '2005-05-01' ORDER BY date;
or in fact even better (for my situation)
SELECT * FROM diary WHERE date BETWEEN '2005-05
I assume that this is program generated SQL, as I hope a human would know
better than to write this. In which case, isn't the answer to improve the
generator rather than expect postgres to make up for its defficiencies?
Well, the issue in my case is we have user food diaries. Usually,
Does it know that the input to the sort routine is already sorted and
hence is a no-op?
Yes
No, but in most cases this will use an index and hence will assume that
the index is responsible for ordering.
OK, so what's going on here?
usa= explain select * from users_myfoods_map where
Seems worthwhile to me --- any objections? Any better ideas about a
name?
pg_session_temp_namespace()
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining
Well, date evidently isn't the high-order key of this index. But why
exactly are you worried about a sort of 2 rows?
Aha that's nailed it:
usa= explain select * from users_myfoods_map where user_id=1 and date
between '2003-11-03' and '2003-11-03' order by user_id, date;
Hi,
Does PostgreSQL do the following optimisation:
SELECT * FROM diary WHERE date = '2005-05-01' ORDER BY date;
or in fact even better (for my situation)
SELECT * FROM diary WHERE date BETWEEN '2005-05-01' AND '2005-05-01'
ORDER BY date;
Does it know that the input to the sort routine is
Looking further ... we already do this implicitly for prodesc in the
call handler - we would just need to do the same thing for per-call
structures and divorce them from prodesc, which can be repeated on the
implicit stack.
I'll work on that - changes should be quite small.
Sounds like
Does truncate not being MVCC-safe cause problems in your situation? It
certainly doesn't in mine and I expect the same is true for alot of
others in the same situation.
Well, it is done inside a transaction, plus has concurrent use...
Chris
---(end of
The current permissions checks for truncate seem to be excessive. It
requires that you're the owner of the relation instead of requiring
that you have delete permissions on the relation. It was pointed out
that truncate doesn't call triggers but it seems like that would be
something
There are other reasons for restricting it:
* truncate takes a much stronger lock than a plain delete does.
* truncate is not MVCC-safe.
I don't really agree with the viewpoint that truncate is just a quick
DELETE, and so I do not agree that DELETE permissions should be enough
to let you do a
The pg_autovacuum on FreeBSD and pg 803 is not working. Just do nothing, no
log, nothing in screen, no daemonize.
It was ok on pg746.
Could some one help me ?
They both work fine for me on my test box...
Are you aware that they change the port? You need to put
postgresql=YES in your
I can't seem to dump old db's:
-bash-2.05b$ pg_dumpall -s -h database-dev dump.sql
Password:
pg_dumpall: could not connect to database postgres: FATAL: database
postgres does not exist
Seems that it is expecting the new 'postgres' database to exist on old
installations?
Chris
Seems that it is expecting the new 'postgres' database to exist on old
installations?
Ooops :-( Seems like maybe we want it to try postgres and then fall
back to trying template1?
Actually, also ONLY assume postgres is a special database if the backend
is 8.1 or higher. We don't want to
Seems that it is expecting the new 'postgres' database to exist on old
installations?
Ooops :-( Seems like maybe we want it to try postgres and then fall
back to trying template1?
No idea :) I haven't followed the new postgres database changes
particularly well...
Chris
You could work around this by explicitly specifying the parameter
type as text or varchar or whatever the domain's base type is.
I wonder though if we oughtn't change the backend so that the inferred
type of a parameter symbol is never a domain, but the domain's base
type. That would force the
How about a PQescapeIdentifier function in libpq? :)
Chris
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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.
Bruce - this is done:
o Add dumping and restoring of LOB comments
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Why was that approved to -announce? What does it have to do with
PostgreSQL announcements?
Marc G. Fournier wrote:
For those that remember far enough back, you will have *cough* fond
memories of Al Dev ... he seems to have resurfaced, and I figured that
this enlightened posting might be a
gmake distclean
./configure ...
gmake install
...
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing
-g -I../../../../src/include -c -o timestamp.o timestamp.c -MMD
timestamp.c: In function `GetCurrentTimestamp':
timestamp.c:955: storage size of `tp' isn't known
Is it possible for a pl/pgsql trigger function to look at the sql
command that caused it to be triggered? If not, is this an idea?
Chris
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
I run initdb and get:
LOG: database system was shut down at 2005-06-29 11:57:10 WST
LOG: checkpoint record is at 0/353E68
LOG: redo record is at 0/353E68; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 548; next OID: 10792
LOG: next MultiXactId: 1; next MultiXactOffset: 0
Use case is making a trigger than can log queries on tables...
Christopher Kings-Lynne wrote:
Is it possible for a pl/pgsql trigger function to look at the sql
command that caused it to be triggered? If not, is this an idea?
Chris
---(end of broadcast
I think the whole GiST limitations page can be removed now...
http://developer.postgresql.org/docs/postgres/limitations.html
Chris
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an
When altering a sequence created by a SERIAL column type (i do this by
examining pg_depend to avoid moving any other sequences that are
'foreign'), i need to recreate the default expression for the SERIAL
column (stored in pg_attrdef.adbin). Is there an API to do that, or do i
have to recreate
Also, isn't rtree still substantially faster than gist?
Not according to contrib/rtree_gist/bench/, though I admit I have not
bothered to reproduce the experiment.
Will you just remove rtree and make rtree indexes use rtree_gist instead?
Chris
---(end of
Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.
Problem is, how the hell do I know it's there before I connect?
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please
This is called range partitioning. We're working on it. You're welcome to
join the Bizgres project where most of the discussion on this feature takes
place:
www.bizgres.org
http://pgfoundry.org/mail/?group_id=1000107
I still think the fact that that discussion is taking place on a
I still think the fact that that discussion is taking place on a
completely non-hackers mailing list is the lamest thing ever...
What, like phpPgAdmin? ;-)
Erm. Last time I checked phpPgAdmin was a userland application, using
PHP and libpq. Bizgres is proposing modifying PostgreSQL
it hard to discuss and follow the project.
Keeping it among yourselves is just a recipe for a bad case of group
think...
Chris
Christopher Kings-Lynne wrote:
I still think the fact that that discussion is taking place on a
completely non-hackers mailing list is the lamest thing ever...
What
I think this is a very good idea. I've come up against this need once or
twice before.. And the fact that stuff in template1 gets propagated out
to all newly created databases can be a major pain when this happens.
A shared database for this stuff would be great - then each tool could
just
In phpPgAdmin the default db to connect to can be specified per-server
in the config file. It defaults to template1. It actually is not
relevant at all which db it is, so long as they can connect to it.
I wonder how many users actually change that value for php/pgadmin or
simply leave it
Probably, though the create db issue is a good reason not to use template1.
Create db issue?
So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?
But then dbas will block off access to that db, or drop it and we're
back to square one...
Chris
um, can we have these as separate GUCs and not lumped together as a
string? i.e.:
autovacuum_frequency = 60 #seconds, 0 = disable
autovacuum_vacuum_threshold = 200
autovacuum_vacuum_multiple = 0.5
autovacuum_analyze_threshold = 100
autovacuum_analyze_multiple = 0.4
AV should be disabled by
I've personally seen at least a dozen user requests for autovacuum in the
backend, and had this conversation about 1,100 times:
NB: After a week, my database got really slow.
Me: How often are you running VACUUM ANALYZE?
NB: Running what?
Me too. Just hang out in #postgresql for a while :)
Well, it's not so much that I care about queries with 1000+ relations,
as that this is a good way to stress-test the code and find out where
the performance issues are. There are many thousand lines of code that
can never be performance-sensitive, but to expose the ones that are
it helps to push
With this Bruce, is there any reason this was accepted now, and not
several years ago when I first submitted it? :D
Also, you can update our SQL99 compatibility list to indicate that we
now have this feature :)
Chris
Bruce Momjian wrote:
Log Message:
---
Add BETWEEN SYMMETRIC.
Check out EnterprisDB: www.enterprisedb.com
Chris
Edward Peschko wrote:
hey all,
I'm trying to convince some people here to adopt either mysql or postgresql
as a relational database here.. However, we can't start from a clean slate;
we have a very mature oracle database that applications
It occurs to me that, because this restoration process is fundamentally
noninteractive, there is no longer any reason that we cannot support
backing up large objects in plain PSQL dumps. The dump script for
each LO would look something like
begin;
select
This avoids the risk of creating any serious backwards-compatibility
issues: if there's anyone out there who does need SnapshotNow reads,
they just have to be sure to open the LO in read-write mode to have
fully backward compatible operation.
Comments, objections?
If you feel like it, feel
They should all be moved. Remember nasties like indexes should be moved
as well as toast tables.
Oh, i thought toast tables should live in the pg_toast namespace?
Oh yes, you're probably right. Indexes should move though I think?
Chris
---(end of
'k, is the bug with pg_dump, or pg_restore? I'm guessing pg_dump, but
just want to make sure ...
Yeah it is an ordering problem with pg_dump...
The bug is in pg_dump and isn't fixed until 8.0.
Chris
---(end of broadcast)---
TIP 7: don't
Someone commented to me recently that they usually use psql's \x
expanded output mode, but find that it produces pretty illegible
results for psql slash commands such as \d. I can't really see a reason
you would _want_ expanded output mode for the result sets of psql
slash commands. Would
One issue that comes to my mind is what to do when dealing with tables
that have assigned triggers and sequences (serials). Do we want to move
them as well or leave them in the source namespace?
They should all be moved. Remember nasties like indexes should be moved
as well as toast tables.
BTW, I noticed that the permission denied messages throughout the
source don't quote the name of the identifier for which permission has
been denied. This violates the error code conventions: Use quotes
always to delimit file names, user-supplied identifiers, and other
variables that might
Try the PL/sh project on www.pgfoundry.org.
Chris
Gevik babakhani wrote:
Dear people,
Does anyone know how to execute an OS command from pgsql. I would like
to create a trigger that op on firing would run/execute an external
program.
Does such functionality exist or do I have to
round() and trunc() also have the virtue that they already have versions
for type numeric. If we keep the operators then we'll be right back
with the complaint that was lodged the other day about exponentiation,
namely unexpected precision loss for numeric inputs:
regression=# select
Now that I look, it doesn't look like these operators are documented
at all in the SGML docs, so it sure seems that removing them should be
pretty painless.
I'd agree with that
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the
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?
I'm interested if anyone is using tablespaces? Do we have any actual
reports of people actually using them, to advantage, in the field??
Maybe the next postgresql.org survey could be on tablespace usage?
Chris
---(end of broadcast)---
TIP 9:
There are some other arguments in favour of a LOAD command Alon?
We already have LOAD, so you'll have to choose something else :)
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
Is there any reason we can't have SELECT FOR UPDATE on union ALL queries?
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
have made some noises about that in the past, but nothing's been done
about it that I've heard of.
This whole GiST concurrency think really needs to be looked at :( There
is so much cool stuff that can be done with it,
unfortunately, we still couldn't find 2-3 months for dedicated work on
concurrencyrecovery for GiST. I'm trying to find support here in Russia
for our work.
How much money (US Dollars) would you need?
Chris
---(end of broadcast)---
TIP 6: Have
How much money (US Dollars) would you need?
Command Prompt could jump on that as well. We could help sponsor a bit.
Maybe we could start a funding project for it?
USD convert to lots of roubles I assume, so it'd be good like that.
Perhaps someone (not me - too busy) on the PostgreSQL
I read the PHP addslashes() manual page:
http://us3.php.net/addslashes
First, I see what people mean about PHP having most of the complex
content in comments, rather than in the actual manual text, and this
tendency is certainly something we want to avoid --- you end up having
to digest
Well, it'll still be necessary to keep 2.53 around, unless you want to
move to 2.59 for future releases of the back branches too ... which
might be OK, I'm not sure.
I thought it was to help the public not have to keep so many versions
around :)
Chris
---(end of
I think we can tell people in 8.1 that they should modify their
applications to only use '', and that \' might be a security problem in
the future. If we get to that then using ESC or not only affects input
of values and literal backslashes being entered, and my guess is that
90% of the
Plan B is for WAL replay to always be willing to extend the file to
whatever record number is mentioned in the log, even though this
may require inventing the contents of empty pages; we trust that their
contents won't matter because they'll be truncated again later in the
replay sequence. This
You can probably just create an INSTEAD rule on the view...
Chris
--= Tono =-- wrote:
Is there any plans to create an INSTEAD OF trigger on
VIEWS? I have view which consists of a master and
detail table. When a row is inserted into the view,
the view needs to figure out if the master record
I have a similar problem and already considered using RULEs, but I
encountered the problem, that I did not find any way to execute
procedures from RULEs without using SELECT, which creates always a
result set being passed to the application invoking the INSERT, UPDATE
or DELETE, even if the
What I'm wondering is whether this is really worth doing or not.
There are currently just two parts of the lexer rules that are affected
--- the {real} rule illustrated above, and the rules that allow quoted
strings to be split across lines as the SQL spec requires. But the
patches are still
I haven't had much experience (okay, I've yet to use it) with tsearch2,
but according to
http://www.postgresql.org/docs/8.0/interactive/limitations.html, GiST
does have concurrency issues The current implementation of GiST within
PostgreSQL has some major limitations: GiST access is not
But I do think it's worth it, even so ... not all client interfaces
support prepared statements (notoriously PHP, although I understand KL
has sent patches to fix that) and not all inserts are suitable for COPY.
There is now pg_prepare/pg_execute/pg_query_params in PHP, however you
could
Based on the comments so far in this thread, I'll go ahead and commit
the patch, with some comments attached of course --- in particular a big
head comment to run flex with -b and see that lex.backup says something
to this effect.
Add it to the release check-list.
Chris
I'm going to second Neil here. This feature becomes useful *only* when there
is a certified or de-facto universal standard XML representation for database
data. Then I could see a case for it. But there isn't.
We've done it in phpPgAdmin (we made up our own standard), and a couple
of
It would be useful to outline positions that are actually available for people
to take.
It's easy to give a general list. I've asked and seen may like it. For me,
what does
helping with advocacy mean? What should be performance tested (I assume new
code,
like the bitmap scan). But at the
201 - 300 of 2006 matches
Mail list logo
