Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

On 30 September 2016 at 10:47, Jay Knight wrote: >>What's max_worker_processes set to? > > 8 > >>One theory would be that, the worker might not have been available >>when you performed the query execution, but it just happened to be >>when you did the EXPLAIN ANALYZE > > This

Re: [GENERAL] Multi tenancy : schema vs databases

On 9/29/2016 2:25 PM, Venkata B Nagothi wrote: Since, you are saying there could be thousands of tenants, going for single-database-per-tenant could possibly end up in a very bad and complex database design. worse, it would also require each tenant to have unique connections, making

Re: [GENERAL] isnull() function in pgAdmin3

On 09/28/2016 02:24 PM, dudedoe01 wrote: In future can you copy and paste the error. It makes it easier to deal with. Thanks. This is with the DATE data type. I have 9.5 version installed. The type really does not matter. The

Re: [GENERAL] Multi tenancy : schema vs databases

From: Venkata B Nagothi Sent: Thursday, September 29, 2016 17:25 To: Rakesh Kumar Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Multi tenancy : schema vs databases On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar

[GENERAL] Getting the currently used sequence for a SERIAL column

Hi, we have up until now, been using pg_get_serial_sequence() to discover which sequence is in use, but can no longer do so due to two tables needing to share the same sequence (prior to being properly merged. No duplicate values, luckily). For one of the tables, pg_get_serial_sequence() won't be

Re: [GENERAL] isnull() function in pgAdmin3

This is with the DATE data type. I have 9.5 version installed. -- View this message in context: http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.html Sent from the PostgreSQL - general mailing list archive at

Re: [GENERAL] Filtering by UUID

Take a look at these links. It should give you a clue to avoid LIMIT / OFFSET. https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way Regards, Walter On Sep 29, 2016 19:19, "Guyren Howe"

Re: [GENERAL] Filtering by UUID

