Re: [GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres

[Venkata Balaji N]

>
> How many concurrent connections can be made, will purely depend on number
> of CPUs (cores) you have available on the database server.
> Well, certainly 1 would be the way to go to build an multi-tenant
> application, but, it strongly depends on your application specific
> requirements and how are you distributing the data across databases and how
> the users are accessing data across the databases.
>
> Yup, I also would like to go with 1, suppose a server with 16 core, 32GB
> memory, SSD box, how many connections could it handle roughly, e.g. O(100)
> or O(1000) or O(5000)?
>
Well, as its been said already, you can typically have 16 concurrent
connections at a time and more if the CPUs are hyper-threading enabled. All
that said, how many requests your database can serve will depend on the
fact that, how long each connection is expected to spend on the database.
This means, there must an appropriate benchmarking process in place. If you
really see a connection/query spending few milli-seconds on the database
with some aggressive connection pooling mechanism, then you can get more
number of requests served in less time. Generally, it is possible that,
1000s of requests can be served and those being concurrent will be far from
real with kind of hardware capacity you have. So, the solution would be to
have appropriate tuning and benchmarking process in place.

Regards,
Venkata B N

Fujitsu, Australia

> On August 1, 2016 at 10:30:48 PM, Venkata Balaji N (nag1...@gmail.com)
> wrote:
>
>
> On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot 
> wrote:
>
>> Hi,
>>
>>   We are trying build a multi tenant application and are debating which
>> approach we should take: (also my understanding is based on that pgbouncer
>> connection pool doesn’t work across different user/database pair):
>>
>> 1.  For each tenant, we create a dedicated database and a dedicated user.
>> This option gives best isolation. However, connection pooling won’t work
>> pgbouncer.
>>
>
> Not sure what you meant by saying connection pooling (pgBouncer) does not
> work ? This is the general approach for building a multi-tenant application.
>
>>
>> 2.  We put all tenants in a single database, and just use one user. I
>> learned that pgbackup will be probamatic when there are > 30 schemas in a
>> database, so we probably can’t create dedicate schema for each tenant.
>>
>>   We are more inclined to choose 1, but don't know how many concurrent
>> connections Postgres can handle for OLTP workload in a 32GB memory, SSD
>> box. Also we would like hear from someone with more postgres experience
>> about the best practice for building multi-tenant application. Again, i am
>> not sure what you meant by saying pgbackup will not work where there are
>> more than 30 schemas ?
>>
>
> Which version of PostgreSQL are you using ?
>
> How many concurrent connections can be made, will purely depend on number
> of CPUs (cores) you have available on the database server.
> Well, certainly 1 would be the way to go to build an multi-tenant
> application, but, it strongly depends on your application specific
> requirements and how are you distributing the data across databases and how
> the users are accessing data across the databases.
>
> Regards,
> Venkata B N
>
> Fujitsu Australia
>
>

Re: [GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres

[John R Pierce]

On 8/1/2016 11:17 PM, Silk Parrot wrote:
Yup, I also would like to go with 1, suppose a server with 16 core, 
32GB memory, SSD box, how many connections could it handle roughly, 
e.g. O(100) or O(1000) or O(5000)?



thats  a fairly small server by today's standards, especially the 32GB 
ram part.


with 16 cores, more than 16 concurrent CPU bound queries will degrade 
each other.idle connections don't use too much memory and no CPU, 
but 1000s sounds like a bad idea.



--
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] pg_restore out of memory

[Miguel Ramos]

Greetings,

To all those who helped out with this problem, I'm sorry it took me so
long to respond. For the record, the matter is solved, at least for us,
but we had to repeat a lot of things to make sure.


First, the "out of memory" problem repeated itself when restoring that
single table, after doing a schema-only restore and removing all
foreign key constraints.

Second, the other weirdness on this table was a couple of constraints
to make sure that the three arrays on each row were of the same length.
But that was not the problem.


It turned out that the backup file was the problem.

Plus, I'm convinced that this was our mistake, someone simply didn't
see an error message during backup or some copy of the file.
Also, we should have already repeated the backup and maybe we didn't.


We did a new custom dump from within latest pgadmin III, and that one
restored just fine (using the same old 9.1.8 pg_restore).

It could have been a bug in pg_dump 9.1.8 since the good backup was
from a newer version. But this didn't seem so likely and because
repeating a full backup would force us to keep the original database on
the server for a few more days (dumps take so long) we didn't do it.

We did however a dump of that single table with pg_dump 9.1.8 and that
one also restored just fine.



Our immediate practical problem is over.
The only complaint would be that the "out of memory" message is
unfriendly.
If you would find useful that we make some additional tests, or some
observation of the file, we would be glad to return the help.


Best Regards,


-- 
Miguel Ramos



-- 
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 to best archetect Multi-Tenant SaaS application using Postgres

[Silk Parrot]

Thanks for the response

  We are trying build a multi tenant application and are debating which 
approach we should take: (also my understanding is based on that pgbouncer 
connection pool doesn’t work across different user/database pair):

1.  For each tenant, we create a dedicated database and a dedicated user. This 
option gives best isolation. However, connection pooling won’t work pgbouncer.

Not sure what you meant by saying connection pooling (pgBouncer) does not work 
? This is the general approach for building a multi-tenant application.
According to https://pgbouncer.github.io/config.html, the connection pooling is 
based on user/database pair. If each user connects under its own username to 
its own database, then the connection opened can’t be shared with other users. 
If I want to make a single server host 1000 tenants at the same time, then 
there will be at least 1000 connections even I use pgBouncer.

How many concurrent connections can be made, will purely depend on number of 
CPUs (cores) you have available on the database server. 
Well, certainly 1 would be the way to go to build an multi-tenant application, 
but, it strongly depends on your application specific requirements and how are 
you distributing the data across databases and how the users are accessing data 
across the databases.
Yup, I also would like to go with 1, suppose a server with 16 core, 32GB 
memory, SSD box, how many connections could it handle roughly, e.g. O(100) or 
O(1000) or O(5000)?


-- 
Regards
Ryan

On August 1, 2016 at 10:30:48 PM, Venkata Balaji N (nag1...@gmail.com) wrote:


On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot  wrote:
Hi,

  We are trying build a multi tenant application and are debating which 
approach we should take: (also my understanding is based on that pgbouncer 
connection pool doesn’t work across different user/database pair):

1.  For each tenant, we create a dedicated database and a dedicated user. This 
option gives best isolation. However, connection pooling won’t work pgbouncer.

Not sure what you meant by saying connection pooling (pgBouncer) does not work 
? This is the general approach for building a multi-tenant application.

2.  We put all tenants in a single database, and just use one user. I learned 
that pgbackup will be probamatic when there are > 30 schemas in a database, so 
we probably can’t create dedicate schema for each tenant.

  We are more inclined to choose 1, but don't know how many concurrent 
connections Postgres can handle for OLTP workload in a 32GB memory, SSD box. 
Also we would like hear from someone with more postgres experience about the 
best practice for building multi-tenant application. Again, i am not sure what 
you meant by saying pgbackup will not work where there are more than 30 schemas 
?

Which version of PostgreSQL are you using ?

How many concurrent connections can be made, will purely depend on number of 
CPUs (cores) you have available on the database server. 
Well, certainly 1 would be the way to go to build an multi-tenant application, 
but, it strongly depends on your application specific requirements and how are 
you distributing the data across databases and how the users are accessing data 
across the databases.

Regards,
Venkata B N

Fujitsu Australia  

Re: [GENERAL] pg_archivecleanup standalone bash script

[Patrick B]

I'll ajust the script and once is done will share here with u guys


Patrick

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Patrick B]

