Re: Problems with Binary Replication

Am 31.03.2012 23:38, schrieb Tom Lane:
> Andreas<maps.on <at> gmx.net>  writes:
>> Now I added 1 row on the master.
>> Before both sequence start values were 403.
>> After the insert the master shows 404 as expected but the "hot-standby"
>> shows 436.
> What are you doing to get it to "show" a value --- not nextval(),
> presumably?
>
> I think that this may be the expected behavior from pre-caching of
> sequence values.  You would see the same thing on the master if you
> were to forcibly crash and restart it.
>
> 			regards, tom lane
>

I checked with pgAdmin.
Even with nextval() the sequence shouldn't move because AFAIK a 
hot-standby is readonly.

The sequence on the hot-standby jumped 32 counters when I inserted a row 
on the master.
Before the insert it had the same value as the master.

Actually I stumbled over it, when I worked on a test pc to prepare some 
sql updates for the master.
I installed there a backup from the hot-standby because I expected this 
should mirror the master 1:1.
At least those tables where only I insert stuff.

Re: loading a function from a file

On 2012-03-26, and <andreaesposito77 <at> gmail.com> wrote:
> Hi all,
> I would like to know how to load a user defined function from a file
> written in pl/pgsql.
>
> I have read it on logging
> psql -h host database <  create_some_func.sql
> but then i am forced to redo the login.  Is there another way to do it?

another way use a connection string:
psql "host=name dbname=database password=SECRET" < create_some_func.sql

another is log into psql and issue the \i psql command to read the file

  \i create_some_func.sql

-- 
⚂⚃ 100% natural

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Problems with Binary Replication

Re: PostgreSQL Trigger and rows updated

Thanks you so much!
it works great now

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Trigger-and-rows-updated-tp5608591p5609895.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Trigger.. AFTER and BEFORE with specific column changed

*I'm trying to follow this :*

CREATE TRIGGER check_update
    *BEFORE* UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE PROCEDURE check_account_update();

*but with AFTER instead of BEFORE. and I'm keep getting error on or near
WHEN.
does that because i have to use BEFORE ? what if i need to execute the
procedure after updating column and it has really changed.*

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-AFTER-and-BEFORE-with-specific-column-changed-tp5610712p5610712.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Parameter setting in multi-statement command; I got bit today

Just FYI... maybe I'm the only Pg veteran who didn't know this but;

Parameter settings in a multi-statement command are not in effect for
later statements in same command.  They will take effect on later
commands however as seen below.

The 2 seconds statement timeout does nothing to prevent the sleep(10)
from completing. 

Platform was Python 2.6 and EDB 8.2.  In the real world, we discovered
this because, I was using such an approach to set a 15 second timeout
so that the next statement, an exclusive lock request would abort is
couldn't be obtained after 15 secs.

This was to avoid contention  in situation where a long-running report
was holding a lock.  

This behavior is quite likely documented somewhere but if so, not
apparent to me. 

Silly example follows...

$ python
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>import pgdb
>conn = pgdb.connect()
>cur = conn.cursor()
>cur.execute("set statement_timeout to '2s'; select pg_sleep(10)")
# first invocation of this succeeds due to the 2s timeout not being
effective yet.

>cur.execute("select pg_sleep(10)")
# A 2s delay here and then exception

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.6/dist-packages/pgdb.py", line 259, in execute
    self.executemany(operation, (params,))
  File "/usr/lib/python2.6/dist-packages/pgdb.py", line 289, in executemany
    raise DatabaseError("error '%s' in '%s'" % (msg, sql))
pg.DatabaseError: error 'ERROR:  canceling statement due to statement timeout
' in 'set statement_timeout to '2s'; select pg_sleep(10)'

It is tedious and I guess mostly unnecessary to do cur.execute("foo")
for each statement and as such, a clever  guy might adopt the habit
of piling all sorts of code into a single execute().

Along these lines, I'd be curious to know of other good reasong for
*not* combining statements like this. 

Thanks>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting <at> comcast.net
p: 732.216.7255

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Trigger.. AFTER and BEFORE with specific column changed

On 04/01/2012 11:45 AM, Albert wrote:
> *I'm trying to follow this :*
>
>
> CREATE TRIGGER check_update
>      *BEFORE* UPDATE ON accounts
>      FOR EACH ROW
>      WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
>      EXECUTE PROCEDURE check_account_update();
>
> *but with AFTER instead of BEFORE. and I'm keep getting error on or near
> WHEN.

What is the error message?

> does that because i have to use BEFORE ? what if i need to execute the
> procedure after updating column and it has really changed.*

Depends what the procedure is doing. For more detail on what the WHEN 
does in BEFORE and AFTER triggers see here:
http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html

