[GENERAL] Out of memory in pg_bulkload

[Job]

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 of record to be loaded into DB.

What could happen?
Thank you,
Francesco

-- 
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] Incrementally refreshed materialized view

[Nguyễn Trần Quốc Vinh]

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 are generated for all changing events on all underlying tables
to collect all changed rows
- Other codes are generated for each matview-query to update the matview.

 We hope that our projects may be helpful for someone!




TS. Nguyễn Trần Quốc Vinh
---
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn ; http://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh 
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98

Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn ;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh 
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Tue, Sep 27, 2016 at 12:23 PM, hariprasath nallasamy <
hariprasathnallas...@gmail.com> wrote:

> We also tried to achieve incremental refresh of materialized view and our
> solution doesn't solve all of the use cases.
>
> Players:
> 1) WAL
> 2) Logical decoding
> 3) replication slots
> 4) custom background worker
>
> Two kinds of approaches :
> 1. Deferred refresh (oracle type of creating log table for each base
> tables with its PK and agg's columns old and new values)
>   a) Log table for each base table has to be created and this log
> table will keep track of delta changes.
>   b) UDF is called to refresh the view incrementally - this will
> run original materialized view query with the tracked delta PK's in their
> where clause. so only rows that are modified/inserted will be touched.
>   c) Log table will keep track of changed rows from the data given by
> replication slot which uses logical decoding to decode from WAL.
>   d) Shared memory is used to maintain the relationship between the
> view and its base table. In case of restart they are pushed to maintenance
> table.
>
> 2. RealTime refresh (update the view whenever we get any change-sets
> related to that base tables)
>   a) Delta data from the replication slot will be applied to view by
> checking the relationship between our delta data and the view definiton.
> Here also shared memory and maintenance table are used.
>   b) Work completed only for materialized views having single table.
>
> Main disadvantage :
> 1) Data inconsistency when master failure and also slave doesn't have
> replication slot as of now. But 2ndquard guys try to create slots in slave
> using some concepts of failover slots. But that doesn't come along with PG
> :(.
> 2) Sum, count and avg are implemented for aggregates(single table) and for
> other aggs full refresh comes to play a role.
> 3) Right join implementation requires more queries to run on the top of
> MV's.
>
> So we are on a long way to go and dono whether this is the right path.
>
> Only deferred refresh was pushed to github.
> https://github.com/harry-2016/MV_IncrementalRefresh
>
> I wrote a post regarding that in medium.
> https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-
> incremental-refresh-44d1ca742599
>
>
>

Re: [GENERAL] Incrementally refreshed materialized view

[Adam Brusselback]

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 changing events on all
> underlying tables.
>
> 2) Asynchronous (deferred) incremental update
> - Triggers are generated for all changing events on all underlying tables
> to collect all changed rows
> - Other codes are generated for each matview-query to update the matview.
>
>  We hope that our projects may be helpful for someone!
>

Very interesting. Does this support materialized views with recursive
queries? What about left joins? (not) exists? Aggregates? Window functions?
In reading up on the implementations in other databases, I was surprised by
some of the limitations imposed by DB2 / Oracle / Sql Server.

I'm trying to look through the code base to answer my questions, but it's
large enough that it may be easier to just ask first.

Re: [GENERAL] Incrementally refreshed materialized view

[Nguyễn Trần Quốc Vinh]

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.

About 15 years ago we implemented for SPJ matvew-queries to generate
triggers in PL/pgSQL. We developed that project for queries with
aggregations about 4 years ago. Unfortunately we lost the last versions. We
have now only the version with some error when there are aggregations. For
SPJ queries it works well. We don't know is it useful or not. If yes, we
can upload for sharing. We didn't share it because we thought that our work
is not good enough for public use.

The projects that generate C-codes was "finished" 2 years ago.

Concern the project that generates triggers (doing incremental update) in
PL/pgSQL, we implemented the algorithm likes your suggestion in this
thread, i.e. at least one key of the base tables are added  automatically
into the mat-view queries for further incremental updates.

TS. Nguyễn Trần Quốc Vinh
---
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn ; http://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh 
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98

Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn ;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh 
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Thu, Sep 29, 2016 at 8:37 PM, Adam Brusselback  wrote:

> On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh <
> ntquocv...@gmail.com> 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 changing events on all
>> underlying tables.
>>
>> 2) Asynchronous (deferred) incremental update
>> - Triggers are generated for all changing events on all underlying tables
>> to collect all changed rows
>> - Other codes are generated for each matview-query to update the matview.
>>
>>  We hope that our projects may be helpful for someone!
>>
>
> Very interesting. Does this support materialized views with recursive
> queries? What about left joins? (not) exists? Aggregates? Window functions?
> In reading up on the implementations in other databases, I was surprised by
> some of the limitations imposed by DB2 / Oracle / Sql Server.
>
> I'm trying to look through the code base to answer my questions, but it's
> large enough that it may be easier to just ask first.
>