This is now solved guys.

There were three problems.

1 - I was looking for the logs on /var/lib/postgresql/9.2/main/pg_log. And
it's wrong. On ubuntu, the logs are: /var/log/postgresql

2 - I just changed the max_stack_depth to the default

DETAIL:  "max_stack_depth" must not exceed 7680kB.
> HINT:  Increase the platform's stack depth limit via "ulimit -s" or local
> equivalent.
> 2016-08-02 19:54:06.293 UTC|12960|FATAL:  configuration file
> "/etc/postgresql/9.2/main/postgresql.conf" contains errors


3 - My wal_files aren't being stored into pg_xlog. I use a different folder
in a different partition, because my servers use SATA disks.
As I use centOS as Postgres Server, the recovery.conf was written for
postgres and the restore_command and cleanup weren't right.
I just had to ajust them to Ubuntu (by putting the entire path) and it
started to work.


Now, the server is restoring the wal_files and it's all good

restored log file "000214AB0063" from archive



Thanks
Patrick

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Patrick B]

>
>
> Short version:
>
> That probably should be postgresql not postgres
>
> Long version:
>
> https://help.ubuntu.com/community/PostgreSQL
>
> 
>>
>
Yep.. my mistake..

/etc/init.d/postgresql start/stop/restart

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[John R Pierce]

On 8/2/2016 1:17 PM, Patrick B wrote:


Ubuntu 16.04.1 LTS


How did you install Postgres and from where?


apt-get install postgresql-9.2 postgresql-contrib-9.2
dpkg -l postgresql




When I start it, it doesn't start...


Show the command you used to start it.


/etc/init.d/postgres start/stop/restart


can you show the output of...

pg_lsclusters

?


ubuntu has this whole infrastructure to support mutliple concurrent 
postgres installs...the init.d/postgres 'service' will only start 
postgres clusters that are configured to autostart as shown by pg_lsclusters




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Adrian Klaver]

On 08/02/2016 01:17 PM, Patrick B wrote:



2016-08-03 8:13 GMT+12:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

On 08/02/2016 01:04 PM, Patrick B wrote:

Hi guys,

I've installed potgres on a Ubuntu machine.


Postgres version?


have a look on the subjec of this email (9.2)



Ubuntu version?


Ubuntu 16.04.1 LTS




How did you install Postgres and from where?


apt-get install postgresql-9.2 postgresql-contrib-9.2
dpkg -l postgresql


From the PGDG repos or Ubuntu?

I would suggest the PGDG repos as they seem to pick up the version 
updates sooner.








When I start it, it doesn't start...


Show the command you used to start it.



/etc/init.d/postgres start/stop/restart



Short version:

That probably should be postgresql not postgres

Long version:

https://help.ubuntu.com/community/PostgreSQL






--
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] Postgresql 9.2 Ubuntu - is not starting

[Patrick B]

So, I found a log file on /var/log/postgres/

DETAIL:  "max_stack_depth" must not exceed 7680kB.
> HINT:  Increase the platform's stack depth limit via "ulimit -s" or local
> equivalent.
> 2016-08-02 19:54:06.293 UTC|12960|FATAL:  configuration file
> "/etc/postgresql/9.2/main/postgresql.conf" contains errors


