Re: [GENERAL] Get tables ending with numbers

[Charles Clavadetscher]

Hello Sathesh

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sathesh S
> Sent: Mittwoch, 15. Februar 2017 07:17
> To: pgsql-general 
> Subject: [GENERAL] Get tables ending with numbers
> 
> Hi All,
> 
> Im trying to get tables ending with numbers (last 8 characters should be 
> numbers).
> 
> For example: I have the tables "test_20160215" and "test_20160131" and 
> "test_1". When i run the below sql im not
> getting any output.
> 
> Select relname from pg_class where relname like '%[0-9]'

You should use an operator for regexp:

CREATE TABLE test_20160215 (id integer);
CREATE TABLE

SELECT relname FROM pg_class WHERE relname ~ '[0-9]';
relname
---
[snip]
 test_20160215
[snip]

Or

SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$';
relname
---
 test_20160215
(1 row)

Regards
Charles

> Can someone please give some idea on this.
> 
> Thanks,
> Sathesh
> 




-- 
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] Get tables ending with numbers

[Tom Lane]

Sathesh S  writes:
> Im trying to get tables ending with numbers (last 8 characters should be 
> numbers).

> For example: I have the tables "test_20160215" and "test_20160131" and 
> "test_1". When i run the below sql im not getting any output.

> Select relname from pg_class where relname like '%[0-9]'

> Can someone please give some idea on this.

You're confusing SQL LIKE patterns with POSIX regexp patterns.  "%" is
a wildcard only in the former; "[...]" is special only in the latter.
(The great thing about standards is there are so many to choose from :-()

A correct implementation of your stated requirement is
where relname ~ '[0-9]$'
or if you want to be picky about "last 8 characters" you could do
where relname ~ '[0-9]{8}$'
See
https://www.postgresql.org/docs/current/static/functions-matching.html

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

[GENERAL] Get tables ending with numbers

[Sathesh S]

Hi All,

Im trying to get tables ending with numbers (last 8 characters should be 
numbers).

For example: I have the tables "test_20160215" and "test_20160131" and 
"test_1". When i run the below sql im not getting any output.

Select relname from pg_class where relname like '%[0-9]'

Can someone please give some idea on this.

Thanks,
Sathesh

Re: [GENERAL] Can't restart Postgres

[Gmail]

Sent from my iPad

> On Feb 14, 2017, at 9:47 PM, Shawn Thomas  wrote:
> 
> No it doesn’t matter if run with sudo, postgres or even root.  Debian 
> actually wraps the command and executes some some initial scripts with 
> different privileges but ends up making sure that Postgres ends up running 
> under the postgres user.  I get the same output if run with sudo:
> 
> sudo systemctl status postgresql@9.4-main.service -l
>Error: could not exec   start -D /var/lib/postgresql/9.4/main -l 
> /var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
> config_file="/etc/postgresql/9.4/main/postgresql.conf”
> 
> Thanks, though.
> 
> -
which start

Can you run start with -x ?

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

No it doesn’t matter if run with sudo, postgres or even root.  Debian actually 
wraps the command and executes some some initial scripts with different 
privileges but ends up making sure that Postgres ends up running under the 
postgres user.  I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service -l
   Error: could not exec   start -D /var/lib/postgresql/9.4/main -l 
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
config_file="/etc/postgresql/9.4/main/postgresql.conf”

Thanks, though.

-Shawn

> On Feb 14, 2017, at 5:12 PM, Adrian Klaver  wrote:
> 
> On 02/14/2017 05:00 PM, Adrian Klaver wrote:
>> On 02/14/2017 12:00 PM, Shawn Thomas wrote:
>>> Yes that would be the standard approach.  But the Debian package removes
>>> pg_ctl from it normal place and wraps it with a perl script in a way
>>> that makes it difficult to work with (it doesn’t accept the same
>>> arguments):
>>> 
>>> https://wiki.debian.org/PostgreSql#pg_ctl_replacement
>>> 
>>> @Mangnus, can you give me an example of how I might use pg_lsclusters
>>> and pg_ctlcluster?  I’ve tried:
>>> 
>> 
>> I do not see a sudo below or is it apparent whether you are doing this
>> as the postgres user.
>> 
>>> pg_ctlcluster 9.4 main start
>>> Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
>>> /var/log/postgresql/postgresql-9.4-main.log -s -o  -c
>>> config_file="/etc/postgresql/9.4/main/postgresql.conf”
>> 
>> Not sure how close Debian 8 is to Ubuntu 16.04(something I use), but
>> from your first post they look like they share the same startup scripts.
>> So something like:
>> 
>> sudo systemctl restart postgresql@9.4-main.service
> ^^^
>  Should be  start
>> 
>> 
>>> 
>>> -Shawn
>>> 
 On Feb 14, 2017, at 11:52 AM, Magnus Hagander > wrote:
 
 On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake > wrote:
 
On 02/14/2017 11:43 AM, Shawn Thomas wrote:
 
pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service;
enabled)
   Active: active (exited) since Tue 2017-02-14 10:48:18 PST;
50min ago
  Process: 28668 ExecStart=/bin/true (code=exited,
status=0/SUCCESS)
 Main PID: 28668 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service
 
 
What about if use pg_ctl as the postgres user? That will give you
a better idea.
 
 
 You don't want ot be doing that on a systemd system, but try a
 combination of pg_lsclusters and pg_ctlcluster. Might be you need to
 shut it down once that way before it realizes it's down,and then start
 it back up.
 
 
 --
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
>>> 
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: [GENERAL] get inserted id from transaction - PG 9.2

[Patrick B]

2017-02-15 12:19 GMT+13:00 Tom Lane :