Re: [GENERAL] Incrementally refreshed materialized view

[Nguyễn Trần Quốc Vinh]

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 source codes. We may be wrong too.

:)

We plan to do by the same way for queries with outer-joins next year. With
recursive queries we have no plan because we don't see any effective update
algorithm. The worst is that we have no fund to do with matviews and
PostgreSQL. All that just for fun! We have too many things to do each day.

TS. Nguyễn Trần Quốc Vinh
---
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn ; http://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh 
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98

Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn ;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh 
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Thu, Sep 29, 2016 at 8:56 PM, Nguyễn Trần Quốc Vinh  wrote:

> 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.
>
> About 15 years ago we implemented for SPJ matvew-queries to generate
> triggers in PL/pgSQL. We developed that project for queries with
> aggregations about 4 years ago. Unfortunately we lost the last versions. We
> have now only the version with some error when there are aggregations. For
> SPJ queries it works well. We don't know is it useful or not. If yes, we
> can upload for sharing. We didn't share it because we thought that our work
> is not good enough for public use.
>
> The projects that generate C-codes was "finished" 2 years ago.
>
> Concern the project that generates triggers (doing incremental update) in
> PL/pgSQL, we implemented the algorithm likes your suggestion in this
> thread, i.e. at least one key of the base tables are added  automatically
> into the mat-view queries for further incremental updates.
>
> TS. Nguyễn Trần Quốc Vinh
> ---
> Chủ nhiệm khoa Tin học
> Trường ĐH Sư phạm - ĐH Đà Nẵng
> Website: http://it.ued.vn ; http://www.ued.vn
> ; http://www.ued.udn.vn
> LLKH: http://scv.ued.vn/~ntquocvinh 
> ĐT: 0511.6-512-586
> DĐ: 0914.78-08-98
> 
> Nguyen Tran Quoc Vinh, PhD
> Dean
> Faculty of Information Technology
> Danang University of Education
> Website: http://it.ued.udn.vn; http://www.ued.vn 
> ; http://www.ued.udn.vn
> SCV: http://scv.ued.vn/~ntquocvinh 
> Phone: (+84) 511.6-512-586
> Mobile: (+84) 914.78-08-98
>
> On Thu, Sep 29, 2016 at 8:37 PM, Adam Brusselback <
> adambrusselb...@gmail.com> wrote:
>
>> On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh <
>> ntquocv...@gmail.com> 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 changing events on all
>>> underlying tables.
>>>
>>> 2) Asynchronous (deferred) incremental update
>>> - Triggers are generated for all changing events on all underlying
>>> tables to collect all changed rows
>>> - Other codes are generated for each matview-query to update the matview.
>>>
>>>  We hope that our projects may be helpful for someone!
>>>
>>
>> Very interesting. Does this support materialized views with recursive
>> queries? What about left joins? (not) exists? Aggregates? Window functions?
>> In reading up on the implementations in other databases, I was surprised by
>> some of the limitations imposed by DB2 / Oracle / Sql Server.
>>
>> I'm trying to look through the code base to answer my questions, but it's
>> large enough that it may be easier to just ask first.
>>
>
>

[GENERAL] Сreate parallel aggregate

[Grigory Smolkin]

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 = geometry,
parallel = safe
);
ERROR:  syntax error at or near "parallel"
LINE 7: parallel = safe

But everything is ok if I use arg_data_type:

postgres=#  CREATE AGGREGATE ST_Extent_parallel(geometry) (
sfunc = ST_CombineBBox,
  combinefunc = ST_CombineBBox,
finalfunc = box2d,
stype = box3d,
parallel = safe
);
CREATE AGGREGATE

Is that a bug or a feature?


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

Re: [GENERAL] Сreate parallel aggregate

[Tom Lane]

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,
>  stype = box3d,
>  basetype = geometry,
>  parallel = safe
>  );
> ERROR:  syntax error at or near "parallel"
> LINE 7: parallel = safe

Old-style CREATE AGGREGATE syntax is only meant to be used with the
options that existed at the time it was deprecated.  It's unfortunate that
the error message is so obscure, but Bison doesn't give us a lot of wiggle
room to make it better, and frankly I don't especially care to put much
effort into that anyway.  (The actual problem is that old_aggr_elem
has to use IDENT to avoid reduce/reduce conflicts, so none of the option
names can be keywords at all, not even unreserved ones.)

If we do anything about this at all, what I'd be inclined to do is shove
the old-style syntax into a footnote at the bottom of the reference page,
similarly to the way the legacy syntaxes for COPY are documented.
And probably we should strip out all but the historical options from
the list that we claim works with it.

A more aggressive answer would be to drop the old-style CREATE AGGREGATE
syntax altogether ... but seeing that we're still supporting pre-7.3 COPY
syntax, probably that won't fly.

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] Large pg_xlog

