date:20070629

Re: [GENERAL] Strange duplicate key violation error

2007-06-29 Thread Richard Huxton


Casey Duncan wrote:
There are in fact one of these tables for each schema, each one contains 
exactly one row (the log in the name is a bit misleading, these just 
contain the current replica state, not a running log).


2007-06-28 08:53:54.937 PDT [d:radio_reports_new u:slony s:4683d86f.3681 
3] ERROR:  duplicate key violates unique constraint replica_sync_log_pkey


I've never seen this error before in millions of updates to this table. 
It confuses me because the columns indexed by the primary key *are not* 
being modified, so how could the update violate the constraint? 
Furthermore there is only one record in the table anyway.


The updates are performed by daemons that are in charge of replicating 
the changes for one database each. So there is no concurrent updating on 
this table, only one process updates it.


OK, so a single slony process is updating a single row and causing a 
pkey violation. Has this happened only the once?


The error caused the daemon to pause, so I restarted it. The error did 
not recur the second time.


fwiw this is postgres 8.1.8 on Linux.


Well, 8.1.9 is the latest release, but I don't see anything concerning this.

Two things occur to me:
1. Slony adds its own triggers to assorted tables. I don't see how it 
could make a difference, but it might be worth keeping in mind.
2. Primary keys are enforced by unique btree indexes and you could have 
come across a strange corner-case where it's not updating correctly. If 
it's only happened the once, that will make it very hard to track down.


If it doesn't do it again in the next day or so, I'd be inclined to 
REINDEX the table, in case it is in a slightly odd state.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Execution variability

2007-06-29 Thread Vincenzo Romano

On Thursday 28 June 2007 16:08:06 Alvaro Herrera wrote:
 Vincenzo Romano escribió:
  The values are here below. I suppose that the hashed
  ones imply a default value.

 Correct (widely known as commented out)

  By the way, it seems that the problem arises with only one query,
  while the other ones behave almost the same all the time.

 Let's see the EXPLAIN ANALYZE.  Does it involve the big table?

I need to check.
The bad guy is a function and I have to get the body out in order to 
make the PG explain it.

-- 
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] AutoVacuum Behaviour Question

2007-06-29 Thread Denis Gasparin

Martijn van Oosterhout ha scritto:
 On Thu, Jun 28, 2007 at 11:12:19AM +0100, Bruce McAlister wrote:
   
 I just want to verify that I understand you correctly here, do you mean
 that the temporary table is created by specific sql, for example, create
 temp table, then perform some actions on that temp table, then, either
 you remove the temp table, or, if you close the session/connection the
 postmaster will clean up the temp table? What happens if you're using
 connection pools, i mean are those sessions deemed closed after the
 queries complete, when the pool connections are persistent.
 

 Yes, the temp table is private to the session and will be removed once
 the session closes, if not sooner. As for connection pools, IIRC there
 is a RESET SESSION command which should also get rid of the temporary
 tables.
   
RESET SESSION command is available only in 8.2 branch, isn't it?
I tried to issue the command in a 8.1 server and the answer was: ERROR: 
unrecognized configuration parameter session

Is there available a patch for the 8.1 version of postgresql?

Thank you,
Denis

Re: [GENERAL] AutoVacuum Behaviour Question

2007-06-29 Thread Bruce McAlister

Denis Gasparin wrote:
 Martijn van Oosterhout ha scritto:
 On Thu, Jun 28, 2007 at 11:12:19AM +0100, Bruce McAlister wrote:
   
 I just want to verify that I understand you correctly here, do you mean
 that the temporary table is created by specific sql, for example, create
 temp table, then perform some actions on that temp table, then, either
 you remove the temp table, or, if you close the session/connection the
 postmaster will clean up the temp table? What happens if you're using
 connection pools, i mean are those sessions deemed closed after the
 queries complete, when the pool connections are persistent.
 

 Yes, the temp table is private to the session and will be removed once
 the session closes, if not sooner. As for connection pools, IIRC there
 is a RESET SESSION command which should also get rid of the temporary
 tables.
   
 RESET SESSION command is available only in 8.2 branch, isn't it?
 I tried to issue the command in a 8.1 server and the answer was: ERROR: 
 unrecognized configuration parameter session
 

I had a look in our configuration and there is a session option that
comes up if you type RESET  and press TAB twice. However, if you do
try to run it, it comes back with:

blueface-crm=# RESET session ;
ERROR:  unrecognized configuration parameter session

We're running PostgreSQL 8.2.4. Alvaro said it was only going to be
available in 8.3. I hope they backport it to 8.2 though, 90% of our
connections are via connection pooled drivers, so being able to reset
the session after each transaction would be a handy way to ensure the
environment is sane for the next transaction :)

 Is there available a patch for the 8.1 version of postgresql?
 
 Thank you,
 Denis

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] a JOIN on same table, but 'slided over'

2007-06-29 Thread Rafal Pietrak

Thank you All for this extensive help!

BTW: google helps, once you know that the construct is called
correlated subquery - there is no way to get an answer before one
knows the question :)

Thenx again!

-R

On Thu, 2007-06-28 at 23:23 +0530, Gurjeet Singh wrote:
On 6/28/07, Alban Hertroys [EMAIL PROTECTED] wrote:

This is called a 'correlated subquery'. Basically the subquery
is
performed for each record in the top query.

Google gave me this:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm

I think the sub-section titled Example: Correlated subquery in a
WHERE Clause is appropriate to explain our query at hand.

Simply put, correlated queries are like nested FOR loops of any high
level programming language.

1. FOR( record R in result of outer-query )
2. execute inner query, using any R.colname1
3. compare R.colname2 with the result of the correlated-subquery
4. produce R in output, iff the above comparison succeeded

Line 2 can be treated as another FOR loop, where every record of
inner-query is being processed, and comparing the local expressions
with a column (or expression) that comes from outer query.

The comparison in step 3 can be against any expression, with columns
or against a pure constant too!

For example, the following query produces the name of all the
employees, who manage at least one other employee.

select empno, ename
from emp e1
where exists (select 1
from emp e2
where e2.mgr = e1.empno);

The only thing I would add for our query is that, that the outer
SELECT of our query produces a cartesian product (no join-condition
between t1 and t2), but only one row from t2 qualifies for the join,
since the WHERE condition is on a unique column, and the correlated
subquery returns just the required value (lowest of the IDs that are
greater than current t1.ID being processed).

I know the above one-line-paragraph may sound a bit cryptic for
someone new to correlated subqueries, but if you understand the
example in the link above, then this would start making some sense.

64 matches

Mail list logo