Re: [GENERAL] Accessing structured datatypes using libpq

[John R Pierce]

On 6/10/2014 11:20 PM, Raimo Jormakka wrote:


What is the recommended way of accessing structured data-types 
(especially JSONB and HSTORE) using libpq? It seems that by default 
JSONB data for example is returned as text.


Is it not possible to have binary access to these data-types? If it is 
possible, then how do I do it exactly as the only thing I get from 
libpq is a void-pointer?


those are text data types.   there is no binary representations.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

[GENERAL] Accessing structured datatypes using libpq

[Raimo Jormakka]

Hi,

What is the recommended way of accessing structured data-types (especially
JSONB and HSTORE) using libpq? It seems that by default JSONB data for
example is returned as text.

Is it not possible to have binary access to these data-types? If it is
possible, then how do I do it exactly as the only thing I get from libpq is
a void-pointer?

Cheers,
Raimo

Re: [GENERAL] Question about partial functional indexes and the query planner

[Tom Lane]

Brian Dunavant  writes:
> I am using a partial functional index on a table where F(a) = a.  Querying
> whre F(a) = a hits the index as expected.  However the reverse statement a
> = F(a) does not.  I have verified this in 9.3.4.
> Is this a deficiency with the query planner, or are these not actually
> equivalent?  I've been stumped on it.

Interesting.  The reason this doesn't work is that predicate_implied_by()
fails to prove "a = b" given "b = a", at least in the general case.
It will figure that out if either a or b is a constant, but not if
neither one is.  The fact that it works with constants might explain
the lack of previous complaints, but this is still pretty surprising
given the amount of knowledge about equality that the system evinces
otherwise.  (I'm also wondering whether the EquivalenceClass logic
might not sometimes rewrite "a = b" into "b = a", causing a failure
of this sort even when the user *had* phrased his query consistently.)

It would be fairly straightforward to add a proof rule along the lines of
"if both expressions are binary opclauses, and the left input expression
of each one is equal() to the right input of the other, and the operators
are each other's commutators, then the implication holds".

An objection might be made that this would add noticeably to the cost of
failing proofs, but I think it wouldn't be that bad, especially if we did
the syscache lookup for the commutator check last.  In most scenarios the
equal() checks would fail pretty quickly when the proof rule doesn't
apply.  Also, I believe that in the case where a or b is a constant,
even though we can make the proof already, this approach would succeed
noticeably more quickly than btree_predicate_proof() can.  (Worth noting
also is that predicate_implied_by() isn't even used unless you have
things like partial indexes involved, so that its cost is not especially
relevant to "simple" queries.)

I'd be inclined to add some similar proof rules to predicate_refuted_by,
along the lines of "a op1 b refutes a op2 b if op1 is op2's negator" and
"a op1 b refutes b op2 a if op1 is the negator of op2's commutator".
Again, the code is currently unable to make such deductions unless a or b
is a constant.

Given the lack of previous complaints, I'm not sure this amounts to
a back-patchable bug, but it does seem like something worth fixing
going forward.

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] Question about partial functional indexes and the query planner

[Brian Dunavant]

Hi everyone,

I am using a partial functional index on a table where F(a) = a.  Querying
whre F(a) = a hits the index as expected.  However the reverse statement a
= F(a) does not.  I have verified this in 9.3.4.

Is this a deficiency with the query planner, or are these not actually
equivalent?  I've been stumped on it.

-Brian Dunavant

Test script to display behavior below:


-- Setup the test data
CREATE OR REPLACE FUNCTION public.return_if_even(v_id integer) returns
integer
LANGUAGE sql AS
$$
SELECT case when v_id % 2 = 1 then 0 else v_id end;
$$;

create table public.partial_functional_index_test as
select id from generate_series(1,100) AS s(id);

create index partial_functional_idx ON public.partial_functional_index_test
USING btree ( public.return_if_even(id) )
WHERE public.return_if_even(id) = id;

