Summarizing:
* Lock table - High-level: executes fast, but concurrency problems.
Guarentees about future changes.
Select for update - Low-level, concurrent, ensures data validity and
indicates its modified shortly.
Select for share - Low-level, concurrent, ensures data validity.
Hopefully this
I'm using Ruby on Rails and have two tables, A and B. Every row in A
needs a corresponding row in B. A also contains a FK pointing to B.
I created a before insert trigger on A that inserts a new row in B, and
sets the FK in A. This seems to be running fine.
So, A has a b_id fie
Not using foreign keys makes sense for some applications. WORM
applications where you know you are loading accurate data, for
example. Or times when it doesn't matter if an application bug
corrupts your data.
But if you care about your data and if you can't trust your client to
edit it co
On May 22, 2007, at 21:21 , Rick Schumeyer wrote:
The problem is that the new A object that rails gets does not see
the effect of the trigger. If I call the "reload" method, then
everything is ok.
As ActiveRecord (which I'm assuming you're using with Rails) has no
idea of what is going
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/22/07 21:12, Naz Gassiep wrote:
> I'm working in a project at the moment that is using MySQL, and people
> keep making assertions like this one:
>
> "*Really* big sites don't ever have referential integrity. Or if the few
> spots they do (like w
Naz Gassiep wrote:
I'm working in a project at the moment that is using MySQL, and people
keep making assertions like this one:
"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via
"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic locking), never the database level."
Some large sites don't even use data types!
http://www.thestar.com/News/
I'm working in a project at the moment that is using MySQL, and people
keep making assertions like this one:
"*Really* big sites don't ever have referential integrity. Or if the few
spots they do (like with financial transactions) it's implemented on the
application level (via, say, optimistic loc
I'm using Ruby on Rails and have two tables, A and B. Every row in A
needs a corresponding row in B. A also contains a FK pointing to B.
I created a before insert trigger on A that inserts a new row in B, and
sets the FK in A. This seems to be running fine.
The problem is that the new A ob
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of sudhir
>Sent: dinsdag 22 mei 2007 19:21
>To: Albe Laurenz
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lock table, Select for update and
>Serialization error
>
>OK. In your example lock tabl
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of sudhir
>Sent: dinsdag 22 mei 2007 19:28
>To: Tom Lane
>Cc: Albe Laurenz; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lock table, Select for update and
>Serialization error
[note: text reordered]
On Tue, 2007-05-22 at 18:07 -0400, Francisco Reyes wrote:
> Does anyone know of any export or copy utility that runs on FreeBSD?
> I basically need a program that will connect to one database, do a
> select and copy the result to a second database.
Two options:
1) if you want a whole table or sc
Francisco Reyes wrote:
Does anyone know of any export or copy utility that runs on FreeBSD?
I basically need a program that will connect to one database, do a
select and copy the result to a second database.
There are a few ways, from memory (so I might have the odd syntax error):
To replica
Use psql; it provides all the command-line options you need.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresq
Does anyone know of any export or copy utility that runs on FreeBSD?
I basically need a program that will connect to one database, do a select
and copy the result to a second database.
Alternatively a tool which would export the data in a format suitable to
be loaded by psql is just fine.
F
> Cyril VELTER wrote:
> > No I'm not. It's not even complied in the server nor in the pg_dump
binary.
> >
> > The server is built on windows using MSYS simply with ./configure &&
> > make
all
> > && make install
> >
> >
> > I've been able to reproduce the problem 6 times (at
Hello,
as far as I know treach2 doesn't support partial word matching. Last
time I checked it didn't. (I also googled before I asking this).
Am I wrong? If not, we would implement some FTI, something like the old
and decrepated FTI-crontib. Where should we start reading tutorials? I
Hello Group,
I am using postgres in my web application. Number
of physical DB connections are just getting increased for each
re-deployment of my web application. I generally dont shutdown my
webserver and only re-deploy my web application. I heard that
"autoReconnect = tr
On Tue, 2007-05-22 at 20:13 +0100, Richard Huxton wrote:
> Robert Fitzpatrick wrote:
> > I did some googling and came up with some ideas, I have it now with
> > these settings and after restarting PG, no help.
> >
> > work_mem = 5MB
>
> My last post missed yours - you're ahead of my previous repl
Robert Fitzpatrick wrote:
I did some googling and came up with some ideas, I have it now with
these settings and after restarting PG, no help.
work_mem = 5MB
My last post missed yours - you're ahead of my previous reply :-)
Try 32MB, then 64MB, then perhaps 128MB. If it looks like you're not
Robert Fitzpatrick wrote:
On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote:
4. We're still 5 x slower than MS-SQL (with the count). That might
well
be down to having to check visibility on each row with our MVCC
rather
than just going to the index.
Tips? I'd love to know how to see in
On Tue, 2007-05-22 at 14:30 -0400, Robert Fitzpatrick wrote:
> On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote:
> >
> > Hmm... How much of your machine is PG getting to use vs. MS-SQL? What
> > are your shared_buffers, work_mem, effective_cache_size (and how much
> > RAM on this box)?
>
On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote:
> 4. We're still 5 x slower than MS-SQL (with the count). That might
> well
> be down to having to check visibility on each row with our MVCC
> rather
> than just going to the index.
Tips? I'd love to know how to see inside MVCC. I really
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/22/07 12:58, Michelle Konzack wrote:
> Am 2007-05-22 12:45:23, schrieb Ron Johnson:
>> Your subject says "physial place", but then you ask about moving a
>> table to another partition.
>>
>> Since partitions are sub-sections of tables, I don't un
Joseph Shraibman wrote:
I'm running:
PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)
My memory settings are:
work_mem = 64MB
shared_buffers = 128MB
temp_buffers = 32MB
I ran a query that was "SELECT field, count(*) INTO TEMP temptable
Robert Fitzpatrick wrote:
On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote:
Robert Fitzpatrick <[EMAIL PROTECTED]> writes:
On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
returning that many rows in a few seconds?
Am 2007-05-22 12:45:23, schrieb Ron Johnson:
> Your subject says "physial place", but then you ask about moving a
> table to another partition.
>
> Since partitions are sub-sections of tables, I don't understand what
> you are asking.
:-)
INSIDE the postgresql I use table partitioning but the wh
Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/22/07 12:01, Michelle Konzack wrote:
Hello,
I am using Debian GNU/Linux 4.0 with postgresql 8.2.
Now I have splited my Monster-Table of 580 GByte (historical timeline)
into one table per year (= several 100 now) and use ta
This keeps biting me. In a trigger function for INSERT OR UPDATE if you try
IF TG_OP = 'UPDATE' AND OLD.foo = 'bar' THEN
...
it will blow up on inserts because there is no OLD. I always expect
this to short circuit and am always disappointed. Easy fix, of
course...
IF TG_OP = 'UPDATE' THEN
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/22/07 12:01, Michelle Konzack wrote:
> Hello,
>
> I am using Debian GNU/Linux 4.0 with postgresql 8.2.
>
> Now I have splited my Monster-Table of 580 GByte (historical timeline)
> into one table per year (= several 100 now) and use table partit
Michelle Konzack wrote:
Hello,
I am using Debian GNU/Linux 4.0 with postgresql 8.2.
Now I have splited my Monster-Table of 580 GByte (historical timeline)
into one table per year (= several 100 now) and use table partitioning.
(Cool for pg_dump since I can dump singel tables and burn it now on
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05/22/07 12:17, Joseph Shraibman wrote:
> I'm running:
>
> PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.6 20060404 (Red Hat 3.4.6-3)
>
> My memory settings are:
>
> work_mem = 64MB
> shared_buffers = 128MB
> temp_
Hello,
I am using Debian GNU/Linux 4.0 with postgresql 8.2.
Now I have splited my Monster-Table of 580 GByte (historical timeline)
into one table per year (= several 100 now) and use table partitioning.
(Cool for pg_dump since I can dump singel tables and burn it now on CD
or DVD). Then I have re
It is not necessary that LOCK TABLE will be the first statement.
(assuming serializable isolation level is snapshot isolation in postgres)
For serializable transaction, snapshot should be taken when the 'BEGIN'
statement is executed, and not when LOCK TABLE succeeds.
Hence, uncommitted changes s
On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote:
> Robert Fitzpatrick <[EMAIL PROTECTED]> writes:
> > On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
> >> Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
> >> returning that many rows in a few seconds?
>
> > I though
jws wrote:
> Is there a way to round an interval to the nearest minute...
Yes. See date_trunc function. Of course this truncates down to the
specified unit. If you want to round up/down to the nearest minute I
suppose you could just add '30 seconds'::interval before truncating.
select
now(),
date
OK. In your example lock table command is used to avoid rollbacks due
to concurrent transaction.
So LOCK TABLE is useful in this situation.
I have one last doubt:
why there is difference between behavior of 'select for update' and
'lock table'.
one causes serialization error and other does no
On 22 May 2007 10:08:24 -0700, jws <[EMAIL PROTECTED]> wrote:
Is there a way to round an interval to the nearest minute or do I need
to create a function for this?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map setti
On Tue, 2007-05-22 at 19:04 +0200, PFC wrote:
> > I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
> > PERCENT' after SELECT in the query. The Enterprise Manager does not
> > indicate how many rows come back. I save it as a VIEW in MS SQL and do a
> > 'select count(*)...' and,
I'm running:
PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-3)
My memory settings are:
work_mem = 64MB
shared_buffers = 128MB
temp_buffers = 32MB
I ran a query that was "SELECT field, count(*) INTO TEMP temptable" and
it grew to be 10gi
Robert Fitzpatrick <[EMAIL PROTECTED]> writes:
> On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
>> Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
>> returning that many rows in a few seconds?
> I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
>
Is there a way to round an interval to the nearest minute or do I need
to create a function for this?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
PERCENT' after SELECT in the query. The Enterprise Manager does not
indicate how many rows come back. I save it as a VIEW in MS SQL and do a
'select count(*)...' and, yes, it comes back 42164877 records.
No, it comes back 1
On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
> Robert Fitzpatrick wrote:
> > I have developed a view in pgsql that takes over 160K ms to execute, but
> > when copied into MS SQL against the old database (with syntax mods of
> > course), runs in a few seconds.
>
> Your query seems to p
Robert Fitzpatrick wrote:
I have developed a view in pgsql that takes over 160K ms to execute, but
when copied into MS SQL against the old database (with syntax mods of
course), runs in a few seconds.
Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
returning that many ro
Andrew Sullivan wrote:
On Tue, May 22, 2007 at 04:36:49PM +0200, [EMAIL PROTECTED] wrote:
pg_dump on master works fine. On slave i've got error on start of
dumping:
-bash-3.00$ pg_dump -d -D my_database
pg_dump: failed sanity check, parent table OID 225893092 of
pg_rewrite entry OID 225893271
Andrew Sullivan wrote:
On Tue, May 22, 2007 at 04:36:49PM +0200, [EMAIL PROTECTED] wrote:
pg_dump on master works fine. On slave i've got error on start of
dumping:
-bash-3.00$ pg_dump -d -D my_database
pg_dump: failed sanity check, parent table OID 225893092 of
pg_rewrite entry OID 225893271
On Tue, May 22, 2007 at 04:36:49PM +0200, [EMAIL PROTECTED] wrote:
> pg_dump on master works fine. On slave i've got error on start of
> dumping:
>
> -bash-3.00$ pg_dump -d -D my_database
> pg_dump: failed sanity check, parent table OID 225893092 of
> pg_rewrite entry OID 225893271 not found
Yo
I posted an issue I was having with a migration from MS SQL server to
pgsql over the weekend. Tom's suggestion for the query I posted was
right on, I made the appropriate updates to column types by dumping,
changing and restoring the database. I then analyze'd the db and my
query performance was eq
"Albe Laurenz" <[EMAIL PROTECTED]> writes:
> You see, there is a difference. In case a) the serializable transaction
> will very likely fail if there are many concurrent changes on the table.
> In case b), the serializable transaction will always succeed, while
> all concurrent updates must wait.
[EMAIL PROTECTED] wrote:
Hi!
I've got problem with dumping the database on the slave server.
Situation:
Master:
Running CentOS release 4.4 (Final) with PostgreSQL 8.2.4.
Slave:
Running CentOS release 4.3 (Final) with PostgreSQL 8.2.4 and slon
version 1.2.9
pg_dump on master works fine. On
Hi!
I've got problem with dumping the database on the slave server.
Situation:
Master:
Running CentOS release 4.4 (Final) with PostgreSQL 8.2.4.
Slave:
Running CentOS release 4.3 (Final) with PostgreSQL 8.2.4 and slon
version 1.2.9
pg_dump on master works fine. On slave i've got error on st
sudhir wrote:
>> LOCK TABLE should never give you an error, except for a deadlock
>> resolution error.
>>
>> LOCK TABLE will just wait until there is no lock on the table that is
>> incompatible with the requested lock, then it will obtain the lock
and
>> return.
>>
>> LOCK TABLE does not modify ta
On 22.05.2007 14:02, Sandro Dentella wrote:
I see that the default encoding for a db is taken from template1.
I'm using debian and I don't understand how to create template1 on
installation of postgresql with a preferred encoding.
Is it safe to just dump/restore template1 with differen
Sandro Dentella wrote:
I'm using debian and I don't understand how to create template1 on
installation of postgresql with a preferred encoding.
Is it safe to just dump/restore template1 with different encoding?
Or drop template1, and then recreate it from template0 with the
defaults y
Hi,
I see that the default encoding for a db is taken from template1.
I'm using debian and I don't understand how to create template1 on
installation of postgresql with a preferred encoding.
Is it safe to just dump/restore template1 with different encoding?
TIA
*:-)
-
Thanks Laurenz for quick reply.
If this is the expected behavior then isn't 'Lock table' is just extra
performance penalty and achieves nothing under serializable isolation level.
The serializable isolation level in postgres is infact snapshot isolation.
Suppose a transaction T is using 'lock
hum not enough tests before sending this mail, the fisrt request
select lexize('fr','chose');
work but the server crash on the second resquest (the same one)
Le mardi 22 mai 2007 à 12:16 +0200, Philippe Amelant a écrit :
> hi,
> Ok it's my mistake, I forgot to add "-i" at for gendict config.sh
hi,
Ok it's my mistake, I forgot to add "-i" at for gendict config.sh
regards
Le lundi 21 mai 2007 à 19:32 +0200, Philippe Amelant a écrit :
> hi,
> I have compiled postgresql 8.2.4 on a debian etch witch french snowball
> stemmer .
> I applied the lastest patch send by Teodor Sigaev
> (http://w
59 matches
Mail list logo
