Re: [GENERAL] Trigger and deadlock
Hi, It's ok. Before the insert, The foreign key constraint locked the row. If transaction A and B lock the row with FK, before doing UPDATE, they were stuck. I found a solution by creating an "before insert" trigger with a simple SELECT FROM UPDATE on the row. Loïc 2013/7/29 Loïc Rollus > Here is pg_lock for relation Y (= 2027300) > > > locktype | database | relation | page | tuple | virtualxid | > transactionid | classid | objid | objsubid | virtualtransaction | pid | > mode | granted > > --+--+--+--+---++---+-+---+--++--+--+- > tuple| 2026760 | 2027300 | 365 |42 || > | | | | 6/313 | 9274 | ShareLock >| f > tuple| 2026760 | 2027300 | 365 |42 || > | | | | 5/113 | 9273 | ExclusiveLock >| f > relation | 2026760 | 2027300 | | || > | | | | 3/2532 | 9104 | > AccessShareLock | t > relation | 2026760 | 2027300 | | || > | | | | 3/2532 | 9104 | RowShareLock > | t > relation | 2026760 | 2027300 | | || > | | | | 3/2532 | 9104 | > RowExclusiveLock | t > relation | 2026760 | 2027300 | | || > | | | | 5/113 | 9273 | > AccessShareLock | t > relation | 2026760 | 2027300 | | || > | | | | 5/113 | 9273 | RowShareLock > | t > relation | 2026760 | 2027300 | | || > | | | | 5/113 | 9273 | > RowExclusiveLock | t > tuple| 2026760 | 2027300 | 365 |42 || > | | | | 3/2532 | 9104 | ExclusiveLock >| t > relation | 2026760 | 2027300 | | || > | | | | 6/313 | 9274 | > AccessShareLock | t > relation | 2026760 | 2027300 | | || > | | | | 6/313 | 9274 | RowShareLock > | t > > > 29-07-2013 10:12:29,004 ERROR GrailsExceptionResolver - PSQLException > occurred when processing request: [POST] /api/annotation.json > ERROR: deadlock detected > Détail : Process 9273 waits for ExclusiveLock on tuple (365,42) of > relation 2027300 of database 2026760; blocked by process 9104. > Process 9104 waits for ShareLock on transaction 1286966; blocked by > process 9273. > Indice : See server log for query details. > Où : SQL statement "UPDATE y > SET count_x = count_x + 1 > WHERE y.id = NEW.y_id" > PL/pgSQL function "incrementy" line 6 at SQL statement. Stacktrace follows: > org.postgresql.util.PSQLException: ERROR: deadlock detected > > I don't understand where the "ExclusiveLock" come from. > Postgresql Doc says ( > http://www.postgresql.org/docs/9.1/static/explicit-locking.html): > "EXCLUSIVE: This lock mode is not automatically acquired on tables by any > PostgreSQL command." > In log, I just see that Hibernate just read the row y (365,42) (simple > select). No explicit lock. > > Loïc > > > > > > > > > > > 2013/7/29 Loïc Rollus > >> Hi, >> >> Thanks for your quick reply! >> I found the table. But the ctid of the row has changed. But during my >> test, I update only 1 row from this table, so I know the row. >> >> I had already put log_statement to 'all'. It's strange because in the >> log, I only see simple "SELECT ... FROM" on this table (no UPDATE or SELECT >> FOR UPDATE). But I can't see request from trigger (not show by >> log_statement='all'?). >> >> Here is a log sample (simplify: insert into X should update the counter >> on the specific row from Y) >> >> >> 1286781 22142 2013-07-26 13:55:22 CEST LOG: execute : insert >> into X... >> 1286780 22426 2013-07-26 13:55:22 CEST LOG: execute : insert >> into X... >> > it should be UPDATE on Y from trigger here >> 1286781 22142 2013-07-26 13:55:22 CEST LOG: execute select this_.id as >> id54_0_, from >> [...22142 follow its way, commit, and carry another request ] >> 1286785 22142 2013-07-26 13:55:23 CEST LOG: execute : insert >> into X >> 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected >> 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for >> ShareLock on transaction 1286780; blocked by process 22426. >> Process 22426 waits for ExclusiveLock on tuple (365,13) of relation >> 2027300 of database 2026760; blocked by process 22142. >> 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query >> details. >> 1286785 22142 2013-07-26 13:55:25 CEST CONTEXT: SQL statement "UPDATE Y >> 1286785 22142 2013-07-26 13:55:25 CEST STATEMENT: insert into X >> Process 22426 waits
Re: [GENERAL] Event trigger information accessibility on plpgsql
Wow Andrew, Great work! Lot of ideas to pick from. Now, like you said it looks rather complicated and fragile. We will take a look at creating a custom C trigger to see if we can have something easier. Pity there was no agreement to just expose the object that fired the trigger. Best, On Jul 29, 2013, at 9:02 PM, Andrew Tipton wrote: > On Mon, Jul 29, 2013 at 10:08 PM, Javier de la Torre > wrote: > You think it will be possible to, instead of comparing schemas, looking for > the last modified OID on the DB to figure out where it happened? > > Not really. When a row has been updated, you can only see the new values. > By comparing the row's xmin value to txid_current() you could tell that it > was updated during this transaction, but there is no way to access the old > row. And after a row has been deleted, there is absolutely no way for the > current transaction to see it any longer. > > I thought that I would try my hand at writing an event trigger that was able > to capture ALTER TABLE .. RENAME and ALTER TABLE ... RENAME COLUMN. Turns > out to be far harder than it looks. After much hacking around, I managed to > come up with a solution. The attached script audit_ddl.sql does the trick. > > $ psql > psql (9.4devel) > Type "help" for help. > > postgres=# create table foo(column_one text, column_two integer); > NOTICE: on_start: taking catalog snapshot... > NOTICE: on_commit: checking for DDL actions. > CREATE TABLE > postgres=# begin; > BEGIN > postgres=# alter table foo rename column column_one to col1; > NOTICE: on_start: taking catalog snapshot... > ALTER TABLE > postgres=# alter table foo rename column column_two to col2; > ALTER TABLE > postgres=# commit; > NOTICE: on_commit: checking for DDL actions. > NOTICE: on_commit: table foo column column_one renamed to col1. > NOTICE: on_commit: table foo column column_two renamed to col2. > COMMIT > > Disclaimer: this is a nasty and grotesque series of hacks. You've been > warned... > > a) using pg_advisory_xact_lock_shared() as a session-scoped variable that > gets automatically reset at the end of the transaction. > b) mucking around in the pg_locks view to determine if that advisory lock is > already held. > c) creating a temporary table whose sole purpose is to cause a constraint > trigger to be fired on transaction commit. (oh yes, if you execute SET > CONSTRAINTS ... during the transaction, you will surely break this.) > > Oh, and you have to remember to > ALTER EVENT TRIGGER audit_ddl_event_trigger DISABLE; > before attempting to DROP anything in the audit_ddl schema, or recursive > hilarity will ensue. > > > Regards, > Andrew Tipton >
Re: [GENERAL] Trigger and deadlock
Loïc Rollus wrote: > It's ok. > Before the insert, The foreign key constraint locked the row. If transaction > A and B lock the row with > FK, before doing UPDATE, they were stuck. > I found a solution by creating an "before insert" trigger with a simple > SELECT FROM UPDATE on the row. You mean "SELECT FOR UPDATE", richt? Great that you could figure out where the problem was. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reddwarf for PostgreSQL?
Hi, There is an opensource DaaS project called "RedDwarf". It seems the project is only for MySQL. Does anybody know if the project will support PostgreSQL in the future? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] to_char with locale decimal separator
On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns wrote: > On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns wrote: >> Hi, >> >> I need to convert some numerical values to text using the decimal >> separator that corresponds to the current locale. However, I do >> not want to lose information by padding with zero decimals or >> truncating zero decimals. So I basically want a text cast that >> also replaces the dot by a comma. I've looked at the to_char >> function and the formatting patterns, but when using those I >> either add or truncate zero decimals. >> >> >> # show lc_numeric; >> lc_numeric >> >> nl_NL.utf8 >> (1 row) >> >> # select 1.500::text; >> text >> --- >> 1.500 >> (1 row) >> >> # select to_char(1.500, '99D'); >> to_char >> -- >>1,5000 >> (1 row) >> >> # select to_char(1.500, 'FM99D'); >> to_char >> - >> 1,5 >> (1 row) >> > > Maybe its important to add that the nr of decimals in the values > is variable. I could of course adjust the pattern to work for > 1.500, but I'm looking for a solution that will work with an > arbitrary numerical value and that's a little more elegant than > casting to text, and then replacing the dot by a comma. > anyone? Giving a locale corresponding textual representation of a numerical value keeping the exact nr of decimal digits must be a fairly common use case. Would it be an idea to implement a to_char function that does not take a formatting pattern and has this behaviour? > >> >> I would like to have '1,500' as the output, what is the best way >> to achieve this? >> >> Thanks in advance, >> >> Ingmar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Trigger on VIEW not firing
Hi, I have a problem with Triggers on VIEW: suppose I have: CREATE TABLE work ( id integer NOT NULL, work TEXT, worktype TEXT ); CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate'; CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$ BEGIN RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME, TG_OP; END; $wrk_tg$ LANGUAGE plpgsql; CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub FOR EACH ROW EXECUTE PROCEDURE wrk_view(); nothing appen when I insert row in work table. Someone can help me?
Re: [GENERAL] Fwd: Trigger on VIEW not firing
2013/7/30 Massimo Costantini : > > Hi, > > I have a problem with Triggers on VIEW: > > suppose I have: > > CREATE TABLE work ( > id integer NOT NULL, > work TEXT, > worktype TEXT > ); > > CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate'; > > > CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$ > BEGIN > RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME, > TG_OP; > END; > $wrk_tg$ LANGUAGE plpgsql; > > CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub > FOR EACH ROW EXECUTE PROCEDURE wrk_view(); > > nothing appen when I insert row in work table. The trigger is on the view "worksub", not the "work" table. BTW the trigger function doesn't return anything, which will cause an error. (Also the view definition is missing column definitions in the SELECT clause). Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: Trigger on VIEW not firing
The trigger is on the view and fires when you query the view: # DELETE FROM worksub; NOTICE: UPDATE VIEW FROM: worksub OPERATION: DELETE ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function wrk_view() On Tue, Jul 30, 2013 at 6:10 PM, Massimo Costantini < massimo.costant...@gmail.com> wrote: > > Hi, > > I have a problem with Triggers on VIEW: > > suppose I have: > > CREATE TABLE work ( > id integer NOT NULL, > work TEXT, > worktype TEXT > ); > > CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate'; > > > CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$ > BEGIN > RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME, > TG_OP; > END; > $wrk_tg$ LANGUAGE plpgsql; > > CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub > FOR EACH ROW EXECUTE PROCEDURE wrk_view(); > > nothing appen when I insert row in work table. > > Someone can help me? > > -- * * Beena Emerson
Re: [GENERAL] Fwd: Trigger on VIEW not firing
Thisi is my real situation, can I do this: CREATE TABLE alarm( name text, id integer, type text, init timestamp, fired timestamp, end timestamp, user test ); CREATE TABLE car ( id integer, type text, speed double ); CREATE VIEW speedv AS SELECT * FROM car WHERE speed>100; CREATE OR REPLACE FUNCTION update_alarm_view() RETURNS TRIGGER AS $alarm_tg$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO alarm VALUES(NEW.type, 0,'SPEED',now(),NULL,NULL,''); RETURN NEW; ELSEIF (TG_OP = 'INSERT') THEN INSERT INTO alarm VALUES(NEW.type, 0,'SPEED',now(),NULL,NULL,''); RETURN NEW; END IF; END; $alarm_tg$ LANGUAGE plpgsql; CREATE TRIGGER alarm_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON speedv FOR EACH ROW EXECUTE PROCEDURE update_alarm_view(); On Tue, Jul 30, 2013 at 2:49 PM, Ian Lawrence Barwick wrote: > 2013/7/30 Massimo Costantini : > > > > Hi, > > > > I have a problem with Triggers on VIEW: > > > > suppose I have: > > > > CREATE TABLE work ( > > id integer NOT NULL, > > work TEXT, > > worktype TEXT > > ); > > > > CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate'; > > > > > > CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$ > > BEGIN > > RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME, > > TG_OP; > > END; > > $wrk_tg$ LANGUAGE plpgsql; > > > > CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub > > FOR EACH ROW EXECUTE PROCEDURE wrk_view(); > > > > nothing appen when I insert row in work table. > > The trigger is on the view "worksub", not the "work" table. > > BTW the trigger function doesn't return anything, which will cause an > error. > (Also the view definition is missing column definitions in the SELECT > clause). > > Regards > > Ian Barwick >
Re: [GENERAL] Fwd: Trigger on VIEW not firing
It works fine if you insert into the view: =# INSERT INTO speedv VALUES (1, 'test', 100); INSERT 0 1 =# SELECT * FROM alarm; name | id | type |init| fired | t_end | t_user --++---++---+---+ test | 0 | SPEED | 2013-07-30 18:08:01.006979 | | | (1 row) =# SELECT * FROM car; id | type | speed +--+--- (0 rows) =# SELECT * FROM speedv; id | type | speed +--+--- (0 rows) -- * * Beena Emerson
Re: [GENERAL] Fwd: Trigger on VIEW not firing
Hi again, IIUC you want to update the alarm table only when the speed limit is above 100. You cannot achieve it by the view and triggers you have written here because the trigger will be fired even for values < 100 =# INSERT INTO speedv VALUES (1, 'test', 10); INSERT 0 1 =# SELECT * FROM speedv; id | type | speed +--+--- (0 rows) =# SELECT * FROM car; id | type | speed +--+--- (0 rows) =# SELECT * FROM alarm; name | id | type |init| fired | t_end | t_user --++---++---+---+ test | 0 | SPEED | 2013-07-30 18:08:01.006979 | | | test | 0 | SPEED | 2013-07-30 18:20:00.73507 | | | (2 rows) If you want to update the alarm table for speed > 100 then use an if else clause in the trigger function: CREATE OR REPLACE FUNCTION update_alarm_view() RETURNS TRIGGER AS $alarm_tg$ BEGIN IF (new.speed > 100) THEN IF (TG_OP = 'UPDATE') THEN INSERT INTO alarm VALUES(NEW.type, 0,'SPEED',now(),NULL,NULL,''); ELSEIF (TG_OP = 'INSERT') THEN INSERT INTO alarm VALUES(NEW.type, 0,'SPEED',now(),NULL,NULL,''); END IF; END IF; RETURN new; END; $alarm_tg$ LANGUAGE plpgsql; And write the trigger on the car table. -- Beena Emerson
Re: [GENERAL] Fwd: Trigger on VIEW not firing
ok, thank to all On Tue, Jul 30, 2013 at 3:30 PM, Beena Emerson wrote: > Hi again, > > IIUC you want to update the alarm table only when the speed limit is above > 100. You cannot achieve it by the view and triggers you have written here > because the trigger will be fired even for values < 100 > > =# INSERT INTO speedv VALUES (1, 'test', 10); > INSERT 0 1 > > =# SELECT * FROM speedv; > id | type | speed > +--+--- > (0 rows) > > =# SELECT * FROM car; > id | type | speed > +--+--- > (0 rows) > > =# SELECT * FROM alarm; > name | id | type |init| fired | t_end | t_user > --++---++---+---+ > test | 0 | SPEED | 2013-07-30 18:08:01.006979 | | | > test | 0 | SPEED | 2013-07-30 18:20:00.73507 | | | > (2 rows) > > If you want to update the alarm table for speed > 100 then use an if else > clause in the trigger function: > > CREATE OR REPLACE FUNCTION update_alarm_view() RETURNS TRIGGER AS > $alarm_tg$ > BEGIN > IF (new.speed > 100) THEN >IF (TG_OP = 'UPDATE') THEN >INSERT INTO alarm VALUES(NEW.type, > 0,'SPEED',now(),NULL,NULL,''); >ELSEIF (TG_OP = 'INSERT') THEN > INSERT INTO alarm VALUES(NEW.type, > 0,'SPEED',now(),NULL,NULL,''); > END IF; > END IF; > RETURN new; > END; > $alarm_tg$ LANGUAGE plpgsql; > > And write the trigger on the car table. > > > > -- > > Beena Emerson > >
Re: [GENERAL] to_char with locale decimal separator
On 07/30/2013 03:03 AM, Ingmar Brouns wrote:
On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns wrote:
anyone? Giving a locale corresponding textual representation
of a numerical value keeping the exact nr of decimal digits
must be a fairly common use case. Would it be an idea to
implement a to_char function that does not take a formatting
pattern and has this behaviour?
Best I can do is a proof of concept in plpythonu for determining locale
decimal point:
test=# SHOW lc_numeric ;
lc_numeric
-
en_US.UTF-8
(1 row)
test=# DO $$
import locale
rs = plpy.execute("SHOW lc_numeric")
lc_n = rs[0]["lc_numeric"]
locale.setlocale(locale.LC_NUMERIC, lc_n)
d = locale.nl_langinfo(locale.RADIXCHAR)
plpy.notice("Decimal point is " + d)
$$ LANGUAGE plpythonu;
NOTICE: Decimal point is .
CONTEXT: PL/Python anonymous code block
DO
test=# set lc_numeric = 'nl_NL.utf8';
SET
test=# DO $$
import locale
rs = plpy.execute("SHOW lc_numeric")
lc_n = rs[0]["lc_numeric"]
locale.setlocale(locale.LC_NUMERIC, lc_n)
d = locale.nl_langinfo(locale.RADIXCHAR)
plpy.notice("Decimal point is " + d)
$$ LANGUAGE plpythonu;
NOTICE: Decimal point is ,
CONTEXT: PL/Python anonymous code block
DO
I would like to have '1,500' as the output, what is the best way
to achieve this?
Thanks in advance,
Ingmar
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Alter table never finishes
Hi, I need to do an alter table on a small table (~300 records), but it never ends. It may be because there are clients using that table. How can I force disconnect all clients to let me alter that table?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 351 6629292 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: corrupted files
On Mon, Jul 29, 2013 at 11:50 PM, Klaus Ita wrote: > I am trying to remember, there was a tool that plotted the contents of the > wal_files in a more readable format ... > xlogdump? https://github.com/snaga/xlogdump
Re: [GENERAL] Alter table never finishes
On Tue, Jul 30, 2013 at 7:50 AM, Leonardo M. Ramé wrote: > Hi, I need to do an alter table on a small table (~300 records), but it > never ends. It may be because there are clients using that table. > > How can I force disconnect all clients to let me alter that table?. > If you are using PostgreSQL 9.2+, you can use this: select pg_cancel_backend(pid) from pg_stat_activity where pid <> pg_backend_pid(); If you are using earlier version, replace "pid" with "procpid". That query will cancel all queries other than the session issuing the pg_cancel_backend() calls.
Re: [GENERAL] Fwd: corrupted files
Yes, that's it! thank you! It turned out that really there was a corruption in the main pg server which was 'virally' propagated to 1. streaming replica 1. replaying wal receiver 1. old backup that tried to replay the wal's I really thought with a master and 3 backups i'd be safe. lg,k On Tue, Jul 30, 2013 at 5:13 PM, bricklen wrote: > On Mon, Jul 29, 2013 at 11:50 PM, Klaus Ita wrote: > >> I am trying to remember, there was a tool that plotted the contents of >> the wal_files in a more readable format ... >> > > xlogdump? > > https://github.com/snaga/xlogdump >
Re: [GENERAL] Fwd: corrupted files
On Tue, Jul 30, 2013 at 8:18 AM, Klaus Ita wrote: > > thank you! It turned out that really there was a corruption in the main pg > server which was 'virally' propagated to > > 1. streaming replica > 1. replaying wal receiver > 1. old backup that tried to replay the wal's > > I really thought with a master and 3 backups i'd be safe. > Physical corruption in the master, or logical?
Re: [GENERAL] to_char with locale decimal separator
On Tue, Jul 30, 2013 at 4:42 PM, Adrian Klaver wrote:
> On 07/30/2013 03:03 AM, Ingmar Brouns wrote:
>>
>> On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns wrote:
>
>
>>>
>>
>> anyone? Giving a locale corresponding textual representation
>> of a numerical value keeping the exact nr of decimal digits
>> must be a fairly common use case. Would it be an idea to
>> implement a to_char function that does not take a formatting
>> pattern and has this behaviour?
>>
>
> Best I can do is a proof of concept in plpythonu for determining locale
> decimal point:
>
> test=# SHOW lc_numeric ;
> lc_numeric
> -
> en_US.UTF-8
> (1 row)
>
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is .
> CONTEXT: PL/Python anonymous code block
> DO
>
>
> test=# set lc_numeric = 'nl_NL.utf8';
> SET
> test=# DO $$
> import locale
> rs = plpy.execute("SHOW lc_numeric")
> lc_n = rs[0]["lc_numeric"]
> locale.setlocale(locale.LC_NUMERIC, lc_n)
> d = locale.nl_langinfo(locale.RADIXCHAR)
> plpy.notice("Decimal point is " + d)
> $$ LANGUAGE plpythonu;
> NOTICE: Decimal point is ,
> CONTEXT: PL/Python anonymous code block
> DO
>
Thanks for your time, appreciate it! As a dirty alternative, you could also do
something like:
select translate(5.000::text,'.',substr(to_char(.0),2,1));
Not so nice, but would work. Though I still feel there should be a more
elegant of doing this...
>
>
>>>
I would like to have '1,500' as the output, what is the best way
to achieve this?
Thanks in advance,
Ingmar
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: corrupted files
i guess logical, caused by whatever. i really cannot say, the wal files all *look* ok, still, they lead to a situation that's a definite dead end. we did have a hard-drive failure (one in 13) at the time, but due to raid5 + hot spare no data should have been corrupted. i mean it's an lsi controller, ... not fond of it, but it's not bad stuff. lg,k On Tue, Jul 30, 2013 at 5:29 PM, bricklen wrote: > > On Tue, Jul 30, 2013 at 8:18 AM, Klaus Ita wrote: > >> >> thank you! It turned out that really there was a corruption in the main >> pg server which was 'virally' propagated to >> >> 1. streaming replica >> 1. replaying wal receiver >> 1. old backup that tried to replay the wal's >> >> I really thought with a master and 3 backups i'd be safe. >> > > > Physical corruption in the master, or logical? > >
Re: [GENERAL] How get column-wise table info from an arbitrary query?
I really dislike ambiguous column names across tables in a database. Use the convention [tablename]_id for each id so every column name is self describing. That way you can: select * from providers inner join provider_types using(provider_type_id); No need for table aliases, column aliases and no ambiguity. Kenneth Tilton-2 wrote > Is there any way on an arbitrary query to determine column names qualified > by table aliases? You could use a query like this to get a list of fully qualified column names: SELECT pg_tables.tablename||'.'||columns.column_name as columnname FROM pg_tables,information_schema.columns columns WHERE pg_tables.tablename=columns.table_name AND pg_tables.schemaname='public' ORDER by pg_tables.tablename; you can amend that query to only look for columns of certain types, tie to primary keys of tables or indexes. PostgreSQL is really rather helpful in that regard. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Incorrect response code after XA recovery
Hi Alban, I stripped down the code to a raw XA example using the latest postgres driver available in maven central. It demonstrates that regardless of what the codebase might suggest, it is certainly the case that postgres is returning XAER_RMERR in the scenario where the resource manager no longer knows about the Xid. The code is available here: https://github.com/tomjenkinson/xa-recovery/commit/944d45e86a91eacb9489843acfbf6a80f1b4b820 I hope that this helps, Tom On Mon 29 Jul 2013 18:52:31 BST, Alban Hertroys wrote: On Jul 29, 2013, at 16:57, Tom Jenkinson wrote: Hi Tom, On Mon 29 Jul 2013 15:46:12 BST, Tom Lane wrote: Tom Jenkinson writes: A little bit of information in the linked bugzilla report is that the exception being returned has an XA error code of XAER_RMERR "An error occurred in rolling back the transaction branch. The resource manager is free to forget about the branch when returning this error so long as all accessing threads of control have been notified of the branch’s state." That does not sound right to me, wouldn't XAER_NOTA "The specified XID is not known by the resource manager" be more accurate? No idea, but in any case that's outside Postgres' purview. It's barely possible that the Postgres JDBC driver has something to do with that, but it sounds more like the XA manager's turf. I am not sure what you mean here as I don't know the structure of how the PostGres project is packaged, all I know is that the PostGres JDBC driver component appears to be returning an XAException with the message "Error rolling back prepared transaction" and an errorCode of XAException.XAER_RMERR rather than XAER_NOTA. Looking at the error codes, it appears that it isn't even the Postgres JDBC driver returning that error, but the XA manager you're using, which is not a part of Postgres (nor is the JDBC driver, for that matter - that's a separate project). The errors you're quoting are from the XA manager and are about XA manager stuff. For all we know, the actual error appears to be occuring in the XA manager and not in Postgres. It's possible that the XA manager error is a result of an error that Postgres returned, but since the XA manager prints its own error message and not the original one, you'll need to uncover those error messages before we can help you with them. For all we know at this point, the error is with your XA manager, not with Postgres. If you want to be sure, grep the source of the JDBC driver for those error codes; I doubt you'll find them in there. Google was kind enough to point me here: http://jdbc.postgresql.org/development/git.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alter table never finishes
How can I force disconnect all clients to let me alter that table?.
Regards,
There are two ways: the first|is based on pg_terminate_backend()
function and 'pg_stat_activity' catalog |||to kill idle processes.
So in a psql session type (tried on PostgreSQL 8.4):
==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed from
pg_stat_activity WHERE current_query LIKE '';
A more heavy handed approach then should be used on terminal, forcing
kill of idle processes using their pid:
:$ for x in `ps -ef | grep -e "postgres.*idle" | awk '{print $2}'`; do
kill -9 $x; done
Hope it can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
Re: [GENERAL] [BUGS] Incorrect response code after XA recovery
Tom Jenkinson escribió: > Hi Alban, > > I stripped down the code to a raw XA example using the latest > postgres driver available in maven central. It demonstrates that > regardless of what the codebase might suggest, it is certainly the > case that postgres is returning XAER_RMERR in the scenario where the > resource manager no longer knows about the Xid. > > The code is available here: > https://github.com/tomjenkinson/xa-recovery/commit/944d45e86a91eacb9489843acfbf6a80f1b4b820 Those error codes do certainly appear in the PGXAConnection.java source in the pgjdbc git. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alter table never finishes
On 2013-07-30 17:56:16 +0200, Giuseppe Broccolo wrote:
> >How can I force disconnect all clients to let me alter that table?.
> >
> >Regards,
>
> There are two ways: the first|is based on pg_terminate_backend()
> function and 'pg_stat_activity' catalog |||to kill idle processes.
> So in a psql session type (tried on PostgreSQL 8.4):
>
> ==# SELECT procpid, (SELECT pg_terminate_backend(procid)) AS killed
> from pg_stat_activity WHERE current_query LIKE '';
>
> A more heavy handed approach then should be used on terminal,
> forcing kill of idle processes using their pid:
>
> :$ for x in `ps -ef | grep -e "postgres.*idle" | awk '{print $2}'`;
> do kill -9 $x; done
>
> Hope it can help.
>
> Giuseppe.
>
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
>
Thanks to both, Giuseppe and Bricklen. As I have 9.2 I've used:
select pg_cancel_backend(pid) from pg_stat_activity where pid <>
pg_backend_pid();
And it returned this:
pg_cancel_backend
---
t
t
(2 rows)
But when I execute my update table command, it still never ends...Any
hint?.
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alter table never finishes
On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé wrote: > > select pg_cancel_backend(pid) from pg_stat_activity where pid <> > pg_backend_pid(); > > And it returned this: > > pg_cancel_backend > --- > t > t > (2 rows) > > But when I execute my update table command, it still never ends...Any > hint?. > > Sounds like locking issues. In another session -- other than the one you are trying to run your update, what does the following query show? SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.query AS waiting_query, waiting.mode AS waiting_mode, waiting.pidAS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.queryAS other_query, other.mode AS other_mode, other.pid AS other_pid, other.granted AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid = waiting.pid) JOIN pg_catalog.pg_locks AS other ON ((waiting."database" = other."database" AND waiting.relation = other.relation) OR waiting.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid) WHERE NOT waiting.granted AND waiting.pid <> other.pid;
Re: [GENERAL] Alter table never finishes
On 2013-07-30 10:26:39 -0700, bricklen wrote: > On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé wrote: > > > > > select pg_cancel_backend(pid) from pg_stat_activity where pid <> > > pg_backend_pid(); > > > > And it returned this: > > > > pg_cancel_backend > > --- > > t > > t > > (2 rows) > > > > But when I execute my update table command, it still never ends...Any > > hint?. > > > > > Sounds like locking issues. In another session -- other than the one you > are trying to run your update, what does the following query show? > > SELECT > waiting.locktype AS waiting_locktype, > waiting.relation::regclass AS waiting_table, > waiting_stm.query AS waiting_query, > waiting.mode AS waiting_mode, > waiting.pidAS waiting_pid, > other.locktype AS other_locktype, > other.relation::regclass AS other_table, > other_stm.queryAS other_query, > other.mode AS other_mode, > other.pid AS other_pid, > other.granted AS other_granted > FROM pg_catalog.pg_locks AS waiting > JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid = > waiting.pid) > JOIN pg_catalog.pg_locks AS other ON ((waiting."database" = > other."database" AND waiting.relation = other.relation) OR > waiting.transactionid = other.transactionid) > JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid) > WHERE NOT waiting.granted > AND waiting.pid <> other.pid; Sorry bricklen, I've killed all idle connections with "kill -9 ", then I was able to execute the alter table. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 351 6629292 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alter table never finishes
On Tue, Jul 30, 2013 at 10:29 AM, Leonardo M. Ramé wrote: > > > select pg_cancel_backend(pid) from pg_stat_activity where pid <> > > > pg_backend_pid(); > As Giuseppe mentioned, if you need to kill not just the queries, but the connection as well, you could use: select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid(); That is much safer than kill -9, which if issued against the parent postgres process will crash your cluster. > > Sorry bricklen, I've killed all idle connections with "kill -9 ", > then I was able to execute the alter table. > No problem.
Re: [GENERAL] Alter table never finishes
Leonardo M. Ramé escribió: > Sorry bricklen, I've killed all idle connections with "kill -9 ", > then I was able to execute the alter table. I don't think that was such a great idea. Once you killed the first one, postmaster terminated all other server processes, run recovery, and restarted service afresh. By the time you got to the second PID, it wouldn't have been there anyway. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reddwarf for PostgreSQL?
On 7/30/2013 12:38 AM, Tatsuo Ishii wrote: There is an opensource DaaS project called "RedDwarf". It seems the project is only for MySQL. Does anybody know if the project will support PostgreSQL in the future? you'd more likely get a useful answer from that project's mail list or forum or whatever they use. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alter table never finishes
On Tue, Jul 30, 2013 at 10:34 AM, bricklen wrote: > Sorry bricklen, I've killed all idle connections with "kill -9 ", > >> then I was able to execute the alter table. >> > > No problem. > btw, I meant 'no need to apologize that the queries were gone', not that I recommend "kill -9" (I don't!)
Re: [GENERAL] How get column-wise table info from an arbitrary query?
I use 'id' for the primary key, and [tablename]_id for each foreign key, I always qualify my column references in SQL, but I would never use SELECT * when selecting from more than one table. Cheers, Gavin On 30/07/13 21:41, sidthegeek wrote: I really dislike ambiguous column names across tables in a database. Use the convention [tablename]_id for each id so every column name is self describing. That way you can: select * from providers inner join provider_types using(provider_type_id); No need for table aliases, column aliases and no ambiguity. Kenneth Tilton-2 wrote Is there any way on an arbitrary query to determine column names qualified by table aliases? You could use a query like this to get a list of fully qualified column names: SELECT pg_tables.tablename||'.'||columns.column_name as columnname FROM pg_tables,information_schema.columns columns WHERE pg_tables.tablename=columns.table_name AND pg_tables.schemaname='public' ORDER by pg_tables.tablename; you can amend that query to only look for columns of certain types, tie to primary keys of tables or indexes. PostgreSQL is really rather helpful in that regard. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] to_char with locale decimal separator
On 07/30/2013 08:34 AM, Ingmar Brouns wrote: Thanks for your time, appreciate it! As a dirty alternative, you could also do something like: select translate(5.000::text,'.',substr(to_char(.0),2,1)); Not so nice, but would work. Though I still feel there should be a more elegant of doing this... Two issues. 1) Leading and trailing zeros tend to be a personal preference and what and how many, even more so. Pre-canned implementations for formatting seem to best guess. I could see where trying to cover every conceivable possibility would get complex. 2) You where looking at converting variable precision values, not uncommon, but adds a layer of complexity. For instance using the Python locale module it is possible to have it format a number according to locale and have trailing zeros, but it will pad to the specified precision(6 by default). So you still need to track something, in this case precision. You could take your quick and dirty solution and put it in a function(to_char_trailing(numeric) and hide the dirty part:) -- Adrian Klaver adrian.kla...@gmail.com -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How get column-wise table info from an arbitrary query?
Gavin Flower-2 wrote > I use 'id' for the primary key, and [tablename]_id for each foreign key, > I always qualify my column references in SQL, but I would never use > SELECT * when selecting from more than one table. > > > Cheers, > Gavin > > On 30/07/13 21:41, sidthegeek wrote: >> I really dislike ambiguous column names across tables in a database. Use >> the >> convention [tablename]_id for each id so every column name is self >> describing. That way you can: >> >> select * from providers inner join provider_types >> using(provider_type_id); >> >> No need for table aliases, column aliases and no ambiguity. >> I'm in the "prefix the id column" camp. I do not use "ORM" middle-ware so that may be a reason I do not have any difficulties but one of the big advantages to table-prefixing generic column names is that you can then make the assumption that any two columns with the same name represent the same data. It does make "SELECT *" more useful when running interactive queries and, more importantly, it makes using NATURAL JOIN and USING (...) much easier - and I hate using ON (...) to perform a join (and I never use the "FROM a, b WHERE a = b" cartesian join construct). To the original question introspection of dynamic SQL is not a strong point of PostgreSQL (cannot speak to other products). Given the nature of how a query works and the fact that columns can be created on-the-fly (i.e., not belonging to any schema) this is not surprising. You could try running and capturing the output of EXPLAIN with various options like JSON and VERBOSE and store that - it depends on your use-case. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765675.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How get column-wise table info from an arbitrary query?
On Tue, Jul 30, 2013 at 4:57 PM, David Johnston wrote: > I'm in the "prefix the id column" camp. I do not use "ORM" middle-ware so > that may be a reason I do not have any difficulties but one of the big > advantages to table-prefixing generic column names is that you can then make > the assumption that any two columns with the same name represent the same > data. It does make "SELECT *" more useful when running interactive queries > and, more importantly, it makes using NATURAL JOIN and USING (...) much > easier - and I hate using ON (...) to perform a join (and I never use the > "FROM a, b WHERE a = b" cartesian join construct). I would say: most of all it completely defeats useful text searching. Any ORM (or anything else) that enforces a primary key integer column named 'id' is bad technology and should be avoided. Schema should define the 'object model' not the other way around; I'd estimate that around 20-30% of my life's work has been cleaning up the various dreck left around by those who fail to grasp that basic principle. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How get column-wise table info from an arbitrary query?
On 31/07/13 09:57, David Johnston wrote: Gavin Flower-2 wrote I use 'id' for the primary key, and [tablename]_id for each foreign key, I always qualify my column references in SQL, but I would never use SELECT * when selecting from more than one table. Cheers, Gavin On 30/07/13 21:41, sidthegeek wrote: I really dislike ambiguous column names across tables in a database. Use the convention [tablename]_id for each id so every column name is self describing. That way you can: select * from providers inner join provider_types using(provider_type_id); No need for table aliases, column aliases and no ambiguity. I'm in the "prefix the id column" camp. I do not use "ORM" middle-ware so that may be a reason I do not have any difficulties but one of the big advantages to table-prefixing generic column names is that you can then make the assumption that any two columns with the same name represent the same data. It does make "SELECT *" more useful when running interactive queries and, more importantly, it makes using NATURAL JOIN and USING (...) much easier - and I hate using ON (...) to perform a join (and I never use the "FROM a, b WHERE a = b" cartesian join construct). To the original question introspection of dynamic SQL is not a strong point of PostgreSQL (cannot speak to other products). Given the nature of how a query works and the fact that columns can be created on-the-fly (i.e., not belonging to any schema) this is not surprising. You could try running and capturing the output of EXPLAIN with various options like JSON and VERBOSE and store that - it depends on your use-case. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765675.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. Hmm... I adopted the convention of just using 'id' for a table's primary key so I could easily distinguish betweenprimary & foreign keys, this was before I came across "ORM" middle-ware. Also, since I know what table I'm looking at, it seemed redundant to also specify the table name as part of the table's primary key! I've used dynamic SQL extensively in SyBase, but not yet needed to in Postgres - not that Postgres is 'better' in this regard, just didn't have the use case. Cheers, Gavin
Re: [GENERAL] How get column-wise table info from an arbitrary query?
Gavin Flower-2 wrote > Also, since I know what table > I'm looking at, it seemed redundant to also specify the table name as > part of the table's primary key! I find this quite the opposite approach. I know I am likely to use a primary key as a foreign key so making it "externally friendly" makes a lot of sense. Where redundancy gets me is all the other functionally dependent columns on the table and sometimes I'll relent and not prefix those since I don't plan to join on them and their "table" qualifier can be done as-needed when writing queries. Examples for non-prefixing are things like invoice open/close dates and sale/cost prices (I do work for retailers). Joining two tables, each having this kind of data, is uncommon and so leaving these attributes plain is reasonable. Besides key fields I also prefix record timestamp fields (and similar) with the table prefix since many/most tables can/should have a timestamp and if you name every single timestamp column "tstamp" you can never use a NATURAL JOIN - though you can still use USING(...) but in that case you still have duplicate output column names. It is not the difficult in most cases to distinguish between primary and foreign keys - in pretty much any context. Column order often suffices and whether a column is used in a primary or foreign context is separate from the meaning of said column data and meaning does not change so the name should not either. The really tricky part is that I prefer to abbreviate the more commonly used table prefixes (and the really long ones) so generally either interpolation or verbosity is needed for any given ID but it is a small price compared to the sanity it provides. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765681.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Snapshot backups
I understand what you are saying, and I understand how the backup_label
works - but I still don't understand why the pg_start and pg_stop commands
are REQUIRED when doing a snapshot backup to ensure data integrity.
Surely not using them and restoring a snapshot is the same as starting
after a crash, and will result in log replay to get to the latest possible
consistent state?
I thought PostgreSQL guaranteed a consistent state after a crash, am I
mistaken about this?
James
James Sewell
PostgreSQL Team Lead / Solutions Architect
_
[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]
Level 2, 50 Queen St,
Melbourne, VIC, 3000
P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com
On Mon, Jul 29, 2013 at 4:54 PM, Amit Langote wrote:
>
> On Mon, Jul 29, 2013 at 3:32 PM, James Sewell
> wrote:
>
>> Hey all,
>>
>> I understand that I have already been given an answer here, but I am
>> still curious as to why this is the case (perhaps I should ask this on the
>> hackers list though, if so let me know).
>>
>> More importantly I'd like to understand why I would need to use the
>> start/stop backup commands to ensure a valid backup when using filesystem
>> snapshots (assuming I get the order correct)- worst case scenario wouldn't
>> it be the same as a crash and cause an automatic roll-forward?
>>
>>
>>
> pg_start_backup('backup_label') and pg_stop_backup(), if I understand it
> correctly, write to the 'backup_label' file the information necessary to
> recover "consistently" from that backup. For example, backup_label file
> contains the checkpoint location and its REDO location (identified as "START
> WAL LOCATION:" field in the backup_label file.) While you are reading the
> code, you can read the comment above the function read_backup_label()
> in src/backend/access/transam/xlog.c
>
>
> --
> Amit Langote
>
--
--
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.
<>
[GENERAL] more fun with building 9.3beta2
I'm not getting the xml2 and uuid-ossp control files delivered to the extension directory I've moved to a CentOS box (and dropped pam): cat /etc/system-release CentOS release 6.4 (Final) uname -a Linux co-app-jl-d001 2.6.32-358.11.1.el6.x86_64 #1 SMP Wed Jun 12 03:34:52 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux pg_config --configure '--prefix=/usr/local/pgsql-9.3b2' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-python' make world says "PostgreSQL, contrib, and documentation successfully made. Ready to install." From "make install" I see only plpython[2]u.control and sql shipped to /usr/local/pgsql-9.3b2/share/extension (though pgplsql files are there as well). template1=# select * from pg_available_extensions() template1-# ; name| default_version | comment +-+--- plpgsql| 1.0 | PL/pgSQL procedural language plpythonu | 1.0 | PL/PythonU untrusted procedural language plpython2u | 1.0 | PL/Python2U untrusted procedural language (3 rows) Any hints appreciated. rjs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] more fun with building 9.3beta2
On 07/30/2013 07:15 PM, Rob Sargent wrote: I'm not getting the xml2 and uuid-ossp control files delivered to the extension directory I've moved to a CentOS box (and dropped pam): cat /etc/system-release CentOS release 6.4 (Final) uname -a Linux co-app-jl-d001 2.6.32-358.11.1.el6.x86_64 #1 SMP Wed Jun 12 03:34:52 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux pg_config --configure '--prefix=/usr/local/pgsql-9.3b2' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-python' make world says "PostgreSQL, contrib, and documentation successfully made. Ready to install." From "make install" I see only plpython[2]u.control and sql shipped to /usr/local/pgsql-9.3b2/share/extension (though pgplsql files are there as well). template1=# select * from pg_available_extensions() template1-# ; name| default_version | comment +-+--- plpgsql| 1.0 | PL/pgSQL procedural language plpythonu | 1.0 | PL/PythonU untrusted procedural language plpython2u | 1.0 | PL/Python2U untrusted procedural language (3 rows) Any hints appreciated. http://www.postgresql.org/docs/9.3/static/install-procedure.html If you built the world above, type instead: gmake install-world This also installs the documentation. rjs -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Snapshot backups
On Tuesday, July 30, 2013, James Sewell wrote: > I understand what you are saying, and I understand how the backup_label > works - but I still don't understand why the pg_start and pg_stop commands > are REQUIRED when doing a snapshot backup to ensure data integrity. > > Surely not using them and restoring a snapshot is the same as starting > after a crash, and will result in log replay to get to the latest possible > consistent state? > That's true provided that all of your data is on a single volume, and you trust your "snapshot" to be free of bugs. Cheers, Jeff >
Re: [GENERAL] Snapshot backups
Jeff Janes writes: > On Tuesday, July 30, 2013, James Sewell wrote: >> I understand what you are saying, and I understand how the backup_label >> works - but I still don't understand why the pg_start and pg_stop commands >> are REQUIRED when doing a snapshot backup to ensure data integrity. >> >> Surely not using them and restoring a snapshot is the same as starting >> after a crash, and will result in log replay to get to the latest possible >> consistent state? > That's true provided that all of your data is on a single volume, and you > trust your "snapshot" to be free of bugs. James stated to begin with that his data was spread across multiple volumes, so it's definitely not safe for him to omit pg_start_backup/pg_stop_backup. Perhaps it would help to consider what can happen when you're not using filesystem snapshots at all, but just an ordinary userspace backup program such as "tar". The difference between recovering from a tar backup and crash recovery is that, because the backup is taken over an extended period of time, it may contain a set of data that does not match any possible instantaneous state of the on-disk data --- and crash recovery only promises to deal with the latter. Here is a concrete example of what can happen: 1. The tar process copies the file for table foo. There are changes to foo in Postgres' shared buffers that haven't made it to disk yet (although those changes are committed and recorded in on-disk WAL), so the copy made by tar isn't entirely up to date. 2. PG's checkpoint process starts a checkpoint run. Along the way, it flushes out the changes to table foo. When done, it updates the last-checkpoint pointer in pg_control, which tells where crash recovery would need to start replaying WAL. 3. The tar process archives pg_control. Now, if you restore the tar backup onto a new system and start up Postgres, you will have an obsolete copy of table foo --- and WAL replay will not apply the needed updates to foo, because it will start from the point in WAL that pg_control says it should start from, and that's after the WAL records that describe the missing changes. Note that this will fail even if you assume you've got perfectly good and complete copies of the WAL files; there's a whole 'nother set of hazards if you don't. For recovery from a tar backup to work, the archived copy of pg_control must point to a spot in the WAL sequence that is before any changes that could possibly not yet appear in any archived data files. The purpose of pg_start_backup/pg_stop_backup is to provide the synchronization needed to meet this requirement. The comparable case isn't possible for crash recovery, assuming that the OS and storage hardware implement fsync() correctly, because we'll have fsync'd the changes to foo down to disk before updating pg_control. Now, if you instead take a filesystem snapshot (representing some instantaneous state of the disk contents) and run "tar" to copy that, you have a good backup, because you must have a copy of pg_control that will tell you to re-apply any changes that are missing from the data files, as well as WAL files that contain the needed records. However, this is only certain if all that data is on *one* filesystem, because otherwise you can't be sure you have mutually consistent snapshots. And you're vulnerable to any bugs in the filesystem's snapshot implementation that might give you inconsistent copies of different files. (Such bugs would probably be closely related to bugs in fsync ... but that doesn't mean they're necessarily exactly the same.) So that's the long form of Jeff's comment above. Any clearer now? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