[Francisco Reyes]

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:  could not open archive status directory 
"pg_xlog/archive_status": No such file or directory


Very puzzled on what could have deleted the folder. I am the only person 
that works on these machines and I would never do any work, much less 
deletions, inside pg_xlog.



Thanks for the help.


--
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] Сreate parallel aggregate

[Grigory Smolkin]

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 ST_Extent_parallel (
  sfunc = ST_CombineBBox,
  combinefunc = ST_CombineBBox,
  finalfunc = box2d,
  stype = box3d,
  basetype = geometry,
  parallel = safe
  );
ERROR:  syntax error at or near "parallel"
LINE 7: parallel = safe

Old-style CREATE AGGREGATE syntax is only meant to be used with the
options that existed at the time it was deprecated.  It's unfortunate that
the error message is so obscure, but Bison doesn't give us a lot of wiggle
room to make it better, and frankly I don't especially care to put much
effort into that anyway.  (The actual problem is that old_aggr_elem
has to use IDENT to avoid reduce/reduce conflicts, so none of the option
names can be keywords at all, not even unreserved ones.)

If we do anything about this at all, what I'd be inclined to do is shove
the old-style syntax into a footnote at the bottom of the reference page,
similarly to the way the legacy syntaxes for COPY are documented.
And probably we should strip out all but the historical options from
the list that we claim works with it.

A more aggressive answer would be to drop the old-style CREATE AGGREGATE
syntax altogether ... but seeing that we're still supporting pre-7.3 COPY
syntax, probably that won't fly.

regards, tom lane


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

[GENERAL] Time travel?

[Melvin Davidson]

*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
/d-log/pg_xlog/0001000D00C9-rw--- 1 postgres postgres
16777216 Sep 29 07:14 /d-log/pg_xlog/0001000D00C9but then I
list the access time for a WAL file and it shows: /home/mdavidson/dba$ ls
-l --time=atime /d-log/pg_xlog/0001000D00C9-rw--- 1
postgres postgres 16777216 Sep  7 14:33
/d-log/pg_xlog/0001000D00C9How is it possible for the WAL file
to be accessed BEFORE it was created?Am I overlooking something simple?*



*Melvin Davidson*

Re: [GENERAL] Time travel?

[Kevin Grittner]

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 database
unusable without warning...

> How is it possible for the WAL file to be accessed BEFORE it was
> created?

Perhaps renaming it counts as "creation" without affecting access
time.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Time travel?

[Paul Jungwirth]

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 stands for "changed" not "created". It is not quite the same as 
mtime ("modified") because mtime is updated only when the file contents 
change, but ctime is updated if either the file contents or file 
attributes change:


http://www.linux-faqs.info/general/difference-between-mtime-ctime-and-atime

I hope that explains it!

Paul


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

[GENERAL] Multi tenancy : schema vs databases

[Rakesh Kumar]

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 light weight from resources point of view.
2 - which is easier for backup/restore
3 - Which is better from security p.o.v

It is possible that this question has been asked before. Is there a  link t=
o earlier posts on the subject.

Thanks.

-- 
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] lost synchronization with server: got message type "Z"

[Marek Petr]

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 function of libpq returned version 90213 at database server 
where 9.2.13 is running version of PostgreSQL.

Could it be caused by older still installed release at database server (see 
below)?




# yum list installed postgres\*

Loaded plugins: product-id, rhnplugin, security, subscription-manager

This system is receiving updates from RHN Classic or RHN Satellite.

Installed Packages

postgresql.x86_64                                               8.4.20-6.el6
                                            @rhel-x86_64-server-6

postgresql-libs.x86_64                                          8.4.20-6.el6
                                             @rhel-x86_64-server-6

postgresql-server.x86_64                                        8.4.20-6.el6
                                             @rhel-x86_64-server-6

postgresql92.x86_64                                             9.2.13-1
PGDG.rhel6                                       @/postgresql92-9.2.13-1
PGDG.rhel6.x86_64

postgresql92-libs.x86_64                                        9.2.13-1
PGDG.rhel6                                       @/postgresql92-libs-9.2.13-
1PGDG.rhel6.x86_64

postgresql92-server.x86_64                                      9.2.13-1
PGDG.rhel6                                       @/postgresql92-server-9.2.
13-1PGDG.rhel6.x86_64




Regards

Marek






-- Původní zpráva --
Od: Tom Lane 
Komu: Marek Petr 
Datum: 27. 9. 2016 14:55:44
Předmět: Re: [GENERAL] lost synchronization with server: got message type 
"Z"

""Marek Petr"  writes:
> From time to time we receive following event from application (Adobe 
> Campaign - former Neolane):
> PostgreSQL error: lost synchronization with server: got message type "Z", 
> length 0\n (iRc=-2006)

Hm.

> Could something else than network cause this event?

We've fixed bugs with related symptoms in the past.

> postgres=# select version();
> PostgreSQL 9.2.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7

