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
reall
>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
&g
>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
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=> ex
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 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 |
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
>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 add
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 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 SER
>Greg wrote:
> > is "DROP TRIGGER" transaction safe?... I mean, could I do:
> >
> > BEGIN
> > DROP TRIGGER category_mv_ut;
> > ... insert rows
> > ... update materialized view table
> > CREATE TRIGGER category_mv_ut AFTER UPDATE ON items
> > FOR EACH ROW EXECUTE PROCEDURE update_ut();
> > COMMIT;
Hello,
I have asked about this before, but I just haven't been able to get
anywhere with it yet.. I'm hoping someone can help me?
Here is my original function and trigger:
CREATE OR REPLACE VIEW items_category AS select count(*) AS count
,b.category,nlevel(b.category) AS level,
subpath(b.cate
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 m
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 broadc
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 jad
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 in
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 rest
y disk usage.
I hope this clarifies... I think I'm confused just trying to explain 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 quer
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 S
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 emailtemplate_email_t
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
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
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() - inte
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, going
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
>>
stries it complains about
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 w
>On Sat, Nov 20, 2004 at 06:09:49PM -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.
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
databas
hen the real
>address is needed. Mailing lists typically don't mask the email
>address, and since you can't fake an address if you wish to get email,
>everyone on the list will increase there changes of being spammed, but
>maybe only slightly. This isn't necessarily a b
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 o
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?..
ml
>
>> -Ursprüngliche Nachricht-
>> Von: [EMAIL 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?
>
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 t
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 sometable
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
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
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 sometable
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 TSVECTOR
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';
CREAT
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 a
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 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
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 (
id1I
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 suita
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 h
51 matches
Mail list logo
