Re: [GENERAL] PGpool question

[Tatsuo Ishii]

> This may be the wrong list, but I am not sure where it needs to go.  I am
> trying to set up pgpool, and I keeping on getting this message:
> 
> NOTICE:  add node from hostname:"xxx" port:9000 pgpool_port:
> rejected.
> Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid
> 10768: DETAIL:  verify the other watchdog node configurations
> Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid
> 10768: LOCATION:  watchdog.c:1481
> 
> I believe I have mismatched settings for the watchdog configuration, but I
> cannot find them.  I have debugging turned all the way up, but nothing is
> telling me what setting is the problem,  Is there any way I can figure out
> what specific watchdog setting its complaining about?

Muhammad Usama kindly followed the question:

The message refers to the configuration mismatch of other_pgpool_* config
parameter,
The one defined in section  5.14.9. Watchdog servers configurations
 of
the documentation.
So you need to check the other_pgpool_hostname, other_pgpool_port or
other_wd_port
config values.

If you have further questions, please post to pgpool-gene...@pgpool.net.

https://www.pgpool.net/mailman/listinfo/pgpool-general

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Question about loading up a table

[Alex Samad]

Hi

So just to go over what i have


server A (this is the original pgsql server 9.2)

Server X and Server Y ... PGSQL 9.6 in a cluster - streaming replication
with hot standby.


I have 2 tables about 2.5T of diskspace.

I want to get the date from A into X and X will replicate into Y.


I am currently on X using this command

pg_dump -U  -h  -t BIGTABLE -a  | sudo -u postgres -i
psql -q ;

This is taking a long time, its been 2 days and I have xfered around 2T..
This is just a test to see how long and to populate my new UAT env. so I
will have to do it again.

Problem is time.  the pg_dump process is single threaded.
I have 2 routers in between A and X but its 10G networking - but my network
graphs don't show much traffic.

Server X is still in use, there are still records being inserted into the
tables.

How can I make this faster.

I could shutdown server A and present the disks to server X, could I load
this up in PGSQL and do a table to table copy - i presume this would be
faster ... is this possible ?  how do I get around the same DB name ?
What other solutions do I have ?

Alex




On 1 August 2017 at 23:24, Scott Marlowe  wrote:

> On Mon, Jul 31, 2017 at 11:16 PM, Alex Samad  wrote:
> > Hi
> >
> > I double checked and there is data going over, thought I would correct
> that.
> >
> > But it seems to be very slow.   Having said that how do I / what tools
> do I
> > use to check through put
>
> Try the pg_current_xlog_location function on the slave?
>

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[Jeff Janes]

On Tue, Aug 1, 2017 at 9:24 AM, Dmitry Lazurkin  wrote:

> On 08/01/2017 07:13 PM, Jeff Janes wrote:
>
> I think that HashSet is a Java-specific term.  It is just a hash table in
> which there is no data to store, just the key itself (and probably a cash
> of the hashcode of that key), correct?
>
>
> Yes. And in Java HashSet implemented on top of HashMap (:
>
> I think a more general solution would be to get the planner and executor
> to run the in-list query using the Hash Join, the same way it runs the
> in-VALUES one.
>
>
> Have additional plan nodes big overhead?
>

I don't think a new plan node will have meaningfully more overhead than new
code of equal generality and robustness which has just been bolted on to
the side of an existing node.  I don't know how to test that, though.  If
the existing hash code is slow, it would probably be better to spend time
improving it for everyone than coming up with yet another implementation.
I know the existing simplehash.h code as specialized for tuples in
src/backend/executor/execGrouping.c
is horribly inefficient with memory usage when the tuples are skinny, but
that shouldn't be a problem for the number of values likely to be present
in a hard-coded in-list.


>
>
> I was impressed at how well the JSON and hstore worked, you might want to
> look at how they do it.  It is must be using an internal hash table of some
> sort.
>
> JSONB and HSTORE keep sorted pairs and use binary search.
>
Ah.  that would be another way to approach it.  How many types have btree
ordering functions without hashing functions, or the reverse?

Cheers,

Jeff

Re: [GENERAL] Shared Constants in PLPGSQL

[Merlin Moncure]

On Tue, Aug 1, 2017 at 8:29 AM, Tim Uckun  wrote:
> In my case I don't expect these constants to be changed on a regular basis.
> They will be set just once and that's it. I was thinking it would be just as
> easy to set them in a proc as it would be to set them in a table. By putting
> them in an immutable proc I can hopefully save a couple of compute cycles.

Sure. The point is, by having a proc return a table based composite
type, you can simplify changes down the line.   Adding a new setting
can be done via ALTER.  Changing a setting (should it become
necessary) can be done with an UPDATE.   The immutable wrapping
function does eliminate some fetches and I would generally write that
wrapper.

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] Problem compiling a C function on Windows - not finding _palloc0@4

