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/pgsql
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
conditions, bitmap heap
Tom Lane wrote:
Jack Orenstein jack.orenst...@hds.com 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
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
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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
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
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
? 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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
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:
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 make changes to your subscription:
http
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 behavior will change
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 index
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
UPDATE
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
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 (pgsql-general@postgresql.org
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 = 1
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
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
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 get
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 size
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, I misspoke. I have
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 work, I'm just
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
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
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
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
postgresql
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 |\
psql ... -c
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 state.
Jack Orenstein
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
+ 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 through Usenet, please send
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
---(end of broadcast
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
message can get through
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_COMMAND_OK
. 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
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
---(end of broadcast
On 9/20/06, Martijn van Oosterhout kleptog@svana.org 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
: 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
---(end of broadcast)---
TIP 1
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 has to be pfree'd, correct
On 9/19/06, Martijn van Oosterhout kleptog@svana.org 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]
...
Jack I have an int8 that I
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,
and I know the length). I want to obtain
On 9/13/06, Martijn van Oosterhout kleptog@svana.org 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 bigints
. 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
---(end of broadcast
);
...
}
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)---
TIP 6
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);
...
}
then how can I
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 specifically mentions
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 versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining
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 Orenstein
---(end of broadcast
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 debugger to get
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.
What this looks like to me
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
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])
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
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 lists at once
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.
---(end
58 matches
Mail list logo
