date:20180524

On Thursday, May 24, 2018, Lele Gaifax  wrote:
>
> So the questions: is the '?' style placeholder a supported variant? and if
> so,
> should the ParamRef doc tell something about that?
>

PostgreSQL's Prepare statement doesn't accept question mark as a parameter
symbol, and cannot ever because it is already a valid operator symbol.

David J.

Re: Insert data if it is not existing


On 05/23/2018 09:39 PM, David G. Johnston wrote:
On Wednesday, May 23, 2018, Adrian Klaver > wrote:



'''INSERT INTO my_table(name, age)
SELECT %s, %s
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''',
('Scott', 23)


I doubt that worked, you have three parameter markers(%s) and two
parameter values. Not only that two of the markers are for identifiers.


The count is indeed off but the two markers after the main select are 
literals, not identifiers.  As is the one being compared to name.


SELECT 'Scott', 23;

is a valid query.


Yeah, forgot about that.



David J.



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

Re: Insert data if it is not existing


On 05/23/2018 06:03 PM, tango ward wrote:





Updated my code to this:

curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))

If I remove SELECT statement, I will get an error message: error : 
psycopg2.ProgrammingError: syntax error at or near "WHERE"

LINE 12: WHERE NOT EXISTS

Trying to coordinate with Lead Dev about adding Index On The Fly


I tried to figure how to make this work and could not, so I led you down 
a false path.









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

RE:Extension/Language of plPerl in PostgreSQL

2018-05-24 Thread Karthick Damodar

Hi Team,

I had tried to install "plperlu" extension in PostgreSQL 9.5 and PostgreSQL 10. 
But I am returned with an following error message,

ERROR: could not load library "C:/Program Files/PostgreSQL/9.5/lib/plperl.dll": 
The specified module could not be found.
SQL state: XX000.

Note: I have installed ActiveState(Perl) in my local system and environment 
variables are assigned properly.

Even I tried in slack community and googled for this issue, but didn't get a 
proper solution.

Please let me know the solution for this as soon as possible.

FYI, following is the specification of the packages I installed,

1.   ActivePerl 5.26.1 Build 2601 (64-bit) - Perl

2.   pgAdmin 4 v3, pgAdmin III 1.20

3.   PostgreSQL 10 and PostgreSQL 9.5

4.   OS Windows 7 professional 64 bit operating system

Thanks,
Karthick
[Aspire Systems]

This e-mail message and any attachments are for the sole use of the intended 
recipient(s) and may contain proprietary, confidential, trade secret or 
privileged information. Any unauthorized review, use, disclosure or 
distribution is prohibited and may be a violation of law. If you are not the 
intended recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.

Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 06:31 AM, Karthick Damodar wrote:

Hi Team,

I had tried to install “*plperlu”* extension in PostgreSQL 9.5 and
PostgreSQL 10. But I am returned with an following error message,

ERROR: could not load library "C:/Program
Files/PostgreSQL/9.5/lib/plperl.dll": The specified module could not be
found.

SQL state: XX000.

*Note:* I have installed *ActiveState(Perl)* in my local system and
environment variables are assigned properly.

Even I tried in slack community and googled for this issue, but didn’t
get a proper solution.

Please let me know the solution for this as soon as possible.

FYI, following is the specification of the packages I installed,

1.ActivePerl 5.26.1 Build 2601 (64-bit) – Perl

2.pgAdmin 4 v3, pgAdmin III 1.20

3.PostgreSQL 10 and PostgreSQL 9.5

4.OS Windows 7 professional 64 bit operating system

Assuming you are using the EDB install see:

https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.02.html

Thanks,

Karthick

Aspire Systems

This e-mail message and any attachments are for the sole use of the
intended recipient(s) and may contain proprietary, confidential, trade
secret or privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited and may be a violation of law.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy all copies of the original message.

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

Re: Parameter placeholders, $n vs ?

2018-05-24 Thread Tom Lane

"David G. Johnston"  writes:
> On Thursday, May 24, 2018, Lele Gaifax  wrote:
>> So the questions: is the '?' style placeholder a supported variant? and
>> if so, should the ParamRef doc tell something about that?

> PostgreSQL's Prepare statement doesn't accept question mark as a parameter
> symbol, and cannot ever because it is already a valid operator symbol.

To enlarge on that a bit:

* PG's core parser certainly does not accept ? as a parameter symbol.
I speculate that you fed the input through some frontend that converts
? to $n (JDBC, perhaps)?

* The only thing in the core code that would print a ParamRef in any
sort of symbolic form is _outParamRef, and it's easily seen by inspection
to be incapable of omitting the "number" field ... not to mention that
it doesn't emit the JSON-esque representation you're showing us.  So
that behavior must also be due to some non-core pretty-printing code you
haven't identified to us.  No idea what rules that might have for deciding
to omit "number".

regards, tom lane

Re: Extension/Language of plPerl in PostgreSQL


On 05/24/2018 07:11 AM, Karthick Damodar wrote:

Please reply to list also, more eyes on the problem.
Ccing list.


Adrian,

Thanks for your response. So I need to install  Perl version 5.24 it seems. 
Since I could have installed EDB package. Let me try this one.


I have not used the EDB installer in some time, but as I remember it the 
Language Pack installs Perl(along with Python and TCL/TK) for you. In 
fact if I remember correctly it will only work properly with its own 
installed languages. You do have to configure it after install:


https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.09.html#



Will get back to you in case of any queries.

Thanks,
Karthick

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, May 24, 2018 7:31 PM
To: Karthick Damodar; pgsql-gene...@postgresql.org
Subject: Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 06:31 AM, Karthick Damodar wrote:

Hi Team,

I had tried to install "*plperlu"* extension in PostgreSQL 9.5 and
PostgreSQL 10. But I am returned with an following error message,

ERROR: could not load library "C:/Program
Files/PostgreSQL/9.5/lib/plperl.dll": The specified module could not be
found.

SQL state: XX000.

*Note:* I have installed *ActiveState(Perl)* in my local system and
environment variables are assigned properly.

Even I tried in slack community and googled for this issue, but didn't
get a proper solution.

Please let me know the solution for this as soon as possible.

FYI, following is the specification of the packages I installed,

1.ActivePerl 5.26.1 Build 2601 (64-bit) - Perl

2.pgAdmin 4 v3, pgAdmin III 1.20

3.PostgreSQL 10 and PostgreSQL 9.5

4.OS Windows 7 professional 64 bit operating system


Assuming you are using the EDB install see:

https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.02.html




Thanks,

Karthick

Aspire Systems

This e-mail message and any attachments are for the sole use of the
intended recipient(s) and may contain proprietary, confidential, trade
secret or privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited and may be a violation of law.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy all copies of the original message.




--
Adrian Klaver
adrian.kla...@aklaver.com
[Aspire Systems]

This e-mail message and any attachments are for the sole use of the intended 
recipient(s) and may contain proprietary, confidential, trade secret or 
privileged information. Any unauthorized review, use, disclosure or 
distribution is prohibited and may be a violation of law. If you are not the 
intended recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.




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

Re: Extension/Language of plPerl in PostgreSQL


On 05/24/2018 07:28 AM, Karthick Damodar wrote:

Okay Adrian. I will look into that.
But I have one query, Does language pack for Perl available in EDB site ?. 
because I downloaded it from perl.org


You should be able to install it from the StackBuilder tool that is 
included with your EDB Postgres install.




Thanks,
Karthick

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, May 24, 2018 7:50 PM
To: Karthick Damodar
Cc: pgsql-general
Subject: Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 07:11 AM, Karthick Damodar wrote:

Please reply to list also, more eyes on the problem.
Ccing list.


Adrian,

Thanks for your response. So I need to install  Perl version 5.24 it seems. 
Since I could have installed EDB package. Let me try this one.


I have not used the EDB installer in some time, but as I remember it the
Language Pack installs Perl(along with Python and TCL/TK) for you. In
fact if I remember correctly it will only work properly with its own
installed languages. You do have to configure it after install:

https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.09.html#



Will get back to you in case of any queries.

Thanks,
Karthick

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, May 24, 2018 7:31 PM
To: Karthick Damodar; pgsql-gene...@postgresql.org
Subject: Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 06:31 AM, Karthick Damodar wrote:

Hi Team,

I had tried to install "*plperlu"* extension in PostgreSQL 9.5 and
PostgreSQL 10. But I am returned with an following error message,

ERROR: could not load library "C:/Program
Files/PostgreSQL/9.5/lib/plperl.dll": The specified module could not be
found.

SQL state: XX000.

*Note:* I have installed *ActiveState(Perl)* in my local system and
environment variables are assigned properly.

Even I tried in slack community and googled for this issue, but didn't
get a proper solution.

Please let me know the solution for this as soon as possible.

FYI, following is the specification of the packages I installed,

1.ActivePerl 5.26.1 Build 2601 (64-bit) - Perl

2.pgAdmin 4 v3, pgAdmin III 1.20

3.PostgreSQL 10 and PostgreSQL 9.5

4.OS Windows 7 professional 64 bit operating system


Assuming you are using the EDB install see:

https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.02.html




Thanks,

Karthick

Aspire Systems

This e-mail message and any attachments are for the sole use of the
intended recipient(s) and may contain proprietary, confidential, trade
secret or privileged information. Any unauthorized review, use,
disclosure or distribution is prohibited and may be a violation of law.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy all copies of the original message.




--
Adrian Klaver
adrian.kla...@aklaver.com
[Aspire Systems]

This e-mail message and any attachments are for the sole use of the intended 
recipient(s) and may contain proprietary, confidential, trade secret or 
privileged information. Any unauthorized review, use, disclosure or 
distribution is prohibited and may be a violation of law. If you are not the 
intended recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.




--
Adrian Klaver
adrian.kla...@aklaver.com
[Aspire Systems]

This e-mail message and any attachments are for the sole use of the intended 
recipient(s) and may contain proprietary, confidential, trade secret or 
privileged information. Any unauthorized review, use, disclosure or 
distribution is prohibited and may be a violation of law. If you are not the 
intended recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.




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

RE: Extension/Language of plPerl in PostgreSQL

2018-05-24 Thread Karthick Damodar

Okay Adrian. I will look into that.
But I have one query, Does language pack for Perl available in EDB site ?. 
because I downloaded it from perl.org

Thanks,
Karthick

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Thursday, May 24, 2018 7:50 PM
To: Karthick Damodar
Cc: pgsql-general
Subject: Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 07:11 AM, Karthick Damodar wrote:

Please reply to list also, more eyes on the problem.
Ccing list.