[Dan Cooperstock at Software4Nonprofits]

OK, I fixed it by changing to a 64-bit compile, which was necessary anyways
because it has to work with a 64-bit install of PostgreSQL.

 

I'm still curious about how I would have fixed that if I needed the 32-bit
version though.

 



Dan Cooperstock
DONATION and ACCOUNTS web site:  
http://www.Software4Nonprofits.com
Email:   i...@software4nonprofits.com
Phone: 416-423-7722
Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada

If you do not want to receive any further emails from Software4Nonprofits,
please reply to this email with the subject line "UNSUBSCRIBE", and we will
immediately remove you from our mailing list, if you are on it.



 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dan Cooperstock at
Software4Nonprofits
Sent: August 1, 2017 12:21 PM
To: pgsql-general@postgresql.org
Subject: [SPAM] [GENERAL] Problem compiling a C function on Windows - not
finding _palloc0@4

 

I'm trying to compile a C function into a DLL for use in PostgreSQL, with
Visual Studio. I'm linking in postgres.lib, but when the linker runs, I'm
getting "unresolved external symbol _palloc0@4 referenced in function
_getSoundex@4". The code is just calling palloc0. I suspect it's something
about the calling convention, but I've tried both __stdcall and _cdecl and I
get the same error. 

 

I'm pre-declaring the function with PGDLLEXPORT to make sure it gets
exported.

 

Any thoughts? Thanks.

 



Dan Cooperstock
DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
Email: i...@software4nonprofits.com  
Phone: 416-423-7722
Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada

If you do not want to receive any further emails from Software4Nonprofits,
please reply to this email with the subject line "UNSUBSCRIBE", and we will
immediately remove you from our mailing list, if you are on it.

 

Re: [GENERAL] standby database crash

[Michael Paquier]

On Mon, Jul 31, 2017 at 11:15 PM, Seong Son (US)  wrote:
> So my questions are, could an old WAL segment being resent through the
> network cause crash like this?  Shouldn’t Postgresql be able to handle out
> of order WAL segments instead of just crashing?

When the streaming connection between a standby and a primary is cut,
the WAL receiver would restart and try to stream from the beginning of
the last segment it was in the middle of. See RequestXLogStreaming in
walreceiverfuncs.c.

> And what would be the best way to recover the standby server?  Resynching
> the entire database seems to be too time consuming.

You may want to check the validity of the so-said WAL segment as well.
Corrupted data could come from it.
-- 
Michael


-- 
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] Partitioned TEMP tables

[Ed Behn]

I tried that. I didn't seem to help.


*Ed Behn */ Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: (410)266-4426 / Cell: (240)696-7443
ed.b...@rockwellcollins.com

www.rockwellcollins.com


On Mon, Jul 31, 2017 at 4:16 PM, Tom Lane  wrote:

> Ed Behn  writes:
> > Does partitioning of TEMP tables not work like non-TEMP tables?
>
> Should be the same ... but you don't get any auto-analyze support on
> a temp table.  I wonder if you're remembering to ANALYZE the temp
> tables after you've populated them.
>
> regards, tom lane
>

Re: [HACKERS] [GENERAL] Not able to create collation on Windows

