Re: [GENERAL] In need of some JSONB examples ?

[Tim Smith]

Thanks for the extra feedback Merlin.  I'll look into it a bit more,
JSONB obviously needs a bit of experimentation in the lab to get my
query syntax right !

On 27 January 2015 at 00:13, Merlin Moncure  wrote:
> On Sun, Jan 25, 2015 at 6:50 AM, Tim Smith
>  wrote:
>>> "Doesn't meet my particular use-case exactly" is not quite the same thing.
>>
>>
>> I would have thought my outlined use-case was pretty basic and common ?
>
> It is.  If your objects are always laid out in about the same way, you
> can use operator extraction for that:
>
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb->1->'ID';
>  ?column?
> ──
>  "2"
>
> If you need to search in a more flexible way, then you need to look at
> the jsquery extension; jsquery allows for arbitrary indexed
> subdocument searching.  see: https://github.com/akorotkov/jsquery
>
> 
> postgres=# create extension jsquery;
> CREATE EXTENSION
>
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> @@ '*.ID = "2"';
>  ?column?
> ──
>  t
> (1 row)
>
> Time: 0.480 ms
> postgres=# select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> @@ '*.ID = "3"';
>  ?column?
> ──
>  f
> (1 row)
>
> postgres=# create table foo as  select
> '[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]'::jsonb
> as v;
> SELECT 1
>
> postgres=# create index on foo using gin (v jsonb_value_path_ops);
> CREATE INDEX
>
> postgres=# set enable_seqscan to false;
> SET
> Time: 0.676 ms
> postgres=# explain select * from foo where v @@ '*.ID = "3"';
>QUERY PLAN
> ─
>  Bitmap Heap Scan on foo  (cost=76.00..80.01 rows=1 width=32)
>Recheck Cond: (v @@ '*."ID" = "3"'::jsquery)
>->  Bitmap Index Scan on foo_v_idx  (cost=0.00..76.00 rows=1 width=0)
>  Index Cond: (v @@ '*."ID" = "3"'::jsquery)
> (4 rows)
>
> 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] Versioning Schema SQL ideas needed

[Tim Smith]

> DISTINCT ON is my favorite lesser-known Postgres feature.

You said it !  There I was, trying and failing to make "DISTINCT" work
for me, little did I know that the little word ON was missing from my
Postgresql vocabulary !


Thanks !

On 27 January 2015 at 02:24, Maciek Sakrejda  wrote:
> On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith 
> wrote:
>>
>> create table templates(
>>template_id int not null primary key,
>>template_groupid int not null,
>>template_version int not null
>>template_text text not null);
>>
>> Would I need to resort to using a CTE or subquery to make this sort of
>> thing work ?  I can't seem to make it work with group by since group
>> by expects aggregation. Surely I don't need to normalise it into a
>> couple of tables ?
>
>
>
> What sort of thing? Selecting the latest version of each template? Try
>
> SELECT
>   DISTINCT ON (template_group_id) template_id, template_group_id,
> template_version, template_text
> FROM
>   templates
> ORDER BY
>   template_group_id, template_version DESC
>
> You could even create a latest_templates view for this. DISTINCT ON is my
> favorite lesser-known Postgres feature.


-- 
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] multiple parameters to an AGGREGATE function

[Christian Paminger]

Hi,

maybe this will help:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer ) RETURNS 
anyelement AS $BODY$ SELECT a FROM unnest( $1 ) a ORDER BY a offset $2 
LIMIT 1; $BODY$


USE it without an extra aggregate-function.

SELECT _final_nth(array_agg(someelement),n) AS someelement FROM 
sometable GROUP BY someelement;


n is the offset.

Greetings ...



On Fri, Feb 3, 2012 at 11:27 AM, Graham 
 wrote:


Hi,
   first time poster here ...

I'm trying to write a simple Aggregate function which returns the nth
element in a collection - ultimately I want to find 95th, 90th percentiles
and so on.

It'd be called like:

select nth_element( value, 95 ) from something group by ...

I'm basing this on an example I found on the Wiki:

http://wiki.postgresql.org/wiki/Aggregate_Mode

So, I have:

CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer )
  RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest( $1 ) a
