Hello,
My database has grown far faster then expected and a query which used to
run acceptably now does not. I'm trying to figure out a way to make
this operate faster and scale better. I'm very open to the idea that
this does not need to be done using a SQL query at all - right now I'm
On Mon, 2005-03-14 at 21:14, Net Virtual Mailing Lists wrote:
On Mon, 2005-03-14 at 06:11, Net Virtual Mailing Lists wrote:
Hello,
I am sorry to bring this up again Does anyone have any idea what
might be going on here?... I'm very worried about this situation.. ;-(
It looks
Hello,
I am sorry to bring this up again Does anyone have any idea what
might be going on here?... I'm very worried about this situation.. ;-(
- Greg
Something even more peculiar (at least it seems to me..)...
If I drop the index table1_category_gist_idx, I get this:
jobs= explain
On Mon, 2005-03-14 at 06:11, Net Virtual Mailing Lists wrote:
Hello,
I am sorry to bring this up again Does anyone have any idea what
might be going on here?... I'm very worried about this situation.. ;-(
It looks to me like either you're not analyzing often enough, or your
I have a rather peculiar performance observation and would welcome any
feedback on this.
First off, the main table (well, part of it.. it is quite large..):
Table table1
Column | Type |
Something even more peculiar (at least it seems to me..)...
If I drop the index table1_category_gist_idx, I get this:
jobs= explain analyze select id from table1 where category @ 'a.b'
ORDER BY category;
QUERY PLAN
I have the following three tables and my inserts are blocking each other
in a way I just can't understand Can someone point me in the
direction as to what is causing this?
jobs= \d master.locations
Table master.locations
Column|
Net Virtual Mailing Lists wrote:
All I did was added an extra column to my table (I called it
batch_process). Then in
the trigger do something like (in whichever function you are calling):
IF NEW.batch_update IS NOT NULL AND NEW.batch_update = ''t'' THEN
NEW.batch_process := NULL
It is the only known way to control triggers though it isn't regularly
tested by the developers.
I think I've come up with another way.. I posted this recently, but did
not get any feedback on it so I'm not sure how dumb it is... It is
working really great for me though
All I did was
Hello,
I have a question about inherited tables w/r to triggers... Hopefully
this will make some sense... (I'll try to keep the schema example as
simple as possible):
Given the follow schema/tables:
-- public stuff
SET search_path = public, pg_catalog;
CREATE TABLE customer(
customer_id
Hello,
Sorry, I forgot the trigger:
-- public stuff
SET search_path = public, pg_catalog;
CREATE TABLE customer(
customer_id SERIAL,
customer_notification INTEGER,
CONSTRAINT customer_notification CHECK notification = 0) OR
(notification = 1)) OR (notification = 2))),
);
CREATE
Hello,
I'm using ltree but I have a requirement to use the - character in the
text of a node. Can I just change (in ltree.h) the following line:
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_')
to:
#define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_' ||
(x)
For some reason, I feel as though I have asked this before but I can't
find it anywhere. I hope it is not repetitive!
I have various triggers and rules in my database, mostly for keeping
tsearch2 updated and (now) materialized views. I'd say probably 90% of
the updates to my database do not
My only suggestion:
I don't care what you do with the newsgroups, just don't screw with the
mailing lists. If the mailing lists go away, I will be *EXTREMELY*
disappointed!
- Greg
Hopefully someone like Russ will tell us the correct term for domains like
microsoft.* and gnu.*. Those on the
For what its worth, I vote no.
I like the mailing lists. If having a newsgroup is beneficial, I say go
ahead and start one, but don't mess around with the mailing lists,
please. I really like the one or two digests I get in my mailbox everyday.
- Greg
---(end of
If you mean that the mailing list will stay in-tact with no connection to
Usenet I'm all for that Why not let everyone use whichever works best
for them and if one of them fades away over time, so be it
I happen to like the mailing list and dislike the news groups, perhaps
I'm just too
I am in the middle of a project to convert non-schema databases to a
schema-based system. The main reason I am doing it is because I need to
do a join on tables between databases, which can only be done with an
contrib module which does not have all the features one might want
(such as use of
Hello,
First, let me apologize for my flurry of emails as of late... I'm working
on something which seems particularly difficult (at least to me)...
My question is regarding creating an index on a view, or perhaps another
way to accomplish this. For example:
CREATE TABLE table1 (
table1_id
it!
- Greg
Net Virtual Mailing Lists wrote:
My question is regarding creating an index on a view, or perhaps
another way to accomplish this.
Views are just macro expansions of queries (in a manner of speaking).
To make queries on views use indexes, you create the indexes on the
underlying tables
Actually this database has been carried forward since the postgres95
days, so you are definitely right in your analysis..
Would another (perhaps safer?) way of doing this is to remove the CREATE
SEQUENCE and SELECT pg_catalog.setval, and replace the DEFAULT
nextval with SERIAL then restore
Hello,
I have a table with a timestamp column and I want to set this to a value
of now() - a random number of days between 0 and 45 for each row... I've
tried to do this a bunch of different ways and can't figure it out...
Here is my latest version:
update sometable set entered_dt = now() -
Try:
select * from event where game_clock IS NULL
- Greg
Occasionally I want to store a null value for my java.sql.Time-- Time
column in Postgresql.
update event set game_clock=null where event_id=1;
I can retreive the record with the null value (type Time) if I select
on the primary key,
Err, I just read my latest digest and saw the solution:
update datafrenzy.jobdata set entered_dt= now() - CAST(round(random()*45)
|| ' days' AS interval);
- Greg
Hello,
I have a table with a timestamp column and I want to set this to a value
of now() - a random number of days between 0 and 45
Hello,
When I do a pgdump --schema=someschema somedatabase something.dump,
the results of the dump file look like this:
REVOKE ALL ON SCHEMA someschema FROM PUBLIC;
GRANT ALL ON SCHEMA someschema TO PUBLIC;
SET search_path = someschema, pg_catalog;
CREATE SEQUENCE
testschema.industries_industry_id_seq not existing, yet I can execute
nextval against that very schema Any idea what might be going
wrong here?...
Thanks!
- Greg
On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:
If I want to take an existing table and add it into a schema
See comments below..
On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:
I am having one problem I just can't figure out In my dump file I
have something like:
CREATE SEQUENCE testschema.industries_industry_id_seq
INCREMENT BY 1
NO MAXVALUE
I've been spending the last few days converting many databases into a
single schema and have completed the process, but now I'm at somewhat of
an impasse as to the best way to proceed forward
It is important for me to explain that each of these databases has a
rather different structure,
Hello,
I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it. Is there some way to do a
join between databases or some other way of making the data in one
-0700, Net Virtual Mailing Lists wrote:
I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it. Is there some way to do a
join between databases or some other way
. This isn't necessarily a big deal because
several people have a separate mailing list address and/or have spam
prevention in place.
Wow this turned into a bigger message then I intended :-)
On Wed, 10 Nov 2004 16:03:48 -0700, Net Virtual Mailing Lists
[EMAIL PROTECTED] wrote:
Yeah.. I'm
Yeah.. I'm with you.. I don't really know what all of this is about - I
like the way the Postgres mailing list works as it is Are any of the
changes being discussed here going to change the content or how we
receive the mailing lists?..
What exactly is the problem they thing exists anyways?..
Yeah.. I'm with you.. I don't really know what all of this is about - I
like the way the Postgres mailing list works as it is Are any of the
changes being discussed here going to change the content or how we
receive the mailing lists?..
.. The only change I've noticed is that in all the time
PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
Net Virtual Mailing Lists
Gesendet: Samstag, 6. November 2004 16:49
An: Matteo Beccati
Betreff: Re: [GENERAL] Can this be indexed?
I am not clear how to use a trigger for this, I will need to
look into that
It is my understanding
Is there a way to create an index that would make this query be efficient
and not perform a sequential scan?
SELECT count(*) AS count,id FROM sometable GROUP BY id;
.. I've considered creating a rule on this table which would put the
results of this into another table anytime it is updated, but
I am not clear how to use a trigger for this, I will need to look into
that
It is my understanding that Postgres does not have materialized views
though (which I believe would solve this problem nicely) - am I mistaken?...
- Greg
Net Virtual Mailing Lists wrote:
Is there a way
Hello,
I have a table with a rule that goes something like this:
CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE
Hello,
Lets say I have data like this:
value1|value2|value3|value4||value(N)
--|--|--|--||
100 | 200 | 300 | 400 ||
10| 20 | | 40 ||
| 15 | | 16 ||
5 | | | ||
Now I want to sort these based on
I couldn't agree more!.. I've been watching the list every day just to
see what is going to be said next
Good stuff!
- Greg
BTW, I think this thread is really interesting -- certainly more
informative than a rehash of the usual processes vs. threads debate.
-Neil
Somehow I missed the ltree[] array stuff - this solves fairly nicely at
least the second part of my problem, but I'm still not sure how to
optimize the query which contains the union...
My optimized table looks like this:
CREATE TABLE sometable (
id SERIAL,
category LTREE[]
);
Hello,
If I do something like:
SELECT * FROM sometable ORDER BY somerow DESC LIMIT 1 OFFSET 2;
.. and there are multiple rows in sometable where somerow is identical,
am I assured that the values will always come back in the same order?
. Or do I need to ensure that a second sort (such as
Hello,
Thank you to Oleg for your help with this earlier! It resolved it very
nicely!
I still have one remaining issue which I can't figure out, perhaps best
explained with an example:
CREATE TABLE sometable (
titleTEXT,
body TEXT,
footer TEXT,
all_fti TSVECTOR
);
UPDATE
Hello,
I have a table that uses tsearch2 and, of course, and index and trigger
to keep everything updated. Something like:
CREATE TABLE sometable (
id SERIAL,
someinteger INTEGER
sometext TEXT,
sometext2TEXT,
sometext3TEXT,
sometext_fti
Hello,
If I have a rule like this:
CREATE OR REPLACE RULE sometable_update AS ON UPDATE TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert AS ON INSERT TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='sometable';
Hello,
I have a table like this with some indexes as identified:
CREATE TABLE sometable (
dataTEXT,
data_ftiTSVECTOR,
category1 INTEGER,
category2 INTEGER,
category3 INTEGER
);
CREATE OR REPLACE FUNCTION is_null(anyelement) RETURNS BOOLEAN AS 'SELECT
$1 IS
Thanks, you are right, I mis-typed the statements (lack of sleep
*shrug*), thanks for parsing through it...
Your suggestion did resolve the situation nicely!
- Greg
Net Virtual Mailing Lists [EMAIL PROTECTED] writes:
I have a table like this with some indexes as identified:
CREATE OR REPLACE
Than you very much Andrew...
Yes you are right.. I mis-typeed CREATE INDEX.. ;-)
The actual create indexes are as you suggested:
CREATE INDEX sometable_category1_idx ON sometable (is_null(category1));
CREATE INDEX sometable_category2_idx ON sometable (is_null(category2));
CREATE INDEX
Hello,
I have 3 tables which are joined that I need to create a summation for
and I just cannot get this to work.
Here's an example:
CREATE table1 (
id1INTEGER,
title1 VARCHAR
);
INSERT INTO table1 (1, 'Heading #1');
INSERT INTO table1 (2, 'Heading #2');
CREATE table2 (
id1
Hello,
I hate to re-ask this again (sorry to be repetitive!)... I really could
use some help with this, if anyone is familiar with table inheritance as
it applies to my questions
Thanks!
- Greg
Hello,
I am playing with the INHERITS functionality of create table to
determine its
Hello,
I have a quick question regarding tsearch2, hopefully this is an okay
place to ask because I couldn't find a tsearch2-specific mailing list..
Anyways, when the field which is indexed using tsearch2 is displayed in
it's entirety I want the searched keywords to be highlighted, just as in
a
49 matches
Mail list logo