[Tom Lane]

Murtuza Zabuawala  writes:
> Yes, I was able to create collation using "C" instead of "POSIX" on windows,
> CREATE COLLATION public.test from pg_catalog."C";

Yeah, I thought that might happen.  So the point basically is that in
almost all of the collations code, the "C" and "POSIX" names are handled
by dedicated code paths that don't care what the system's locale support
thinks.  But we missed that for CREATE COLLATION.  Aside from the case
you ran into, this means you can't do CREATE COLLATION ... FROM "C"
at all on platforms that lack HAVE_LOCALE_T.  There's no good reason
for that IMO; not if we're one line of code away from allowing it.

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: [HACKERS] [GENERAL] Not able to create collation on Windows

[Murtuza Zabuawala]

Hi Tom,

Yes, I was able to create collation using "C" instead of "POSIX" on windows,

CREATE COLLATION public.test from pg_catalog."C";

--
Regards,
Murtuza Zabuawala

On Tue, Aug 1, 2017 at 9:09 PM, Tom Lane  wrote:

> Peter Eisentraut  writes:
> > On 8/1/17 10:53, Tom Lane wrote:
> >> I think this is actually a bug, because the collations code clearly
> >> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,
>
> > You seem to say that we should support a "POSIX" locale even on systems
> > where the C library does not support that.  I'm not convinced about that.
>
> Uh, we already do.  Note all the regression tests that unconditionally
> assume that the POSIX collation works.  Also, I am confused by your
> apparent belief that there might somewhere be a version of libc that
> fails to provide C-locale-compliant behavior.  Surely nobody would
> tolerate a version of strcmp() that fails to act per C spec.
>
> regards, tom lane
>

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[Dmitry Lazurkin]

On 08/01/2017 07:13 PM, Jeff Janes wrote:
> I think that HashSet is a Java-specific term.  It is just a hash table
> in which there is no data to store, just the key itself (and probably
> a cash of the hashcode of that key), correct? 