> Adrian,
>
> Thanks for your response. So I need to install  Perl version 5.24 it seems. 
> Since I could have installed EDB package. Let me try this one.

I have not used the EDB installer in some time, but as I remember it the
Language Pack installs Perl(along with Python and TCL/TK) for you. In
fact if I remember correctly it will only work properly with its own
installed languages. You do have to configure it after install:

https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.09.html#

>
> Will get back to you in case of any queries.
>
> Thanks,
> Karthick
>
> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Sent: Thursday, May 24, 2018 7:31 PM
> To: Karthick Damodar; pgsql-gene...@postgresql.org
> Subject: Re: Extension/Language of plPerl in PostgreSQL
>
> On 05/24/2018 06:31 AM, Karthick Damodar wrote:
>> Hi Team,
>>
>> I had tried to install "*plperlu"* extension in PostgreSQL 9.5 and
>> PostgreSQL 10. But I am returned with an following error message,
>>
>> ERROR: could not load library "C:/Program
>> Files/PostgreSQL/9.5/lib/plperl.dll": The specified module could not be
>> found.
>>
>> SQL state: XX000.
>>
>> *Note:* I have installed *ActiveState(Perl)* in my local system and
>> environment variables are assigned properly.
>>
>> Even I tried in slack community and googled for this issue, but didn't
>> get a proper solution.
>>
>> Please let me know the solution for this as soon as possible.
>>
>> FYI, following is the specification of the packages I installed,
>>
>> 1.ActivePerl 5.26.1 Build 2601 (64-bit) - Perl
>>
>> 2.pgAdmin 4 v3, pgAdmin III 1.20
>>
>> 3.PostgreSQL 10 and PostgreSQL 9.5
>>
>> 4.OS Windows 7 professional 64 bit operating system
>
> Assuming you are using the EDB install see:
>
> https://www.enterprisedb.com/docs/en/10.0/Language_Pack_v10/EDB_Postgres_Language_Pack_Guide.1.02.html
>
>
>>
>> Thanks,
>>
>> Karthick
>>
>> Aspire Systems
>>
>> This e-mail message and any attachments are for the sole use of the
>> intended recipient(s) and may contain proprietary, confidential, trade
>> secret or privileged information. Any unauthorized review, use,
>> disclosure or distribution is prohibited and may be a violation of law.
>> If you are not the intended recipient, please contact the sender by
>> reply e-mail and destroy all copies of the original message.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> [Aspire Systems]
>
> This e-mail message and any attachments are for the sole use of the intended 
> recipient(s) and may contain proprietary, confidential, trade secret or 
> privileged information. Any unauthorized review, use, disclosure or 
> distribution is prohibited and may be a violation of law. If you are not the 
> intended recipient, please contact the sender by reply e-mail and destroy all 
> copies of the original message.
>


--
Adrian Klaver
adrian.kla...@aklaver.com
[Aspire Systems]

This e-mail message and any attachments are for the sole use of the intended 
recipient(s) and may contain proprietary, confidential, trade secret or 
privileged information. Any unauthorized review, use, disclosure or 
distribution is prohibited and may be a violation of law. If you are not the 
intended recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.

computing z-scores

2018-05-24 Thread Martin Mueller

You construct a z-score for a set of values by subtracting the average from the 
value and dividing the result by the standard deviation. I know how to do this 
in a two-step procedure. First, I compute the average and standard deviation. 
In a second run I use the formula and apply it to each value. 

Is there a way of doing this in a single-step procedure or can you chain the 
two parts together in one query?  This goes beyond my SQL competence. 

Martin Mueller

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

2018-05-24 Thread Maxim Boguk

On Thu, May 24, 2018 at 12:38 PM, Maxim Boguk  wrote:

>
>
>>
>> > About gdb bt - it's tricky because it is mission critical master db of
>> > huge project.
>> > I'll will try promote backup replica and check is issue persist there
>> and
>> > if yes - we will have our playground for a while, but it will require
>> > sometime to arrange.
>>
>> You should be ok to just bt that in the running cluster, but I
>> definitely understand if you don't want to do that...  I'd appreciate if
>> you set up the a playground, because this seems like something that'll
>> reappear.
>>
>> Greetings,
>>
>> Andres Freund
>>
>
> bt full :
>