> Patrick B  writes:
> > I'm simply doing an insert and I want to get the inserted id with a
> select.
> > I'm doing this all in the same transactions.
>
> > Example:
> > BEGIN;
> > INSERT INTO test (id,name,description) VALUES (default,'test 1','testing
> > insert');
> > SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here
>
> Maybe you meant "SELECT * FROM test", or at least "SELECT id FROM test"?
> Because that row certainly should be visible here.
>
> Having said that, the above coding seems rather broken, because it's just
> assuming that the new row will have the highest ID in the table.  Even if
> that's true at the instant of insertion, you have a race condition:
> another transaction could insert and commit a new row with a higher ID
> between your INSERT and your SELECT.
>
> The usual solution for this problem in PG is RETURNING:
>
> INSERT INTO test (id,name,description)
>   VALUES (default,'test 1','testing insert')
>   RETURNING id;
>
>
Thanks guys!

RETURNING id - it's what i was looking for.

Thanks a lot!
Patrick





> That will get you the generated column's value reliably, and it avoids
> one query roundtrip besides.
>
> regards, tom lane
>

Re: [GENERAL] PostgreSQL corruption

[James Sewell]

OK,

So with some help from the IRC channel (thanks macdice and JanniCash)  it's
come to light that my RAID1 comprised of 2 * 7200RPM disks is reporting
~500 ops/sec in pg_test_fsync.

This is higher than the ~120 ops/sec which you would expect from 720RPM
disks - therefore something is lying.

Breaking up the RAID and re-imaging with JBOD dropped this to 50 ops/sec -
another question but still looking like a real result.

So in this case it looks like the RAID controller wasn't disabling caching
as advertised.

Cheers,



James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Wed, Feb 15, 2017 at 10:29 AM, James Sewell 
wrote:

> That's the plan, but it's essentially a client managed embedded database
> so small steps needed. If I can prove it's the hardware first that would be
> preferable.
>
> It looks like diskcheck.pl doesn't work on Windows (no IO::Handle::sync)
> - does anybody know of an alternative testkit. A C one would be the best I
> suppose as it could exactly mimic PostgreSQL.
>
> Cheers,
>
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
>
>
>
> Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
> *P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com
> *F *(+61) 2 8099 9099 <(+61)%202%208099%209000>
>
> On Wed, Feb 15, 2017 at 4:10 AM, Magnus Hagander 
> wrote:
>
>> On Tue, Feb 14, 2017 at 5:21 AM, James Sewell 
>> wrote:
>>
>>> Hello All,
>>>
>>> I am working with a client who is facing issues with database corruption
>>> after a physical hard power off (the machines are at remote sites, this
>>> could be a power outage or user error).
>>>
>>> They have an environment made up of many of the following consumer grade
>>> stand alone machines:
>>>
>>>- Windows 7 SP1
>>>- PostgreSQL 9.2.4
>>>
>>>
>> If you're using 9.2.4, you are missing about 4 years worth of bugfixes.
>> While what you're talking aobut sounds like other issues, you should really
>> upgrade that to something that doesn't have loads of known bugs and then
>> re-run the tests.
>>
>> --
>>  Magnus Hagander
>>  Me: http://www.hagander.net/
>>  Work: http://www.redpill-linpro.com/
>>
>
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

On 02/14/2017 05:00 PM, Adrian Klaver wrote:

On 02/14/2017 12:00 PM, Shawn Thomas wrote:

Yes that would be the standard approach.  But the Debian package removes
pg_ctl from it normal place and wraps it with a perl script in a way
that makes it difficult to work with (it doesn’t accept the same
arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement

@Mangnus, can you give me an example of how I might use pg_lsclusters
and pg_ctlcluster?  I’ve tried:



I do not see a sudo below or is it apparent whether you are doing this
as the postgres user.


pg_ctlcluster 9.4 main start
Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”


Not sure how close Debian 8 is to Ubuntu 16.04(something I use), but
from your first post they look like they share the same startup scripts.
So something like:

sudo systemctl restart postgresql@9.4-main.service

 ^^^
  Should be  start





-Shawn


On Feb 14, 2017, at 11:52 AM, Magnus Hagander > wrote:

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake > wrote:

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service;
enabled)
   Active: active (exited) since Tue 2017-02-14 10:48:18 PST;
50min ago
  Process: 28668 ExecStart=/bin/true (code=exited,
status=0/SUCCESS)
 Main PID: 28668 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service


What about if use pg_ctl as the postgres user? That will give you
a better idea.


You don't want ot be doing that on a systemd system, but try a
combination of pg_lsclusters and pg_ctlcluster. Might be you need to
shut it down once that way before it realizes it's down,and then start
it back up.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/








--
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] Can't restart Postgres

[Adrian Klaver]

On 02/14/2017 12:00 PM, Shawn Thomas wrote:

Yes that would be the standard approach.  But the Debian package removes
pg_ctl from it normal place and wraps it with a perl script in a way
that makes it difficult to work with (it doesn’t accept the same arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement

@Mangnus, can you give me an example of how I might use pg_lsclusters
and pg_ctlcluster?  I’ve tried:



I do not see a sudo below or is it apparent whether you are doing this 
as the postgres user.



pg_ctlcluster 9.4 main start
Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”


Not sure how close Debian 8 is to Ubuntu 16.04(something I use), but 
from your first post they look like they share the same startup scripts. 
So something like:


sudo systemctl restart postgresql@9.4-main.service




-Shawn


On Feb 14, 2017, at 11:52 AM, Magnus Hagander > wrote:

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake > wrote:

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service;
enabled)
   Active: active (exited) since Tue 2017-02-14 10:48:18 PST;
50min ago
  Process: 28668 ExecStart=/bin/true (code=exited,
status=0/SUCCESS)
 Main PID: 28668 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service


What about if use pg_ctl as the postgres user? That will give you
a better idea.


You don't want ot be doing that on a systemd system, but try a
combination of pg_lsclusters and pg_ctlcluster. Might be you need to
shut it down once that way before it realizes it's down,and then start
it back up.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/





--
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] database folder name and tables filenames

[Adrian Klaver]

On 02/14/2017 09:47 AM, Mimiko wrote:
> On 14.02.2017 17:30, Adrian Klaver wrote:
>>> Is there a way to change postgres behavior to name database folders by
>>> the database name? And table files in them by table's name? And not
>>> using OIDs.
>>
>> No.
>>
>> Is there a particular problem you are trying to solve?
> 
> No, there is not a problem. Its a convenience to visually view databases
> and tables with theirs name and know what the size they occupy with
> using queries of pg_catalog, like there is in mysql.

Take look at:

https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT

So as example:

test=# select pg_size_pretty(pg_database_size('test'));
 pg_size_pretty 

 8464 kB
(1 row)


> 
> 
> On 14.02.2017 17:34, Tom Lane wrote:
>> It used to work like that, decades ago, and it caused enormous problems
>> during table/database renames.  We're not going back.
> 
> So this is the culprit. Isn't there any option to use names? Even when
> compiling?
> 
> 


-- 
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] Bad planning data resulting in OOM killing of postgres

[Tom Lane]

I wrote:
> David Hinkle  writes:
>> Thanks guys, here's the information you requested:
>> psql:postgres@cipafilter = show work_mem;
>> work_mem
>> ──
>> 10MB
>> (1 row)

> [ squint... ]  It should absolutely not have tried to hash a 500M-row
> table if it thought work_mem was only 10MB.  I wonder if there's an
> integer-overflow problem or something like that.