I changed the max_stack_depth value on postgresql.conf.

Now, I'm getting this error:

could not open file "pg_xlog/000214AB000B" (log file 5291,
> segment 11): No such file or directory



The wal_files aren't inside pg_xlog... I store them in a different folder.
Will try to solve this.

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Patrick B]

2016-08-03 8:13 GMT+12:00 Adrian Klaver :

> On 08/02/2016 01:04 PM, Patrick B wrote:
>
>> Hi guys,
>>
>> I've installed potgres on a Ubuntu machine.
>>
>
> Postgres version?
>

have a look on the subjec of this email (9.2)


>
> Ubuntu version?
>

Ubuntu 16.04.1 LTS


>
> How did you install Postgres and from where?


apt-get install postgresql-9.2 postgresql-contrib-9.2
dpkg -l postgresql


>
>
>
>> When I start it, it doesn't start...
>>
>
> Show the command you used to start it.
> 


/etc/init.d/postgres start/stop/restart

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Adrian Klaver]

On 08/02/2016 01:04 PM, Patrick B wrote:

Hi guys,

I've installed potgres on a Ubuntu machine.


Postgres version?

Ubuntu version?

How did you install Postgres and from where?



When I start it, it doesn't start...


Show the command you used to start it.



/etc/init.d/postgresql status


*●*postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
   Active: *active (exited)*since Tue 2016-08-02 19:54:06 UTC; 7min ago
  Process: 12963 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 12963 (code=exited, status=0/SUCCESS)
Tasks: 0
   Memory: 0B
  CPU: 0
   CGroup: /system.slice/postgresql.service


But when looking for the process:
ps fax/ps aux

... it doesn't show postgres.

Also, the logs aren't being created. the pg_log folder is empty.


It's my first time using Ubuntu

I've done a pg_basebackup from my master and I'm trying to start this
slave server.

Do you guys know what should be going on?

Cheers
Patrick





--
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

[GENERAL] Postgresql 9.2 Ubuntu - is not starting

[Patrick B]

Hi guys,

I've installed potgres on a Ubuntu machine.

When I start it, it doesn't start...

> /etc/init.d/postgresql status


*●* postgresql.service - PostgreSQL RDBMS
>Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
> preset: enabled)
>Active: *active (exited)* since Tue 2016-08-02 19:54:06 UTC; 7min ago
>   Process: 12963 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>  Main PID: 12963 (code=exited, status=0/SUCCESS)
> Tasks: 0
>Memory: 0B
>   CPU: 0
>CGroup: /system.slice/postgresql.service


But when looking for the process:
ps fax/ps aux

... it doesn't show postgres.

Also, the logs aren't being created. the pg_log folder is empty.


It's my first time using Ubuntu

I've done a pg_basebackup from my master and I'm trying to start this slave
server.

Do you guys know what should be going on?

Cheers
Patrick

[GENERAL] Updated RUM-index and support for bigint as part of index

[Andreas Joseph Krogh]

Hi.
 
I see the RUM-index is updated, which is great!
 
I wonder, to be able to sort by timestamp one has to create the index like 
this:
 
CREATE INDEX rumidx ON origo_email_delivery USING rum (fts_all 
rum_tsvector_timestamp_ops, received_timestamp)WITH (attach = 
'received_timestamp', TO = 'fts_all', order_by_attach = TRUE ); 
Then, to be able to use the index for sorting by the 
"received_timestamp"-column one has to issue a query like this:
EXPLAIN ANALYZE SELECT del.entity_id, del.subject, del.received_timestamp, 
fts_all <=>to_tsquery('simple', 'andreas&kr') AS rank FROM origo_email_delivery 
delWHERE del.fts_all @@ to_tsquery('simple', 'andreas&kr') ORDER BY '2000-01-01'
::TIMESTAMP <=> del.received_timestamp LIMIT 10; 
QUERY PLAN 
--
Limit(cost=14.40..26.47 rows=10 width=89) (actual time=10.908..10.952 rows=10 
loops=1) -> Index Scan using rumidx on origo_email_delivery del (cost
=14.40..3221.22rows=2657 width=89) (actual time=10.906..10.947 rows=10 loops=1) 
IndexCond: (fts_all @@ '''andreas'' & ''kr'''::tsquery) Order By: 
(received_timestamp <=>'2000-01-01 00:00:00'::timestamp without time zone) 
Planningtime: 0.491 ms Execution time: 11.010 ms (6 rows) 
 
The ORDER BY part seems strange; It seems one has to find a value "lower than 
any other value" to use as a kind of base, why is this necessary? It also seems 
that in order to be able to sort DESC one has to provide a timestamp value 
"higher than any other value", is this correct?
 
It would be great if the docs explained this.
 
I really miss the opportunity to include a BIGINT as part of the index, so 
that the WHERE-clause could be like this:
 
WHERE del.fts_all @@ to_tsquery('simple', 'andreas&kr') AND del.folder_id IN (1
,2,3)
 
Having this would be perfect for my use-case searching in email in folders, 
sorted by received_date, and having it use ONE index.
 
Will this be supported?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Lmhelp1]

Thank you for this note.
Best regards,
--
Léa Massiot


On 2016-08-02 6:57 PM, Tom Lane wrote:

Lmhelp1  writes:

cmd.exe psql # \s
history is not supported by this installation

This means you have a copy of psql.exe that was built without the readline
library, so history doesn't work either.  I'm not sure what the state of
the readline library is for Windows; there may not be any version that
works well.

regards, tom lane




On 2016-08-02 6:55 PM, Lmhelp1 wrote:

Thank you for your answer.

I can't see any difference.
In "Properties", there is an entry "Command History" with two fields 
"Buffer size" set to 50 and "Number of Buffers" set to 4.

These settings are exactly the same in "cmd.exe" and "SQL Shell"...

Best regards,
--
Léa Massiot



On 2016-08-02 6:39 PM, Adrian Klaver wrote:

On 08/02/2016 09:39 AM, Lmhelp1 wrote:

Thank you for your answer.

With the "SQL Shell" tool, the commands history is indeed available!
So, I guess I am now going to use this tool instead of "cmd.exe".
This solves my problem.


Or look at the properties of the menu item and see what is done to 
enable history.




Thank you and best regards.
--
Léa Massiot



On 2016-08-02 6:27 PM, Adrian Klaver wrote:


What happens if you use the SQL Shell from the program menu on the
Start Menu?:

http://www.enterprisedb.com/resources-community/tutorials-quickstarts/windows/getting-started-postgres-plus-tutorial-windows 









On 2016-08-02 6:19 PM, Lmhelp1 wrote:


Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe"
installer the 2016/04/21.
So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.

Best regards,
--
Léa Massiot


On 2016-08-02 5:56 PM, Melvin Davidson wrote:


Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT 



The following may be pertinent.

If -n / --no-readline was specified, then command history is not 
used.


What happens if you issue the \s command in psql? Is history 
supported?


What is the value for HISTFILE?

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston
mailto:david.g.johns...@gmail.com>>wrote:
On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson mailto:melvin6...@gmail.com>> wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you
provide us with a little more useful information like current
PostgreSQL version and O/S?
Otherwise we have absolutely no idea what the problem might be.


"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though
knowing how it was installed might (though not for me personally).

David J.



On 2016-08-02 5:40 PM, Lmhelp1 wrote:


Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide
us with a little more useful information like current PostgreSQL
version and O/S?
Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 mailto:lmhe...@orange.fr>> wrote:

Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work
again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org 
)

To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Tom Lane]

Lmhelp1  writes:
> cmd.exe psql # \s
> history is not supported by this installation

This means you have a copy of psql.exe that was built without the readline
library, so history doesn't work either.  I'm not sure what the state of
the readline library is for Windows; there may not be any version that
works well.

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] Commands history with psql in a Windows command line shell

[Lmhelp1]

Thank you for your answer.

I can't see any difference.
In "Properties", there is an entry "Command History" with two fields 
"Buffer size" set to 50 and "Number of Buffers" set to 4.

These settings are exactly the same in "cmd.exe" and "SQL Shell"...

Best regards,
--
Léa Massiot



On 2016-08-02 6:39 PM, Adrian Klaver wrote:

On 08/02/2016 09:39 AM, Lmhelp1 wrote:

Thank you for your answer.

With the "SQL Shell" tool, the commands history is indeed available!
So, I guess I am now going to use this tool instead of "cmd.exe".
This solves my problem.


Or look at the properties of the menu item and see what is done to 
enable history.




Thank you and best regards.
--
Léa Massiot



On 2016-08-02 6:27 PM, Adrian Klaver wrote:


What happens if you use the SQL Shell from the program menu on the
Start Menu?:

http://www.enterprisedb.com/resources-community/tutorials-quickstarts/windows/getting-started-postgres-plus-tutorial-windows 









On 2016-08-02 6:19 PM, Lmhelp1 wrote:


Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe"
installer the 2016/04/21.
So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.

Best regards,
--
Léa Massiot


On 2016-08-02 5:56 PM, Melvin Davidson wrote:


Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT 



The following may be pertinent.

If -n / --no-readline was specified, then command history is not used.

What happens if you issue the \s command in psql? Is history 
supported?


What is the value for HISTFILE?

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston
mailto:david.g.johns...@gmail.com>>wrote:
On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson mailto:melvin6...@gmail.com>> wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you
provide us with a little more useful information like current
PostgreSQL version and O/S?
Otherwise we have absolutely no idea what the problem might be.


"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though
knowing how it was installed might (though not for me personally).

David J.



On 2016-08-02 5:40 PM, Lmhelp1 wrote:


Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide
us with a little more useful information like current PostgreSQL
version and O/S?
Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 mailto:lmhe...@orange.fr>> wrote:

Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work
again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org 
)

To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.













--
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] Commands history with psql in a Windows command line shell

[Adrian Klaver]

On 08/02/2016 09:39 AM, Lmhelp1 wrote:

Thank you for your answer.

With the "SQL Shell" tool, the commands history is indeed available!
So, I guess I am now going to use this tool instead of "cmd.exe".
This solves my problem.


Or look at the properties of the menu item and see what is done to 
enable history.




Thank you and best regards.
--
Léa Massiot



On 2016-08-02 6:27 PM, Adrian Klaver wrote:


What happens if you use the SQL Shell from the program menu on the
Start Menu?:

http://www.enterprisedb.com/resources-community/tutorials-quickstarts/windows/getting-started-postgres-plus-tutorial-windows







