[GENERAL] PostGIS Raster - Loading MrSID format

[Osahon Oduware]

Hi,

I am trying to use the raster2pgsql tool to import raster images in MrSID
format to PostGIS database on a Windows system. However, I get an error
indicating that the format is not supported which I could confirm by
running:
raster2pgsql -G

Please, could someone help with the basic steps to follow to load raster
data in MrSID format using the raster2pgsql tool.

[GENERAL] PostGIS Out-DB Raster Not Behaving As Expected

[Osahon Oduware]

Hi All,

I created an out-db raster using the following syntax:

raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
public.{table} | psql -h {host} -p {port} -d {database} -U {user}

The table was created successfully. I wanted to confirm that the actual
file is being read from the location in the filesystem by performing the
following steps:
1) I moved the raster file to a different location.
2) I opened QGIS and attempted to load the raster from PostGIS table.

I was surprised that QGIS could load the file. *How is this possible when
the actual raster data is not stored in the database table?*

Re: [GENERAL] Unable to connect to Postgresql

[Adrian Klaver]

On 04/10/2017 01:23 PM, John Iliffe wrote:

On Monday 10 April 2017 11:53:35 Daniel Verite wrote:

John Iliffe wrote:

So, the problem is resolved, although I have no idea why it was
necessary.


The key seems to be the PrivateTmp=true in the systemd service.
Apache is not chrooted as demonstrated upthread, but that
setting alone makes the normal, system-wide /tmp inaccessible
to its processes, including the postgres Unix domain socket opened
there.

I suspect that your attempt to set PrivateTmp=false as a test was
missed by systemd  for some reason. You could probably insist
in that direction and eventually make it work, but I don't see
how it's better than the alternative /var/pgsql or localhost through
TCP.


Also, I now have several hundred programmes to update to add the host
path and none of them will now be portable.


Given that you set two directories: /tmp and /var/pgsql,
I would think you can let the other apps use /tmp as before
and have only Apache use /var/pgsql ?


Yes, I will do that, but there are several hundred PHP web page scripts to
be updated.  Presumably if one script opens two different databases then
both of the pg_connect() instances will need to be updated.



Seems I missed a message:

" I just installed everything (APache, Postgresql, PHP, OpenSSL from 
source as I always have over a period of more than 15 years."








Best regards,





--
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] Unable to connect to Postgresql

[Adrian Klaver]

On 04/10/2017 01:23 PM, John Iliffe wrote:

On Monday 10 April 2017 11:53:35 Daniel Verite wrote:

John Iliffe wrote:

So, the problem is resolved, although I have no idea why it was
necessary.


The key seems to be the PrivateTmp=true in the systemd service.
Apache is not chrooted as demonstrated upthread, but that
setting alone makes the normal, system-wide /tmp inaccessible
to its processes, including the postgres Unix domain socket opened
there.

I suspect that your attempt to set PrivateTmp=false as a test was
missed by systemd  for some reason. You could probably insist
in that direction and eventually make it work, but I don't see
how it's better than the alternative /var/pgsql or localhost through
TCP.


Also, I now have several hundred programmes to update to add the host
path and none of them will now be portable.


Given that you set two directories: /tmp and /var/pgsql,
I would think you can let the other apps use /tmp as before
and have only Apache use /var/pgsql ?


Yes, I will do that, but there are several hundred PHP web page scripts to
be updated.  Presumably if one script opens two different databases then
both of the pg_connect() instances will need to be updated.


Out of curiosity where did you install Postgres from?

The reason I ask it that I was trying to figure why Fedora would invoke 
private /tmp's for services and then have the Postgres socket be only in 
the public /tmp. So I spun up a Fedora 25 instance and did an install of 
Postgres from the Fedora repos and then from the PGDG repos. In either 
case I got a postgresql.conf that had:


unix_socket_directories = '/var/run/postgresql', '/tmp'

Upstream you showed your default as:

unix_socket_directories = '/tmp'

which is what I see when I do a source install.

So did you do a source install or did you copy a postgresql.conf from 
somewhere else?







Best regards,





--
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] Unable to connect to Postgresql

[John Iliffe]

Thanks for the idea Rick.  It hadn't occurred to me; might have at about 
the 200th page :-(

John
=
On Monday 10 April 2017 16:46:14 Rick Widmer wrote:
> On 4/10/2017 2:23 PM, John Iliffe wrote:
> > On Monday 10 April 2017 11:53:35 Daniel Verite wrote:
> >>John Iliffe wrote:
> >> Given that you set two directories: /tmp and /var/pgsql,
> >> I would think you can let the other apps use /tmp as before
> >> and have only Apache use /var/pgsql ?
> > 
> > Yes, I will do that, but there are several hundred PHP web page
> > scripts to be updated.  Presumably if one script opens two different
> > databases then both of the pg_connect() instances will need to be
> > updated.
> 
> How about creating a php file with the connect code, once, then
> including that file from all of the hundreds of web pages.  It won't
> help much with the first set of changes, but it will help with
> portability later.
> 
> 
> Rick


-- 
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] Unable to connect to Postgresql

[rob stone]

Hello John,

On Mon, 2017-04-10 at 16:23 -0400, John Iliffe wrote:
> On Monday 10 April 2017 11:53:35 Daniel Verite wrote:
> >   John Iliffe wrote:
> > > So, the problem is resolved, although I have no idea why it was
> > > necessary.
> > 
> 

Good to hear.

> > 
> 
> Yes, I will do that, but there are several hundred PHP web page
> scripts to 
> be updated.  Presumably if one script opens two different databases
> then 
> both of the pg_connect() instances will need to be updated.
> 

Are you saying that every single PHP script has its own separate
routine to connect to the database?

We have just a single class to do connection stuff and the parameters
are held in pg_service.conf. That file has to be readable by Apache.
You just pass in an argument that represents the database to which you
want to connect, and it grabs host, port, etc. out of the service file.
All you have to do is keep the service file up-to-date.