-- This will hit the index
explain analyze select count(1) from public.partial_functional_index_test
where public.return_if_even(id) = id;

-- This will not hit the index
explain analyze select count(1) from public.partial_functional_index_test
where id = public.return_if_even(id);


-- To work around it, I can index both ways:
drop index partial_functional_idx;

create index partial_functional_idx ON public.partial_functional_index_test
USING btree ( public.return_if_even(id) )
WHERE public.return_if_even(id) = id OR id = public.return_if_even(id);

-- Now both versions will hit the index
explain analyze select count(1) from public.partial_functional_index_test
where public.return_if_even(id) = id;
explain analyze select count(1) from public.partial_functional_index_test
where id = public.return_if_even(id);

-- Cleanup test data
drop table public.partial_functional_index_test;
drop function public.return_if_even(integer);

Re: [GENERAL] Warm standby (log shipping) from PG 8.3 to 9.3

[David Wall]

On 6/10/2014 11:54 AM, hubert depesz lubaczewski wrote:
On Tue, Jun 10, 2014 at 8:13 PM, David Wall > wrote:


Is it safe to assume that my working PG 8.3 archive command on the
master and recovery.conf (using contrib's pg_standby) on the
standby will work the same under 9.3?


Yes, it will work just fine. Of course you can't load 9.3 xlogs into 
8.3, or 8.3 xlogs into 9.3, but the commands are the same.


Thanks.  Yes, that makes sense as I'll update both DBs to the same 
version and we'll likely start with a fresh DB snapshot.


Do you know if there's a way to turn that warm standby into a hot 
standby (so I can query the standby during its ongoing recovery) 
easily?  Any docs that show the changes necessary to make that happen, 
or is that a bigger task?


David

Re: [GENERAL] Warm standby (log shipping) from PG 8.3 to 9.3

[hubert depesz lubaczewski]

On Tue, Jun 10, 2014 at 8:13 PM, David Wall  wrote:

> Is it safe to assume that my working PG 8.3 archive command on the master
> and recovery.conf (using contrib's pg_standby) on the standby will work the
> same under 9.3?
>

Yes, it will work just fine. Of course you can't load 9.3 xlogs into 8.3,
or 8.3 xlogs into 9.3, but the commands are the same.

Regards,

depesz

[GENERAL] Warm standby (log shipping) from PG 8.3 to 9.3

[David Wall]

Is it safe to assume that my working PG 8.3 archive command on the 
master and recovery.conf (using contrib's pg_standby) on the standby 
will work the same under 9.3?


That is, under PG 8.3, my master server uses:

archive_mode = on
archive_command = '~/postgresql/bin/copyWAL "%p" "%f"'
archive_timeout = 300

(and copyWAL does an SCP to the warm standby's recovery WALs directory)

And my warm standby recovery warm standby uses recovery.conf:

restore_command = '~/postgresql/bin/pg_standby -l -d -t 
~/postgresql/recoveryWALs/STOP_RECOVERY ~/postgresql/recoveryWALs %f %p 
%r 2>> ~/postgresql/logs/pg_standby.log'


I'm getting ready to do a migration to upgraded versions to 9.3 and 
wanted to know if I had to address this concern or whether it should 
just work the same as in 8.3.  We're not ready to try streaming.


I'll read up on what it means to do hot standby instead of warm 
standby.  We don't expect to need to use the standby for running queries 
from our app, but it would sure be nice, from a comfort level, for 
operations to be able to see updates in the standby.  Is it really just 
as easy as our current warm standby but just adding 'wal_level = 
hot_standby' to the master's postgresql.conf ?  Is there anything I can 
read about moving from 8.3 warm standby to 9.3 hot standby?


It's a bit confusing because of the various options for standby mode.  
Would it be better to use the new standby setup instead of pg_standby?


Thanks,
David


--
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] slow query question: more indexes considered harmful

[Sandeep Gupta]

This went outside the purview of the mailing list.

I wanted to get some input regarding the odd behaviour of the query
planner.
Mostly out of curiosity.

This (http://explain.depesz.com/s/vj4) query plan has actual time = 17217
vs.
this one (http://explain.depesz.com/s/ojX) which has actual time =  23321

Although the planner picks the second one. What it is about the query
planner
that make it skip plans using the index on  pid?.

Thanks.
Sandeep








On Sun, May 18, 2014 at 9:43 PM, Sandeep Gupta 
wrote:

> Hi,
>
>  I have typical setup consisting of two tables (demography and ses) with a
> typical filter-join-groupby-orderby query.
>
> Schemas:
> demography (pid int, countyid int)
> ses (pid int, exposed_time int)
>
> query:
>
> select countyid, count(pid)
> from demography, ses
> where demography.pid = ses.pid
> and exposed_time >4678 and exposed_time < 5042
> group by countyid
> order by countyid desc;
>
>
> If I have indexes on all the fields pid (in both tables), countyid,
> exposed_time then the
> query takes 21 secs.  The query plan is at http://explain.depesz.com/s/ojX
>
> If I drop the countyid and exposed_time index then the query takes 15-16
> secs.
> Query plan for this is here : http://explain.depesz.com/s/vj4
>
>
> I would like to keep all the indexes. But not sure what to change so that
> the engine
> is guided towards picking up the second plan.
>
> Thanks in advance,
> Sandeep
>
>
>
>

Re: [GENERAL] pg_standby replication problem

[Khangelani Gama]

Thank You, I will have a look.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, June 10, 2014 3:45 PM
To: Khangelani Gama; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_standby replication problem

On 06/09/2014 10:02 PM, Khangelani Gama wrote:
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Tuesday, June 10, 2014 1:42 AM
> To: Khangelani Gama; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg_standby replication problem
>
> On 06/09/2014 11:15 AM, Khangelani Gama wrote:
>> This is the standby replication setting with archive_command sending
>> the WALs from master to standby.
>>
>>
>
>
>
> Thanks  for feedback from everyone, I will try and remove the correct
> old walfiles.
>
>
>
>
> What are the conf settings on the standby server?
>
>
>
> Standby server config settings are as follows:
>

My mistake, I should have been more specific.

What is in your recovery.conf file on the standby?

Is the standby something you really want to try to rescue at this point or
would it be feasible just to start over?

If you do decide to start over a little time spent on what you want to
happen would help out.

Options to look at:

1) Streaming replication. WAL files are streamed from master to standby.

2) Hot standby. The standby can process read only queries while in standby
mode.

3) Archiving. WAL files are archived in a location for possible use by
standby. Needs some mechanism to prune files or you could fill a disk again.

All 3 of the above can be combined if desired, which is where the thinking
time part comes in.

Places to start looking for information:

http://www.postgresql.org/docs/9.3/interactive/high-availability.html

https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial



--
Adrian Klaver
adrian.kla...@aklaver.com


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee 
only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any 
review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended 
addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries 
distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than 
strictly for business purposes.



-- 
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] How can I tell if pg_restore is running?

[Francisco Olarte]

Hi Moshe:

On Tue, Jun 10, 2014 at 4:02 PM, Moshe Jacobson  wrote:
> My extension has a config table that is dumped by pg_dump and populated by
> pg_restore.

> Is there a way for my extension's trigger functions to return immediately
> when triggered by pg_restore?

Is there any reason to avoid pg_restore --disable-triggers ?

Francisco Olarte.


-- 
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] How can I tell if pg_restore is running?