On 2016-08-02 6:19 PM, Lmhelp1 wrote:


Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe"
installer the 2016/04/21.
So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.

Best regards,
--
Léa Massiot


On 2016-08-02 5:56 PM, Melvin Davidson wrote:


Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT

The following may be pertinent.

If -n / --no-readline was specified, then command history is not used.

What happens if you issue the \s command in psql? Is history supported?

What is the value for HISTFILE?

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston
mailto:david.g.johns...@gmail.com>>wrote:
On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson mailto:melvin6...@gmail.com>> wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you
provide us with a little more useful information like current
PostgreSQL version and O/S?
Otherwise we have absolutely no idea what the problem might be.


"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though
knowing how it was installed might (though not for me personally).

David J.



On 2016-08-02 5:40 PM, Lmhelp1 wrote:


Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide
us with a little more useful information like current PostgreSQL
version and O/S?
Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 mailto:lmhe...@orange.fr>> wrote:

Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work
again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



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




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.









--
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] Commands history with psql in a Windows command line shell

[Lmhelp1]

Thank you for your answer.

With the "SQL Shell" tool, the commands history is indeed available!
So, I guess I am now going to use this tool instead of "cmd.exe".
This solves my problem.

Thank you and best regards.
--
Léa Massiot



On 2016-08-02 6:27 PM, Adrian Klaver wrote:


What happens if you use the SQL Shell from the program menu on the 
Start Menu?:


http://www.enterprisedb.com/resources-community/tutorials-quickstarts/windows/getting-started-postgres-plus-tutorial-windows 








On 2016-08-02 6:19 PM, Lmhelp1 wrote:


Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe" 
installer the 2016/04/21.

So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.

Best regards,
--
Léa Massiot


On 2016-08-02 5:56 PM, Melvin Davidson wrote:


Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT

The following may be pertinent.

If -n / --no-readline was specified, then command history is not used.

What happens if you issue the \s command in psql? Is history supported?

What is the value for HISTFILE?

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston 
mailto:david.g.johns...@gmail.com>>wrote:
On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson > wrote:


Are you doing this in PostgreSQL 7.3 and Windows XP? Can you
provide us with a little more useful information like current
PostgreSQL version and O/S?
Otherwise we have absolutely no idea what the problem might be.


"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though 
knowing how it was installed might (though not for me personally).


David J.



On 2016-08-02 5:40 PM, Lmhelp1 wrote:


Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:
Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide 
us with a little more useful information like current PostgreSQL 
version and O/S?

Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 > wrote:


Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work
again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



-- 
Sent via pgsql-general mailing list

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




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Adrian Klaver]

On 08/02/2016 09:19 AM, Lmhelp1 wrote:

Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe"
installer the 2016/04/21.
So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.


What happens if you use the SQL Shell from the program menu on the Start 
Menu?:


http://www.enterprisedb.com/resources-community/tutorials-quickstarts/windows/getting-started-postgres-plus-tutorial-windows



Best regards,
--
Léa Massiot




--
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] Commands history with psql in a Windows command line shell

[Lmhelp1]

Thank you for your answer.

1.
Like I wrote in my first post, the command I use to launch "psql" is:
cmd.exe> psql -p  -U  
So, I do not specify the "--no-readline" option.

2.
cmd.exe psql # \s
history is not supported by this installation

Powershell psql # \s
history is not supported by this installation

To install PostgreSQL, I used the "postgresql-9.5.2-1-windows-x64.exe" 
installer the 2016/04/21.

So I don't remember much about what I did that day.

3.
When I do:
psql # \set
HISTFILE does not appear in the result.
So, I guess it is not set.

Best regards,
--
Léa Massiot


On 2016-08-02 5:56 PM, Melvin Davidson wrote:


Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT

The following may be pertinent.

If -n / --no-readline was specified, then command history is not used.

What happens if you issue the \s command in psql? Is history supported?

What is the value for HISTFILE?

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston 
mailto:david.g.johns...@gmail.com>>wrote:
On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson > wrote:


   Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide
   us with a little more useful information like current PostgreSQL
   version and O/S?
   Otherwise we have absolutely no idea what the problem might be.


"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though 
knowing how it was installed might (though not for me personally).


David J.



On 2016-08-02 5:40 PM, Lmhelp1 wrote:


Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:
Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide 
us with a little more useful information like current PostgreSQL 
version and O/S?

Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 >wrote:


Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work
again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Melvin Davidson]

On Tue, Aug 2, 2016 at 11:40 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson 
> wrote:
>
>> Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide us
>> with a little more useful information like current PostgreSQL version and
>> O/S?
>> Otherwise we have absolutely no idea what the problem might be.
>>
>>
> "Windows (8.1)" refers to the O/S version.
>
> Knowing the PostgreSQL version probably won't make a difference though
> knowing how it was installed might (though not for me personally).
>
> David J.
>
>
Thank you, Based on
https://www.postgresql.org/docs/9.5/static/app-psql.html#APP-PSQL-ENVIRONMENT

The following may be pertinent.

If -n / --no-readline was specified, then command history is not used.

What happens if you issue the \s command in psql? Is history supported?

What is the value for HISTFILE?







-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[David G. Johnston]

On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson 
wrote:

> Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide us
> with a little more useful information like current PostgreSQL version and
> O/S?
> Otherwise we have absolutely no idea what the problem might be.
>
>
"Windows (8.1)" refers to the O/S version.