> 20120313 (Red Hat 4.4.7-11), 64-bit

What libpq version is being used on the client side? The most recent
related bugfix I can find in the 9.2 commit history was in libpq, and
it came out in 9.2.8.

regards, tom lane"

Re: [GENERAL] Time travel?

[Thomas Munro]

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 that 9.4.0 has known bugs
> (fixed in later minor releases) that can render the database
> unusable without warning...
>
>> How is it possible for the WAL file to be accessed BEFORE it was
>> created?
>
> Perhaps renaming it counts as "creation" without affecting access
> time.

Apparently some filesystems change the ctime for rename and others
don't, and POSIX tolerates both.

-- 
Thomas Munro
http://www.enterprisedb.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] Query generator

[Vinicius Segalin]

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?

[Melvin Davidson]

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 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 database
> > unusable without warning...
> >
> >> How is it possible for the WAL file to be accessed BEFORE it was
> >> created?
> >
> > Perhaps renaming it counts as "creation" without affecting access
> > time.
>
> Apparently some filesystems change the ctime for rename and others
> don't, and POSIX tolerates both.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

>Apparently some filesystems change the ctime for rename and others
>don't, and POSIX tolerates both.

OK, thanks. Back to the future! :D

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

Re: [GENERAL] Query generator

[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
> queries with join, group by, etc.
> 

You should try sqlsmith (https://github.com/anse1/sqlsmith), which works
very well.

-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org


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

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

[Thomas Kellerer]

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 in the --verbose output of pg_upgrade is that the old cluster - 
despite being a 9.5 one - has the "pg_control version number 942"

Here is the part of the pg_upgrade output:

.

copying "d:/Daten/db/pgdata95/base/16410/85351" to 
"d:/Daten/db/pgdata96/base/16411/85351"
  d:/Daten/db/pgdata95/base/16410/85351_fsm
copying "d:/Daten/db/pgdata95/base/16410/85351_fsm" to 
"d:/Daten/db/pgdata96/base/16411/85351_fsm"
  d:/Daten/db/pgdata95/base/16410/85351_vm
copying "d:/Daten/db/pgdata95/base/16410/85351_vm" to 
"d:/Daten/db/pgdata96/base/16411/85351_vm"
  d:/Daten/db/pgdata95/base/16410/85358
copying "d:/Daten/db/pgdata95/base/16410/85358" to 
"d:/Daten/db/pgdata96/base/16411/85358"
  d:/Daten/db/pgdata95/base/16410/85358.1
copying "d:/Daten/db/pgdata95/base/16410/85358.1" to 
"d:/Daten/db/pgdata96/base/16411/85358.1"
  d:/Daten/db/pgdata95/base/16410/85358.2
copying "d:/Daten/db/pgdata95/base/16410/85358.2" to 
"d:/Daten/db/pgdata96/base/16411/85358.2"
  d:/Daten/db/pgdata95/base/16410/85358.3
copying "d:/Daten/db/pgdata95/base/16410/85358.3" to 
"d:/Daten/db/pgdata96/base/16411/85358.3"
  d:/Daten/db/pgdata95/base/16410/85358_fsm
copying "d:/Daten/db/pgdata95/base/16410/85358_fsm" to 
"d:/Daten/db/pgdata96/base/16411/85358_fsm"
  d:/Daten/db/pgdata95/base/16410/85358_vm
copying "d:/Daten/db/pgdata95/base/16410/85358_vm" to 
"d:/Daten/db/pgdata96/base/16411/85358_vm"

error while copying relation "public.wb_downloads" 
("d:/Daten/db/pgdata95/base/16410/85358_vm" to 
"d:/Daten/db/pgdata96/base/16411/85358_vm"): Invalid argument
Failure, exiting

The file in question is 65.536 bytes in size.

I saved all log files and the complete output from the failed run, so if you 
are interested I can supply them (I ran pg_upgrade with the --retain option).

Regards
Thomas
 




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

[GENERAL] Parallel query only when EXPLAIN ANALYZEd

[Jay Knight]

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,
ceil(random()*10)::int AS low
  )
  select item, low, ceil(low + random()*100)::int as high from r
);
create index on t1(item, low, high);

create table t2 as (
  with r as (
SELECT generate_series(1,30) AS id, ceil(random()*25)::int as item,
ceil(random()*10)::int AS low
  )
  select item, low, ceil(low + random()*100)::int as high from r
);
create index on t2(item, low, high);

And this query:

explain analyze
select count(*)
  from t1
  join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >= t2.low;

If max_parallel_workers_per_gather is set to zero, this query take around
54 seconds on my machine (with and without the explain analyze).  But if I
set max_parallel_workers_per_gather=4, The query with explain analyze takes
25 seconds, and the query plan shows that it is using some workers to
parallelize the work.  But* when I run it with
max_parallel_workers_per_gather=4 without the explain analyze, it takes 54
seconds again*.  Watching my CPU graphs, it looks the same as it does with
max_parallel_workers_per_gather=0 (just one core spikes).  Query plan with
zero workers: https://explain.depesz.com/s/RUO, and with 4 workers:
https://explain.depesz.com/s/AB97.