[Martin French]

> My extension has a config table that is dumped by pg_dump and 
> populated by pg_restore.
> However, this table has triggers on it that I would like not to do 
> anything if the table is being populated by pg_restore. I want the 
> triggers to operate only if the user is manipulating the table 
> directly after the database has been restored and is running.
> 
> Is there a way for my extension's trigger functions to return 
> immediately when triggered by pg_restore?
> 
> 

If this is a data only restore, and you are running as a superuser, you 
can not simply specify pg_restore --disable-triggers ?
=

Romax Technology Limited 
A limited company registered in England and Wales.
Registered office:
Romax Technology Centre 
University of Nottingham Innovation Park
Triumph Road
Nottingham
NG7 2TU
United Kingdom
Registration Number: 2345696
VAT Number: 526 246 746

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=


Confidentiality Statement
This transmission is for the addressee only and contains information that 
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf 
of the addressee 
you may not copy or use it, or disclose it to anyone else. 
If you have received this transmission in error please delete from your 
system and contact the sender. Thank you for your cooperation.
=

Re: [GENERAL] two questions about fulltext searchign / tsvector indexes

[Jonathan Vanasco]

On Jun 10, 2014, at 8:26 AM, Vick Khera wrote:

Thanks so much for this.

We do a lot of searching on this column, so pre-computing seems to be the way.