#0  errmsg_internal (fmt=0x555b62e6eb70 "found xmin %u from before
relfrozenxid %u") at
/build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8/build/../src/backend/utils/error/elog.c:828
edata = 
__func__ = "errmsg_internal"
#1  0x555b62ad1cb9 in heap_prepare_freeze_tuple (tuple=,
relfrozenxid=relfrozenxid@entry=248720453,
relminmxid=relminmxid@entry=53644256,
cutoff_xid=3485221679,
cutoff_multi=, frz=frz@entry=0x555b640d1988,
totally_frozen_p=0x7ffca32c0e90 "\001")
at
/build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8/build/../src/backend/access/heap/heapam.c:6685
changed = 
freeze_xmax = 0 '\000'
xid = 
totally_frozen = 
__func__ = "heap_prepare_freeze_tuple"
#2  0x555b62bfd2b5 in lazy_scan_heap (aggressive=0 '\000', nindexes=2,
Irel=0x555b64095948, vacrelstats=, options=26,
onerel=0x555b64029498)
at
/build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8/build/../src/backend/commands/vacuumlazy.c:1090
tuple_totally_frozen = 1 '\001'
itemid = 0x7f2b7bc5d4fc
buf = 18138842
page = 
offnum = 26
maxoff = 26
hastup = 1 '\001'
nfrozen = 0
freespace = 
all_frozen = 1 '\001'
tupgone = 0 '\000'
prev_dead_count = 0
all_visible_according_to_vm = 0 '\000'
all_visible = 1 '\001'
has_dead_tuples = 0 '\000'
visibility_cutoff_xid = 3490221678
relname = 0x555b640296a8 "pg_authid"
relminmxid = 53644256
tups_vacuumed = 0
indstats = 0x555b64095928
relfrozenxid = 248720453
skipping_blocks = 0 '\000'
frozen = 0x555b640d1988
initprog_val = {1, 2, 582}
vacuumed_pages = 0
num_tuples = 80
nkeep = 0
ru0 = {tv = {tv_sec = 1527175061, tv_usec = 739743}, ru = {ru_utime
= {tv_sec = 0, tv_usec = 6}, ru_stime = {tv_sec = 1, tv_usec = 416000},
{ru_maxrss = 9704,
  __ru_maxrss_word = 9704}, {ru_ixrss = 0, __ru_ixrss_word =
0}, {ru_idrss = 0, __ru_idrss_word = 0}, {ru_isrss = 0, __ru_isrss_word =
0}, {ru_minflt = 33982,
  __ru_minflt_word = 33982}, {ru_majflt = 0, __ru_majflt_word =
0}, {ru_nswap = 0, __ru_nswap_word = 0}, {ru_inblock = 0, __ru_inblock_word
= 0}, {ru_oublock = 8,
  __ru_oublock_word = 8}, {ru_msgsnd = 0, __ru_msgsnd_word =
0}, {ru_msgrcv = 0, __ru_msgrcv_word = 0}, {ru_nsignals = 0,
__ru_nsignals_word = 0}, {ru_nvcsw = 38446,
  __ru_nvcsw_word = 38446}, {ru_nivcsw = 2, __ru_nivcsw_word =
2}}}
vmbuffer = 8763411
empty_pages = 0
nunused = 0
i = 
next_unskippable_block = 
buf = {data = 0x1 , len
= -1557393520, maxlen = 32764, cursor = -1557393616}
nblocks = 2
blkno = 
tuple = {t_len = 144, t_self = {ip_blkid = {bi_hi = 0, bi_lo = 1},
ip_posid = 26}, t_tableOid = 1260, t_data = 0x7f2b7bc5e6b0}
initprog_index = {0, 1, 5}
#3  lazy_vacuum_rel (onerel=onerel@entry=0x555b64029498,
options=options@entry=1, params=params@entry=0x7ffca32c11b0,
bstrategy=)
at
/build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8/build/../src/backend/commands/vacuumlazy.c:253
vacrelstats = 
Irel = 0x555b64095948
nindexes = 2
ru0 = {tv = {tv_sec = 93850993708032, tv_usec = 23936}, ru =
{ru_utime = {tv_sec = 128, tv_usec = 93851007694584}, ru_stime = {tv_sec =
0, tv_usec = 0}, {ru_maxrss = 140723046059424,
  __ru_maxrss_word = 140723046059424}, {ru_ixrss =
93850989549501, __ru_ixrss_word = 93850989549501}, {ru_idrss = 16,
__ru_idrss_word = 16}, {ru_isrss = 93851007694584,
  __ru_isrss_word = 93851007694584}, {ru_minflt =
140723046059472, __ru_minflt_word = 140723046059472}, {ru_majflt =
93850989559601, __ru_majflt_word = 93850989559601}, {
  ru_nswap = 93850994010032, __ru_nswap_word = 93850994010032},
{ru_inblock = 139833259949608, __ru_inblock_word = 139833259949608},
{ru_oublock = 93850994010032,
  __ru_oublock_word = 93850994010032}, {ru_msgsnd =
93851008239032, __ru_msgsnd_word = 93851008239032}, {ru_msgrcv =
140723046059904, __ru_msgrcv_word = 140723046059904}, {
  ru_nsignals = 93850989313526, __ru_nsignals_word =
93850989313526}, {ru_nvcsw = 345176855416, __ru_nvcsw_word = 345176855416},
{ru_nivcsw = 140723046059551,

Re: computing z-scores

On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> You construct a z-score for a set of values by subtracting the average
> from the value and dividing the result by the standard deviation. I know
> how to do this in a two-step procedure. First, I compute the average and
> standard deviation. In a second run I use the formula and apply it to each
> value.
>
> Is there a way of doing this in a single-step procedure or can you chain
> the two parts together in one query?  This goes beyond my SQL competence.
>

Window functions provide the easiest means to apply aggregated values to
individual rows.

SELECT v, (v - (AVG(v) OVER ()) / (stddev(v) OVER ())) AS z_v
FROM  (
VALUES (1),(2),(3)
) vals (v);

//-1, 0, 1

https://www.postgresql.org/docs/10/static/tutorial-window.html

David J.

Re: computing z-scores

2018-05-24 Thread Ron


On 05/24/2018 10:15 AM, Martin Mueller wrote:

You construct a z-score for a set of values by subtracting the average from the 
value and dividing the result by the standard deviation. I know how to do this 
in a two-step procedure. First, I compute the average and standard deviation. 
In a second run I use the formula and apply it to each value.

Is there a way of doing this in a single-step procedure or can you chain the 
two parts together in one query?  This goes beyond my SQL competence.


What about this?

SELECT value, (value - AVG(value))/stddev(value) as zvalue
FROM sometable
WHERE some conditions
GROUP by value


--
Angular momentum makes the world go 'round.

Re: computing z-scores

On Thursday, May 24, 2018, Ron  wrote:

> On 05/24/2018 10:15 AM, Martin Mueller wrote:
>
>> You construct a z-score for a set of values by subtracting the average
>> from the value and dividing the result by the standard deviation. I know
>> how to do this in a two-step procedure. First, I compute the average and
>> standard deviation. In a second run I use the formula and apply it to each
>> value.
>>
>> Is there a way of doing this in a single-step procedure or can you chain
>> the two parts together in one query?  This goes beyond my SQL competence.
>>
>
> What about this?
>
> SELECT value, (value - AVG(value))/stddev(value) as zvalue
> FROM sometable
> WHERE some conditions
> GROUP by value
>

That is syntactically correct but doesn't provide the correct answer.

David J.

Re: computing z-scores

On Thursday, May 24, 2018, David G. Johnston 
wrote:

> On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
> martinmuel...@northwestern.edu> wrote:
>
>> You construct a z-score for a set of values by subtracting the average
>> from the value and dividing the result by the standard deviation. I know
>> how to do this in a two-step procedure. First, I compute the average and
>> standard deviation. In a second run I use the formula and apply it to each
>> value.
>>
>> Is there a way of doing this in a single-step procedure or can you chain
>> the two parts together in one query?  This goes beyond my SQL competence.
>>
>
> Window functions provide the easiest means to apply aggregated values to
> individual rows.
>
> SELECT v, (v - (AVG(v) OVER ()) / (stddev(v) OVER ())) AS z_v
> FROM  (
> VALUES (1),(2),(3)
> ) vals (v);
>
> //-1, 0, 1
>
> https://www.postgresql.org/docs/10/static/tutorial-window.html
>
> David J.
>

I think I have misplaced a parenthesis though...order of operations needs
one added around the subtraction.

Note, this is not the correct list for questions like this.  The -general
list is the one you want to be using.

David J.

Performance opportunities for write-only audit tables?

2018-05-24 Thread Paul Jungwirth


Hi,

A project of mine uses a trigger-based approach to record changes to an 
audit table. The audit table is partitioned by month (pg 9.5, so 
old-fashioned partitioning). These tables are write-heavy but 
append-only and practically write-only: we never UPDATE or DELETE, and 
we seem to consult them only a few times a year. But they are enormous: 
bigger than the rest of the database in fact. They slow down our 
backups, they increase WAL size and streaming replication, they add to 
recovery time, they make upgrades more time-consuming, and I suppose 
they compete for RAM.


This is all on an AWS EC2 instance with EBS storage. We also run a warm 
standby with streaming replication.


Since these tables are so different from everything else, I'm wondering 
what opportunities we have to reduce their performance cost. I'm 
interested both in practical high-bang-for-buck changes, but also in 
harder just-interesting-to-think-about last-resort approaches. Here are 
a few ideas of my own, but I'm curious what others think:


We already have no indexes or foreign keys on these tables, so at least 
there's no cost there.


Since they are already partitioned, we could move old data to offline 
storage and drop those tables. This feels like the biggest, easiest win, 
and something we should have done a long time ago. Probably it's all we 
need.


Put them on a different tablespace. This one is also pretty obvious, but 
aside from using a separate disk, I'm curious what other crazy things we 
could do. Is there any per-tablespace tuning possible? (I think the 
answer within Postgres is no, but I wish we could change the settings 
for wal_level, or exclude them from replication, or something, so I'm 
wondering if we could achieve the same effect by exploiting being on a 
separate filesystem.) Maybe put the tablespace on some FUSE filesystem 
to get async writes? Or just pick different mount options, e.g. on ext4 
lazytime,dealloc,data=writeback? I don't know. Or at a different level: 
change the triggers so they call a custom function that uses a new 
thread to store the audit records elsewhere. Maybe these ideas are all 
too risky, but I think the organization is fine with slightly relaxed 
durability guarantees for this data, and anyway I'm just curious to have 
a list of possibilities before I categorize anything as too crazy or 
not. :-)


If we upgraded to pg 10 we could use logical replication and leave out 
the audit tables. That is appealing. Even without upgrading, I guess we 
could replace those tables with postgres_fdw ones, so that they are not 
replicated? Has anyone else used that trick?


Thanks!

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hmm .. surely

diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 5016181fd7..5d7fa1fb45 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -6690,7 +6690,7 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple,
xid = HeapTupleHeaderGetXmin(tuple);
xmin_frozen = ((xid == FrozenTransactionId) ||
   HeapTupleHeaderXminFrozen(tuple));
-   if (TransactionIdIsNormal(xid))
+   if (!xmin_frozen && TransactionIdIsNormal(xid))
{
if (TransactionIdPrecedes(xid, relfrozenxid))
ereport(ERROR,


??


-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Parameter placeholders, $n vs ?

2018-05-24 Thread Lele Gaifax

Tom Lane  writes:

> "David G. Johnston"  writes:
>> On Thursday, May 24, 2018, Lele Gaifax  wrote:
>>> So the questions: is the '?' style placeholder a supported variant? and
>>> if so, should the ParamRef doc tell something about that?
>
>> PostgreSQL's Prepare statement doesn't accept question mark as a parameter
>> symbol, and cannot ever because it is already a valid operator symbol.
>
> To enlarge on that a bit:
>
> * PG's core parser certainly does not accept ? as a parameter symbol.
> I speculate that you fed the input through some frontend that converts
> ? to $n (JDBC, perhaps)?
>
> * The only thing in the core code that would print a ParamRef in any
> sort of symbolic form is _outParamRef, and it's easily seen by inspection
> to be incapable of omitting the "number" field ... not to mention that
> it doesn't emit the JSON-esque representation you're showing us.  So
> that behavior must also be due to some non-core pretty-printing code you
> haven't identified to us.  No idea what rules that might have for deciding
> to omit "number".

Thank you to both. As said, the tool is built on the PG 10 parser extracted as a
standalone library, that serializes the statement AST as JSON. I assumed it
didn't diverge much from what the core parser accepts.

I will clarify my doubt with libpg_query's author.

bye, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

Re: Error on vacuum: xmin before relfrozenxid

2018-05-24 Thread Paolo Crosato

2018-05-24 8:30 GMT+02:00 Andrey Borodin :

> Hi!
>
> > 24 мая 2018 г., в 0:55, Paolo Crosato 
> написал(а):
> >
> > 1) VACUUM FULL was issued after the first time the error occurred, and a
> couple of times later. CLUSTER was never run.
> > 2) Several failovers tests were perfomed before the cluster was moved to
> production. However, before the move, the whole cluster was wiped,
> including all the application and monitoring users. After the db was moved
> to production, a couple of users were added without any problem.
> > 3) No, even if the replication level is set to logical in
> postgresql.conf, we only use streaming replication.
>
> I've encountered seemingly similar ERROR:
> [ 2018-05-22 15:04:03.270 MSK ,,,281756,XX001 ]:ERROR:  found xmin
> 747375134 from before relfrozenxid 2467346321
> [ 2018-05-22 15:04:03.270 MSK ,,,281756,XX001 ]:CONTEXT:  automatic vacuum
> of table "postgres.pg_catalog.pg_database"
>
> Table pg_database, probably, was not changed anyhow for a long period of
> database exploitation.
> Unfortunately, I've found out this only there were million of xids left
> and had to vacuum freeze db in single user mode asap. But, probably, I will
> be able to restore database from backups and inspect it, if necessary.
> Though first occurrence of this error was beyond recovery window.
>
> Best regards, Andrey Borodin.


I could build a mirror instance with barman and see if the issue is present
as well, then try to vacuum freeze it in single mode, and see if it
disappears; but I would like to know why it happened in the first time. I
wonder if the autovacuum settings played a role, we kept the defaults, even
if the instance has a very heavy update workload.

Best Regards,

Paolo Crosato

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote:
> Hmm .. surely
> 
> diff --git a/src/backend/access/heap/heapam.c 
> b/src/backend/access/heap/heapam.c
> index 5016181fd7..5d7fa1fb45 100644
> --- a/src/backend/access/heap/heapam.c
> +++ b/src/backend/access/heap/heapam.c
> @@ -6690,7 +6690,7 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple,
>   xid = HeapTupleHeaderGetXmin(tuple);
>   xmin_frozen = ((xid == FrozenTransactionId) ||
>  HeapTupleHeaderXminFrozen(tuple));
> - if (TransactionIdIsNormal(xid))
> + if (!xmin_frozen && TransactionIdIsNormal(xid))
>   {
>   if (TransactionIdPrecedes(xid, relfrozenxid))
>   ereport(ERROR,
> 
> 
> ??

I don't think that's necesary - HeapTupleHeaderGetXmin() returns
FrozenTransactionId if the tuple is frozen (note the
HeapTupleHeaderXminFrozen() within).

Greetings,

Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:30:54 -0700, Andres Freund wrote:
> On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote:
> > Hmm .. surely
> > 
> > diff --git a/src/backend/access/heap/heapam.c 
> > b/src/backend/access/heap/heapam.c
> > index 5016181fd7..5d7fa1fb45 100644
> > --- a/src/backend/access/heap/heapam.c
> > +++ b/src/backend/access/heap/heapam.c
> > @@ -6690,7 +6690,7 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple,
> > xid = HeapTupleHeaderGetXmin(tuple);
> > xmin_frozen = ((xid == FrozenTransactionId) ||
> >HeapTupleHeaderXminFrozen(tuple));
> > -   if (TransactionIdIsNormal(xid))
> > +   if (!xmin_frozen && TransactionIdIsNormal(xid))
> > {
> > if (TransactionIdPrecedes(xid, relfrozenxid))
> > ereport(ERROR,
> > 
> > 
> > ??
> 
> I don't think that's necesary - HeapTupleHeaderGetXmin() returns
> FrozenTransactionId if the tuple is frozen (note the
> HeapTupleHeaderXminFrozen() within).

FWIW, even if that weren't the case: a) there'd be a lot more wrong with
this routine imo. b) some of the tuples affected clearly weren't
frozen...

Greetings,

Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote:

> FWIW, even if that weren't the case: a) there'd be a lot more wrong with
> this routine imo. b) some of the tuples affected clearly weren't
> frozen...

Right.

BTW is it just a coincidence or are all the affected tables pg_authid?
Maybe the problem is shared relations ..?  Maybe the fact that they have
separate relfrozenxid (!?) in different databases?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote:

> On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote:
> > Hmm .. surely

> > xid = HeapTupleHeaderGetXmin(tuple);
> > xmin_frozen = ((xid == FrozenTransactionId) ||
> >HeapTupleHeaderXminFrozen(tuple));
> > -   if (TransactionIdIsNormal(xid))
> > +   if (!xmin_frozen && TransactionIdIsNormal(xid))

> I don't think that's necesary - HeapTupleHeaderGetXmin() returns
> FrozenTransactionId if the tuple is frozen (note the
> HeapTupleHeaderXminFrozen() within).

Ah, yeah ... I probably thought about this when writing it and removed
it for that reason.

BTW I think the definition of HeapTupleHeaderXminFrozen is seriously
confusing, by failing to return true if the xmin is numerically
FrozenXid (which it'll be if the database was pg_upgraded).  I wonder
about this one in HeapTupleSatisfiesMVCC:

else
{
/* xmin is committed, but maybe not according to our snapshot */
if (!HeapTupleHeaderXminFrozen(tuple) &&
XidInMVCCSnapshot(HeapTupleHeaderGetRawXmin(tuple), 
snapshot))
return false;   /* treat as still in progress */
}

I think this is not a bug only because XidInMVCCSnapshot does this

/* Any xid < xmin is not in-progress */
if (TransactionIdPrecedes(xid, snapshot->xmin))
return false;

which makes it return false for FrozenXid, but seems more of an accident
than explicitly designed.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

2018-05-24 Thread Jeremy Finzel

>
> BTW is it just a coincidence or are all the affected tables pg_authid?
> Maybe the problem is shared relations ..?  Maybe the fact that they have
> separate relfrozenxid (!?) in different databases?
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
 We have had this problem twice and both times on both pg_authid and
pg_auth_members. Thanks,

Jeremy

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote:
> BTW is it just a coincidence or are all the affected tables pg_authid?
> Maybe the problem is shared relations ..?  Maybe the fact that they have
> separate relfrozenxid (!?) in different databases?

Yes, that appears to be part of the problem. I've looked at a number of
shared relation related codepaths, but so far my theory is that the
relcache is wrong. Note that one of the reports in this thread clearly
had a different relcache relfrozenxid than in the catalog.

Then there's also:
http://archives.postgresql.org/message-id/1527193504642.36340%40amazon.com

Greetings,

Andres Freund

Re: Performance opportunities for write-only audit tables?

2018-05-24 Thread Andrew Bartley

Hi,

The two main techniques we use are.

1. Create a script to pass the -t param to pg_dump to exclude the log
tables.  The idea here is to backup the rest of your DB to one backup
regime and the log tables to another. We set it up so at the end of the day
the current log table is backed up and loaded into an backup archive, then
we vacuum freeze the log table.  The benefits are that once each log table
is "Closed" (meaning you will no longer be writing to that log table ever
again), is backed up once only ever...   It speeds housekeeping up, and
your daily backups are much smaller.

2. Use  UNLOGGED in the log table creates.

Have fun.

Andrew

On Fri, 25 May 2018 at 02:55 Paul Jungwirth 
wrote:

> Hi,
>
> A project of mine uses a trigger-based approach to record changes to an
> audit table. The audit table is partitioned by month (pg 9.5, so
> old-fashioned partitioning). These tables are write-heavy but
> append-only and practically write-only: we never UPDATE or DELETE, and
> we seem to consult them only a few times a year. But they are enormous:
> bigger than the rest of the database in fact. They slow down our
> backups, they increase WAL size and streaming replication, they add to
> recovery time, they make upgrades more time-consuming, and I suppose
> they compete for RAM.
>
> This is all on an AWS EC2 instance with EBS storage. We also run a warm
> standby with streaming replication.
>
> Since these tables are so different from everything else, I'm wondering
> what opportunities we have to reduce their performance cost. I'm
> interested both in practical high-bang-for-buck changes, but also in
> harder just-interesting-to-think-about last-resort approaches. Here are
> a few ideas of my own, but I'm curious what others think:
>
> We already have no indexes or foreign keys on these tables, so at least
> there's no cost there.
>
> Since they are already partitioned, we could move old data to offline
> storage and drop those tables. This feels like the biggest, easiest win,
> and something we should have done a long time ago. Probably it's all we
> need.
>
> Put them on a different tablespace. This one is also pretty obvious, but
> aside from using a separate disk, I'm curious what other crazy things we
> could do. Is there any per-tablespace tuning possible? (I think the
> answer within Postgres is no, but I wish we could change the settings
> for wal_level, or exclude them from replication, or something, so I'm
> wondering if we could achieve the same effect by exploiting being on a
> separate filesystem.) Maybe put the tablespace on some FUSE filesystem
> to get async writes? Or just pick different mount options, e.g. on ext4
> lazytime,dealloc,data=writeback? I don't know. Or at a different level:
> change the triggers so they call a custom function that uses a new
> thread to store the audit records elsewhere. Maybe these ideas are all
> too risky, but I think the organization is fine with slightly relaxed
> durability guarantees for this data, and anyway I'm just curious to have
> a list of possibilities before I categorize anything as too crazy or
> not. :-)
>
> If we upgraded to pg 10 we could use logical replication and leave out
> the audit tables. That is appealing. Even without upgrading, I guess we
> could replace those tables with postgres_fdw ones, so that they are not
> replicated? Has anyone else used that trick?
>
> Thanks!
>
> --
> Paul  ~{:-)
> p...@illuminatedcomputing.com
>
>

Re: Performance opportunities for write-only audit tables?

2018-05-24 Thread Andrew Bartley

Sorry that should be a "vacuum full freeze"... not just a  "vacuum freeze"

On Fri, 25 May 2018 at 07:07 Andrew Bartley  wrote:

> Hi,
>
> The two main techniques we use are.
>
> 1. Create a script to pass the -t param to pg_dump to exclude the log
> tables.  The idea here is to backup the rest of your DB to one backup
> regime and the log tables to another. We set it up so at the end of the day
> the current log table is backed up and loaded into an backup archive, then
> we vacuum freeze the log table.  The benefits are that once each log table
> is "Closed" (meaning you will no longer be writing to that log table ever
> again), is backed up once only ever...   It speeds housekeeping up, and
> your daily backups are much smaller.
>
> 2. Use  UNLOGGED in the log table creates.
>
> Have fun.
>
> Andrew
>
> On Fri, 25 May 2018 at 02:55 Paul Jungwirth 
> wrote:
>
>> Hi,
>>
>> A project of mine uses a trigger-based approach to record changes to an
>> audit table. The audit table is partitioned by month (pg 9.5, so
>> old-fashioned partitioning). These tables are write-heavy but
>> append-only and practically write-only: we never UPDATE or DELETE, and
>> we seem to consult them only a few times a year. But they are enormous:
>> bigger than the rest of the database in fact. They slow down our
>> backups, they increase WAL size and streaming replication, they add to
>> recovery time, they make upgrades more time-consuming, and I suppose
>> they compete for RAM.
>>
>> This is all on an AWS EC2 instance with EBS storage. We also run a warm
>> standby with streaming replication.
>>
>> Since these tables are so different from everything else, I'm wondering
>> what opportunities we have to reduce their performance cost. I'm
>> interested both in practical high-bang-for-buck changes, but also in
>> harder just-interesting-to-think-about last-resort approaches. Here are
>> a few ideas of my own, but I'm curious what others think:
>>
>> We already have no indexes or foreign keys on these tables, so at least
>> there's no cost there.
>>
>> Since they are already partitioned, we could move old data to offline
>> storage and drop those tables. This feels like the biggest, easiest win,
>> and something we should have done a long time ago. Probably it's all we
>> need.
>>
>> Put them on a different tablespace. This one is also pretty obvious, but
>> aside from using a separate disk, I'm curious what other crazy things we
>> could do. Is there any per-tablespace tuning possible? (I think the
>> answer within Postgres is no, but I wish we could change the settings
>> for wal_level, or exclude them from replication, or something, so I'm
>> wondering if we could achieve the same effect by exploiting being on a
>> separate filesystem.) Maybe put the tablespace on some FUSE filesystem
>> to get async writes? Or just pick different mount options, e.g. on ext4
>> lazytime,dealloc,data=writeback? I don't know. Or at a different level:
>> change the triggers so they call a custom function that uses a new
>> thread to store the audit records elsewhere. Maybe these ideas are all
>> too risky, but I think the organization is fine with slightly relaxed
>> durability guarantees for this data, and anyway I'm just curious to have
>> a list of possibilities before I categorize anything as too crazy or
>> not. :-)
>>
>> If we upgraded to pg 10 we could use logical replication and leave out
>> the audit tables. That is appealing. Even without upgrading, I guess we
>> could replace those tables with postgres_fdw ones, so that they are not
>> replicated? Has anyone else used that trick?
>>
>> Thanks!
>>
>> --
>> Paul  ~{:-)
>> p...@illuminatedcomputing.com
>>
>>

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote:

> On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote:
> > BTW is it just a coincidence or are all the affected tables pg_authid?
> > Maybe the problem is shared relations ..?  Maybe the fact that they have
> > separate relfrozenxid (!?) in different databases?
> 
> Yes, that appears to be part of the problem. I've looked at a number of
> shared relation related codepaths, but so far my theory is that the
> relcache is wrong. Note that one of the reports in this thread clearly
> had a different relcache relfrozenxid than in the catalog.

Hmm ... is that because they read the values on different databases?
Are you referring to the reports by Maxim Boguk?  I see one value from
template1, another value from template0.

> Then there's also:
> http://archives.postgresql.org/message-id/1527193504642.36340%40amazon.com

ah, so deleting the relcache file makes the problem to go away?  That's
definitely pretty strange.  I see no reason for the value in relcache to
become out of step with the catalogued value in the same database ... I
don't think we transmit in any way values of one database to another.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Performance opportunities for write-only audit tables?

On Thursday, May 24, 2018, Andrew Bartley  wrote:

> Hi,
>
> The two main techniques we use are.
>
> The idea here is to backup the rest of your DB to one backup regime and
> the log tables to another. We set it up so at the end of the day the
> current log table is backed up and loaded into an backup archive, then we
> vacuum freeze the log table.
>

Yeah, doing logging in-database for immediate performance while
periodically copying or moving said data to external storage seems like the
best option.  Depends on the definition of an acceptable process and
response time should the audit data be needed though.

>
> 2. Use  UNLOGGED in the log table creates.
>

Seriously?  For audit tables?

David J.

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid