Re: [GENERAL] [ADMIN] How to drop stats on table

[Albe Laurenz]

Sridhar N Bamandlapally wrote:
> the actual issue is, when
> 
> 1. temp table is created with rows
> 2. stats/analyze on table (tmp1)
> 3. table dropped (tmp1)
> 
> but in stats related catalog tables a blot is created

What is a blot in this context? Are you talking about "bloat"?

> In this scenario, thousands of temp tables created per day, blots are 
> increasing and stats related
> tables are growing to 10's of GB
> 
> however, we schedule vacuum on catalog tables to control size
> 
> the worry is, catalog tables also undergo MVCC concept
> 
> I think when table is dropped, should have option to remove or reuse related 
> blot-space on catalog
> tables

It must be "bloat" you're talking about.

Do you have "autovacuum = on"?
If yes, it should take care of the problem if there are only thousands
of temporary tables per day.

What do you get for
   SELECT last_autovacuum, last_autoanalyze
   FROM pg_stat_sys_tables
   WHERE schemaname = 'pg_catalog' AND relname = 'pg_statistic';

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

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

[Albe Laurenz]

Chris Richards wrote:
> Adrian is correct. This worked by itself whereas using it in the creation of 
> the temporary table
> failed.
> mdb-> SELECT pq.* FROM policyqueue AS pq
> mdb-> JOIN seed_progress AS sp ON pq.id  =sp.polidx;
> 
> I checked the query Albe suggested; there were two `relfilenode`s (11936 and 
> 11937) that exhibited the
> error. Respectively, they were pg_depend_depender_index and 
> pg_depend_reference_index.
> 
> Unfortunately, I didn't disable the nightly processes and something must  
> have(?) fixed the glitch; at
> midnight GMT the query ran successfully. Ugh.
> 
> If it crops up again, I have some tools to try and capture data immediately, 
> and the suggested REINDEX
> since both appear to be indices.

These indexes are both on the system table "pg_depend".

That explains why the query ran without problems but the CREATE TABLE didn't:
Creating an object creates dependencies, and you have data corruption in the
system table that tracks dependencies.

I would be pretty worried in your place if I had a corrupted catalog, even if
it seems to have "fixed itself".  There might be other tables with corruption.

I would do two things:
1) Wait for a moment when there is little database traffic and
   run "REINDEX TABLE pg_depend;" to rebuild these indexes.

2) As soon as you can schedule some downtime, pg_dumpall the database
   cluster, stop the server, throw away the database cluster, create a new
   one with "initdb" and restore the dump into that.
   That will get rid of any lurking data corruption.
   Watch out for error messages during the pg_dumpall!


The main question is of course how you got the corruption in the first place.
Are you running the latest minor release for your PostgreSQL version?
Were there any crashes recently?
Do you have "fsync = on"?
Did you make sure that you have a reliable storage system?

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] current_query='IDLE" in pg_stat_activity

[paramjib baruah]

Hi ,

I am trying to check current running sql queries in postgres through
pg_stat_activity .However, in the current_query column I could see only
"IDLE" . I am not able to see the query .

Thanks
Paramjib Baruah

[GENERAL] using a postgres table as a multi-writer multi-updater queue

[Chris Withers]

Hi All,

I wondered if any of you could recommend best practices for using a 
postgres table as a queue. Roughly speaking, 100-200 workers will vomit 
rows and rates of a few hundres per second into the table leaving the 
status as new and then as many workers as needed to keep up with the 
load will plough through the queue changing the status to something 
other than new.


My naive implementation would be something along the lines of:

CREATE TABLE event (
tstimestamp,
event char(40),
statuschar(10),
CONSTRAINT pkey PRIMARY KEY(ts, event)
);


...with writers doing INSERT or COPY to get data into the table and 
readers doing something like:


SELECT FOR UPDATE * FROM event WHERE status='new' LIMIT 1000;

...so, grabbing batches of 1,000, working on them and then setting their 
status.


But, am I correct in thinking that SELECT FOR UPDATE will not prevent 
multiple workers selecting the same rows?


Anyway, is this approach reasonable? If so, what tweaks/optimisations 
should I be looking to make?


If it's totally wrong, how should I be looking to approach the problem?

cheers,

Chris

Re: [GENERAL] current_query='IDLE" in pg_stat_activity

[Chris Withers]

