Re: [GENERAL] json select question
This is probably the start of your quest into this project. No doubt you’ll
need to handle other queries.
So my suggestion is first to rationalise the data storage before digging any
deeper.
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of CS DBA
Sent: 22 September 2016 00:54
To: pgsql-general
Subject: [GENERAL] json select question
All;
I'm working with a client running postgres 9.2, they have a table with a
"json_data_string" column of type json
the data looks something like this with lots of rows for each (i.e. lots of
json_data_string->book_name rows, lots of json_data_string->catalog_name rows,
etc:
'{ "book_name": "Book the Fourth", "author": { "first_name": "Charles",
"last_name": "Davis" } }'
'{ "catalog_name": "Catalog the Fourth", "author": { "first_name": "Charles",
"last_name": "Davis" } }'
'{ "magazine_name": "mag4", "author": { "first_name": "Charles", "last_name":
"Davis" } }'
'{ "category_name": "region", "author": { "first_name": "Charles", "last_name":
"Davis" } }'
How can i pull a unique list of all json column names? such as book_name,
catalog_name, etc
Thanks in advance
Re: [GENERAL] Unstable C Function
Ian Campbell writes: > Thanks for personally replying, Tom. I appreciate it. > You are correct. In the interim, I found the following change solved the > issue: > SPI_finish(); // move to here > SRF_RETURN_DONE(funcctx); That might work under light usage, but the problem with it is you're blocking any other function in the same query from using SPI, since you're leaving your own connection active when returning. Sooner or later that's gonna be a problem. 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] Monitor pg_xlog size via SQL with postgres 9.4
Hello all, is there a way to monitor the size of the pg_xlog directory in SQL? The goal is to monitor the pg_xlog file without ressorting to a 'du' like solution that needs a direct access to the machine. I know I can get the retained size for existing replication slots segment in case there are some with the following query : <<< SELECT slot_name, database, active, pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots ; >>> but how could I monitor the real size of the pg_xlog directory and detect it is growing (may be because the archive_command is becomed faulty) I was thinking of using the last_archived_wal field of the pg_stat_archiver table, but I am not sure it is accurate, and I do not find any postgres stored procedure that converts the file name into an LSN (ie, a function that does the opposite of pg_xlogfile_name()) Thanks and regards, Sylvain
Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4
On Thu, 22 Sep 2016 14:23:20 +0200 Sylvain Marechal wrote: > Hello all, > > is there a way to monitor the size of the pg_xlog directory in SQL? The > goal is to monitor the pg_xlog file without ressorting to a 'du' like > solution that needs a direct access to the machine. You might be inspired by the following query: https://github.com/OPMDG/check_pgactivity/blob/master/check_pgactivity#L6156 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] Monitor pg_xlog size via SQL with postgres 9.4
Hello: On Thu, Sep 22, 2016 at 2:23 PM, Sylvain Marechal wrote: > is there a way to monitor the size of the pg_xlog directory in SQL? The goal > is to monitor the pg_xlog file without ressorting to a 'du' like solution > that needs a direct access to the machine. Well AFAIK SQL doesn't even have the concept of a directory ( functions callable from SQL provide it, but SQL is for databases & AMOF you can have a perfectly valid SQL db without disks ), so I assume you want some way to get at the machine disk usage without opening a shell on it, from a db client. You could try one of the functions in https://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE and many of the untrusted programming languages for postgres functions ( plperl, plpython, etc ) has methods of calling stat in the server. 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] Monitor pg_xlog size via SQL with postgres 9.4
On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> is there a way to monitor the size of the pg_xlog directory in SQL? The
Assuming you have superuser privileges, it will most likely work:
select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir) as f
from pg_ls_dir('pg_xlog/')) x where not (f).isdir;
Best regards,
depesz
--
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] Monitor pg_xlog size via SQL with postgres 9.4
Thank you all, I thought there may be a specific posgresql stored procedure
that did the work of enumerating the files of the pg_xlog directory to get
its full size, or may be one that gave the full size of the server
databases including pg_xlog.
Nevermind, this does the work.
Regards,
Sylvain
2016-09-22 16:05 GMT+02:00 hubert depesz lubaczewski :
> On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> > is there a way to monitor the size of the pg_xlog directory in SQL? The
>
> Assuming you have superuser privileges, it will most likely work:
>
> select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir)
> as f from pg_ls_dir('pg_xlog/')) x where not (f).isdir;
>
> Best regards,
>
> depesz
>
>
Re: [GENERAL] Unstable C Function
I'm going to rewrite it to use your tuplestore suggestion. OK, so SPI is only suitable for single-call functions, right? If another function in the query attempted to use SPI, I assume there would be a deadlock? Regards, Ian On Thu, Sep 22, 2016 at 7:25 PM Tom Lane wrote: > Ian Campbell writes: > > Thanks for personally replying, Tom. I appreciate it. > > You are correct. In the interim, I found the following change solved the > > issue: > > > SPI_finish(); // move to here > > SRF_RETURN_DONE(funcctx); > > That might work under light usage, but the problem with it is you're > blocking any other function in the same query from using SPI, since > you're leaving your own connection active when returning. Sooner > or later that's gonna be a problem. > > regards, tom lane >
Re: [GENERAL] Unstable C Function
Ian Campbell writes: > OK, so SPI is only suitable for single-call functions, right? It's usable by strictly nested functions, but a multi-call SRF is more like a coroutine. > If another > function in the query attempted to use SPI, I assume there would be a > deadlock? No, the second arrival would get a SPI_ERROR_CONNECT failure from SPI_connect when there's already an open connection. For the nested- calls case you can prevent that with SPI_push/SPI_pop around a call that might wish to use SPI, but that fix doesn't work in a coroutine situation. 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