ORDER BY a
offset $2
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE nth_element( anyelement, integer ) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_nth,
  INITCOND='{}'
);

Where the 2nd parameter would be, say 5 for the 5th element an so on.

The function declaration seems fine. But the CREATE AGGREGATE declaration
fails with:

ERROR:  function array_append(anyarray, anyelement, integer) does not exist

so, I suppose it's decided to call array_append with all the parameters on
the command line, rather than just the array in the 1st element. Is there
any way to stop it doing this?

I've searched the online documentation and Googled but haven't found
anything.

thanks,

Graham
x


--
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] Postgres seems to use indexes in the wrong order

[Tim Uckun]

Thanks for the tips. I'll make some adjustments

On Tue, Jan 27, 2015 at 8:38 PM, Sameer Kumar 
wrote:

>
> On Tue, Jan 27, 2015 at 6:59 AM, Tim Uckun  wrote:
>
>> The query seems to first use the timestamp column which results in a huge
>> number of records and then filters out using the integer and the macaddr
>> indices.  If it was to use the integer index first it would start with a
>> tiny number of records.
>>
>
> ​May be the record distribution of quantiles is skewed.​ Have you tried to
> set more granular statistics for your int column?
>
> The effective_cache_size is one gig. The others are not set so therefore
>> the default.
>
>
> ​Ideally the effective_cache_size can be set to as much as 50-60% of your
> available memory. Also you need to tune your random_page_cost as per the
> behavior of your disk.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server​
>
> ​If these two does not work then may be you should go for setting a more
> granular statistics collection for your specific column-
>
> alter table  alter column  set  statistics 1000;
> analyze ;
>
> ​
>
>
> Best Regards,
>
> *Sameer Kumar | Database Consultant*
>
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
>
> M: *+65 8110 0350 <%2B65%208110%200350>*  T: +65 6438 3504 |
> www.ashnik.com
>
> *[image: icons]*
>
>
>
> [image: Email patch] 
>
>
>
> This email may contain confidential, privileged or copyright material and
> is solely for the use of the intended recipient(s).
>

[GENERAL]

[Oliver Dizon]

Hi Guys,

I hope I'm in the right place to throw this. I just want to ask the reason 
behind this weird scenario.

All records were deleted in a table even if the subquery in the where clause 
has a missing a column.

--from a certain script where table_id is not yet existing on table_sample

delete from main_table_sample where table_id in (select table_id from 
table_sample);

Im expecting that postgres will return an ERROR message like
'column "table_id" does not exist on table table_sample'

But it did not returned any error and deleted all the records instead.

Version: psql (8.4.9, server 9.2.5)


Thanks and Regards,
__
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoli...@rtzassociates.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] cannot start 9.3 after system crash

[Andreas Laggner]

Hi list,

the system HDD of a server running postgresql 9.3 died (sudden 
electronical death) during a pg_dump. The database is located on a 
hardware raid system (another HDD). I installed a new ubuntu 14.04 with 
postgresql 9.3 postgis 2.1. But i cannot start the server. First i got 
the error:


2015-01-27 09:37:11 CET   LOG:  database system was shut down at 
2015-01-23 16:37:02 CET

2015-01-27 09:37:11 CET   LOG:  invalid primary checkpoint record
2015-01-27 09:37:11 CET   LOG:  invalid secondary checkpoint record
2015-01-27 09:37:11 CET   PANIC:  could not locate a valid checkpoint record
2015-01-27 09:37:11 CET   LOG:  startup process (PID 5309) was 
terminated by signal 6: Aborted
2015-01-27 09:37:11 CET   LOG:  aborting startup due to startup process 
failure


I solved this by executing pg_resetxlog.

Now i get another error and i do not know what else i can do:


2015-01-27 12:02:22 CET   LOG:  database system was interrupted; last 
known up at 2015-01-27 10:24:56 CET
2015-01-27 12:02:22 CET   LOG:  database system was not properly shut 
down; automatic recovery in progress