On 23/11/2015 10:33, paramjib baruah wrote:
I am trying to check current running sql queries in postgres through 
pg_stat_activity .However, in the current_query column I could see 
only "IDLE" . I am not able to see the query .
That would suggest no query is running, what suggests to you that there 
are queries you should be expecting to see?


cheers,

Chris




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

[GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

I got this error trying to upgrade one of our database clusters (happily in
testing) from 9.2 to 9.4:

Old and new cluster install users have different values for pg_authid.oid

Important background here is that we used to run the database as the
postgres unix user, but recently we had changed it to run as a different
user (because we have several different databases all running as the
postgres user on different machines and we wanted each logically separate
database to run as a different extra for that purpose unix user -- this
simplified internal administration management).

We had done this by adding a new superuser to the database (with the name
of the unix user it will run as in the future). turning off the database,
chown -R  databasedir, starting the database

(and adapting the startup scripts accordingly).

What I wasn't aware of is that postgres knows which user was used to run
pg_init.

So my first attempt at upgrading by running the below as the new user

old_loc=/mnt/dbc03-d1/proddb/postgres
new_loc=/mnt/dbc03-d1/proddb-94/postgres

rm -rf $new_loc/*
/usr/pgsql-9.4/bin/initdb $new_loc
self-service stop postgresql-9.2

/usr/pgsql-9.4/bin/pg_upgrade \
  -k \
  -j 8 \
  --old-datadir $old_loc \
  --new-datadir $new_loc \
  --old-bindir /usr/pgsql-9.2/bin \
  --new-bindir /usr/pgsql-9.4/bin

Failed with the above "Old and new cluster ..."

In my next attempt I tried adding the bold to the initdb command

/usr/pgsql-9.4/bin/initdb $new_loc *-U postgres*

But that eventually fails during pg_upgrade with:

connection to database failed: FATAL role "" does not exist

could not connect to new postmaster started with the command:
/usr/pgsql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/mnt/dbc03-d1/proddb-94/...

last thing it prints before that is

Creating dump of database schemas
  
ok

So I would love to know what the recommended way to go forward is.  Ideally
it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but if I
have to do some additional one off work this
time to get past this hurdle using the user that is of course an acceptable
trade off).

Thanks in advance,

Bene

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

[Ladislav Lenart]

Hello.


On 23.11.2015 11:41, Chris Withers wrote:
> Hi All,
> 
> I wondered if any of you could recommend best practices for using a postgres
> table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates 
> of
> a few hundres per second into the table leaving the status as new and then as
> many workers as needed to keep up with the load will plough through the queue
> changing the status to something other than new.
> 
> My naive implementation would be something along the lines of:
> 
> CREATE TABLE event (
> tstimestamp,
> event char(40),
> statuschar(10),
> CONSTRAINT pkey PRIMARY KEY(ts, event)
> );
> 
> 
> ...with writers doing INSERT or COPY to get data into the table and readers
> doing something like:
> 
> SELECT FOR UPDATE * FROM event WHERE status='new' LIMIT 1000;
> 
> ...so, grabbing batches of 1,000, working on them and then setting their 
> status.
> 
> But, am I correct in thinking that SELECT FOR UPDATE will not prevent multiple
> workers selecting the same rows?
> 
> Anyway, is this approach reasonable? If so, what tweaks/optimisations should I
> be looking to make?
> 
> If it's totally wrong, how should I be looking to approach the problem?

I suggest an excellent read on this topic:

http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/

Highly recommended if you haven't read it yet.

Also, if you aim on 9.5 (not released yet), it will introduce:

SELECT...
FOR UPDATE
SKIP LOCKED -- this is new

which supports exactly this use-case (i.e. to implement a job queue).


HTH,

Ladislav Lenart



-- 
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] current_query='IDLE" in pg_stat_activity

[Melvin Davidson]

You have not specified your O/S or PostgreSQL version, but if you are
running on a Linux O/S, you can run the attached current_queries.sh, which
loops every 5 seconds by default.
Note the usage options below:
usage() {
echo "Usage: $0 [-s  -d  -h  -U  -p
 -x -i]"
echo "-x will exit after 1 iteration"
echo "-i will exclude  queries"
exit 1
}

On Mon, Nov 23, 2015 at 5:42 AM, Chris Withers 
wrote:

>
> On 23/11/2015 10:33, paramjib baruah wrote:
>
>> I am trying to check current running sql queries in postgres through
>> pg_stat_activity .However, in the current_query column I could see only
>> "IDLE" . I am not able to see the query .
>>
> That would suggest no query is running, what suggests to you that there
> are queries you should be expecting to see?
>
> cheers,
>
> Chris
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



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


current_queries.sh
Description: Bourne shell script

-- 
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 cpu 100% need help

[Kevin Grittner]

On Mon, Nov 23, 2015 at 12:20 AM, 657985...@qq.com <657985...@qq.com> wrote:

> [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
> [always] madvise never
> [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
> [always] madvise never

There's your problem.  You need to set those to "never".  You can
get immediate relief by echoing 'never' to those pseudo-files, but
you need to configure your system to set them at OS boot time, too.

-- 
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] Problems with pg_upgrade after change of unix user running db.

[Jim Nasby]

On 11/23/15 5:12 AM, Benedikt Grundmann wrote:

So I would love to know what the recommended way to go forward is.
Ideally it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but
if I have to do some additional one off work this
time to get past this hurdle using the user that is of course an
acceptable trade off).


Can you provide a test script that demonstrates the problem?

The unix user that runs the database processes is pretty inconsequential 
to Postgres (as long as it's not root). There's generally no need for 
any correlation between unix accounts and database accounts. initdb uses 
the unix account running initdb for the initial database account, but as 
you've seen you can over-ride that.


pg_upgrade does care about unix accounts though (since it's dealing 
directly with the file system). It also needs to be able to connect to 
both databases, so it needs valid database login credentials as well.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] using a postgres table as a multi-writer multi-updater queue

[Jim Nasby]

On 11/23/15 6:12 AM, Ladislav Lenart wrote:

I suggest an excellent read on this topic:

http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/

Highly recommended if you haven't read it yet.


One thing it doesn't mention that you need to be aware of is the vacuum 
workload on a queue table. In a busy queue, it will be difficult or even 
impossible for vacuum to keep the amount of dead rows down to something 
manageable. That's why PgQ and Slony don't even attempt it; instead, 
they rotate through a fixed set of tables. Once all the entries in a 
table have been processed, the table is truncated.


If you go the delete route, make sure you don't index any fields in the 
queue that get updated (otherwise you won't get HOT updates), and run a 
very aggressive manual vacuum so the table stays small.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] using a postgres table as a multi-writer multi-updater queue

[Andy Colson]

On 11/23/2015 4:41 AM, Chris Withers wrote:

Hi All,

I wondered if any of you could recommend best practices for using a
postgres table as a queue. Roughly speaking, 100-200 workers will vomit
rows and rates of a few hundres per second into the table leaving the
status as new and then as many workers as needed to keep up with the
load will plough through the queue changing the status to something
other than new.

My naive implementation would be something along the lines of:

CREATE TABLE event (
 tstimestamp,
 event char(40),
 statuschar(10),
 CONSTRAINT pkey PRIMARY KEY(ts, event)
);


...with writers doing INSERT or COPY to get data into the table and
readers doing something like:

SELECT FOR UPDATE * FROM event WHERE status='new' LIMIT 1000;

...so, grabbing batches of 1,000, working on them and then setting their
status.

But, am I correct in thinking that SELECT FOR UPDATE will not prevent
multiple workers selecting the same rows?

Anyway, is this approach reasonable? If so, what tweaks/optimisations
should I be looking to make?

If it's totally wrong, how should I be looking to approach the problem?

cheers,

Chris


Have you tried Redis?  Its really good at that sort of thing.

-Andy


--
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] using a postgres table as a multi-writer multi-updater queue

[John R Pierce]

On 11/23/2015 2:41 AM, Chris Withers wrote:


If it's totally wrong, how should I be looking to approach the problem?



depending on where these queue entries are coming from, I'd considering 
using a message queueing system like AMS, MQseries, etc, rather than 
trying to use a relational database table as a queue. your external data 
source(s) would write messages to this queue, and you'd have 
'subscriber' processes that listen to the queue and process the 
messages, inserting persistent data into the database as needed.




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] error messages not getting logged when running script from cron

[anj patnaik]

I have a cron script that does the following:

PGPASSWORD=$PGPASSWORD /opt/PostgreSQL/9.4/bin/pg_dump -t RECORDER -Fc $i
-U postgres -Z0 | xz -9 > "$backup_dir/$i-$timeslot-database"

xzcat "$backup_dir/$i-$timeslot-database" |
/opt/PostgreSQL/9.4/bin/pg_restore -h $backupHost -U postgres -d backupDB
-c -p 5434 -v

My cron tab entry:

0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron"
myem...@comp.com

I am re-directing stderr to stdout and then sending that to email.

I get an email nightly with that title, but no error messages.

Last night, the restore never finished, but there was no error output.

Any ideas to get an email with error info.

thanks a lot

Re: [GENERAL] error messages not getting logged when running script from cron

[Kevin Grittner]

On Fri, Nov 20, 2015 at 5:09 PM, anj patnaik  wrote:

> 0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron" 
> myem...@comp.com
>
> I am re-directing stderr to stdout and then sending that to email.

But you are redirecting stdout (and therefore also stderr) to
/dev/null -- therefore there is no body input to the mail command.

> I get an email nightly with that title, but no error messages.

In that case you will probably be fine if you remove "1> /dev/null"
from the command.

--
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] error messages not getting logged when running script from cron

[Tom Lane]

anj patnaik  writes:
> My cron tab entry:

> 0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron"
> myem...@comp.com

> I am re-directing stderr to stdout and then sending that to email.

Uh, read it again: you're redirecting stdout to /dev/null and then
redirecting stderr to go where stdout goes.  So all output is
going to the bit bucket, not the pipe.

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] error messages not getting logged when running script from cron

[Melvin Davidson]

You could also try tweaking the following attached backup script.
Caution, I wrote this quickly from a skeleton script and has not been
tested.

On Mon, Nov 23, 2015 at 3:21 PM, Tom Lane  wrote:

> anj patnaik  writes:
> > My cron tab entry:
>
> > 0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron"
> > myem...@comp.com
>
> > I am re-directing stderr to stdout and then sending that to email.
>
> Uh, read it again: you're redirecting stdout to /dev/null and then
> redirecting stderr to go where stdout goes.  So all output is
> going to the bit bucket, not the pipe.
>
> 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
>



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


make_backup.sh
Description: Bourne shell script

-- 
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] error messages not getting logged when running script from cron

[John R Pierce]

On 11/23/2015 12:21 PM, Tom Lane wrote:

>0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron"
>myem...@comp.com
>I am re-directing stderr to stdout and then sending that to email.

Uh, read it again: you're redirecting stdout to /dev/null and then
redirecting stderr to go where stdout goes.  So all output is
going to the bit bucket, not the pipe.



the 2>&1 notation is not completely intuitiveif you want to 
redirect stderr to the pipe and bitbucket stdout, do it in the opposite 
order.


      2>&1 1> /dev/null | ...

that sends stderr to the file that stdout was assigned, and sends stdout 
to the bit bucket...   note specifically that redirecting stdout won't 
affect the file stderr is being sent.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

[Merlin Moncure]

On Mon, Nov 23, 2015 at 12:54 PM, John R Pierce  wrote:
> On 11/23/2015 2:41 AM, Chris Withers wrote:
>>
>>
>> If it's totally wrong, how should I be looking to approach the problem?
>
> depending on where these queue entries are coming from, I'd considering
> using a message queueing system like AMS, MQseries, etc, rather than trying
> to use a relational database table as a queue. your external data source(s)
> would write messages to this queue, and you'd have 'subscriber' processes
> that listen to the queue and process the messages, inserting persistent data
> into the database as needed.

I just don't agree with this generalization.  Keeping the state of the
queue in the database has a lot of advantages and is a lot easier to
deal with from a programming perspective especially if SQL is your
core competency.  Being able to produce and consume in SQL based on
other relational datasources is...elegant.

Specialized queue systems are a very heavy dependency and adding a new
server to your platform to mange queues is not something to take
lightly.  This advice also applies to scheduling systems like quartz,
specialized search like solr and elastisearch, and distributed data
platforms like hadoop.  I've used all of these things and have tended
to wish I had just used the database instead in just about every case.

Also, check out pgq (https://wiki.postgresql.org/wiki/PGQ_Tutorial).
Personally, I tend to roll my own queues.   It's not difficult.

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] ??: postgres cpu 100% need help

[Merlin Moncure]

On Mon, Nov 23, 2015 at 9:30 AM, Kevin Grittner  wrote:
> On Mon, Nov 23, 2015 at 12:20 AM, 657985...@qq.com <657985...@qq.com> wrote:
>
>> [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
>> [always] madvise never
>> [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
>> [always] madvise never
>
> There's your problem.  You need to set those to "never".  You can
> get immediate relief by echoing 'never' to those pseudo-files, but
> you need to configure your system to set them at OS boot time, too.

yup.  IMNSHO automatic enabling of THP defrag is one of the worst
performance related mistakes I've ever seen in the history of the
linux kernel (although maybe the blame lies with the distros...I'm not
sure).  It caused a number of terrible outages here before we figured
out the underlying cause.  (of course, everyone blamed postgres until
then)

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] using a postgres table as a multi-writer multi-updater queue

[John R Pierce]

On 11/23/2015 2:51 PM, Merlin Moncure wrote:

On Mon, Nov 23, 2015 at 12:54 PM, John R Pierce  wrote:
>
>depending on where these queue entries are coming from, I'd considering
>using a message queueing system like AMS, MQseries, etc, rather than trying
>to use a relational database table as a queue. your external data source(s)
>would write messages to this queue, and you'd have 'subscriber' processes
>that listen to the queue and process the messages, inserting persistent data
>into the database as needed.
I just don't agree with this generalization.  Keeping the state of the
queue in the database has a lot of advantages and is a lot easier to
deal with from a programming perspective especially if SQL is your
core competency.  Being able to produce and consume in SQL based on
other relational datasources is...elegant.


our whole system at $job is message based as its a distributed 
system.clients send messages to middleware servers that talk to the 
database servers.noone talks directly to the database, instead they 
use messaging.   also the several databases in our core cluster talk to 
each other with messaging, where the front end database publishes events 
that the other reporting database servers subscribe to.   its a very 
powerful model for building complex distributed systems and maintaining 
quite a lot of implementation flexibility, as the exact nature of the 
schema only needs to be known by a few publisher and subscriber modules.




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] JSON path wild cards?

[Dennis]

Is there a way to specify a wild card in a json path?

For example I have the following json doc:

[ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] },  {“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} 
] }, … ]

How do I write a select clause that can return the values for all b x values 
something like [{b:x}] that would return all the b:x values in the array? e.g. 
7 and 4 ... 

Also is there a definition of the syntax of a proper json path for use in 
postgres?

-- 
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] JSON path wild cards?

[Michael Paquier]

On Tue, Nov 24, 2015 at 1:39 PM, Dennis  wrote:
> Is there a way to specify a wild card in a json path?

No.

> For example I have the following json doc:
>
> [ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] },  {“a”:2,”b”: [ { “x”:
4,”y”:5,”z”:6} ] }, … ]
>
> How do I write a select clause that can return the values for all b x
values something like [{b:x}] that would return all the b:x values in the
array? e.g. 7 and 4 ...

To do a lookup at json arrays and look at what you wish you are going to
need some logic based on json_array_elements with -> or ->>. For example
using your case above:
=#  select ((value->'b')::json)->0->'x' as keys
from json_array_elements('[ {"a":1,"b": [ { "x": 7,"y":8,"z":9} ]
},{"a":2,"b": [ { "x": 4,"y":5,"z":6} ] }]'::json) AS json_data;
 keys
--
 7
 4
(2 rows)

That's a bit rough I agree but the correct functions wrapped with some
plpgsql or SQL could prove to be generic enough.

> Also is there a definition of the syntax of a proper json path for use in
postgres?

http://www.postgresql.org/docs/devel/static/functions-json.html
-- 
Michael

Re: [GENERAL] JSON path wild cards?

[Dennis]

Thanks,  weirdly I just stumbled on the idea when your reply came in, of using 
jsonb_array_elements function wrapped in a CTE that uses the containment 
operator within the CTE to select just the json docs I want and then I can 
select the specific json key values from each jsonb row/object returned by the 
CTE.  Basically  the same thing as a for each or wild card and it’s super fast 
with the jsonb indexing.

e.g

WITH jsd AS 
(
SELECT jsonb_array_elements([ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] },  
{“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} ] } ]) AS ae 
)
SELECT ae->’b’ FROM jsd;

Note: In my real world problem I have WHERE clause in the CTW statement using 
the @> operator so the CTE can be more selective against the entire set of 
jsonb doc’s in the containing table.

> 
> On Nov 23, 2015, at 10:39 PM, Dennis  wrote:
> 
> Is there a way to specify a wild card in a json path?
> 
> For example I have the following json doc:
> 
> [ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] },  {“a”:2,”b”: [ { “x”: 
> 4,”y”:5,”z”:6} ] }, … ]
> 
> How do I write a select clause that can return the values for all b x values 
> something like [{b:x}] that would return all the b:x values in the array? 
> e.g. 7 and 4 ... 
> 
> Also is there a definition of the syntax of a proper json path for use in 
> postgres?
> 
> -- 
> 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