Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

[Alexey Bashtanov]

Hello Kevin,

On 06/04/16 23:22, Kevin Burke wrote:
/Why mess around with DISABLE/ENABLE TRIGGER instead of TRUNCATE?/ We 
observed that TRUNCATE took about 200ms, but this method takes about 
13ms. Over a our test suite TRUNCATE is going to more or less double 
the length of the suite.


We could resolve some kind of foreign key dependency tree, issue the 
DELETE FROM's in the right order, and skip DISABLE TRIGGER etc., but 
haven't invested the time to do this or figured out how to maintain 
it/update it as we add new tables.
I'd suggest one more method: SET CONSTRAINTS ALL DEFERRED and DELETE 
FROM all tables in arbitrary order (will require making constraints 
deferrable)
Specifically we kick off the ALTER TABLE ... DELETE, a second later 
(our deadlock_timeout) a log message appears that the ALTER TABLE is 
waiting for an AccessExclusiveLock held by process 16936... which is 
later revealed to be an autovacuum process of the "events" table.
this only indicates that 16936 does not allow ALTER TABLE to 
acquireAccessExclusiveLock
this does not necessarily mean that 16936 has acquired 
AccessExclusiveLock, it means that 16936 has acquired some lock 
conflicting with AccessExclusiveLock you can use this table 
 
to determine what locks are conflicting one with another Regards, Alexey 
Bashtanov

Re: [GENERAL] what database schema version management system to use?

[Berend Tober]

John R Pierce wrote:

On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:

I am searching for a proper database schema version management system.



At my $job we're old school. our schemas are versioned. there's a
settings table with (setting TEXT, value TEXT) fields, a row in that is
('version', '1.0')  or whatever.

each new release of the schema is released as a .SQL file which builds
the full schema from scratch, and a .SQL file which updates the previous
version to the new version. the full build and update .sql files are
kept in our source code control along with the rest of our software.
we're quite careful about how we modify our schema so it can be done
online, update the schema on the live database, then update and restart
the application/middleware.




I would be interested in knowing specifically how the ".SQL file which 
updates the previous version to the new version" is generated. Is there 
a tool that does that based on the difference between new and old? Or is 
that update script coded by hand?






--
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] what database schema version management system to use?

[Karsten Hilbert]

On Thu, Apr 07, 2016 at 06:21:10AM -0400, Berend Tober wrote:

> I would be interested in knowing specifically how the ".SQL file which
> updates the previous version to the new version" is generated. Is there a
> tool that does that based on the difference between new and old? Or is that
> update script coded by hand?

We (GNUmed) create it manually.

In fact, those scripts are a by-product of sane database
layout development. Starting from what is, developers write
scripts which modify the layout to what shall be.

It doesn't seem to be good practice to do
point-and-click-based "development" of databases.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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

[GENERAL] Non-default postgresql.conf values to log

[Alex Ignatov]

Hello!
For example in oracle alert.log file we have:
...
System parameters with non-default values:
  processes= 300
  nls_language = "AMERICAN"
  nls_territory= "RUSSIA"
  memory_target= 720M
  control_files= "/ora/oradata/orcl/control01.ctl"
...
My question is: is there any option(s) to log non-default 
postgresql.conf values to log file?




--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
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] Trying to understand page structures in PG

[Rakesh Kumar]

On Wed, Apr 6, 2016 at 6:33 AM, Albe Laurenz  wrote:
> Rakesh Kumar wrote:

> Every row has two system columns associated with it: xmin and xmax
>
> xmin is the transaction ID that created the row, while xmax is
> the transaction ID that removed the row.
>
> So when an update takes place, xmax of the original row and xmin
> of the new row are set to the transaction ID of the current transaction.
>
> Furthermore, the commit log (CLOG) logs for each transaction whether
> it was committed or rolled back.
>
> Now when a backend examines a row, it first checks if the row is
> visible, i.e. xmin must be less or equal to the current transaction ID
> and xmax must be 0 or greater than the current transaction ID
> or belong to a transaction that was rolled back.
>
> To save CLOG lookups, the first reader who consults the CLOG will
> save the result in so-called "hint bits" on the row itself.