Cheers,
Rob


-- 
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] Unable to connect to Postgresql

[Daniel Verite]

John Iliffe wrote:

> Yes, I will do that, but there are several hundred PHP web page scripts to 
> be updated.  Presumably if one script opens two different databases then 
> both of the pg_connect() instances will need to be updated.

If you have many calls to pg_connect() without a host
in the connect string, meaning it defaults to /tmp, instead of
changing them you may inject a PGHOST variable into the Apache
environment with /var/pgsql as the value. This will make it
the default host, so basically everything that was going
implicitly to /tmp will go to /var/pgsql instead.
I think in RedHat the definition should go into /etc/sysconfig/httpd

Generally the environment variables described for libpq here:
https://www.postgresql.org/docs/9.6/static/libpq-envars.html
apply to php's pg_connect() as well, since it's really just
a thin wrapper on top of libpq's PQconnectdb().

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Unable to connect to Postgresql

[Adrian Klaver]

On 04/10/2017 01:23 PM, John Iliffe wrote:

On Monday 10 April 2017 11:53:35 Daniel Verite wrote:

John Iliffe wrote:

So, the problem is resolved, although I have no idea why it was
necessary.


The key seems to be the PrivateTmp=true in the systemd service.
Apache is not chrooted as demonstrated upthread, but that
setting alone makes the normal, system-wide /tmp inaccessible
to its processes, including the postgres Unix domain socket opened
there.

I suspect that your attempt to set PrivateTmp=false as a test was
missed by systemd  for some reason. You could probably insist
in that direction and eventually make it work, but I don't see
how it's better than the alternative /var/pgsql or localhost through
TCP.


Also, I now have several hundred programmes to update to add the host
path and none of them will now be portable.


Given that you set two directories: /tmp and /var/pgsql,
I would think you can let the other apps use /tmp as before
and have only Apache use /var/pgsql ?


Yes, I will do that, but there are several hundred PHP web page scripts to
be updated.  Presumably if one script opens two different databases then
both of the pg_connect() instances will need to be updated.


Seems to me that it would be easier to try:

PrivateTmp=false

in the httpd systemd file.







Best regards,





--
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] Unable to connect to Postgresql

[Rick Widmer]

On 4/10/2017 2:23 PM, John Iliffe wrote:

On Monday 10 April 2017 11:53:35 Daniel Verite wrote:

John Iliffe wrote:



Given that you set two directories: /tmp and /var/pgsql,
I would think you can let the other apps use /tmp as before
and have only Apache use /var/pgsql ?


Yes, I will do that, but there are several hundred PHP web page scripts to
be updated.  Presumably if one script opens two different databases then
both of the pg_connect() instances will need to be updated.


How about creating a php file with the connect code, once, then 
including that file from all of the hundreds of web pages.  It won't 
help much with the first set of changes, but it will help with 
portability later.



Rick



--
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] Unable to connect to Postgresql

[John Iliffe]

On Monday 10 April 2017 11:53:35 Daniel Verite wrote:
>   John Iliffe wrote:
> > So, the problem is resolved, although I have no idea why it was
> > necessary.
> 
> The key seems to be the PrivateTmp=true in the systemd service.
> Apache is not chrooted as demonstrated upthread, but that
> setting alone makes the normal, system-wide /tmp inaccessible
> to its processes, including the postgres Unix domain socket opened
> there.
> 
> I suspect that your attempt to set PrivateTmp=false as a test was
> missed by systemd  for some reason. You could probably insist
> in that direction and eventually make it work, but I don't see
> how it's better than the alternative /var/pgsql or localhost through
> TCP.
> 
> > Also, I now have several hundred programmes to update to add the host
> > path and none of them will now be portable.
> 
> Given that you set two directories: /tmp and /var/pgsql,
> I would think you can let the other apps use /tmp as before
> and have only Apache use /var/pgsql ?

Yes, I will do that, but there are several hundred PHP web page scripts to 
be updated.  Presumably if one script opens two different databases then 
both of the pg_connect() instances will need to be updated.

> 
> 
> Best regards,


-- 
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] Are multiple array_aggs going to be in the same order?

[Tom Lane]

Paul Jungwirth  writes:
> On 04/10/2017 11:35 AM, Tom Lane wrote:
>> I'm not very keen on recommending that the OP insert an ORDER BY into
>> each aggregate call, because that would cause a separate sort for each
>> aggregate (unless someone's improved that recently while I wasn't
>> looking).

> I mentioned this in my other email, upon a second look I was misreading 
> the EXPLAIN output. (The sort was for the GROUP BY, not the individual 
> ORDER BYs.) Do aggregate function sorts even show up in EXPLAIN?

They don't.  It's not a plannable behavior, just something that's
hard-wired into nodeAgg.c.  I'd like to see that improved someday,
but I don't think it's really high on anyone's priority list.

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] Are multiple array_aggs going to be in the same order?

[Merlin Moncure]

On Mon, Apr 10, 2017 at 1:43 PM, Merlin Moncure  wrote:
> On Mon, Apr 10, 2017 at 1:35 PM, Tom Lane  wrote:
>>
>> Yes, and in fact we documented the ORDER-BY-in-subselect solution back
>> before we had the ORDER-BY-in-aggregate feature.  I don't remember exactly
>> where, but I'm sure it's still described somewhere.  So it is documented
>> behavior that an aggregate without its own ORDER BY will see the rows in
>> whatever order the FROM clause supplies them.
>
> The documentation is a bit ambiguous on the topic TBH.Via
> https://www.postgresql.org/docs/9.6/static/functions-aggregate.html:
>
> "The aggregate functions array_agg, json_agg, jsonb_agg,
> json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as
> similar user-defined aggregate functions, produce meaningfully
> different result values depending on the order of the input values.
> This ordering is unspecified by default, but can be controlled by
> writing an ORDER BY clause within the aggregate call, as shown in
> Section 4.2.7. Alternatively, supplying the input values from a sorted
> subquery will usually work.