> On Sep 29, 2016, at 16:14 , Colin Morelli wrote: > > Well then...just like that you made me feel like a total idiot! Hah. > > I guess that would work fine. I just need to encode some additional > information in the pagination links that the API returns (a pagination

Re: [GENERAL] Filtering by UUID

Well then...just like that you made me feel like a total idiot! Hah. I guess that would work fine. I just need to encode some additional information in the pagination links that the API returns (a pagination "marker" would be a combination of created_at and uuid). I know this question is

Re: [GENERAL] Filtering by UUID

On Sep 29, 2016, at 16:03 , Colin Morelli wrote: > > Hey list, > > I'm using UUID primary keys in my application. I need to add pagination, and > am trying to avoid OFFSET/LIMIT. I do have a created_at timestamp that I > could use, but it's possible for multiple

[GENERAL] Filtering by UUID

Hey list, I'm using UUID primary keys in my application. I need to add pagination, and am trying to avoid OFFSET/LIMIT. I do have a created_at timestamp that I could use, but it's possible for multiple records to be created at the same timestamp (postgres offers millisecond precision here, I

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Alvaro Herrera writes: > Moreover I think getErrorText() as a whole is misconceived and should be > removed altogether (why pstrdup the string?). Indeed. I think bouncing the error back to the caller is misguided to start with, seeing that the caller is just going to

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

>What's max_worker_processes set to? 8 >One theory would be that, the worker might not have been available >when you performed the query execution, but it just happened to be >when you did the EXPLAIN ANALYZE This happens consistently this way. Every time I run it with explain analyze it uses

[GENERAL] executing os commands from a function

All I know this may sound like heresy since it involves executing an OS command from a function , but here goes After an insert in a table, I want to touch a file I.e After insert into table test values (100) I want in a dir to have file 100 I used plsh extension but I had to use two

Re: [HACKERS] [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Tom Lane wrote: > Thomas Kellerer writes: > > for some reason pg_upgrade failed on Windows 10 for me, with an error > > message that one specifc _vm file couldn't be copied. > > Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new > code for 9.6 and

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

On 30 September 2016 at 08:52, Jay Knight wrote: > So, why might postgres parallelize the query when I explain analyze it, but > not when I just run it by itself? One theory would be that, the worker might not have been available when you performed the query execution, but it

Re: [GENERAL] Multi tenancy : schema vs databases

On Fri, Sep 30, 2016 at 5:18 AM, Rakesh Kumar wrote: > > Hi > > I would like to know which technique is better for supporting > multi-tenancy= > applications, going upto hundreds or even thousands of tenants. > > 1 - One database with difference schemas (one schema

Re: [GENERAL] Query generator

2016-09-29 16:32 GMT-03:00 Julien Rouhaud : > On 29/09/2016 21:27, Vinicius Segalin wrote: > > Hi everyone, > > > > Hello, > > > Does anyone know a random query generator for Postgres? Something that > > gets my schema and, based on that, generates hundreds of different

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Thomas Kellerer writes: > for some reason pg_upgrade failed on Windows 10 for me, with an error message > that one specifc _vm file couldn't be copied. Hmm ... a _vm file would go through rewriteVisibilityMap(), which is new code for 9.6 and hasn't really gotten that much

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Adrian Klaver schrieb am 29.09.2016 um 22:55: After running a "vacuum full" on the table in question the upgrade goes through. Assuming you did that on old cluster? Yes, correct. I did that on the 9.5 cluster Where both clusters installed the same way? Yes. I always download the ZIP

Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

On 09/29/2016 12:50 PM, Thomas Kellerer wrote: Hello, for some reason pg_upgrade failed on Windows 10 for me, with an error message that one specifc _vm file couldn't be copied. When I try to copy that file manually everything works fine. After running a "vacuum full" on the table in question

Re: [GENERAL] lost synchronization with server: got message type "Z"

"Marek Petr" writes: > Remote psql client (application server) version is 9.2.16 and pg_lib_version > function of DBD::Pg module returned version 80412. Ouch. 8.4.x has been out of support for almost two years now, and 8.4.12 is a good four years old. You should try to get an

[GENERAL] Parallel query only when EXPLAIN ANALYZEd

Hello, I installed 9.6 on Windows 7, and am experimenting with the new parallel query feature. I've found a behavior that seems inconsistent to me. Consider these two tables: create table t1 as ( with r as ( SELECT generate_series(1,30) AS id, ceil(random()*25)::int as item,

[GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

Hello, for some reason pg_upgrade failed on Windows 10 for me, with an error message that one specifc _vm file couldn't be copied. When I try to copy that file manually everything works fine. After running a "vacuum full" on the table in question the upgrade goes through. One thing I noticed

Re: [GENERAL] Query generator

On 29/09/2016 21:27, Vinicius Segalin wrote: > Hi everyone, > Hello, > Does anyone know a random query generator for Postgres? Something that > gets my schema and, based on that, generates hundreds of different > queries with join, group by, etc. > You should try sqlsmith

Re: [GENERAL] Time travel?

On Thu, Sep 29, 2016 at 3:20 PM, Thomas Munro wrote: > On Fri, Sep 30, 2016 at 8:17 AM, Kevin Grittner wrote: > > On Thu, Sep 29, 2016 at 2:06 PM, Melvin Davidson > wrote: > > > >> I am using PostgreSQL 9.4.0 (Yes, I know

[GENERAL] Query generator

Hi everyone, Does anyone know a random query generator for Postgres? Something that gets my schema and, based on that, generates hundreds of different queries with join, group by, etc. Thank you.

Re: [GENERAL] Time travel?

On Fri, Sep 30, 2016 at 8:17 AM, Kevin Grittner wrote: > On Thu, Sep 29, 2016 at 2:06 PM, Melvin Davidson wrote: > >> I am using PostgreSQL 9.4.0 (Yes, I know 9.4.9 is most recent but >> it's out of my control) > > As long as the decision-maker is aware

Re: [GENERAL] lost synchronization with server: got message type "Z"

Thank you for the response. Remote psql client (application server) version is 9.2.16 and pg_lib_version function of DBD::Pg module returned version 80412. Remove connection via psql client: $ psql psql (9.2.16, server 9.2.13) Type "help" for help. postgres=# PQlibVersion

Re: [GENERAL] Time travel?

Hi Melvin: On 09/29/2016 12:06 PM, Melvin Davidson wrote: I list the creation time for a WAL file and it shows: /home/mdavidson/dba$ ls -l --time=ctime /d-log/pg_xlog/0001000D00C9 -rw--- 1 postgres postgres 16777216 Sep 29 07:14 /d-log/pg_xlog/0001000D00C9 ctime

[GENERAL] Multi tenancy : schema vs databases

Hi I would like to know which technique is better for supporting multi-tenancy= applications, going upto hundreds or even thousands of tenants. 1 - One database with difference schemas (one schema per tenant) or 2 - One database per tenant. The points to be considered are: 1 - which is more

Re: [GENERAL] Time travel?

On Thu, Sep 29, 2016 at 2:06 PM, Melvin Davidson wrote: > I am using PostgreSQL 9.4.0 (Yes, I know 9.4.9 is most recent but > it's out of my control) As long as the decision-maker is aware that 9.4.0 has known bugs (fixed in later minor releases) that can render the

[GENERAL] Time travel?

*So here is a strange question. I am using PostgreSQL 9.4.0 (Yes, I know 9.4.9 is most recent but it's out of my control) O/S is AWS Debian 3.16.7-2 (2014-11-06) x86_64 GNU/Linux. I list the creation time for a WAL file and it shows: /home/mdavidson/dba$ ls -l --time=ctime

Re: [GENERAL] Сreate parallel aggregate

Thank you for the detailed answer. I think a less obscure error message would be a good thing. On 09/29/2016 05:26 PM, Tom Lane wrote: Grigory Smolkin writes: I was trying to create a parallel aggregate with base_type parameter and failed postgres=# CREATE AGGREGATE

Re: [GENERAL] Large pg_xlog

On 09/29/2016 01:36 AM, Michael Paquier wrote: something is broken with your installation. Don't you have in your log files something like that? LOG: could not create archive status file "pg_xlog/archive_status/00010001.ready": No such file That was the problem. FATAL:

Re: [GENERAL] Сreate parallel aggregate

Grigory Smolkin writes: > I was trying to create a parallel aggregate with base_type parameter and > failed > postgres=# CREATE AGGREGATE ST_Extent_parallel ( > sfunc = ST_CombineBBox, > combinefunc = ST_CombineBBox, > finalfunc = box2d, >

[GENERAL] Сreate parallel aggregate

Hello, everyone! I was trying to create a parallel aggregate with base_type parameter and failed postgres=# CREATE AGGREGATE ST_Extent_parallel ( sfunc = ST_CombineBBox, combinefunc = ST_CombineBBox, finalfunc = box2d, stype = box3d, basetype =

Re: [GENERAL] Incrementally refreshed materialized view

We came to C and discontinued the version generating code in PL/pgSQL because of - Our testing showed that triggers in C give better performance than the ones in PL/pgSQL. Our opinion may be wrong. - If we can generate triggers and other updating codes in C, we may integrate it into PostgreSQL

Re: [GENERAL] Incrementally refreshed materialized view

Dear Adam. There are read-me files within each projects. They support only inner-joins and aggregations (sum, count, min, max, avg). The updating algorithm for matviews with min/max must be differ from without ones. When there are min and/or max, we have to use the base table, otherwise, not.

Re: [GENERAL] Incrementally refreshed materialized view

On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh wrote: > Dear, > > As it was recommended, we pushed our projects into github: > https://github.com/ntqvinh/PgMvIncrementalUpdate. > > 1) Synchronous incremental update > - For-each-row triggers are generated for all

Re: [GENERAL] Incrementally refreshed materialized view

Dear, As it was recommended, we pushed our projects into github: https://github.com/ntqvinh/PgMvIncrementalUpdate. 1) Synchronous incremental update - For-each-row triggers are generated for all changing events on all underlying tables. 2) Asynchronous (deferred) incremental update - Triggers

[GENERAL] Out of memory in pg_bulkload

Hello, sometimes, in pg_bulkload log, i see an "out of memory" error. We use Postgresql 8.4.8 with Pg_bulkload 3.1 but we have at least 2 Gb of free memory space and system machine does not use swap. We notice that it happens only sometimes; in other launch it works fine, with an higher number