after an attempt at stress-testing my app i started seeing some
corrupted-looking entries in the postgresql query log. for example:
...
2006-10-10 21:33:24 PDT [31175]: [509-1] LOG: statem2006-10-10 21:33:24
PDT [31166]: [4206-1] LOG: duration: 0.197 ms
ent: EXECUTE unnamed [PREPARE: SELECT
Is there a utility that could update/merge functions/views from a postgresql dump to an existing db?
On Wed, Oct 11, 2006 at 12:26:19PM +0530, Indira Muthuswamy wrote:
Hai,
Can anyone of you help me in finding the datatype of a particular
column in a table in Postgres?
Indira,
You'll want to look at the columns view in the information_schema.
Thanks for you detail mail on this. We will look into 8.1.4 and start using
it.
Meanwhile, we would like to know about this error.
When I start PostgreSQL service, the below error message is displayed and
finally service didn't started.
The PostgreSQL Database Server 8.0 service of a local
I think you should have a look at the ltree contrib package. It
implements tree-like structures, and probably makes your problem much
simpler.
I have never used contrib modules with PostgreSQL - are they easy to
handle - I mean, especially upon upgrade, etc.
---(end
Hey guys, I would like to know a good way of updating or appending the schema names in the search path, Right now I do a show search_path, get the names of all the schemas, and then set search path; adding the new schema. I'm in the process of integrating a few systems and will need to
On Tue, 2006-10-10 at 14:50 -0400, Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
* MySQL is used as a primary development platform.
Another good reason.
Actually that's *the* reason --- it's always going to be hard for
Postgres to look good for an application that's been
Hi All,
May be someone can help me with the following problem:
1. I need to extend 'featurs' of database user account.
2. I did that by creating a table:
CREATE TABLE users (username text, -- key matching 'current_user'
freaturs text -- thing I need
);
3. I allow acces to that table
On Oct 11, 2006, at 03:34 , Jim C. Nasby wrote:
And increase estimated_cache_size to something close
to how much memory you have.
That would be effective_cache_size.
Alexander.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hello,
On Tue, 2006-10-10 at 11:30 +0200, Ludwig Kniprath wrote:
/usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1: libgeos_c.so.1: cannot
open shared object file: No such file or directory
Did you run make install agains geos? Is the path that libgeos_c.so.1
lives in ldconfig path?
Regards,
--
Jan Cruz [EMAIL PROTECTED] writes:
Is there a utility that could update/merge functions/views from a postgresql
dump to an existing db?
I remember seeing something about a 'diff'... Something like 'pgdiff'...
With it you could compare and generate a script that went from one situation
to the
am Wed, dem 11.10.2006, um 7:37:11 -0300 mailte Jorge Godoy folgendes:
Jan Cruz [EMAIL PROTECTED] writes:
Is there a utility that could update/merge functions/views from a postgresql
dump to an existing db?
I remember seeing something about a 'diff'... Something like 'pgdiff'...
With
Is it by file name or by inode?
Brad.
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Wed, 2006-10-11 at 09:47 -0400, Brad Nicholson wrote:
Is it by file name or by inode?
Filename
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Hi all I have the following query that sources two tables:
select pi2.* from allpoints2 a1
inner join prem_info pi2 on pi2.prem = a1.prem AND the_geom is null
AND pi2.multiplier 1
where route in
(select route from prem_info pi
inner join allpoints2 a on a.prem = pi.prem
where feederid =
George Pavlov [EMAIL PROTECTED] writes:
after an attempt at stress-testing my app i started seeing some
corrupted-looking entries in the postgresql query log. for example:
It looks like you're getting messages interspersed in the log, ie,
single messages aren't written indivisibly. This
Alvin [EMAIL PROTECTED] writes:
Hey guys, I would like to know a good way of updating or appending th=
e schema names in the search path,
Perhaps like this:
regression=# create schema news;
CREATE SCHEMA
regression=# show search_path;
search_path
$user,public
(1 row)
Taking a second look. I see a few places I made a mistake. :-(
CREATE VIEW your_view AS
^
SELECT N.first_name,
N.last_name,
A.street,
A.city,
W.hour,
FROM person P
LEFT JOIN name N ON (P.namid = N.id)
Rafal Pietrak [EMAIL PROTECTED] writes:
5. one of the procedures is:
CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE
MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset
role; return true; END $$ LANGUAGE plpgsql STABLE;
You should use the SECURITY DEFINER property
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
bar.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
Pgsql 8.1.4.
I want return custom type from function as row, not as values in brackets (1,2).
I have following type and function:
CREATE TYPE new_item_return_type AS
(item_id bigint,
last_update timestamp without time zone);
CREATE OR REPLACE FUNCTION new_item( new_title int8,
Joe Kramer [EMAIL PROTECTED] schrieb:
I want to get:
item_id | last_update
-
32 | 1234-12-12 12:12:12
Untested:
SELECT item_id, last_update from public.new_item(3,2);
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will
On 10/11/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I think you should have a look at the ltree contrib package. It
implements tree-like structures, and probably makes your problem much
simpler.
I have never used contrib modules with PostgreSQL - are they easy to
handle - I mean,
Andrew Sullivan wrote:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
bar.
The Mantis bug
Andreas Kretschmer [EMAIL PROTECTED] writes:
Joe Kramer [EMAIL PROTECTED] schrieb:
I want to get:
item_id | last_update
-
32 | 1234-12-12 12:12:12
Untested:
SELECT item_id, last_update from public.new_item(3,2);
Or just
SELECT *
Andrew Sullivan wrote:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
bar.
Command Prompt
Guy Rouillier wrote:
Andrew Sullivan wrote:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer [EMAIL PROTECTED] writes:
Joe Kramer [EMAIL PROTECTED] schrieb:
I want to get:
item_id | last_update
-
32 | 1234-12-12 12:12:12
Untested:
Karen Hill wrote:
What is the best way to find out the total number of rows returned by
an refcursor? This would allow the client user to know the total
amount of rows as they are using FETCH FORWARD/BACKWARD.
For example let's say that an refcursor has 300 rows. The user fetches
20 at a
On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer [EMAIL PROTECTED] writes:
Joe Kramer [EMAIL PROTECTED] schrieb:
I want to get:
item_id | last_update
Karen Hill [EMAIL PROTECTED] writes:
-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count?
If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE
BACKWARD
Tom Lane wrote:
Karen Hill [EMAIL PROTECTED] writes:
-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count?
If you want an accurate count, the only way is to traverse the cursor.
Consider using MOVE FORWARD ALL and noting the
Hi,I'm trying to start my postgres server using# pg_ctl start -D /usr/local/pgsql/abcand get an error like:invalid data in PID file /usr/local/pgsql/abc/postmaster.pid
can anyone comment whats wrong.My postmaster.pid file is empty at the time when i run this command.Regards,Jas
First, thanks for all the feedback. After spending some more time
evaluating what we would gain by using slony I'm not sure it's worth
it. However I thought I would get some more feedback before
finalizing that decision.
The primary reason for looking at replication was to move cpu
intensive
On Wed, 2006-10-11 at 10:30 -0400, Tom Lane wrote:
1. I used the STABLE keyword to tell executor to evaluate the function
just once per statement.
Wrong. STABLE is not a directive to the system, it is a promise about
the behavior of your function ... and you're trying to break the
On Wed, Oct 11, 2006 at 02:50:59PM -0400, J S B wrote:
I'm trying to start my postgres server using
# pg_ctl start -D /usr/local/pgsql/abc
and get an error like:
invalid data in PID file /usr/local/pgsql/abc/postmaster.pid
can anyone comment whats wrong.
My postmaster.pid file is
Sorry wrong list, this was meant for the slony list...
Chris
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
i never touched it. when i opened it to check the stuff inside, found it empty.What should i do now?On 10/11/06, Martijn van Oosterhout
kleptog@svana.org wrote:On Wed, Oct 11, 2006 at 02:50:59PM -0400, J S B wrote:
I'm trying to start my postgres server using # pg_ctl start -D
I deleted postmaster.pid file and got rid of the previous error.Now its a new error which say something as follows after i try starting my postgres server usring pg_ctl start -D /usr/local/pgsql/jsbali :-postmaster starting
LOG: could not bind IPv4 socket: Address already in useHINT: Is another
J S B [EMAIL PROTECTED] writes:
FATAL: could not open lock file /tmp/.s.PGSQL.5432.lock: Permission
denied
Can you please tell me what is this all about?
It looks to me like you have, or had, another postmaster running under a
different userid. Perhaps you should back up to the beginning
Tom Lane wrote:
J S B [EMAIL PROTECTED] writes:
FATAL: could not open lock file /tmp/.s.PGSQL.5432.lock: Permission
denied
Can you please tell me what is this all about?
It looks to me like you have, or had, another postmaster running under a
different userid. Perhaps you should back up
Actually i've been trying to setup my postgres database in a new machine.the machine that i'm using right now is goin to die very soon. So i was trying to restore it in another machine but before that I have to install postgres in this new machine.
I installed postgres successfully, created a DB
J S B [EMAIL PROTECTED] writes:
There was some problem with postmaster.pid file, so i just removed it.
Without bothering to stop the old postmaster first, I take it.
At this point your best bet is to find out the PID of the old postmaster
with ps and then kill it with kill -INT pid. I suspect
Well, I've been very particular with the user id. There's just one single user ID am working on my postgres with.when i start the postgres, it creates the follwoing processes for postgres:1 S jsbali 4034 4033 0 76 0 - 2489 - 15:41 pts/2 00:00:00 postgres: logger process
1 S jsbali 4036 4033 0 75 0
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
On 10/11/06, Uwe C. Schroeder [EMAIL PROTECTED] wrote:
Well, SELECT * is only evil if your application relies on a specific column
order to function. The moment you change the table layout and you're using
select * your application will cease functioning.
My app uses tons of select *, but then I
J S B [EMAIL PROTECTED] writes:
Then i try to start postgres again and it gives me the same error:
LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
Um, have you tried waiting a bit as the
On Tue, 2006-10-10 at 04:16, Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/09/06 22:43, Jonathan Greenberg wrote:
So I've been looking at the documentation for COPY, and I'm curious about a
number of features which do not appear to be included, and whether these
I made a HUGE mistake, and used 'UK' as the abbreviation for the
united kingdom ( the ISO abbv is 'GB' )
I've got a database where 8 tables have an FKEY on a table
'location_country' , using the text 'uk' as the value -- so i've got
9 tables that I need to swap data out on
can anyone
On Wed, 2006-10-11 at 11:05 -0700, Uwe C. Schroeder wrote:
On Wednesday 11 October 2006 10:42, A. Kretschmer wrote:
am Wed, dem 11.10.2006, um 12:56:51 -0400 mailte Tom Lane folgendes:
Andreas Kretschmer [EMAIL PROTECTED] writes:
Joe Kramer [EMAIL PROTECTED] schrieb:
I want to get:
On Oct 11, 2006, at 2:55 PM, snacktime wrote:
So by putting all the data into one schema, every report query now
gets run against a million or more rows instead of just a few hundred
or thousand. So all clients will see a drop in query performance
instead of just the clients with large
Devrim GUNDUZ schrieb:
Hello,
On Tue, 2006-10-10 at 11:30 +0200, Ludwig Kniprath wrote:
/usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1: libgeos_c.so.1: cannot
open shared object file: No such file or directory
Did you run make install agains geos? Is the path that libgeos_c.so.1
On Thu, 2006-10-12 at 01:36 +0530, Merlin Moncure wrote:
On 10/11/06, Uwe C. Schroeder [EMAIL PROTECTED] wrote:
Well, SELECT * is only evil if your application relies on a specific
column
order to function. The moment you change the table layout and you're using
select * your application
I made a HUGE mistake, and used 'UK' as the abbreviation for the
united kingdom ( the ISO abbv is 'GB' )
I've got a database where 8 tables have an FKEY on a table
'location_country' , using the text 'uk' as the value -- so i've got
9 tables that I need to swap data out on
can
I got the error mesage,
ERROR: could not find relation 19693 among query result relations,
from a strange interaction between inser, foreign keys and a udf.
After a but of trial and error, I came up with a test case:
create or replace function explode_array(in_array anyarray) returns
setof
On Wed, Oct 11, 2006 at 02:08:03PM -0700, Jeff Davis wrote:
select q.*, bar from
(
select a, b,c from foo
) q;
What is bar?
XMIN, for example
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---(end of
On 10/12/06, Jeff Davis [EMAIL PROTECTED] wrote:
assumed column ordering is the real enemy. Here is another place
where select * is imo better style than non select *:
select q.*, bar from
(
select a, b,c from foo
) q;
What is bar?
bar is somthing else, a constant, field from
create table fp1(a text, b text, primary key(a,b));
create table foo (a text, b numeric, c text);
insert into foo select 'a', 1, explode_array(string_to_array('a,b',
',')); -- works
truncate foo;
alter table foo add foreign key(a,b) references fp1(a,b) on delete cascade;
insert into foo
HiI want to be able to loop through an array. The code I have used in the past is: FOR i IN array_lower(arrayvar,1) .. array_upper(arrayvar,1) LOOP currentvalue:=arrayvar[1][i]; RAISE NOTICE '%', currentvalue; END LOOP;But now I need to loop through the whole array, not just the one element. I
Martijn van Oosterhout wrote:
On Fri, Oct 06, 2006 at 12:44:43PM +0300, Martins Mihailovs wrote:
I would be a glad to hear your solutions, experience in web application
with multi languages (searching with indexing, sorting and others
problems with multi byte encoding).
For developers: what
In the document Transaction Processing in PostgreSQL
( http://www.postgresql.org/files/developer/transactions.pdf )
I read :
Postgres transactions are only guaranteed atomic if a disk page write
is an atomic action. On most modern hard drives that's true if a page
is a physical sector, but most
Mabye I made myself not clear enough- sorry for that...What I want is having a statement like:PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))BEGIN ---check if something is valid ---compute something ---store values I got via THIS query and put them in table A, B and C
Please, provide a list of OS in which PostgreSQL have a native 64bit
support.
Thanks!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I am porting PostgreSQL 8.1.4 to Embedded Linux 2.x.
Cross-compiling had little problem with zic (timezone),
but I changed cross-compiled zic to native-compiled one so I can
cross-compile correctly I think.
However, executing postgresql on my ebmedded linux has problem with initdb.
(root accout
Hi List !
I need to add a column to a view, which would contain an automatically
generated sequence.
An automatic row numbering would do the trick (I only need unique
numbers, that's all), but I don't even know how to achieve this.
Does anybody have a solution for this problem ?
Thanks a lot
Thank you, But there seems to be syntax errors. Could you help?
Richard Broersma Jr wrote:
table name
{
id integer primary key
first_name text,
middle_name text,
last_name text
}
table address
{
id integer primary key
number int,
street text,
Hello,
I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.
First of all, ZABBIX supports three database engines: MySQL, Oracle and
PostgreSQL. It uses absolutely standard SQL, same for all three
database engines. We have absolutely no intention to
Hi.
Are there any plans to implement CUBE, ROLLUP and/or GROUPING SETS in future
PostgreSQL versions?
I could not find any info on the TODO-page.
/stig
---(end of broadcast)---
TIP 4: Have you searched our list archives?
PostgreSQL doesn't really have any unstable versions (unless you're
talking about code right out of CVS). The closest you might come will
be the initial release of a major version, or of course a beta/
release candidate. (Note that the first dot indicates a major
version for PostgreSQL. 8.0
Steve Crawford schrieb:
Guy Rouillier wrote:
Andrew Sullivan wrote:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 16:10, Richard Broersma Jr wrote:
I made a HUGE mistake, and used 'UK' as the abbreviation for the
united kingdom ( the ISO abbv is 'GB' )
I've got a database where 8 tables have an FKEY on a table
'location_country' , using the
What PG version is this, on what operating system? Do you have
redirect_stderr enabled?
Sorry, I should have included that:
PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)
And yes, redirect_stderr = on. I have no definitive way of
Well, that is hardly surprising. What exactly is your point?
If you want to write portable software, you usually stay with generally
available, standardized features or API's, be it database independent,
platform independent, you name it. You certainly don't go for
user-defined types. I
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 14:48, Chris Browne wrote:
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
[snip]
Oh, and a cluster of IBM p570s would probably be enough to run a 20
user system :-(. [Actually,
can anyone suggest a non-nightmarish way for me to do this ?
If your tables are setup to ON UPDATE CASCASE then you are fine.
Just updated the main table and PostgreSQL will take care of the rest.
I doesn't appear that ALTER TABLE can change constraint characteristics.
You'd have to
[EMAIL PROTECTED] writes:
In the document Transaction Processing in PostgreSQL
( http://www.postgresql.org/files/developer/transactions.pdf )
That's very, very old information.
I read :
Postgres transactions are only guaranteed atomic if a disk page write
is an atomic action.
Not true
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 18:53, Richard Broersma Jr wrote:
can anyone suggest a non-nightmarish way for me to do this
?
If your tables are setup to ON UPDATE CASCASE then you are
fine. Just updated the main table and PostgreSQL will take
care of the rest.
Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 14:48, Chris Browne wrote:
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
[snip]
Oh, and a cluster of IBM p570s would probably be enough to run a 20
user
I doesn't appear that ALTER TABLE can change constraint
characteristics. You'd have to drop/recreate, no?
Now that you mention it, I've never tried it or seen it done.
Here I what I came up with:
[snip]
It is nice to see things work so well. :-)
It would be interesting to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 19:15, Richard Broersma Jr wrote:
I doesn't appear that ALTER TABLE can change constraint
characteristics. You'd have to drop/recreate, no?
Now that you mention it, I've never tried it or seen it done.
Here I what I came up with:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 19:10, Geoffrey wrote:
Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 14:48, Chris Browne wrote:
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
On 11 Oct 2006 07:54:52 -0700, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
Hello,
I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.
just so you know, I brought this up after taking a look at the zabbix
software, which is in my opinion very
On 10/10/06, adam lawrence [EMAIL PROTECTED] wrote:
But now I need to loop through the whole array, not just the one element. I
want to do something like:
FOR j in LOOP
FOR i IN array_lower(arrayvar,1) .. array_upper(arrayvar,1) LOOP
currentvalue:=arrayvar[j][i];
RAISE NOTICE
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
Unfortunately PostgreSQL performs much slower than MySQL doing large
number of updates for one single table. By its nature ZABBIX requires
to execute hundreds of updates per second for large installations.
PostgreSQL cannot handle this nicely.
If
On 10/9/06, Christian Kasprowicz [EMAIL PROTECTED] wrote:
PROCEDURE MyProcedure(Value1 int, Value2 text, Value3 varchar(30))
BEGIN
---check if something is valid
---compute something
---store values I got via THIS query and put them in table A, B and C
---see wether everything is ok
1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute update test set aaa=1 where id=0; in an endless loop
I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
am Tue, dem 10.10.2006, um 16:33:03 +0200 mailte Arnaud Lesauvage folgendes:
Hi List !
I need to add a column to a view, which would contain an automatically
generated sequence.
An automatic row numbering would do the trick (I only need unique
numbers, that's all), but I don't even know
I have a need to create some statistical functions in PostgreSQL that
will take multiple column arguments and return multiple values.
A good example would be a least squares fit of a line through a set of
(x,y) points. In this case, I would want the function to accept two
columns of data
I have a need to create some statistical functions in PostgreSQL that
will take multiple
column arguments and return multiple values.
A good example would be a least squares fit of a line through a set
of (x,y) points.
In this case, I would want the function to accept two columns of
data
89 matches
Mail list logo