Knowing the PostgreSQL version probably won't make a difference though
knowing how it was installed might (though not for me personally).

David J.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Lmhelp1]

Thank you for your answer.

cmd.exe> psql --version
psql (PostgreSQL) 9.5.2

Like I wrote, the Windows OS is 8.1 Pro.

Best regards,
--
Léa Massiot


On 2016-08-02 5:31 PM, Melvin Davidson wrote:
Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide 
us with a little more useful information like current PostgreSQL 
version and O/S?

Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1 > wrote:


Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve
these last two commands I entered by hitting the top arrow key.
Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the
commands history using Powershell, maybe the command "\set
HISTSIZE 100" and maybe a "psqlrc" file.
But whatever I've been trying to do since, I can't make it work again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Commands history with psql in a Windows command line shell

[Melvin Davidson]

Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide us
with a little more useful information like current PostgreSQL version and
O/S?
Otherwise we have absolutely no idea what the problem might be.

On Tue, Aug 2, 2016 at 11:18 AM, Lmhelp1  wrote:

> Hello,
>
> I am using "psql" in "cmd.exe" in Windows (8.1).
>
> Below is the command I use to connect to a database with "psql":
> cmd.exe> psql -p  -U  
>
> My problem is the following.
> Suppose I enter one command:
> # SELECT * FROM ;
> and then another one:
> # SELECT * FROM ;
> The commands history "is not working": meaning I cannot retrieve these
> last two commands I entered by hitting the top arrow key.
> Instead, I have to type them again.
>
> To try to solve the problem, I entered the command:
> # \set HISTSIZE 100
> but it didn't change anything.
>
> Yesterday, I don't remember what I did, but I could access the commands
> history using Powershell, maybe the command "\set HISTSIZE 100" and maybe a
> "psqlrc" file.
> But whatever I've been trying to do since, I can't make it work again.
>
> Can you please advise me what to do to make this work?
>
> Best regards.
> --
> Léa Massiot
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Commands history with psql in a Windows command line shell

[Lmhelp1]

Hello,

I am using "psql" in "cmd.exe" in Windows (8.1).

Below is the command I use to connect to a database with "psql":
cmd.exe> psql -p  -U  

My problem is the following.
Suppose I enter one command:
# SELECT * FROM ;
and then another one:
# SELECT * FROM ;
The commands history "is not working": meaning I cannot retrieve these 
last two commands I entered by hitting the top arrow key.

Instead, I have to type them again.

To try to solve the problem, I entered the command:
# \set HISTSIZE 100
but it didn't change anything.

Yesterday, I don't remember what I did, but I could access the commands 
history using Powershell, maybe the command "\set HISTSIZE 100" and 
maybe a "psqlrc" file.

But whatever I've been trying to do since, I can't make it work again.

Can you please advise me what to do to make this work?

Best regards.
--
Léa Massiot



--
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] consolidating data with window functions

[Ladislav Lenart]

On 1.8.2016 18:48, Andrew Geery wrote:
> I have a data-set with 
> - a line number
> - a name
> - a value
> 
> I want to select the rows in line number order, but I want to consolidate
> consecutive rows into a single row, concatenating the names, if the value is 
> null. 
> 
> For example, here's my data:
> 
> values (
> (1, 'a', 1),
> (2, 'b', 2),
> (3, 'c', null),
> (4, 'd', null),
> (5, 'e', 3),
> (6, 'f', null),
> (7, 'g', null),
> (8, 'h', 4),
> (9, 'i', null),
> (10, 'j', 5)
> )
> 
> I want to transform the data into this:
> 
> values (
> (1, 'a', 1),
> (2, 'b', 2),
> (3, 'cd', null),
> (4, 'e', 3),
> (5, 'fg', null),
> (6, 'h', 4),
> (7, 'i', null),
> (8, 'j', 5)
> )
> 
> Below is what I came up with.  The "u" table computes an amount to add to get
> the next logical row number; the "x" table actually computes the logical row
> number; finally we group by the logical row number and use string_agg to get a
> single name for each row.
> 
> Is there an easier way to write this query, using some window function
> functionality that I'm not aware of :)?
> 
> Thanks
> Andrew
> 
> with 
> t (line_number, my_name, my_value) as (values 
> (1, 'a', 1),
> (2, 'b', 2),
> (3, 'c', null),
> (4, 'd', null),
> (5, 'e', 3),
> (6, 'f', null),
> (7, 'g', null),
> (8, 'h', 4),
> (9, 'i', null),
> (10, 'j', 5)),
> u as (
> select 
> line_number,
> my_name,
> my_value,
> case when lag(my_value, 1) over (order by line_number) is null then case when
> my_value is null then 0 else 1 end else 1 end amount_to_add
> from 
> t),
> x as (
> select 
> line_number,
> my_name,
> my_value,
> sum(amount_to_add) over (order by line_number) logical_line
> from 
> u)
> select
> logical_line,
> string_agg(my_name, ''),
> my_value
> from
> x
> group by
> logical_line,
> my_value
> order by
> logical_line

Hello.

The same transformation with a recursive CTE:


WITH RECURSIVE
source AS (
SELECT *
FROM (
VALUES
(1, 'a', 1),
(2, 'b', 2),
(3, 'c', null),
(4, 'd', null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)
) AS t(a,b,c)
)
, grouped_source AS (
SELECT
source.*
, 1 AS r
FROM source
WHERE source.a = 1
UNION ALL
SELECT
source.*
, (CASE WHEN grouped_source.c IS NULL AND source.c IS NULL
THEN grouped_source.r
ELSE grouped_source.r + 1
END) AS r
FROM
grouped_source
JOIN source ON source.a = grouped_source.a + 1
)
SELECT
r AS a,
string_agg(b, '') AS b,
MIN(c) AS c
FROM grouped_source
GROUP BY r
ORDER BY r


Though I do not know which one is easier to read / understand (for you) nor
which one performs better.

HTH,

Ladislav Lenart



-- 
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] files in database directory

[Grigory Smolkin]

On 08/02/2016 04:51 PM, Adrian Klaver wrote:

On 08/02/2016 06:44 AM, Grigory Smolkin wrote:

Hello, everyone!

I found some files in postgresql database directory:

t13_14363083
t13_14363081
t13_14363098

Some of them are quite old. Can someone explain their purpose?


https://www.postgresql.org/docs/9.5/static/storage-file-layout.html
"
... But for temporary relations, the file name is of the form 
tBBB_FFF, where BBB is the backend ID of the backend which created the 
file, and FFF is the filenode number.  ...

"

Thank you, I overlooked that part.

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [GENERAL] files in database directory

[Adrian Klaver]

On 08/02/2016 06:44 AM, Grigory Smolkin wrote:

Hello, everyone!

I found some files in postgresql database directory:

t13_14363083
t13_14363081
t13_14363098

Some of them are quite old. Can someone explain their purpose?


https://www.postgresql.org/docs/9.5/static/storage-file-layout.html
"
... But for temporary relations, the file name is of the form tBBB_FFF, 
where BBB is the backend ID of the backend which created the file, and 
FFF is the filenode number.  ...

"


--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




--
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

[GENERAL] files in database directory

[Grigory Smolkin]

Hello, everyone!

I found some files in postgresql database directory:

t13_14363083
t13_14363081
t13_14363098

Some of them are quite old. Can someone explain their purpose?

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [GENERAL] Force pg_hba.conf user with LDAP

[Joseph Kregloh]

On Mon, Aug 1, 2016 at 5:21 PM, Jeff Janes  wrote:

> On Mon, Aug 1, 2016 at 1:32 PM, John McKown
>  wrote:
> > On Mon, Aug 1, 2016 at 2:49 PM, Jeff Janes  wrote:
> >>
> >> On Mon, Aug 1, 2016 at 11:40 AM, Joseph Kregloh <
> jkreg...@sproutloud.com>
> >> wrote:
> >> > Hi,
> >> >
> >> > Is there a way to force the user being sent to LDAP?
> >> >
> >> > For example I have the following entry in my pg_hba.conf file:
> >> > hostapdb apuser   10.0.20.1/22   ldap
> >> > ldapserver="389-ds1.sl.com:389" ldapbasedn="dc=sl,dc=com"
> >> >
> >> > - I will be connecting as apuser.
> >> > - I will supply my own user's password.
> >> >
> >> > When PostgreSQL does the authentication I would like it to replace
> >> > apuser
> >> > with jkregloh.
> >> >
> >> > The reason why I want to do this is to limit power granted to a user.
> >> > For
> >> > example I want to be able to user my regular user jkregloh for
> everyday
> >> > things. But when I need super user actions I will login using apuser.
> >> > Now
> >> > this is easy enough to do without LDAP. But if I disable my user via
> >> > LDAP it
> >> > would remove access from both my regular user and my superuser, that's
> >> > the
> >> > functionality I am looking for.
> >> >
> >> > I am pretty sure this is not possible, but I am floating the question
> >> > anyways in hope of suggestions.
> >>
> >> I've wanted this as well, and for the same reason.  I think you are
> >> correct, that this is not currently possible.  Only authentication
> >> methods which inherently provide the authenticating user's username
> >> implement the pg_ident.conf mechanism.  LDAP does not independently
> >> provide a username, it only uses the one provided to it.
> >>
> >> I thought a quick and dirty solution would be stuff both user names
> >> (the authenticating username and the database username) into the
> >> existing username slot of the libpq protocol, separated by some
> >> obscure character.  Then break them apart on that character, and look
> >> in pg_ident.conf to make sure the specified authenticating user is
> >> allowed to connect as the specified database user.  I've never gotten
> >> around to implementing it, though, and I doubt it would be accepted
> >> into core with the "magic character" design.
> >>
> >> Cheers,
> >>
> >> Jeff
> >>
> >
> > Perhaps what is necessary is something akin to the UNIX "sudo" facility.
> > That is, an SQL statement prefix which, if used, runs the given SQL
> > statement as a PG superuser. You then GRANT(?) authority to that facility
> > like you would to a table or database or ... . E.g. GRANT SUDO TO
> SOMEBODY;
> > who could then do SUDO some other SQL statement; and that SQL statement
> > would be done as if the PG user was a superuser.
>
> You can do something like:
>
> create user jkregloh login noinherit;
> grant apuser to jkregloh;
>
>
> Now once he logs in as jkrogloh he can promote himself to apuser by
> using "set role apuser".  So it takes an intentional action to grant
> yourself extra powers, so should be effective at avoiding mistakes.
> It is not quite as emphatic as having to do an entirely separate
> login, however.  Also, if you want the user to inherit from some roles
> and not from others, I think you are out of luck with this approach.
> Finally if you have customized user settings by "alter role apuser set
> ..." those will not get processed when you do a "set role apuser".
>
>
Thanks, this makes sense. It's kind of like the sudo approach mentioned
earlier. They would also need to take an action to return back to their
original role.