What do you think about injecting the following lingo?

... depending on the order of the input values. This ordering is
unspecified by default, but can be controlled by ...

would be changed to

... depending on the order of the input values. Input value ordering
will be consistent across multiple order dependent aggregate functions
across the same grouping but is otherwise unspecified by default as
long as all there is no explicit ordering for any aggregate function
in the grouping.  Furthermore, input value ordering can be controlled
by ...

merlin


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

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

[Paul Jungwirth]

On 04/10/2017 11:35 AM, Tom Lane wrote:

I'm not very keen on recommending that the OP insert an ORDER BY into
each aggregate call, because that would cause a separate sort for each
aggregate (unless someone's improved that recently while I wasn't
looking).


I mentioned this in my other email, upon a second look I was misreading 
the EXPLAIN output. (The sort was for the GROUP BY, not the individual 
ORDER BYs.) Do aggregate function sorts even show up in EXPLAIN? I can't 
seem to find any. For example in this try the sorts are just for 
grouping and joining:


db=> explain select c.id, array_agg(e.item order by e.id), 
array_agg(e.payee order by e.id) from expense_categories c join expenses 
e on e.expense_category_id = c.id group by c.id;
  QUERY PLAN 


---
 GroupAggregate  (cost=223.44..285.14 rows=16 width=30)
   ->  Merge Join  (cost=223.44..264.44 rows=2728 width=30)
 Merge Cond: (c.id = e.expense_category_id)
 ->  Sort  (cost=1.48..1.52 rows=16 width=4)
   Sort Key: c.id
   ->  Seq Scan on expense_categories c  (cost=0.00..1.16 
rows=16 width=4)

 ->  Sort  (cost=221.96..228.78 rows=2728 width=30)
   Sort Key: e.expense_category_id
   ->  Seq Scan on expenses e  (cost=0.00..66.28 rows=2728 
width=30)

(9 rows)

Paul



--
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] Are multiple array_aggs going to be in the same order?

[Merlin Moncure]

On Mon, Apr 10, 2017 at 1:35 PM, Tom Lane  wrote:
> "David G. Johnston"  writes:
>> On Mon, Apr 10, 2017 at 11:02 AM, Merlin Moncure  wrote:
>>> Sure, but isn't it fair to consider that an implementation artifact?
>
>> So, the presence of ORDER BY in the aggregate function call is a PostgreSQL
>> extension...
>
>> It seems reasonable to declare that the order of the values in the
>> generated array match whatever order the FROM clause supplies the rows.  If
>> that is not acceptable a PostgreSQL-specific ORDER BY modifier can be added
>> which will cause an additional sort-and-scan of the input relation to occur
>> (optimized across multiple column invocations when possible).
>
> Yes, and in fact we documented the ORDER-BY-in-subselect solution back
> before we had the ORDER-BY-in-aggregate feature.  I don't remember exactly
> where, but I'm sure it's still described somewhere.  So it is documented
> behavior that an aggregate without its own ORDER BY will see the rows in
> whatever order the FROM clause supplies them.

The documentation is a bit ambiguous on the topic TBH.Via
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html:

"The aggregate functions array_agg, json_agg, jsonb_agg,
json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as
similar user-defined aggregate functions, produce meaningfully
different result values depending on the order of the input values.
This ordering is unspecified by default, but can be controlled by
writing an ORDER BY clause within the aggregate call, as shown in
Section 4.2.7. Alternatively, supplying the input values from a sorted
subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Beware that this approach can fail if the outer query level contains
additional processing, such as a join, because that might cause the
subquery's output to be reordered before the aggregate is computed."

If you think the behavior ought to be defined to work that way, that's
fine by me.   A small documentation fix could clarify that, I think.

merlin


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

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Apr 10, 2017 at 11:02 AM, Merlin Moncure  wrote:
>> Sure, but isn't it fair to consider that an implementation artifact?

> So, the presence of ORDER BY in the aggregate function call is a PostgreSQL
> extension...

> It seems reasonable to declare that the order of the values in the
> generated array match whatever order the FROM clause supplies the rows.  If
> that is not acceptable a PostgreSQL-specific ORDER BY modifier can be added
> which will cause an additional sort-and-scan of the input relation to occur
> (optimized across multiple column invocations when possible).

Yes, and in fact we documented the ORDER-BY-in-subselect solution back
before we had the ORDER-BY-in-aggregate feature.  I don't remember exactly
where, but I'm sure it's still described somewhere.  So it is documented
behavior that an aggregate without its own ORDER BY will see the rows in
whatever order the FROM clause supplies them.

I'm not very keen on recommending that the OP insert an ORDER BY into
each aggregate call, because that would cause a separate sort for each
aggregate (unless someone's improved that recently while I wasn't
looking).

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] Are multiple array_aggs going to be in the same order?

[David G. Johnston]

On Mon, Apr 10, 2017 at 11:02 AM, Merlin Moncure  wrote:

> On Mon, Apr 10, 2017 at 12:01 PM, Tom Lane  wrote:
> > Merlin Moncure  writes:
> >> On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe  wrote:
> >>> If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the
> ARRAY_AGGs be guaranteed to have entries in the same (ie corresponding)
> order?
> >>>
> >>> eg
> >>>
> >>> SELECT
> >>> u.name,
> >>> ARRAY_AGG(o.order_date) AS order_dates,
> >>> ARRAY_AGG(o.order_total) AS order_totals
> >>> FROM
> >>> user u JOIN
> >>> orders o USING (user_id)
> >>> GROUP BY
> >>> u.user_id
> >
> >> It is unsafe to rely on aggregation order unless specified -- you can
> >> add ORDER BY to the aggregation clause.
> >
> > You definitely can't assume anything about the order in which the FROM
> > clause will deliver rows, but I think that's not quite what the question
> > was.  If I read it right, the OP wants to be sure that the two aggregate
> > functions will see the data in the *same* unspecified order.  I think
> > that's a pretty safe assumption.  The server would have to go way
> > out of its way to do differently, and it doesn't.
>
> Sure, but isn't it fair to consider that an implementation artifact?
> If his code depends on that ordering being the same across aggregate
> functions, and the SQL standard doesn't specify that (I guess it
> might, but I'm skeptical), he ought to specify that for clarify at the
> very least.
>

