i++ instead of i = i + 1.
This was over 4 years ago, and Oracle/java integration may have improved, but
slight
improvements in this area really don't change the equation much.
Jack Orenstein
---(end of broadcast)---
TIP 1: if posting/reading th
omenon? Why are these files
lost? Are they really lost, or have they simply moved somewhere? What
happens to the disk blocks formerly occupied by the files?
Getting back in service following this file loss is not a problem; I'm
just trying to understand how postgres gets into this st
Erik Jones wrote:
On Oct 12, 2007, at 2:40 PM, John D. Burger wrote:
DB-related humor:
http://xkcd.com/327/
Sanitize database inputs, by all means, but also use prepared statements.
Jack Orenstein
---(end of broadcast)---
TIP 4: Have you
and this produces a good query plan. But this means that fewer than
100 rows are returned. For reasons too boring to go into, that would
be very inconvenient for my application.
Why does adding the value restriction so radically change the execution plan?
Jack Orenstein
--
Sent via pgsql-ge
Sam Mason wrote:
On Mon, Jun 22, 2009 at 05:55:28PM -0400, Jack Orenstein wrote:
ris-# select *
ris-# from T
ris-# where pk > 10
ris-# and value = 'asdf'::bytea
ris-# order by pk
ris-# limit 100;
PG thinks that you're going to get 16 rows back matching those
condi
Tom Lane wrote:
Jack Orenstein writes:
Limit (cost=0.00..324.99 rows=100 width=451)
-> Index Scan using t_pkey on t (cost=0.00..296027.98 rows=91088
width=451)
Index Cond: (pk > 10)
Adding the value restriction at the top of this query plan wouldn'
from T
where x > $1
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm using postgresql 8.3.7. Under what conditions does a JDBC prepared statement
result in a server-side prepared statement?
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/
ctions?
What if we do a binary copy instead? (We're going to investigate BINARY to see
if there is a performance improvement.)
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Francisco Reyes wrote:
On 4:05 pm 07/21/08 Jack Orenstein <[EMAIL PROTECTED]> wrote:
What if we do a binary copy instead?
What do you mean by a binary copy?
pg_dump -Fc?
No, I mean changing this:
psql -h $SOURCE_HOST ... -c "copy $SOURCE_SCHEMA.$SOURCE_TABLE to stdout&q
ed.
Am I just on the wrong path here, starting with the postgresql and
postgresql-libs RPMs?
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a
large set of RPMs, these I think:
postgresql
postgresql-contrib
postgresql-devel
postgresql-jdbc
postgresql-libs
post
Our application is running Postgres 7.4, (working on conversion to 8.3
right now). Our testing involves various forms of violence, including
shutting off power and kill -9 postmaster.
Occasionally we observe a form of database corruption in which one of
the files storing a table or index disappea
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
The question is how to check for consistency in the case of
large tables, which are split into multiple segments, (e.g. 123456.1,
123456.2). I.e., how can I find out how many segments there should be?
The kernel-defined EOF
umulation of row versions have anything to do with autocommit mode (as
opposed to isolation level) on a connection used for the scan?
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Martijn van Oosterhout wrote:
On Wed, Sep 10, 2008 at 09:45:04AM -0400, Jack Orenstein wrote:
Am I on the right track -- does autocommit = false for the BIG scan force
versions of TINY to accumulate? I played around with a JDBC test program,
and so far cannot see how the autocommit mode causes
Tomasz Ostrowski wrote:
On 2008-09-11 17:21, Jack Orenstein wrote:
Then do the processing in separate transactions like this (in pseudocode):
The id > last_id trick doesn't work for me -- I don't have an index that would
support it efficiently.
Turning on autocommit seems to w
Tomasz Ostrowski wrote:
On 2008-09-11 18:03, Jack Orenstein wrote:
When you do:
result = query("select something from sometable")
then all rows of a result will be cached by a client program.
I am very sure this is not happening. Maybe some rows are being
cached (specifying fetch
Tomasz Ostrowski wrote:
On 2008-09-11 17:21, Jack Orenstein wrote:
The id > last_id trick doesn't work for me -- I don't have an index that would
support it efficiently.
You do not have a primary key? If you do then you have an index as it is
automatically created.
Sorry,
On Sep 13, 2008, at 4:39 AM, Tomasz Ostrowski wrote:
On 2008-09-12 15:52, Jack Orenstein wrote:
Sorry, I misspoke. I have an index, but preferred doing a scan
without the index in this case.
Why?
The only reason I can think of is that you'd like to avoid disk
seeking. But you g
ns:
- Why would the optimizer ever choose idx_dn over idx_df given that idx_df has
to be more selective?
- Is there any way to force the use of idx_df?
Jack Orenstein
P.S. Yes, I know, 7.4. We're upgrading to 8.3 but we have this problem right
now.
--
Sent via pgsql-general mailing list (pgs
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
If I run EXPLAIN on this query, (plugging in values 1 and 2 for the variables),
before VACUUM ANALYZE, I get the desired execution plan:
Index Scan using idx_df on T (cost=0.00..4.83 rows=1 width=454)
Index Cond: ((dh =
Researching this some more, it appears to be the case that VACUUM (by itself, no
ANALYZE) is changing the optimizer's behavior. Here is a self-contained test:
select '*** drop t';
drop table t cascade;
select '*** create t(dh, fh, nm, filler)';
create table t (dh int, fh int, nm int, filler ch
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
If you plug in a value that *does* occur in the table it should probably
choose the more-relevant index consistently.
Unfortunately, it matters a lot at runtime. The dh value is not very selective,
as shown
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
I may have simplified too far. Our application runs a number of
different queries. All our WHERE clauses restrict dh and fh. For a
given pair of (dh, fh) values, the initial query should come up empty
and then insert this pair, an
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
- I created two schemas, NOVAC and VAC, each with a table T as described above.
- Before loading data, I ran VACUUM ANALYZE on VAC.T.
- I then started loading data. The workload is a mixture of INSERT, SELECT and
UPDAT
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
EXPLAIN says that the correct index is being used -- it didn't used
to. However, pg_stat* says otherwise. In my test, I have exactly one
dh value. Running EXPLAIN with this value produces a plan using idx_dh
(the correct
resolve itself? We're not doing any full
vacuums.
We're in the process of upgrading to 8.3.4, so I'd appreciate any
throughs on whether and how this behavior will change with the newer
release.
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Scott Marlowe wrote:
On Wed, Nov 12, 2008 at 8:13 AM, Jack Orenstein <[EMAIL PROTECTED]> wrote:
My application is running on 7.4. We have one huge table that drives
SNIP
We're in the process of upgrading to 8.3.4, so I'd appreciate any
throughs on whether and how this beha
g of what is correct behavior (in the literal
and bound variable cases)? Is there a bug in the driver? in postgres? in the
docs? Or in my understanding?
Jack Orenstein
P.S. If you want to play with this, I can send you my test programs for the
cases described above.
--
Sent via pgsql-general m
Tom Lane wrote:
My Java is pretty weak, but doesn't it think that backslash is special
in string literals? If I assume that each pair of \'s in your examples
went to the database as one \, then the results are what I'd expect.
regards, tom lane
Daniel Verite wrote:
>
er? What about optimization of
any prepared statements on connections whose lifetime spans the creation of idx2?
Jack Orenstein
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm using postgresql 8.3.6 through JDBC, and trying to measure the maximum
transaction rate on a given Linux box. I wrote a test program that:
- Creates a table with two int columns and no indexes,
- loads the table through a configurable number of threads, with each
transaction writing one r
t run VACUUM VERBOSE from JDBC and get the output
in the postgres log file.
Jack Orenstein
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
the remote db,
21167, doesn't realize this, and the copy from process (21168) is
simply waiting for more data.
The postgres logs don't indicate any trouble on either host around the
time that the copy starts, or for the next few minutes.
Why is process 21167 stuck?
Jack Orenst
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
21167 ?S 0:00 /usr/local/pgsql/bin/psql -h 10.3.1.154 mydb -U username -c \copy sometable to stdout
Why is process 21167 stuck?
Are you sure it's made a connection at all? You might try attaching to
it with a
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
I'm not sure I did this right, (it's been a while since I worked with
gdb). But here's what I found.
Doesn't look like either of those can be trusted very far :-(. But both
of them seem to be waiting for input.
On 9/14/06, Andrew - Supernews <[EMAIL PROTECTED]> wrote:
On 2006-09-14, "Jack Orenstein" <[EMAIL PROTECTED]> wrote:
> I don't think I explained myself clearly. I have a C string (char*,
> terminating zero) and a byte array (char*, possibly containing zeros,
&g
On 9/18/06, Jack Orenstein <[EMAIL PROTECTED]> wrote:
-- Forwarded message --
From: Andrew - Supernews <[EMAIL PROTECTED]>
...
Jack> I have an int8 that I need as a Datum for use with
Jack> SPI_execute_plan.
Int64GetDatum(your_variable)
which then
On 9/19/06, Martijn van Oosterhout wrote:
On Tue, Sep 19, 2006 at 01:27:56PM -0400, Jack Orenstein wrote:
> On 9/18/06, Jack Orenstein <[EMAIL PROTECTED]> wrote:
> >-- Forwarded message --
> >From: Andrew - Supernews <[EMAIL PROTECTED]>
> ...
> &
On 9/20/06, Martijn van Oosterhout wrote:
On Tue, Sep 19, 2006 at 04:00:43PM -0400, Jack Orenstein wrote:
> Can you provide some guidance (or point to some documentation) on how
> to manage memory? Is the idea that I should (must?) not pfree
> palloc'ed memory from Int64GetDatum
psql:test.sql:23: WARNING: MY DEBUG OUTPUT
CONTEXT: SQL statement "insert into log select insert_ifs( $1 ,
$2 , $3 , $4 , $5 , $6 , $7 )"
PL/pgSQL function "regress" line 5 at SQL statement
for each line of output.
Jack Orenstein
---(
;s a cost
issue involved, as I need to update every row in a large table, never
updating the index key. Will this run faster if I drop the index?
(Yes, I can run the experiment, but I'd like to understand the
fundamentals better.)
Jack Orenstein
sary to compress
the space away. All of these seem incompatible with your requirements.
I agree with another responder who suggested using the filesystem
for your images.
Jack Orenstein
---(end of broadcast)---
TIP 6: explain analyze is your friend
strategy? (I can summarize my vacuuming
strategy for anyone interested.)
Jack Orenstein
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
The problem has occurred again, and I've found a buffer overflow in
psql that explains it. Here is code from src/bin/psql/common.c, from
the PrintQueryResults function:
case PGRES
drive to alter the table
for the rewrite that's going to take place.
Forgive a dumb question: What does postgresql do with ALTER TABLE?
What sort of modifications do not require time proportional to the
number of rows in the table?
Jack Orenstein
(plan);
...
}
then how can I access the saved plan in a later invocation of foobar?
saved_plan goes out of scope on exit from foobar.
I've googled, but found little but copies of the postgres doc quoted
above.
Jack Orenstein
---(end of broadcast)---
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
I'm missing something very basic. If I do this:
Datum foobar(PG_FUNCTION_ARGS)
{
...
void* plan = SPI_prepare(...);
void* saved_plan = SPI_saveplan(plan);
...
}
the
Tom Lane wrote:
Jack Orenstein <[EMAIL PROTECTED]> writes:
Assigning SPI_prepare output to a local makes sense. Assigning
SPI_saveplan output to a static makes sense. But I don't see
the point in assigning SPI_saveplan output to a local, yet
that's what one case the doc speci
h problems. I don't really understand why this works,
however. I would expect the commit or abort to suffice.
(I can post a test program demonstrating the problem if there is
interest.)
Jack Orenstein
---(end of broadcast)---
TIP 9: In version
nto a
Datum. Is CStringGetDatum the right thing to use?
bytea: I have an unsigned char* (not zero-terminated). Can I use
PointerGetDatum?
For the varchar and bytea cases, I assume that I simply pfree the
palloc'ed data as usual, after the SPI_execute_plan call.
Jack Orenstein
On 9/13/06, Martijn van Oosterhout wrote:
On Tue, Sep 12, 2006 at 09:57:33PM -0400, Jack Orenstein wrote:
> ...
> int, bigint: From looking at postgres.h, I realize that Datum is an
> unsigned long. I'm guessing that I should just be able to assign Datums
> carrying ints or bi
d when corruption
is detected?
- What symptoms should prompt us to suspect and check for corruption?
- Are there any tools we can run to determine whether a database is
corrupt?
Jack Orenstein
---(end of broadcast)---
TIP 2: you can get off all li
dds that a needed page will be present in the shared buffers.
I'm using 7.3.4, and will be upgrading to 7.4.2 soon.
Jack Orenstein
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Alvaro Herrera wrote:
On Wed, Jun 02, 2004 at 08:38:58PM -0400, Jack Orenstein wrote:
What is the pattern of access to data pages? I can think of two likely
answers:
1) The index is scanned for ages 30 through 40. As each index entry is
scanned, a row is retrieved.
This one. There have been
this would be a huge win in situations (like mine) where the keys never change.
Jack Orenstein
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
is behavior for pgplsql programs, where exceptions
cannot be caught, but it seems to be an unnecessary restriction for
Java, and in general, for applications written using APIs that permit
continuation following an error.
Jack Orenstein
Reference Information Systems, Inc.
---
58 matches
Mail list logo
