date:20180731


On 07/31/2018 06:12 AM, vardenis pavardenis wrote:



2018-07-31 0:02 GMT+03:00 Adrian Klaver >:


On 07/30/2018 10:33 AM, vardenis pavardenis wrote:



2018-07-30 16:51 GMT+03:00 Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>>:

     On 07/30/2018 12:40 AM, vardenis pavardenis wrote:

         hello.
         thats interesting.
         maybe you have clue why it happened (i tried install by
         instructions) and how to fix it? :)


     Just dawned on me that you are running 14.05 not 14.04 and
that the
     packages you installed are for 14.04.


nice shot!
Will try it.


ok. looks like problem solved, but anyway i wanna share a solution 
(thanks a lot Adrian one more time).
I downloadad archived ubuntu iso 
(http://old-releases.ubuntu.com/releases/14.04.0/ - i picked the last 
one:  "64bit-PC (AMD64) server install image.". Reinstalled server.


follow instructions on https://wiki.postgresql.org/wiki/Apt

got:
Success. You can now start the database server using:

     /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l 
logfile start


Ver Cluster Port Status Owner    Data directory  Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log
update-alternatives: using 
/usr/share/postgresql/10/man/man1/postmaster.1.gz to provide 
/usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
  * Starting PostgreSQL 10 database 
server 
[ OK ]

Processing triggers for libc-bin (2.19-0ubuntu6.14) ...
tadas@ubuntu:~$ service postgresql status
10/main (port 5432): online

thanks Adrian, rob and pgsql-general!!!

PS (offtopic): One thing i can't understand - after installation this 
release shows: Ubuntu 14.04.5 LTS. My problematic installation also was 
Ubuntu 14.04.5 LTS (i make mistake when say it is "14.05"). same 
versions are different? its leagal?


Yes, it is a point release:

https://en.wikipedia.org/wiki/Ubuntu_version_history#Ubuntu_14.04_LTS_(Trusty_Tahr)




If that does no work you can always install from source:

https://www.postgresql.org/ftp/source/v10.4/


That will require installing dev packages though.

Thanks.



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Question on postgresql.conf

"Lu, Dan"  writes:
> Say, I want to put the config file on a share drive so all my postgres 
> configuration file can be seen in one location.
> /nfs/global/postgres-.cnf

What I'd do is make each data directory's postgresql.conf
contain just this:

include /nfs/global/postgres-.cnf

and then just start the postmaster without any weird switches.
In this way you will avoid breaking PG-specific tools that
expect to find the config file at its standard location.

regards, tom lane

Re: Design of a database table


On 07/30/2018 11:40 PM, hmidi slim wrote:
Actually, the data_periods contains a complete range such as 
[2018-09-01,2018-09-30] and data_sub_periods contains sub periods 
contained in this period like:

[2018-09-05, 2018-09-07]
[2018-09-09, 2018-09-11]
[2018-09-12, 2018-09-19]

I make two conditions in order to fetch first if the period 
[2018-09-01,2018-09-30] contained in the first table.If it exists I will 
return the sub periods that overlaps the given period

where data_periods.period && '[2018-09-01,2018-09-30]'::daterange
and data_sub_periods  && '[2018-09-01,2018-09-30] '::daterange



If data_sub_periods are actually sub periods of period then you should 
need only search for the period [2018-09-01,2018-09-30] and join 
data_periods_info to period on period.id = 
data_periods_info.data_periods_id.



--
Adrian Klaver
adrian.kla...@aklaver.com

RE: Question on postgresql.conf

2018-07-31 Thread Lu, Dan

Thanks for your reply.

I can start the database like this: pg_ctl start -D /hostname/postgres/data -l 
/hostname/postgres/log

My data directory has the following file:
postgres.cnf

Content of the postgres.cnf contains only 1 line:
include /nfs/global/postgres-.cnf

Question:
Can  be a variable like `hostname` derived from Unix shell or I have 
to hardcode the name of the host like " include /nfs/global/postgres-host123.cnf

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tuesday, July 31, 2018 9:52 AM
To: Lu, Dan 
Cc: David G. Johnston ; Alvaro Aguayo Garcia-Rada 
; pgsql-gene...@postgresql.org
Subject: Re: Question on postgresql.conf

"Lu, Dan"  writes:
> Say, I want to put the config file on a share drive so all my postgres 
> configuration file can be seen in one location.
> /nfs/global/postgres-.cnf

What I'd do is make each data directory's postgresql.conf contain just this:

include /nfs/global/postgres-.cnf

and then just start the postmaster without any weird switches.
In this way you will avoid breaking PG-specific tools that expect to find the 
config file at its standard location.

regards, tom lane

IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

Re: Question on postgresql.conf

"Lu, Dan"  writes:
> Question:
> Can  be a variable like `hostname` derived from Unix shell or I 
> have to hardcode the name of the host like " include 
> /nfs/global/postgres-host123.cnf

Nope, you'd have to hard-code it.

Of course, you could have the startup script that you intended to set the
env variable just overwrite $PGDATA/postgresql.conf with the right thing.

regards, tom lane

Re: Question on postgresql.conf

2018-07-31 Thread Matt Zagrabelny

On Tue, Jul 31, 2018 at 9:21 AM, Tom Lane  wrote:

> "Lu, Dan"  writes:
> > Question:
> > Can  be a variable like `hostname` derived from Unix shell or
> I have to hardcode the name of the host like " include
> /nfs/global/postgres-host123.cnf
>
> Nope, you'd have to hard-code it.
>
> Of course, you could have the startup script that you intended to set the
> env variable just overwrite $PGDATA/postgresql.conf with the right thing.


Or have puppet/chef/ansible/etc. write the correct config file based on
your dynamic data.

-m

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

2018-07-31 Thread Melvin Davidson

> [2] https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%
> 40wrigleys.postgresql.org
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

> https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%
40wrigleys.postgresql.org



















*Wow, so BUG #9606 has been known since 2014-03-17, but no one has bothered
to fix it?As I've said before, instead of fixing the problem, the
resolution seems to be to "make it go away". I've seen that logic echoed in
other situations outside of the IT environment.The fact remains, my code
works (or worked) because I was only interested in finding tables that were
newly created without a primary key. While I acknowledge that bug, the
situation where the primary key is dropped is extremely rare and would only
happen in the rare case where the primary key needed to be altered or
replaced by another key, in which case the problem is moot. Since
PostgreSQL is a relational database,I cannot think of a situation where a
DBA would allow the primary key to just be dropped/removed altogether.I was
hoping that at least one other person would see my point of view, but by
the harsh replies I've been getting, I feel more like a whistle blower that
insiders think I also should be made to "go away". Well, you are right.
This old Viet Vet shall now end this conversation and his career.I just
need a way to do so quietly and painlessly.The truth is absolute and cannot
be changed.Perception is not the truth.Flerp!*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Question on postgresql.conf


On 7/31/2018 9:36 AM, Matt Zagrabelny wrote:


Or have puppet/chef/ansible/etc. write the correct config file based on 
your dynamic data.


Postgres ain't orrible. I wonder if one actually needs as much 
host-specific config tuning in the first place... I haven't touched 
oracle in forever, though, and mostly managed to repress the memories by 
now.


Dima

Re: alter table docs

2018-07-31 Thread Rob Sargent


I'm not anxious to see it back-patched.


On 07/30/2018 04:25 PM, Tom Lane wrote:

Rob Sargent  writes:

Exactly.  That that is in the "box" made me think a similar blurb for
the non-index version should be there also.

This seems to have been fixed in v11 but not back-patched.

regards, tom lane

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On 07/31/2018 07:47 AM, Melvin Davidson wrote:

[2]

https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org

--
David Rowley http://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Training & Services

>
https://www.postgresql.org/message-id/flat/20140317185255.20724.49675%40wrigleys.postgresql.org

*Wow, so BUG #9606 has been known since 2014-03-17, but no one has
bothered to fix it?
As I've said before, instead of fixing the problem, the resolution seems
to be to
"make it go away". I've seen that logic echoed in other situations
outside of the

IT environment.
The fact remains, my code works (or worked) because I was only
interested in finding
tables that were newly created without a primary key. While I
acknowledge that bug, the
situation where the primary key is dropped is extremely rare and would
only happen
in the rare case where the primary key needed to be altered or replaced
by another
key, in which case the problem is moot. Since PostgreSQL is a relational
database,
I cannot think of a situation where a DBA would allow the primary key to
just be

dropped/removed altogether.
I was hoping that at least one other person would see my point of view,
but by the
harsh replies I've been getting, I feel more like a whistle blower that
insiders

think I also should be made to "go away".

That is not the case. You may not like the explanations that where
provided, that is your right, but the decision has been made.
Alternatives where provided so there is a migration path. People are
just questioning why you are getting so worked up over what is a
relatively minor change. This is nowhere near a disruptive change as say
the implicit cast changes in 8.3. I for one do not want you to 'go away'.

Well, you are right. This old Viet Vet shall now end this conversation
and his career.

I just need a way to do so quietly and painlessly.
The truth is absolute and cannot be changed.
Perception is not the truth.
Flerp!*
*
*
**--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Question on postgresql.conf

2018-07-31 Thread George Neuner

On Mon, 30 Jul 2018 18:35:59 +, "Lu, Dan"  wrote:

>I am going through the PostgreSQL manual and came across a question 
>hoping you can help me.  It appears the "postgresql.conf" file is 
>needed by default to start Postgres.  Since we have standard with 
>other RDBMS to store the configuration file on a shared location for
>easy comparison, I would like to use a different name for this file
>like .conf.

Is there some reason that postgresql.conf cannot be a link to your
file?

George

Re: Question on postgresql.conf

On 07/31/2018 12:33 PM, George Neuner wrote:

> Is there some reason that postgresql.conf cannot be a link to your
> file?

It's six of one, half a dozen of the other. The big problem is when the
network share is unavailable at host boot and all of your databases are
belong to bitbucket. If you want to do it right, you need chef/puppet as
already suggested.

One useful thing linux (at least) has now is conf.d directories whose
contents are auto-included after the main config: this way you can keep
the vendor-provided /etc/blah.conf and have all your host-specific
settings in /etc/blah.d/myhost.conf, all living together happily ever
after. You still want the latter to be a local file that's guaranteed
available when blah service starts up. (Postgres devs: hint! hint!)

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu

signature.asc
Description: OpenPGP digital signature

Re: Question on postgresql.conf

2018-07-31 Thread Steve Atkins



> On Jul 31, 2018, at 10:59 AM, Dimitri Maziuk  wrote:
> 
> On 07/31/2018 12:33 PM, George Neuner wrote:
> 
>> Is there some reason that postgresql.conf cannot be a link to your
>> file?
> 
> It's six of one, half a dozen of the other. The big problem is when the
> network share is unavailable at host boot and all of your databases are
> belong to bitbucket. If you want to do it right, you need chef/puppet as
> already suggested.
> 
> One useful thing linux (at least) has now is conf.d directories whose
> contents are auto-included after the main config: this way you can keep
> the vendor-provided /etc/blah.conf and have all your host-specific
> settings in /etc/blah.d/myhost.conf, all living together happily ever
> after. You still want the latter to be a local file that's guaranteed
> available when blah service starts up. (Postgres devs: hint! hint!)

You'd do that with "include_dir 'conf.d'" or similar, I think, which PostgreSQL
has in all current versions.

Cheers,
  Steve

Re: Question on postgresql.conf

On 07/31/2018 01:05 PM, Steve Atkins wrote:

> You'd do that with "include_dir 'conf.d'" or similar, I think, which 
> PostgreSQL
> has in all current versions.

So it does, huh. I guess it helps to read all the way to end of
postgresql.conf sometimes...

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu

signature.asc
Description: OpenPGP digital signature

Re: Question on postgresql.conf

2018-07-31 Thread George Neuner

On Tue, 31 Jul 2018 12:59:03 -0500, Dimitri Maziuk
 wrote:

>On 07/31/2018 12:33 PM, George Neuner wrote:
>
>> Is there some reason that postgresql.conf cannot be a link to your
>> file?
>
>It's six of one, half a dozen of the other. The big problem is when the
>network share is unavailable at host boot and all of your databases are
>belong to bitbucket. If you want to do it right, you need chef/puppet as
>already suggested.

I understand the problem with network shares - but the OP mentioned
only a "shared location", which could be just another directory on the
same device.   Because the OP said also that there were other DBMS
being used in the same shop, I considered that there might be a
respectable SAN involved.

George

Re: Question on postgresql.conf

On 07/31/2018 01:16 PM, George Neuner wrote:
> 
> I understand the problem with network shares - but the OP mentioned
> only a "shared location", which could be just another directory on the
> same device.   Because the OP said also that there were other DBMS
> being used in the same shop, I considered that there might be a
> respectable SAN involved.

(Veering further OT) yeah and you have to remember to tell Lennartware
to start the automounter before the database and make sure the next
update doesn't "improve" on your configs... IME the shared location that
works best is the chef master.

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu

signature.asc
Description: OpenPGP digital signature

Re: Strange behavior with missing column in SQL function

Marcelo Lacerda  writes:
> Here's the code that reproduces the behavior:
> http://paste.debian.net/1035412/

For the archives' sake, the issue of concern here is what error
message to throw for

CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
RETURNS INTEGER AS $$
SELECT myrow.c + myrow.b;
$$ LANGUAGE sql;

if mytable is a composite type that has no "c" column.

> I have already discussed this in the IRC channel but there doesn't seem to
> be a consensus on whether this is a bug here's a brief transcript of
> RhodiumToad's opinion:
>> this isn't new, goes back to 9.1 at least
>> basically, the error path in sql_fn_post_column_ref is a bit confused.
>> seeing r.c it tries to resolve it as parameter.field, fails, and rather
>> than reporting the error directly as being a missing field, it just returns
>> with the reference unresolved
>> then the outer parser code, having failed to resolve it as table.column
>> and having had the hook function not override it, reports it on the
>> assumption that it's a missing table
>> so it's probably been this way for as long as named parameters have
>> worked in language sql

I can't get real excited about changing this.  Yeah, it's obvious to a
human that the other error message wording would be more apropos here,
but there are nearby cases where that's not so.  So I'm afraid we'd
just be improving some cases by de-improving others.

The core of the problem is what about ambiguous references such as

CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
RETURNS INTEGER AS $$
SELECT myrow.c + myrow.b FROM myrow;
$$ LANGUAGE sql;

where "myrow" is a table with a different set of column names from
"mytable".  The existing behavior for that is to seek the column name
in "myrow" (the table), failing that to seek it in the parameter,
and only to throw an error if both fail.  You could make a reasonable
argument that it shouldn't work like that --- having the query-local
table name mask the parameter entirely would likely be better.  But
I don't know if we can get away with changing that from a backwards
compatibility standpoint.  Even if we thought we could, the existing
hook definition doesn't support doing that; we'd need some other API.

If we did change this, then it'd be reasonable to tighten the error
message behavior; but as things stand, sql_fn_post_column_ref doesn't
know what's going on so it's not in a position to throw a reliable
error message.

regards, tom lane

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

2018-07-31 Thread Peter Geoghegan

On Tue, Jul 31, 2018 at 7:47 AM, Melvin Davidson  wrote:
> I was hoping that at least one other person would see my point of view, but
> by the
> harsh replies I've been getting, I feel more like a whistle blower that
> insiders
> think I also should be made to "go away".

You were bellicose from almost the beginning of this thread. And, yes,
that does detract from your argument. Just as it would in almost any
other sphere or arena.

> Well, you are right. This old Viet Vet shall now end this conversation and
> his career.
> I just need a way to do so quietly and painlessly.
> The truth is absolute and cannot be changed.
> Perception is not the truth.
> Flerp!

I cannot imagine what reaction you were expecting to this. In all
sincerity, I suggest reflecting on your words. You don't seem to have
realistic expectations about how the community works, or could ever
work.

-- 
Peter Geoghegan

pg_basebackup without copying the logs

2018-07-31 Thread Johnes Castro

Hi

is there any way to do pg_basebackup without copying the logs?


Best Regards,

Johnes Castro

Tecnisys

Re: pg_basebackup without copying the logs


On 07/31/2018 01:42 PM, Johnes Castro wrote:

Hi

is there any way to do pg_basebackup without copying the logs?


Are you talking about the transaction logs(WAL) or the logging messages?



Best Regards,

Johnes Castro

Tecnisys




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_basebackup without copying the logs

2018-07-31 Thread Johnes Castro

logging messages.

I've noticed that in version 10, you have the -X none option.




De: Adrian Klaver 
Enviado: terça-feira, 31 de julho de 2018 18:57
Para: Johnes Castro; pgsql-gene...@postgresql.org
Assunto: Re: pg_basebackup without copying the logs

On 07/31/2018 01:42 PM, Johnes Castro wrote:
> Hi
>
> is there any way to do pg_basebackup without copying the logs?

Are you talking about the transaction logs(WAL) or the logging messages?

>
> Best Regards,
>
> Johnes Castro
>
> Tecnisys
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_basebackup without copying the logs


On 07/31/2018 01:59 PM, Johnes Castro wrote:
Please reply to list also.
Ccing list.

  logging messages.


Not that I know of.






*De:* Adrian Klaver 
*Enviado:* terça-feira, 31 de julho de 2018 18:57
*Para:* Johnes Castro; pgsql-gene...@postgresql.org
*Assunto:* Re: pg_basebackup without copying the logs
On 07/31/2018 01:42 PM, Johnes Castro wrote:

Hi

is there any way to do pg_basebackup without copying the logs?


Are you talking about the transaction logs(WAL) or the logging messages?



Best Regards,

Johnes Castro

Tecnisys




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_basebackup without copying the logs


On 07/31/2018 02:04 PM, Johnes Castro wrote:

logging messages.

I've noticed that in version 10, you have the -X none option.


That refers to the transaction(WAL) logs.






*De:* Adrian Klaver 
*Enviado:* terça-feira, 31 de julho de 2018 18:57
*Para:* Johnes Castro; pgsql-gene...@postgresql.org
*Assunto:* Re: pg_basebackup without copying the logs
On 07/31/2018 01:42 PM, Johnes Castro wrote:

Hi

is there any way to do pg_basebackup without copying the logs?


Are you talking about the transaction logs(WAL) or the logging messages?



Best Regards,

Johnes Castro

Tecnisys




--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_basebackup without copying the logs

2018-07-31 Thread Johnes Castro


>Not that I know of.

Ok, Thanks for attention.


De: Adrian Klaver 
Enviado: terça-feira, 31 de julho de 2018 19:05
Para: Johnes Castro
Cc: pgsql-general
Assunto: Re: pg_basebackup without copying the logs

On 07/31/2018 01:59 PM, Johnes Castro wrote:
Please reply to list also.
Ccing list.
>   logging messages.

Not that I know of.

>
>
>
> 
> *De:* Adrian Klaver 
> *Enviado:* terça-feira, 31 de julho de 2018 18:57
> *Para:* Johnes Castro; pgsql-gene...@postgresql.org
> *Assunto:* Re: pg_basebackup without copying the logs
> On 07/31/2018 01:42 PM, Johnes Castro wrote:
>> Hi
>>
>> is there any way to do pg_basebackup without copying the logs?
>
> Are you talking about the transaction logs(WAL) or the logging messages?
>
>>
>> Best Regards,
>>
>> Johnes Castro
>>
>> Tecnisys
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Strange behavior with missing column in SQL function

2018-07-31 Thread Marcelo Lacerda

> CREATE OR REPLACE FUNCTION myfunction(myrow mytable)
> RETURNS INTEGER AS $$
> SELECT myrow.c + myrow.b FROM myrow;
> $$ LANGUAGE sql;

> where "myrow" is a table with a different set of column names from
> "mytable".  The existing behavior for that is to seek the column name
> in "myrow" (the table), failing that to seek it in the parameter,
> and only to throw an error if both fail.
Wow I never thought this would be possible. why didn't the designers of the
language use myrow mytable%ROWTYPE for rows of a table as a parameter,
given that it's a valid type in PL/PGSQL? I figure that way it would have
been way easier to disambiguate the definitions.

How to prevent "no wait lock" after a connection drop

2018-07-31 Thread Ertan Küçükoğlu

Hello,

Using PostgreSQL 9.6.9 (will be PostgreSQL 10.4 in a month) running on
Debian Stretch on a VPS server.

I am collecting customer data from distributed locations. There will be
around 50-70 locations uploading data. Customer data is on local FirebirdSQL
database and there are about 15 tables synced to PostgreSQL in total. I am
using direct SSL connection to PostgreSQL to do delete/insert/update of
necessary records in that order. Everything is in a single transaction and
that transaction is rolled back if anything goes wrong.

Very first run of the application upload may take more than an hour
depending on internet connection speed. Fast internet connections can
complete relatively large initial transfer under 10 minutes. Consecutive
runs are pretty fast and completes under a minute even on slow connections.

What I observe during my tests is that if I intentionally drop internet
connection during any stage of data transfer (that is mostly while inserting
to tables) application gives error and stop. For next sync operation (which
runs every 5 mins) gets "no wait lock" error and exit without doing
anything. That lock stage roughly stays for 1-2 hours or more.
 
I use Read Committed transaction isolation level and default action is
rollback on close.

I wonder if I can overcome that long lock waits as this is not desired which
will break data sync for a long period.

Thanks & regards
Ertan Küçükoğlu

Re: How to prevent "no wait lock" after a connection drop