So, what is better from the postgres memory point of view: to use
temporary
objects, or to use common variables ?
A temp table might take *slightly* more room than variables...
Can you suggest me other point of views to be taken into consideration in
my
case ?
Code maintenance. I can't
Hello list,
I have a mission to implement a two-node active-passive PostgreSQL cluster.
The databases at the cluster are rather large (hundreds of GB's) which opts
me to consider a shared disk environment. I know this is not natively
supported with PostgreSQL, but I have been investigating the
hi
I use UTF-8 as server character encoding,
and use sjis as client character encoding.
For some reason, some none sjis encoding character was insert into the database.
WHEN I use
set client_encoding='SJIS
select * from xxx
I got such error message
Native Error: ERROR: character 0xc2a0 of
On 16.08.2007 08:42, Mikko Partio wrote:
I have a mission to implement a two-node active-passive PostgreSQL cluster.
The databases at the cluster are rather large (hundreds of GB's) which opts
me to consider a shared disk environment. I know this is not natively
supported with PostgreSQL, but I
Hi,
On Thu, 2007-08-16 at 09:42 +0300, Mikko Partio wrote:
The idea would be that the cluster programs with gfs (and HP ilo)
would make sure that only one postmaster at a time would be able to
access the shared disk, and in case the active node fails the cluster
software would shift the
Hi,
On Thu, 2007-08-16 at 10:05 +0300, Devrim GÜNDÜZ wrote:
(Of course, there is still a risk of data loss -- the postmasters are
not aware of each other and they don't share each other's buffers,
etc.)
Err... I was talking about uncommitted transactions, and of course this
does not mean a
Am Donnerstag, 16. August 2007 08:40 schrieb bhyuan:
Can I ignore the error message by confiing the config file?
No, there are not provisions for that. Some errors of this type used to be
ignored, but that led to SQL injection-like security issues, so you don't
want that.
--
Peter
Hello,
I would like to ask you for an advice.
There are two tables in my PostgreSQL database - main table with datas and
second with translations for all languages of these records.
When I try to UPDATE a record in the language table and this record
doesn't exists there I need to INSERT into
am Thu, dem 16.08.2007, um 10:30:01 +0200 mailte Ji?í N?mec folgendes:
Hello,
I would like to ask you for an advice.
There are two tables in my PostgreSQL database - main table with datas and
second with translations for all languages of these records.
When I try to UPDATE a record in
there is no array for cstring type. I created it manually in pg_type table.
It seems to me that it's working fine. Can this cause any problems?
PHP receives data as text. Records are converted to cstring through
record_out function. So I just wanted to substitute refcursors/setof
records. Can this
Am Donnerstag, 16. August 2007 11:22 schrieb Sergey Moroz:
there is no array for cstring type. I created it manually in pg_type table.
It seems to me that it's working fine. Can this cause any problems?
The cstring type is not for general use. Use varchar or text.
--
Peter Eisentraut
On Thu, Aug 16, 2007 at 01:21:43AM -0400, Tom Lane wrote:
Decibel! [EMAIL PROTECTED] writes:
But... taking a quick look at RI_FKey_check in backend/utils/adt/
ri_triggers.c, I don't see it checking to see if the FK has changed,
which seems odd. I would think that if the FK fields haven't
Scott Marlowe wrote:
When I go to amazon.com I only ever get three pages of results. ever.
Because they know that returning 190 pages is not that useful, as
hardly anyone is going to wander through that many pages.
Google, you'll notice says Results 1 - 10 of about 5,610,000 for
blacksmith
On Thu, Aug 16, 2007 at 09:17:37AM +0300, Sabin Coanda wrote:
So, what is better from the postgres memory point of view: to use
temporary
objects, or to use common variables ?
A temp table might take *slightly* more room than variables...
Can you suggest me other point of views to
On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote:
Scott Marlowe wrote:
When I go to amazon.com I only ever get three pages of results. ever.
Because they know that returning 190 pages is not that useful, as
hardly anyone is going to wander through that many pages.
Google,
Decibel! wrote:
On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote:
Scott Marlowe wrote:
When I go to amazon.com I only ever get three pages of results. ever.
Because they know that returning 190 pages is not that useful, as
hardly anyone is going to wander through that many
hi,
i need something to distinguish two separate calls for some select.
i tried to use c functions GetCurrentTransactionId() and
GetCurrentCommandId(),
but there is a problem:
if i'll make plpgsql function, which uses GetCurrentTransactionId() and
GetCurrentCommandId() - getCurrentCommandId
Rainer Bauer [EMAIL PROTECTED] writes:
Anyway, what Phoenix is trying to say is that 2 queries are required: One to
get the total count and one to get the tuples for the current page. I reckon
it would help, if the query returning the result set could also report the
total no. of tuples
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
But if you go to eBay, they always give you an accurate count. Even if the
no.
of items found is pretty large (example: http://search.ebay.com/new).
And I'd bet money that they're using a full text search of some kind to
get those
Hello list,
If I've got a trigger that calls a function each time there is a DELETE or
UPDATE opration on a table in my system, and in this function I retrieve
some boolean information from another table and based on this information,
additional code will be ran or not in this function. Could the
On 16/08/07, Richard Broersma Jr [EMAIL PROTECTED] wrote:
--- Phoenix Kiula [EMAIL PROTECTED] wrote:
On 16/08/07, Rodrigo De León [EMAIL PROTECTED] wrote:
On Aug 15, 11:46 pm, [EMAIL PROTECTED] (Phoenix Kiula) wrote:
Appreciate any tips, because it would
be nasty to have to do this
On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote:
Anyway, what Phoenix is trying to say is that 2 queries are required: One to
get the total count and one to get the tuples for the current page. I reckon
it would help, if the query returning the result set could also report the
am Thu, dem 16.08.2007, um 14:11:07 +0200 mailte Ji??í N??mec folgendes:
see
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
Yes I see, but I'll try to describe it in more detail:
I could write plpgsql trigger function which will
Probably an optimistic question - can a user with access to two
databases create a function in one to access tables in the other? Or
triggers?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
take a look at dblink in contrib module
With Regards
Ashish...
- Original Message -
From: Phoenix Kiula [EMAIL PROTECTED]
To: Postgres General pgsql-general@postgresql.org
Sent: Thursday, August 16, 2007 6:18 PM
Subject: [GENERAL] Accessing tables in other databases, through functions
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jean-David Beyer wrote:
Tom Lane wrote:
Jean-David Beyer [EMAIL PROTECTED] writes:
What is the current status of (pre) compiling Embedded SQL in C++ programs?
I just asked Michael Meskes about that (had you put a support request
into Red Hat
Hello
look on
see http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/
or http://pgfoundry.org/projects/dblink-tds/
Regards
Pavel Stehule
2007/8/16, Phoenix Kiula [EMAIL PROTECTED]:
Probably an optimistic question - can a user with access to two
databases create a function in one
Thanks for your replay.
Maybe SQL injection-like security issues will occour,
but I find that differend version of Postgresql get different result.
Such as the sql
set client_encoding='SJIS';
select '\xc3\xaa',* from xxx;
on V7.4 @RH3 got
\xc3\xaa
on [EMAIL PROTECTED] got
(blank)
on
On Wed, August 15, 2007 21:15, Phoenix Kiula wrote:
Thanks. Here's my locale information:
locale
LANG=en_US.UTF-8
LC_CTYPE=en_US.UTF-8
LC_NUMERIC=en_US.UTF-8
LC_TIME=en_US.UTF-8
LC_COLLATE=en_US.UTF-8
LC_MONETARY=en_US.UTF-8
LC_MESSAGES=en_US.UTF-8
LC_PAPER=en_US.UTF-8
Phoenix Kiula wrote:
On 16/08/07, Richard Broersma Jr [EMAIL PROTECTED] wrote:
--- Phoenix Kiula [EMAIL PROTECTED] wrote:
On 16/08/07, Rodrigo De León [EMAIL PROTECTED] wrote:
On Aug 15, 11:46 pm, [EMAIL PROTECTED] (Phoenix Kiula) wrote:
Appreciate any tips, because
--- Phoenix Kiula [EMAIL PROTECTED] wrote:
UPDATE T1
SET T1.title = T2.title
FROM T2
WHERE T1.id = T2.id
AND T1.title IS NULL;
Thanks much RIchard, but neither of those work. For me table t1 has
over 6 million rows, and table t2 has about 600,000. In both of the
queries
Am Donnerstag, 16. August 2007 15:21 schrieb bhyuan:
Maybe SQL injection-like security issues will occour,
but I find that differend version of Postgresql get different result.
That just shows that some versions are more broken than others. But there was
a lot of thought put into the current
On 8/16/07, Phoenix Kiula [EMAIL PROTECTED] wrote:
Probably an optimistic question - can a user with access to two
databases create a function in one to access tables in the other? Or
triggers?
If this is related
data that you just need logically separated, schemas might work for
you too:
On Thu, Aug 16, 2007 at 01:30:11PM +0200, hubert depesz lubaczewski wrote:
hi,
i need something to distinguish two separate calls for some select.
i tried to use c functions GetCurrentTransactionId() and
GetCurrentCommandId(),
but there is a problem:
if i'll make plpgsql function, which
On Thu, Aug 16, 2007 at 08:52:02AM -0300, Marcelo de Moraes Serpa wrote:
Hello list,
If I've got a trigger that calls a function each time there is a DELETE or
UPDATE opration on a table in my system, and in this function I retrieve
some boolean information from another table and based on
On Thu, Aug 16, 2007 at 09:14:24AM -0500, Decibel! wrote:
Well of course, if you're running it in a separate command. If you run
the function twice from one query I'd expect both to return the same.
no. if i run one query with function i get sifferend commandid's inside
the function.
example:
D. Dante Lorenso [EMAIL PROTECTED] writes:
I need logic like atomic test and set or pop 1 item off the queue
atomically and tell me what that item was.
In my situation, there are a dozen or so machines polling this queue
periodically looking for work to do. As more polling is occurring,
Devrim GÜNDÜZ [EMAIL PROTECTED] writes:
What I'm pondering here is that is the cluster able to keep the
postmasters synchronized at all times so that the database won't get
corrupted.
Keep all the $PGDATA in the shared disk. That would minimize data loss
(Of course, there is still a risk
On Aug 15, 2007, at 9:21 PM, D. Dante Lorenso wrote:
Erik Jones wrote:
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item,
so inside PGSQL, use SELECT
Trevor Talbot wrote:
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
But if you go to eBay, they always give you an accurate count. Even if
the no.
of items found is pretty large (example: http://search.ebay.com/new).
And I'd bet money that they're using a full text search of some
Gregory Stark wrote:
Rainer Bauer [EMAIL PROTECTED] writes:
Anyway, what Phoenix is trying to say is that 2 queries are required: One to
get the total count and one to get the tuples for the current page. I reckon
it would help, if the query returning the result set could also report the
Martijn van Oosterhout wrote:
On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote:
Anyway, what Phoenix is trying to say is that 2 queries are required: One to
get the total count and one to get the tuples for the current page. I reckon
it would help, if the query returning the result
hubert depesz lubaczewski [EMAIL PROTECTED] writes:
so - as you can see in one query, the value changes in plpgsql. and i need
some
id that will be unchanged within one end-user-supplied query.
AFAIR, the only state that's guaranteed to work like that is
statement_timestamp. Of course you
Hi Tom :)
Is this:
libpq-fe.h:
/**
$PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.134 2006/10/04
00:30:13 momjian Exp $
**/
the latest version of libpq?
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: madhtr [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
On 16/08/07, Rainer Bauer [EMAIL PROTECTED] wrote:
Gregory Stark wrote:
Rainer Bauer [EMAIL PROTECTED] writes:
Anyway, what Phoenix is trying to say is that 2 queries are required: One
to
get the total count and one to get the tuples for the current page. I
reckon
it would help,
Postgres Gurus,
Please suggest me what is wrong with this
function.
This function tries to retrieve set of rows from description table based
on set of ID fields passed as array.
The error, I get is : ERROR: only one AS item needed for language plpgsql
CREATE OR
In response to Rainer Bauer [EMAIL PROTECTED]:
Trevor Talbot wrote:
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
But if you go to eBay, they always give you an accurate count. Even if
the no.
of items found is pretty large (example: http://search.ebay.com/new).
And I'd
Rainer Bauer [EMAIL PROTECTED] writes:
My point is that whatever search criterias are involved and how many items
are found eBay always returns the *accurate* number of items found.
And exactly how do you know that that's true?
regards, tom lane
madhtr wrote:
Is this:
libpq-fe.h:
/**
$PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.134 2006/10/04
00:30:13 momjian Exp $
**/
the latest version of libpq?
This is what we ship with 8.2.4, yes.
--
Alvaro Herrera
Hi
I have installed PostGRESql 7.4.17 and when i try to connect to the db i get
the msg
psql: FATAL: no pg_hba.conf entry for host ???, user sfmdb, database
LOGDB, SSL off
My pg_hba.conf entry is as below
# TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD
#
On Aug 16, 5:19 am, [EMAIL PROTECTED] (Decibel!) wrote:
On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote:
Scott Marlowe wrote:
But if you go to eBay, they always give you an accurate count. Even if the
no.
of items found is pretty large (example: http://search.ebay.com/new).
Bill Moran wrote:
Consider this query with multiple WHERE conditions:
http://search.ebay.com/ne-ol-an_W0QQfasiZ1QQfbdZ1QQfcdZ1QQfcidZ77QQfclZ3QQfmcZ1QQfrppZ50QQfsooZ1QQfsopZ1QQftidZ1QQpriceZ1QQsabdhiZ100QQsacurZ999QQsalicZQ2d15QQsaprchiZ5QQsatitleZQ28neQ2aQ2colQ2aQ2canQ2aQ29QQsojsZ0
My
Tom Lane wrote:
Rainer Bauer [EMAIL PROTECTED] writes:
My point is that whatever search criterias are involved and how many items
are found eBay always returns the *accurate* number of items found.
And exactly how do you know that that's true?
5 years experience with developing a browser for
On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote:
Postgres Gurus,
Please suggest me what is wrong with this
function.
This function tries to retrieve set of rows from description table based
on set of ID fields passed as array.
The error, I
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
My point is that whatever search criterias are involved and how many items
are found eBay always returns the *accurate* number of items found.
Before this drifts off:
* I do know *why* count(*) is slow using Postgres.
* I *think* that
On Thu, 2007-08-16 at 21:16 +0530, Rajaram J wrote:
Hi
I have installed PostGRESql 7.4.17 and when i try to connect to the db
i get the msg
psql: FATAL: no pg_hba.conf entry for host ???, user sfmdb,
database LOGDB, SSL off
You are allowing local, non TCP/IP connctions, and connections
Decibel! wrote:
On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote:
Postgres Gurus,
Please suggest me what is wrong with this
function.
This function tries to retrieve set of rows from description table based
on set of ID fields passed as
Trevor Talbot wrote:
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
My point is that whatever search criterias are involved and how many items
are found eBay always returns the *accurate* number of items found.
Before this drifts off:
* I do know *why* count(*) is slow using Postgres.
Thank you, just had time to test this.
I wrote a simple sproc that adds a row to a test table. I've created a one
step job
db = db the sproc is in
kind = sql
def = SELECT proc_test01_insertrow();
set to run on each minute of every hour every day, starting today 00:00:01
The job, step, and
On Aug 16, 11:06 am, [EMAIL PROTECTED] (Ranjan Kumar Baisak)
wrote:
Decibel! wrote:
On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote:
Postgres Gurus,
Please suggest me what is wrong with this
function.
This function tries to retrieve set
On Thu, Aug 16, 2007 at 11:20:30AM -0400, Tom Lane wrote:
AFAIR, the only state that's guaranteed to work like that is
statement_timestamp. Of course you have to worry whether your machine
is fast enough to do more than one client interaction within whatever
the clock resolution is.
i'll
After using PostgreSQL 8.1 and earlier versions for years, I'm suddenly
encountering a vexing problem. A certain kind of update and insert is
frequently reverting to its original condition after a day or two -- the
inserted record is gone and the updated one is back the way it was.
There seems
On Aug 16, 2007, at 12:27 , John Gunther wrote:
After using PostgreSQL 8.1 and earlier versions for years, I'm
suddenly encountering a vexing problem. A certain kind of update
and insert is frequently reverting to its original condition after
a day or two -- the inserted record is gone
Ok, i figured something out anyway ... hopefully it will be helpful to the
group ...
The following works on my WinXP pro machine most of the time. I have
confirmed
that I have the latest versions of everything.
///
#pragma once
#include stdlib.h
#include
Hello,
Can anybody suggest me how to use Integer array inside a
function in where IN clause.
In my function, I have used it like
WHERE d_base.id in (|array_to_string| (integer_array, ','))
However it does not work. ANy suggestion?
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
Trevor Talbot wrote:
On 8/16/07, Rainer Bauer [EMAIL PROTECTED] wrote:
My point is that whatever search criterias are involved and how many items
are found eBay always returns the *accurate* number of items found.
Before this drifts
Douglas McNaught wrote:
D. Dante Lorenso [EMAIL PROTECTED] writes:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
immediately after
On Thu, 2007-08-16 at 14:36 -0400, Ranjan Kumar Baisak wrote:
Hello,
Can anybody suggest me how to use Integer array inside a
function in where IN clause.
In my function, I have used it like
WHERE d_base.id in (array_to_string (integer_array, ','))
However it does not work.
Scott Marlowe wrote:
FYI, I went to the ebay page you posted, which listed something like
98011 pages, and asked for page 96000. It searched for about a minute
and timed out with the error message
There was a problem executing your request. Please try again.
Tried it again, twice, about 5
novnov wrote:
Thank you, just had time to test this.
I wrote a simple sproc that adds a row to a test table. I've created a one
step job
db = db the sproc is in
kind = sql
def = SELECT proc_test01_insertrow();
set to run on each minute of every hour every day, starting today
D. Dante Lorenso [EMAIL PROTECTED] writes:
Douglas McNaught wrote:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
immediately
Tom Lane wrote:
D. Dante Lorenso [EMAIL PROTECTED] writes:
Douglas McNaught wrote:
How quickly after you update the row status are you comitting (and
releasing locks)?
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
I am trying to force a column to have lowercase because Postgresql is
case-sensitive in queries. For the time being I've made an expression
index on lower(KEY). But I would like to have just lower case data and
then drop this expression index.
However, I see some inconsisent behavior from
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/16/07 21:58, Phoenix Kiula wrote:
I am trying to force a column to have lowercase because Postgresql is
case-sensitive in queries. For the time being I've made an expression
index on lower(KEY). But I would like to have just lower case data
On Aug 16, 2007, at 21:58 , Phoenix Kiula wrote:
However, I see some inconsisent behavior from Postgresql. When I issue
an UPDATE command , it shows me a duplicate violation (which could be
correct) --
-# update TABLE set ACOLUMN = lower(ACOLUMN);
ERROR: duplicate key violates unique
Hi,
I know this issue has been discussed at length before, but postgresql's
behaviour of forcing a rollback when any error occurs is making life
very difficult for me. We use Spring's transaction proxies, which are
applied to methods in web controllers. In the backend code, if a runtime
Phoenix Kiula [EMAIL PROTECTED] writes:
However, I see some inconsisent behavior from Postgresql. When I issue
an UPDATE command , it shows me a duplicate violation (which could be
correct) --
-# update TABLE set ACOLUMN = lower(ACOLUMN);
ERROR: duplicate key violates unique
D. Dante Lorenso [EMAIL PROTECTED] writes:
Tom Lane wrote:
D. Dante Lorenso [EMAIL PROTECTED] writes:
I am calling a stored proc from PHP. Since I do not begin a
transaction, I assume that my call is automatically committed
immediately after invocation.
Have you actually verified that,
I suspect you're not showing us the exact queries you're running. For
one, you can't have a table named TABLE (without quotes) in PostgreSQL.
Of course. The data is a tad private, hence the simple table and
column names represented in uppercase.
Perhaps something else you changed when
On 17/08/07, Tom Lane [EMAIL PROTECTED] wrote:
Phoenix Kiula [EMAIL PROTECTED] writes:
However, I see some inconsisent behavior from Postgresql. When I issue
an UPDATE command , it shows me a duplicate violation (which could be
correct) --
-# update TABLE set ACOLUMN =
Tyson Lloyd Thwaites [EMAIL PROTECTED] writes:
Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us
keep going.
How do you catch exceptions in these other dbs?
The way to do it in Postgres is with the SAVEPOINT command.
For example, if something goes wrong, I can't
Gregory Stark wrote:
Tyson Lloyd Thwaites [EMAIL PROTECTED] writes:
Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us
keep going.
How do you catch exceptions in these other dbs?
plain java try/catch. In other dbs, if I am in a txn, and I run 3
statements,
Vacuum memory usage is tuned by the maintenance_work_mem parameter. I
suggest you look at
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html and
http://www.postgresql.org/docs/8.2/static/kernel-resources.html#AEN19338.
Thanks Sander, I've read so many of these pages
it is all so easy with other dbs, but with postgresql
it is a nightmare... the only solution I can see is to remove the
declarative transactions in Spring and start using manual transactions
blocks around everything that could possibly go wrong... just because of
a quirk in postgresql
True... apologies for any offence caused. Tweaking emotion levels.
done :)
The core problem is that we are maintaining a fairly mature app, and the
behaviour in question means we effectively can't integrate with
postgresql (which is a shame - I really *really* want to... we currently
85 matches
Mail list logo