2015-01-27 12:02:22 CET   LOG:  record with zero length at 360/E20001C8
2015-01-27 12:02:22 CET   LOG:  redo is not required
2015-01-27 12:02:22 CET [unknown] [unknown] LOG:  incomplete startup packet
2015-01-27 12:02:22 CET   FATAL:  could not access status of transaction 
214707
2015-01-27 12:02:22 CET   DETAIL:  Could not open file "pg_clog/": 
Datei oder Verzeichnis nicht gefunden.
2015-01-27 12:02:22 CET   LOG:  startup process (PID 6520) exited with 
exit code 1
2015-01-27 12:02:22 CET   LOG:  aborting startup due to startup process 
failure



thanks for helping  -  Andreas


--
Dipl. Geoökologe Andreas Laggner

Thünen-Institut für Agrarklimaschutz (AK)
Arbeitsgruppe Emissionsinventare
Johann Heinrich von Thünen-Institut (vTI),
Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

Thünen Institute of Climate-Smart Agriculture
Johann Heinrich von Thünen Institute (vTI),
Federal Research Institute for Rural Areas, Forestry and Fisheries

Bundesallee 50
D-38116 Braunschweig

Tel.: (+49) (0)531 596 2636
Fax : (+49) (0)531 596 2645
E-mail: andreas.lagg...@ti.bund.de
Homepage: http://www.ti.bund.de



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

[Albe Laurenz]

Oliver Dizon wrote:
> I hope I'm in the right place to throw this. I just want to ask the reason 
> behind this weird scenario.
> 
> All records were deleted in a table even if the subquery in the where clause 
> has a missing a column.
> 
> --from a certain script where table_id is not yet existing on table_sample
> 
> delete from main_table_sample where table_id in (select table_id from 
> table_sample);
> 
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
> 
> But it did not returned any error and deleted all the records instead.
> 
> Version: psql (8.4.9, server 9.2.5)

That seems unlikely.

Can you produce a reproducible test case?

Yours,
Laurenz Albe

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

[GENERAL] sslmode verify-ca and verify-full: essentialy the same?

[David Guyot]

Hi, there.

Firstly, as this is my first post on a PgSQL ML, I hope this ML is the
good one for my question.

I'm trying to secure further some PgSQL servers and am reading
documentation about libpq sslmode option. I have a question about that:
as I understand the internals of this option, the difference between
verify-ca and verify-full is that, for verify-full, client will compare
the hostname the server gave and the one in the SSL certificate, and
will give up if these two values differ. Am I right up to now?

If I'm right, I feel like the extra security of verify-full compared to
verify-ca is merely a smoke screen because, as far as I know, nothing
prevents a crafted server to read the certificate's hostname and give
this one as its own, and the libpq shouldn't show a better MitM
protection with verify-full than with verify-ca. If I'm wrong, where am
I wrong? How does libpq verify the server's name? Reverse DNS? Other
mean?

Hoping someone can enlighten me about this,

Regards.
-- 
David Guyot
Administrateur système, réseau et télécom / Sysadmin
Europe Camions Interactive / Stockway
Moulin Collot
F-88500 Ambacourt
03 29 30 47 85


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] (unknown)

[Thomas Kellerer]

Oliver Dizon schrieb am 27.01.2015 um 11:46:
> I hope I'm in the right place to throw this. I just want to ask the reason 
> behind this weird scenario.
> 
> All records were deleted in a table even if the subquery in the where clause 
> has a missing a column.
> 
> --from a certain script where table_id is not yet existing on table_sample
> 
> delete from main_table_sample where table_id in (select table_id from 
> table_sample);
> 
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
> 
> But it did not returned any error and deleted all the records instead.
> 
> Version: psql (8.4.9, server 9.2.5)

No, this is correct and complies with the SQL standard. If you reference a 
column in a sub-select that is available in the outer query but not in the 
sub-select, then it's automatically a reference to the column from the outer 
query. 

So in your query, the column "table_id" in the sub-query references the 
main_table_sample.table_id.

I agree this is confusing, but it's the way it was specified in the SQL 
standard.

Thomas



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

Re: [GENERAL] sslmode verify-ca and verify-full: essentialy the same?

[Magnus Hagander]

On Tue, Jan 27, 2015 at 2:29 PM, David Guyot <
david.gu...@europecamions-interactive.com> wrote:

