Suppose I have a database with a table in it. I try to dump that
database. The user I dump as is not the owner of that table nor does it
have the SELECT permission on that table.
Why can't I do a schema-only dump of that table? I find that I need
SELECT permission on the table, even though
do we maintain anything anywhere for this? mainly, some way of
determining # of 'sorts to disk' vs 'sort in memory', to determine
whether or not sort_mem is set to a good value?
I don't think there is currently, but wondering how hard it would be to
get something like this added ... ?
While
Isn't that what pg_stat_database reports with its xact_commit and
xact_rollback values?
Ah yes. Doh :)
Chris
---(end of broadcast)---
TIP 6: explain analyze is your friend
A general ability to be able to dump views as if they were tables would
be more broadly applicable methinks?
Merlin Moncure wrote:
I have a situation where I need to hack pg_dump not to dump columns with
a particular name. If this is of interest to the community I can spend
a little extra effo
Yes for the love of god do it :D
David Fetter wrote:
Folks,
Before I dive into this, is there some reason why the pg_catalog.*
tables/views should not have comments that match the descriptions in
the docs? I can see where this could cause some maintenance issues,
and those probably need to be
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
C
Whether this is a good idea is another question entirely. Lots of
people will tell you it's a horrid idea for PG functions to cause
outside-the-database side effects. The reason is that if the
transaction that called the function aborts later, there is no way
to roll back what was done outside t
Whether this is a good idea is another question entirely. Lots of
people will tell you it's a horrid idea for PG functions to cause
outside-the-database side effects. The reason is that if the
transaction that called the function aborts later, there is no way
to roll back what was done outside t
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 of
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 tak
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 pgfou
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.
Mo
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)
);
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 out
elect 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 par
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 seems
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 broadcast)
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 posting/readin
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 wa
Why don't you just use EnterpriseDB?
Chris
That would defeat my goal of not rewriting all my Oracle code.
If I were fool enough to plan an attack on the main executor's exception
handling to try and disarm it of its subtransaction semantics, where
would I start? Where would I end? What would
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: explain
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)
* C
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
-
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 there
* 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 easy
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"...
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
--use-set-session-
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 warnin
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 fir
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/fa
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 see
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 broadcast
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 shou
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 Pos
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 suggest
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;
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 col
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 user
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,
99.%
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 BETWEE
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 al
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 recur
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 broadcast)---
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
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 mes
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
-
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 /etc/
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 T
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 ea
How about a PQescapeIdentifier function in libpq? :)
Chris
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 pr
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.
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
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
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
timestamp
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 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
LOG
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]
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
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 inde
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 broadcast)--
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 send
ee why you are
making 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 ma
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 itsel
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
comp
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
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 def
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 create
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
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 whi
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 def
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.
Pav
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 lo_create(original-OID-of
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 poi
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 free
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 a
'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 forge
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 broadcast)
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 c
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?
-
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 postmas
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 12345678901
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 writ
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:
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
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
subscr
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 Founda
unfortunately, we still couldn't find 2-3 months for dedicated work on
concurrency&recovery 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
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, and
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 broa
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
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 backslash
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 s
201 - 300 of 2140 matches
Mail list logo
