Re: [GENERAL] checkpoints anatomy

[Achilleas Mantzios]

http://dba.stackexchange.com/questions/61822/what-happens-in-postgresql-checkpoint

and the now classic :
http://www.depesz.com/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-checkpoint_timeout-checkpoint_warning/

On 12/10/2015 04:39, Richardson Hinestroza wrote:

Hello, excuse me for my poor english. i am writting from Colombia and i am 
postgresql fan.

I want to know if postgresql checkpoints prevent current transactions to write 
the same page being flush to disk by checkpoint proccess.

And I want know if the postgresql checkpoint use the ARIES algorithmo. and 
known technical details about postgresql checkpoints.

i can not foud in the web answers for my question.

i would apreciate your answer. thanks a lot



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

[rob stone]

 
> ETO::0::LOG:  0: connection received: host=127.0.0.1
> port=1083
> ETO::0::LOCATION:  BackendInitialize,
> src\backend\postmaster\postmaster.c:3850
> ETO::0::LOG:  0: connection authorized: user=its-eto_pg36
> database=eto_sql_db
> ETO::0::LOCATION:  PerformAuthentication,
> src\backend\utils\init\postinit.c:239
> ETO::0::LOG:  0: statement: set client_encoding to 'LATIN1'
> ETO::0::LOCATION:  exec_simple_query,
> src\backend\tcop\postgres.c:890
> ETO::0::LOG:  0: duration: 63.000 ms
> ETO::0::LOCATION:  exec_simple_query,
> src\backend\tcop\postgres.c:1118
> ETO::0::LOG:  0: statement: BEGIN;
> ETO::0::LOCATION:  exec_simple_query,
> src\backend\tcop\postgres.c:890
> ETO::0::LOG:  0: duration: 0.000 ms
> ETO::0::LOCATION:  exec_simple_query,
> src\backend\tcop\postgres.c:1118
> ETO::0::LOG:  0: disconnection: session time: 0:00:00.297
> user=its-eto_pg36 database=eto_sql_db host=127.0.0.1 port=1083
> ETO::0::LOCATION:  log_disconnections,
> src\backend\tcop\postgres.c:
> * * *
> * * *
>  
> Thanks For Any Feedback,
>  
> Steve

Hello Steve,

I really do not understand "why" you need the SQLSTATE code after
executing a "BEGIN" so as to go into transaction state.

AFAIK you can only retrieve the SQLSTATE error code when an error
actually occurs. So, if your query statement was successful, then PHP
does not expose a SQLSTATE code of 0.

If I run the following code:-

--


--

it returns the following:-


ERROR:  42P01: relation "rhubarb" does not exist
LINE 1: SELECT * FROM rhubarb
  ^
LOCATION:  parserOpenTable, parse_relation.c:986


SQLSTATE 42P01 is the error "undefined_table".


Note that you have to use pg_send_query to take advantage of
pg_get_result, etc.


HTH,

Rob



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

[GENERAL] checkpoints anatomy

[Richardson Hinestroza]

Hello, excuse me for my poor english. i am writting from Colombia and i am
postgresql fan.

I want to know if postgresql checkpoints prevent current transactions to
write the same page being flush to disk by checkpoint proccess.

And I want know if the postgresql checkpoint use the ARIES algorithmo. and
known technical details about postgresql checkpoints.

i can not foud in the web answers for my question.

i would apreciate your answer. thanks a lot

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

[Victor Blomqvist]

On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver 
wrote:

> On 10/09/2015 08:30 PM, Victor Blomqvist wrote:
>
>> Note that these errors most of the time only happens very briefly at the
>> same time as the ALTER is run. When I did some experiments today the
>> server in total had around 3k req/s with maybe 0.1% of them touching the
>> table being updated, and the error then happens maybe 1-10% of the times
>> I try this operation. If I do the operation on a table with more load
>> the error will happen more frequently.
>>
>
> Out of curiosity more then any else, what happens if you ADD a column
> instead of DROP a column in the experiment?
>

The same behaviour. (Actually its more annoying than when it happens with
DROPs since we do ADDs much more often)