So, why might postgres parallelize the query when I explain analyze it, but
not when I just run it by itself?

Thanks,
Jay K

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

[Tom Lane]

"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 up-to-date version of
libpq and see if that doesn't make things better.

Note that I never heard of "Adobe Campaign" before, and have no idea what
it would take to link it against a newer libpq.  You might have to get
Adobe involved, unless it goes through DBD::Pg.

regards, tom lane


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

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

[Adrian Klaver]

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 the upgrade goes
through.


Assuming you did that on old cluster?

Upgrading to 9.6?

Where both clusters installed the same way?



One thing I noticed in the --verbose output of pg_upgrade is that the
old cluster - despite being a 9.5 one - has the "pg_control version
number 942"


Which is correct:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_control.h;h=0b8bea74a891831bc3cbe8fd4d4233475a8329c4;hb=ba37ac217791dfdf2b327c4b75e7083b6b03a2f5


What was the complete command line invocation of pg_upgrade?



Here is the part of the pg_upgrade output:

.

copying "d:/Daten/db/pgdata95/base/16410/85351" to
"d:/Daten/db/pgdata96/base/16411/85351"
  d:/Daten/db/pgdata95/base/16410/85351_fsm
copying "d:/Daten/db/pgdata95/base/16410/85351_fsm" to
"d:/Daten/db/pgdata96/base/16411/85351_fsm"
  d:/Daten/db/pgdata95/base/16410/85351_vm
copying "d:/Daten/db/pgdata95/base/16410/85351_vm" to
"d:/Daten/db/pgdata96/base/16411/85351_vm"
  d:/Daten/db/pgdata95/base/16410/85358
copying "d:/Daten/db/pgdata95/base/16410/85358" to
"d:/Daten/db/pgdata96/base/16411/85358"
  d:/Daten/db/pgdata95/base/16410/85358.1
copying "d:/Daten/db/pgdata95/base/16410/85358.1" to
"d:/Daten/db/pgdata96/base/16411/85358.1"
  d:/Daten/db/pgdata95/base/16410/85358.2
copying "d:/Daten/db/pgdata95/base/16410/85358.2" to
"d:/Daten/db/pgdata96/base/16411/85358.2"
  d:/Daten/db/pgdata95/base/16410/85358.3
copying "d:/Daten/db/pgdata95/base/16410/85358.3" to
"d:/Daten/db/pgdata96/base/16411/85358.3"
  d:/Daten/db/pgdata95/base/16410/85358_fsm
copying "d:/Daten/db/pgdata95/base/16410/85358_fsm" to
"d:/Daten/db/pgdata96/base/16411/85358_fsm"
  d:/Daten/db/pgdata95/base/16410/85358_vm
copying "d:/Daten/db/pgdata95/base/16410/85358_vm" to
"d:/Daten/db/pgdata96/base/16411/85358_vm"

error while copying relation "public.wb_downloads"
("d:/Daten/db/pgdata95/base/16410/85358_vm" to
"d:/Daten/db/pgdata96/base/16411/85358_vm"): Invalid argument
Failure, exiting

The file in question is 65.536 bytes in size.

I saved all log files and the complete output from the failed run, so if
you are interested I can supply them (I ran pg_upgrade with the --retain
option).

Regards
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] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"

[Thomas Kellerer]

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 Archive from 
http://www.enterprisedb.com/products-services-training/pgbindownload then run 
initdb manually.

Both were initialized using:

   initdb -D "..."  --lc-messages=English -U postgres --pwfile=pwfile.txt -E 
UTF8 -A md5


What was the complete command line invocation of pg_upgrade?


That was in a batch file:

set LC_MESSAGES=English

set oldbin=c:\Programme\PostgreSQL\9.5\bin
set newbin=c:\Programme\PostgreSQL\9.6\bin
"%newbin%\pg_upgrade" ^
  --username=postgres ^
  --old-bindir="%oldbin%" ^
  --new-bindir="%newbin%" ^
  --old-datadir=d:/Daten/db/pgdata95 ^
  --new-datadir=d:/Daten/db/pgdata96 ^
  --retain ^
  --verbose ^
  --old-port=5432 ^
  --new-port=5433

 





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

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

[Tom Lane]

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 testing.  Its error
reporting is shamefully bad --- you can't tell which step failed, and
I wouldn't even put a lot of faith in the errno being meaningful,
considering that it does close() calls before capturing the errno.

But what gets my attention in this connection is that it doesn't
seem to be taking the trouble to open the files in binary mode.
Could that lead to the reported failure?  Not sure, but it seems
like at the least it could result in corrupted VM files.

