Re: [GENERAL] Unstable C Function

[Tom Lane]

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

Re: [GENERAL] Unstable C Function

[Ian Campbell]

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] Monitor pg_xlog size via SQL with postgres 9.4

[Sylvain Marechal]

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] Monitor pg_xlog size via SQL with postgres 9.4

[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



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

[Francisco Olarte]

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

[Jehan-Guillaume de Rorthais]

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

[GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

[Sylvain Marechal]

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] Unstable C Function

[Tom Lane]

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

Re: [GENERAL] json select question

[FarjadFarid(ChkNet)]

 

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]

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);

I'll look into tuplestore. Thanks for the hint. Fixed IMMUTABLE.

Regards
Ian Campbell

On Thu, Sep 22, 2016 at 9:29 AM Tom Lane  wrote:

> Ian Campbell  writes:
> > The function works fine on first call, sometimes more, then either resets
> > the connection or throws this on any further calls:
> > ERROR: cache lookup failed for type 0 SQL state: XX000
>
> I think the core problem here is that you're dealing with
> pass-by-reference results from the SPI_execute() --- specifically,
> the int4[] "vals" values --- as if they were pass-by-value.  You're
> just saving the Datums, which are only pointers, and expecting what
> they point to to still be good when you get around to doing
> heap_form_tuple with them.  But in reality they stopped being good
> the moment you did SPI_finish().
>
> The failures would be a lot less intermittent if you were testing in
> a debug build (with CLOBBER_FREED_MEMORY defined).
>
> The two basic approaches you could take that would work reliably are
>
> 1. Copy all the int4[] values into the multi_call_memory_ctx before
> doing SPI_finish.
>
> 2. Instead of using multi-call mode, form all the result tuples during
> a single call and return them in a tuplestore, so that all the work
> is done before you call SPI_finish.  You wouldn't really need the FIFO
> data structure if you did it that way.
>
> There are some other things I could criticize here, like labeling the
> function IMMUTABLE when its results depend on table contents, but
> they probably aren't causing your crashes.
>
> regards, tom lane
>