Quick questions:
For big tables with frequent insert, no update, and frequent read
(using indexes), will clustering help?
what should be done on such table other than regular analyze?
comments are appreciated.
On 8/16/05, Kevin Murphy <[EMAIL PROTECTED]> wrote:
> Greg Stark wrote:
>
> >All that
Matthew Terenzio wrote:
On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote:
db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user)
REFERENCES users(user_id);
ERROR: syntax error at or near "user" at character 56
I'm either blind or something besides syntax can cause this error
Matthew Terenzio <[EMAIL PROTECTED]> writes:
> On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote:
>
>> db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user)
>> REFERENCES users(user_id);
>> ERROR: syntax error at or near "user" at character 56
>>
>> I'm either blind or something be
On Tue, Aug 16, 2005 at 10:09:14PM -0400, Matthew Terenzio wrote:
> >db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user)
> >REFERENCES users(user_id);
> >ERROR: syntax error at or near "user" at character 56
> >
> >I'm either blind or something besides syntax can cause this error.
On Aug 16, 2005, at 9:37 PM, Matthew Terenzio wrote:
db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user)
REFERENCES users(user_id);
ERROR: syntax error at or near "user" at character 56
I'm either blind or something besides syntax can cause this error. no?
changed the fiel
Greg Stark wrote:
All that said clustering is indeed often quite effective. Especially if it
makes an index scan efficient enough to win over sequential scans you can see
some huge effects. It's most useful for tables that aren't undergoing lots of
updates and don't need to be reclustered often.
db=# ALTER TABLE ONLY table ADD CONSTRAINT "$1" FOREIGN KEY (user)
REFERENCES users(user_id);
ERROR: syntax error at or near "user" at character 56
I'm either blind or something besides syntax can cause this error. no?
---(end of broadcast)---
T
Kevin Murphy <[EMAIL PROTECTED]> writes:
> This is just an FYI for other people out there with large tables: table
> clustering sped up my queries from 10-100 times, which I am very happy about.
> I'm posting this in case it's ever useful to anybody. If someone reading this
> feels that I did so
On Tue, Aug 16, 2005 at 07:19:38PM -0500, Apu Islam wrote:
> I have a trigger which is not working properly.
> The error I get is parse error at $1. I am putting the code here for
> someone to see and comment on.
When do you get the error? When you create the function, or when
the trigger calls i
I have a trigger which is not working properly.
The error I get is parse error at $1. I am putting the code here for
someone to see and comment on.
(p/s the double quotes are actually two single quotes)
best regards,
-apu
CREATE FUNCTION cust_call_update() RETURNS TRIGGER AS '
DECLARE
I noticed on the drupal-devel mailing lists that a new postgresql
maintainer is wanted. You can view the thread at:
http://lists.drupal.org/archives/drupal-devel/2005-08/msg00432.html
You can sign up for the drupal-devel mailing list by filling out the
form at the bottom of the page at:
http://
Wow, non-blocking lock failure?
Can I take this chance to say an overdue thanks to the Postgresql
developers? A truly commercial grade feature set in a free
database...
On 8/16/05, Matt Miller <[EMAIL PROTECTED]> wrote:
> On Tue, 2005-08-16 at 12:01 -0700, Bill Moseley wrote:
> > I wondered if m
Jonathan Villa schrieb:
> I'm having some trouble getting one of the contrib modules to load
> correctly...
>
> it's for tsearch2.sql
>
> I've tried
>
> ./configure --prefix=/usr/local/pgsql
> gmake all
> gmake install
>
> and I've also tried, from the contrib/tsearch2/ dir,
>
> gmake
>
> but
marcelo Cortez wrote:
Hi Richard , folks
the query is:
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
...etc
I'm not seeing anything here that should take locks, although
On Tue, 2005-08-16 at 12:01 -0700, Bill Moseley wrote:
> I wondered if my application should set an alarm and timeout
> with an error if, by odd chance, an update hangs.
Here's a way to handle this under the upcoming 8.1 release:
Before you execute the update you can execute SELECT ... FOR UPDATE
Hello,
I´m using PostgreSQL 7.3.2 and i´m having a little problem.
Error: Unable to look up type id 0
This error happens when I try to update a record on the table. I can
solve this if a drop the table and create it again, but how can I solve
this problem whitout need to do this? Any idea?
On Tue, Aug 16, 2005 at 08:25:25PM +0200, Martijn van Oosterhout wrote:
> On Tue, Aug 16, 2005 at 11:05:35AM -0700, Bill Moseley wrote:
> > I've read over the docs on Concurrency Control but still not clear
> > about when transactions block other updates, and how to deal with that
> > on the applic
I'm having some trouble getting one of the contrib modules to load
correctly...
it's for tsearch2.sql
I've tried
./configure --prefix=/usr/local/pgsql
gmake all
gmake install
and I've also tried, from the contrib/tsearch2/ dir,
gmake
but that fails when looking for flex, which by the way, is
On Tue, Aug 16, 2005 at 11:05:35AM -0700, Bill Moseley wrote:
> I've read over the docs on Concurrency Control but still not clear
> about when transactions block other updates, and how to deal with that
> on the application level.
>
> If I do a BEGIN and an UPDATE in one psql session and then try
I've read over the docs on Concurrency Control but still not clear
about when transactions block other updates, and how to deal with that
on the application level.
If I do a BEGIN and an UPDATE in one psql session and then try and do
an UPDATE in another psql session that UPDATE waits until either
Hi Richard , folks
the query is:
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_
marcelo Cortez wrote:
hi all
I have a lock problem, one select query freeze my
application, the query in question is on complex
select
An ordinary select doesn't take any locks. What is the actual query
causing this?
--
Richard Huxton
Archonet Ltd
---(end of b
This is just an FYI for other people out there with large tables: table
clustering sped up my queries from 10-100 times, which I am very happy
about. I'm posting this in case it's ever useful to anybody. If
someone reading this feels that I did something wrong, let me know.
I conducted tests
hi all
I have a lock problem, one select query freeze my
application, the query in question is on complex
select , with many join's, but select all of them.
The select * from pg_catalog.pg_locks show many rows
while the query freeze my aplication.
the aplication use ODBC .
any ideas?.
any help
On Tue, Aug 16, 2005 at 04:54:15PM +0100, Oluwatope Akinniyi wrote:
> On Fedora as:
> 192.168.0.253:5432:hms:sysdba:x
> where hms is the database and sysdba the username;
>
> I have a the ~/.pgpass inside the home of user tope (i.e. /home/tope).
> I tried with
>
> $ psql
I wonder if you sho
>
>
>>> > On Fedora: wrong file permissions?
>>> > Btw.: ~/.pgpass is only supported in 7.3 and later.
>>
>>
>>
>> Thanks.
>>
>> The permission is 0600 on Fedora. But must the file
>> (~/.pgpass) be owned by postgres? Also what could explain the
>> reason why it is not working on Windows X
On Tue, 2005-08-16 at 17:31 +0300, Andrus wrote:
> Each invoice must have at leat one row.
>
> I want that transaction commit fails if invoice header is inserted
> without
> any row in invoice rows table.
>
> How to prevent inserting invoice headers without rows ?
You could have a NOT NULL fore
Stephan Szabo <[EMAIL PROTECTED]> writes:
> I think this is similar to the issue recently reported on -bugs. My
> theory there was that trigger timing changes between 7.4 and 8.0 seems to
> have caused the sequence of checks inside the trigger manager and triggers
> that prevented intermediate sta
I have tables for invoice headers and rows:
CREATE TABLE invoiceheader ( id SERIAL PRIMARY KEY, );
CREATE TABLE invoicerow ( id SERIAL PRIMARY KEY,
idheader INTEGER NOT NULL REFERENCES invoiceheader ON DELETE CASCADE ON
UPDATE CASCADE,
);
Each invoice must have at leat one row.
I w
Jake Stride wrote:
I seem to be having some problems with cascading updates, I seem to
remember that this worked in the database in 7.4 but seems to fail in 8,
can anyone give me any pointer please as I seem to be able to find
anything about this online.
No when I try to do an update I get the
For table replication, I need to know the date and time when particular
table is last changed by insert, update or delete command.
This information is required for most tables in database (about 100 tables).
How to obtain this information without creating 100 triggers ?
Andrus.
-
Hello All,
I hope that you are well. Based on the consensus on the list and the good
recommendations from y'all. I will be ordering Douglas' book. I think that I
would benefit greatly from it. Thanks to all that responded to this thread.
Cheers,
Aly.
WELTY, RICHARD wrote:
On Tue, 16 Aug 2005, Jake Stride wrote:
> I seem to be having some problems with cascading updates, I seem to
> remember that this worked in the database in 7.4 but seems to fail in 8,
> can anyone give me any pointer please as I seem to be able to find
> anything about this online.
I think this
Alvaro Herrera wrote:
On Mon, Aug 15, 2005 at 01:15:03PM -0700, TJ O'Donnell wrote:
While writing installation instructions for my new PostgreSQL product, I
found myself
writing the following sentence:
"For first time users, we recommend building the gnova database,
since it has no impact on
Ulrich Wisser <[EMAIL PROTECTED]> writes:
>indexrelid
> ---
> userclick_i01
> userclick_i02
> userclick_i03
> userclick_i04
> userclick_i05
> userclick_i06
> userclick_i07
> (7 rows)
OK, so userclick_i02 appears to be the broken index.
> How do I proceed? How can I t
Aly Dharshi [mailto:[EMAIL PROTECTED] wrote:
> Having read both books would your recommendation be
> to go with Korry Douglas' book ?
a qualified "probably". one of the things i'm going to do when
i actually write the review is make a quick pass over douglas
for a compare and contrast; i think th
Hello,
The platform is linux and the version is postgreSQL
8.0.0
not that the database is going into some
inconsistent state.
it is not crashing
but ... then ..the beahviour is different for the
two ..i mean processes...and threads
i ll do more checking to see if it crashes or not.
Someti
On Tue, Aug 16, 2005 at 04:06:57PM +0530, Surabhi Ahuja wrote:
> Dear All,
>
> it seems to me that ...isolation is guaranteed only if we are considering
> different connections in different processes.
>
> if we have multiple threads ...each using diff connection (but same process)
> ..isolat
Dear All,
it seems to me that ...isolation is
guaranteed only if we are considering different connections in different
processes.
if we have multiple threads ...each using diff
connection (but same process) ..isolation is not happening.
when context switch between threads happens
..everythi
Dennis Bjorklund wrote:
On Mon, 15 Aug 2005, Mage wrote:
2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint
"common_adviewnum_adid_site_day_index"
Between your select and your insert someone else inserted a row making the
insert fail.
I see. I thought that th
On Tue, Aug 16, 2005 at 01:20:29AM -0500, Jim C. Nasby wrote:
> My experience is that long FAQ's are fine, so long as they're easy to
> search through. This means you've got to support users who may not know
> the magic word to search on. A good example is finding the limits for
> how many rows in
> > On Fedora: wrong file permissions?
> > Btw.: ~/.pgpass is only supported in 7.3 and later.
>
> Thanks.
>
> The permission is 0600 on Fedora. But must the file
> (~/.pgpass) be owned by postgres? Also what could explain the
> reason why it is not working on Windows XP where permission
> is
Hi,
I have installed working SSL Certificates on Windows XPSP2 and Fedora
Core 4.
The PostgreSQL 8.0.3 Documentation states the following in the creation
of SSL certificates for SSL connection:
"Fill out the information that openssl asks for. Make sure that you
enter the local host name as "Comm
Andreas wrote:
> On Fedora: wrong file permissions?
> Btw.: ~/.pgpass is only supported in 7.3 and later.
Thanks.
The permission is 0600 on Fedora. But must the file (~/.pgpass) be owned by
postgres? Also what could explain the reason why it is not working on Windows
XP where permission is no
I seem to be having some problems with cascading updates, I seem to
remember that this worked in the database in 7.4 but seems to fail in 8,
can anyone give me any pointer please as I seem to be able to find
anything about this online.
I have several tables, but the two I am having issue with are
> >In theory, but I beleive there are build issues in the currently
> >released version when it comes to building on win32 with
> >ENABLE_THREAD_SAFETY.
> >
> >//Magnus
> >
> >
> >
> >
> Thank you for this information.
>
> Your wrote: "In theory"
> Do you know anybody who has tested this ?
>
Hi Tom,
No, it would be the one next to be processed. VACUUM does them in OID
order, so try something like
select indexrelid::regclass from pg_index
where indrelid = 'public.userclick'::regclass
order by indexrelid;
indexrelid
---
userclick_i01
usercli
47 matches
Mail list logo