Yes. And in Java HashSet implemented on top of HashMap (:

> I think a more general solution would be to get the planner and
> executor to run the in-list query using the Hash Join, the same way it
> runs the in-VALUES one.

Have additional plan nodes big overhead?

> I was impressed at how well the JSON and hstore worked, you might want
> to look at how they do it.  It is must be using an internal hash table
> of some sort.

JSONB and HSTORE keep sorted pairs and use binary search.

[GENERAL] Problem compiling a C function on Windows - not finding _palloc0@4

[Dan Cooperstock at Software4Nonprofits]

I'm trying to compile a C function into a DLL for use in PostgreSQL, with
Visual Studio. I'm linking in postgres.lib, but when the linker runs, I'm
getting "unresolved external symbol _palloc0@4 referenced in function
_getSoundex@4". The code is just calling palloc0. I suspect it's something
about the calling convention, but I've tried both __stdcall and _cdecl and I
get the same error. 

 

I'm pre-declaring the function with PGDLLEXPORT to make sure it gets
exported.

 

Any thoughts? Thanks.

 



Dan Cooperstock
DONATION and ACCOUNTS web site: http://www.Software4Nonprofits.com
Email: i...@software4nonprofits.com  
Phone: 416-423-7722
Mail: 57 Gledhill Ave., Toronto ON M4C 5K7, Canada

If you do not want to receive any further emails from Software4Nonprofits,
please reply to this email with the subject line "UNSUBSCRIBE", and we will
immediately remove you from our mailing list, if you are on it.



 

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[Jeff Janes]

On Mon, Jul 31, 2017 at 12:29 PM, Dmitry Lazurkin  wrote:

> On 31.07.2017 19:42, Jeff Janes wrote:
>
> I think it is simply because no one has gotten around to implementing it
> that way.  When you can just write it as a values list instead, the
> incentive to make the regular in-list work better is not all that strong.
>
> Cheers,
>
> Jeff
>
>
> I see from explain that IN-clause uses just array with function ANY. I
> think for efficient implementation of this task I should implement new
> datatype "hashset". Am I wrong?
>

I think that HashSet is a Java-specific term.  It is just a hash table in
which there is no data to store, just the key itself (and probably a cash
of the hashcode of that key), correct?  PostgreSQL already has a template
for in-memory hash tables, src/include/lib/simplehash.h (and also one for
possibly-shared in-memory tables, src/backend/utils/hash/dynahash.c) , and
you should be able to specialize it for the case there there is no data
associated with the key.  I think the harder part would be to get the
planner to use the hash table you implement.  You would also have to
include code to fall back onto the array scanning for data types which do
not have a hash method defined.

I think a more general solution would be to get the planner and executor to
run the in-list query using the Hash Join, the same way it runs the
in-VALUES one.

I was impressed at how well the JSON and hstore worked, you might want to
look at how they do it.  It is must be using an internal hash table of some
sort.  But those only support strings as keys, while the in-list has to
support every data type, including user-defined-ones, so they have more
opportunities for optimization.

Cheers,

Jeff

Re: [HACKERS] [GENERAL] Not able to create collation on Windows

[Tom Lane]

Peter Eisentraut  writes:
> On 8/1/17 10:53, Tom Lane wrote:
>> I think this is actually a bug, because the collations code clearly
>> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,

> You seem to say that we should support a "POSIX" locale even on systems
> where the C library does not support that.  I'm not convinced about that.

Uh, we already do.  Note all the regression tests that unconditionally
assume that the POSIX collation works.  Also, I am confused by your
apparent belief that there might somewhere be a version of libc that
fails to provide C-locale-compliant behavior.  Surely nobody would
tolerate a version of strcmp() that fails to act per C spec.

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: [HACKERS] [GENERAL] Not able to create collation on Windows

[Peter Eisentraut]

On 8/1/17 10:53, Tom Lane wrote:
> Murtuza Zabuawala  writes:
>> I am trying to create collation on windows using default POSIX collation
>> with pgAdmin3 but I am getting error as shown in screenshot, Can someone
>> suggest how to fix this?
> 
>> *Syntax:*
>> CREATE COLLATION public.test from pg_catalog."POSIX";
> 
>> *Error:*
>> ERROR: could not create locale "POSIX". No error
> 
> Hmm.  Evidently Windows' _create_locale() doesn't accept "POSIX".
> You might find that "C" works instead, don't know for sure.
> 
> I think this is actually a bug, because the collations code clearly
> means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,

You seem to say that we should support a "POSIX" locale even on systems
where the C library does not support that.  I'm not convinced about that.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Not able to create collation on Windows

[Tom Lane]

Murtuza Zabuawala  writes:
> I am trying to create collation on windows using default POSIX collation
> with pgAdmin3 but I am getting error as shown in screenshot, Can someone
> suggest how to fix this?

> *Syntax:*
> CREATE COLLATION public.test from pg_catalog."POSIX";

> *Error:*
> ERROR: could not create locale "POSIX". No error

Hmm.  Evidently Windows' _create_locale() doesn't accept "POSIX".
You might find that "C" works instead, don't know for sure.

I think this is actually a bug, because the collations code clearly
means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c,
which could be noticeably simpler if that case weren't contemplated.
However, DefineCollation checks validity of the new collation by
unconditionally calling pg_newlocale_from_collation().  That violates
the advice in pg_newlocale_from_collation's header comment:

 * Also, callers should avoid calling this before going down a C/POSIX
 * fastpath, because such a fastpath should work even on platforms without
 * locale_t support in the C library.

Every other call site honors that.

So I think what we ought to do is change DefineCollation more or
less like this:

-   (void) pg_newlocale_from_collation(newoid);
+   if (!lc_collate_is_c(newoid) || !lc_ctype_is_c(newoid))
+   (void) pg_newlocale_from_collation(newoid);

Another issue exposed by this report is that we aren't reporting
_create_locale() failures in a useful way.  It's possible this
could be improved by inserting

#ifdef WIN32
_dosmaperr(GetLastError());
#endif

into report_newlocale_failure in pg_locale.c, but I'm not really
sure.  Microsoft's man page for _create_locale() fails to say much
of anything about its error-case behavior, and definitely does not
say that it sets the GetLastError indicator.  Still, there's certainly
no chance that printing errno without doing this will be useful.
I would suggest that we do that for starters, and if we hear that
we're still getting silly errors, just hot-wire the code to assume
ENOENT on Windows.

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] Logging in Code vs SQL-WHERE was: Row based permissions: at DB or at Application level?