Ah, nah, scratch that: I was thinking of the hash aggregation case,
where there's no run-time ability to spill to disk so the planner
will not risk using hash aggregation if it estimates the hash table
would exceed work_mem.  Hash joins do have the ability to restrict
memory consumption by increasing the number of batches, so the planner
doesn't worry about it in that case.

I think what must be happening is that there's some one value of
log_raw.titleid that occurs a ridiculous number of times, so that
the executor is unable to split up that particular hash bucket,
leading to OOM when it tries to load all those rows to process
the hash bucket.

The planner does attempt to estimate the worst-case bucket size,
but in what now seems like brain fade, it doesn't do more with that
information than charge an appropriate number of tuple comparisons.
That would somewhat discourage use of a hash join, but a merge join
on this many tuples would be pretty expensive too, so it's not overly
surprising that it went with hashing anyway.

I am thinking we ought to fix it so it rejects (or at least heavily
penalizes) a hash join if it estimates that the rows containing
the inner side's most common value wouldn't all fit in work_mem.

What's not completely certain however is whether this diagnosis is
accurate for your case, or whether the proposed remedy would fix it.
It would be useful to see the contents of pg_stats.most_common_freqs
for log_raw.titleid.

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] PostgreSQL corruption

[James Sewell]

That's the plan, but it's essentially a client managed embedded database so
small steps needed. If I can prove it's the hardware first that would be
preferable.

It looks like diskcheck.pl doesn't work on Windows (no IO::Handle::sync) -
does anybody know of an alternative testkit. A C one would be the best I
suppose as it could exactly mimic PostgreSQL.

Cheers,

James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Wed, Feb 15, 2017 at 4:10 AM, Magnus Hagander 
wrote:

> On Tue, Feb 14, 2017 at 5:21 AM, James Sewell 
> wrote:
>
>> Hello All,
>>
>> I am working with a client who is facing issues with database corruption
>> after a physical hard power off (the machines are at remote sites, this
>> could be a power outage or user error).
>>
>> They have an environment made up of many of the following consumer grade
>> stand alone machines:
>>
>>- Windows 7 SP1
>>- PostgreSQL 9.2.4
>>
>>
> If you're using 9.2.4, you are missing about 4 years worth of bugfixes.
> While what you're talking aobut sounds like other issues, you should really
> upgrade that to something that doesn't have loads of known bugs and then
> re-run the tests.
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] get inserted id from transaction - PG 9.2

[Tom Lane]

Patrick B  writes:
> I'm simply doing an insert and I want to get the inserted id with a select.
> I'm doing this all in the same transactions.

> Example:
> BEGIN;
> INSERT INTO test (id,name,description) VALUES (default,'test 1','testing
> insert');
> SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here

Maybe you meant "SELECT * FROM test", or at least "SELECT id FROM test"?
Because that row certainly should be visible here.

Having said that, the above coding seems rather broken, because it's just
assuming that the new row will have the highest ID in the table.  Even if
that's true at the instant of insertion, you have a race condition:
another transaction could insert and commit a new row with a higher ID
between your INSERT and your SELECT.

The usual solution for this problem in PG is RETURNING:

INSERT INTO test (id,name,description)
  VALUES (default,'test 1','testing insert')
  RETURNING id;

That will get you the generated column's value reliably, and it avoids
one query roundtrip besides.

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] get inserted id from transaction - PG 9.2

[Steve Atkins]

> On Feb 14, 2017, at 2:55 PM, Patrick B  wrote:
> 
> Hi all,
> 
> I'm simply doing an insert and I want to get the inserted id with a select. 
> I'm doing this all in the same transactions.
> 
> Example:
> 
> BEGIN;
> 
> INSERT INTO test (id,name,description) VALUES (default,'test 1','testing 
> insert');
> SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here

You want "select * from test ..." or "select id from test ..." here. Should 
work fine then.

> 
> COMMIT;
> 
> I only can see that inserted row if I do the select outside of this 
> transaction.
> 
> How could I get that ? 

This'd be the idiomatic way of doing it:

INSERT INTO test (name,description) VALUES ('test 1','testing insert') 
RETURNING id;

Cheers,
  Steve



-- 
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] get inserted id from transaction - PG 9.2

[David G. Johnston]

On Tue, Feb 14, 2017 at 3:55 PM, Patrick B  wrote:

> Hi all,
>
> I'm simply doing an insert and I want to get the inserted id with a
> select. I'm doing this all in the same transactions.
>
> Example:
>
> BEGIN;
>
>
> INSERT INTO test (id,name,description) VALUES (default,'test 1','testing
> insert');
>
> SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here
>
>
>
SELECT  FROM test ...

Written correctly it w
orks for me...

CREATE TABLE testserial (id serial PRIMARY KEY);
BEGIN;
INSERT INTO testserial VALUES (DEFAULT);
SELECT * FROM testserial;

I see one row with id = 1 ...

SELECT version();
version
PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit

​Default transaction isolation level.​​


COMMIT;
>
>
> I only can see that inserted row if I do the select outside of this
> transaction.
>
> How could I get that ?
>
>
The easiest solution is:

INSERT INTO test [...]
RETURNING id;

David J.
​

[GENERAL] get inserted id from transaction - PG 9.2

[Patrick B]

Hi all,

I'm simply doing an insert and I want to get the inserted id with a select.
I'm doing this all in the same transactions.

Example:

BEGIN;


INSERT INTO test (id,name,description) VALUES (default,'test 1','testing
insert');

SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here


COMMIT;


I only can see that inserted row if I do the select outside of this
transaction.

How could I get that ?

Thanks!
Patrick

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

[Tom Lane]

David Hinkle  writes:
> Thanks guys, here's the information you requested:
> psql:postgres@cipafilter = show work_mem;
>  work_mem
> ──
>  10MB
> (1 row)

[ squint... ]  It should absolutely not have tried to hash a 500M-row
table if it thought work_mem was only 10MB.  I wonder if there's an
integer-overflow problem or something like that.

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] postgresql how to duplicate rows in result.

[John McKown]

On Tue, Feb 14, 2017 at 2:50 PM, Merlin Moncure  wrote:

> ​
>
>
> IMO, lateral join (available as of 9.3) is faster and simpler.
>

​And, nicely, I've learned something new. I've never used a LATERAL join
before. Interesting.​



>
> merlin
>



-- 
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

[Nikolai Zhubr]

14.02.2017 18:15, Rader, David:
[...]

Try the libpq call from pg_isready. It does actually make a round trip
to the postgres server and asks the server if it is ready to accept
connections. So you are running the socket communication code of
postgres and a small bit of "status" check but not any sql.


Indeed, such function is available. But essentially, this function is a 
(kind of) combined login+logout, therefore it would not work for my 
purpose. (Despite its name, it can not be used to perform some 
communication "ping" within an established session, such thing simply 
does not exist in the protocol, at least as of 9.5)



Regards,
Nikolai



from mirror:
https://github.com/postgres/postgres/blob/master/src/bin/scripts/pg_isready.c

relevant call is PQping:

rv = PQpingParams(keywords, values, 1);







--
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 how to duplicate rows in result.

[Merlin Moncure]

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure  wrote:
> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>  wrote:
>> Hi list,
>> sorry for my english, I will try to example as well. I've a query that joins
>> multiple tables and return a result like:
>>
>> id,customers,phone,code,number
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,21221,221 , 1
>>
>>
>> I need, where "number" field is > 1, to duplicate the row * N(number field
>> value) with a result like this:
>>
>> id,customers,phone,code,number
>> 1 , ,3,123 , 2
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,21221,221 , 1
>>
>> How I can accomplish to this problem?
>
> SELECT * FROM foo CROSS JOIN LATERAL (1,number);
>
> :-D

oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

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 how to duplicate rows in result.

[Merlin Moncure]

On Tue, Feb 14, 2017 at 12:42 PM, John McKown
 wrote:
> On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
>  wrote:
>>
>> Hi list,
>> sorry for my english, I will try to example as well. I've a query that
>> joins multiple tables and return a result like:
>>
>>
>> id,customers,phone,code,number
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,21221,221 , 1
>>
>>
>> I need, where "number" field is > 1, to duplicate the row * N(number field
>> value) with a result like this:
>>
>> id,customers,phone,code,number
>> 1 , ,3,123 , 2
>> 1 , ,3,123 , 2
>> 2 , aassdsds,33322,211 , 1
>> 3 , ,21221,221 , 1
>>
>> How I can accomplish to this problem?
>>
>> I'm using postgresql 9.3.15
>
>
> I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
> following recursive CTE should work for you.
>
> 
>
> tsh009=# \d baggi
>   Table "public.baggi"
>   Column   |  Type   | Modifiers
> ---+-+---
>  id| integer |
>  customers | text|
>  phone | text|
>  code  | integer |
>  number| integer |
>
> tsh009=# select * from baggi;
>  id | customers | phone | code | number
> +---+---+--+
>   1 |   | 3 |  123 |  2
>   2 | aassdsds  | 33322 |  211 |  1
>   3 |   | 21221 |  221 |  1
> (3 rows)
>
> tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
> select 1, id, customers, phone, code, number from baggi
> UNION ALL
> select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
> AS m, baggi AS b  where m.id = b.id and m.k < b.number )
> select id, customers, phone, code, number from multiple order by id
> ;
>  id | customers | phone | code | number
> +---+---+--+
>   1 |   | 3 |  123 |  2
>   1 |   | 3 |  123 |  2
>   2 | aassdsds  | 33322 |  211 |  1
>   3 |   | 21221 |  221 |  1
> (4 rows)

IMO, lateral join (available as of 9.3) is faster and simpler.

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] Can't restart Postgres

[Tom Lane]

Shawn Thomas  writes:
> I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running 
> Postgres instance (9.4) which caused it to shut down. The last line of 
> main.log:
> FATAL:  could not load server certificate file 
> "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

There's a bit more to it than that, because simply deleting that file would
not result in a live server shutting down; before v10, the server only
examines its certificate file at startup.

> I've since restored the cert but cannot get Postgres to start back up.

FWIW, I don't see how removing that file would result in a silent exit
without any error messages.  I suspect you did more damage to the PG
installation than you've realized.  As JD mentioned, permissions problems
on the executable are a possibility.  I'm also wondering if you broke
the logging configuration, such that PG tries to write an error log
message but can't, or it's writing it somewhere other than where you
expect.

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] Can't restart Postgres

[Shawn Thomas]

Yes that would be the standard approach.  But the Debian package removes pg_ctl 
from it normal place and wraps it with a perl script in a way that makes it 
difficult to work with (it doesn’t accept the same arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement 


@Mangnus, can you give me an example of how I might use pg_lsclusters and 
pg_ctlcluster?  I’ve tried:

pg_ctlcluster 9.4 main start
Error: could not exec   start -D /var/lib/postgresql/9.4/main -l 
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
config_file="/etc/postgresql/9.4/main/postgresql.conf” 

-Shawn

> On Feb 14, 2017, at 11:52 AM, Magnus Hagander  wrote:
> 
> On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake  > wrote:
> On 02/14/2017 11:43 AM, Shawn Thomas wrote:
> pangaea:/var/log# systemctl status postgresql
> ● postgresql.service - PostgreSQL RDBMS
>Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
>Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
>   Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>  Main PID: 28668 (code=exited, status=0/SUCCESS)
>CGroup: /system.slice/postgresql.service
> 
> What about if use pg_ctl as the postgres user? That will give you a better 
> idea.
> 
> You don't want ot be doing that on a systemd system, but try a combination of 
> pg_lsclusters and pg_ctlcluster. Might be you need to shut it down once that 
> way before it realizes it's down,and then start it back up. 
> 
> 
> -- 
>  Magnus Hagander
>  Me: http://www.hagander.net/ 
>  Work: http://www.redpill-linpro.com/ 

Re: [GENERAL] Can't restart Postgres

[Moreno Andreo]

Il 14/02/2017 20:31, Joshua D. Drake ha scritto:

On 02/14/2017 11:17 AM, Shawn Thomas wrote:
I inadvertently deleted the ssl-cert-snakeoil.pem out from under a 
running Postgres instance (9.4) which caused it to shut down. The 
last line of main.log:


FATAL:  could not load server certificate file 
"/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory


I've since restored the cert but cannot get Postgres to start back 
up.  It's the Debian 8 packaged version which complicates the 
debugging and troubleshooting.  There doesn't seem to be a way to do 
anything with Postgres outsided the of Debian's systemd wrappers.  
All I've got to work with is from /var/syslog:


pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
pangaea postgresql@9.4-main[28684]: Error: could not exec  start -D 
/var/lib/postgresql/9.4/main -l 
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
config_file="/etc/postgresql/9.4/main/postgresql.conf" :
pangaea systemd[1]: postgresql@9.4-main.service: control process 
exited, code=exited status=1


Any help/advice would be greatly appreciated.


It is likely a permissions issue. What does the systemctl log say?


I'd take a look with a simple ls -las in the certificate directory 
(/etc/ssl/certs)... Not being sure but your postgres user should be at 
least capable of reading it (the certificate), if not being the owner at 
all


Cheers
Moreno



JD



-Shawn









--
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] Can't restart Postgres

