Hi,
Tom Allison schrieb:
On Sep 11, 2007, at 5:49 AM, Tom Allison wrote:
I was able get my database working again.
Never figured out why...
My database data (sorry about the redundancy there) is sitting on a
RAID1 array with LVM and ReiserFS.
I've heard some dissention about the use of
New to Pg and wondering the extent of PG's table partitioning
capability.
I have a huge table 18 million rows(growth rate ~8 million a week)
which I like to implement partitioning.
Initially the plan is to just partition it by date. eg: 1 partition per
month.
Now, I'm thinking if it's possible
Dear list,
If I create on my system (Postgresql 8.2.4) a new database it gets
automatically the schema public.
I would like to know what this schema is for?
On my system I have a couple of different users.
The owner of the public-schema is postgres.
Could it be Fatal to give all of my users
Hi there,
I learned in another posting that my table design - in a polite way -
could be improved.
So, before doing any additional design errors, I would like to get
feedback, if possible.
I am dealing with some 500 tables for worldwide national statistics
(GDP, population, environment
Ow Mun Heng wrote:
New to Pg and wondering the extent of PG's table partitioning
capability.
I have a huge table 18 million rows(growth rate ~8 million a week)
which I like to implement partitioning.
OK
Initially the plan is to just partition it by date. eg: 1 partition per
month.
Fair
Kai Behncke wrote:
Dear list,
If I create on my system (Postgresql 8.2.4) a new database it gets
automatically the schema public.
I would like to know what this schema is for?
On my system I have a couple of different users. The owner of the
public-schema is postgres.
By default everyone
Stefan Schwarzer wrote:
Hi there,
I learned in another posting that my table design - in a polite way -
could be improved.
So, before doing any additional design errors, I would like to get
feedback, if possible.
I am dealing with some 500 tables for worldwide national statistics
(GDP,
Stefan Schwarzer schrieb:
Hi there,
I learned in another posting that my table design - in a polite way -
could be improved.
So, before doing any additional design errors, I would like to get
feedback, if possible.
I am dealing with some 500 tables for worldwide national statistics
(GDP,
Kai Behncke wrote:
If I create on my system (Postgresql 8.2.4) a new database it
gets automatically the schema public.
I would like to know what this schema is for?
It is kind of a default schema. If you keep the standard
configuration, then every user can access objects in this schema
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/12/07 03:28, Stefan Schwarzer wrote:
[snip]
The new design would be like this:
id |year|value
---
1 | 1970| NULL
1 | 1971| 36
1
1 | 2005| 45
Thanks for the feedback and the suggestions.
A problem I have now when using the new design is the following:
As a result from my PostGres query I get something like this:
year|value |name
---
2001| 123 | Afghanistan
On Wed, 2007-09-12 at 09:42 +0100, Richard Huxton wrote:
Ow Mun Heng wrote:
New to Pg and wondering the extent of PG's table partitioning
capability.
I have a huge table 18 million rows(growth rate ~8 million a week)
which I like to implement partitioning.
OK
Initially the plan
Stefan Schwarzer wrote:
Thanks for the feedback and the suggestions.
A problem I have now when using the new design is the following:
As a result from my PostGres query I get something like this:
year|value |name
---
2001| 123
Ow Mun Heng wrote:
On Wed, 2007-09-12 at 09:42 +0100, Richard Huxton wrote:
Well, I suppose you could partition over (month,product) but you
probably don't want to. Sounds fiddly to manage.
Yeah.. well current schema w/ everything in 1 table and the way things
are ordered, normalised design
Hi,
Im a newbie to postgres. Can you explain what pgpool is ?
Thanks
On 11/09/2007, Phoenix Kiula [EMAIL PROTECTED] wrote:
The suggestion in this thread that a regex index will come into play
only when the WHERE condition specifically mentions it was indeed the
key for me.
Ok, I've hit a snag about this index. I think it's to do with how my
regex is
Hi
One of PgBouncer original design goals is to create small and robust
connection pooler.
If we start adding more and more fancy features like load balancing then we
can easily end up in the same place from which we wanted to get away
(pgPool).
We do our load balancing on ip level when the
Many, many thanks for that and the other advices from everybody.
You're great!
As I am neither expert in PHP, nor in Postgres, MySQL, Javascript,
HTML etc. etc., all I do in the forums is asking questions; and not
adding value by helping others (due to my limited knowledge). All I
can do
Stefan Schwarzer wrote:
Many, many thanks for that and the other advices from everybody. You're
great!
As I am neither expert in PHP, nor in Postgres, MySQL, Javascript, HTML
etc. etc., all I do in the forums is asking questions; and not adding
value by helping others (due to my limited
Phoenix Kiula wrote:
Ok, I've hit a snag about this index. I think it's to do with how my
regex is structured. Basically this column can have either IP
addresses, or alphanumeric user IDs. If it is not an IP address, it is
a registered user ID. What is the best way of ascertaining that a
column
Hello
We plProxy to split our database into partitions. See Kristo's blog's about
that at http://kaiv.wordpress.com/.
For replication we use Londiste in SkyTools package. SkyTools contains
several more scripts that are useful when buildin large and complex systems
running on large number of
I've been passively collecting data for a few months. I realized, after
a while, that I never added a timestamp column to the table the data is
being stored in. I've since added that.
Is there a way to find out when the previous rows were inserted? There
is a serial integer for the primary
am Wed, dem 12.09.2007, um 7:32:45 -0600 mailte Dennis Muhlestein folgendes:
I've been passively collecting data for a few months. I realized, after
a while, that I never added a timestamp column to the table the data is
being stored in. I've since added that.
Is there a way to find
On Sep 12, 2007, at 8:32 , Dennis Muhlestein wrote:
Is there a way to find out when the previous rows were inserted?
There is a serial integer for the primary key on the table.
Nope.
Michael Glaesemann
grzm seespotcode net
---(end of
am Wed, dem 12.09.2007, um 15:41:44 +0200 mailte A. Kretschmer folgendes:
am Wed, dem 12.09.2007, um 7:32:45 -0600 mailte Dennis Muhlestein folgendes:
I've been passively collecting data for a few months. I realized, after
a while, that I never added a timestamp column to the table the
Martijn van Oosterhout wrote:
avg(*) is not valid, same for sum(*)
Doh!
Thanks.
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
A. Kretschmer wrote:
am Wed, dem 12.09.2007, um 15:41:44 +0200 mailte A. Kretschmer folgendes:
am Wed, dem 12.09.2007, um 7:32:45 -0600 mailte Dennis Muhlestein folgendes:
I've been passively collecting data for a few months. I realized, after
a while, that I never added a timestamp column
Hi
I have the master-child tables as follows
Master table
create table foo(a numeric(10,0));
first child table foo1 as
create table foo1 (check(a=0 and a10)) inherits(foo)
second child table foo2 as
create table foo1 (check(a=10) inherits(foo)
Now I create this function to redirect the inserts
Josh Harrison [EMAIL PROTECTED] writes:
Now I create this function to redirect the inserts and updates on the
master table.
Uh ... there never will be any updates in the master table, since it
hasn't got any entries.
What you'd need is an insert trigger on the master and update triggers
on
Hi,
I'm using PostgreSQL 8.1.8 and am having trouble with a table which
contains a large amount of data. Data is constantly being inserted into
the table, roughly a million inserts per hour at peak. The table
currently has about 100 million entries which take up 14G of space (24G
with indices).
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Mike Charnoky wrote:
Hi,
At this point, I'm at a loss. I've searched the archives for similar
problems, but none of the suggestions have worked. Is the data in this
table corrupted? Why are both vacuum and reindex failing to complete?
Is
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction commit?
Here's an example of what I'm thinking of:
- I write a function (it doesn't matter what language it's in:
PL/pgSQL, PL/Java, etc)
- I register that function as a post-commit callback function
- when a
On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction commit?
There is the LISTEN/NOTIFY mechanism. It's not automatic but I think it
does most of what oyu want.
Have a nice day,
--
Martijn van
--- Jay Dickon Glanville [EMAIL PROTECTED] wrote:
Is it possible for PostgreSQL to notify me of a successful transaction commit?
I've haven't used it yet, but will LISTEN and NOTIFY work for you?
http://www.postgresql.org/docs/8.2/static/sql-listen.html
2007/9/12, Jay Dickon Glanville [EMAIL PROTECTED]:
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction commit?
Here's an example of what I'm thinking of:
- I write a function (it doesn't matter what language it's in:
PL/pgSQL, PL/Java, etc)
- I register that
On Sep 12, 2007, at 11:51 AM, Richard Broersma Jr wrote:
--- Jay Dickon Glanville [EMAIL PROTECTED] wrote:
Is it possible for PostgreSQL to notify me of a successful
transaction commit?
I've haven't used it yet, but will LISTEN and NOTIFY work for you?
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
At this point, I'm at a loss. I've searched the archives for similar
problems, but none of the suggestions have worked. Is the data in this
table corrupted? Why are both vacuum and reindex failing to complete?
Is
Thanks. Check this website that talk abt the approach that I had mentioned
http://blogs.ittoolbox.com//oracle/guide/archives/comparing-partitioned-tables-in-oracle-and-enterprisedbpostgresql-13261
I also tried your method (triggers on the child table) and it gives the same
error too.
Josh
On
On 9/12/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote:
On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction
commit?
There is the LISTEN/NOTIFY mechanism. It's not automatic but I
On Wed, 2007-09-12 at 13:10 -0400, Jay Dickon Glanville wrote:
On 9/12/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote:
On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction
commit?
Joshua D. Drake [EMAIL PROTECTED] writes:
At this point, you are in a world of hurt :). If you stop a vacuum you
have created a huge mess of dead rows in that table.
Only if it was a vacuum full, which he didn't mention having tried.
I'm kinda wondering whether the vacuum and reindex did
On 9/12/07, Tino Wildenhain [EMAIL PROTECTED] wrote:
Alternatively you could use XFS but I'm not sure if it performs
better on failing hardware. I guess not.
Actually I've seen anecdotal evidence that XFS with its
aggressive write-caching has caused data-loss in the
event of power-failures.
Jay Dickon Glanville wrote:
What I'd like to be able to do with this event is to notify any
applications of this change, so they can update their cached view of
the database.
So, is this possible? Or am I wishing for the sky? ;-)
You're wishing for these, I think:
If I create an ON UPDATE trigger run on each row after update, does
the trigger fire only on rows affected by the update or for all rows?
For example:
CREATE TRIGGER my_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE my_update_proc;
UPDATE my_table SET my_val =
Pavel Stehule wrote:
2007/9/12, Jay Dickon Glanville [EMAIL PROTECTED]:
- I write a function (it doesn't matter what language it's in:
PL/pgSQL, PL/Java, etc)
- I register that function as a post-commit callback function
- when a client commits a transaction, the function gets called, and
the
Hi,
Does pgpool II support foreign key constraints on partitions? If so, how?
Thanks
Hi,
I am not sure if this qualifies as a bug report or a feature request,
but I don't see any way to tell Postgresql that the members of a record
cannot be NULL. This causes all kinds of problems when this record
is used to declare the return type of a function. Suppose I had the
following
On 9/12/07, Cultural Sublimation [EMAIL PROTECTED] wrote:
Thanks for the help!
Not really following you, but try these:
CREATE OR REPLACE FUNCTION GET_MOVIES ()
RETURNS SETOF MOVIES
LANGUAGE SQL STABLE
AS
$$
SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES;
$$;
-- OR --
CREATE OR REPLACE FUNCTION
Why do you create an extra type for that?
Just have your method return movies
i.e.
CREATE FUNCTION get_movies ()
RETURNS SETOF movies
...
...
HTH
Uwe
On Wednesday 12 September 2007, Cultural Sublimation wrote:
Hi,
I am not sure if this qualifies as a bug report or a feature request,
but
I have never heard that stopping a vacuum is problematic... I have had
to do this many times in the past without any adverse affects. Is there
some sort of documentation which elaborates on this issue?
For the record, I did a VACUUM ANALYZE, not FULL. Now that I think
about it, I probably
Mike Charnoky wrote:
Alvaro: The cluster suggestion probably won't help in my case since data
in the table should already be naturally ordered by date.
It's not helpful only for reordering, but also for getting rid of dead
tuples.
--
Alvaro Herrera
Why do you create an extra type for that?
Just have your method return movies
Hi,
Thanks for the answer. The simple example obfuscates the fact that in reality
the table has a few extra columns that are omitted from get_movies_t.
Therefore, I cannot return movies.
However, your answer did
On 13/09/2007, Alvaro Herrera [EMAIL PROTECTED] wrote:
Mike Charnoky wrote:
Alvaro: The cluster suggestion probably won't help in my case since data
in the table should already be naturally ordered by date.
It's not helpful only for reordering, but also for getting rid of dead
tuples.
Phoenix Kiula escribió:
On 13/09/2007, Alvaro Herrera [EMAIL PROTECTED] wrote:
Mike Charnoky wrote:
Alvaro: The cluster suggestion probably won't help in my case since data
in the table should already be naturally ordered by date.
It's not helpful only for reordering, but also for
Phoenix Kiula [EMAIL PROTECTED] writes:
Apart from creating a new table, indexing it, then renaming it to
original table -- is there an alternative to CLUSTER that doesn't
impose a painful ACCESS EXCLUSIVE lock on the table? We are on
Postgres 8.2.3 and have a heavy duty table that starts
Hi again,
However, your answer did give me an idea: instead of declaring
get_movies_t as a record, I declare it as dummy table, and return
that (see code at the end).
This works, though it is *very* ugly. Any other ideas?
My apologies, but it turns out that this solution doesn't work after
I haven't tried it with a view yet - so this may or may not work. But try
giving it a shot by declaring a view
create view vmovies as
select movie_id,movie_text from movies
and let your function return setof vmovies
Maybe that works - I think it should.
Uwe
On Wednesday 12 September 2007,
Cultural Sublimation escreveu:
Hi,
I am not sure if this qualifies as a bug report or a feature request,
but I don't see any way to tell Postgresql that the members of a record
cannot be NULL. This causes all kinds of problems when this record
is used to declare the return type of a function.
CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$
Hey,
Thanks for the suggestion. Unfortunately, it still doesn't work.
Here is what Postgresql is telling the
Greg Sabino Mullane wrote:
...in favor of renaming the database Horizontica.
...should definitely be HorizonticaSQL
Surely that should be capitalized HorizonticASQL, no.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
I haven't tried it with a view yet - so this may or may not work. But try
giving it a shot by declaring a view
create view vmovies as
select movie_id,movie_text from movies
and let your function return setof vmovies
Maybe that works - I think it should.
Hey,
Thanks for the help.
On 9/12/07, Ron Mayer [EMAIL PROTECTED] wrote:
Greg Sabino Mullane wrote:
...in favor of renaming the database Horizontica.
...should definitely be HorizonticaSQL
Surely that should be capitalized HorizonticASQL, no.
I can just see it now.
I've been using Horizont and
followed by
--- Cultural Sublimation [EMAIL PROTECTED] wrote:
CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$
SELECT movie_id, movie_name FROM get_movies ();
=
[EMAIL PROTECTED] (Jay Dickon Glanville) writes:
On 9/12/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote:
On Wed, Sep 12, 2007 at 12:36:22PM -0400, Jay Dickon Glanville wrote:
Hello all.
Is it possible for PostgreSQL to notify me of a successful transaction
commit?
There is the
I don't know if this will work, but here is another idea:
SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
Hi,
Nope. That's not even valid syntax...
But thanks for effort, anyway!
Cheers,
C.S.
--- Cultural Sublimation [EMAIL PROTECTED] wrote:
I don't know if this will work, but here is another idea:
SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
Hi,
Nope. That's not even valid syntax...
It isn't valid SQL spec syntax but it is
Cultural Sublimation [EMAIL PROTECTED] writes:
This bug seems to obvious to have been generally missed.
It's not a bug: there is no mechanism enforcing that the result of a
function can't be NULL.
For functions returning scalars you can get the effect by declaring the
result as being of a
Richard Broersma Jr wrote:
--- Cultural Sublimation [EMAIL PROTECTED] wrote:
I don't know if this will work, but here is another idea:
SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
Hi,
Nope. That's not even valid syntax...
On Wed, Sep 12, 2007 at 02:22:46PM -0700, Cultural Sublimation wrote:
SELECT movie_id, movie_name FROM get_movies ();
= returns a SETOF of (int4 NULL, text NULL)
I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:
One note: I know this
On Wednesday 12 September 2007 15:56:13 Josh Trutwin wrote:
If I create an ON UPDATE trigger run on each row after update, does
the trigger fire only on rows affected by the update or for all rows?
For example:
CREATE TRIGGER my_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
On Wednesday 12 September 2007 09:34:55 Richard Huxton wrote:
To be honest, I'd probably just have a separate column uid_type, set
it when creating the user and then just have a partial index WHERE
uid_type='IP'
Or have a separate column with the user ID and have (anonymous) when you
have
Hi Tom,
In any case, it appears to me that your gripe has little to do with
whether there's actually any enforcement of the not-null condition,
and much to do with whether some unspecified client-side software
thinks the query result column is guaranteed not null. Most likely
you're going
Hi,
I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:
Yeah, that is the correct semantics: it can be NULL. It does make
a world of difference on the client side, because an int4 never NULL
is a different type from int4 possibly NULL.
On 9/9/07, Scott Marlowe [EMAIL PROTECTED] wrote:
Hey! Some of us just figured out how to pronounce PostgreSQL properly.
Postgreh SeeQuell??
/me ducks
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
Cultural Sublimation wrote:
Hi,
I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:
Yeah, that is the correct semantics: it can be NULL. It does make
a world of difference on the client side, because an int4 never NULL
is a
howto check column than row??
create table horizontal (id integer, first varchar(8), last varchar(8));
insert into horizontal values ('1', 'Jack', 'Ja');
insert into horizontal values ('2', 'Jill', 'Ji');
insert into horizontal values ('3', 'Mary', 'Ma');
select * from horizontal where true;
Interesting. What language are you using?
OCaml. The type-safety comes from the PG'OCaml bindings, which basically
check if the types in the database are consistent with the types in the
program. It's very neat technology, but unfortunately sometimes it seems
too advanced for its own good,
Does anyone know where I could find a tool which allows importing schema
information from a postgres database into visio? The boss guys want some
pretty pictures...
Andrew
On Wed, Sep 12, 2007 at 04:32:22PM -0700, Andrew Hammond wrote:
Does anyone know where I could find a tool which allows importing
schema information from a postgres database into visio? The boss
guys want some pretty pictures...
I'm pretty sure VisioPro has a way to attach to databases via
On Sep 12, 2007, at 4:32 PM, Andrew Hammond wrote:
Does anyone know where I could find a tool which allows importing
schema information from a postgres database into visio? The boss
guys want some pretty pictures...
Visio has that built-in for a long time - point it at the database
via
On 13/09/2007, Tom Lane [EMAIL PROTECTED] wrote:
Phoenix Kiula [EMAIL PROTECTED] writes:
Apart from creating a new table, indexing it, then renaming it to
original table -- is there an alternative to CLUSTER that doesn't
impose a painful ACCESS EXCLUSIVE lock on the table? We are on
Helllo,
you can also look at Case Studio, this software have a nice reverse
engineering functionnality!
Good luck
Bruno Lavoie
Andrew Hammond a écrit :
Does anyone know where I could find a tool which allows importing
schema information from a postgres database into visio? The boss guys
Cultural Sublimation [EMAIL PROTECTED] writes:
Interesting. What language are you using?
OCaml. The type-safety comes from the PG'OCaml bindings, which basically
check if the types in the database are consistent with the types in the
program.
Unfortunately for you, they are not different
On Wed, 12 Sep 2007 23:32:24 -0400
Bruno Lavoie [EMAIL PROTECTED] wrote:
Helllo,
you can also look at Case Studio, this software have a nice reverse
engineering functionnality!
Good luck
Bruno Lavoie
Andrew Hammond a écrit :
Does anyone know where I could find a tool which allows
On Mon, 2007-09-10 at 13:00 -0600, RC Gobeille wrote:
Or this one:
http://schemaspy.sourceforge.net/
Can't seem to get it to connect to PG using the example.
java -jar schemaSpy_3.1.1.jar -t pgsql -u operator -p operator -o
test_db -host localhost -db test_db
Using database properties:
On Wed, 12 Sep 2007 23:32:24 -0400
Bruno Lavoie [EMAIL PROTECTED] wrote:
you can also look at Case Studio, this software have a nice reverse
engineering functionnality!
hear hear - great little tool
_
{Beto|Norberto|Numard} Meijome
A tyrant...is always stirring up
86 matches
Mail list logo