I am assuming the same must be true for the indexes also. Does PG look
up primary key
by examining the rows like you described above.


-- 
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] Problem after replication switchover

[Lars Arvidson]

> If it's just storing the logs, I doubt it's the cause of the problem. You can 
> ignore my message. I had too much fun fighting with Gluster recently.
Hehe, hope you came through on top ;).  Anyways, I added an md5sum calculation 
in the archiving script just to be able to verify that the files don't get 
corrupted. From what I can tell this far they have not been corrupted but I'll 
keep an eye on it.

> I reread your original full post, and the one thing that stuck out for me was 
> "the clusters are now replicating from each other". I feel like that could be 
> a problem. But someone more intimate with the replication might want to input 
> on that.
Would be nice with some input on that. I do think it is the way to go for 
ensuring that two cluster are in synch when switching master (although I cannot 
remember the source of that information). Josh Berkus writes a bit about cycles 
in replication 
(http://www.databasesoup.com/2013/01/cascading-replication-and-cycles.html) and 
writes "temporary cycle is a legitimate part of a transition between two 
different replication setups".

> Other than that, I wonder if you just have a hardware problem with your 
> storage.
We use fusionio ssd cards for storage. If there is something wrong with them I 
would expect more problems than I am currently experiencing, and more random 
problems...

/Lars


-- 
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] Non-default postgresql.conf values to log

[Christoph Moench-Tegeder]

## Alex Ignatov (a.igna...@postgrespro.ru):

> My question is: is there any option(s) to log non-default 
> postgresql.conf values to log file?

Depending on your use case, I'd start with a simple query:
SELECT name, setting FROM pg_settings WHERE source <> 'default'
Wrap that in a COPY, or perhaps it's good enough to have it in
the database at all times anyway...

Regards,
Christoph

-- 
Spare Space


-- 
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] database is not accepting commands to avoid wraparound data loss in database

[Merlin Moncure]

On Wed, Apr 6, 2016 at 10:13 AM, pedrovlf  wrote:
> Hi,
>
> I'm following error in my DBMS,
>
> < 2016-04-05 17:02:42.053 BRT >ERROR:  database is not accepting commands to
> avoid wraparound data loss in database "zabbix"
> < 2016-04-05 17:02:42.053 BRT >HINT:  Stop the postmaster and vacuum that
> database in single-user mode.
>
>
> I'm running the vacuum in single mode, but is taking too long ... you can
> retrieve the base otherwise? Perhaps with truncate on a table ...

Also, how long is too long, and how big is this table?

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] PostgreSQL 9.5.1 -> PG_REWIND NOT FOUND

[Adrian Klaver]

On 04/06/2016 11:31 PM, sgringer wrote:

I have found it in this folder /usr/lib/postgresql/9.5/bin
the command  "pg_rewind" don't work globally like othe pg


That is where the other Postgres commands are located also, correct?

So can you access pg_dump, for example?


--
Adrian Klaver
adrian.kla...@aklaver.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] Non-default postgresql.conf values to log

[Tom Lane]

Alex Ignatov  writes:
> My question is: is there any option(s) to log non-default 
> postgresql.conf values to log file?

No, but you can easily find all the non-default settings by querying
the pg_settings view.

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

Re: [GENERAL] Non-default postgresql.conf values to log

[Alex Ignatov]

On 07.04.2016 16:53, Tom Lane wrote:

Alex Ignatov  writes:

My question is: is there any option(s) to log non-default
postgresql.conf values to log file?

No, but you can easily find all the non-default settings by querying
the pg_settings view.

regards, tom lane


Yeah, i know it but pg_settings is available only after pg is ready to 
accept client connections.
Also in some cases log file is placed on another server say, syslog. And 
if your pg server is not available you cant say anything about your 
pg_settings.


--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
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] Non-default postgresql.conf values to log

