Re: [GENERAL] Get tables ending with numbers

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

Re: [GENERAL] Get tables ending with numbers

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

[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]' Can someone

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] Can't restart Postgres

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 systemct

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

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

Re: [GENERAL] PostgreSQL corruption

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

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] database folder name and tables filenames

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

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

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

Re: [GENERAL] PostgreSQL corruption

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

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

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

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

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

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

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

[GENERAL] get inserted id from transaction - PG 9.2

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 ro

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

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 a

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

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

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

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

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

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

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

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

Re: [GENERAL] Can't restart Postgres

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 direc

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] Can't restart Postgres

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 fi

Re: [GENERAL] Can't restart Postgres

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)

Re: [GENERAL] Can't restart Postgres

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=

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] Can't restart Postgres

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

Re: [GENERAL] database folder name and tables filenames

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

[GENERAL] Can't restart Postgres

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 ce

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

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 ,

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

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

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

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

Re: [GENERAL] xmlelement AND timestamps.

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

Re: [GENERAL] database folder name and tables filenames

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

[GENERAL] postgresql how to duplicate rows in result.

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 th

Re: [GENERAL] database folder name and tables filenames

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 conven

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

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

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 o

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

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 extr

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

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 >

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

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 term

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

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)

Re: [GENERAL] database folder name and tables filenames

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

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

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

Re: [GENERAL] database folder name and tables filenames

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

Re: [GENERAL] database folder name and tables filenames

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.

[GENERAL] database folder name and tables filenames

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

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

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

Re: [GENERAL] Auto-Rollback option

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.

Re: [GENERAL] Auto-Rollback option

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 Nab

Re: [GENERAL] clone_schema function

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 refere

Re: [GENERAL] clone_schema function

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

[GENERAL] PostgreSQL Code of Conduct Draft

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 mail

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

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 postg

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

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 pi

Re: [GENERAL] Write from Postgres to SQL Server

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.

Re: [GENERAL] Documentation inconsistency (at least to me)

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 bo