I'm not worried about disk space for now, and can revisit that later if there 
is a problem

Just for clarification on this:

Option A (less fast):
create gin index on tsvector(searchable_column)

Option B (faster):
create tsvector column for `searchable_column`
create gin index on searchable_column





> On Mon, Jun 9, 2014 at 8:55 PM, Jonathan Vanasco  wrote:
>>I can't figure out which one to use.  This is on a steadily growing 
>> table of around 20MM rows that gets 20-80k new records a day, but existing 
>> records are rarely updated.
> 
> The question as always is a time-space trade-off. How frequently do
> you make the full text search? If you do it frequently, then with a
> pre-computed tsv column you save all that time per row of computing
> the tsvector on every search. If you do it infrequently, the space
> savings (and not needing to maintain that column) may benefit you.
> 
> Personally in these days of cheap disks I'd go with the dedicated
> column. Given that, you want to just have a GIN index on that one
> column, and the query you want, given some plain text string like
> "fluffy dog" is this:
> 
> select plainto_tsquery('fluffy dog') @@ my_tsv_column;
> 
> I always use a trigger on insert and update to maintain the ts_vector
> column, so there is no doubt of how it was computed by various
> programs.
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

// Jonathan Vanasco

c. 646.729.6436  |  415.501.9815
e. jonat...@2xlp.com
w. http://findmeon.com/user/jvanasco 
linkedin. http://linkedin.com/in/jonathanvanasco
blog. http://destructuring.net

Re: [GENERAL] How can I tell if pg_restore is running?

[Keith Fiske]

On Tue, Jun 10, 2014 at 10:02 AM, Moshe Jacobson  wrote:

> My extension has a config table that is dumped by pg_dump and populated by
> pg_restore.
> However, this table has triggers on it that I would like not to do
> anything if the table is being populated by pg_restore. I want the triggers
> to operate only if the user is manipulating the table directly after the
> database has been restored and is running.
>
> Is there a way for my extension's trigger functions to return immediately
> when triggered by pg_restore?
>
> Thanks.
>
> Moshe Jacobson
> Manager of Systems Engineering, Nead Werx Inc. 
> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
>
> "Quality is not an act, it is a habit." -- Aristotle
>

I'd look into advisory locks.

http://www.postgresql.org/docs/9.3/static/explicit-locking.html#ADVISORY-LOCKS
http://www.postgresql.org/docs/9.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

You'd probably have to wrap your pg_dump/restore in some sort of script
that can call the advisory locks, but I don't see why it wouldn't work.
Then you'd have your triggers check if the advisory lock is held and skip
whatever they do if so.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

[GENERAL] How can I tell if pg_restore is running?

[Moshe Jacobson]

My extension has a config table that is dumped by pg_dump and populated by
pg_restore.
However, this table has triggers on it that I would like not to do anything
if the table is being populated by pg_restore. I want the triggers to
operate only if the user is manipulating the table directly after the
database has been restored and is running.

Is there a way for my extension's trigger functions to return immediately
when triggered by pg_restore?