[Rob Sargent]

> On Apr 7, 2016, at 8:26 AM, Alex Ignatov  wrote:
> 
> 
> 
>> On 07.04.2016 16:53, Tom Lane wrote:
>> Alex Ignatov  writes:
>>> My question is: is there any option(s) to log non-default
>>> postgresql.conf values to log file?
>> No, but you can easily find all the non-default settings by querying
>> the pg_settings view.
>> 
>>regards, tom lane
> 
> Yeah, i know it but pg_settings is available only after pg is ready to accept 
> client connections.
> Also in some cases log file is placed on another server say, syslog. And if 
> your pg server is not available you cant say anything about your pg_settings.

At that point you're looking in a file: log file or config file. Does it matter 
which?
> -- 
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] 9.5 new features

[Alvaro Herrera]

I just noticed this old thread.

Thomas Kellerer wrote:

> > 62.1. Introduction
> > ...
> > "A block range is a group of pages that are physically adjacent in the 
> > table; for each block range, some summary info is stored by the index."
> > 
> > From the above, may I presume that it is best to cluster (or sort),
> > the table based on the intended BRIN column(s) before actually
> > creating the index to insure the pages are adjacent? If so, should
> > that not be included in the documentation, instead of implied?
> 
> That is something I am also curious about. If that was true, it would
> mean that BRIN indexes couldn't be used on tables that are not
> clustered along the index

There's no hard requirement that values must be clustered.  If the
values are clustered, that's the best case scenario for BRIN and things
will be very quick.  However, clustering a table is a slow operation and
requires locking the table, so I don't recommend that.

But strict correlation isn't really necessary either -- you just need
the values to be grouped together.  To illustrate, consider this
simplistic case: table has four pages, all the values in the first page
have col1=999, page 2 has all col1=1, page 3 has all col1=1500, page 4
has col1=-1000.  There's little correlation there, but a BRIN index with
pages_per_range=1 can still help a query that looks for col1 > 500
execute optimally.

> it wouldn't make sense to have more than one BRIN index.

Well, you can put all the columns in a single index, and it works just
like if you had one index for each column.  However, if you want a BRIN
index that's more detailed for certain columns than others, you can use
different pages_per_range settings on multiple indexes.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Non-default postgresql.conf values to log

[Alex Ignatov]

On 07.04.2016 17:59, Rob Sargent wrote:



On Apr 7, 2016, at 8:26 AM, Alex Ignatov  wrote:




On 07.04.2016 16:53, Tom Lane wrote:
Alex Ignatov  writes:

My question is: is there any option(s) to log non-default
postgresql.conf values to log file?

No, but you can easily find all the non-default settings by querying
the pg_settings view.

regards, tom lane

Yeah, i know it but pg_settings is available only after pg is ready to accept 
client connections.
Also in some cases log file is placed on another server say, syslog. And if 
your pg server is not available you cant say anything about your pg_settings.

At that point you're looking in a file: log file or config file. Does it matter 
which?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



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


It matter when your pg host is down and all you have is log file. It is 
very usual situation. You need historical info.


--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
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] what database schema version management system to use?

[John R Pierce]

On 4/7/2016 3:21 AM, Berend Tober wrote:

John R Pierce wrote:

On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:

I am searching for a proper database schema version management system.



At my $job we're old school. our schemas are versioned. there's a
settings table with (setting TEXT, value TEXT) fields, a row in that is
('version', '1.0')  or whatever.

each new release of the schema is released as a .SQL file which builds
the full schema from scratch, and a .SQL file which updates the previous
version to the new version. the full build and update .sql files are
kept in our source code control along with the rest of our software.
we're quite careful about how we modify our schema so it can be done
online, update the schema on the live database, then update and restart
the application/middleware.




I would be interested in knowing specifically how the ".SQL file which 
updates the previous version to the new version" is generated. Is 
there a tool that does that based on the difference between new and 
old? Or is that update script coded by hand? 



by hand, with a text editor of choice.