>
>
>> Also, someone suggested me to try and recreate the functions returning
>> the table as well inside a transaction, but that did not change anything:
>> BEGIN;
>> ALTER TABLE...
>> CREATE OR UPDATE FUNCTION ...
>> END;
>>
>> Thanks for your help so far!
>> /Victor
>>
>> On Fri, Oct 9, 2015 at 10:49 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/09/2015 07:31 AM, Albe Laurenz wrote:
>>
>> Adrian Klaver wrote:
>>
>> For the reason why this is happening see:
>>
>>
>> http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>>
>>
>> Yes, but the ALTER TABLE causes the plan to be recreated
>> the next time.
>>
>>
>> But does it? From the link above:
>>
>> "Because PL/pgSQL saves prepared statements and sometimes
>> execution
>> plans in this way, SQL commands that appear directly in a
>> PL/pgSQL
>> function must refer to the same tables and columns on every
>> execution;
>> that is, you cannot use a parameter as the name of a table
>> or column in
>> an SQL command. To get around this restriction, you can
>> construct
>> dynamic commands using the PL/pgSQL EXECUTE statement — at
>> the price of
>> performing new parse analysis and constructing a new
>> execution plan on
>> every execution."
>>
>> I see '*' as a parameter. Or to put it another way '*' is
>> not referring
>> to the same thing on each execution when you change the
>> table definition
>> under the function.  Now if I can only get the brain to wake
>> up I could
>> find the post where Tom Lane explained this more coherently
>> then I can:)
>>
>>
>> Session 1:
>>
>> test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar
>> NOT NULL, to_be_removed integer NOT NULL);
>> CREATE TABLE
>> test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF
>> users AS
>>  $$BEGIN RETURN QUERY SELECT * FROM users WHERE id =
>> id_; END;$$ LANGUAGE plpgsql;
>> CREATE FUNCTION
>>
>> Session 2:
>>
>> test=> SELECT id, name FROM select_users(18);
>>id | name
>> +--
>> (0 rows)
>>
>> Ok, now the plan is cached.
>>
>> Now in Session 1:
>>
>> test=> ALTER TABLE users DROP COLUMN to_be_removed;
>> ALTER TABLE
>>
>> Session2:
>>
>> test=> SELECT id, name FROM select_users(18);
>>id | name
>> +--
>> (0 rows)
>>
>> No error.  This is 9.4.4.
>>
>>
>> I stand corrected. I also tried on Postgres 9.3.7, which is a close
>> as I could get to OP's 9.3.5 and it worked. Will have to rethink my
>> assumptions.
>>
>>
>>
>> Yours,
>> Laurenz Albe
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] There can be only one

[Andreas Kretschmer]

Andreas Kretschmer  wrote:

> Create a partial unique index on is_default.

as an example:


test=# CREATE TABLE payment_via (
  idint PRIMARY KEY,
  provider  text NOT NULL,
  keys  hstore NOT NULL DEFAULT '',
  is_defaultboolean NOT NULL DEFAULT FALSE
);
CREATE TABLE
test=*# create unique index idx_default on payment_via(is_default) where
is_default;
CREATE INDEX
test=*# insert into payment_via values (1, 'foo','', true);
INSERT 0 1
test=*# insert into payment_via values (2, 'bla','', false);
INSERT 0 1
test=*# insert into payment_via values (3, 'blubb','', true);
ERROR:  duplicate key value violates unique constraint "idx_default"
DETAIL:  Key (is_default)=(t) already exists.
test=*#




Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] There can be only one

[Andreas Kretschmer]

Create a partial unique index on is_default.

Am 11. Oktober 2015 09:41:08 MESZ, schrieb Jason Dusek :
>Consider a table of providers, for which one is the default. For
>example,
>payment providers:
>
>CREATE TABLE payment_via (
>  iduuid PRIMARY KEY,
>  provider  text NOT NULL,
>  keys  hstore NOT NULL DEFAULT ''
>);
>
>Here we store together the name of the provider — medici, paypal — with
>access tokens needed to use a certain payment account. How shall we
>store
>which one is the default? Ideally, we’d be able to ensure there is *but
>one*
>default.
>
>CREATE TABLE payment_via (
>  iduuid PRIMARY KEY,
>  provider  text NOT NULL,
>  keys  hstore NOT NULL DEFAULT '',
>  is_defaultboolean NOT NULL DEFAULT FALSE
>);
>
>How shall we state the constraint? The obvious thing would seem to be:
>
>CREATE TABLE payment_via (
>  iduuid PRIMARY KEY,
>  provider  text NOT NULL,
>  keys  hstore NOT NULL DEFAULT '',
>  is_defaultboolean NOT NULL DEFAULT FALSE,
>  EXCLUDE (is_default USING AND)
>);
>
>However, this is a syntax error. There is always:
>
>CREATE TABLE payment_via (
>  iduuid PRIMARY KEY,
>  provider  text NOT NULL,
>  keys  hstore NOT NULL DEFAULT '',
>  is_defaultboolean NOT NULL DEFAULT FALSE,
>  EXCLUDE (is_default USING =) WHERE (is_default)
>);
>
>but this seems awkward and I was hoping there was some way to use AND
>as an
>operator.
>​

-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

[GENERAL] There can be only one

[Jason Dusek]

Consider a table of providers, for which one is the default. For example,
payment providers:

CREATE TABLE payment_via (
  iduuid PRIMARY KEY,
  provider  text NOT NULL,
  keys  hstore NOT NULL DEFAULT ''
);

Here we store together the name of the provider — medici, paypal — with
access tokens needed to use a certain payment account. How shall we store
which one is the default? Ideally, we’d be able to ensure there is *but one*
default.

CREATE TABLE payment_via (
  iduuid PRIMARY KEY,
  provider  text NOT NULL,
  keys  hstore NOT NULL DEFAULT '',
  is_defaultboolean NOT NULL DEFAULT FALSE
);

How shall we state the constraint? The obvious thing would seem to be:

CREATE TABLE payment_via (
  iduuid PRIMARY KEY,
  provider  text NOT NULL,
  keys  hstore NOT NULL DEFAULT '',
  is_defaultboolean NOT NULL DEFAULT FALSE,
  EXCLUDE (is_default USING AND)
);

However, this is a syntax error. There is always:

CREATE TABLE payment_via (
  iduuid PRIMARY KEY,
  provider  text NOT NULL,
  keys  hstore NOT NULL DEFAULT '',
  is_defaultboolean NOT NULL DEFAULT FALSE,
  EXCLUDE (is_default USING =) WHERE (is_default)
);

but this seems awkward and I was hoping there was some way to use AND as an
operator.
​