[Magnus Hagander]

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake 
wrote:

> On 02/14/2017 11:43 AM, Shawn Thomas wrote:
>
>> pangaea:/var/log# systemctl status postgresql
>> ● postgresql.service - PostgreSQL RDBMS
>>Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
>>Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
>>   Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>>  Main PID: 28668 (code=exited, status=0/SUCCESS)
>>CGroup: /system.slice/postgresql.service
>>
>
> What about if use pg_ctl as the postgres user? That will give you a better
> idea.


You don't want ot be doing that on a systemd system, but try a combination
of pg_lsclusters and pg_ctlcluster. Might be you need to shut it down once
that way before it realizes it's down,and then start it back up.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
  Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 28668 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service


What about if use pg_ctl as the postgres user? That will give you a 
better idea.


jD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
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] Can't restart Postgres

[Shawn Thomas]

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
  Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 28668 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

-Shawn

> On Feb 14, 2017, at 11:31 AM, Joshua D. Drake  wrote:
> 
> On 02/14/2017 11:17 AM, Shawn Thomas wrote:
>> I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running 
>> Postgres instance (9.4) which caused it to shut down. The last line of 
>> main.log:
>> 
>> FATAL:  could not load server certificate file 
>> "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory
>> 
>> I've since restored the cert but cannot get Postgres to start back up.  It's 
>> the Debian 8 packaged version which complicates the debugging and 
>> troubleshooting.  There doesn't seem to be a way to do anything with 
>> Postgres outsided the of Debian's systemd wrappers.  All I've got to work 
>> with is from /var/syslog:
>> 
>> pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
>> pangaea postgresql@9.4-main[28684]: Error: could not exec  start -D 
>> /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log 
>> -s -o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
>> pangaea systemd[1]: postgresql@9.4-main.service: control process exited, 
>> code=exited status=1
>> 
>> Any help/advice would be greatly appreciated.
> 
> It is likely a permissions issue. What does the systemctl log say?
> 
> JD
> 
>> 
>> -Shawn
>> 
> 
> 
> -- 
> Command Prompt, Inc.  http://the.postgres.company/ 
> 
>+1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
> Unless otherwise stated, opinions are my own.

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

On 02/14/2017 11:17 AM, Shawn Thomas wrote:

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running 
Postgres instance (9.4) which caused it to shut down. The last line of main.log:

FATAL:  could not load server certificate file 
"/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

I've since restored the cert but cannot get Postgres to start back up.  It's 
the Debian 8 packaged version which complicates the debugging and 
troubleshooting.  There doesn't seem to be a way to do anything with Postgres 
outsided the of Debian's systemd wrappers.  All I've got to work with is from 
/var/syslog:

pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
pangaea postgresql@9.4-main[28684]: Error: could not exec  start -D 
/var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
config_file="/etc/postgresql/9.4/main/postgresql.conf" :
pangaea systemd[1]: postgresql@9.4-main.service: control process exited, 
code=exited status=1

Any help/advice would be greatly appreciated.


It is likely a permissions issue. What does the systemctl log say?

JD



-Shawn




--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
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 folder name and tables filenames

[Tom Lane]

Mimiko  writes:
> On 14.02.2017 17:30, Adrian Klaver wrote:
>> Is there a particular problem you are trying to solve?

> No, there is not a problem. Its a convenience to visually view databases and 
> tables with theirs name and know what the size they occupy with using 
> queries of pg_catalog, like there is in mysql.

Don't really see why you need the underlying files to be named differently
for that purpose.  Typically people write something like
select relname, pg_relation_size(oid) from pg_class where ...

If you really want to do it from outside the database, the
contrib/oid2name program might help you.

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

[GENERAL] Can't restart Postgres

[Shawn Thomas]

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running 
Postgres instance (9.4) which caused it to shut down. The last line of main.log:

FATAL:  could not load server certificate file 
"/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

I've since restored the cert but cannot get Postgres to start back up.  It's 
the Debian 8 packaged version which complicates the debugging and 
troubleshooting.  There doesn't seem to be a way to do anything with Postgres 
outsided the of Debian's systemd wrappers.  All I've got to work with is from 
/var/syslog:

pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
pangaea postgresql@9.4-main[28684]: Error: could not exec  start -D 
/var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s 
-o  -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
pangaea systemd[1]: postgresql@9.4-main.service: control process exited, 
code=exited status=1

Any help/advice would be greatly appreciated.

-Shawn

-- 
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 how to duplicate rows in result.

[John McKown]

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi <
alessandro.ba...@gmail.com> wrote:

> Hi list,
> sorry for my english, I will try to example as well. I've a query that
> joins multiple tables and return a result like:
>
>
> ​​
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
>
> I need, where "number" field is > 1, to duplicate the row * N(number field
> value) with a result like this:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?
>
> I'm using postgresql 9.3.15


​I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
following recursive CTE should work for you.​



tsh009=# \d baggi
  Table "public.baggi"
  Column   |  Type   | Modifiers
---+-+---
 id| integer |
 customers | text|
 phone | text|
 code  | integer |
 number| integer |

tsh009=# select * from baggi;
 id | customers | phone | code | number
+---+---+--+
  1 |   | 3 |  123 |  2
  2 | aassdsds  | 33322 |  211 |  1
  3 |   | 21221 |  221 |  1
(3 rows)

tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
select 1, id, customers, phone, code, number from baggi
UNION ALL
select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
AS m, baggi AS b  where m.id = b.id and m.k < b.number )
select id, customers, phone, code, number from multiple order by id
;
 id | customers | phone | code | number
+---+---+--+
  1 |   | 3 |  123 |  2
  1 |   | 3 |  123 |  2
  2 | aassdsds  | 33322 |  211 |  1
  3 |   | 21221 |  221 |  1
(4 rows)







> .
>
> thanks in advance.
>
> Alessandro.
>


-- 
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] postgresql how to duplicate rows in result.

[Merlin Moncure]

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
 wrote:
> Hi list,
> sorry for my english, I will try to example as well. I've a query that joins
> multiple tables and return a result like:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
>
> I need, where "number" field is > 1, to duplicate the row * N(number field
> value) with a result like this:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

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 how to duplicate rows in result.

[Melvin Davidson]

On Tue, Feb 14, 2017 at 1:04 PM, Alessandro Baggi <
alessandro.ba...@gmail.com> wrote:

> Hi list,
> sorry for my english, I will try to example as well. I've a query that
> joins multiple tables and return a result like:
>
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
>
> I need, where "number" field is > 1, to duplicate the row * N(number field
> value) with a result like this:
>
> id,customers,phone,code,number
> 1 , ,3,123 , 2
> 1 , ,3,123 , 2
> 2 , aassdsds,33322,211 , 1
> 3 , ,21221,221 , 1
>
> How I can accomplish to this problem?
>
> I'm using postgresql 9.3.15.
>
> thanks in advance.
>
> Alessandro.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It would be immensely helpful if you provided the schema of tables involved
with original query.
In the meantime, I suggest you look into the use of UNION.

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

Re: [GENERAL] xmlelement AND timestamps.

[Lynn Dobbs]

Well, I couldn't reproduce allowing infinite timestamps in 9.2.4 either.

While fixing some other minor issues that came up in the migration to 
9.6.1, I copied a replaced good functions with bad ones.


The good functions called another function when putting my 
starting,ending columns in xml.  That function dates the timestamp or 
date and formats it and converts to text.


The documentation does say, "The particular behavior for individual data 
types is expected to evolve in order to align the SQL and PostgreSQL 
data types with the XML Schema specification, "  I didn't go chasing 
that down.  My bad.


My bad functions were written using the xml2 extension that treated the 
xml as text rather than "real" xml.


Lynn Dobbs
Chief Technical Office
CreditLink Corporation
858-496-1000 x 103

On 02/13/2017 06:10 PM, David G. Johnston wrote:
On Mon, Feb 13, 2017 at 6:36 PM, Adrian Klaver 
>wrote:


On 02/13/2017 02:56 PM, Lynn Dobbs wrote:

I just migrated from 9.2.4 to 9.6.1 and had several user created
functions fail.

Recreating the failure with "SELECT xmlelement(name foo,
'infinity'::timestamp)
ERROR: timestamp out of range
DETAIL: XML does not support infinite timestamp values.

I don't find anything in the documentation that explains this.  I
consider this a regression.


All I could find was this thread from 2009:

https://www.postgresql.org/message-id/41F26B729B014C3E8F20F5B7%40teje


which indicated it was fixed at that time.


​Actually, the cause of said commit (circa 2009) is that xmlelements 
were failing but xmlattributes were not​...which makes me wonder how a 
9.2 era release (circa 2012) supposedly worked with the submitted 
expression.


The basic answer is that the XML data type is defined by standard and 
we attempt to conform to the standard.  In order to do so we must 
disallow infinite timestamps even though we accept them in SQL.


I just tested the OP query on 9.0.x and 9.4.x and get the same error 
on both.


There'd be a bit more sympathy if the OP were complaining about a 
patch release changing behavior - bug fix or not - but since the 
complaint involves going from 9.2 to 9.6 on its face this is an 
allowed behavior change regardless of the history.


However, feel free to make a straight argument for accepting infinite 
timestamps and thus go above-and-beyond the relevant standards.  
Personally this seems like a not-so-useful restriction on our 
implementation.  Let the user decide whether they want to deviate from 
the standard and risk cross-system incompatibilities.  XML itself is 
textual and we don't have any internal support for DTD or Schema as it 
is so I'm not sure what material benefit we gain by restraining 
ourselves here.


David J.

Re: [GENERAL] database folder name and tables filenames

[Pavel Stehule]

2017-02-14 18:47 GMT+01:00 Mimiko :

> On 14.02.2017 17:30, Adrian Klaver wrote:
>
>> Is there a way to change postgres behavior to name database folders by
>>> the database name? And table files in them by table's name? And not
>>> using OIDs.
>>>
>>
>> No.
>>
>> Is there a particular problem you are trying to solve?
>>
>
> No, there is not a problem. Its a convenience to visually view databases
> and tables with theirs name and know what the size they occupy with using
> queries of pg_catalog, like there is in mysql.


There are a databases where you can see only one file or few files without
any relation to tables.


>
>
>
> On 14.02.2017 17:34, Tom Lane wrote:
> > It used to work like that, decades ago, and it caused enormous problems
> > during table/database renames.  We're not going back.
>
> So this is the culprit. Isn't there any option to use names? Even when
> compiling?


It is not possible.

Regards

Pavel

>
>
>
> --
> Mimiko desu.
>
>
>
> --
> 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 how to duplicate rows in result.

[Alessandro Baggi]

Hi list,
sorry for my english, I will try to example as well. I've a query that 
joins multiple tables and return a result like:



id,customers,phone,code,number
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1


I need, where "number" field is > 1, to duplicate the row * N(number 
field value) with a result like this:


id,customers,phone,code,number
1 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1

How I can accomplish to this problem?

I'm using postgresql 9.3.15.

thanks in advance.

Alessandro.



--
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 folder name and tables filenames

[Mimiko]

On 14.02.2017 17:30, Adrian Klaver wrote:

Is there a way to change postgres behavior to name database folders by
the database name? And table files in them by table's name? And not
using OIDs.


No.

Is there a particular problem you are trying to solve?


No, there is not a problem. Its a convenience to visually view databases and tables with theirs name and know what the size they occupy with using 
queries of pg_catalog, like there is in mysql.



On 14.02.2017 17:34, Tom Lane wrote:
> It used to work like that, decades ago, and it caused enormous problems
> during table/database renames.  We're not going back.

So this is the culprit. Isn't there any option to use names? Even when 
compiling?


--
Mimiko desu.


--
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] Use full text to rank results higher if they are "closer hit"

[Thomas Nyberg]

Excellent great info! To save the extra mailing list pings, thanks to 
_everyone_ this is exactly what I was looking for.


Cheers,
Thomas


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

[Magnus Hagander]

On Tue, Feb 14, 2017 at 5:21 AM, James Sewell 
wrote:

> Hello All,
>
> I am working with a client who is facing issues with database corruption
> after a physical hard power off (the machines are at remote sites, this
> could be a power outage or user error).
>
> They have an environment made up of many of the following consumer grade
> stand alone machines:
>
>- Windows 7 SP1
>- PostgreSQL 9.2.4
>
>
If you're using 9.2.4, you are missing about 4 years worth of bugfixes.
While what you're talking aobut sounds like other issues, you should really
upgrade that to something that doesn't have loads of known bugs and then
re-run the tests.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Use full text to rank results higher if they are "closer hit"

[Artur Zakirov]

On 14.02.2017 18:35, Thomas Nyberg wrote:


Here both 'hello' and 'hello world' are ranked equally highly when
searching with 'hello'. What I'm wondering is, is there a way within
postgres to have it match higher to just 'hello' than 'hello world'?
I.e. something like it slightly down-weights extraneous terms? Of course
in general I don't know the query or the field strings ahead of time.

Thanks for any help!

Cheers,
Thomas




Hello,

try the query:

SELECT s, ts_rank(vector, query) AS rank
FROM t, to_tsvector(s) vector, to_tsquery('hello') query
WHERE query @@ vector;
  s  |   rank
-+---
 hello   | 0.0607927
 hello world | 0.0303964
(2 rows)

And read about **normalization** in [1]

https://www.postgresql.org/docs/current/static/textsearch-controls.html

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
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] Use full text to rank results higher if they are "closer hit"

[Adrian Klaver]

On 02/14/2017 07:35 AM, Thomas Nyberg wrote:
> Hello,
> 
> I think it's easier to explain my question with example code:
> 
> 
> CREATE TABLE t ( s VARCHAR );
> CREATE TABLE
> 
> INSERT INTO t VALUES ('hello'), ('hello world');
> INSERT 0 2
> 
> SELECT * FROM t;
>   s
> -
>  hello
>  hello world
> (2 rows)
> 
> SELECT s, ts_rank(vector, query) AS rank
> FROM t, to_tsvector(s) vector, to_tsquery('hello') query
> WHERE query @@ vector;
>   s  |   rank
> -+---
>  hello   | 0.0607927
>  hello world | 0.0607927
> (2 rows)
> 
> 
> Here both 'hello' and 'hello world' are ranked equally highly when
> searching with 'hello'. What I'm wondering is, is there a way within
> postgres to have it match higher to just 'hello' than 'hello world'?
> I.e. something like it slightly down-weights extraneous terms? Of course
> in general I don't know the query or the field strings ahead of time.

Some digging around found this:

https://www.postgresql.org/docs/9.6/static/textsearch-controls.html#TEXTSEARCH-RANKING

Setting a normalization of 1:

test=# SELECT s, ts_rank(vector, query, 1) AS rank
FROM t, to_tsvector(s) vector, to_tsquery('hello') query
WHERE query @@ vector; 
  s  |   rank
-+---
 hello   | 0.0607927
 hello world | 0.0383559

> 
> Thanks for any help!
> 
> Cheers,
> Thomas
> 
> 


-- 
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] Use full text to rank results higher if they are "closer hit"

[Tom Lane]

Thomas Nyberg  writes:
> Here both 'hello' and 'hello world' are ranked equally highly when 
> searching with 'hello'. What I'm wondering is, is there a way within 
> postgres to have it match higher to just 'hello' than 'hello world'? 
> I.e. something like it slightly down-weights extraneous terms? Of course 
> in general I don't know the query or the field strings ahead of time.

Read the documentation for ts_rank --- there's a normalization option
for that.

I'd also suggest you might prefer using ts_rank_cd, which is supposed to
penalize cases where the matching words aren't close together.

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] Use full text to rank results higher if they are "closer hit"

[Artur Zakirov]

On 14.02.2017 18:57, Artur Zakirov wrote:


Hello,

try the query:

SELECT s, ts_rank(vector, query) AS rank
FROM t, to_tsvector(s) vector, to_tsquery('hello') query
WHERE query @@ vector;
  s  |   rank
-+---
 hello   | 0.0607927
 hello world | 0.0303964
(2 rows)


Sorry, the query is:

SELECT s, ts_rank(vector, query, 2) AS rank
FROM t, to_tsvector(s) vector, to_tsquery('hello') query
WHERE query @@ vector;

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
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] database folder name and tables filenames

[Adrian Klaver]

On 02/14/2017 07:19 AM, Mimiko wrote:

Hello.

Is there a way to change postgres behavior to name database folders by
the database name? And table files in them by table's name? And not
using OIDs.


No.

Is there a particular problem you are trying to solve?








--
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] Use full text to rank results higher if they are "closer hit"

[Thomas Nyberg]

Hello,

I think it's easier to explain my question with example code:


CREATE TABLE t ( s VARCHAR );
CREATE TABLE

INSERT INTO t VALUES ('hello'), ('hello world');
INSERT 0 2

SELECT * FROM t;
  s
-
 hello
 hello world
(2 rows)

SELECT s, ts_rank(vector, query) AS rank
FROM t, to_tsvector(s) vector, to_tsquery('hello') query
WHERE query @@ vector;
  s  |   rank
-+---
 hello   | 0.0607927
 hello world | 0.0607927
(2 rows)


Here both 'hello' and 'hello world' are ranked equally highly when 
searching with 'hello'. What I'm wondering is, is there a way within 
postgres to have it match higher to just 'hello' than 'hello world'? 
I.e. something like it slightly down-weights extraneous terms? Of course 
in general I don't know the query or the field strings ahead of time.


Thanks for any help!

Cheers,
Thomas


--
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 folder name and tables filenames

[Adrian Klaver]

On 02/14/2017 07:19 AM, Mimiko wrote:

Hello.

Is there a way to change postgres behavior to name database folders by
the database name? And table files in them by table's name? And not
using OIDs.


For more information see:

https://www.postgresql.org/docs/9.6/static/storage-file-layout.html







--
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] database folder name and tables filenames

[Tom Lane]

Mimiko  writes:
> Is there a way to change postgres behavior to name database folders by the 
> database name? And table files in them by table's name? And not using OIDs.

It used to work like that, decades ago, and it caused enormous problems
during table/database renames.  We're not going back.

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

[GENERAL] database folder name and tables filenames

[Mimiko]

Hello.

Is there a way to change postgres behavior to name database folders by the 
database name? And table files in them by table's name? And not using OIDs.


--
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: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

[Rader, David]

--
David Rader
dav...@openscg.com

On Tue, Feb 14, 2017 at 5:28 AM, Nikolai Zhubr  wrote:

> 14.02.2017 12:47, John R Pierce:
>
>> On 2/13/2017 11:03 PM, Nikolai Zhubr wrote:
>>
>>> Now I'd like to locate a CPU eater more precisely - supposedly there
>>> is some issue with communication, that is why I don't want to mix in
>>> anything else.
>>>
>>
>> use iperf to test the network transport layer, without any postgres in
>> the loop?
>>
>
> No, the network performance in general is fine. However, some specific
> continuous communication pattern causes something to go very wrong on
> windows periodically. (And there is no such problem on linux btw) While the
> issue now seems rather related to windows itself and not postgres, but the
> pattern typical for some of my postgres uses is apparently a reliable
> trigger. Therefore, I'm thinking to first isolate the problem inside
> postgres, and if it still triggers then, second step, create a test
> "server" consisting of communication code of postgres to run without any
> other parts of postgres.
>
>
> Regards,
> Nikolai
>
>
>
>>
>>
>
Try the libpq call from pg_isready. It does actually make a round trip to
the postgres server and asks the server if it is ready to accept
connections. So you are running the socket communication code of postgres
and a small bit of "status" check but not any sql.

