active='t'),id)
But the above does not appear to exist. Is there a simple way to create a
check constraint for this type of situation, or do I need to create a function
to eval a check constraint?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http:
On Sun, 5 Mar 2006, Michael Glaesemann wrote:
On Mar 5, 2006, at 17:25 , Jeff Frost wrote:
I believe you're looking for what is called a partial index.
http://www.postgresql.org/docs/current/interactive/indexes-partial.html
create unique index foo_partial_idx on foo (id) where a
COMMIT;
After commit, I get a lovely:
ERROR: could not open relation with OID x
Is this expected? To solve this, I simply moved my initial update outside the
transaction.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phon
On Fri, 17 Mar 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
After commit, I get a lovely:
ERROR: could not open relation with OID x
Could we see a complete test case, rather than handwaving? I'd expect
some issues like this if you were using any prepared st
On Fri, 17 Mar 2006, Jeff Frost wrote:
Could we see a complete test case, rather than handwaving? I'd expect
some issues like this if you were using any prepared statements or
plpgsql functions with non-EXECUTEd queries involving the dropped table,
but your description doesn't ment
hanging a NOT NULL constraint. I didn't
think this to be the expected behavior for this query, so I thought I'd post
and see whether I was thinking along the wrong lines. If this is the expected
behavior, then TRUNCATE...ALTER TABLE appears like the way to go in the
future.
Tha
x27;re recreating
(create or replace) that function in that transaction anyway, but perhaps that
needs to happen before the update.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FA
On Fri, 17 Mar 2006, Jeff Frost wrote:
I'll try that and see if that makes the difference, since we're recreating
(create or replace) that function in that transaction anyway, but perhaps
that needs to happen before the update.
I added this at the top of the transaction:
DRO
CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id)
REFERENCES accounts_basics (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
Now that I've got a test case for you guys to look at, I'm off to rewrite our
standard procedure to use TRUNCATE instea
rd and not credit_card_audit. Are you saying that it could cause
this sort of problem even though it doesn't fire?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
atabase
suffers from performance since. For example, a simple query such as
"SELECT MIN(a-primary-key-column) FROM a-table" takes quite a very long
time; actually I gave up before getting the result.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.fro
it should be
(hopefullY) fixed by tomorrow morning.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 6: explain analyze is your friend
remedy the problem. Is postgres logging any errors?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 1: if postin
Is there a reasonable way to extract a list of all tables which contain a
specific column name from the system views on 8.1?
For instance, I might want to enumerate all tables with a column named
last_modified.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC
On Fri, 21 Apr 2006, Bruno Wolff III wrote:
On Fri, Apr 21, 2006 at 09:29:33 -0700,
Jeff Frost <[EMAIL PROTECTED]> wrote:
Is there a reasonable way to extract a list of all tables which contain a
specific column name from the system views on 8.1?
For instance, I might want to enumera
X foo_A_B_unique_idx ON foo (A,B);
See the docs here:
http://www.postgresql.org/docs/current/interactive/indexes-unique.html
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
elect id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingll
#x27;s no way to get around the subselect
though.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 6: explain analyze is your friend
27;t be too bad.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
last 30 items per each account_id...so each account_id will have his last 30
messages in the table.
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadc
one
who set up this box and there has been virtually no administration or
maintenance on it that I know of...) How about the WAL files in pg_xlog?
How critical are they when no data on the system is critical in and of
itself? Any suggestions would be greatly appreciated...
--
Je
terminated:
Daniel,
I would guess this is more appropriate for the -admin list so I cc'd it.
I think you are most likely running out of memory or running up against a
ulimit on memory. I would first check my ulimit settings on the postgres user
and see if they are a bit small.
--
Jeff
up on filesystem maintenance/tweaking...
Scott Marlowe wrote:
I can't tell you the number of times that little trick has saved my
life.
On Thu, 2006-07-27 at 11:32, Jeff Frost wrote:
You can probably just "tune2fs -m 0 " to give yourself enough
space to get out of the jam before yo
;page_view_visit_idx" btree (visit_id)
Foreign-key constraints:
"fk34afd255fbacabec" FOREIGN KEY (visit_id) REFERENCES visit(id)
What kind of silliness am I forgetting?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Thu, 2 Nov 2006, Jeff Frost wrote:
I'm having problem with a cleanup script that runs nightly. The script calls
the following query:
delete from visit where id not in (select distinct visit_id from page_view);
This yields the following error:
ERROR: update or delete on "visit
On Thu, 2 Nov 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
delete from visit where id not in (select distinct visit_id from page_view);
This yields the following error:
ERROR: update or delete on "visit" violates foreign key constraint
"fk34afd255fb
On Fri, 3 Nov 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
On Thu, 2 Nov 2006, Tom Lane wrote:
This seems pretty darn weird. I am wondering about corrupt indexes ---
can you find the indicated key in either table if you set
enable_indexscan and enable_bitmapsca
On Fri, 3 Nov 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
BTW, just to make sure I get the right file to ship over if we have this
again, it would be: /var/lib/pgsql/data/base/9366228/16204210 yes?
Not necessarily --- the filename is initially the same as the index OI
On Fri, 3 Nov 2006, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
Well, I spoke to soon on the it all works front. So, it's been
reindexed and appears to be working properly now. I guess I'll keep
an eye on it for a while. I didn't get your query suggestion in
rring, but
it's possible the index was already damaged?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 3
On Tue, 7 Nov 2006, Jeff Frost wrote:
Well, it's been working wonderfully since the REINDEX, so I don't know what
to say. Any idea if having a too small max_fsm_pages could hose an index,
because I know that happened not too long before we started seeing this
problem. The fsm set
e row returned by a subquery used as an expression
Thanks
Richard
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://
Perhaps I should have asked this earlier. What information are you trying to
extract?
On Mon, 22 Jan 2007, Richard Ray wrote:
This is not exactly what I need
I want to return the data in t1
On Mon, 22 Jan 2007, Jeff Frost wrote:
I think this is what you're looking for Richard:
S
pg_class where relname = 't1') and attisdropped = false and attnum > 0)
is a substitute for *
On Mon, 22 Jan 2007, Jeff Frost wrote:
Perhaps I should have asked this earlier. What information are you trying
to extract?
On Mon, 22 Jan 2007, Richard Ray wrote:
This is not exactly what
On Mon, 22 Jan 2007, Richard Ray wrote:
On Mon, 22 Jan 2007, Jeff Frost wrote:
So why are you avoiding "SELECT * FROM t1;" ?
I was affeared that if I brought my total ignorance to light I would be band
from the list but here goes.
I work in UNIX/Linux environments.
It's my
On Tue, 23 Jan 2007, Josh Williams wrote:
From: Jeff Frost <[EMAIL PROTECTED]>
On Mon, 22 Jan 2007, Richard Ray wrote:
...
That's definitely part of it. I'm assuming the above is an abridged example
and the OP is doing something dynamic with the query. The real trouble i
explain analyze output yet.
Does anyone have a better method of separating this data out?
---
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)
On Mon, 18 Jun 2007, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
[ umpteen million iterations of: ]
-> Limit (cost=0.00..367.09 rows=1 width=8)
-> Index Scan Backward using page_view_stamp_idx on
page_view pv2 (cost=0.00..158215
On Sun, 17 Jun 2007, Jeff Frost wrote:
-
Seq Scan on page_view pv1 (cost=0.00..11529031.34 rows=3580205 width=239)
SubPlan
-> Result (cost=1.58..1.59 r
On Sun, 17 Jun 2007, Jeff Frost wrote:
On Mon, 18 Jun 2007, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> writes:
[ umpteen million iterations of: ]
-> Limit (cost=0.00..367.09 rows=1 width=8)
-> Index Scan Backward using page_view_
doesn't hurt anything, I'm just curious why it skips one after
every generate_series insert?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(
thing to do would be to lowercase everything, then remove all
duplicate chars and spaces, then use length() on that, but it's not obvious to
me how I might remove the duplicate chars with the pattern matching support
in the docs.
--
Jeff Frost, Owner <[EMAIL PROTECTED]&g
On Wed, 5 Mar 2008, Osvaldo Rosario Kussama wrote:
Jeff Frost escreveu:
I've got an interesting one...I'm trying to find columns that have three or
fewer distinct characters (for example, "aa"). Wondering if I need
to write a function or if someone has an idea how
43 matches
Mail list logo