-Joseph



> Cheers,
>
> Jeff
>
>
> --
> 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] Re: Query planner using hash join when merge join seems orders of magnitude faster

[Branden Visser]

Thanks for your reply Tom.

On Mon, Aug 1, 2016 at 6:56 PM, Tom Lane  wrote:
> Branden Visser  writes:
>> I just wanted to update that I've found evidence that fixing the
>> planner row estimation may not actually influence it to use the more
>> performant merge join instead of hash join. I have found instances
>> where the row estimation is *overestimated* by a magnitude of 4x
>> (estimates 2.4m rows) and still chooses hash join over merge join,
>> where merge join is much faster (45s v.s. 12s).
>
> I wonder why the merge join is faster exactly.  It doesn't usually have a
> huge benefit unless the inputs are presorted already.  The one case I can
> think of where it can win quite a lot is if the range of merge keys in one
> input is such that we can skip reading most of the other input.  (Extreme
> example: one input has keys 1..10, but the other input has keys 1..1.
> We only need to read the first 1% of the second input, assuming there's an
> index on its key column so that we don't have to read the whole thing
> anyway to sort it.)
>

The nature of the data is such that rows for tables `uv`, `ci` and `r`
aliases tend to be created and linked in unison, therefore maybe their
incremental ids and references may have some natural ordering in the
DB that coincidentally helps out the merge join?

> The planner is aware of that effect, but I wonder if it's misestimating it
> for some reason.

The uv.content_item_id field has 25% null values, if that helps
uncover anything. Aside from that it's a bit of a mystery to me.

> Anyway it would be worth looking closely at your EXPLAIN
> ANALYZE results to determine whether early-stop is happening or not.  It'd
> manifest as one join input node showing an actual number of rows returned
> that's less than you'd expect.
>

I think I'd need a little more detail on this to be able to
investigate it. The merge join shows a number of rows (~410k)
consistent with the sort that occurs just before and just after. Also
the join with result_items shows an expected increase in rows given my
data.

All that said adding an index on the r.content_item_id has improved
the performance of this query without having to fiddle with the
planner -- a pretty silly oversight :/

Let me know if there's anything in here that you want me to dig into
any further.

Cheers,
Branden


> 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] pg_restore out of memory

[Miguel Ramos]

Greetings,

To all those who helped out with this problem, I'm sorry it took me so
long to respond. For the record, the matter is solved, at least for us,
but we had to repeat a lot of things to make sure.


First, the "out of memory" problem repeated itself when restoring that
single table, after doing a schema-only restore and removing all
foreign key constraints.

Second, the other weirdness on this table was a couple of constraints
to make sure that the three arrays on each row were of the same length.
But that was not the problem.


It turned out that the backup file was the problem.

Plus, I'm convinced that this was our mistake, someone simply didn't
see an error message during backup or some copy of the file.
Also, we should have already repeated the backup and maybe we didn't.


We did a new custom dump from within latest pgadmin III, and that one
restored just fine (using the same old 9.1.8 pg_restore).

It could have been a bug in pg_dump 9.1.8 since the good backup was
from a newer version. But this didn't seem so likely and because
repeating a full backup would force us to keep the original database on
the server for a few more days (dumps take so long) we didn't do it.

We did however a dump of that single table with pg_dump 9.1.8 and that
one also restored just fine.



Our immediate practical problem is over.
The only complaint would be that the "out of memory" message is
unfriendly.
If you would find useful that we make some additional tests, or some
observation of the file, we would be glad to return the help.


Best Regards,

--
Miguel Ramos


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

[GENERAL] Upserting all excluded values

[hari.prasath]

Hi all,

 Is there any way to do insert on conflict update all the null rows with 
the excluded values.



For ex:



=>table1 will looks like=>table2 will 
looks like

Column |  Type   | ModifiersColumn |  Type  
 | Modifiers 

+-+---   
+-+---

 pk_t   | integer | not null pk_t1  | 
integer | not null
 c1 | integer |  col1   
| integer | 

 c2 | integer |  col2   
| integer | 

Indexes:  
Indexes:

"t_pkey" PRIMARY KEY, btree (pk_t)"t1_pkey" 
PRIMARY KEY, btree (pk_t1)



and for having left join result of table1 and table2 i have one view in the 
form of table name newtable


Column |  Type   | Modifiers | Storage | Stats target | Description 

+-+---+-+--+-

 pk_t   | integer |   | plain   |  | 

 c1 | integer |   | plain   |  | 

 pk_t1  | integer |   | plain   |  | 

 col1   | integer |   | plain   |  | 

Indexes:

"mvjt_pk_t_idx" UNIQUE, btree (pk_t)

"mvjt_c1_idx" btree (c1)



and for upserting i am using



>>insert into mvjt select * from t left join t1 on t.pk_t = t1.pk_t1 and 
pk_t1 in (select pk_t1 from log_t1) ON CONFLICT (pk_t) DO Update set 

pk_t1 = EXCLUDED.pk_t1, col1 = EXCLUDED.col1;



The above query is with n attributes(here n will be 2 columns pk_t1 and col1). 



Is there any simple method to update the conflict value to the table with 
lesser query.?






cheers

- Harry