Thanks.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] pg_standby replication problem

[Adrian Klaver]

On 06/09/2014 10:02 PM, Khangelani Gama wrote:

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, June 10, 2014 1:42 AM
To: Khangelani Gama; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_standby replication problem

On 06/09/2014 11:15 AM, Khangelani Gama wrote:

This is the standby replication setting with archive_command sending
the WALs from master to standby.






Thanks  for feedback from everyone, I will try and remove the correct old
walfiles.




What are the conf settings on the standby server?



Standby server config settings are as follows:



My mistake, I should have been more specific.

What is in your recovery.conf file on the standby?

Is the standby something you really want to try to rescue at this point 
or would it be feasible just to start over?


If you do decide to start over a little time spent on what you want to 
happen would help out.


Options to look at:

1) Streaming replication. WAL files are streamed from master to standby.

2) Hot standby. The standby can process read only queries while in 
standby mode.


3) Archiving. WAL files are archived in a location for possible use by 
standby. Needs some mechanism to prune files or you could fill a disk again.


All 3 of the above can be combined if desired, which is where the 
thinking time part comes in.


Places to start looking for information:

http://www.postgresql.org/docs/9.3/interactive/high-availability.html

https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial



--
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] two questions about fulltext searchign / tsvector indexes

[Kevin Grittner]

Vick Khera  wrote:

> Jonathan Vanasco  wrote:

> Personally in these days of cheap disks I'd go with the dedicated
> column. Given that, you want to just have a GIN index on that one
> column, and the query you want, given some plain text string like
> "fluffy dog" is this:
>
> select plainto_tsquery('fluffy dog') @@ my_tsv_column;
>
> I always use a trigger on insert and update to maintain the ts_vector
> column, so there is no doubt of how it was computed by various
> programs.

I was going to make a similar recommendation, but Vick beat me to
it.  The only thing I would add, is that you might want to consider
whether a match in one column should carry more weight than a match
in the other column.  If so, you should convert each to a tsvector
separately, and give each one a different weight before
concatenating the tsvector objects and storing the result.

http://www.postgresql.org/docs/current/interactive/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSVECTOR

--
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] two questions about fulltext searchign / tsvector indexes

[Vick Khera]

On Mon, Jun 9, 2014 at 8:55 PM, Jonathan Vanasco  wrote:
> I can't figure out which one to use.  This is on a steadily growing 
> table of around 20MM rows that gets 20-80k new records a day, but existing 
> records are rarely updated.

The question as always is a time-space trade-off. How frequently do
you make the full text search? If you do it frequently, then with a
pre-computed tsv column you save all that time per row of computing
the tsvector on every search. If you do it infrequently, the space
savings (and not needing to maintain that column) may benefit you.

Personally in these days of cheap disks I'd go with the dedicated
column. Given that, you want to just have a GIN index on that one
column, and the query you want, given some plain text string like
"fluffy dog" is this:

select plainto_tsquery('fluffy dog') @@ my_tsv_column;

I always use a trigger on insert and update to maintain the ts_vector
column, so there is no doubt of how it was computed by various
programs.


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

Re: [GENERAL] PostgreSQL 9.2.4 + CentOS 6.5 64 bit - segfault error in initdb

[Bhushan Pathak]

I will try the the RPMs from the rhel 6 link & post updates.

Thanks
Bhushan Pathak


On Mon, Jun 9, 2014 at 8:06 PM, Adrian Klaver 
wrote:

> On 06/09/2014 01:53 AM, Bhushan Pathak wrote:
>
>>
>> I do not have any earlier versions of postgres installed, neither a
>> parallel instance running. In the pgstartup.log file, only the segfault
>> error is recorded, nothing else.
>>
>> I have downloaded the following RPMs from
>> http://yum.postgresql.org/9.2/redhat/rhel-5.6-x86_64/
>>
>
> I thought you where on Centos 6.5 would you not need?:
>
> http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/repoview/
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>