Hi there,
I'm a bit surprised how bad could be plan for unused table.
I had to cancel second query :)
tp=# explain analyze select tp_rewrite_substitute(query,
'select p.name_tsquery, p.name_alias_tsquery from place p')
from to_tsquery('newyorkhotel') as query;
y;
On E, 2005-08-29 at 09:50 +0400, Oleg Bartunov wrote:
Hi there,
I'm a bit surprised how bad could be plan for unused table.
I had to cancel second query :)
...
tp=# explain analyze select tp_rewrite_substitute(query,
'select p.name_tsquery, p.name_alias_tsquery from place p')
from place,
On Mon, 29 Aug 2005, Hannu Krosing wrote:
The only difference is unused table 'place' in FROM-clause. I typed in by
mistake, but I think optimizer could figure out not to take into account
this table.
This is valid SQL and afaik exactly what you asked for in the query.
Postgresql can't
In article [EMAIL PROTECTED],
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
* 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.
Why? It's a
On P, 2005-08-28 at 22:23 +0200, Andreas Pflug wrote:
I'm currently testing pgAdmin support for slony, on pgsql CVS HEAD, and
encounter strange problems from time to time.
After dropping and recreating the slony schema, all changes committed
and all backends in IDLE state, I'm getting
On 29 Aug 2005 09:56:44 +0200, Harald Fuchs wrote:
Christopher Kings-Lynne writes:
Oh, and 'select rowid, * from table' which returns special rowid
column that just incrementally numbers each row.
I think you can pretty much do that already by defining your own
aggregate function. The obvious
Hannu Krosing wrote:
On P, 2005-08-28 at 22:23 +0200, Andreas Pflug wrote:
I'm currently testing pgAdmin support for slony, on pgsql CVS HEAD, and
encounter strange problems from time to time.
After dropping and recreating the slony schema, all changes committed
and all backends in IDLE
Server: 7.4.8 on Red Hat EL4. Client psql 8.0.3 on WinXP. Using a
test server.crt and server.key, as described in 8.0 docs 16.8, I can
activate SSL encryption (WinXP 8.0.3 psql reports SSL Connection
at connect), and as expected, the server log reports that root.crt
is not found. If I copy
Hello,
I just found quite a strange behaviour of 8.1beta on SunOS:
uname -a:
SunOS sun46 5.8 Generic_108528-14 sun4u sparc SUNW,Ultra-80
gcc -v:
Reading specs from
/systools/bin/../lib/gcc-lib/sparc-sun-solaris2.7/3.2.1/specs
Configured with: /disk-c/hiller/gcc-3.2.1/configure
To the last sentence in my last post Re: SSL client crt verification:
From docs I understand that I only have to set SSL = true in postgresql.conf
to switch it on (in addition to providing the crt and key files).
I should have added:
...and using hostssl in pg_hba.conf (although host also works
On Monday 29 August 2005 00:33, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
On a slightly related note, I've noticed that psql isn't backward
compatible.
We have never expected psql's \d commands to work against older server
versions, and two months after feature freeze isn't
On Mon, 29 Aug 2005, Christopher Kings-Lynne wrote:
Oh, and 'select rowid, * from table' which returns special rowid column
that just incrementally numbers each row.
In sql2003 there is a window function called ROW_NUMBER() that can be used
to get numbers like that (one also need to specify
I want to write a selectivity function for GIST
indexes.
The select condition is somefield
array[1,2,3],
But when Im inside selectivity function I get
args: T_Var, T_Const.
So function needs to get the actual array contents
from T_Const .
How to do it ?
P.S
T_Const is like
On Mon, Aug 29, 2005 at 08:12:37AM -0400, Robert Treat wrote:
On Monday 29 August 2005 00:33, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
On a slightly related note, I've noticed that psql isn't
backward compatible.
We have never expected psql's \d commands to work against
Sergey E. Koposov [EMAIL PROTECTED] wrote
selecting default max_connections ... 10
selecting default shared_buffers ... 50
DETAIL: Failed system call was shmget(key=1, size=1957888, 03600).
selecting default max_connections ... 10
selecting default shared_buffers ... 20
DETAIL: Failed
Knut P Lehre [EMAIL PROTECTED] writes:
Maybe my problem is that 7.4.8 doesn't support client authentication?
The code is there but it's pretty buggy, as I recall. I'd suggest
updating to 8.0 if you want to use SSL for authentication.
regards, tom lane
David Fetter wrote:
On Mon, Aug 29, 2005 at 08:12:37AM -0400, Robert Treat wrote:
On Monday 29 August 2005 00:33, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
On a slightly related note, I've noticed that psql isn't
backward compatible.
We have never
Sergey E. Koposov [EMAIL PROTECTED] writes:
So, are the shared memory requirements increased for 8.1 ?
Yes; mostly from 2PC support I think. Try reducing
max_prepared_transactions. (We might want to debate whether the default
setting should be smaller than 50 --- it looks to me like that's
Andrew Dunstan [EMAIL PROTECTED] writes:
If we're going to do backwards compatibility for psql then we need to do
it in a fairly comprehensive way, not bit by bit, because we can
reasonably say either we support backwards compatibility or we don't
support backwards compatibility, but we
Tom Lane wrote:
It would be good to set some parameters before starting: how far
back is reasonable to support? pg_dump goes back to 7.0 but that's now
mostly for historical reasons, ie, 7.0 was the immediately previous
release when we started making it do backwards-compatible dumps. I'm
On Mon, 29 Aug 2005, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
So, are the shared memory requirements increased for 8.1 ?
Yes; mostly from 2PC support I think. Try reducing
max_prepared_transactions. (We might want to debate whether the default
setting should be smaller
Yes, and a flag to ecpg. Added to TODO:
Um, it's not clear *when* you need to know this:
- application configure time?
- application compile time?
- application link time?
- application run time?
Of those possibilities, add a function responds to only one, and
On Sun, 21 Aug 2005 19:27:35 -0500
Jim C. Nasby [EMAIL PROTECTED] wrote:
On Mon, Aug 08, 2005 at 07:45:38PM -0400, Andrew Dunstan wrote:
So the short answer is possibly You build the tests and we'll run 'em.
Would some version of dbt2/3 work for this?
Yeah, trying... On the larger system
Sergey E. Koposov [EMAIL PROTECTED] writes:
Yes, the decreasing of max_prepared_transaction helped (after some
testing, I've found that the max_prepared_transactions=3
max_connections=10 shared_buffers=20 was well enough to fit 1mb of
shared memory)
20 buffers ... ugh. Obviously we are
Sergey E. Koposov wrote:
unfortunatly max_connections=10 is not enough to run
fully the make check :(, but I cannot raise more the max_connections
parameter
You can limit the number of connections that make check runs. We built
that facility in for cygwin, but it looks like you could
On Mon, 29 Aug 2005, Andrew Dunstan wrote:
unfortunatly max_connections=10 is not enough to run
fully the make check :(, but I cannot raise more the max_connections
parameter
You can limit the number of connections that make check runs. We built
that facility in for cygwin, but it
On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
Yes, the decreasing of max_prepared_transaction helped (after some
testing, I've found that the max_prepared_transactions=3
max_connections=10 shared_buffers=20 was well enough to fit
On Mon, 29 Aug 2005, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
So, are the shared memory requirements increased for 8.1 ?
Yes; mostly from 2PC support I think. Try reducing
max_prepared_transactions. (We might want to debate whether the default
setting should be smaller
Heikki Linnakangas [EMAIL PROTECTED] writes:
On Mon, 29 Aug 2005, Tom Lane wrote:
Yes; mostly from 2PC support I think. Try reducing
max_prepared_transactions. (We might want to debate whether the default
setting should be smaller than 50 --- it looks to me like that's adding
over 700K to
On Mon, 29 Aug 2005, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
Yes, the decreasing of max_prepared_transaction helped (after some
testing, I've found that the max_prepared_transactions=3
max_connections=10 shared_buffers=20 was well enough to fit 1mb of
shared memory)
20
Alvaro Herrera [EMAIL PROTECTED] writes:
On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote:
20 buffers ... ugh. Obviously we are on the hairy edge of no longer
functioning at all in 1MB shared memory. I'm not sure there is a whole
lot we can do about this, but it's a tad irritating
I wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
8 buffers each, I think, no? That's 32 buffers total.
You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's
now 8. Did we bump that up on the basis of any solid evidence?
Never mind, looks like that goes all the way back:
Heikki Linnakangas [EMAIL PROTECTED] writes:
On Mon, 29 Aug 2005, Tom Lane wrote:
20 buffers ... ugh. Obviously we are on the hairy edge of no longer
functioning at all in 1MB shared memory. I'm not sure there is a whole
lot we can do about this, but it's a tad irritating that clog,
Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Oh, and 'select rowid, * from table' which returns special rowid
column that just incrementally numbers each row.
Why?
Perhaps Christopher meant
select row_number() OVER (...) as rowid
Jim C. Nasby [EMAIL PROTECTED] writes:
Of course this might not make it into 8.1, but it seems somewhat
backwards to be setting the default config just to satisfy make check.
Some of us prefer make installcheck ... so I'd still resist setting
the defaults to values that would make the
On Sat, 2005-08-27 at 17:00 -0700, Varun Kacholia wrote:
Hi everybody,
I would like to add query sampling support to postgresql (atleast as a part
of
my project, if someone feels strongly against checking it in the main
branch).
I have been going over the code and I do see a lot of
On Mon, Aug 29, 2005 at 01:28:22PM -0400, Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
On Mon, 29 Aug 2005, Tom Lane wrote:
Yes; mostly from 2PC support I think. Try reducing
max_prepared_transactions. (We might want to debate whether the default
setting should be
On Thu, 2005-08-25 at 19:13 -0400, Alvaro Herrera wrote:
Or, slightly different, what are people's most wanted features?
My approach to that question has been to try to group together
particular use cases. Currently, I see that PostgreSQL is great for web
applications (OLTP) and getting better
In PostgreSQL Weekly News, David Fetter wrote:
Please test the new beta. Some of the new features are at
http://developer.postgresql.org/docs/postgres/release.html#RELEASE-8-1
I notice that Neil's patch regarding reducing the number of memory
allocations during aggregation operations isn't
1)
I want to test my own selectivity function against
future stats collector.
Is this a right way to update statistic used by
planner ?
update pg_statistic set
stadistinct=4,stakind3=1,stanumbers3=array[0.8,0.2],stavalues3=array[1,10001]
where starelid=950855 and staattnum =
Thanks, Tom for keeping on this. I was on vacation last week. Will try
the latest ASAP.
On Mon, 2005-08-22 at 20:20 -0400, Tom Lane wrote:
I wrote:
I've been sniffing around that patch and not really finding any smoking
gun about why it would make things slower when you're not using
I assume you realise that Bernoulli sampling is currently possibly using
the random() function and setseed() ?
Yes, select * from table where random() x, does the job.
I can't see why TABLESAMPLE effects a sequential scan *only*, in all
cases. I agree that there seems little point in
[Please CC any replies, thanks]
Hi,
I saw the discussion about an tester for MVCC. Since I'd never done
anything with asyncronous queries before, I figured I'd try to write
something useful with it. The result is at:
http://svana.org/kleptog/pgsql/mvcctest.tar.gz
It's a tester that takes a
I have a table with several 'smallint' fields that I'd like to convert to
booleean ... the data in each is either 0 or 1, and:
# select '1'::boolean;
bool
--
t
(1 row)
# select '0'::boolean;
bool
--
f
(1 row)
so they do cast as expected ... but, if I try to do the ALTER, I get:
On Mon, Aug 29, 2005 at 08:15:41PM -0300, Marc G. Fournier wrote:
I have a table with several 'smallint' fields that I'd like to convert to
booleean ... the data in each is either 0 or 1, and:
# select '1'::boolean;
bool
--
t
(1 row)
# select '0'::boolean;
bool
--
f
(1
On Mon, 2005-08-29 at 20:15 -0300, Marc G. Fournier wrote:
I have a table with several 'smallint' fields that I'd like to convert to
booleean ... the data in each is either 0 or 1, and:
# ALTER TABLE table ALTER COLUMN field1 type boolean;
ERROR: column field1 cannot be cast to type
Marc G. Fournier [EMAIL PROTECTED] writes:
# ALTER TABLE table ALTER COLUMN field1 type boolean;
ERROR: column field1 cannot be cast to type pg_catalog.bool
Should this not work?
No, because there's no built-in cast from smallint to bool. You could
do something like
... type boolean using
David Fetter [EMAIL PROTECTED] writes:
I've noticed that \df doesn't do quite what it might when a function
is created with named input parameters. Please find enclosed a patch
against CVS TIP that does this better.
Meanwhile, getting back to the actual merits of the patch ... this is
not
On Mon, Aug 29, 2005 at 11:13:29AM -0400, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
I've noticed that \df doesn't do quite what it might when a
function is created with named input parameters. Please find
enclosed a patch against CVS TIP that does this better.
Meanwhile,
David Fetter [EMAIL PROTECTED] writes:
Speaking of said psql's columnar representations, what about the
alignment thing proposed earlier where an embedded newline doesn't
mess up the alignment of everything else? Is there some generic way
to handle this?
If that's not on TODO already, it
Has anyone been able to set kern.sysv.shmmax above 4MB at all in latest
OS X? I just spent a while trying what seemed every possible
permutation of setting up /etc/sysctl.conf and editing /etc/rc directly,
and it just fails (symptom: sysctl shows shmmax as -1, and Postgres
cannot start).
Grrr.
Hi,
i have installed the latest CVS, 8.1beta1.
and get an error executing at DROP USER. these are the commands i
execute and the error i get:
in: psql -U postgres template1
CREATE USER deimos;
CREATE TABLESPACE sgis_dat OWNER deimos LOCATION '/data/postgres/sgis_dat';
CREATE TABLESPACE
Simon Riggs [EMAIL PROTECTED] writes:
I notice that Neil's patch regarding reducing the number of memory
allocations during aggregation operations isn't mentioned. It was
originally discussed in 8.0beta (2-3?) time.
What happened there?
- patch not committed in the end
- committed but not
Tom,
On 8/29/05 5:18 PM, Tom Lane [EMAIL PROTECTED] wrote:
Has anyone been able to set kern.sysv.shmmax above 4MB at all in latest
OS X? I just spent a while trying what seemed every possible
permutation of setting up /etc/sysctl.conf and editing /etc/rc directly,
and it just fails
On Mon, Aug 29, 2005 at 07:19:21PM -0500, Jaime Casanova wrote:
the i drop the objects:
DROP DATABASE sgis;
DROP TABLESPACE sgis_dat;
DROP TABLESPACE sgis_idx;
DROP USER deimos;
ERROR: cache lookup failed for tablespace 16396
Confirmed. Most likely this is a problem in shared dependency
Jaime Casanova [EMAIL PROTECTED] writes:
the i drop the objects:
DROP DATABASE sgis;
DROP TABLESPACE sgis_dat;
DROP TABLESPACE sgis_idx;
DROP USER deimos;
ERROR: cache lookup failed for tablespace 16396
Fixed ... it seems the shared-dependency patch missed DROP TABLESPACE.
If you need to
Alvaro Herrera [EMAIL PROTECTED] writes:
I'll submit a fix after dinner.
Done already ...
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Tom Lane wrote:
Has anyone been able to set kern.sysv.shmmax above 4MB at all in latest
OS X? I just spent a while trying what seemed every possible
permutation of setting up /etc/sysctl.conf and editing /etc/rc directly,
and it just fails (symptom: sysctl shows shmmax as -1, and Postgres
Luke Lonergan [EMAIL PROTECTED] writes:
Yes - it's very strange, I've had the same experience though I finally found
that setting SHMMAX and SHMALL to the same values, namely 268435456, seems
to work out fine.
Interesting. I wonder if there's some bit of code that thinks that
SHMALL is
On Aug 29, 2005, at 8:18 PM, Tom Lane wrote:
Has anyone been able to set kern.sysv.shmmax above 4MB at all in
latest
OS X? I just spent a while trying what seemed every possible
permutation of setting up /etc/sysctl.conf and editing /etc/rc
directly,
and it just fails (symptom: sysctl
In order to get postgres working in concert with an iSight on a
PowerBook, I had to increase shmmax, and it seemed to work just fine
by editing /etc/rc:
sysctl -w kern.sysv.shmmax=134217728 kern.sysv.shmmin=1
kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024
After restarting,
Tom,
On 8/29/05 6:41 PM, Tom Lane [EMAIL PROTECTED] wrote:
Interesting. I wonder if there's some bit of code that thinks that
SHMALL is measured in bytes (contrary to OSX's general convention
that it's measured in pages).
I don't know, but I agree that the behavior has changed from Panther
Thomas F. O'Connell [EMAIL PROTECTED] writes:
After restarting, I have:
# sysctl -a | grep shm
kern.sysv.shmmax: 134217728
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024
Of course, this still doesn't seem to be enough to let postgres play
nicely
On Aug 29, 2005, at 10:37 PM, Tom Lane wrote:
Thomas F. O'Connell [EMAIL PROTECTED] writes:
After restarting, I have:
# sysctl -a | grep shm
kern.sysv.shmmax: 134217728
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024
Of course, this still doesn't
I just spent a tedious hour digging through the buildfarm results
to see what I could learn about the intermittent failures we're seeing
in the stats regression test, such as here:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=ferretdt=2005-05-29%2018:25:09
This is seen in both Check and
Jeff - [EMAIL PROTECTED] writes:
On Aug 29, 2005, at 8:18 PM, Tom Lane wrote:
Has anyone been able to set kern.sysv.shmmax above 4MB at all in
latest OS X?
yeah, you need to set shmmax and shmall.
Did that. Set shmall first, shmall second, both together in one sysctl
command; no joy
66 matches
Mail list logo