Has anyone tested vismap upgrades on Windows, and made an effort
to validate that the output wasn't garbage?

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] Query generator

[Vinicius Segalin]

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
> > queries with join, group by, etc.
> >
>
> You should try sqlsmith (https://github.com/anse1/sqlsmith), which works
> very well.


I had found this one before, but all I could get was queries using
"standard" tables, like the ones from pg_catalog and information_schema. It
didn't generate queries from the tables I've created. Was I doing something
wrong?

Re: [GENERAL] Multi tenancy : schema vs databases

[Venkata B Nagothi]

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 per tenant)
> or
> 2 - One database per tenant.
>

Did you mean one database with-in a postgresql cluster ?


> The points to be considered are:
>
> 1 - which is more light weight from resources point of view.
> 2 - which is easier for backup/restore
> 3 - Which is better from security p.o.v
>

A schema per tenant would probably be a good idea to go with. 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.

One point to consider would be that, how different could be the
backup/restore, security or any other database policies for different
tenants.


Regards,

Venkata B N
Database Consultant / Architect

Re: [GENERAL] Parallel query only when EXPLAIN ANALYZEd

[David Rowley]

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 just happened to be
when you did the EXPLAIN ANALYZE. If the executor can't get a free
worker process, then it'll just do all the work in the main process.
The plan parallel plan that you've shown, given no extra worker
processes, would most likely perform the same as the serial plan you
showed, since the extra Finalize Aggregate node is only handling 1 row
anyway.

What's max_worker_processes set to?

If this is just a test machine, you should be able to see what's going
on if you install auto_explain, and enable auto_explain.log_analyze
(https://www.postgresql.org/docs/current/static/auto-explain.html)
Setting this up will log the EXPLAIN ANALYZE to the PostgreSQL logs
when you execute the query as normal.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

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

[Alvaro Herrera]

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 hasn't really gotten that much testing.  Its error
> reporting is shamefully bad --- you can't tell which step failed, and
> I wouldn't even put a lot of faith in the errno being meaningful,
> considering that it does close() calls before capturing the errno.

So we do close() in a bunch of places while closing shop, which calls
_close() on Windows; this function sets errno.  Then we call
getErrorText(), which calls _dosmaperr() on the result of
GetLastError().  But the last-error stuff is not set by _close; I suppose
GetLastError() returns 0 in that case, which promps _doserrmap to set errno to 
0.
http://stackoverflow.com/questions/20056851/getlasterror-errno-formatmessagea-and-strerror-s
So this wouldn't quite have the effect you say; I think it'd say
"Failure while copying ...: Success" instead.

However surely we should have errno save/restore.

Other than that, I think the _dosmaperr() call should go entirely.
Moreover I think getErrorText() as a whole is misconceived and should be
removed altogether (why pstrdup the string?).  There are very few places
in pg_upgrade that require _dosmaperr; I can see only copyFile and
linkFile.  All the others should just be doing strerror() only, at least
according to the manual.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] executing os commands from a function

[Armand Pirvu (home)]

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 functions and a trigger, see code 
below

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
FOR EACH ROW EXECUTE PROCEDURE func1();

testdb=# insert into test3 values (777);
INSERT 0 1
testdb=# select * from test3;
 col1 
--
  777

[postgres@edb1 ~]$ ls -ltr
-rw--- 1 postgres postgres 0 Sep 29 16:30 777


It works but can I be simpler ? Any other alternatives ? In Ingres for example 
I can use dbevent and an esqlc app which listens 


Thank you 

Armand



-- 
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] Parallel query only when EXPLAIN ANALYZEd

[Jay Knight]

>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 parallel workers, and every time I run it without it
doesn't.  I just enabled auto_explain, and see that it is "planning" to use
a worker, but doesn't launch it.  Is there a way to know why a planned
worker doesn't get launched?

2016-09-29 16:45:44 CDT LOG:  duration: 50703.595 ms  plan:
Query Text: select count(*) from t1
join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >= t2.low
Finalize Aggregate  (cost=14609058.99..14609059.00 rows=1 width=8)
(actual time=50703.584..50703.584 rows=1 loops=1)
  ->  Gather  (cost=14609058.88..14609058.99 rows=1 width=8) (actual
time=50703.581..50703.581 rows=1 loops=1)
Workers Planned: 1
Workers Launched: 0
->  Partial Aggregate  (cost=14608958.88..14608958.89 rows=1
width=8) (actual time=50703.579..50703.579 rows=1 loops=1)
  ->  Nested Loop  (cost=0.42..13608937.28 rows=48641
width=0) (actual time=0.534..50577.673 rows=3669891 loops=1)
->  Parallel Seq Scan on t1  (cost=0.00..3386.71
rows=176471 width=12) (actual time=0.041..18.351 rows=30 loops=1)
->  Index Only Scan using t2_item_low_high_idx on
t2  (cost=0.42..63.77 rows=1333 width=12) (actual time=0.167..0.168 rows=12
loops=30)
  Index Cond: ((item = t1.item) AND (low <=
t1.high) AND (high >= t1.low))
  Heap Fetches: 0