So, the presence of ORDER BY in the aggregate function call is a PostgreSQL
extension...

It seems reasonable to declare that the order of the values in the
generated array match whatever order the FROM clause supplies the rows.  If
that is not acceptable a PostgreSQL-specific ORDER BY modifier can be added
which will cause an additional sort-and-scan of the input relation to occur
(optimized across multiple column invocations when possible).  Thus two
aggregate functions w/o an ORDER BY will always see the source rows in the
same order.

SELECT array_agg(v)
FROM (
SELECT * FROM (VALUES (3),(1),(2)) vals (v) ORDER BY 1
) AS src

The only real confusion is whether a query like the above is guaranteed to
supply rows to the outer select target list in order.  If that is indeed
the case then the overall behavior seems quite reasonable to explicitly
define like above.

Dave

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

[Merlin Moncure]

On Mon, Apr 10, 2017 at 12:01 PM, Tom Lane  wrote:
> Merlin Moncure  writes:
>> On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe  wrote:
>>> If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the 
>>> ARRAY_AGGs be guaranteed to have entries in the same (ie corresponding) 
>>> order?
>>>
>>> eg
>>>
>>> SELECT
>>> u.name,
>>> ARRAY_AGG(o.order_date) AS order_dates,
>>> ARRAY_AGG(o.order_total) AS order_totals
>>> FROM
>>> user u JOIN
>>> orders o USING (user_id)
>>> GROUP BY
>>> u.user_id
>
>> It is unsafe to rely on aggregation order unless specified -- you can
>> add ORDER BY to the aggregation clause.
>
> You definitely can't assume anything about the order in which the FROM
> clause will deliver rows, but I think that's not quite what the question
> was.  If I read it right, the OP wants to be sure that the two aggregate
> functions will see the data in the *same* unspecified order.  I think
> that's a pretty safe assumption.  The server would have to go way
> out of its way to do differently, and it doesn't.

Sure, but isn't it fair to consider that an implementation artifact?
If his code depends on that ordering being the same across aggregate
functions, and the SQL standard doesn't specify that (I guess it
might, but I'm skeptical), he ought to specify that for clarify at the
very least.

merlin


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

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

[Paul Jungwirth]

On 04/10/2017 09:33 AM, Merlin Moncure wrote:

On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe  wrote:

If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs,

>> will the ARRAY_AGGs be guaranteed to have entries in the
>> same (ie corresponding) order?

It is unsafe to rely on aggregation order unless specified --

> you can add ORDER BY to the aggregation clause.

Just thought I'd add that if you do this:

SELECT  u.name,
ARRAY_AGG(o.order_date ORDER BY o.id) AS order_dates,
ARRAY_AGG(o.order_total ORDER BY o.id) AS order_totals
...

Then you can check EXPLAIN and should see that Postgres is only doing 
one sort, not two, so there is no performance cost. (Of course for more 
complicated queries you might want to double check what EXPLAIN intends 
to do.)


But something I've wondered myself is how well Postgres handles sorting 
already-mostly-sorted lists? I tried diving into the code, starting from 
here:


https://doxygen.postgresql.org/tuplesort_8c_source.html

but I couldn't answer the question myself. The comments say that as long 
as the tuples all fit in work_mem, it uses qsort, but the code appears 
to call qsort_ssup or qsort_tuple, whose definitions I couldn't find. (I 
looks like they are from qsort_tuple.c which is generated by a Perl 
script.) I know that qsort(3) is not necessarily quicksort, despite the 
name. Does anyone know what algorithm Postgres uses? It seems like the 
mostly-already-sorted case would happen a lot, so I'm curious if 
Postgres pays a performance cost there?


Thanks,
Paul



--
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] Are multiple array_aggs going to be in the same order?

[Tom Lane]

Merlin Moncure  writes:
> On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe  wrote:
>> If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the 
>> ARRAY_AGGs be guaranteed to have entries in the same (ie corresponding) 
>> order?
>> 
>> eg
>> 
>> SELECT
>> u.name,
>> ARRAY_AGG(o.order_date) AS order_dates,
>> ARRAY_AGG(o.order_total) AS order_totals
>> FROM
>> user u JOIN
>> orders o USING (user_id)
>> GROUP BY
>> u.user_id

> It is unsafe to rely on aggregation order unless specified -- you can
> add ORDER BY to the aggregation clause.

You definitely can't assume anything about the order in which the FROM
clause will deliver rows, but I think that's not quite what the question
was.  If I read it right, the OP wants to be sure that the two aggregate
functions will see the data in the *same* unspecified order.  I think
that's a pretty safe assumption.  The server would have to go way
out of its way to do differently, and it doesn't.

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] Are multiple array_aggs going to be in the same order?

[Merlin Moncure]

On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe  wrote:
>
> If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the ARRAY_AGGs 
> be guaranteed to have entries in the same (ie corresponding) order?
>
> eg
>
> SELECT
> u.name,
> ARRAY_AGG(o.order_date) AS order_dates,
> ARRAY_AGG(o.order_total) AS order_totals
> FROM
> user u JOIN
> orders o USING (user_id)
> GROUP BY
> u.user_id


It is unsafe to rely on aggregation order unless specified -- you can
add ORDER BY to the aggregation clause.

merlin


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