--
john r pierce, recycling bits in santa cruz



--
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] Non-default postgresql.conf values to log

[David G. Johnston]

On Thu, Apr 7, 2016 at 10:06 AM, Alex Ignatov 
wrote:

> It matter when your pg host is down and all you have is log file. It is
> very usual situation. You need historical info.


​I think this would be a good idea.  Is it something you are thinking of
writing now that you see PostgreSQL does not have the capability?  I'd
imagine it would default to OFF and/or would only happen when/if the
database couldn't start successfully.

David J.

Re: [GENERAL] Non-default postgresql.conf values to log

[David G. Johnston]

On Thu, Apr 7, 2016 at 10:37 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Apr 7, 2016 at 10:06 AM, Alex Ignatov 
> wrote:
>
>> It matter when your pg host is down and all you have is log file. It is
>> very usual situation. You need historical info.
>
>
> ​I think this would be a good idea.  Is it something you are thinking of
> writing now that you see PostgreSQL does not have the capability?  I'd
> imagine it would default to OFF and/or would only happen when/if the
> database couldn't start successfully.
>
>
​Admittedly, I'm not sure how you would fix any problems without access to
the server and its config files - at which point you are back to simply
reviewing those.

I'm not sure how much merit "historical info" has and how one would
configure the server to ensure that said info is available when/if
eventually needed.
​
David J.

Re: [GENERAL] Non-default postgresql.conf values to log

[Adrian Klaver]

On 04/07/2016 10:06 AM, Alex Ignatov wrote:



On 07.04.2016 17:59, Rob Sargent wrote:



On Apr 7, 2016, at 8:26 AM, Alex Ignatov 
wrote:




On 07.04.2016 16:53, Tom Lane wrote:
Alex Ignatov  writes:

My question is: is there any option(s) to log non-default
postgresql.conf values to log file?

No, but you can easily find all the non-default settings by querying
the pg_settings view.

regards, tom lane

Yeah, i know it but pg_settings is available only after pg is ready
to accept client connections.
Also in some cases log file is placed on another server say, syslog.
And if your pg server is not available you cant say anything about
your pg_settings.

At that point you're looking in a file: log file or config file. Does
it matter which?

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



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



It matter when your pg host is down and all you have is log file. It is
very usual situation. You need historical info.


So you are logging to another computer, otherwise if the host is down 
how are you getting the log?


In any case Postgres does log changes to parameters in its log file:

-2016-04-07 10:39:21.868 PDT-0LOG:  received SIGHUP, reloading 
configuration files
-2016-04-07 10:39:21.869 PDT-0LOG:  parameter "log_statement" changed to 
"all"
-2016-04-07 10:44:42.930 PDT-0LOG:  received SIGHUP, reloading 
configuration files
-2016-04-07 12:44:42.989 CDT-0LOG:  parameter "log_timezone" changed to 
"US/Central
-2016-04-07 12:45:29.966 CDT-0LOG:  parameter "log_statement" changed to 
"mod"
-2016-04-07 10:45:29.966 PDT-0LOG:  parameter "log_timezone" changed to 
"US/Pacific"







--
Adrian Klaver
adrian.kla...@aklaver.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] Non-default postgresql.conf values to log

[Tom Lane]

"David G. Johnston"  writes:
> ​Admittedly, I'm not sure how you would fix any problems without access to
> the server and its config files - at which point you are back to simply
> reviewing those.

Yeah.  Other related problems include being unable to *find* the log file
if you don't know what the server configuration is.

There is already a "postgres -C guc_name" option if you want to find out
from the command line what value a particular GUC is set to by the
cluster's configuration files.  I could see some value in a variant of
that that prints all GUCs with non-default sources.  But that would go
to stdout in any case.  Wanting it to go into a log file sounds to me
a whole lot like wanting to duplicate some Oracle-based DBA habits at a
bug-compatible level.

(Note also that there's already logging of on-the-fly *changes* in
configuration file settings, so I'm not buying the "historical info"
angle at all.)

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