On Apr 2, 2008, at 9:02 PM, Alex Solovey wrote:
The reduced database example has the same problem in EXPLAIN
ANALYZE as production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
Hang on... You prefer sequential scans because i
"Adam Rich" <[EMAIL PROTECTED]> writes:
>> I have just created a table using SELECT INTO however the PK was
>> supposed to be a serial. It is now an integer. To make it a serial I
>> just create the seq and set the default to be the nextval() of that
>> sequence right? is there anything else I need
Andreas <[EMAIL PROTECTED]> writes:
> make[3]: *** No rule exists for Target »utf8_and_euc_jis_2004.o«,
> needed to create »libutf8_and_euc_jis_2004.so.0.0«. END.
There are two or three reports like this in the archives. It appears to
be related to using an old version of "tar" that fails to
On Wed, Apr 2, 2008 at 10:12 PM, Naz Gassiep <[EMAIL PROTECTED]> wrote:
> I have just created a table using SELECT INTO however the PK was
> supposed to be a serial. It is now an integer. To make it a serial I
> just create the seq and set the default to be the nextval() of that
> sequence right
> I have just created a table using SELECT INTO however the PK was
> supposed to be a serial. It is now an integer. To make it a serial I
> just create the seq and set the default to be the nextval() of that
> sequence right? is there anything else I need to do?
You'll want to do this:
ALTER SE
I have just created a table using SELECT INTO however the PK was
supposed to be a serial. It is now an integer. To make it a serial I
just create the seq and set the default to be the nextval() of that
sequence right? is there anything else I need to do? It'll maintain the
transactional safety
--- Ian Sillitoe <[EMAIL PROTECTED]> wrote:
> I completely take your points - so maybe I should be
> asking for advice on
> database design instead.
>
> We are annotating nodes on a hierarchical structure
> where NULL implied an
I don't mean to be rude, but yuck. Why provide a
record for data
Hi,
on my old SUSE 9.3 box the build won't run through for PG 8.3.0 and 8.3.1.
I earlier tried 8.3.0 and gave up. Now 8.3.1 doesn't work either.
I'm in directory postgresql-8.3.1 and run
./configure --enable-nls='de' --enable-thread-safety
the makefile gets created
Then I run make
The process d
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> I am in the process of converting our 7.4.19 database to 8.3.1. We have
> various scenarios where we need to disable all triggers for a given table
> except for the Slony replication triggers. I'm looking for advice for the
> "proper" way to d
Greetings:
I am in the process of converting our 7.4.19 database to 8.3.1. We have
various scenarios where we need to disable all triggers for a given table
except for the Slony replication triggers. I'm looking for advice for the
"proper" way to do this. I have a function, disable_triggers(tab
On Wed, Apr 02, 2008 at 02:42:08PM -0400, Morris Goldstein wrote:
> (Why not give postgres its own volumes? Long and not very interesting story.)
But relevant to this case. I think you need to give it its own volumes.
A
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Thanks very much for this and to all the reponses. As a side note, I've only
joined this mailing list this afternoon, but it already seems like an good
choice (albeit one I should have made about 2 years ago :P) - very active
and very helpful - excellent work, many thanks indeed.
Cheers,
Ian
On
On Wed, Apr 02, 2008 at 05:49:37PM +0100, Ian Sillitoe wrote:
> Unless I've missed something, the docs on
> http://www.postgresql.org/docs/8.1/static/functions-comparison.html seem to
> suggest that the concept is an example of bad programming and the workaround
> (of switching on the 'transform_nu
On Wed, Apr 2, 2008 at 1:06 PM, Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>,
>
> "Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> writes:
>
> > On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> >> ... I have no idea how it could be fixed.
>
>
In article <[EMAIL PROTECTED]>,
"Rodrigo E. De León Plicet" <[EMAIL PROTECTED]> writes:
> On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
>> ... I have no idea how it could be fixed.
> - CREATE INDEX xifoo ON foo(bar_id);
> - ANALYZE;
> - Retry.
A compound index
CREATE
On Wed, Apr 2, 2008 at 11:43 AM, Ian Sillitoe
<[EMAIL PROTECTED]> wrote:
> I completely take your points - so maybe I should be asking for advice on
> database design instead.
There are a couple of choice you can use:
First is the PostgreSQL contribe Module Ltree:
http://www.postgresql.org/docs/c
On Wed, Apr 2, 2008 at 1:20 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> In this simple (which means "reduced") test database, yes. But the actual
> table "foo" in production database:
>
> 1. partitioned, with 50+ partitions
> 2. heavily updated (and indexes make it slow)
> 3. has more fields
The reduced database example has the same problem in EXPLAIN ANALYZE as
production one, here:
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
That's why I posted the smallest dataset I could reproduce the problem with.
Rodrigo E. De León Pli
"Ian Sillitoe" <[EMAIL PROTECTED]> writes:
> Thanks for the reply - after a bit more poking around it seems that:
> t1.col IS NOT DISTINCT FROM t2.col
> should work - although I guess this means an upgrade from 8.1 to 8.3
Note that while this will give you the right answers, it will probably
be do
>>> "Ian Sillitoe" <[EMAIL PROTECTED]> 03/04/08 5:49 AM >>>
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
In a join, no value can be ascribed to a null field, so the equivalence fails.
You can do tests like IS NULL, wh
I completely take your points - so maybe I should be asking for advice on
database design instead.
We are annotating nodes on a hierarchical structure where NULL implied an
absence of a value in this depth of the hierarchy. As a method of enforcing
this view, we use constraints to make sure we onl
On Wed, Apr 2, 2008 at 10:55 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Morris Goldstein" <[EMAIL PROTECTED]> writes:
>
> > Without going into a lot of details about our application, I'll just
> > say that the ability to start Postgres with just the "startup schema"
> > present is incredibly usef
On Wed, Apr 2, 2008 at 12:12 PM, Rodrigo E. De León Plicet
<[EMAIL PROTECTED]> wrote:
> Also important, consider creating additional indexes based on your
> access patterns.
Good point. Note that you can create indexes and then track their
usefulness with the pg_stat_user_indexes view, which I f
> - CREATE INDEX xifoo ON foo(bar_id);
In this simple (which means "reduced") test database, yes. But the
actual table "foo" in production database:
1. partitioned, with 50+ partitions
2. heavily updated (and indexes make it slow)
3. has more fields like bar_id
We had indexes on several fiel
Also important, consider creating additional indexes based on your
access patterns.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <[EMAIL PROTECTED]> wrote:
> ... I have no idea how it could be fixed.
- CREATE INDEX xifoo ON foo(bar_id);
- ANALYZE;
- Retry.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.po
Ah - thanks and apologies for not finding those previous discussions. Does
anyone else feel this might be useful as a point on the NULL section of the
FAQ (it certainly would have saved me an afternoon)?
Cheers,
Ian
On Wed, Apr 2, 2008 at 6:24 PM, Craig Ringer <[EMAIL PROTECTED]>
wrote:
> Ian S
--- Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
> 'transform_null_equals' won't help you at all here
> since it only help
> in the very specific case of comparing with a
> constant. The easiest is
> to think of NULL as meaning 'unknown'. Clearly you
> can't compare that
> usefully with anythi
Hello,
I got 2 database, one for product use and another for developer use. Both are
same. In these database, I got a procedure which has a TIMSTAMP datatype as IN
parameter.
My problem is, when my Java application use the developer database and call
this procedure, it is working fine, but w
Hello,
I was trying to optimize a slow query in database running 8.3.1. It
turned out that planner is choosing nested loop join resulting in
multiple sequential scans over the long table. Here is a simplified
database schema, consisting of two tables:
CREATE TABLE bar (
bar_id in
On Wed, Apr 02, 2008 at 05:49:37PM +0100, Ian Sillitoe wrote:
> This is probably a stupid question that has a very quick answer, however it
> would be great if someone could put me out of my misery...
>
> I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
> function) where
Thanks for the reply - after a bit more poking around it seems that:
t1.col IS NOT DISTINCT FROM t2.col
should work - although I guess this means an upgrade from 8.1 to 8.3
Cheers,
Ian
On Wed, Apr 2, 2008 at 6:23 PM, Martijn van Oosterhout <[EMAIL PROTECTED]>
wrote:
> On Wed, Apr 02, 2008 at
Ian Sillitoe wrote:
This is probably a stupid question that has a very quick answer, however it
would be great if someone could put me out of my misery...
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
Sounds like you
Apologies - just reread my post and I've confused matters with typos during
the abtraction of my code. For the purposes of the example given, please
read "get_colnames_for_id()" rather than "get_cathcode()"...
Cheers,
Ian
On Wed, Apr 2, 2008 at 5:49 PM, Ian Sillitoe <[EMAIL PROTECTED]>
wrote:
>
This is probably a stupid question that has a very quick answer, however it
would be great if someone could put me out of my misery...
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
-- for a given id, return a bunch of col
On Apr 1, 2008, at 8:56 PM, Michael Enke wrote:
Hi all,
I need to know if multiple tables have (may have most probably)
identical content.
Since I want a fast solution (which means not comparing tables row
by row),
I thought it would be a good idea to have an sql function operating
on a ta
Jon,
Thanks for the reply.
As i explained earlier, I am using java (via hibernate) to control the
overall transaction.
I cannot have begin/rollback statements for dblink_exec.
Only when the entire method call from java succeeds I want the transaction to
be commited.
I dont want a
"Albe Laurenz" <[EMAIL PROTECTED]> writes:
> Peter Schuller wrote:
>> This is what I am wondering. Whether it is done this way due to
>> expecation/standard, or as an implementation side effect. In the
>> latter case it is fixable.
> I don't see how this could break a standard.
Actually, I think
On Apr 1, 2008, at 8:56 PM, Michael Enke wrote:
Hi all,
I need to know if multiple tables have (may have most probably)
identical content.
Since I want a fast solution (which means not comparing tables row
by row),
I thought it would be a good idea to have an sql function operating
on a t
Jon,
Thanks for the reply.
As i explained earlier, I am using java (via hibernate) to control the
overall transaction.
I cannot have begin/rollback statements for dblink_exec.
Only when the entire method call from java succeeds I want the transaction to
be commited.
I dont want a
"Morris Goldstein" <[EMAIL PROTECTED]> writes:
> Without going into a lot of details about our application, I'll just
> say that the ability to start Postgres with just the "startup schema"
> present is incredibly useful. On a SAN, we put this startup schema
> on a volume that is permanently bound
> Hey everyone,
>
> Are you going to PGCon 2008?
Yes, I am.
> If so, where are you staying. My company has
> decided to send me, and I am just wondering where to stay. Based on price,
> it looks like on the University of Ottawa campus, or the Quality Inn are my
> two choices (the third hotel i
On Wed, Apr 2, 2008 at 1:19 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Wed, 2 Apr 2008, mark wrote:
>
> this really clear! Thannks!!
> >
>
> This is the first time someone new to this has ever said that about
> checkpoint tuning, which is quite the victory for all of us who worked
> toward th
> Hi,
>
> Le Tuesday 01 April 2008 22:00:11 Irina Connelly, vous avez écrit :
> > I am looking around for an ETL tool to transfer data from one Postgres
> > database to another periodically.
>
> You could simply rely on COPY for this, as data coming out of PG could be
> trusted to be able to get
I sent this just a few days ago to the list on a similar question. You
will need to move your code from the trigger to a regular function like
the one below. The function should handle the update to the table and
also to the linked database.
create table log (update_datetime timestamp);
On Wed, Apr 2, 2008 at 8:44 AM, Zdenek Kotala <[EMAIL PROTECTED]> wrote:
> Morris Goldstein napsal(a):
> > - Postgres running normally on /dev/sda and /dev/sdb.
> > - Update to table in /dev/sdb tablespace is committed but still exists in
> WAL.
> > - Postgres crashes (e.g. power failure).
> > - Po
Hey everyone,
Are you going to PGCon 2008? If so, where are you staying. My company has
decided to send me, and I am just wondering where to stay. Based on price,
it looks like on the University of Ottawa campus, or the Quality Inn are my
two choices (the third hotel is more than I am allotted
Hi all,
Since 2008-03-27 I get in paralel to the list messages this useless
Daily diggest. Can anyone tell me who has activated this?
Is this an accident by the list owner?
Thanks, Greetings and nice Day
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Ne
Morris Goldstein napsal(a):
But that makes me wonder: what about this sequence of events:
- Postgres running normally on /dev/sda and /dev/sdb.
- Update to table in /dev/sdb tablespace is committed but still exists in WAL.
- Postgres crashes (e.g. power failure).
- Postgres starts with /dev/sda
Peter Schuller wrote:
[about a serialization error caused by a foreign key constraint]
>> Transaction 2 now issues an INSERT on "atable". This requires a
>> RowShareLock on the index row of the index on "othertable" that
>> is referenced by the foreign key constraint. But the corresponding
>> inde
Michael Enke wrote:
I need to know if multiple tables have (may have most probably)
identical content.
Since I want a fast solution (which means not comparing tables row by row),
I thought it would be a good idea to have an sql function operating on a
table or view
similar to md5sum on a file
> A SELECT ... FROM "othertable" ... FOR SHARE won't conflict with a
> concurrent update on "atable".
>
> Do I guess right that there was also an UPDATE on the row in
> "othertable"?
Yes, that was what I meant to convey. Sorry if I was not clear. The
point was that an INSERT to "atable" conflicte
Peter Schuller wrote:
> Using PostgreSQL 8.2, I have "atable" one of whose columns reference a
> column in "othertable". I see serialization failures as a result of
> *inserts* to atable in the context of:
>
> '"SELECT 1 FROM ONLY othertable x WHERE "otherid" = $1 FOR SHARE OF
> x" ' in 'INSER
Roberts Jon <[EMAIL PROTECTED]> wrote:
> >
> > Hi all,
> >
> > I was trying to find some way to implement multithreading into my
> > postgreSQL stored functions.
> >
>
> You can use pgAgent to submit a job for each thread.
>
>
> Jon
Well, that is also an interesting idea. I will definet
> create or replace function sum_elements(anyarray)
> returns anyelement as $$
> select sum($1[i])
>from generate_series(array_lower($1,1),
>array_upper($1,1)) g(i);
> $$ language sql immutable;
Thank you! Anyway what I was really asking was a "$" (or w
On Wed, 2 Apr 2008, Tomasz Ostrowski wrote:
Even 64 (I like round numbers) would not be too much. This
would make 1GB of data in WALs.
It's much worse than that. Assume the system starts a checkpoint after
checkpoint_segments worth of WAL files have been used. It may take that
long before
Michael Enke <[EMAIL PROTECTED]> writes:
> I need to know if multiple tables have (may have most probably)
> identical content.
That sounds me you should re-consider your database design. Having PK/FK
relations is the reason we divide common parts into separate tables to
avoid from data duplicatio
On Wed, 2 Apr 2008, mark wrote:
this really clear! Thannks!!
This is the first time someone new to this has ever said that about
checkpoint tuning, which is quite the victory for all of us who worked
toward the 8.3 functional and monitoring improvements in this area.
Please keep posting the
Ivan Sergio Borgonovo wrote:
> http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html
>
> "A STABLE function cannot modify the database and is guaranteed to
> return the same results given the same arguments for all rows within
> a single statement. This category allows the optimizer
On 2008-04-02 09:30, mark wrote:
> Based on what Tomasz suggested a day ago, I had changed settings to
>checkpoint_segments = 16
>checkpoint_timeout = 20min
>checkpoint_completion_target = 0.8
> but i still do get statements that take over 2 or 3 seconds to execute
> someti
am Wed, dem 02.04.2008, um 11:56:38 +0800 mailte Michael Enke folgendes:
> Hi all,
> I need to know if multiple tables have (may have most probably) identical
> content.
> Since I want a fast solution (which means not comparing tables row by row),
> I thought it would be a good idea to have an sq
On Tue, Apr 1, 2008 at 5:31 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Tue, 1 Apr 2008, mark wrote:
>
> current settings all default
> > > #checkpoint_segments = 3
> > > #checkpoint_timeout = 5min
> > > #checkpoint_completion_target = 0.5
> > > #checkpoint_warning = 30s
> > >
> >
> > this is
Hi all,
I need to know if multiple tables have (may have most probably) identical
content.
Since I want a fast solution (which means not comparing tables row by row),
I thought it would be a good idea to have an sql function operating on a table
or view
similar to md5sum on a file and only compa
Hi all -
I've setup a warm standby using WAL archiving and pg_standby and it
seems to be mostly working. I do have a few questions though:
Here's my archive_comand and restore_command for reference
(postgres_db_2 is a hosts entry in case you were wondering).
archive_command = 'scp -q %p postgre
64 matches
Mail list logo