Re: [GENERAL] WAL being written during SELECT * query

[Tom DalPozzo]

2017-04-10 16:49 GMT+02:00 Bill Moran :

>
> > >> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo 
> > >> wrote:
> > >> > Hi,
> > >> > I have a very big table (10GB).
> > >> > I noticed that many WAL segments are being written when elaborating
> read
> > >> > only transactions like this:
> > >> > select * from dati256 where id >4300 limit 100;
> > >> > I don't understand why are there WAL writings during read only
> > >> transactions.
>
> These are hint bits. The mechanism and behavior are known and documented:
> https://wiki.postgresql.org/wiki/Hint_Bits
>
> --
> Bill Moran 
>

Hi, OK! Thank you very much
Pupillo

Re: [GENERAL] PostgreSQL and Kubernetes

[Moreno Andreo]

  
  
Il 03/04/2017 22:32, Alex Kliukin ha
  scritto:

as it makes little sense to build an aircraft carrier
  to carry a single Cessna. 

:-) A bit extreme, but it makes the idea ...
Thanks
Moreno.-

  
  
   
  

  Kind
regards,
  --
  Oleksii
“Alex" Kliukin

  
  



  

Re: [GENERAL] PostgreSQL and Kubernetes

[Moreno Andreo]

Il 03/04/2017 18:10, Joe Conway ha scritto:

On 03/31/2017 01:58 AM, Moreno Andreo wrote:

Il 30/03/2017 14:38, Vick Khera ha scritto:

On Thu, Mar 30, 2017 at 6:10 AM, Moreno Andreo
> wrote:

 Since I'm on Google Cloud Platform, I thought it would be a good
 idea to see what it offers.


They currently have in beta a Postgres flavor of their cloudsql. I
haven't used it yet, but I plan to test it sometime in the next couple
of months.

I'm aware of this (and I'm about to test it too), but while this is in
beta I need a solution to keep up with my architecture... I can't stay
with a single server and no backup (except a snapshot of the disk made
on a daily basis... but that's no real backup) waiting for Cloud SQL to
get out of beta...

Take a look at:
https://github.com/CrunchyData/crunchy-containers
This is another good post... but I decided to keep the current 
architecture, adding a standby server managed by repmgr and load 
balanced by PgPool II, with barman taking care of the backup. I don't 
think spending more than a week studying the docs and preparing the 
whole thing in test lab.


If I'm about to migrate to Cloud SQL I think it's not worth the effort 
to create a new structure and migrate in there, just for some time... 
and then migrate again to CloudSQL.


Thanks again
Moreno.-



HTH,

Joe






--
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] Unable to connect to Postgresql

[Daniel Verite]

John Iliffe wrote:

> So, the problem is resolved, although I have no idea why it was necessary.  

The key seems to be the PrivateTmp=true in the systemd service.
Apache is not chrooted as demonstrated upthread, but that
setting alone makes the normal, system-wide /tmp inaccessible
to its processes, including the postgres Unix domain socket opened
there.

I suspect that your attempt to set PrivateTmp=false as a test was
missed by systemd  for some reason. You could probably insist
in that direction and eventually make it work, but I don't see
how it's better than the alternative /var/pgsql or localhost through TCP.

> Also, I now have several hundred programmes to update to add the host path 
> and none of them will now be portable.

Given that you set two directories: /tmp and /var/pgsql,
I would think you can let the other apps use /tmp as before 
and have only Apache use /var/pgsql ?


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Unable to connect to Postgresql

[John Iliffe]

On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
>   John Iliffe wrote:
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there?
> 
>   pg_connect("host=/var/pgsql [...other parameters...]")
> 
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
> 
YUP IT WORKS!

to borrow Apache's happy-message when you first install it.

So, the problem is resolved, although I have no idea why it was necessary.  
Also, I now have several hundred programmes to update to add the host path 
and none of them will now be portable.

Somehow this seems a step backwards, but my sincere thanks to everyone on 
this list who helped me.  The whole thing was way beyond my detailed 
understanding of Linux.

I provided some further information in a previous memo about the details of 
the unit file which may provide some information for those who understand 
such things.  It might be a good idea to provide some details of the 
systemctl process in the Postgresql documentation for people who are first 
encountering it (like me) when they have been used to the old init.d file 
approach.  

I will bring the same thing to Apache's attention on their list too.

Regards,

John

> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
> 
> But again, most people would use host=localhost in this setup.
> 
> Best regards,


-- 
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] Unable to connect to Postgresql

[John Iliffe]

On Monday 10 April 2017 09:46:54 Daniel Verite wrote:
>   John Iliffe wrote:
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there?
> 
>   pg_connect("host=/var/pgsql [...other parameters...]")
> 
> The fact that the value for host starts with a slash indicates
> without ambiguity that it's a path on disk rather than the name
> or address of a host on the network.
> 
> However if apache runs in a chroot jail, it should no more see
> /var/pgsql than it sees /tmp
> Given an apache process id, on Linux you should be able
> to check the current filesystem-root of that process with
> # ls -ld /proc/$PID/root
> It should show a soft link to the directory that corresponds to
> the root from the point of view of the $PID process.
> 
You are exceeding my level of understanding of Linux but the following 
result suggests to me that Apache is using the common root directory (/) as 
its base. 

-
[root@prod04 John]# ls -ld /proc/27753/root
lrwxrwxrwx. 1 root root 0 Apr 10 10:52 /proc/27753/root -> /
--

Other than the rather odd requirement in the systemctl unit file that httpd 
has a private /tmp (see arrow below) there should be no reason why Apache 
can't see the /tmp directory.  One of the earlier references in this series 
mentioned I should check for that, which is the reason for the secondary 
socket on /var/pgsql/  .  I don't know how to make mod_php, or for that 
matter php-fpm, look for the Postgresql socket on /var/pgsql/ though.  
There is no conf file for mod_php and no directive that I can find in the 
php-fpm configuration file.