[Thomas Güttler]

I have been thinking about this again.

Doing perm checking in code (I use Python) has the advantage that you can do 
logging:


def has_perm(item, user):
if user.is_superuser:
logger.debug('Access to %s for %s allowed, since user is superuser' % 
(item, user))
return True
if ...:
logger.debug('Access to %s for %s allowed, since ...' % (item, user))
logger.debug('Access to %s for %s not allowed.' % (item, user))
return False


We use this sometimes for debugging. This way I can see why a user is allowed 
to access an object or not.

This is an argument for permission checking in code.

On the other side I still think perm checking in SQL WHERE has more benefits.

Regards,
  Thomas Güttler





--
Thomas Guettler http://www.thomas-guettler.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] Shared Constants in PLPGSQL

[Tim Uckun]

In my case I don't expect these constants to be changed on a regular basis.
They will be set just once and that's it. I was thinking it would be just
as easy to set them in a proc as it would be to set them in a table. By
putting them in an immutable proc I can hopefully save a couple of compute
cycles.



On Wed, Aug 2, 2017 at 1:04 AM, Merlin Moncure  wrote:

> On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun  wrote:
> > What's the best way to deal with global constants in PLPGSQL. Currently
> I am
> > putting them in a function with out parameters and then calling that
> > function from every other function that needs them like this.
> >
> > CREATE OR REPLACE FUNCTION hashids.constants(
> >  OUT min_alphabet_length integer,
> > OUT sep_div numeric,
> > OUT guard_div numeric,
> > OUT default_steps text,
> > OUT default_alphabet text,
> > OUT salt text)
> >
> > I am presuming that if I set this function as immutable the calls to this
> > function will be cached and will not incur much overhead.
>
> Yes.  Couple things I'd suggest changing.
> 1. Make a control table, say, hashids.config and put your data there.
>
> CREATE TABLE hashids.config
> (
>   min_alphabet_length integer,
>   ...
> );
>
> -- one record only, please:
> CREATE UNIQUE INDEX ON hashids.config((1));
>
> 2. let's change your function to return the table type!
> CREATE OR REPLACE FUNCTION hashids.constants()
>   RETURNS hashids.config AS
> $$
>   SELECT * FROM hashids.config;
> $$ LANGUAGE SQL IMMUTABLE;
>
> ...here we're breaking a rule.  This is technically not an immutable
> query.  However, if you are calling this all over the place in
> plpgsql, you can save a few cycles since operations of the form of:
>
> DECLARE
>   settings hashid.config;
> BEGIN
>   settings := hashids.constants();
>   ...
>
> ...will be calculated at plan time and not re-evaluated every time the
> function is called.  The savings here are pretty minor but I've
> employed this trick many times because there's very little downside to
> doing so.  You do have to remember to recreate the constants()
> function every time you change a setting in order to force the plan to
> re-evaluate.  The main advantage over your approach is that you don't
> have to modify multiple things every time you add a new config values;
> just add a column and replace the function.
>
> merlin
>

Re: [GENERAL] Question about loading up a table

[Scott Marlowe]

On Mon, Jul 31, 2017 at 11:16 PM, Alex Samad  wrote:
> Hi
>
> I double checked and there is data going over, thought I would correct that.
>
> But it seems to be very slow.   Having said that how do I / what tools do I
> use to check through put

Try the pg_current_xlog_location function on the slave?


-- 
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] Shared Constants in PLPGSQL