> Hi, there.
>
> Firstly, as this is my first post on a PgSQL ML, I hope this ML is the
> good one for my question.
>
> I'm trying to secure further some PgSQL servers and am reading
> documentation about libpq sslmode option. I have a question about that:
> as I understand the internals of this option, the difference between
> verify-ca and verify-full is that, for verify-full, client will compare
> the hostname the server gave and the one in the SSL certificate, and
> will give up if these two values differ. Am I right up to now?
>

Almost correct. It will compare the hostname that the client used (in the
connection string) with the hostname in the SSL certificate, and give up if
the two values differ.

The server does not give the client a hostname at any point (other than the
CN of the certificate).


If I'm right, I feel like the extra security of verify-full compared to
> verify-ca is merely a smoke screen because, as far as I know, nothing
> prevents a crafted server to read the certificate's hostname and give
> this one as its own, and the libpq shouldn't show a better MitM
> protection with verify-full than with verify-ca. If I'm wrong, where am
> I wrong? How does libpq verify the server's name? Reverse DNS? Other
> mean?
>

libpq uses the hostname that you specify in the connection string (or in an
environment variable, or however you end up specifying it).


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

Re: [GENERAL] sslmode verify-ca and verify-full: essentialy the same?

[David Guyot]

Ah! So there was my error! Should be good to explain this in the
official libpq documentation, don't you think? If I correctly read, the
connection string as source of the hostname isn't explicit, there is
only the mention that libpq will check that the responding server is
“the one I specify”. Once I know that it means “the one I specify in the
connection string”, it's all clear, but, IMHO, there's still a doubt
when you don't know what that does mean.

Anyway, thanks for your help, Magnus.

Regards.

Le mardi 27 janvier 2015 à 14:37 +0100, Magnus Hagander a écrit : 
> On Tue, Jan 27, 2015 at 2:29 PM, David Guyot
>  wrote:
> Hi, there.
> 
> Firstly, as this is my first post on a PgSQL ML, I hope this
> ML is the
> good one for my question.
> 
> I'm trying to secure further some PgSQL servers and am reading
> documentation about libpq sslmode option. I have a question
> about that:
> as I understand the internals of this option, the difference
> between
> verify-ca and verify-full is that, for verify-full, client
> will compare
> the hostname the server gave and the one in the SSL
> certificate, and
> will give up if these two values differ. Am I right up to
> now? 
> 
> 
> Almost correct. It will compare the hostname that the client used (in
> the connection string) with the hostname in the SSL certificate, and
> give up if the two values differ.
> 
> 
> The server does not give the client a hostname at any point (other
> than the CN of the certificate).
> 
> 
> 
> 
> If I'm right, I feel like the extra security of verify-full
> compared to
> verify-ca is merely a smoke screen because, as far as I know,
> nothing
> prevents a crafted server to read the certificate's hostname
> and give
> this one as its own, and the libpq shouldn't show a better
> MitM
> protection with verify-full than with verify-ca. If I'm wrong,
> where am
> I wrong? How does libpq verify the server's name? Reverse DNS?
> Other
> mean? 
> 
> 
> libpq uses the hostname that you specify in the connection string (or
> in an environment variable, or however you end up specifying it).
> 
> 
> 
> 
> -- 
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/

-- 
David Guyot
Administrateur système, réseau et télécom / Sysadmin
Europe Camions Interactive / Stockway
Moulin Collot
F-88500 Ambacourt
03 29 30 47 85


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL]

[Tom Lane]

Oliver Dizon  writes:
> Hi Guys,
> I hope I'm in the right place to throw this. I just want to ask the reason 
> behind this weird scenario.

> All records were deleted in a table even if the subquery in the where clause 
> has a missing a column.

> --from a certain script where table_id is not yet existing on table_sample

> delete from main_table_sample where table_id in (select table_id from 
> table_sample);

> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'

Read up on "outer references" in SQL sub-selects.  This query is entirely
legal, although it doesn't do what you want.

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] SSO Windows-to-unix

[Jeremy Palmer]

Sorry to ask again, but I still have not got any reply to this. Maybe this 
indicates it’s a bad option?