[root@prod04 John]# cat /etc/systemd/system/httpd.service
[Unit]
Description=The Apache HTTP Server

[Service]
Type=forking
EnvironmentFile=/usr/apache-2.4.25/bin/envvars
PIDFile=/var/run/httpd.pid
ExecStart=/usr/apache-2.4.25/bin/apachectl -k start
ExecReload=/usr/apache-2.4.25/bin/apachectl -k graceful
ExecStop=/usr/apache-2.4.25/bin/apachectl -k stop
KillSignal=SIGCONT
PrivateTmp=true<---

[Install]
WantedBy=multi-user.target



FYI, changing the PrivateTmp directive to false doesn't fix the problem and 
since I don't know why it is there I don't ant to touch it.

> But again, most people would use host=localhost in this setup.
> 
> Best regards,


-- 
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] WAL being written during SELECT * query

[Bill Moran]

> >> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo 
> >> wrote:
> >> > Hi,
> >> > I have a very big table (10GB).
> >> > I noticed that many WAL segments are being written when elaborating read
> >> > only transactions like this:
> >> > select * from dati256 where id >4300 limit 100;
> >> > I don't understand why are there WAL writings during read only
> >> transactions.

These are hint bits. The mechanism and behavior are known and documented:
https://wiki.postgresql.org/wiki/Hint_Bits

-- 
Bill Moran 


-- 
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] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 23:21:58 Adrian Klaver wrote:
> On 04/09/2017 05:30 PM, John Iliffe wrote:
> > On Sunday 09 April 2017 20:01:32 Adrian Klaver wrote:
> >> So the issue is in PHP via Apache using the socket, because if I
> >> remember right you used localhost in the Apache/PHP combination and
> >> it worked, correct?
> > 
> > Yes.
> > 
> > I think there is some confusion here, might be on my part, I don't
> > know.
> > 
> > There is a network connection from 192.168.1.10 to 192.168.1.6 to
> > Apache and then there should be a connection from Apache on using
> > localhost (or 127.0.0.1) to Postgresql.  So shouldn't that be
> > sufficient?  Other than the original error on my part, coding the
> > server's external address (192.168.1.6) in the db_connect() call
> > which is now fixed, shouldn't the pg_hba host address  line be
> > 127.0.0.1/32 ?
> 
> Yes, Apache is connecting to Postgres on the same machine so localhost
> should be sufficient for IP purposes.
> 
> Not sure that it applies here, but what does ifconfig show?
> 
---
root@prod04 John]# ifconfig -a
enp0s20f0u10: flags=4163  mtu 1500
inet 192.168.1.7  netmask 255.255.255.0  broadcast 192.168.1.255
inet6 fe80::84a:4eb9:f4a4:98a6  prefixlen 64  scopeid 0x20
ether 54:b8:0a:ef:c7:0f  txqueuelen 1000  (Ethernet)
RX packets 210126  bytes 238984885 (227.9 MiB)
RX errors 0  dropped 0  overruns 0  frame 0
TX packets 120884  bytes 14119037 (13.4 MiB)
TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s31f6: flags=4163  mtu 1500
inet 192.168.1.6  netmask 255.255.255.255  broadcast 192.168.1.6
inet6 fe80::62d4:f478:8bbb:34a1  prefixlen 64  scopeid 0x20
ether 38:d5:47:19:0d:cb  txqueuelen 1000  (Ethernet)
RX packets 4589  bytes 1072227 (1.0 MiB)
RX errors 0  dropped 0  overruns 0  frame 0
TX packets 1003  bytes 71483 (69.8 KiB)
TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
device interrupt 16  memory 0xf700-f702  

lo: flags=73  mtu 65536
inet 127.0.0.1  netmask 255.0.0.0
inet6 ::1  prefixlen 128  scopeid 0x10
loop  txqueuelen 1000  (Local Loopback)
RX packets 106993  bytes 22736948 (21.6 MiB)
RX errors 0  dropped 0  overruns 0  frame 0
TX packets 106993  bytes 22736948 (21.6 MiB)
TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
---

The actual connection being used should be on device "lo" which is on the 
"localhost" address of 127.0.0.1.

> > Anyhow, that is working properly now.  The domain socket doesn't have
> > an explicit address (for 'local') as it is on the current machine as
> > far as I understand.
> > 
> > Am I correct?
> 
> Correct. The issue is then why psql and stand alone PHP can see the
> domain socket while PHP through Apache does not. Not sure why that is at
> the moment. Hmm, had a thought. What user is Apache running as and does
> that user have permissions on the socket file(s)?
> 
Looks like anybody can connect to either of the Postgresql domain sockets.

socket on /tmp

srwxrwxrwx.  1 postgres postgres system_u:object_r:initrc_tmp_t:s00 Apr 
10 10:35 .s.PGSQL.5432 


second socket on /var/pgsql
--
srwxrwxrwx.  1 postgres postgres system_u:object_r:httpd_var_run_t:s0
0 Apr 10 10:35 .s.PGSQL.5432
--

Apache is running under userid apache as expected
---
[root@prod04 John]# ps -ef | grep httpd
root   420   356  0 10:39 pts/000:00:00 grep --color=auto httpd

root 27753 1  0 Apr09 ?00:00:00 
/usr/apache-2.4.25/bin/httpd -k start

apache   27754 27753  0 Apr09 ?00:00:03 
/usr/apache-2.4.25/bin/httpd -k start

apache   27755 27753  0 Apr09 ?00:00:04 
/usr/apache-2.4.25/bin/httpd -k start

apache   27756 27753  0 Apr09 ?00:00:04 
/usr/apache-2.4.25/bin/httpd -k start


with the following SELinux context info:
---
ps -eZ | grep httpd
system_u:system_r:unconfined_service_t:s0 27753 ? 00:00:00 httpd
system_u:system_r:unconfined_service_t:s0 27754 ? 00:00:03 httpd
system_u:system_r:unconfined_service_t:s0 27755 ? 00:00:04 httpd
system_u:system_r:unconfined_service_t:s0 27756 ? 00:00:04 httpd

--

> > John
> > 
>  Report back.
>  
> > Based on the reference that Joe sent earlier, I do have a second
> > domain socket on /var/pgsql but the problem is how do I get PHP to
> > look there? There isn't any config file for mod_php and php-fpm
> > has one but the location of the domain socket is the default -
> > 

Re: [GENERAL] Unable to connect to Postgresql

[John Iliffe]

On Sunday 09 April 2017 22:07:12 rob stone wrote:
> On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote:
> > > 
> > >
> > > You have Apache, PHP, and Postgres all running on your LAN at
> > > 192.168.1.6.
> > >
> > > 
> > > 
> > >
> > > You are on 192.168.1.10.
> > >
> > > 
> > >
> > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to
> > 
> > run
> > 
> > > that script?
> > 
> > testfcgi.php is in the document root of the default named virtual
> > server.
> 
> O.K.
> 
> So in sites-available, your conf file just tells Apache to run
> testfcgi.php and nothing else?

Apache just runs the programme whose name follows the / in the URL.  
Nothing fancy here at all.  testfcgi.php is a php script that is run by 
mod_php in Apache and uses the php command pg_connect() to try and reach 
Postgresql.  That works if Postgresql is reached via a TCP connection, as 
suggested by Adrian yesterday, but not if the connection is using a Unix 
domain socket, which is the usual way to do that.

Despite the name, testfcgi.php is running as a mod_php script, not a cgi.  
That is the next step to get working!

Regards,

John
> There is no resolution required by obtaining the IP address from
> /etc/hosts.
> 
> Are you able to put some trigger_error messages into testfcgi.php in
> order to make sure Apache is running the correct program?
> 
> Cheers,
> Rob


-- 
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] WAL being written during SELECT * query

[Tom DalPozzo]

2017-04-06 17:51 GMT+02:00 Tom DalPozzo :

>
>
> 2017-04-04 19:18 GMT+02:00 Scott Marlowe :
>
>> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo 
>> wrote:
>> > Hi,
>> > I have a very big table (10GB).
>> > I noticed that many WAL segments are being written when elaborating read
>> > only transactions like this:
>> > select * from dati256 where id >4300 limit 100;
>> > I don't understand why are there WAL writings during read only
>> transactions.
>> > Regards
>> > Pupillo
>>
>>
>>
>> I think this is the db setting hint bits, but I'm no expert in that area.
>>
>
> Hi,
> I'm not able to reproduce the problem in a deterministic way. Sometimes it
> does sometimes not.
> Anyway, examining with pg_xlogdum those WAL, I get many many records like
> these:
>
> rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
> C/E502AF28, prev C/E5028F20, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454439 FPW
> rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
> C/E502CF30, prev C/E502AF28, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454440 FPW
> rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
> C/E502EF38, prev C/E502CF30, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454441 FPW
> rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
> C/E5030F40, prev C/E502EF38, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454442 FPW
> rmgr: XLOGlen (rec/tot):  0/  8173, tx:  0, lsn:
> C/E5032F48, prev C/E5030F40, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454443 FPW
>
> What is the meaning of FPI_FOR_HINT?
>
> I've replication slots and async replication, but slaves are kept off (PCs
> not powered).
> No other queries except that one.
> Even select count (*) from dati256; gives the problem (sometimes)
> Thanks
> Pupillo
>
>
>
>
Hi, I can reproduce the problem.
1) Insert many rows (>50 milions) in the table.
2) perform a select like select count(*) in order to force the system to
read all the rows.
The FIRST time you do step 2) it writes all those FP_FOR_HINTS recs.
Next times or if the table is quite small, it doesn't do the problem.
If, instead of select count (*) you make a select which involved only a
group of tuples, it write only the recs regarding those tuples.


Regards
Pupillo

Re: [GENERAL] Unable to connect to Postgresql

[Daniel Verite]

John Iliffe wrote:

> Based on the reference that Joe sent earlier, I do have a second domain 
> socket on /var/pgsql but the problem is how do I get PHP to look there?  

  pg_connect("host=/var/pgsql [...other parameters...]")

The fact that the value for host starts with a slash indicates
without ambiguity that it's a path on disk rather than the name
or address of a host on the network.

However if apache runs in a chroot jail, it should no more see
/var/pgsql than it sees /tmp
Given an apache process id, on Linux you should be able
to check the current filesystem-root of that process with
# ls -ld /proc/$PID/root
It should show a soft link to the directory that corresponds to
the root from the point of view of the $PID process.

But again, most people would use host=localhost in this setup.

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Unable to connect to Postgresql

[Daniel Verite]

John Iliffe wrote:

> >  
> > 
> Basically, nothing.  
> 
> If I include it in an Apache script exactly as suggested, then the script 
> puts out a blank screen, no error messages.

So from the point of view of the Apache process, that file does
not exist. That's consistent with the error you reported when
trying to connect through the Unix domain socket:

  Unable to connect to PostgreSQL server: could not connect to server: 
  No such file or directory
  Is the server running locally and  accepting connections on
  Unix domain socket  "/tmp/.s.PGSQL.5432"?

If apache is chrooted, or secured by other means with the similar result
that it cannot access the full filesystem, that's expected.
In this case, connecting to localhost with TCP/IP rather than a Unix domain
socket looks like the simpler solution.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] TimeScaleDB -- Open Source Time Series Database Released (www.i-programmer.info);

[Steve Petrie, P.Eng.]

Please see below.

- Original Message - 
From: "Nicolas Paris" 

To: "Steve Petrie, P.Eng." 
Cc: 
Sent: Sunday, April 09, 2017 7:58 AM
Subject: Re: [GENERAL] TimeScaleDB -- Open Source Time Series Database 
Released (www.i-programmer.info);