Thanks,
Jay K

On Thu, Sep 29, 2016 at 4:29 PM David Rowley 
wrote:

> 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 just happened to be
> when you did the EXPLAIN ANALYZE. If the executor can't get a free
> worker process, then it'll just do all the work in the main process.
> The plan parallel plan that you've shown, given no extra worker
> processes, would most likely perform the same as the serial plan you
> showed, since the extra Finalize Aggregate node is only handling 1 row
> anyway.
>
> What's max_worker_processes set to?
>
> If this is just a test machine, you should be able to see what's going
> on if you install auto_explain, and enable auto_explain.log_analyze
> (https://www.postgresql.org/docs/current/static/auto-explain.html)
> Setting this up will log the EXPLAIN ANALYZE to the PostgreSQL logs
> when you execute the query as normal.
>
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

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

[Tom Lane]

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 do pg_fatal
anyway.  We should rewrite these functions to just error out internally,
which will make it much easier to provide decent error reporting
indicating which call failed.

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] Filtering by UUID

[Colin Morelli]

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 believe?)

Is there an efficient way to filter based on the time component of UUID
v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's
just performing a lexicographic sort on the hex representation of the UUID.
Or, alternatively, does anyone have other suggestions on paginating large
data sets?

Best,
Colin

Re: [GENERAL] Filtering by UUID

[Guyren Howe]

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 records to be created at the same 
> timestamp (postgres offers millisecond precision here, I believe?)
> 
> Is there an efficient way to filter based on the time component of UUID v1s? 
> WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's just 
> performing a lexicographic sort on the hex representation of the UUID. Or, 
> alternatively, does anyone have other suggestions on paginating large data 
> sets?

Why not just sort on (created_at, uuid) (ie us the UUID just to force a 
complete ordering)?



-- 
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] Filtering by UUID

[Colin Morelli]

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 virtually impossible to answer without more
information, but based on your gut - do you think it would make sense to
define a compound index across (created_at, uuid), or do you think just an
index on created_at is enough, if we can assume that *most* records (>80%)
won't have collisions on their created_at timestamp?

Thanks!
Colin

On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe  wrote:

> 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 records to be created at the
> same timestamp (postgres offers millisecond precision here, I believe?)
> >
> > Is there an efficient way to filter based on the time component of UUID
> v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's
> just performing a lexicographic sort on the hex representation of the UUID.
> Or, alternatively, does anyone have other suggestions on paginating large
> data sets?
>
> Why not just sort on (created_at, uuid) (ie us the UUID just to force a
> complete ordering)?
>
>

Re: [GENERAL] Filtering by UUID

[Guyren Howe]

> 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 
> "marker" would be a combination of created_at and uuid).
> 
> I know this question is virtually impossible to answer without more 
> information, but based on your gut - do you think it would make sense to 
> define a compound index across (created_at, uuid), or do you think just an 
> index on created_at is enough, if we can assume that most records (>80%) 
> won't have collisions on their created_at timestamp?
> 

Quite likely just the created_at will be sufficient, although I like to add 
other fields to the index since you're going to have it anyway, if you have 
occasion to often filter the list on some other field.

But you don't need the UUID field because even if there is collision, it will 
be a small number of records.

> On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe  > wrote:
> 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 records to be created at the 
> > same timestamp (postgres offers millisecond precision here, I believe?)
> >
> > Is there an efficient way to filter based on the time component of UUID 
> > v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's 
> > just performing a lexicographic sort on the hex representation of the UUID. 
> > Or, alternatively, does anyone have other suggestions on paginating large 
> > data sets?
> 
> Why not just sort on (created_at, uuid) (ie us the UUID just to force a 
> complete ordering)?
> 

Re: [GENERAL] Filtering by UUID

[Cachique]

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"  wrote:

>
> 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
> "marker" would be a combination of created_at and uuid).
>
> I know this question is virtually impossible to answer without more
> information, but based on your gut - do you think it would make sense to
> define a compound index across (created_at, uuid), or do you think just an
> index on created_at is enough, if we can assume that *most* records
> (>80%) won't have collisions on their created_at timestamp?
>
>
> Quite likely just the created_at will be sufficient, although I like to
> add other fields to the index since you're going to have it anyway, if you
> have occasion to often filter the list on some other field.
>
> But you don't need the UUID field because even if there is collision, it
> will be a small number of records.
>
> On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe  wrote:
>
>> 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 records to be
>> created at the same timestamp (postgres offers millisecond precision here,
>> I believe?)
>> >
>> > Is there an efficient way to filter based on the time component of UUID
>> v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's
>> just performing a lexicographic sort on the hex representation of the UUID.
>> Or, alternatively, does anyone have other suggestions on paginating large
>> data sets?
>>
>> Why not just sort on (created_at, uuid) (ie us the UUID just to force a
>> complete ordering)?
>>
>>
>