>
>
> On Tue, Jan 13, 2015 at 5:15 AM, Jeremy Palmer 
> mailto:jpal...@linz.govt.nz>> wrote:
>
> Hi All,
>
> I'm just investigating the option for configuring SSO for windows clients 
> connecting to a PostgreSQL 9.3 server installed on Ubuntu 14.04. Our windows 
> environment uses a Windows 2012 domain controller.
>
> The best information I could find on this subject was 
> http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf,
>  but I see it's dated 2008. Could someone confirm that this is still the best 
> how-to guide for this subject and if there are any other considerations with 
> newer versions of PostgreSQL?
>
> Many thanks,
> Jeremy
>



This message contains information, which may be in confidence and may be 
subject to legal privilege. If you are not the intended recipient, you must not 
peruse, use, disseminate, distribute or copy this message. If you have received 
this message in error, please notify us immediately (Phone 0800 665 463 or 
i...@linz.govt.nz) and destroy the original message. LINZ accepts no 
responsibility for changes to this email, or for any attachments, after its 
transmission from LINZ. Thank You.

Re: [GENERAL]

[Oliver Dizon]

Oliver Dizon  writes:
> Hi Guys,
> I hope I'm in the right place to throw this. I just want to ask the reason 
> behind this weird scenario.

> All records were deleted in a table even if the subquery in the where clause 
> has a missing a column.

> --from a certain script where table_id is not yet existing on table_sample

> delete from main_table_sample where table_id in (select table_id from 
> table_sample);

> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'

> Read up on "outer references" in SQL sub-selects.  This query is entirely
> legal, although it doesn't do what you want.

>   regards, tom lane

It is indeed an example of outer references. I guess I should used aliases for 
tables in constructing queries with sub-selects to avoid this instance.

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] (unknown)

[Oliver Dizon]

Oliver Dizon schrieb am 27.01.2015 um 11:46:
> I hope I'm in the right place to throw this. I just want to ask the reason 
> behind this weird scenario.
> 
> All records were deleted in a table even if the subquery in the where clause 
> has a missing a column.
> 
> --from a certain script where table_id is not yet existing on table_sample
> 
> delete from main_table_sample where table_id in (select table_id from 
> table_sample);
> 
> Im expecting that postgres will return an ERROR message like
> 'column "table_id" does not exist on table table_sample'
> 
> But it did not returned any error and deleted all the records instead.
> 
> Version: psql (8.4.9, server 9.2.5)

   >No, this is correct and complies with the SQL standard. If you reference a 
column in a sub-select that is available in the outer query but   not in the 
sub-select, then it's automatically a reference to the column from the outer 
query. 

   >So in your query, the column "table_id" in the sub-query references the 
main_table_sample.table_id.

   >I agree this is confusing, but it's the way it was specified in the SQL 
standard.

   >Thomas

Yea. I've realized that it's yet another example of "outer
reference". I guess I really need to read another SQL book again :-)

Thanks!

__
Oliver G. Dizon
Z Getcare Systems Team
RTZ Associates, Inc.
johnoli...@rtzassociates.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] Conflicting function name in dynamically-loaded shared library

[Adam Mackler]

Hi:

I am wanting to define some functions as described in section 35.9 of
the manual, "C-Language Functions."  I am compiling pre-existing files
of c-code from another project into object files, and then linking
those object files into a shared library along with my own functions
that follow the posgresql calling convention, invoking other functions
as desired.

This pre-existing c-code I'm compiling happens to contain a function
named "point_add()".  I see the postgresql source file fmgrtab.c also
has a function with the same name.  When my code tries to invoke its
version of point_add() I get a bus error, and when I changed the name
of that function to something else the bus error went away.

Of course, since I'm working with source code I can just keep the
modified function name, but I would like to be able to keep my version
of this c code updated with that project and so to use it unmodified.

So my questions are: first, might I be wrong about the cause of this
bus error?  I cannot think of another reason why changing the name of
a function would have this effect, but maybe there's some other reason
besides the "point_add()" function in fmgrtab.c conflicting.  If so,
I'm interested to know.

If, however, it is probable that this bus error is a result of this
naming conflict, then do I have any options for working around it that
would enable me to use the code from this other library without
changing the name of its "point_add()" function?  I know I could ask
that project's developers to change the function's name, but that
could break other code that currently uses it, and even if it didn't,
I would prefer something less intrusive on that project.

Thanks very much for any ideas about this,
-- 
Adam Mackler


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