Le 09 avril 2017 à 05:31, Steve Petrie, P.Eng. écrivait :

Warm Greetings To pgsql-general@postgresql.org

(I am a very newbie user of PG for a pretty trivial PHP / SQL web 
app. Been

lurking with great admiration for a long time, on the
pgsql-general@postgresql.org discussion list channel.)

I subscribe to a usefully wide-ranging but tightly edited source of
tech-related news:

www.i-programmer.info

* * *
* * *

Here is a link to an interesting recent i-programmer article titled 
"Open

Source Time Series Database Released":

http://www.i-programmer.info/news/84/10648.html

And here are selected snippets quoted from this i-programmer web 
article

about the TimeScaleDB open source project :

"A new, open-source time series database built with the Postgres 
engine has
been released. TimeScaleDB is currently available in a single-node 
version,

and is optimized for fast ingest and complex queries.

"The developers say that it offers advantages because unlike 
traditional
RDBMS, TimescaleDB it scales-out horizontally across multiple 
servers; while

unlike NoSQL databases, it natively supports all of SQL



Thanks for the work around timeseries databases !

No mention of horizontal sharding mecanisms in the paper. Can you
provide more details ?




Just to be clear. I am not involved in any way in the TimeSeriesDB 
project. To repeat, I happened to come across mention of TimeSeriesDB in 
an e-newsletter www.i-programmer.info


I don't use TimeSeriesDB myself. But I thought the claims by its 
developers for its providing NoSQL-like capabilities, but built on the 
PostgreSQL DB engine, and providing a complete SQL interface, might be 
interesting to some members of pgsql-general@postgresql.org


Regrets for any confusion caused.

Steve





Steve

* * *

Steve Petrie, P.Eng.

http://aspetrie.net
Oakville, Ontario, Canada
(905) 847-3253
apet...@aspetrie.net



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



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




--
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] [ADMIN] calculating table and index size

[Günce Kaya]

Hello,

Thanks for helpful answers and sharing all of your knowledge about this
issue. Your knowledge gave me ideas and made it more clear.

Thank all of you again.

Best regards,

Gunce

On Fri, Apr 7, 2017 at 6:30 PM, Guillaume Lelarge 
wrote:

> Le 7 avr. 2017 4:58 PM, "Alban Hertroys"  a écrit :
>
> On 7 April 2017 at 09:11, Günce Kaya  wrote:
> > Hi again,
> >
> > Sorry for delay.
> >
> > Guillaume, I read your answer for first question but It's not clear to
> me.
> > The table has a column and index also use that column. so in that
> example, I
> > think table size and index size should be equal. Why these are not equal?
>
> If I understand correctly, the table only has 1 (integer) column and
> all those 1,400,000 rows have the same value?
>
>
> That's what I also understood.
>
>
>
>
> Then the table has to store each row separately and thus has to store
> the same value repeatedly. It also has to store some meta-data, such
> as visibility information.
>
>
> The meta data is the important stuff here. You have around seven system
> columns for each row, bringing the row size from a mere 4 bytes to
> something a bit more than 30 bytes.
>
>
> The index on the other hand (assuming a btree index) knows that there
> is only a single value in the table and therefore only stores a single
> value, but it has to reference each row in the table that contains
> that value.
>
>
> Not true for a btree index. The value is stored as many times as it
> appears on the table.
>
> True on a gin index IIRC
>
>
>
>
> So the table and the index are storing different things, but the total
> size of each row/index node for that single integer column is of the
> same order of magnitude. That's why they are similar in size.
>
> If you would add another integer column to your table and VACUUM FULL
> the table, the table would be about double its size, but the index
> would stay the same size.
>
>
> The table wouldn't double in size. It would grow but not that much. Though
> I agree the index would stay the same.
>
>
> Regards,
> Alban.
>
>
> > On Wed, Apr 5, 2017 at 1:02 PM, Steven Chang 
> > wrote:
> >>
> >> Hello,
> >>
> >> try pgstattuple() and pgstatindex() , I think you will figure it
> out.
> >>
> >> Steven
> >>
> >> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge :
> >>>
> >>> Hi,
> >>>
> >>> 2017-04-05 9:44 GMT+02:00 Günce Kaya :
> 
>  Hi all,
> 
>  I have some questions about calculating table and index size.
> 
>  I have a dummy table which has an integer column and its index. The
>  table has 140 rows and all of rows are same thats value is
> 2000.
>  Table size is 50MB and index size is 31MB. Why there is too much size
>  difference between table and its index? what happen on data files
> when we
>  add index?
> 
> >>>
> >>> You have metadata informations in the table datafiles that you don't
> have
> >>> on the index datafiles. For example, all the system columns for each
> line.
> >>>
> 
>  Second question is that after created table, table size was 0 byte. I
>  inserted a row as 120 then table size was 8192 byte. I inserted five
> times
>  same value to the table and table size is still 8192 bytes. Table size
>  changed after inserted lots of rows. Table size was stabile till
> first few
>  hundred rows. why table size didn't change when I inserted lots of
> rows?
> 
> >>>
> >>> PostgreSQL works with 8KB blocks. When you insert a line, it puts it
> on a
> >>> block, but this block may contain many lines. So your next new lines
> still
> >>> fit in the first block... until it doesn't, and you'll see a new block
> >>> coming, making your table datafile grows to 16KB. And so on and so on.
> >>>
> >>>
> >>> --
> >>> Guillaume.
> >>>   http://blog.guillaume.lelarge.info
> >>>   http://www.dalibo.com
> >>
> >>
> >
> >
> >
> > --
> > Gunce Kaya
> >
> > Linkedin - Twitter - Blog
>
>
>
> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>
>
>


-- 
Gunce Kaya

Linkedin  - Twitter
 - Blog