from mirror:
https://github.com/postgres/postgres/blob/master/src/bin/scripts/pg_isready.c

relevant call is PQping:

rv = PQpingParams(keywords, values, 1);

Re: [GENERAL] Auto-Rollback option

[Adrian Klaver]

On 02/14/2017 05:12 AM, mpomykacz wrote:

Ok, thanks for the answers. But unfortunatelly they did not solve my problem.


Still not actually sure what the issue is?:

1) Problem with pgAdmin setup

or

2) Broader issue of having Postgres rollback automatically on a error.

or

3) Patch management.



I will move it to the pgadmin subforum.
Thanks:)



--
View this message in context: 
http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944159.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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] Auto-Rollback option

[mpomykacz]

Ok, thanks for the answers. But unfortunatelly they did not solve my problem.
I will move it to the pgadmin subforum.
Thanks:)



--
View this message in context: 
http://postgresql.nabble.com/Auto-Rollback-option-tp5943942p5944159.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] clone_schema function

[Melvin Davidson]

On Tue, Feb 14, 2017 at 7:46 AM, Michael Librodo
  wrote:

> I had to modify the portion that copies FK constraint:
>
> https://gist.github.com/mlibrodo/6f246c483e650dc716eba752a9d3c79a
>
> Basically, the issue on my end was that the FK constraints on the
> cloned(destination) schema seem to reference the source_schema
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Michael,
Perhaps you can combine that code with the original by Emanuel '3manuek'
and that I modified to be more inclusive
of additional objects. See attached.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: clone_schema(text, text, boolean)

-- DROP FUNCTION clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2015-09-20 by Melvin Davidson
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
  IF FOUND
THEN 
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;

  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
   AND objsubid = 0;
  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;
  
  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)
  LOOP
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

EXECUTE 'SELECT last_value, max_value, start_value, increment_by, 
min_value, cache_value, log_cnt, is_cycled, is_called 
  FROM ' || quote_ident(source_schema) || '.' || 
quote_ident(object) || ';' 
  INTO sq_last_value, sq_max_value, sq_start_value, 
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, 
sq_is_called ; 

IF sq_is_cycled 
  THEN 
sq_cycled := 'CYCLE';
ELSE
sq_cycled := 'NO CYCLE';
END IF;

EXECUTE 'ALTER SEQUENCE '   || quote_ident(dest_schema) || '.' || 
quote_ident(object) 
|| ' INCREMENT BY ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value 
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH '   || sq_start_value
|| ' RESTART '  || sq_min_value 
|| ' CACHE '|| sq_cache_value 
|| sq_cycled || ' ;' ;

buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs 
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || 
', ' || sq_is_called || ');' ; 
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value 
|| ', ' || sq_is_called || ');' ;
END IF;

  -- add sequence comments
SELECT oid INTO tbl_oid
  FROM pg_class 
 WHERE relkind = 'S'
   AND relnamespace = src_oid
   AND relname = quote_ident(object);

SELECT description INTO v_def
  FROM pg_description
 WHERE objoid = tbl_oid
   AND objsubid = 0;

IF FOUND
  THEN  
  

Re: [GENERAL] clone_schema function

[Michael Librodo <mike.librodo(at)gmail(dot)com>]

I had to modify the portion that copies FK constraint:

https://gist.github.com/mlibrodo/6f246c483e650dc716eba752a9d3c79a

Basically, the issue on my end was that the FK constraints on the 
cloned(destination) schema seem to reference the source_schema




--
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 Code of Conduct Draft

[Dave Page]

The revised draft of the proposed Code of Conduct for the PostgreSQL
community is at https://wiki.postgresql.org/wiki/Code_of_Conduct.

This updated draft incorporates comments and suggestions from the
community received at PgCon Ottawa and subsequent discussion.

We will not be monitoring the mailing lists for comments or suggested
changes. If you have comments, please email them to
coc-comme...@postgresql.org no later than 2017-03-05, 11:59PM GMT for
the committee to review.

Regards, Dave.

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


-- 
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: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

[Nikolai Zhubr]

14.02.2017 12:47, John R Pierce:

On 2/13/2017 11:03 PM, Nikolai Zhubr wrote:

Now I'd like to locate a CPU eater more precisely - supposedly there
is some issue with communication, that is why I don't want to mix in
anything else.


use iperf to test the network transport layer, without any postgres in
the loop?


No, the network performance in general is fine. However, some specific 
continuous communication pattern causes something to go very wrong on 
windows periodically. (And there is no such problem on linux btw) While 
the issue now seems rather related to windows itself and not postgres, 
but the pattern typical for some of my postgres uses is apparently a 
reliable trigger. Therefore, I'm thinking to first isolate the problem 
inside postgres, and if it still triggers then, second step, create a 
test "server" consisting of communication code of postgres to run 
without any other parts of postgres.



Regards,
Nikolai








--
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: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

[John R Pierce]

On 2/13/2017 11:03 PM, Nikolai Zhubr wrote:
Now I'd like to locate a CPU eater more precisely - supposedly there 
is some issue with communication, that is why I don't want to mix in 
anything else.


use iperf to test the network transport layer, without any postgres in 
the loop?



--
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] Write from Postgres to SQL Server

[John R Pierce]

On 2/13/2017 8:53 PM, Guyren Howe wrote:
I am also interested to know if the SQL Server FDW extension works 
reliably, or any other advice folks may have.




If I was where you were, I'd be testing that FDW, finding any 
limitations that might effect your use cases, and working with/around them.





--
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] Documentation inconsistency (at least to me)

[Francisco Olarte]

Thomas:

On Mon, Feb 13, 2017 at 11:26 PM, Thomas Kellerer  wrote:
> I wonder why regexp_split_to_array() is listed under "String functions and
> operators" [1] but string_to_array() is listed under "Array functions and
> operators" [2]
>
> I find that a bit inconsistent - I would expect to find both in the same
> chapter.

Seen the description of string_to_array I would assume it is there to
pair with array_to_string, which seems to its dual, as a way to
[un]marshal arrays with potential nulls. OTOH regexp_split is not
easily invertible and performs the type of operation you normally do
with more free-form text, user input and the like.

> I would suggest to put both into "String functions and operators" because
> after all string_to_array() does more or less the same as
> regexp_split_to_array() does. But at the end of the day I think it's just
> important that both are in the same chapter (unless I overlooked a huge
> difference between the two that warrants this distinction).

Given the above I would certainly expect array_to_string in array
functions. Then I would expect string_to_array next to it. And any
regexp related function clustered with its siblings, either in the
string page or ( in another manuals ) in its dedicated section.

Francisco Olarte.


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