[Merlin Moncure]

On Tue, Aug 1, 2017 at 8:04 AM, Merlin Moncure  wrote:
> re-evaluate.  The main advantage over your approach is that you don't
> have to modify multiple things every time you add a new config values;
> just add a column and replace the function.

This can be automated too, via event triggers:
https://www.postgresql.org/docs/9.3/static/sql-createeventtrigger.html

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] Shared Constants in PLPGSQL

[Merlin Moncure]

On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun  wrote:
> What's the best way to deal with global constants in PLPGSQL. Currently I am
> putting them in a function with out parameters and then calling that
> function from every other function that needs them like this.
>
> CREATE OR REPLACE FUNCTION hashids.constants(
>  OUT min_alphabet_length integer,
> OUT sep_div numeric,
> OUT guard_div numeric,
> OUT default_steps text,
> OUT default_alphabet text,
> OUT salt text)
>
> I am presuming that if I set this function as immutable the calls to this
> function will be cached and will not incur much overhead.

Yes.  Couple things I'd suggest changing.
1. Make a control table, say, hashids.config and put your data there.

CREATE TABLE hashids.config
(
  min_alphabet_length integer,
  ...
);

-- one record only, please:
CREATE UNIQUE INDEX ON hashids.config((1));

2. let's change your function to return the table type!
CREATE OR REPLACE FUNCTION hashids.constants()
  RETURNS hashids.config AS
$$
  SELECT * FROM hashids.config;
$$ LANGUAGE SQL IMMUTABLE;

...here we're breaking a rule.  This is technically not an immutable
query.  However, if you are calling this all over the place in
plpgsql, you can save a few cycles since operations of the form of:

DECLARE
  settings hashid.config;
BEGIN
  settings := hashids.constants();
  ...

...will be calculated at plan time and not re-evaluated every time the
function is called.  The savings here are pretty minor but I've
employed this trick many times because there's very little downside to
doing so.  You do have to remember to recreate the constants()
function every time you change a setting in order to force the plan to
re-evaluate.  The main advantage over your approach is that you don't
have to modify multiple things every time you add a new config values;
just add a column and replace the function.

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] Shared Constants in PLPGSQL

[bto...@computer.org]

- Original Message -
> From: "Tim Uckun" 
> To: "pgsql-general" 
> Sent: Tuesday, August 1, 2017 5:56:02 AM
> Subject: [GENERAL] Shared Constants in PLPGSQL
> 
> What's the best way to deal with global constants in PLPGSQL. Currently I
> am putting them in a function with out parameters and then calling that
> function from every other function that needs them like this.
> 
> CREATE OR REPLACE FUNCTION hashids.constants(
>  OUT min_alphabet_length integer,
> OUT sep_div numeric,
> OUT guard_div numeric,
> OUT default_steps text,
> OUT default_alphabet text,
> OUT salt text)
> 
> I am presuming that if I set this function as immutable the calls to this
> function will be cached and will not incur much overhead.
> 
> Is there a better way to deal with this?
> 

One alternative is to create a configuration values table to store application 
configuration parameters. This table generally has exactly one row. You can add 
new application configuration parameters easily as application requirements 
evolve by adding a new column of an appropriate data type. Then, when you need 
the configuration values in PLPSQL or anywhere for that matter, you do a simple 
SELECT statement. Also you, can update parameters if necessary (supposing if 
they are not actually *constants*) at run time very easily, too.

-- B



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

[GENERAL] Shared Constants in PLPGSQL

[Tim Uckun]

What's the best way to deal with global constants in PLPGSQL. Currently I
am putting them in a function with out parameters and then calling that
function from every other function that needs them like this.

CREATE OR REPLACE FUNCTION hashids.constants(
 OUT min_alphabet_length integer,
OUT sep_div numeric,
OUT guard_div numeric,
OUT default_steps text,
OUT default_alphabet text,
OUT salt text)

I am presuming that if I set this function as immutable the calls to this
function will be cached and will not incur much overhead.

Is there a better way to deal with this?