Look for the Notes section.

>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-AFTER-and-BEFORE-with-specific-column-changed-tp5610712p5610712.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>

-- 
Adrian Klaver
adrian.klaver <at> gmail.com

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Parameter setting in multi-statement command; I got bit today

Jerry Sievers <gsievers19 <at> comcast.net> writes:
> Just FYI... maybe I'm the only Pg veteran who didn't know this but;
> Parameter settings in a multi-statement command are not in effect for
> later statements in same command.

This is not a true statement in general.

> They will take effect on later
> commands however as seen below.
> The 2 seconds statement timeout does nothing to prevent the sleep(10)
> from completing. 

I believe what's happening there is that the timeout is applied to each
submitted query string, not individual statements within such a string.

			regards, tom lane

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Parameter setting in multi-statement command; I got bit today

Tom Lane <tgl <at> sss.pgh.pa.us> writes:

> Jerry Sievers <gsievers19 <at> comcast.net> writes:
>
>> Just FYI... maybe I'm the only Pg veteran who didn't know this but;
>> Parameter settings in a multi-statement command are not in effect for
>> later statements in same command.
>
> This is not a true statement in general.
>
>> They will take effect on later
>> commands however as seen below.
>> The 2 seconds statement timeout does nothing to prevent the sleep(10)
>> from completing. 
>
> I believe what's happening there is that the timeout is applied to each
> submitted query string, not individual statements within such a string.

Ok, fair enough.

But anyway, then in such a case...

set statement_timeout to '2s'; select foo();  --sent to backend as single string

The foo runs in whatever context was established before and the
statement_timeout setting not actually solid until end of that string
execution.

New statement_timeout setting in effect going forward.

I was naively expecting that the setting was processed in lexical
order in the string and the new setting effective for statements
further out in same string.

What's interesting, is that I just tried ...

set search_path to foo; select * from foo;  --single call to cur.execute()

Huh?!  That did work.  Schema foo not in search_path so the select
should have failed unless the immediatly following path setting, in
same command string, was processed and applied to statements in same
string. 

In fact, in the very next multi-statement command that I tried, I said
"reset search_path; select..." and this raised an exception. 

Doh!!

The defective code ran fine for months by coincidence since in this
DB, OLAP workloads are quite rare and there is generally nothing
holding a lock on the relevant table for long enough to be a problem.

Well, I'll have to scan several other home spun administrative
utilities to see if this same snake in the grass could be elsewhere
too.  Creature of habit, ya know 

Thanks!

> 			regards, tom lane
>

-- 
Jerry Sievers

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Versioned, chunked documents

Hi,

I have documents which are divided into chunks, so that the (ordered)
concatenation of chunks make the whole document. Each of the chunks may
be edited separately and past versions of the chunks need to be kept.

The structure looks fairly simple:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
...
);

CREATE TABLE documents_chunks (
    id SERIAL PRIMARY KEY,
    ctime TIMESTAMP NOT NULL,
    documents_id INTEGER REFERENCES documents(id),
    seq INTEGER NOT NULL, -- sequence within the document
    content TEXT,
...
);

The first goal is to retrieve the latest version of the whole document,
made from the latest versions of all chunks, but later the goal will
also be to fetch the whole version at some point in time (i.e. with
chunks created before a point in time).

I did the first goal by creating two helper views:

CREATE VIEW documents_chunks_last_version_chunk_ids AS
    SELECT documents_id, max(id), seq FROM documents_chunks GROUP BY
documents_id, seq;

CREATE VIEW documents_chunks_last_version_content AS
    SELECT documents_chunks.documents_id, content
        FROM documents_chunks
        JOIN documents_chunks_last_version_chunk_ids ON
documents_chunks.id=documents_chunks_last_version_chunk_ids.max
        ORDER BY documents_chunks_last_version_chunk_ids.seq;

There are indexes on the document_chunks fields seq and documents_id.

Everything looked fine until I examined the output of EXPLAIN ANALYZE
and saw this:

db=> set enable_seqscan to off;
SET
db=> explain analyze select * from documents_chunks_last_version_content
where documents_id=1;

EXPLAIN output given in: http://explain.depesz.com/s/mpY

The query output seems correct on this test case:
db=> select * from documents_chunks_last_version_content where
documents_id=1;
 documents_id | content
--------------+---------
            1 | C1, v2
            1 | C2, v3
            1 | C3, v1
(3 rows)

This huge cost of 10000000000 which appeared out of nowhere in the
EXPLAIN output and the seq scan worry me - where did that come from?
There are absolutely no unindexed fields in the query, and the result
set of the aggregate ("max") is very small.

Of course, I might be doing the whole structure wrong - any ideas?

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general