Re: [GENERAL] isnull() function in pgAdmin3

[dudedoe01]

 

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

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

[Hanne Moa]

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 returning anything useful since it
tracks which table *owns* a sequence and not which sequence is used by
which table.column.

The necessary information seems to be in the table
"information_schema.columns", in  "column_default". Is this to be
regarded as internal API or is it safe to use this to find the correct
sequence? It works in all cases and on all the version of postgres
that are relevant to us. The production system is currently running
9.3 (I'm pining for 9.5...)

Furthermore, what's stored in the column seems to be a string of the
format "nextval('sequencename'::regclass)". Is there a function to
parse this, to return just the sequence name, or will the sequence
name always be without for instance a schema name so that a naive
parser of our own will do? Googling found no candidates.


HM


-- 
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] Multi tenancy : schema vs databases

[Rakesh Kumar]

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 
mailto:rakeshkumar...@outlook.com>> 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 per tenant)
or
2 - One database per tenant.

Did you mean one database with-in a postgresql cluster ?

Yes.  Say something like this within a PG cluster

db4978
db6234
...
100s of such databases.


-- 
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] isnull() function in pgAdmin3

[Adrian Klaver]

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 issue is as the error says, there 
is no isnull() function in Postgres:


test=# select version();
   version 


-
 PostgreSQL 9.5.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit

(1 row)


test=# select isnull();
ERROR:  function isnull() does not exist
LINE 1: select isnull();
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.








--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923277.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] Multi tenancy : schema vs databases

[John R Pierce]

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 connection pooling a nightmare.



depending on the nature of the application, its data isolation 
requirements, and how much per-tenant customization there is, assuming 
the customers('tenants') aren't directly accessing SQL, I could see many 
scenarios with ONE database+schema, and 'tenant' is just a field that 
qualifies queries.   From a pure performance standpoint, this likely 
woudl be the most efficient, as 1000s of schemas with 100s of tables 
each == 100s of 1000s of tables, which means massive bloat of the 
postgres catalog, and also makes caching less effective.




--
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] Parallel query only when EXPLAIN ANALYZEd

[David Rowley]

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 happens consistently this way.  Every time I run it with explain
> analyze it uses parallel workers, and every time I run it without it
> doesn't.  I just enabled auto_explain, and see that it is "planning" to use
> a worker, but doesn't launch it.  Is there a way to know why a planned
> worker doesn't get launched?

It seems not, at least not unless you're willing to attach a debugger.

> 2016-09-29 16:45:44 CDT LOG:  duration: 50703.595 ms  plan:
> Query Text: select count(*) from t1
> join t2 on t1.item = t2.item and t1.low <= t2.high and t1.high >= t2.low
> Finalize Aggregate  (cost=14609058.99..14609059.00 rows=1 width=8)
> (actual time=50703.584..50703.584 rows=1 loops=1)
>   ->  Gather  (cost=14609058.88..14609058.99 rows=1 width=8) (actual
> time=50703.581..50703.581 rows=1 loops=1)
> Workers Planned: 1
> Workers Launched: 0
> ->  Partial Aggregate  (cost=14608958.88..14608958.89 rows=1
> width=8) (actual time=50703.579..50703.579 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.42..13608937.28 rows=48641
> width=0) (actual time=0.534..50577.673 rows=3669891 loops=1)
> ->  Parallel Seq Scan on t1  (cost=0.00..3386.71
> rows=176471 width=12) (actual time=0.041..18.351 rows=30 loops=1)
> ->  Index Only Scan using t2_item_low_high_idx on t2
> (cost=0.42..63.77 rows=1333 width=12) (actual time=0.167..0.168 rows=12
> loops=30)
>   Index Cond: ((item = t1.item) AND (low <=
> t1.high) AND (high >= t1.low))
>   Heap Fetches: 0

That's interesting.

How about trying to increase max_worker_processes to say, 16. I know
you've said you've tried multiple times and it seems consistent, but
increasing this seems to be worth a try, if anything, to rule that
out.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Multi tenancy : schema vs databases

[Venkata B Nagothi]

On Fri, Sep 30, 2016 at 10:16 AM, Rakesh Kumar 
wrote:

>
>
> 
> 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  mailto:rakeshkumar...@outlook.com>> 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 per tenant)
> or
> 2 - One database per tenant.
>
> Did you mean one database with-in a postgresql cluster ?
>
> Yes.  Say something like this within a PG cluster
>
> db4978
> db6234
> ...
> 100s of such databases.
>

That would make things worst if you are going for one database per tenant.
As said by John just now, it would end up in an very complex and bad design
contributing to very poor performance and high maintenance overhead.
A schema per tenant would be a good idea and its hard to say without
knowing the data isolation levels you require for each tenant.

Regards,
Venkata B N

Database consultant / Architect